Warehouse facts, dims & events

1-- Fact: order_items grain2fact_order_items (3  order_item_id,4  order_id,5  date_key,           -- FK date dim6  customer_key,       -- FK customer dim (surrogate)7  product_key,8  qty,9  gross_cents,10  discount_cents,11  net_cents12)1314dim_customer (customer_key SK, customer_id NK, country, segment, ...)15dim_product  (product_key SK, sku NK, category, ...)16dim_date     (date_key, date, week, month, fiscal_year, is_holiday)1718-- Grain sentence: one row per order line per order.

1-- Semi-additive: daily account balance snapshot2fact_account_daily_balance (3  date_key, account_key, balance_cents4)5-- Correct: SUM(balance) for a day across accounts6-- Wrong: SUM(balance) across days for 'total money'78-- Use last snapshot in range or average balance rules as product defines

1dim_customer_scd2 (2  customer_key   BIGSERIAL PK,   -- surrogate per version3  customer_id    BIGINT NOT NULL, -- natural key4  country        TEXT,5  segment        TEXT,6  valid_from     TIMESTAMPTZ NOT NULL,7  valid_to       TIMESTAMPTZ,    -- null or '9999' if current8  is_current     BOOLEAN NOT NULL9);1011-- Fact points at customer_key version true at event time12-- Point-in-time join:13SELECT f.*, d.segment14FROM fact_order_items f15JOIN dim_customer_scd2 d16  ON d.customer_id = f.customer_id17 AND f.ordered_at >= d.valid_from18 AND (d.valid_to IS NULL OR f.ordered_at < d.valid_to);

1-- Simplified funnel: view → add_cart → purchase within 7 days2WITH views AS (3  SELECT user_id, MIN(ts) AS view_ts4  FROM events WHERE event_name = 'view_product'5  GROUP BY 16),7carts AS (8  SELECT e.user_id, MIN(e.ts) AS cart_ts9  FROM events e10  JOIN views v ON v.user_id = e.user_id11  WHERE e.event_name = 'add_cart'12    AND e.ts >= v.view_ts13    AND e.ts < v.view_ts + INTERVAL '7 days'14  GROUP BY 115),16purchases AS (17  SELECT e.user_id, MIN(e.ts) AS purchase_ts18  FROM events e19  JOIN carts c ON c.user_id = e.user_id20  WHERE e.event_name = 'purchase'21    AND e.ts >= c.cart_ts22    AND e.ts < c.cart_ts + INTERVAL '7 days'23  GROUP BY 124)25SELECT26  (SELECT COUNT(*) FROM views) AS n_view,27  (SELECT COUNT(*) FROM carts) AS n_cart,28  (SELECT COUNT(*) FROM purchases) AS n_purchase;

1-- Idempotent daily load sketch (conceptual)2DELETE FROM fact_order_items WHERE date_key = :load_date;3INSERT INTO fact_order_items4SELECT ... FROM staging_orders WHERE order_date = :load_date;56-- Or MERGE on natural keys for slowly changing facts7MERGE INTO fact_x t8USING staging_x s ON t.nk = s.nk9WHEN MATCHED THEN UPDATE SET ...10WHEN NOT MATCHED THEN INSERT ...;