Dimensional Modeling Example: E-commerce Orders, Returns and Inventory

This is a complete dimensional model for an online store, worked through the way you would in an interview: requirements first, then a bus matrix, then each fact table with its grain, dimensions and facts, then the SQL it makes easy and the follow-up questions interviewers ask. The store processes about 2 million orders a day with around five items each, sells 50,000 products and has 20 million customers. Analysts need yesterday's numbers by 6am and three years of history.

1. Requirements: the questions the model must answer

Data realities from profiling the source Postgres database: orders change status after purchase, refunds can arrive weeks later, some test orders are hard-deleted, shipping is charged per order rather than per item, and timestamps are UTC while the business reports in local time.

2. Bus matrix

Date, customer, product and warehouse are shared by most processes, so they are built first and conformed: one definition, one owner, used everywhere.

3. Order lines (transaction fact)

Grain: one row per product on an order (an order line). It is the most atomic grain the source provides, and every sales question rolls up from it.

create table fact_order_line (
  order_date_key        int not null,   -- dim_date (role-playing view: order date)
  customer_key          int not null,   -- dim_customer, version valid at order time
  customer_profile_key  int not null,   -- mini-dimension: age, income band, loyalty tier at order time
  product_key           int not null,   -- dim_product, version valid at order time
  promotion_key         int not null,   -- -1 = "No promotion"
  warehouse_key         int not null,
  order_flags_key       int not null,   -- junk dimension
  audit_key             int not null,   -- which pipeline run loaded the row
  order_number          varchar(20),    -- degenerate dimension
  order_line_number     smallint,
  quantity              int,
  gross_amount          decimal(12,2),  -- quantity × unit list price
  discount_amount       decimal(12,2),
  net_amount            decimal(12,2),
  cost_amount           decimal(12,2),
  shipping_allocated    decimal(12,2)   -- order shipping fee spread across lines by net value
)
partition by (order_date_key);

The shipping fee: header amounts at a line grain

Shipping is charged once per order, but the fact is at line grain. Repeating the full fee on every line would multiply shipping revenue by the number of lines. Two valid options, and you should say which you chose:

• Allocate the fee to lines in proportion to each line's net amount (as above), so line-level margin includes shipping and sums back exactly to the order's fee. Round so the allocations add up to the cent.
• Keep an order-level fact (fact_order, one row per order) for header amounts such as shipping and order-level discounts, and leave them off the line table entirely.

Average order value then comes from the order level (sum of revenue divided by count of distinct orders), not from averaging lines.

4. Dimensions

5. Returns (transaction fact)

Refunds arrive days or weeks after the sale. Two designs come up in interviews:

• A separate returns process (chosen here): fact_return_line, one row per returned product, with the return date, the original order date (another role of dim_date), product, customer, warehouse and reason. Facts: returned quantity, refund amount, restocking fee. Sales reported for a closed month never change, and return rates come from drilling across.
• Negative rows on the sales fact: append a row with negative quantity and amount, dated on the refund date. Net sales become a single sum, but reasons and return-specific measures have nowhere natural to go.

create table fact_return_line (
  return_date_key       int not null,
  original_order_date_key int not null,   -- role-playing date: when it was bought
  customer_key          int not null,
  product_key           int not null,
  warehouse_key         int not null,
  return_reason_key     int not null,
  order_number          varchar(20),      -- degenerate: links back to the order
  return_number         varchar(20),
  quantity_returned     int,
  refund_amount         decimal(12,2),
  restocking_fee        decimal(12,2)
);

6. Order fulfilment (accumulating snapshot)

Grain: one row per order, updated as it moves through placed, paid, packed, shipped and delivered (or cancelled). This is what answers "where do orders get stuck?" without reconstructing timelines from events.

create table fact_order_fulfilment (
  order_number         varchar(20) primary key,
  customer_key         int not null,
  warehouse_key        int not null,
  carrier_key          int not null,       -- "Not yet assigned" until shipping
  placed_date_key      int not null,
  paid_date_key        int not null,
  packed_date_key      int not null,
  shipped_date_key     int not null,
  delivered_date_key   int not null,
  cancelled_date_key   int not null,
  order_net_amount     decimal(12,2),
  line_count           int,
  hours_to_pay         decimal(8,2),
  hours_to_pack        decimal(8,2),
  hours_to_ship        decimal(8,2),
  hours_in_transit     decimal(8,2),
  is_delivered         smallint,
  is_cancelled         smallint,
  is_late              smallint            -- delivered after the promised date
);

The load is a MERGE on order number: new orders insert, milestone events update the date keys and lags. Because the row is updated, keep the raw status events in the lake so the snapshot can always be rebuilt.

7. Inventory (periodic snapshot)

Grain: one row per product per warehouse per day, written every day even when nothing moved.

create table fact_inventory_daily (
  snapshot_date_key   int not null,
  product_key         int not null,
  warehouse_key       int not null,
  quantity_on_hand    int,            -- semi-additive: never sum across days
  quantity_reserved   int,
  quantity_inbound    int,
  stock_value         decimal(14,2),  -- semi-additive
  is_out_of_stock     smallint,       -- additive counter: days out of stock
  primary key (snapshot_date_key, product_key, warehouse_key)
);

At 50,000 products across 20 warehouses that is about a million rows a day, a modest size for a warehouse. Stock on hand is summed across products and warehouses, but across days it takes the last value of the period or an average. is_out_of_stock is additive, so "days out of stock this month" is a plain sum.

8. Questions the model answers

-- Monthly revenue and margin by category, using the category at the time of sale
select d.month, p.category,
       sum(f.net_amount) as revenue,
       sum(f.net_amount - f.cost_amount) as margin
from fact_order_line f
join dim_date d    on f.order_date_key = d.date_key
join dim_product p on f.product_key = p.product_key
group by d.month, p.category;

-- Repeat-customer rate last quarter (customers with 2+ orders)
with per_customer as (
  select c.customer_id, count(distinct f.order_number) as orders
  from fact_order_line f
  join dim_customer c on f.customer_key = c.customer_key
  join dim_date d     on f.order_date_key = d.date_key
  where d.fiscal_quarter = '2026-Q2'
  group by c.customer_id                 -- durable id, not the versioned key
)
select avg(case when orders >= 2 then 1.0 else 0 end) as repeat_rate from per_customer;

-- Median hours from order to delivery by carrier, delivered orders only
select k.carrier_name, percentile_cont(0.5) within group (order by f.hours_to_ship + f.hours_in_transit) as median_hours
from fact_order_fulfilment f
join dim_carrier k on f.carrier_key = k.carrier_key
where f.is_delivered = 1
group by k.carrier_name;

-- Days out of stock per category last month
select p.category, sum(i.is_out_of_stock) as product_days_out_of_stock
from fact_inventory_daily i
join dim_product p on i.product_key = p.product_key
join dim_date d    on i.snapshot_date_key = d.date_key
where d.month = '2026-09'
group by p.category;

Return rate by category is a drill-across query, combining sales and returns at the category level. See conformed dimensions for the SQL.

9. Loading it

• Extract from a read replica or with change data capture, never from the primary, and land raw data unchanged.
• Load dimensions before facts. When a fact arrives for an unknown customer, insert an inferred member rather than dropping the fact.
• Look up each fact's dimension keys using the version valid at the event time (valid_from <= order_ts < valid_to).
• Make every load idempotent: MERGE on order number and line number, or overwrite whole date partitions, so a rerun never double counts.
• Reconcile row counts and revenue against the source each day, and block publishing if they differ.

10. Follow-up questions and answers

Common mistakes on this problem

• Mixing order headers and lines in one table, so shipping and order discounts are double counted.
• Overwriting product category in place, which silently rewrites last year's reports.
• Updating the original sale row on refund, losing when the refund happened and changing closed periods.
• Summing a daily inventory snapshot across days.
• Using the source customer id as the fact's key, which breaks as soon as history is needed.
• Joining the sales and returns facts directly to compute return rate.