Correct, Governed Deduplication in SQL (without breaking your pipelines)

A deterministic, auditable pattern to find, flag, and quarantine duplicates — while row-level DQ “breadcrumbs” flow all the way to your data products.

Summary

• ROW_NUMBER() must have an ORDER BY. Without it your “first row” is non-deterministic.
• Don’t hide evidence with DISTINCT/UNION/GROUP BY. Instead: Detect → Flag → Route.
• Add row-level DQ status/labels and reasons; carry them downstream.
• Publish two outputs: a golden dataset (winners) and a quarantine (losers for analysis).
• Track metrics and alert on spikes — keep pipelines running while making issues visible.

The classic mistake (and the fix)

– ❌ Non-deterministic (may pick different rows per run)
SELECT user_id, email, created_at
FROM users
QUALIFY ROW_NUMBER() OVER (PARTITION BY email) = 1;

This can return different winners because there’s no sort rule.

– ✅ Deterministic: stable order + tie-breaker(s)
SELECT user_id, email, created_at
FROM users
QUALIFY ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, ingested_at DESC, user_id
) = 1;

Guideline for **ORDER BY** (adapt as needed):

1. Business time (effective_at/created_at)
2. Ingest time (ingested_at)
3. Source priority (e.g., CRM > CSV upload)
4. Stable surrogate (id/row_hash)

If your engine lacks QUALIFY (e.g., some Spark/Databricks), wrap in a subquery and WHERE rn = 1.

Why governed deduplication?

Real sources often contain duplicates. We don’t want critical loads to halt for a few bad rows, and we don’t want to silently erase them either. We want a deterministic winner, we want to see and analyze the losers, and we want DQ breadcrumbs that travel with the data so downstream consumers understand provenance and quality.

The pattern: Detect → Flag → Route

1) Detect & rank

WITH ranked AS (
SELECT
u.,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, ingested_at DESC, user_id
) AS rn,
COUNT() OVER (PARTITION BY email) AS dup_ct
FROM raw.users u
)

2) Flag with row-level DQ labels

, flagged AS (
SELECT
*,
CASE
WHEN dup_ct = 1 THEN ‘OK’
WHEN rn = 1 THEN ‘OK_PRIMARY’ – winner kept for downstream use
ELSE ‘DQ_DUPLICATE’ – loser quarantined
END AS dq_status,
CASE
WHEN dup_ct = 1 THEN NULL
WHEN rn = 1 THEN NULL
ELSE ‘Duplicate key; non-winner by created_at/ingested_at/id order’
END AS dq_reason
FROM ranked
)

3) Route into two clear outputs

• Golden dataset (use in downstream transforms):

SELECT *
FROM flagged
WHERE dq_status IN (‘OK’,’OK_PRIMARY’);

• Quarantine table/view (for ops & BI DQ dashboards):

CREATE TABLE IF NOT EXISTS dq.duplicates_users AS
SELECT *
FROM flagged
WHERE dq_status = ‘DQ_DUPLICATE’;

The same logic can drive alerts, dashboards, and root-cause investigations.

Why not DISTINCT, UNION, or GROUP BY?

They hide which row won, throw away columns or force arbitrary aggregates, and make audits difficult. Windowed ranking + flags preserves the evidence and keeps your pipeline moving.

Engine-specific snippets

Snowflake / BigQuery

Use QUALIFY as shown above. Make NULL handling explicit if needed:

ORDER BY
(effective_at IS NULL), – put NULLs last
effective_at DESC,
ingested_at DESC,
row_hash

Databricks / Spark SQL

No QUALIFY? Use a subquery:

SELECT
FROM (
SELECT u.,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, ingested_at DESC, user_id
) AS rn,
COUNT(*) OVER (PARTITION BY email) AS dup_ct
FROM raw.users u
) t
WHERE rn = 1; – golden
– losers: WHERE rn > 1

Postgres

Same subquery pattern. DISTINCT ON can be deterministic only if paired with a complete ORDER BY, but you still need to persist losers elsewhere—so the ranking pattern is clearer and more auditable.

dbt implementation (with breadcrumbs)

Model: **stg_users_dedup.sql**
(Produces the golden dataset but keeps DQ columns.)

WITH ranked AS (
SELECT
,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, ingested_at DESC, user_id
) AS rn,
COUNT() OVER (PARTITION BY email) AS dup_ct
FROM {{ ref(‘stg_users_raw’) }}
),
flagged AS (
SELECT
*,
CASE WHEN dup_ct=1 THEN ‘OK’
WHEN rn=1 THEN ‘OK_PRIMARY’
ELSE ‘DQ_DUPLICATE’ END AS dq_status,
CASE WHEN dup_ct=1 THEN NULL
WHEN rn=1 THEN NULL
ELSE ‘Duplicate key; non-winner’ END AS dq_reason
FROM ranked
)
SELECT * FROM flagged WHERE dq_status IN (‘OK’,’OK_PRIMARY’);

Companion quarantine model: **dq_users_duplicates.sql**

SELECT * FROM {{ ref(‘stg_users_dedup__flagged’) }}
WHERE dq_status = ‘DQ_DUPLICATE’;

Tests (YAML)

models:
– name: stg_users_dedup
columns:
– name: email
tests:
– dbt_utils.unique_combination_of_columns:
combination_of_columns: [‘email’]
where: “dq_status in (‘OK’,’OK_PRIMARY’)”
– name: dq_status
description: “Row-level DQ label that propagates downstream”

Observability playbook

Metrics to store per run

• total_rows, duplicate_rows, %_duplicates
• Top duplicate keys (e.g., email)
• Source/system breakdown, batch/file identifiers

INSERT INTO dq.run_metrics
(run_id, table_name, total_rows, duplicate_rows, pct_duplicates, run_ts)
SELECT
{{ var(‘run_id’) }}, ‘users’,
COUNT(),
SUM(CASE WHEN dq_status=’DQ_DUPLICATE’ THEN 1 ELSE 0 END),
SAFE_DIVIDE(SUM(CASE WHEN dq_status=’DQ_DUPLICATE’ THEN 1 ELSE 0 END), COUNT()),
CURRENT_TIMESTAMP();

Dashboard quick spec (Power BI / Looker)

• KPIs: duplicate count, % duplicates, winners vs. losers, 30-day trend
• Slices: by source_system, file_batch_id, ingestion path
• Drill: show duplicate groups (winner + losers with order columns & dq_reason)
• Alerts: trigger on % duplicates threshold or sudden spikes vs. rolling mean

Edge cases & tips

• Late arrivals: ordering on both business and ingest time re-resolves winners deterministically on reprocess.
• Composite keys: partition by all parts of the business key (e.g., account_id, email).
• Source reliability: introduce source_priority into the order to prefer trusted systems.
• NULL semantics: decide explicitly (NULLs first/last) and encode in the ORDER BY.
• Provenance: store dq_source_keys (file id, row nr) and a row_hash to trace lineage.
• Downstream propagation: keep dq_status and dq_reason in your semantic model; consumers should see that a record is derived from rows with DQ issues.

Ready-to-use mini-template

– GOLDEN OUTPUT
WITH ranked AS (
SELECT ,
ROW_NUMBER() OVER (
PARTITION BY business_key
ORDER BY effective_at DESC,
ingested_at DESC,
source_priority DESC,
row_hash
) AS rn,
COUNT() OVER (PARTITION BY business_key) AS dup_ct
FROM src.table
),
flagged AS (
SELECT *,
CASE WHEN dup_ct=1 THEN ‘OK’
WHEN rn=1 THEN ‘OK_PRIMARY’
ELSE ‘DQ_DUPLICATE’ END AS dq_status,
CASE WHEN dup_ct>1 AND rn>1 THEN ‘Duplicate; non-winner’ END AS dq_reason
FROM ranked
)
SELECT * FROM flagged WHERE dq_status IN (‘OK’,’OK_PRIMARY’);

Key takeaways

• Pair ROW_NUMBER() with a deterministic ORDER BY and a clear tie-breaker chain.
• Treat deduplication as governance, not deletion.
• Flag and quarantine duplicates; propagate DQ breadcrumbs to the end of your pipelines.
• Add metrics, dashboards, and alerts to make quality visible without blocking delivery.
• Keep the process idempotent: same inputs → same winners, every time.

If this pattern fits your stack, you can lift the snippets directly for Snowflake, BigQuery, Databricks/Spark, or Postgres. Keep the DQ columns visible to analysts, wire up a small BI page for monitoring, and your pipelines keep flowing — even when the real world sends you duplicates.