How do you roll out a fact table grain change from one row per order to one row per order line?

## Why a grain change is a different table The grain of a fact table is the business meaning of one row. Change it and you have changed the meaning of every aggregate anyone has ever written against it: - `COUNT(*)` counted orders; now it counts lines. - `AVG(order_total)` averaged over orders; now it averages an order-level value repeated once per line, weighting large orders more heavily. - A join to any dimension now fans out differently. - A measure that was additive at order grain (shipping charge) is now repeated across lines and will double, triple or decuple when summed. No amount of care in the DDL makes this a compatible edit. The professional framing is: `fct_order` and `fct_order_line` are two tables, and the rollout is a migration of consumers between them. ## Step 1 — decide where each measure lives The hardest part is not the rows; it is the measures that do not exist at the finer grain. - **Naturally line-level** (quantity, extended price, unit cost): move down cleanly. - **Order-level but allocatable** (order discount, shipping charge): choose an explicit allocation rule — pro-rata by line extended price is the common one — write the allocated amount into the line row, and prove the allocation sums back to the order value exactly, including the rounding remainder. Never leave a cent unallocated and never let rounding drift. - **Order-level and not allocatable** (order count, a header-level status duration): keep an order-grain fact table for these. Two fact tables at different grains, joined at report time through conformed dimensions, is a correct and normal design, not a failure. A rule of thumb interviewers listen for: *never repeat an order-level value on every line*. It will be summed, and it will be wrong. ## Step 2 — preserve order-level analysis Put the order number on the line rows as a degenerate dimension: a business identifier held in the fact row with no dimension table of its own. Then `COUNT(DISTINCT order_number)` recovers order counts, and average order value becomes `SUM(revenue) / COUNT(DISTINCT order_number)` rather than `AVG(...)`. This is exactly the sort of rewrite that consumers must be told about, because their existing formula still runs and silently returns a different number. ## Step 3 — build side by side and reconcile Run both tables in parallel over the same period and prove the invariants: ```sql SELECT o.order_number, o.order_total, SUM(l.extended_price + l.allocated_shipping) AS line_total FROM fct_order o JOIN fct_order_line l ON l.order_number = o.order_number GROUP BY o.order_number, o.order_total HAVING ABS(o.order_total - SUM(l.extended_price + l.allocated_shipping)) > 0.005; ``` An empty result set is the evidence you take to stakeholders. Also check the grain itself is what you claim — one row per order line, no more: ```sql SELECT order_number, line_number, COUNT(*) FROM fct_order_line GROUP BY order_number, line_number HAVING COUNT(*) > 1; ``` ## Step 4 — make the old table a derived aggregate The move that turns a risky migration into a calm one: once the line-grain table is trustworthy, stop loading the order-grain table from source and rebuild it as an aggregate over the line-grain table. Now there is one pipeline and one source of truth; the old table becomes a compatibility surface returning exactly the numbers it always did. Existing dashboards do not break, and any divergence between the two is impossible by construction rather than a thing you monitor. Where the old table cannot be expressed as an aggregate — because it holds unallocatable header measures — keep it as a genuine order-grain fact table sourced from the same staging layer, and reconcile it on a schedule. ## Step 5 — migrate consumers and retire Enumerate consumers from warehouse query logs and BI lineage rather than from memory. Publish a sunset date for the old table. Give the migrating teams the specific rewrites they need — `COUNT(*)` becomes `COUNT(DISTINCT order_number)`, `AVG(order_total)` becomes the ratio form — because the failure mode is not an error, it is a number that changed. Take the old table out of the consumer-facing view layer before dropping it, so a mistake is a one-line rollback rather than a restore. ## When not to do it at all If only one report needs line detail, an additional line-grain fact table alongside the existing order-grain one may be the whole answer. The atomic grain is the right default for a new model, but retrofitting it onto a heavily consumed published table is a project, and it needs to be justified by the questions it unlocks.