An order fact table repeats the order-level shipping charge on every line-item row — what breaks?

## The symptom The first sign is usually a report that is right at one level and wrong at another. Shipping revenue by month looks inflated; shipping per order looks correct. Or gross margin comes out too low, and the gap is proportional to how many lines orders happen to have. ```text order_id line_no product line_amount shipping_amount 1001 1 A 40.00 9.99 1001 2 B 25.00 9.99 1001 3 C 15.00 9.99 SUM(line_amount) = 80.00 -- correct SUM(shipping_amount) = 29.97 -- wrong: the order was charged 9.99 once ``` ## Why it happens The table declares one grain — one row per order line — but carries a measure taken at a coarser grain. The loader had a header value and a line-level target, and the easiest thing to do was copy it down. Nothing in the database objects: the column type is fine, the row count is fine, the joins are fine. Only the meaning is broken, and meaning is exactly what a declared grain is supposed to protect. The general rule this violates: **a measure may sit on a fact row only if it is measured at that row's grain.** A coarser measure copied onto finer rows will be counted once per finer row, which is the definition of double counting. The same failure appears with an order-level discount, a monthly account balance copied onto daily rows, or a campaign budget repeated on every click. ## Why it is worse than an obviously wrong number The inflated value is plausible. Shipping revenue that is 2.4x too high does not look like a NULL or a negative number; it looks like a good quarter. It passes review, gets quoted in a meeting, and is discovered months later when someone reconciles against the finance system. Meanwhile every downstream model built on the table inherits the error. ## Fix one: allocate the measure down to the grain Split the order-level amount across the lines so the parts sum back to the original. The allocation basis should be defensible to the business — line amount, weight, or unit count are the usual choices. ```sql SELECT l.order_id, l.line_no, l.line_amount, h.shipping_amount * l.line_amount / SUM(l.line_amount) OVER (PARTITION BY l.order_id) AS allocated_shipping FROM order_line l JOIN order_header h ON h.order_id = l.order_id; ``` Now every column on the row is a true line-level measurement and `SUM` is safe at any level. Two cautions: rounding must be handled so the allocated parts add back to the header value exactly (give the remainder to the largest line), and the allocation basis becomes a business rule that has to be documented, because someone will eventually ask why line C absorbed $1.87 of shipping. ## Fix two: split the fact table by grain If allocation is not defensible — some order-level values genuinely cannot be attributed to lines — keep two fact tables: line measures at line grain, order-level measures at order grain, one row per order. Each is internally consistent and each can be aggregated freely on its own. A report that needs both aggregates each table separately to a common level and combines the results, rather than joining them row-to-row, which would reintroduce the fan-out. ## Fix three, the one to avoid A tempting third option is to keep one table and add a marker — repeat the value but null it out on all but the first line, or add a `row_type` column and filter. It works right up until someone forgets the filter. It also makes the table's grain unstatable: rows no longer all mean the same thing, so "what does one row represent" has two answers. Every future query carries tribal knowledge, and new consumers learn it by producing a wrong number first. ## Detecting it before a report does Two checks catch nearly all of this class: 1. For every measure column, ask whether the source system records that value at the table's declared grain. Anything sourced from a header, a monthly close, or a parent entity is suspect. 2. Reconcile a coarse aggregate against the source of record: `SUM(shipping_amount)` for a period against the order system's shipping revenue for the same period. A ratio near the average lines-per-order is a fingerprint for this exact defect. ## The wider pattern This is the mixed-grain failure in its simplest form, but the same reasoning covers other shapes: header rows and detail rows stored in one table, one table holding both a per-event row and a per-day summary row, or a fact table that gained a new key column mid-life so old rows and new rows mean different things. In every case the cure is the same — make all rows mean one thing, and if two things need storing, store them in two tables. ## What an interviewer listens for They want the diagnosis stated as a grain mismatch rather than "the SQL is wrong", the ratio-to-line-count fingerprint, and both principled fixes with a reason to prefer one. Candidates who reach for a filter or a `DISTINCT` patch as the primary answer are showing they would leave the defect in the model.