Home › Data engineering › Metric views › 5 · Grain
Data engineering · BI and analytics · free guide · 5 · Grain
Grain, fan-out and the kinds of measure
Grain is what one row means. It is the first question to ask about any metric view, because every measure is computed over those rows. Most wrong numbers in semantic models are grain mistakes: a join that repeats rows, an average of averages, or a distinct count added up from pieces.
The rule
The source grain is the finest slice any metric needs
Pick the source of a metric view at the finest grain any of its metrics must be sliced by. If someone will ever ask "by product", the source can't be at region level. This is why gold should be atomic (one row per business event) and why pre-summed gold tables make bad sources: they answer only the questions someone already thought of.
Silent wrong numbers
Fan-out: when a join repeats rows
Joining down to a finer table repeats the coarser rows.
Metric view joins are many-to-one by default: each source row should find at most one row in the joined table. If the joined table has duplicates (a dealer table with two rows for one dealer after an SCD2 change, say), you get wrong numbers with no error: either the sale rows repeat and sums inflate, or (for a many-to-many match, per the docs) the engine takes the first matching row, so the sale lands in an arbitrary slice. The docs say it plainly: if the join fans out, measures are wrong.
version: 1.1
source: main.sales_gold.fact_invoice_header # one row per invoice
joins:
- name: lines
source: main.sales_gold.fact_sales_line
on: lines.invoice_id = source.invoice_id
cardinality: one_to_many # runtime 18.1+, YAML 1.1
measures:
- name: freight
expr: SUM(freight_amount) # counted once per invoice
- name: line_count
expr: COUNT(lines.line_no) # aggregated at the line grainWhen you do need the finer table, join it as one_to_many: the source rows are not repeated, and measures over the joined table are aggregated at their own grain. One-to-many columns can't be fields, and their materializations only match exactly (no rollup).
Guard it: join dimensions on a key that is unique in gold (the current row of an SCD2 dimension, or the surrogate key), declare primary keys, and test uniqueness in CI. Only set rely.at_most_one_match: true when that test exists, because the flag is not checked.
Three kinds of measure
Additive, semi-additive, non-additive
Which jar a measure is in decides how it may be summed and pre-aggregated.
| Kind | Examples | Adds across | How to model it |
|---|---|---|---|
| Additive | SUM, COUNT | Any slice, any rollup | Safe to pre-aggregate at any grain. |
| Semi-additive | stock on hand, balances | Across dealers yes, across days no | Window measure with range: current and semiadditive: last. |
| Non-additive | ratios, COUNT(DISTINCT), median, percentiles | Never from pieces | Compose ratios from additive parts; distinct counts can't roll up from pre-aggregates. |
version: 1.1
source: main.sales_gold.fact_stock_daily # one row per dealer, product and day
fields:
- name: snapshot_date
expr: snapshot_date
- name: region
expr: region
measures:
- name: units_on_hand
expr: SUM(units_on_hand)
window:
- order: snapshot_date
range: current
semiadditive: last # by month: the last day's stock, not the sum of 30 daysA semi-additive measure: summed across dealers on one day, but by month it returns the last day's value.
The most common mistake
Ratio of sums, not average of ratios
Margin % by region, then 'overall':
| Net revenue | Gross margin | Margin % | |
|---|---|---|---|
| North | 100 | 10 | 10% |
| South | 900 | 270 | 30% |
| Average of the two ratios | 20% (wrong) | ||
| Ratio of the sums | 1000 | 280 | 28% (right) |
Averaging the two regional percentages gives 20%; the true company margin is 28%. Always define a ratio as MEASURE(numerator) / MEASURE(denominator) so the engine sums first and divides last, at whatever grain the query asks for. Never store a pre-computed percentage in gold and SUM or AVG it.
Distinct counts
Distinct counts don't add up
Active dealers in January plus active dealers in February is not active dealers in Q1: a dealer who bought both months is counted twice. COUNT(DISTINCT) must be computed from the atomic rows at the grain asked for. This is also why a materialized aggregate can answer a distinct count only on an exact match of its dimensions, never by rolling up.
Different grains
When metrics live at different grains
| Situation | Setup | Verdict |
|---|---|---|
| Sales at invoice-line grain, targets at month × region | Two metric views; join their results in a CTE (or declare a relationship for tools) | Pursue |
| Force targets onto the sales fact by repeating them on every line | Targets get multiplied by the line count | Drop |
| Stock snapshots and sales in one metric view | Different grains in one source; one of them fans out | Drop |
| Header amounts (freight) with line amounts | Metric view on headers with lines as one_to_many, or allocate freight to lines in gold | Pursue |
Time grains
Time is a dimension at several grains
- Define date, week, month and quarter as separate fields (DATE_TRUNC), so users and Genie can pick a grain by name.
- Join a date dimension for fiscal periods, holidays and working days rather than computing them in each expression.
- Window measures order by a named field: define hierarchy levels on that field by name, not on the raw source column, or rollups go wrong.
- Gaps break trailing windows: a dealer with no sales in a week has no rows, so 'last 4 weeks' may reach back further than you think. Densify where it matters.
- For period-over-period on months, use month offsets (-12 month), not day offsets, so month lengths and leap years are handled.
← 4 · Parts · Next: 6 · Row-level security →
FAQ
Questions people ask
What is fan-out in a metric view?
A join where one source row matches several rows in the joined table, so the source row is repeated and sums and counts come out too big, with no error. Metric view joins assume many-to-one; use one_to_many cardinality when you really need the finer table.
How do I model stock or account balances?
As a window measure with range: current and semiadditive: last ordered by the snapshot date. It sums across dealers on one day but returns the last day's value when you group by month.
Why is my overall margin different from the average of regional margins?
Because the average of ratios is not the ratio of sums. Define margin as MEASURE(gross_margin) / MEASURE(net_revenue) so the engine sums before dividing.
Sources
Checked against (11 Nov 2026)
- Databricks: Unity Catalog metric views and the YAML reference
- Materialization · Advanced techniques · Relationships · BI tools · Feature availability
- Row filters and column masks · ABAC evaluation through views (KB)
- dbt semantic models · MetricFlow open-sourced · Apache Ossie
Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.