Granularity Design
Concepts covered: Grain Definition
What one row means
The granularity of a summary is the meaning of one row: the set of columns that make a row unique and the time bucket it covers. trips_daily_city has the granularity (city, day). Every design decision about a summary is a decision about granularity, because the granularity fixes both the questions the table can answer and its size.
| Granularity | Rows per day | Rows per year | Answers | Cannot answer |
|---|---|---|---|---|
| (day) | 1 | 365 | company-wide daily trend | anything by city |
| (city, day) | 300 | 109,500 | city trends and rankings | hourly peaks, vehicle mix |
| (city, vehicle_type, hour) | 28,800 | 10.5M | peak hours per city and type | anything about one rider |
| (rider_id, day) | 1.1M | 400M | per-rider daily activity | nothing a raw query cannot do as cheaply |
The last row fails. A high-cardinality column in the granularity gives the summary about as many rows as the raw table, since most riders take 1 trip a day, and a summary the size of its source saves nothing. For 300 cities the third row fits best: it answers every hourly peak question and still sits about 38 times below the raw table, 28,800 rows a day against 1.1 million trips.
Finest grain first, coarser grains from it
Pick the finest granularity any current consumer needs, then derive each coarser table from the one below it rather than from raw. Per night, the hourly table reads about 1.1 million raw trips, the daily table reads 28,800 hourly rows, and the monthly table rebuilds the current month from at most 9,300 daily rows. Each step is a SUM for additive metrics and a merge for sketches; a metric that cannot be rolled up has to be recomputed from raw at every level, and the chain breaks.
- trips (source, 2.4B rows)
- hourly summary (storage, 10.5M rows per year)
- daily summary (storage, 110K rows per year)
- monthly summary (storage, 3.6K rows per year)
- ops dashboard (consumer, reads daily)
- finance report (consumer, reads monthly)
- trips -> hourly summary
- hourly summary -> daily summary
- daily summary -> monthly summary
- daily summary -> ops dashboard
- monthly summary -> finance report
Columns that belong in the granularity
- A column belongs when a dashboard filters or groups by it: city, vehicle_type, hour.
- Leave out a column that gives about 1 raw row per summary row: rider_id makes 1.1M rows a day from 1.1M trips.
- A column with 2,000 values, such as restaurant, can stay if each summary row still covers dozens of raw rows.
- The time bucket is the finest size anyone charts: hour for ops, day for most teams, never minute for a dashboard.
- A derived flag such as is_airport_trip has 2 values, doubles the rows at most, and often replaces a second summary.
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.