How do you serve sub-minute-fresh results from a rollup table that only refreshes hourly?

seniornice to knowfreq 30%

answer

settled history versus the live tail
two branches, one exclusive boundary
the seam is where bugs live
only additive measures survive the union
the tail scan must prune to recent partitions

basics

~20 s

Union the rollup for all closed periods with an on-the-fly aggregate of the fact table for the open tail, splitting on an exclusive boundary so nothing is counted twice, then re-aggregate the union. It works only for additive measures and only if the tail scan prunes to recent partitions.

solid answer

~50 s

Serve the query from two sources and combine them: the rollup covers everything up to its refresh boundary, and a live aggregate over the fact table covers only the rows after it. The boundary predicate must be exclusive on one side — the rollup takes ts < boundary, the tail takes ts >= boundary — or the overlap period is double counted. Then re-aggregate the union with the same additive rules a coarser rollup read would use, which means the pattern is safe for sums, counts, minima and maxima and unsafe for stored averages or distinct counts. It stays cheap only because the tail predicate prunes the fact table to its newest partitions, so time partitioning or clustering is a precondition rather than a nicety. Hide the whole thing behind a view so no consumer reimplements the boundary logic, and drive the boundary from the refresh's recorded watermark rather than a hard-coded interval.

Serve the query from **two sources and combine them**: the rollup covers everything up to its refresh boundary, and a live aggregate over the fact table covers only the rows after it. The boundary predicate must be **exclusive on one side** — the rollup takes `ts < boundary`, the tail takes `ts >= boundary` — or the overlap period is double counted. Then re-aggregate the union with the same additive rules a coarser rollup read would use, which means the pattern is safe for sums, counts, minima and maxima and unsafe for stored averages or distinct counts. It stays cheap only because the tail predicate prunes the fact table to its newest partitions, so time partitioning or clustering is a precondition rather than a nicety. Hide the whole thing behind a view so no consumer reimplements the boundary logic, and drive the boundary from the refresh's recorded watermark rather than a hard-coded interval.

code

sql · 15 lines

sql

-- One shared view: settled rollup plus live tail
CREATE VIEW v_orders_fresh AS
WITH b AS (SELECT watermark_ts AS boundary FROM agg_refresh_state
           WHERE agg_name = 'agg_orders_hourly')
SELECT bucket_hour, country, sum_amount, row_count
FROM agg_orders_hourly, b
WHERE bucket_hour < b.boundary
UNION ALL
SELECT date_trunc('hour', order_ts) AS bucket_hour,
       country,
       sum(amount) AS sum_amount,
       count(*)    AS row_count
FROM fact_orders, b
WHERE order_ts >= b.boundary
GROUP BY 1, 2;

go deeper

for a junior

Know the idea: read the pre-computed table for older data and compute the most recent slice directly from the raw table, then combine the two so results look up to date.

for a middle

Explain the half-open boundary that prevents double counting, why only re-aggregatable measures survive the union, and why the live branch is cheap only when the fact table prunes on the time predicate.

for a senior

Show the seam failure modes — late rows behind the boundary, non-atomic refresh, drifting metric definitions between the two branches — and how you would publish the boundary from the refresh watermark.

for a principal

Decide whether freshness should be bought this way at all versus refreshing more often, streaming into the aggregate, or relaxing the freshness SLA, and own the single shared definition so consumers cannot disagree.

The problem

A rollup answers a dashboard in a fraction of the cost of scanning the fact table, but it is only as fresh as its last refresh. Refreshing more often costs more compute and, past a point, cannot keep up at all. The pattern that resolves the tension is to stop treating the rollup as the answer and treat it as the answer for the settled part of history, with the recent tail computed live.

The construction

Three pieces:

A boundary. A single timestamp separating what the rollup covers from what it does not. It must come from the refresh job itself — the watermark it recorded when it last ran — not from a hard-coded now() - 1 hour, because a delayed refresh would silently leave a gap.
The historical branch. Read the rollup for periods strictly before the boundary.
The live branch. Aggregate the fact table for rows at or after the boundary, producing rows with the same grain and the same measure columns as the rollup.

Then union the two and re-aggregate. Because both branches emit the same shape, the outer aggregation is just the same coarser-grain read you would have done against the rollup alone.

The three things that make it correct

Exclusive boundary. Exactly one branch may claim any given row. Writing ts <= boundary on one side and ts >= boundary on the other double counts the boundary instant; writing < and > loses it. Pick half-open intervals and use them consistently.

Additive measures only. The union is a re-aggregation, so the same rule from coarser-grain reads applies: sums, counts, minima and maxima combine; averages must be carried as a sum and a count and divided at the very end; distinct counts cannot be combined at all without mergeable sketch state. A distinct-user metric served this way will be wrong for any user active in both the settled and the live region.

A cheap tail. The whole design assumes the live branch reads a tiny slice. That is only true if the fact table is partitioned or clustered on the same timestamp so the predicate prunes to the newest partitions. On an unpartitioned fact table the live branch scans everything and the rollup buys you nothing.

Late arrivals at the seam

The uncomfortable case is a row that arrives now but whose event timestamp falls before the boundary. The live branch excludes it by predicate, and the rollup was built before it existed, so it is invisible until the region is recomputed. Two mitigations: drive the boundary from load time rather than event time so nothing can slip behind it, or overlap deliberately — let the live branch cover a lateness window and have the rollup exclude the same window, accepting a slightly larger tail scan in exchange for correctness.

Operational notes

Expose it as a view. If each dashboard writes its own union, the boundary logic will drift between them and two dashboards will disagree. One view, one definition, everybody reads the same number.
Watch behaviour during refresh. As the boundary advances, rows migrate from the live branch to the historical one. If the refresh is not atomic, a query can see a period in neither branch or in both. Publish the new boundary only after the rollup rows for that period are committed.
Know when the engine does this for you. Some analytical engines implement exactly this internally, combining a materialized aggregate with the base rows written since its last refresh, which is why they can rewrite queries onto a slightly stale aggregate without returning stale numbers. Where that exists, use it rather than hand-rolling — the hand-rolled version is for when it does not, or when the boundary policy has to be yours.
Two code paths, one metric. The measure definition now exists in the refresh job and in the live branch. Generate both from one definition if you can; otherwise a change to one and not the other produces a discontinuity exactly at the seam, which is a genuinely nasty bug to chase.

The honest summary for an interview: it is a real, widely used pattern that decouples freshness from refresh cost, and its failure modes are all at the seam — double counting, missing late rows, and non-additive measures.

## The problem A rollup answers a dashboard in a fraction of the cost of scanning the fact table, but it is only as fresh as its last refresh. Refreshing more often costs more compute and, past a point, cannot keep up at all. The pattern that resolves the tension is to stop treating the rollup as the answer and treat it as the answer *for the settled part of history*, with the recent tail computed live. ## The construction Three pieces: 1. **A boundary.** A single timestamp separating what the rollup covers from what it does not. It must come from the refresh job itself — the watermark it recorded when it last ran — not from a hard-coded `now() - 1 hour`, because a delayed refresh would silently leave a gap. 2. **The historical branch.** Read the rollup for periods strictly before the boundary. 3. **The live branch.** Aggregate the fact table for rows at or after the boundary, producing rows with the same grain and the same measure columns as the rollup. Then union the two and re-aggregate. Because both branches emit the same shape, the outer aggregation is just the same coarser-grain read you would have done against the rollup alone. ## The three things that make it correct **Exclusive boundary.** Exactly one branch may claim any given row. Writing `ts <= boundary` on one side and `ts >= boundary` on the other double counts the boundary instant; writing `<` and `>` loses it. Pick half-open intervals and use them consistently. **Additive measures only.** The union is a re-aggregation, so the same rule from coarser-grain reads applies: sums, counts, minima and maxima combine; averages must be carried as a sum and a count and divided at the very end; distinct counts cannot be combined at all without mergeable sketch state. A distinct-user metric served this way will be wrong for any user active in both the settled and the live region. **A cheap tail.** The whole design assumes the live branch reads a tiny slice. That is only true if the fact table is partitioned or clustered on the same timestamp so the predicate prunes to the newest partitions. On an unpartitioned fact table the live branch scans everything and the rollup buys you nothing. ## Late arrivals at the seam The uncomfortable case is a row that arrives *now* but whose event timestamp falls before the boundary. The live branch excludes it by predicate, and the rollup was built before it existed, so it is invisible until the region is recomputed. Two mitigations: drive the boundary from **load time** rather than event time so nothing can slip behind it, or overlap deliberately — let the live branch cover a lateness window and have the rollup exclude the same window, accepting a slightly larger tail scan in exchange for correctness. ## Operational notes - **Expose it as a view.** If each dashboard writes its own union, the boundary logic will drift between them and two dashboards will disagree. One view, one definition, everybody reads the same number. - **Watch behaviour during refresh.** As the boundary advances, rows migrate from the live branch to the historical one. If the refresh is not atomic, a query can see a period in neither branch or in both. Publish the new boundary only after the rollup rows for that period are committed. - **Know when the engine does this for you.** Some analytical engines implement exactly this internally, combining a materialized aggregate with the base rows written since its last refresh, which is why they can rewrite queries onto a slightly stale aggregate without returning stale numbers. Where that exists, use it rather than hand-rolling — the hand-rolled version is for when it does not, or when the boundary policy has to be yours. - **Two code paths, one metric.** The measure definition now exists in the refresh job and in the live branch. Generate both from one definition if you can; otherwise a change to one and not the other produces a discontinuity exactly at the seam, which is a genuinely nasty bug to chase. The honest summary for an interview: it is a real, widely used pattern that decouples freshness from refresh cost, and its failure modes are all at the seam — double counting, missing late rows, and non-additive measures.

Why should the boundary come from the refresh job rather than from now() minus an interval?
Because a hard-coded interval assumes the refresh ran on time. If it is delayed or fails, the rollup no longer covers up to that point and the live branch does not start early enough, leaving a silent gap in the middle of the result. Publishing the watermark the refresh actually reached makes the boundary self-correcting: a late refresh simply widens the live branch and the numbers stay complete.
What happens to this pattern when the metric is a distinct count?
It breaks. A user active both before and after the boundary is counted once in each branch, so the union over-counts. You cannot repair that from the stored scalar. Either keep mergeable sketch state in both branches and accept approximation, or drop the pattern for that metric and compute it directly from the fact table over the whole window.
How do you keep the two branches from drifting apart as the metric definition evolves?
Generate both from a single definition — a macro, a templated model, or a shared view that the refresh job materializes and the live branch executes directly. When they are maintained separately, a change applied to only one produces a step change exactly at the boundary, which looks like a data incident rather than a code bug and takes far longer to diagnose.

saying these in an interview costs you the question

Uses an inclusive predicate on both branches and double counts the seam
Hard-codes the boundary as now() minus the refresh interval
Applies the pattern to a distinct-count metric
Forgets the fact table must prune on the tail predicate
Lets each dashboard build its own union instead of sharing a view

How do you serve sub-minute-fresh results from a rollup table that only refreshes hourly?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The problem

The construction

The three things that make it correct

Late arrivals at the seam

Operational notes

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

The problem

The construction

The three things that make it correct

Late arrivals at the seam

Operational notes

answer

go deeper

context