How Do You Handle Duplicates?

Duplicates arrive from 3 directions, and the interviewer wants to hear you name them before you write a window function. Upstream systems deliver at least once, so a retry on their side sends the same event twice. Your own retries, when the load is not idempotent, double rows you already wrote. And the same real-world entity lives in 2 systems that both export it, so the customer exists once in the CRM and once in billing with different email addresses. The first 2 produce exact or versioned duplicates of one record; the third produces conflicting records that need a rule for which one wins.

Exact duplicates and versioned duplicates

SELECT DISTINCT removes rows that are identical in every column. It does nothing for 3 rows that share an order_id but carry different updated_at values, because each row is distinct. That shape, the same key with several versions, is the common one, and it needs a rule that picks the version to keep.

Row 2 and row 3 share the same updated_at, so the rule needs a second sort key. ingested_at breaks the tie: the latest arrival wins. Without a tie-breaker the engine picks whichever row it reads first, and the pipeline returns different answers on different runs.

SELECT
  order_id,
  customer_id,
  order_total,
  currency,
  updated_at
FROM (
  SELECT
    order_id,
    customer_id,
    order_total,
    currency,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC, ingested_at DESC
    ) AS rn
  FROM bronze.orders
  WHERE dt = DATE '2026-10-20'
) ranked
WHERE rn = 1

Order 1001 comes out once with order_total 54.00 from the row ingested at 11:50. Say why ORDER BY has 2 keys when you write it, because an interviewer who sees one key will ask what happens on a tie, and a candidate who already answered it has shown they ran into it before.

Cross-source deduplication with source priority

When the same customer arrives from 2 systems, recency alone gives the wrong answer. Billing updated the record at 10:00 with a verified payment email; the CRM updated it at 14:00 when a sales rep typed a typo. Newest wins picks the typo. The rule needs a priority: billing is the system of record for this entity, so billing wins whenever it has a row, and recency decides only within a source.

SELECT
  customer_id,
  email,
  source_system
FROM (
  SELECT
    customer_id,
    email,
    source_system,
    updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY
        CASE source_system
          WHEN 'billing' THEN 1
          WHEN 'crm' THEN 2
          ELSE 3
        END,
        updated_at DESC
    ) AS rn
  FROM (
    SELECT
      customer_id,
      email,
      'billing' AS source_system,
      updated_at
    FROM bronze.billing_customers
    UNION ALL
    SELECT
      customer_id,
      email,
      'crm' AS source_system,
      updated_at
    FROM bronze.crm_customers
  ) unioned
) ranked
WHERE rn = 1

The CASE in the ORDER BY turns a business rule into a sort key, and the ELSE 3 catches a source nobody listed so it never silently outranks billing. The rule here is whole-row: billing wins every column. The field-level version, where billing wins email but the CRM wins phone, uses a separate priority per column and COALESCE across sources ordered by that priority. Mention that it exists, and say you would start whole-row because consumers can reason about one winner.

Where it runs and what it costs

ROW_NUMBER partitioned by key forces a shuffle of every row to the worker holding its key, then a sort within each key. On a 500 million row daily feed that shuffle is the most expensive step in the load, so run it once, when bronze becomes silver, and leave bronze holding every version so an audit can see what arrived. Deduplicating again in every downstream model repeats the shuffle and invites 2 models to pick different winners for the same key.

The question wears several disguises: 'this feed sometimes sends events twice', 'the same customer shows up in 2 systems', 'the retry doubled our counts'. Each one resolves to the same 2 decisions, which key defines a duplicate and which rule picks the survivor, and an answer that names both before writing SQL reads as experience.

Do

• Name the 3 sources of duplicates before writing SQL
• Order by a business timestamp, then an ingestion timestamp, for a deterministic winner
• Encode source priority as a CASE sort key with an ELSE for unknown sources
• Dedupe once at the silver layer and keep every version in bronze

Avoid

• Reach for DISTINCT when rows differ in a timestamp column
• Order by one key and leave ties to the engine
• Let newest-wins override the system of record
• Dedupe again in every downstream model

About this lesson

This section is part of the Make It Reliable: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.