skip to content

How would you plan a three-year backfill so it neither starves the daily schedule nor explodes cost?

level: principalimportance: should knowfreq 38%

answer

  1. 1095 runs is a capacity question
  2. protect the nightly schedule first
  3. chunk, isolate, cap the concurrency
  4. build aside, validate, swap at the end

basics

~20 s

Size it first: windows times per-run cost and duration. Then chunk into coarser windows where the transform allows, run it on isolated compute with a hard concurrency cap, checkpoint progress so it resumes, build into a shadow target and swap after validation.

solid answer

~50 s

Treat it as a capacity exercise, not a button. **Size**: 1,095 daily windows times per-run duration and cost gives you the bill and the wall-clock estimate before you commit. **Chunk**: if the transform is partition-parallel, replay history as monthly rather than daily windows — 36 runs instead of 1,095. **Isolate**: separate worker queue and separate warehouse compute so production runs are never behind backfill runs, plus a hard cap on concurrent runs and on read pressure against the source. **Order**: newest-first if the recent data has the most value, strictly sequential if a window depends on its predecessor. **Checkpoint**: record completed windows so a failure at hour nine resumes rather than restarts. **Publish safely**: build into a shadow table and swap atomically after validating row counts and a few business metrics per window, so consumers never read a half-rebuilt mart, and the old table stays as rollback. And suppress notifications and alerting for the duration.

code

sql · 18 lines
sql
-- build history aside, validate, then swap in one step
CREATE TABLE sales_daily__shadow LIKE sales_daily;

-- backfill runs write into the shadow target, chunk by chunk
-- ... 36 monthly chunks ...

-- per-chunk gate before promoting
SELECT month, cnt_new, cnt_old
  FROM (SELECT date_trunc('month', dt) AS month, count(*) AS cnt_new
          FROM sales_daily__shadow GROUP BY 1) n
  FULL JOIN (SELECT date_trunc('month', dt) AS month, count(*) AS cnt_old
               FROM sales_daily GROUP BY 1) o USING (month)
 WHERE cnt_new IS NULL OR cnt_new = 0
    OR abs(cnt_new - cnt_old) > 0.1 * cnt_old;

-- promote atomically; old table remains for rollback
ALTER TABLE sales_daily RENAME TO sales_daily__prev;
ALTER TABLE sales_daily__shadow RENAME TO sales_daily;

go deeper

for a junior

Understand that a backfill is many runs, not one, and that running them all at once competes with the pipelines that keep today's data fresh.

for a middle

Be able to do the sizing arithmetic and name the levers: coarser chunks, a concurrency cap, a separate queue, and resuming from recorded progress rather than restarting.

for a senior

Show operational judgment — isolating compute so production is untouched, capping read pressure on the source, ordering newest-first versus sequential, and validating each chunk before anything is promoted.

for a principal

Own the policy: who may backfill what, what size needs review, whether restated history is communicated or versioned instead, and the investment case for making the backfill path cheap so teams stay willing to change transformation logic.

## Start with arithmetic, not with a button Three years of daily windows is 1,095 runs; hourly is over 26,000. Before anything else, measure one representative run: how long it takes, how much compute it consumes, how much it reads from the source. Multiply. That single calculation decides everything else, and it routinely reveals that the naive plan costs more than the quarter's entire warehouse budget or would take eleven days of wall clock. It also reframes the conversation with stakeholders. "This backfill is four days and roughly this much compute" is a decision someone can make. "I kicked off a backfill" is not. ## Chunk the history A daily schedule exists because that is the cadence at which fresh data is wanted, not because the transform must run per day. If the computation is partition-parallel — each output row derives from its own window's input, with no carry-over — replay history in coarser windows: monthly, or quarterly. Thirty-six runs instead of a thousand cuts per-run overhead (cluster spin-up, planning, task scheduling) that often dominates the actual work on small daily slices. The qualifier matters. If window N reads window N-1's output — running balances, state carried forward, incrementally built dimensions — you cannot chunk and you cannot parallelise. Either constrain the backfill to strictly sequential execution, or restructure the transform so a window is computable from source alone, which is usually the better long-term investment. ## Isolate so production is untouched Backfill runs and scheduled runs must not compete. Practical separations, in rough order of importance: - **Execution slots** — a dedicated queue or worker pool for backfill runs, so the nightly schedule never queues behind history. - **Compute** — a separate warehouse cluster or job cluster, so a heavy replay does not slow interactive queries and so its cost is separately attributable. - **Source pressure** — a hard cap on concurrent reads against the upstream system. An operational database is the most common casualty of a backfill: nobody budgeted for 200 simultaneous extracts against the primary. - **Concurrency cap** — a ceiling on active backfill runs, tuned from the measured per-run resource profile rather than set to "as fast as possible". ## Order deliberately Newest-first delivers value early and lets you abort after a day with the most-used history already correct. Oldest-first is right when consumers need contiguity from the beginning, or when later windows depend on earlier ones. Strictly sequential is mandatory for carry-forward computations. Pick consciously and state the choice in the plan, because the default is whatever the tool does and that is rarely what you wanted. ## Make it resumable A multi-day backfill will be interrupted — a spot instance reclaimed, a credential rotated, someone's laptop. Record per-window completion in a durable place so the operation resumes at the boundary instead of restarting. This is nearly free when every window is an independent idempotent run, and it is the difference between an interruption costing an hour and costing the whole job. ## Publish through a shadow, not in place Rebuilding a published table in place means every consumer reads a partially rebuilt dataset for the duration — dashboards showing 2023 under the new logic and 2024 under the old, or missing entirely. Build the recomputed history into a shadow table, validate, then swap in a single metadata operation. Benefits compound: consumers see one instant transition, rollback is a swap back, and you get a window to compare old and new side by side. Validation should be per chunk and cheap: row counts against the existing table, a handful of business aggregates (revenue by month, distinct customers), and an explicit check that no window came back empty. Empty results from an aged-out source are the failure mode that quietly destroys history, and only a positive check catches it. ## Suppress the noise For the duration: notifications off, freshness and volume alerting suppressed for the affected datasets, and downstream triggering paused so consumers do not fan out once per window. Announce the window to consumers if the target is published; a backfill that changes historical numbers is a communication event, because someone has a screenshot of the old figure. ## The policy layer Beyond this one operation, decide as a lead: who may launch a backfill and against which datasets; what size threshold requires review; whether backfills always route to isolated compute by default; how restated history is communicated to consumers; and whether published marts get versioned rather than restated when the change is large enough. Then invest in making the path cheap — because the real long-term cost of a painful backfill process is not the compute, it is that engineers stop changing transformation logic they know they cannot safely re-apply to history.

  • When can you not replay history in coarser chunks?
    When a window's output depends on the previous window's output — running balances, carry-forward state, incrementally built dimensions. Those must run sequentially in order, so chunking and parallelism are both off the table. The durable fix is restructuring the transform so each window is computable from source alone, which makes every future backfill cheap.
  • Why build a large backfill into a shadow table rather than replacing windows in place?
    Because an in-place rebuild leaves consumers reading a half-rebuilt dataset for hours or days, with some periods under new logic and some under old. A shadow build lets you validate the whole thing, swap in one metadata operation so the transition is instant, keep the old table as rollback, and compare old versus new before committing.
  • What single validation catches the worst backfill outcome?
    An explicit non-empty and plausible-volume check per chunk. If the source has aged out, been mutated, or a credential silently returned nothing, the replay computes an empty result and a replacing write erases good history. Comparing each chunk's row count and a business aggregate against the existing table turns that silent destruction into a stopped job.

saying these in an interview costs you the question

  • Launches the backfill at full concurrency alongside the nightly schedule
  • Skips the arithmetic on run count, duration and cost
  • Rebuilds a published table in place while consumers read it
  • Parallelises windows whose computation carries state forward
  • Has no resume point, so an interruption restarts the whole job

context

resolving links…