Merging Duplicates Into One Record

Concepts covered: Conditional Aggregation, Multiple WINDOWs, Min & Max, Set Combination, Conditional Logic

When no single copy is complete

Keeping 1 whole row assumes some row carries every column correctly. A CDC connector configured to send only changed columns breaks that, since its status update arrives with amount and shipping_address empty. So do 2 systems describing the same order or customer, because each tends to be right about different columns: the CRM owns the email, billing owns the payment address. In both cases the surviving record is assembled column by column.

Apply a keep-the-latest rule to the whole row and you keep version 81247: an order with no amount and no address. Keep the first version instead and you report it as 'placed' at the old address. Neither row is the order. What produces the right record is a survivorship rule per column. That is the master data management term for deciding which source or version wins each attribute, and interviewers who have built this recognize the word.

One rule per column

Rule	Use it for	How in SQL
Latest non-null	Attributes updated piecemeal: status, amount, address	Rank NULLs last, newest first, read rn = 1
Earliest	First-seen facts: created_at, first channel	MIN(updated_at)
Maximum or any-true	Values that only grow: last_lsn, ever_refunded	MAX(...)
Authoritative source	Columns one system owns: email from the CRM	Rank by source priority, then newest
Sum across rows	Rows that are parts of a whole, never copies	SUM(...)

WITH v AS (
  SELECT order_id, source_lsn, status, amount,
         shipping_address, updated_at,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY
           CASE WHEN status IS NULL THEN 1 ELSE 0 END,
           source_lsn DESC) AS status_rn,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY
           CASE WHEN amount IS NULL THEN 1 ELSE 0 END,
           source_lsn DESC) AS amount_rn,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY
           CASE WHEN shipping_address IS NULL THEN 1 ELSE 0 END,
           source_lsn DESC) AS address_rn
  FROM orders_raw
)
SELECT order_id,
       MAX(CASE WHEN status_rn = 1 THEN status END)            AS status,
       MAX(CASE WHEN amount_rn = 1 THEN amount END)            AS amount,
       MAX(CASE WHEN address_rn = 1 THEN shipping_address END) AS shipping_address,
       MIN(updated_at)                                         AS created_at,
       MAX(source_lsn)                                         AS last_lsn
FROM v
GROUP BY order_id;

Each window ranks the versions for 1 column. The CASE expression pushes NULLs to the bottom, and then the newest commit wins. The outer query folds each order to 1 row, and MAX(CASE WHEN status_rn = 1 THEN status END) reads the single value that won. The MAX is there only because GROUP BY needs an aggregate, and it sees 1 non-null input. Engines with an IGNORE NULLS option on FIRST_VALUE can replace each ranking with 1 window over the same ordering. Snowflake and BigQuery have that option, and so does Oracle.

2 sources, 1 owner per column

The same shape handles 2 systems. Stack them with UNION ALL, tag each row with its source, and put the owner's priority inside each column's ordering. NULLs still sort last, so when the owning system has no value, the other system's copy fills in. That is usually what the business wants, but confirm it out loud.

WITH combined AS (
  SELECT customer_id, email, billing_address, updated_at,
         'crm' AS src
  FROM crm_customers
  UNION ALL
  SELECT customer_id, email, billing_address, updated_at,
         'billing' AS src
  FROM billing_customers
),
ranked AS (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
           CASE WHEN email IS NULL THEN 1 ELSE 0 END,
           CASE WHEN src = 'crm' THEN 1 ELSE 2 END,
           updated_at DESC) AS email_rn,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
           CASE WHEN billing_address IS NULL THEN 1 ELSE 0 END,
           CASE WHEN src = 'billing' THEN 1 ELSE 2 END,
           updated_at DESC) AS address_rn
  FROM combined AS c
)
SELECT customer_id,
       MAX(CASE WHEN email_rn = 1 THEN email END) AS email,
       MAX(CASE WHEN address_rn = 1 THEN billing_address END) AS billing_address
FROM ranked
GROUP BY customer_id;

The shortcut that builds the wrong record

The tempting version is GROUP BY order_id with MAX on every column. On text, MAX is alphabetical. 'shipped' beats 'placed' by luck, and 'cancelled' loses to both, so a cancelled order reports as whichever earlier status sorts after it: shipped if it shipped, placed if it never did. Interviewers plant a status that sorts early to see who reaches for this.

The subtler failure comes from rules applied too finely. An address changes as a unit: the street and city move with the postcode. Pick each by its own latest non-null, and a partial update that touched only the postcode pairs the new postcode with the old street: an address nobody ever entered. Columns that form 1 fact take 1 ranking, and all of them are read from its winner. That ranking's NULL test covers the whole group with OR, so a version missing any part sorts below every complete one. Saying 'these 3 columns travel together' tells the interviewer you have shipped a merge.

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
  CASE WHEN street IS NULL OR city IS NULL
         OR postcode IS NULL THEN 1 ELSE 0 END,
  CASE WHEN src = 'billing' THEN 1 ELSE 2 END,
  updated_at DESC) AS address_rn

-- outer SELECT: every part comes from the same winning row
MAX(CASE WHEN address_rn = 1 THEN street END)   AS street,
MAX(CASE WHEN address_rn = 1 THEN city END)     AS city,
MAX(CASE WHEN address_rn = 1 THEN postcode END) AS postcode

About this lesson

This section is part of the Deduplication: 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.