Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Backfill two years for a new column

Pipelines & scenarios · Production Scenarios

Backfill two years for a new column

Mediumpipelines-47
scenariobackfillpartitionsthrottlingvalidation

Question

Analysts need a new derived column populated for two years of history. How do you do it safely?

Solution

A two-year backfill is a controlled operation, not a single big query. The goals are: do not disturb live work, make each piece safe to rerun, and check every piece before declaring it done.

Prepare

  • Define the logic and test it on a few days of data, and compare with an independently calculated result for a sample.
  • Add the column to the table as nullable first, so existing queries and loads continue to work.
  • Estimate the cost: how much data is read and rewritten, how long a day takes, and how much it costs in compute. Two years of daily partitions is about 730 units of work, so a day's timing multiplied by 730, divided by your parallelism, gives the duration.
  • Agree a window with stakeholders, especially if tables are used by dashboards or other jobs.

Run it in slices

Process one partition (one day, or one week for small partitions) per task, and run several in parallel, with a cap. Throttling keeps the warehouse or cluster available for normal work. Run the heavy part off-peak. Newest data first is often best, since it is the most valuable, and older periods can follow slowly.

Make each slice idempotent

Overwrite the partition, or MERGE by key, so a failed or repeated slice does not duplicate anything. Track progress in a control table (date, status, row count) so you can restart exactly where it stopped, and not run everything again.

INSERT OVERWRITE orders PARTITION (order_date = '2024-03-01')
SELECT ..., new_calc(...) AS new_col
FROM orders_source WHERE order_date = '2024-03-01';

Validate

After each slice, check the row count equals the original, the new column has no unexpected NULLs, and some totals match a known number. After the whole backfill, run a global check across all partitions.

Downstream and communication

Rebuild models that depend on the table, in the right order. Tell analysts when the history is complete, and say what changed if old numbers moved. Do not let someone query half-backfilled data without knowing.

Avoid

Rewriting hot partitions while people query them in business hours, using one giant job that fails after nine hours with no progress saved, and changing business logic during the backfill.

PreviousNext