Additive Measures

Not all numbers can be summed; getting additivity wrong produces dashboards that lie

Not all numbers can be summed; getting additivity wrong produces dashboards that lie

Category

Data Modeling

Difficulty

advanced

Duration

25 minutes

Challenges

0 hands-on challenges

Topics covered: "Can You Sum This Metric Across Dimensions?", Fully Additive: Revenue, Quantity, Cost, Semi-Additive: Balances and Snapshots, Non-Additive: Ratios, Percentages, Distinct Counts, Designing Fact Tables for Correct Aggregation

Lesson Sections

1. "Can You Sum This Metric Across Dimensions?"

   The Three Classifications What They're Really Testing The 60-Second Framework Step 4 is the strong-hire signal. Saying 'I would not store conversion_rate in the fact table; I would store clicks and impressions, and let the BI tool compute the ratio' shows you design for correct aggregation, not just convenience. Why Companies Care Cite these in your answer: 'At a major bank, a dashboard SUMmed daily balances across a month. The result was 30x the actual balance. The CFO reported it to the board

2. Fully Additive: Revenue, Quantity, Cost

   Fully additive measures are the simplest and most common. SUM them across any dimension and the result is meaningful. Revenue summed by product, by region, by month, or by any combination is always correct. This is the default case, and most candidates handle it well. The interview signal is not knowing that revenue is additive. It is knowing where the boundaries are. The Easy Ones: Confirm Them Fast and Move On The Traps: Metrics That Look Additive but Break on Rollup The strongest interview mo

3. Semi-Additive: Balances and Snapshots

   Your semi-additive answer: 'Semi-additive measures are the most dangerous because they SUM correctly across most dimensions. Balance summed across accounts on March 15 is correct. Balance summed across January through March is 90x the actual balance. The trap is that it LOOKS right until someone checks against the source.' Say 'looks right until someone checks.' That is what makes semi-additive measures dangerous. The Rule You Must State Immediately State the rule in one sentence: 'Balance is a

4. Non-Additive: Ratios, Percentages, Distinct Counts

   The interviewer gives you a table with CTR (click-through rate) as a column and asks you to roll it up by product category. This is a trap. Pre-computed ratios break on every rollup. The candidate who says 'I would not store CTR in the fact table; I would store clicks and impressions and compute the ratio at query time' passes. The candidate who says 'just average the CTRs' fails, because that produces a mathematically wrong result. Why the Candidate Who Stores CTR Fails Set up the trap: 'Two pr

5. Designing Fact Tables for Correct Aggregation

   The strongest closing move in an additive measures interview is showing you design fact tables that make incorrect aggregation impossible. Not 'document the rules' but 'store only additive components so there is no way to get it wrong.' This is the difference between a schema that works when people follow the rules and a schema that works regardless. The Checklist You Walk Through in the Interview A Complete Example The table comment IS the documentation. Putting grain and additivity rules in th