Part II · Five Failures · Chapter 3

Measure Types

A SUM that runs and returns a number is not the same as a SUM that means something. Additivity and grain are type-level properties of measures, and violating them produces numbers that are precise, plausible, and inflated by exactly the factor you won’t notice.

The second and third lies in the $7.2M answer were arithmetic errors that passed every syntax check. The agent summed three monthly snapshots of a stock measure (inflating by 3×), then aggregated across a join whose grain was finer than the measure (inflating by 3.4×). Together: roughly 10× the true value, from valid SQL.

This chapter treats both as instances of the same underlying problem: the aggregation is illegal given the measure’s type. The fix is a metric registry that encodes additivity and grain as properties of the measure rather than properties of the query, and a validator that rejects re-aggregations that violate them.

3.1 Stocks, flows, and semi-additivity

Every quantitative business measure falls into one of three classes:

Class	Definition	Example	SUM across time?
Additive	Sums meaningfully across all dimensions	Revenue, cost, units sold	Yes
Semi-additive	Sums across some dimensions but not others	ARR, headcount, inventory on-hand, account balance	No — take period-end or average
Non-additive	Cannot be summed across any dimension; must be recomputed from base facts	Conversion rate, P95 latency, NPS score, average resolution time	No

The rule: semi-additive measures are stocks, not flows. They represent a state at a point in time. ARR at $400K in May, June, and July is $400K of ARR, not $1.2M. An account at $400K ARR in each of three months has $400K at risk, not $1.2M — and the error is exactly 3×, which for a slightly smaller company still looks like a plausible ARR figure.

Three shapes of the same bug

-- Average of averages. Wrong unless every group is the same size.
SELECT AVG(daily_avg_resolution_hours) FROM daily_ticket_stats;

-- Sum of rates. Meaningless.
SELECT SUM(conversion_rate) FROM funnel_by_channel;

-- P95 of P95s. Not a percentile of anything.
SELECT AVG(p95_latency) FROM service_latency_hourly;

Each is syntactically perfect and semantically void. No linter catches them. Execution accuracy catches them only if someone happened to write a golden query for that exact question.

3.2 The metric registry

The fix is to encode the measure’s type in a registry that the query layer resolves through:

measures:
  arr:
    description: "Annual Recurring Revenue"
    agg: sum
    additivity: semi_additive
    non_summable_dimensions: [month, date]
    time_rule: last   # take period-end snapshot, don't sum
    grain: [account_id, month]

  resolution_time_p95:
    description: "95th percentile ticket resolution time"
    agg: percentile(95)
    additivity: non_additive
    recompute_from: ticket_events  # must go to base facts
    grain: [ticket_id]

  conversion_rate:
    description: "Sessions that converted to purchase"
    agg: ratio
    numerator: conversions
    denominator: sessions
    additivity: non_additive
    grain: [session_id]
    # Compiles to SUM(conversions)/SUM(sessions) at target grain,
    # NOT AVG(precomputed_rate)

When the query layer encounters a request to aggregate a measure, it consults the registry:

1. Is the requested aggregation compatible with the measure’s additivity class?
2. Is the aggregation dimension in the measure’s non_summable_dimensions?
3. If the measure is a ratio, does the plan compute SUM(num)/SUM(den) or AVG(precomputed)?

Violations are compile errors — the query is rejected before execution, not warned after.

3.3 Grain and fan traps

The third lie: revenue was counted 3.4 times because a join introduced spurious multiplicity.

SELECT a.account_id, SUM(o.order_amount) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN accounts a ON a.id = o.account_id
GROUP BY 1;

The order_items join was added to filter on product category, but order_amount lives at the order grain. Each order now appears once per line item. With an average of 3.4 items per order, revenue inflates 3.4×.

The nasty part: it inflates unevenly. Accounts that buy more SKUs per order look disproportionately large. The ranking changes, not just the magnitude. So the “top accounts” list is wrong in a way that survives a sanity check on the total, and wrong in a direction that correlates with product breadth — exactly the kind of correlation a human will find a story for.

Grain as a type

Every table and every intermediate result has a grain — the set of columns that uniquely identifies a row:

grains:
  orders:       {order_id}
  order_items:  {order_id, line_item_id}
  accounts:     {account_id}
  monthly_arr:  {account_id, month}

When you join two tables, the grain of the result is the union of their keys (for an inner join on a shared key). If the result grain is finer than the measure’s declared grain, a fan-out has occurred:

join(orders, order_items) on order_id
  → result grain = {order_id, line_item_id}
  → order_amount.grain = {order_id}
  → FANOUT DETECTED: result grain ⊋ measure grain

Aggregating a fanout result set is an error unless you either:

1. Pre-aggregate the finer side down to the coarser grain before joining, or
2. Deduplicate symmetrically (Looker’s symmetric aggregates approach)

Option 1 is preferred because the resulting query plan is readable and doesn’t depend on dialect quirks:

-- Correct: filter at item grain, then join pre-aggregated
WITH filtered_orders AS (
  SELECT DISTINCT order_id
  FROM order_items
  WHERE category = 'Enterprise'
)
SELECT a.account_id, SUM(o.order_amount) AS revenue
FROM orders o
JOIN filtered_orders fo ON fo.order_id = o.order_id
JOIN accounts a ON a.id = o.account_id
GROUP BY 1;

3.4 Implementation: the grain checker

from dataclasses import dataclass

@dataclass(frozen=True)
class Grain:
    keys: frozenset[str]

    def is_subset_of(self, other: "Grain") -> bool:
        return self.keys <= other.keys

    def join_with(self, other: "Grain", join_keys: set[str]) -> "Grain":
        """Grain of an inner join result."""
        return Grain(self.keys | other.keys)

    def has_fanout_for(self, measure_grain: "Grain") -> bool:
        """True if aggregating this measure over this result is unsafe."""
        return not measure_grain.keys <= self.keys
        # Unsafe when result grain is FINER than measure grain

@dataclass
class Measure:
    name: str
    grain: Grain
    additivity: str  # "additive", "semi_additive", "non_additive"
    non_summable: frozenset[str] = frozenset()
    time_rule: str = "sum"  # "sum", "last", "average"

def validate_aggregation(measure: Measure, result_grain: Grain,
                         agg_function: str, group_dims: set[str]) -> str:
    """Returns None if valid, or an error message."""
    # Check 1: fan-out
    if not measure.grain.keys <= result_grain.keys:
        # Result is coarser than measure — this is fine (rolling up)
        pass
    if result_grain.keys > measure.grain.keys:
        return (f"Fan-out detected: result grain {result_grain.keys} is finer "
                f"than measure grain {measure.grain.keys}. "
                f"Pre-aggregate or deduplicate before aggregating.")

    # Check 2: additivity
    if measure.additivity == "non_additive" and agg_function in ("sum", "avg"):
        return (f"Measure '{measure.name}' is non-additive. "
                f"Cannot apply {agg_function}. Recompute from base facts.")

    # Check 3: semi-additive over forbidden dimension
    if measure.additivity == "semi_additive":
        forbidden = measure.non_summable & group_dims
        if forbidden and agg_function == "sum":
            return (f"Measure '{measure.name}' is semi-additive. "
                    f"Cannot SUM across {forbidden}. "
                    f"Use time_rule='{measure.time_rule}' instead.")

    return None  # valid

3.5 Ratio measures and the average-of-averages trap

Ratios deserve special treatment because the correct aggregation is never AVG(precomputed_rate) — it is always SUM(numerator) / SUM(denominator) at the target grain.

Consider conversion rate, defined as conversions / sessions. If Channel A has 10 conversions out of 100 sessions (10%), and Channel B has 5 out of 20 sessions (25%), the overall rate is 15/120 = 12.5%, not the average of 10% and 25% (17.5%). The average-of-averages is weighted equally by group, not by volume, and is always wrong unless all groups have identical denominators.

The registry encodes this:

conversion_rate:
  agg: ratio
  numerator: conversions
  denominator: sessions
  compile_to: "SUM(conversions) / NULLIF(SUM(sessions), 0)"

When the query planner encounters a request for “conversion rate by region,” it doesn’t look for a precomputed conversion_rate column and average it. It compiles to SUM(conversions) / SUM(sessions) grouped by region. The difference between these two queries is the difference between a meaningful metric and noise.

3.6 Time intelligence

Semi-additive measures require time intelligence: the query layer must know how to handle the time dimension correctly without being told each time.

The registry’s time_rule field specifies the behavior:

Rule	Behavior	Typical measures
last	Take the last value in the period	ARR, headcount, account balance
first	Take the first value in the period	Opening balance
average	Average across the period	Average inventory, average DAU
max	Peak value in the period	Peak concurrent users

When an agent asks “What is our ARR this quarter?” and the query spans May–July, the compiled query automatically resolves to the July value (the last month in the range), not the sum. This happens at the IR level, before SQL generation, which means the model never needs to know the rule — it is enforced structurally.

---

Exercises

1. Build the full metric registry validator. Given a YAML registry file and a proposed aggregation (measure name, aggregate function, grouping dimensions, filter dimensions), return either “valid” with the compiled SQL fragment, or “rejected” with a specific error message. Test it against these cases:
   • SUM(arr) grouped by account_id → valid
   • SUM(arr) grouped by account_id, month → rejected (sum across time)
   • AVG(conversion_rate) grouped by channel → rejected (average of ratios); should suggest the correct SUM/SUM formulation
   • SUM(order_amount) after joining with order_items → rejected (fan-out)
2. Implement grain propagation through a query plan tree. Each join node should compute the result grain from its inputs and the join keys. Each aggregate node should check the measure’s grain against the current result grain and reject if a fan-out is detected.

1. Your warehouse has a pre-aggregated weekly_metrics table with grain {account_id, week}. It contains both additive measures (weekly_revenue) and semi-additive measures (week_end_arr). Design the rules for when the query planner can serve a request from this pre-aggregate vs. when it must fall through to base facts. Implement the grain-subsumption check: the pre-aggregate is usable only if its grain is equal to or coarser than what the query needs, AND the measure’s additivity permits the pre-aggregation that was already applied.
2. A measure net_revenue is defined as gross_revenue - refunds. Both components are additive. Is net_revenue itself additive? What about net_revenue_rate = net_revenue / orders? Design the rules for derived measures — measures computed from other measures — and how their additivity class is inferred from their components.

---