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

Order header joined to its 3 linesorder 101freight = 100line 1line 2line 3SUM(freight) after the join = 300the truth is 100The fixkeep each measure at its own grain:freight lives on the header tablemetric view on headers, lines joinedwith cardinality: one_to_manySUM(freight) = 100, line counts still work

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 grain

When 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

Additiveadd across every slicenet revenueunits soldline countSemi-additiveadd across some, not timestock on handaccount balanceheadcountNon-additivenever add piecesmargin %distinct dealersmedian priceKnow which jar a measure is in before you pre-aggregate it, or the numbers lie.

Which jar a measure is in decides how it may be summed and pre-aggregated.

KindExamplesAdds acrossHow to model it
AdditiveSUM, COUNTAny slice, any rollupSafe to pre-aggregate at any grain.
Semi-additivestock on hand, balancesAcross dealers yes, across days noWindow measure with range: current and semiadditive: last.
Non-additiveratios, COUNT(DISTINCT), median, percentilesNever from piecesCompose 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 days

A 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 revenueGross marginMargin %
North1001010%
South90027030%
Average of the two ratios20% (wrong)
Ratio of the sums100028028% (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

SituationSetupVerdict
Sales at invoice-line grain, targets at month × regionTwo 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 lineTargets get multiplied by the line countDrop
Stock snapshots and sales in one metric viewDifferent grains in one source; one of them fans outDrop
Header amounts (freight) with line amountsMetric view on headers with lines as one_to_many, or allocate freight to lines in goldPursue

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)

Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.