The one rule: decide the grain first
Every fact table has a grain: what one row means ("one order line", "one product per warehouse per week"). Write it down before you add a column. Most modeling bugs (double counting, totals that don't add up, budgets that can't be compared with sales) are grain bugs.
Bad vs good
The flat table
BAD: one wide table GOOD: a star ┌─────────────────────────────────────┐ DimDate ─┐ │ OrderID, OrderDate, CustomerName, │ DimCustomer ─┤ │ CustomerCity, Segment, ProductName, │ ├──► FactSales (one row per order line) │ Category, Brand, Region, Manager, │ DimProduct ──┤ keys + Quantity, UnitPrice, Discount │ Quantity, UnitPrice, Discount, … │ DimRegion ───┘ └─────────────────────────────────────┘ Repeats every customer and product attribute on every row, can't hold a budget or a second fact, and makes time intelligence and RLS awkward.
Facts related to facts
BAD: FactSales ◄──── many-to-many ────► FactBudget GOOD: both facts filtered by shared dimensions
DimDate (month) ──► FactSales
Totals repeat or vanish depending on DimDate (month) ──► FactBudget
the visual; nobody can explain the numbers. DimRegion ──► FactSales, FactBudget
Category ──► FactSales (via DimProduct), FactBudget
Snowflake and bidirectional chains
BAD: FactSales ◄── DimProduct ◄─both─► DimSubCategory ◄─both─► DimCategory GOOD: flatten SubCategory and Category into DimProduct; single-direction relationships
Fact patterns
| Pattern | Grain | Example | Watch out for |
|---|---|---|---|
| Transaction fact | One row per event | FactSales: one order line | Duplicates from re-exports; returns as negative rows or a separate fact |
| Periodic snapshot | One row per entity per period | FactInventory: stock per product per week | Semi-additive: never sum over time (pattern) |
| Accumulating snapshot | One row per process instance, updated as it moves | Order lifecycle: ordered, packed, shipped, delivered dates on one row | Several date roles; rows change after load |
| Factless fact | One row per occurrence or eligibility, no measure | Attendance, "customer eligible for promotion" | Count rows; the absence of a row is information |
| Multiple fact tables | Each its own grain | Sales and Budget | Relate each to shared (conformed) dimensions, never to each other |
| Different grains | Daily sales, monthly targets | FactSales vs FactBudget | Relate the coarser fact to a coarser attribute (month, category); blank below its grain |
Dimension patterns
| Pattern | What it is | Example | Teach yourself with |
|---|---|---|---|
| Conformed dimension | One dimension shared by several facts, with the same keys and meaning | One DimCustomer for Sales, Returns and Support | Sprint 05 |
| Role-playing dimension | One dimension used in several roles | DimDate as Order Date and Ship Date | USERELATIONSHIP, or a second date table if both roles are needed on one visual |
| SCD type 1 | Overwrite the attribute; no history | Correcting a misspelt city | Keys and SCDs |
| SCD type 2 | New row per change with valid-from/to and a surrogate key; facts point at the version current when they happened | Customer moves region: old sales stay in the old region | Keys and SCDs |
| Junk dimension | Several low-cardinality flags combined into one small dimension | IsPromo × IsOnline × PaymentType | Dimension patterns |
| Degenerate dimension | A dimension attribute with no table, kept on the fact | OrderID on FactSales | Use it for counts and drill-through; don't build a dimension for it |
| Parent-child | A self-referencing hierarchy | DimEmployee[ManagerKey] → EmployeeKey | PATH, PATHITEM to flatten into levels |
| Bridge | Resolves many-to-many between a dimension and a fact or two dimensions | Accounts with several owners; products in several promotions | Bridge table plus a bidirectional or CROSSFILTER relationship, used deliberately (guidance) |
SCD type 2 in practice
sql
-- the dimension keeps every version
-- CustomerSK (surrogate key), CustomerID (business key), Region, ValidFrom, ValidTo, IsCurrent
-- facts store CustomerSK of the version valid on the transaction date
SELECT f.*, d.CustomerSK
FROM staging_sales AS f
JOIN dim_customer AS d
ON d.CustomerID = f.CustomerID
AND f.OrderDate >= d.ValidFrom AND f.OrderDate < d.ValidTo;Decide which attributes are type 2 (tracked) and which are type 1 (overwritten), and record the decision in the model design document. Microsoft documents SCD type 2 loading with Fabric Data Factory; see the references.
Model checklist
The full version is the model review checklist.