One Grain per Model

Concepts covered: Model layers and grain

What one row is

The grain of a model is what 1 row stands for. fct_orders holds 1 row per order, fct_order_items 1 row per line on an order, int_plays__daily_by_user 1 row per user per day. Write the grain down before the select, because every join and every sum in the model is correct or wrong only relative to it. A model with 2 grains mixed together has no grain, and no query on it can be trusted.

A join that multiplies

Take a shop with 3 orders and 5 order lines. Each order carries its own total, and each line belongs to exactly 1 order.

Revenue across the 3 orders is 30.00 plus 12.00 plus 45.00, which is 87.00. Someone wants revenue per customer with an item count beside it, and joins the 2 staging models in one step.

select
    o.customer_id,
    sum(o.order_total) as revenue,
    count(i.item_id) as item_count
from {{ ref('stg_shop__orders') }} as o
join {{ ref('stg_shop__order_items') }} as i
    on o.order_id = i.order_id
group by
    o.customer_id

Trace the join before the group by. Every order row is matched to each of its lines, so the join result has 5 rows and order 1 appears twice.

Customer 101 shows 150.00 against a true 75.00. Order 1 has 2 lines, so its 30.00 is counted twice; order 3 has 2 lines, so its 45.00 is counted twice. The query raised no error and returned numbers, and the numbers are wrong by exactly the number of lines on each order. Any measure that lives on the one side of a join is multiplied by the number of matches on the many side, and a sum over it inherits every copy.

Change the grain in its own model

The item count lives at the order-line grain and the total lives at the order grain. The fix brings the lines up to the order grain first, in an intermediate model whose only job is that aggregation, and then joins 2 models that both hold 1 row per order.

select
    order_id,
    count(*) as item_count,
    sum(quantity) as units
from {{ ref('stg_shop__order_items') }}
group by
    order_id

select
    o.order_id,
    o.customer_id,
    o.order_total,
    coalesce(i.item_count, 0) as item_count,
    coalesce(i.units, 0) as units
from {{ ref('stg_shop__orders') }} as o
left join {{ ref('int_order_items__per_order') }} as i
    on o.order_id = i.order_id

Both sides of the join have 1 row per order_id, so the join is 1 to 1 and the output keeps the order grain: 3 rows in, 3 rows out, and sum(order_total) returns 87.00. The left join and the coalesce keep an order that has no lines yet, which an inner join would drop. Revenue per customer is now a group by over fct_orders, with nothing left to multiply.

Proving the grain

select
    count(*) as row_count,
    count(distinct order_id) as order_count
from analytics.fct_orders

If the 2 numbers match, the model holds 1 row per order. If row_count is larger, some join multiplied rows, and the difference is the number of extra copies. Run this on every model whose grain you have declared, and run it again after adding any join. The check takes a second and catches the error before a dashboard does.

Do

• Name the grain in the model name and in its first comment line
• Aggregate the many side to the one side's grain before joining
• Compare count(*) with count(distinct key) after every new join
• Use coalesce after a left join so a missing match reads 0

Avoid

• Sum a measure from the one side of a 1 to many join
• Mix 2 grains in 1 model
• Trust a query because its numbers look plausible
• Patch fanout with select distinct, which hides the cause

Section 0 joined int_plays__daily_by_user to stg_billing__subscriptions on user_id. That join is many to 1 only while every user has exactly 1 subscription row. The first user with a cancelled row and an active row gets every minute of listening counted twice, with no error anywhere. The join belongs behind an intermediate model that reduces subscriptions to 1 row per user, which the ephemeral section builds.

About this lesson

This section is part of the Models: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.