Metric Types and Additivity

Concepts covered: Metric Additivity

Roll up: a summary of a summary

The nightly table holds one row per city per day. A monthly report wants one row per city per month, and the cheapest way to produce it reads the daily table rather than the raw trips. Combining finer rows into coarser rows is a roll up, and whether the result is right depends on the metric. Suppose the nightly job also stored distinct_riders and avg_fare for each day.

city	trip_date	trip_count	fare_total	distinct_riders	avg_fare
Austin	2026-10-20	10,000	150,000.00	5,200	15.00
Austin	2026-10-21	18,412	341,207.50	9,800	18.53
Austin	2026-10-22	19,006	352,118.25	10,100	18.53

Summing trip_count across the 3 days gives 47,418, and summing fare_total gives 843,325.75. Both are correct, because each trip belongs to exactly one day and its fare is counted exactly once. A metric with that property is additive: the roll up is a SUM over the finer rows, along any column you like.

Now sum distinct_riders: 25,100. The true 3-day figure is lower, perhaps 19,600, because a rider who took a trip on the 20th and again on the 22nd appears in both daily counts and once in the combined count. Nothing in the daily table can recover the overlap. The avg_fare column fails differently. Summing it gives 52.06, which means nothing, and averaging the 3 averages gives 17.35, which is wrong: the quiet 20th gets the same weight as the busy 21st. The true 3-day average is 843,325.75 divided by 47,418, which is 17.79.

3 kinds of metric

Examples	Kind	How it rolls up
trip_count, fare_total, distance_km	Additive	SUM across any column: city, day, vehicle type
drivers_online, account_balance	Semi-additive	SUM across cities or accounts; last value or average across time
distinct_riders, avg_fare, p95_wait	Non-additive	Cannot be rolled up from the stored number; store its ingredients instead

Semi-additive metrics measure a level at an instant rather than a flow over a period. drivers_online at 9am in Austin plus drivers_online at 9am in Berlin is a real number, the drivers online across both cities. drivers_online at 9am plus drivers_online at 10am counts the same drivers twice. Rolling a snapshot across time means choosing one instant (the last reading of the month) or averaging the readings, and the column name must say which, so a reader of drivers_online_month_end knows what the number is.

Store the parts, never the answer

Non-additive metrics become additive when the summary stores their ingredients. A ratio is a numerator over a denominator, and both are sums. Store fare_total and trip_count, leave avg_fare out, and divide at read time: the monthly average is sum(fare_total) / sum(trip_count), which is the weighted 17.79 for the 3 days above.

SELECT
  city,
  date_trunc('month', trip_date) AS trip_month,
  sum(trip_count) AS trip_count,
  sum(fare_total) AS fare_total,
  sum(fare_total) / sum(trip_count) AS avg_fare
FROM trips_daily_city
GROUP BY
  city,
  date_trunc('month', trip_date)

Distinct counts have no exact ingredient smaller than the full list of ids, but they have an approximate one. A sketch is a small, fixed-size summary of a set of ids. Two sketches can be merged, and the merged sketch estimates the distinct count of the union, within about 2 percent, at a few kilobytes per row. The nightly job stores a sketch of rider ids per row, and every roll up merges them.

SELECT
  city,
  date_trunc('day', started_at) AS trip_date,
  approx_set(rider_id) AS riders_sketch
FROM trips
GROUP BY
  city,
  date_trunc('day', started_at)

SELECT
  city,
  date_trunc('month', trip_date) AS trip_month,
  cardinality(merge(riders_sketch)) AS distinct_riders
FROM trips_daily_city
GROUP BY
  city,
  date_trunc('month', trip_date)

Percentiles have the same shape: the p95 of 30 daily p95 values is not the monthly p95. Store a quantile digest, a sketch of the value distribution that merges the same way, or keep the finer rows and compute the percentile from them.

A daily summary stores conversion_rate as bookings divided by searches. A weekly dashboard averages the 7 daily rates. Why is the weekly figure wrong, and what fixes it?

Each day gets equal weight regardless of how many searches it had, so a quiet Sunday pulls the week as hard as a busy Friday; store bookings and searches and divide their weekly sums.
Averages of rates are always too high because a rate cannot exceed 1; store the rate as a percentage between 0 and 100 and average those values instead.
The daily rate is rounded, so 7 roundings compound into a visible error; store the rate with 6 decimal places and the weekly average becomes exact.
Rates need a sketch the way distinct counts do; store a sketch of booking ids per day and merge the 7 sketches to obtain the weekly rate.

About this lesson

This section is part of the Pre-Aggregation lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.