Why Pre-Aggregate?

Concepts covered: Pre-Aggregation

One dashboard, 2.4 billion rows

Take a simplified model of that team's data at Uber. Every completed trip is 1 row in trips: trip_id, rider_id, driver_id, country, city, vehicle_type, started_at, fare, distance_km. After 6 years the table holds 2.4 billion rows, about 900GB on disk. The dashboard charts trips and fare per city per day for the last 90 days, and this is the query behind it.

SELECT
  city,
  date_trunc('day', started_at) AS trip_date,
  count(*) AS trip_count,
  sum(fare) AS fare_total
FROM trips
WHERE started_at >= date '2026-07-25'
GROUP BY
  city,
  date_trunc('day', started_at)

The engine reads every row from the last 90 days, about 100 million of them, groups them into 300 cities by 90 days, and returns 27,000 rows. On a shared cluster that takes 40 seconds. Each morning 40 analysts open the dashboard, each tab re-runs the query, and every filter click (one city, one week) re-runs it again with a narrower WHERE clause. The same 100 million rows are read hundreds of times a day to produce the same 27,000 numbers.

Compute once, read many

Pre-aggregation moves that GROUP BY out of the dashboard and into a scheduled job. The job runs once a night, writes its result to a summary table, and every dashboard query reads the summary instead of the raw table.

CREATE TABLE trips_daily_city AS
SELECT
  city,
  date_trunc('day', started_at) AS trip_date,
  count(*) AS trip_count,
  sum(fare) AS fare_total
FROM trips
GROUP BY
  city,
  date_trunc('day', started_at)

One row now means: every trip that started in one city on one day. The table holds 657,000 rows (300 cities by 2,190 days), the dashboard query becomes a filter over it, and the 2.4 billion raw rows are never touched after the nightly job finishes. A filter for Berlin in the last week reads 7 rows and returns in under a second.

SELECT
  city,
  trip_date,
  trip_count,
  fare_total
FROM trips_daily_city
WHERE trip_date >= date '2026-07-25'

• 3,650x
• 30MB

What you give up

If next month's questions group by a column this month's never used, the summary cannot answer them, and the job has to be changed and rerun.

A product manager asks why the nightly summary job cannot be dropped and the dashboard pointed straight at trips on a bigger cluster. Which answer is right?

• A bigger cluster cannot run a GROUP BY over 100 million rows, so the summary table exists because the raw query would fail to return at all.
• The dashboard tool can only read tables with fewer than 1 million rows, so the summary exists to fit under that limit rather than to save work.
• A bigger cluster makes each query faster, but every refresh still reads the same 100 million rows to reproduce the same 27,000 numbers, so the repeated work stays and the bill grows with it.
• The summary exists because trips is deleted after 90 days, so the nightly job is the only place where the older history survives.

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.