Data Modeling in Practice: Grain, Facts, Dimensions, and Keys

Declare the Grain, Before Anything Else

Grain is what a single row represents. It's the first decision, it constrains every subsequent one, and skipping it is the root cause of most broken models.

A grain statement should be one sentence with no "and also" in it:

• One row per order line item.
• One row per customer, per day.
• One row per support ticket state change.

Write it into the model's documentation, not just your head, so it's visible to whoever reads the table next:

models:
  - name: fct_order_items
    description: |
      Grain: one row per order line item.
      Includes cancelled orders (flagged, not filtered).
      Excludes internal test accounts.
    columns:
      - name: order_item_sk
        tests: [unique, not_null]

The reason grain comes first is that it determines what you're allowed to put in the table. A column only belongs if it's a genuine property of that grain. Order-level shipping cost does not belong on a line-item fact — not because it's unavailable, but because it's not a property of a line item, and putting it there means summing it across lines double-counts the shipping on every multi-line order.

the failure mode

Mixed grain is the quiet killer. A table where most rows are line items but order-level discount rows have been appended looks fine in a preview and produces wrong totals forever. If you find yourself adding a row_type column to distinguish what different rows mean, you have two tables wearing a trenchcoat.

Go finer than you think you need. You can always aggregate a fine-grained fact up; you cannot decompose a coarse one back down. Storage is the cheap part of this trade.

Fact Tables: Measurements and Foreign Keys

A fact table holds the things you measure, plus the keys that let you slice those measurements. Structurally it's narrow and long: a handful of numeric columns, a set of foreign keys, and a great many rows.

{{ config(materialized='incremental', unique_key='order_item_sk') }}

SELECT
    -- surrogate key for this grain
    {{ dbt_utils.generate_surrogate_key(['oi.order_id', 'oi.line_number']) }}
        AS order_item_sk,

    -- foreign keys to dimensions
    d.date_key             AS order_date_key,
    c.customer_sk,
    p.product_sk,

    -- degenerate dimension: kept on the fact, no dim table
    oi.order_id,
    oi.line_number,

    -- measures, all additive at this grain
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price  AS gross_amount,
    oi.discount_amount,
    oi.quantity * oi.unit_price - oi.discount_amount AS net_amount

FROM {{ ref('stg_order_items') }} oi
LEFT JOIN {{ ref('dim_customers') }} c ON oi.customer_id = c.customer_id
LEFT JOIN {{ ref('dim_products') }}  p ON oi.product_id  = p.product_id
LEFT JOIN {{ ref('dim_dates') }}     d ON oi.order_date  = d.date_day

order_id there is a degenerate dimension — an identifier that's genuinely dimensional but has no attributes of its own worth a separate table, so it lives on the fact. Perfectly normal, and better than creating a dim_orders containing nothing but an ID.

the three fact types

• Transaction facts — one row per event, at the moment it happened. The default, and what most people mean by "fact table." Insert-only, never updated.
• Periodic snapshot facts — one row per entity per period, capturing state. Account balance per customer per day. Predictable size, and the right answer for anything where "what was it on that date" matters more than individual events.
• Accumulating snapshot facts — one row per process instance, with columns for each milestone, updated as the process advances. An order with ordered_at, picked_at, shipped_at, delivered_at in one row. Ideal for pipeline and lag analysis, and the one fact type that genuinely gets updated in place.

additivity is a property you have to check

Not every number can be summed. Additive measures sum across all dimensions — revenue, quantity. Semi-additive measures sum across some but not time — an account balance can be summed across customers, but summing a balance across days is meaningless. Non-additive measures can't be summed at all: ratios, percentages, unit prices.

The practical rule is to store the components and derive the ratio at query time. Storing margin_pct on a fact guarantees someone eventually averages it and reports a number that doesn't match margin computed properly. Store revenue and cost; let the metric layer divide.

Dimension Tables: Context, Denormalized

Dimensions hold the descriptive attributes you filter and group by. They're wide and short — many columns, comparatively few rows — and they should be flat. Where normalization says to split category into its own table, dimensional modeling says to flatten it in.

{{ config(materialized='table') }}

SELECT
    {{ dbt_utils.generate_surrogate_key(['p.product_id']) }} AS product_sk,
    p.product_id,                       -- natural key, kept for joins/debugging

    p.product_name,
    p.sku,

    -- hierarchy flattened in, not snowflaked out
    c.category_name,
    c.department_name,
    b.brand_name,
    b.supplier_name,

    -- useful derived attributes belong here too
    CASE WHEN p.list_price < 500  THEN 'budget'
         WHEN p.list_price < 5000 THEN 'mid'
         ELSE 'premium' END AS price_band,
    p.is_active

FROM {{ ref('stg_products') }} p
LEFT JOIN {{ ref('stg_categories') }} c ON p.category_id = c.category_id
LEFT JOIN {{ ref('stg_brands') }}     b ON p.brand_id    = b.brand_id

That price_band column is doing real work. Derived attributes belong in the dimension precisely so that every analyst uses the same buckets — the moment the definition lives in individual queries instead, you get four incompatible definitions of "premium."

three patterns worth knowing

Conformed dimensions are dimensions shared across multiple fact tables. If dim_customers is used by both fct_orders and fct_support_tickets, you can compare order volume against ticket volume by the same customer segment. This is the single highest-value property of a dimensional model, and it only works if you resist the urge to build dim_customers_for_support.

Role-playing dimensions are one physical dimension referenced several times with different meanings. A date dimension joined as order date, ship date, and delivery date. Expose them as views with prefixed column names so a query reading ship_date_month is unambiguous.

Junk dimensions collapse a scatter of low-cardinality flags — is_gift, is_expedited, payment_type — into one small dimension of distinct combinations, replacing several columns on the fact with a single key. Worth doing when the flags start multiplying; not worth doing for three of them.

A date dimension deserves special mention: build one, always, covering more years than you think you need. Fiscal periods, holiday flags, and week-start conventions are business logic, and business logic belongs in a table rather than being re-derived with date functions in every query.

Keys: Natural, Surrogate, and the Unknown Member

Every dimension has a natural key — the identifier the source system uses. Most should also have a surrogate key, a warehouse-generated identifier that the facts actually join on.

The reasons for the extra key are practical rather than theoretical. Natural keys change: a system migration renumbers customers, an acquisition introduces ID collisions, a source switches from integers to UUIDs. They're also often composite, which means every fact table carries three columns to identify one dimension row. And critically, tracking history requires a key that can distinguish two versions of the same entity — which a natural key, by definition, cannot.

-- current-state dimension: key on the natural key alone
{{ dbt_utils.generate_surrogate_key(['customer_id']) }} AS customer_sk

-- historised (SCD2) dimension: include the validity start
{{ dbt_utils.generate_surrogate_key(['customer_id', 'valid_from']) }} AS customer_sk

-- composite grain on a fact
{{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }} AS order_item_sk

Hash-based keys have a real advantage over sequences in a distributed warehouse: they're deterministic. The same inputs produce the same key on any machine, in any order, on a full refresh or an incremental run — so a rebuild doesn't renumber everything and orphan your facts. Always hash a normalized input, though: trailing whitespace or inconsistent casing produces two keys for one entity.

the unknown member

Facts arrive referencing dimension rows that don't exist yet — a late-arriving product, a null customer on a guest checkout. If you inner join, those facts silently vanish and your revenue total is quietly short. If you leave the key null, outer joins spread nulls through every report.

The fix is a designated unknown row in every dimension:

-- in the dimension: append a known sentinel row
SELECT ... FROM real_products
UNION ALL
SELECT '-1' AS product_sk,
       'UNKNOWN' AS product_id,
       'Unknown product' AS product_name,
       'Unknown' AS category_name

-- in the fact: never emit a null key
coalesce(p.product_sk, '-1') AS product_sk

Now unmatched facts are visible rather than missing. "Unknown product: ₹40,000" in a report is a data quality bug someone will fix. A revenue total that's ₹40,000 short with no indication why is a bug nobody will ever find.

Slowly Changing Dimensions: Keeping the Past

Customers move cities. Products get recategorised. Sales reps change territory. The question every dimension eventually has to answer is: when an attribute changes, what happens to the rows that referenced the old value?

In dbt, Type 2 is handled by snapshots rather than models, because it needs to capture change as it happens rather than derive it after the fact:

{% snapshot customers_snapshot %}

{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['city', 'segment', 'plan_tier']
) }}

SELECT * FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

Note that check_cols lists only the attributes worth versioning. Snapshotting every column means a new row every time last_login_at updates, which turns a dimension into an event log and makes joins much more expensive.

the join people get wrong

A Type 2 dimension has multiple rows per natural key, so joining a fact to it on the natural key alone multiplies your rows. The fact must join on the surrogate key captured at event time, or — if you only have the natural key — on the key and the validity window:

SELECT
    f.order_id,
    f.net_amount,
    c.segment          -- segment as it was on the order date
FROM {{ ref('fct_orders') }} f
JOIN {{ ref('dim_customers_scd') }} c
  ON f.customer_id = c.customer_id
 AND f.order_ts >= c.dbt_valid_from
 AND f.order_ts <  coalesce(c.dbt_valid_to, '9999-12-31')

That coalesce on the open-ended row is not optional — dbt_valid_to is null for the current version, and a plain < comparison against null drops every current row, which is a bug that passes tests and fails in production.

One thing snapshots can't do: recover history you didn't capture. They record changes from the moment you start running them. If historical tracking might matter later, start snapshotting now, even before anyone has asked for it.