skip to content

Which measure columns belong in an aggregate fact table so further rollups stay correct?

level: middleimportance: should knowfreq 45%

answer

  1. what happens when you average the averages?
  2. numerator and denominator, separately
  3. one customer, ten days, one month
  4. additive, semi-additive, non-additive
  5. components stored, ratios derived at query time

basics

~20 s

Store additive components — sums and counts — never stored averages or percentages. Ratios are recomputed at query time from the components; distinct counts do not roll up at all and need the atomic table or a sketch column.

solid answer

~60 s

The rule is: **store the numerator and the denominator, not the ratio**. A daily aggregate should carry `revenue_amount` and `order_count`, and let the semantic layer or the report compute average order value as one divided by the other. If you store `avg_order_value` instead, anyone rolling days up to a month gets an unweighted average of daily averages — a number that answers no question and quietly disagrees with the atomic table. The same discipline applies to percentages, margins and rates: they are non-additive, so they belong in the presentation layer, computed from additive stored components. Distinct counts are the genuinely hard case. `distinct_customers` per day cannot be summed to a month, because a customer active on ten days would be counted ten times. Either serve monthly distincts from the atomic fact table, or store a mergeable sketch column that can be combined — and be explicit that it is approximate. Semi-additive measures such as balances need care too: they add across product and store but not across time, so an aggregate must summarize them at a period boundary rather than summing them.

code

sql · 22 lines
sql
-- WRONG: the ratio is frozen at day grain
CREATE TABLE agg_sales_day_store_bad (
  date_key        INTEGER NOT NULL,
  store_key       INTEGER NOT NULL,
  avg_order_value DECIMAL(18,2) NOT NULL
);

-- RIGHT: additive components, ratio computed later
CREATE TABLE agg_sales_day_store (
  date_key       INTEGER NOT NULL,
  store_key      INTEGER NOT NULL,
  revenue_amount DECIMAL(18,2) NOT NULL,
  order_count    INTEGER NOT NULL,
  PRIMARY KEY (date_key, store_key)
);

-- monthly average order value, correct at any grain
SELECT d.month_key,
       SUM(a.revenue_amount) / NULLIF(SUM(a.order_count), 0) AS avg_order_value
FROM   agg_sales_day_store a
JOIN   dim_date d ON d.date_key = a.date_key
GROUP  BY d.month_key;

go deeper

for a junior

Remember the rule of thumb: aggregate tables store sums and counts. Percentages and averages are calculated when the report runs, from those stored components.

for a middle

Explain the three additivity grades and demonstrate with numbers why an average of averages diverges from the true weighted figure, then show the component-column fix.

for a senior

Show you audit an existing aggregate for non-additive columns before trusting it, and that you have a stated policy for distinct counts — atomic fallback, per-grain rows, or a disclosed sketch.

for a principal

Own metric definition as a governed asset: components stored once, ratios defined centrally rather than frozen into tables, and an explicit organizational position on approximate distinct counts.

## The design question An aggregate fact table is only useful if reports can roll it up further — days to months, months to quarters, stores to regions. Whether that works is decided entirely by which **measure columns** you chose to store. Get it wrong and the aggregate does not fail loudly; it returns a plausible number that disagrees with the atomic star. ## Additivity, in three grades Dimensional modelling grades measures by how they combine: - **Additive** — can be summed across every dimension, including time. Revenue, quantity, cost. These are the measures aggregates are made of. - **Semi-additive** — can be summed across every dimension *except* time. Account balances, inventory on hand, headcount. Summing January's daily balances gives a meaningless number roughly thirty times too large. - **Non-additive** — cannot be summed across anything. Ratios, percentages, margins, averages, unit prices, distinct counts. An aggregate fact table should be built from additive measures wherever possible, because additive measures survive any further rollup unchanged. ## Store components, compute ratios at query time The most common defect in a real aggregate is a stored average. Suppose `agg_sales_day_store` holds `avg_order_value`. A monthly report averages those daily values: ```text day orders revenue avg_order_value D1 1000 50000 50.00 D2 2 500 250.00 AVG(avg_order_value) = 150.00 <- wrong SUM(revenue)/SUM(orders) = 50.40 <- correct ``` The unweighted mean treats a two-order day as equal in weight to a thousand-order day. The fix is structural, not a warning in the documentation: store `revenue_amount` and `order_count`, and define the metric as `SUM(revenue_amount) / NULLIF(SUM(order_count), 0)` in the layer that serves reports. Then the ratio is correct at every grain, because it is always recomputed from additive components. The same holds for a conversion rate (store `conversions` and `sessions`), a margin percentage (store `revenue` and `cost`) and a weighted average price (store `revenue` and `quantity`). ## Distinct counts do not roll up `COUNT(DISTINCT customer_id)` is the measure that most often breaks an aggregate strategy. A customer who shops on ten days contributes to ten daily rows, so summing daily `distinct_customers` overstates the month badly, and there is no arithmetic on the stored numbers that repairs it — the identity information needed to deduplicate was discarded when the aggregate was built. Three honest options: 1. **Serve distinct counts from the atomic fact table.** Correct, slower, and often perfectly acceptable because these queries are rarer than sums. 2. **Pre-aggregate each grain you actually need** — a separate monthly row carrying `distinct_customers_month`, computed from the atomic table. Correct, but it does not compose: quarterly distincts need their own row again. 3. **Store a mergeable sketch** (a HyperLogLog-style state column), which can be combined across rows to give an approximate distinct count at any grain. Fast and composable, but approximate, and the approximation must be disclosed to whoever reads the number. What is not acceptable is storing `distinct_customers` per day and letting the BI tool sum it. Choose one of the three and document which. ## Semi-additive measures in an aggregate Balances and inventory levels need an explicit decision, because "summarize" does not mean "sum" for them. A monthly inventory aggregate typically stores the **period-end** value (the balance on the last day), and sometimes an average daily balance alongside it as a separate, clearly named column. What it must never do is `SUM(balance)` over the days in the month. Naming carries the semantics: `ending_balance` and `avg_daily_balance` are unambiguous; a column called `balance` in a month-grain table invites exactly the wrong aggregation. ## Keep the counts you will need Always carry a row count — `order_line_count`, `fact_row_count` — even when nobody has asked for it. It costs almost nothing, it is the denominator for any average someone later wants, and it is what a reconciliation query compares against the atomic table to prove no rows were lost. ## Name measures for their grain A column called `revenue_amount` means the same thing at both grains and can keep its name; that consistency is what lets a semantic layer treat the two tables as interchangeable for that metric. But a column whose meaning is grain-specific — `orders_per_day`, `ending_balance` — must say so in its name, so no one sums it by accident. ## What interviewers listen for "Store the components, compute the ratio at query time" is the sentence they want, followed by the recognition that distinct counts are a different problem with no purely arithmetic fix, and that semi-additive measures need a stated rule at period boundaries.

  • How would you support monthly distinct customer counts when only a daily aggregate exists?
    Either compute them from the atomic fact table, pre-aggregate a monthly row with its own distinct count, or store a mergeable sketch column that combines across rows for an approximate answer. Summing daily distinct counts is never an option — a customer active on ten days is counted ten times, and no arithmetic on the stored numbers repairs it.
  • An inventory aggregate at month grain — what do you store for the on-hand quantity?
    The period-end value, and optionally an average daily balance as a separate, explicitly named column. On-hand quantity is semi-additive: it adds across products and warehouses but not across time, so summing daily levels into a month produces a number roughly thirty times too large.
  • Why keep a row count in the aggregate even when no report asks for one?
    It is nearly free, it is the denominator for any average requested later, and it is what a reconciliation query compares against the atomic table to prove the rollup lost no rows. Adding it after the fact means backfilling every period.

saying these in an interview costs you the question

  • Storing an average and letting reports average it again
  • Summing daily distinct counts to get a monthly figure
  • Summing balances across time in a period aggregate
  • Assuming any numeric column can be summed
  • Omitting the row count so no average can be recomputed

context

resolving links…