Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Duplicates appeared in a fact table

Data quality · Incidents & Scenarios

Duplicates appeared in a fact table

Mediumdata-quality-38
scenariodeduplicationfact-tablesincident-response

Question

You find duplicate orders in a fact table that was clean last week. What do you do?

Solution

When duplicate orders appear in a previously clean fact table, immediately scope the blast radius and pause downstream publishing to prevent inaccurate metrics from reaching executive dashboards. Once publishing is paused, identify whether the root cause stems from non-idempotent pipeline retries, join fan-outs, or duplicate source events, rebuild the affected partitions, add automated uniqueness tests, and communicate status to stakeholders.

Containing blast radius from duplicated orders

Triage the incident rapidly by containing downstream exposure:

  • Quantify and scope the damage: Run queries on the fact table to identify duplicate business keys and determine which partitions are corrupted. Check whether duplicates span the entire historical table or started on a specific date last week.
  • Pause downstream publishing: Immediately pause dependent mart jobs and dashboard syncs, or flag the table as degraded in the data catalog. This stops analysts and downstream automated processes from using inflated metrics while repairs are underway.
  • Notify stakeholders: Send an incident notification to consumers explaining that an anomaly was detected in order counts and an update will follow within a specific window.
SELECT order_id, count(*) AS occurrence_count
FROM fact_orders
WHERE order_date >= current_date - 14
GROUP BY order_id
HAVING count(*) > 1;

After containing the blast radius, isolate the root cause behind the duplication:

  • Non-idempotent reprocessing: Check orchestrator logs for pipeline retries. If a task failed midway and re-ran using a naive insert statement without clearing the target partition first, the retry appended duplicate rows into the table.
  • Join fan-out on dimension tables: Inspect recent changes in upstream dimension models. If a dimension like dim_customer or dim_promotion developed duplicate natural keys, or if an SCD Type 2 dimension join lacked an effective date filter, a one-to-one join inadvertently became one-to-many, duplicating fact rows.
  • Upstream source duplication: Check source transaction databases or Kafka topics. An upstream service deployment might have re-emitted messages or created duplicate order records at the source.

Rebuilding partitions and preventing recurrence

Once the root cause is identified, repair the dataset safely:

  • Rebuild affected partitions: Deduplicate the corrupted data by selecting the latest valid record per order key using window functions. Overwrite only the affected partitions atomically using table replacement or merge statements.
  • Implement automated blocking tests: Add a strict uniqueness test on order_id in dbt, Great Expectations, or Soda Core that halts the pipeline before data is published.
  • Publish post-incident communication: Provide stakeholders with a clear postmortem detailing the cause, the repaired partitions, and the preventive controls deployed.
PreviousNext