skip to content

You add a derived column to an incrementally loaded fact table — how do you backfill history?

level: middleimportance: should knowfreq 50%

answer

  1. ask whether the old values still exist
  2. one code path or two?
  3. batches need to be safe to re-run
  4. partially filled columns mislead consumers
  5. the job succeeding is not the finish line

basics

~20 s

Either rebuild the table in full, or run a bounded backfill over historical date ranges in idempotent, re-runnable batches while the nightly incremental load keeps running. Pick by cost, and first check whether the historical source values still exist at all.

solid answer

~50 s

Start with a question, not a job: **can the historical value even be reconstructed?** If the source is mutable and only carries current state, you cannot recover what the attribute was five years ago, and the honest answer is that the column is meaningful only from a stated date onward — document that rather than fabricating it. If history is recoverable, choose between a **full refresh** (simplest, correct by construction, viable while the table is small enough to rebuild inside your window) and a **bounded backfill** that walks historical date ranges in batches. Make each batch idempotent and keyed by the range it covers, so a failure resumes rather than restarts and a re-run cannot duplicate rows. Run it beside — never inside — the nightly incremental load, keep the column nullable until the backfill completes, and finish with a reconciliation query comparing row counts and sums before and after.

code

sql · 20 lines
sql
-- rerunning this range must be a no-op, not a duplication
DELETE FROM fct_orders
WHERE order_date >= DATE '2023-04-01'
  AND order_date <  DATE '2023-05-01';

INSERT INTO fct_orders (order_key, order_date, customer_sk, revenue, size_bucket)
SELECT s.order_key,
       s.order_date,
       d.customer_sk,
       s.revenue,
       CASE WHEN s.revenue >= 1000 THEN 'LARGE'
            WHEN s.revenue >= 100  THEN 'MEDIUM'
            ELSE 'SMALL' END
FROM stg_orders s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND s.order_date >= d.valid_from
 AND s.order_date <  d.valid_to
WHERE s.order_date >= DATE '2023-04-01'
  AND s.order_date <  DATE '2023-05-01';

go deeper

for a junior

Know that adding a column only fixes new rows, and that old rows stay NULL until someone deliberately populates them. Be able to name the two options: rebuild everything, or fill in history in chunks.

for a middle

Explain why a backfill must be idempotent and resumable, how it runs alongside the nightly load without overlapping watermarks, and why a partially filled column should stay hidden from consumers.

for a senior

Show judgment about recoverability: whether the historical inputs still exist, what stamping history with current attributes silently does to trend reporting, and how you reconcile counts and sums before calling it done.

for a principal

Own the policy question — when a derivation should be materialised at all versus computed in the serving view, and what your platform owes consumers in the way of documented validity dates for attributes that only exist from a certain point onward.

## The first question is not "how" but "can I" Adding a derived column to a fact table you load incrementally splits into two very different problems: computing it for new rows (easy — change the transformation) and computing it for the rows already there. Before designing a backfill, establish whether the inputs for history still exist. - **Derived from columns already in the fact row** (a margin computed from price and cost, a bucketed order size): fully recoverable. Pure arithmetic over rows you already hold. - **Derived from a dimension that keeps history**: recoverable *as of the transaction date*, provided the dimension's history goes back far enough. - **Derived from a dimension that keeps only current state, or from a source system that overwrites**: not recoverable. You can stamp history with today's values, but that is not what the value was. The third case is the one candidates get wrong. Backfilling five years of orders with today's customer segment produces a column that is internally consistent and historically false; every trend by segment is then computed as if customers had always been in their current segment. Sometimes that is exactly what the business wants ("show me last year's revenue under today's org structure") — but it must be a stated decision with a name, not an accident of the backfill query. ## Full refresh versus bounded backfill **Full refresh** — rebuild the whole table from source with the new logic. It is the correct-by-construction option: one code path produces every row, so there is no risk of history and new rows disagreeing. Its limits are time, cost, and the fact that a rebuild may re-issue surrogate keys or drop late-arriving corrections that were applied in place. Prefer it whenever the table fits comfortably in your maintenance window. **Bounded backfill** — leave existing rows and update or rewrite them range by range, usually one month or one load-date at a time. This is what you do when a full rebuild is uneconomic. The design requirements are: 1. **Idempotent per range.** Running the same range twice must produce the same result. Delete-and-reinsert the range, or a MERGE keyed on the fact's business key, both work; a blind INSERT does not. 2. **Resumable.** Track which ranges are done in a control table so a failure at range 37 of 60 does not restart the whole job. 3. **Bounded blast radius.** One transaction per range, not one over five years. A giant transaction is both a long-running risk and, on many engines, expensive to roll back. 4. **Scheduled beside the incremental load.** The nightly job must keep running and must not process the same ranges the backfill is rewriting; give the backfill old ranges and the incremental job recent ones, and make sure their watermarks cannot overlap. ## Managing the intermediate state While the backfill runs, the column exists but is partially populated. Consumers who find it will draw wrong conclusions from the mix of populated and NULL rows. Options: keep the column out of the consumer-facing view until the backfill finishes, or publish it with a documented "valid from" date. Publishing a half-filled column into a mart and letting people build dashboards on it is the mistake that gets escalated. ## Verifying it worked The backfill is not done when the job succeeds; it is done when it reconciles. - **Row counts unchanged** before and after — a backfill that adds or loses rows has a duplication or filter bug. - **Existing measures unchanged** — SUM of revenue per month must be identical; you added a column, you did not restate anything. - **New column completeness** — no NULLs outside the documented period, and its distribution passes a sanity check (a bucketing column whose values are 99% one bucket usually means a join failed and defaulted). - **Spot reconciliation against source** for a sample of periods. ```sql -- completeness and non-mutation check after a backfill window SELECT date_trunc('month', order_date) AS month, COUNT(*) AS rows_now, COUNT(*) FILTER (WHERE size_bucket IS NULL) AS unfilled, SUM(revenue) AS revenue_now FROM fct_orders GROUP BY 1 ORDER BY 1; ``` ## The cheap escape hatch If the derived column is a pure function of columns already in the row, consider not persisting it at all at first: expose it in the consumer-facing view as an expression. History is instantly "backfilled", nothing is rewritten, and you can materialise it later if the compute cost justifies it. Persisting a derivation is an optimisation, and optimisations deserve a reason.

  • What is wrong with backfilling a historical fact table using today's dimension attribute values?
    It rewrites history as though every entity had always had its current attributes, so trends by that attribute become meaningless — churned customers move into their final segment retroactively. If the dimension keeps history, join as of the transaction date instead. If it does not, the honest answer is that the attribute is only valid from a stated date.
  • How do you keep a multi-day backfill from colliding with the nightly incremental load?
    Partition the work by time and keep the boundaries disjoint: the incremental job owns recent load dates, the backfill owns older ranges, and a control table records which ranges each has completed. Never let the backfill move the incremental job's watermark, and make each backfill range independently re-runnable so an overlap is recoverable rather than duplicating rows.

saying these in an interview costs you the question

  • Assumes historical source values are always still available
  • Stamps history with today's dimension values without saying so
  • Runs the backfill as one enormous transaction
  • Publishes a half-populated column to consumers
  • Declares success when the job exits zero, without reconciling

context

resolving links…