Grain, keys & modeling patterns

Grain is the answer to: what does one row in this table mean? One row per order? Per order line? Per order per day it remained open? Every downstream number depends on this answer, and every warehouse horror story starts with two people assuming different grains. Rule one of dimensional modeling, no exceptions: declare the grain in a sentence, write it in the table's docs, and refuse rows that violate it.

• fct_order_lines — grain: one row per line item on an order. In production an order with three products = three rows, and you'd key on (order_id, line_number) or a surrogate line id. Harbor Lane's dataset is simpler — its generator writes exactly one line per order, so order_id alone uniquely identifies a row and is a valid primary key here. Either way, SUM(qty * unit_price) is meaningful and COUNT(*) counts lines, not orders — counting orders means COUNT(DISTINCT order_id). Grain mistakes are exactly this subtle, which is why you write it down.
• dim_customer — grain: one row per real-world customer. Not per account, not per system. The entire point of Module 4 is making this sentence true.
• dim_product — grain: one row per SKU. Easy today; the moment merchandising introduces variants, you re-litigate it. Grain declarations are living documents.

Three kinds of keys

• Natural keys exist in the business: sku, order_id, an email. Use them when they're stable and unique. sku qualifies. Email does not — people change emails, share them, mistype them.
• Source keys exist in one system: web_id, pos_id. They identify a row in that system, nothing more. Never let a source key masquerade as a customer identity — that's how you get two of everyone.
• Surrogate keys are minted by the warehouse: customer_key 481 means 'golden customer #481, whichever source rows that currently includes.' Surrogates let identity survive merges, splits, and source-system migrations. Dimensions that face entity resolution must use them.

Patterns you'll actually use

• Wide fact, thin dims (the default). Put the measures and the foreign keys on the fact; put descriptions on dims. When in doubt, this.
• Slowly changing dimensions (SCD). When a customer moves from Boston to Austin, do you overwrite (Type 1 — current truth, history lost) or version the row (Type 2 — valid_from/valid_to, history kept)? Default to Type 1 until a real question needs history; Type 2 everywhere is complexity you pay for daily.
• Crosswalk tables. A two-column map from source refs to golden keys (W0042 → 481, P0107 → 481). Unglamorous, and the single most useful table in any multi-system warehouse. You'll build one in Module 4.
• One big table (OBT). Pre-joining the star into a wide flat table for a specific consumer — BI tools and LLM agents both benefit, because a flat table removes join-reasoning errors. OBT is a serving layer, generated from the star; it is never the model itself.