Refresh Strategies for Aggregates

3 ways to keep a summary current

A summary is a cache of a GROUP BY, and like any cache it goes stale the moment new rows land in the raw table. The refresh strategy decides how much work each update costs and how far behind the raw table the summary may fall.

Full rebuild

Drop the summary and run the CREATE TABLE AS from the first section again. The result is correct by construction, since every raw row is read every time, and the job is 1 statement. Cost is the problem: the run reads 2.4 billion rows tonight and 2.8 billion next year, and a nightly job that already takes 3 hours has no room to grow.

Incremental append

Aggregate only the rows that arrived since the last run and insert them. For a daily summary that means one day of trips, about 1.1 million rows, at a cost that stays flat for years.

INSERT INTO trips_daily_city
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-10-22'
  AND started_at < date '2026-10-23'
GROUP BY
  city,
  date_trunc('day', started_at)

Incremental append breaks on 2 kinds of row. A trip that started at 23:58 on the 22nd but reached trips at 02:10 on the 23rd, after the job ran, belongs to a day that is already summarized, and tomorrow's run filters it out by date, so it is never counted. A job rerun by hand after a failure inserts the 22nd a second time, and the dashboard shows Austin at 38,012 trips instead of 19,006.

Trailing window rebuild

Delete the last N days from the summary, recompute them from raw, and insert. With N = 3 the job reads 3 days of trips, a trip arriving up to 3 days late lands in its proper day on the next run, and running the job twice produces the same table as running it once, because the delete removes whatever the first run wrote.

DELETE FROM trips_daily_city
WHERE trip_date >= date '2026-10-20'

INSERT INTO trips_daily_city
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-10-20'
  AND started_at < date '2026-10-23'
GROUP BY
  city,
  date_trunc('day', started_at)

Pick N from the data by measuring how late trips actually arrive. If 99.9 percent land within 6 hours and the rest within 2 days, N = 3 covers them, and N = 30 reads 10 times the rows for no gain. A trip later than N days needs a backfill: the same 2 statements with an older date, which is why the job takes its start date as a parameter.

Trust the numbers after the job

A refresh that fails without a signal is worse than no refresh, because nobody notices the missing or half-counted day. Most failures show up in 2 checks. First, the summary's trip_count for the latest day must equal a count(*) on the raw table for the same day exactly, at the cost of one scan of 1.1 million rows, and any difference pages the owner. Second, compare the day against the same weekday last week, where a gap above 20 percent pages the owner before anyone reads the number.

The trips_daily_city job appends one day per run, with the date as a parameter. Tuesday's 02:00 run inserted Monday's rows, then reported a failure in a later step. On Wednesday morning, after the normal run had appended Tuesday, an engineer reran the job for Monday's date. How do Monday and Tuesday look in the summary?

• Monday once and Tuesday once, because the INSERT skips rows whose city and trip_date already exist in the summary.
• Monday twice and Tuesday once: the rerun recomputed and inserted Monday again, and nothing in an append removes the first copy.
• Monday missing and Tuesday once, because the failed run removed Monday's rows when it stopped and the rerun started from Tuesday.
• Monday twice and Tuesday twice, because every run inserts the 2 most recent days to cover late arrivals and nothing removes the first copies.

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.