Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Pipeline ran twice and doubled the data

Pipelines & scenarios · Production Scenarios

Pipeline ran twice and doubled the data

Mediumpipelines-44
scenarioidempotencyduplicatesretrydata-fix

Question

A retry caused a pipeline to load the same day twice and numbers doubled. How do you fix the data and stop it from happening again?

Solution

First stop the damage and measure it. Then repair the data, and finally change the load so a retry cannot double anything again. The root cause is almost always a load that appends without checking what is already there.

Measure

Find the affected partitions and tables. A quick way is to count rows per load date and compare with a normal day, or look for duplicate business keys:

SELECT order_date, COUNT(*) AS rows, COUNT(DISTINCT order_id) AS keys
FROM fact_orders
GROUP BY order_date
HAVING COUNT(*) <> COUNT(DISTINCT order_id);

Also check what read the bad data before you fix it: dashboards, exports, downstream tables, and any ML features. They may need a rebuild too.

Repair

  • If the load added a run id or load timestamp, delete the rows from the duplicate run, and nothing else. This is the cleanest fix.
  • If not, rebuild each affected partition from the raw source files, which you kept in the landing zone. Overwrite the partition.
  • As a last resort, deduplicate by business key with ROW_NUMBER() ... = 1, choosing the version deliberately. Take a backup or a table clone first, and verify counts afterwards.

Then rerun downstream models for those dates, and tell consumers that numbers for the affected days changed.

Why it happened

A retry, for example after a timeout where the first attempt actually succeeded, ran the whole insert again. Plain INSERT appends and cannot tell it has run before.

Make it impossible

  • Overwrite the partition for the run date instead of appending, so a rerun replaces the same data.
  • Or MERGE on the business key.
  • Or write to a staging table and publish it with an atomic swap.
  • Put a uniqueness check on the key after each load (dbt unique, or a count comparison), so a double load fails the run and nobody sees the doubled numbers.
  • Record a load id on every row for traceability.

Say plainly that retries are normal in distributed systems, so every load must assume it may run twice. The design goal is that running twice leaves the same result as running once.

PreviousNext