Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Reprocessing history after a logic change

Batch & Streaming · Batch Design

Reprocessing history after a logic change

Mediumbatch-streaming-52
backfillreprocessingdata-migrationspartitioning

Question

A metric definition changed and you need to recompute two years of history. How do you approach it?

Solution

When a metric definition changes across two years of historical data, you compute the backfill into a separate shadow table or versioned dataset rather than mutating the live production table directly. You execute the recomputation partition by partition with concurrency throttling to avoid overwhelming warehouse cluster resources and exceeding compute budgets. After validating sample metrics between the old and new calculations, you perform an atomic cutover using a SQL view or table swap and notify downstream stakeholders.

Cost estimation and shadow table staging

Before launching queries across twenty-four months of historical data, complete two preliminary steps:

  • Estimate compute costs: Run a benchmark test on one historical month. Multiply the query runtime and warehouse credits consumed by twenty-four to project total financial expense and execution time. This prevents unexpected budget overruns on cloud warehouses like Snowflake or BigQuery.
  • Create a shadow target table: Build a new table structure, such as fct_monthly_revenue_v2, holding the updated business logic. Never execute UPDATE or DELETE statements directly against the live production table, which must remain stable to serve ongoing operational queries.

Partitioned backfilling with concurrency throttling

Attempting to process two years of data in a single massive query risks query timeouts, memory exhaustion, and slot starvation.

Structure the backfill across discrete chronological partitions:

-- Process historical partitions in manageable increments
INSERT INTO fct_monthly_revenue_v2
SELECT ...
FROM raw_orders
WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';

Throttling backfill concurrency is essential. Run jobs with bounded parallelism (such as two concurrent partitions at a time) to prevent backfill tasks from starving regular production ETL jobs of compute capacity.

Validation, atomic cutover, and stakeholder communication

Once the backfill completes, run verification queries comparing old and new metrics across key business dimensions:

  • Confirm that metric discrepancies match expected formula changes rather than unintended join multiplications or dropped null values.
  • Swap tables atomically by altering a SQL view pointer (CREATE OR REPLACE VIEW fct_monthly_revenue AS SELECT * FROM fct_monthly_revenue_v2) or performing an atomic table rename. This prevents BI dashboards from encountering empty intermediate states.

Before executing the cutover, notify downstream analytics consumers and dashboard owners about the update, outlining exactly how historical reporting figures will shift under the new definition.

PreviousNext