Identifying Duplicates — Identify duplicates and unexpected...

SECTION 1

Historical Context & Motivation

The problem of duplicate data is as old as database management itself. When E.F. Codd formalized the relational model in 1970, he introduced the concept of candidate keys — minimal sets of attributes that uniquely identify each tuple in a relation — precisely to prevent the semantic ambiguity that arises when identical rows coexist. Despite this theoretical safeguard, real-world systems have always been plagued by duplicates, whether from application bugs, ETL pipeline failures, schema design oversights, or the pragmatic decision to forgo constraints for insertion speed.

As data warehousing emerged in the 1990s and data volumes exploded through the 2000s with web-scale applications, the scope and impact of duplicate rows grew dramatically. A single duplicated order record could cascade into incorrect revenue reports, double-shipped products, or erroneous machine-learning training data. The rise of data quality engineering as a discipline in the 2010s formalized the detection and remediation of duplicates as a first-class concern, spawning dedicated tools, frameworks, and SQL patterns that every practitioner is expected to master.

1970

Codd's Relational Model

E.F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," establishing candidate keys and set-based semantics that inherently disallow duplicate tuples within a relation.

1986

SQL Standardization (SQL-86)

The first ANSI SQL standard permits tables without primary keys, allowing bag semantics. The DISTINCT keyword is introduced to collapse duplicates in query results, implicitly acknowledging their prevalence.

1996

Data Warehousing Era

Ralph Kimball's dimensional modeling approach popularizes surrogate keys and ETL pipelines, but introduces new duplicate risks at the staging and loading phases where constraints are often relaxed.

2012

Big Data & Schema-on-Read

Hadoop and NoSQL stores defer schema enforcement to query time. Without upfront constraints, duplicate detection shifts entirely to analysts and data engineers writing defensive SQL.

2020s

Modern Data Quality Frameworks

Tools like dbt tests, Great Expectations, and Monte Carlo codify uniqueness assertions as automated pipeline checks, making duplicate detection a continuous, observable process.

The central question this lesson addresses is deceptively simple: how do you systematically detect rows that should be unique but aren't, and how do you diagnose the unexpected row multiplication that silently inflates aggregates? Answering this requires not just knowledge of SQL syntax, but a disciplined methodology for reasoning about grain, keys, and join semantics.

SECTION 2

Core Principles & Definitions

Before diving into detection queries, it is essential to establish a precise vocabulary. A duplicate is any row that shares the same value(s) on a set of columns that should, by business logic, uniquely identify a record. Note the distinction from a full-row duplicate, where every column is identical: partial-key duplicates are far more common and far more insidious, because they may carry conflicting values in non-key columns. Unexpected multiplicity refers to the phenomenon where a query returns more rows than expected, typically because a JOIN introduces a one-to-many or many-to-many fan-out that the developer did not anticipate. Both problems — true duplicates in source data, and artificial multiplicity from query logic — produce the same symptom: inflated counts, sums, and averages that silently corrupt analytical results.

Grain

The grain of a table (or query result) is the level of detail each row represents — e.g., one row per order, per order-line, or per customer-day. Duplicates violate the declared grain.

Natural vs. Surrogate Keys

A natural key derives from business data (e.g., SSN, email). A surrogate key is system-generated (e.g., auto-increment ID). Surrogate keys are always unique by construction, but natural-key duplicates can still exist.

Fan-Out

A fan-out occurs when a JOIN matches one row on the left side to multiple rows on the right, multiplying the left-side row. This is the primary cause of unexpected multiplicity in query results.

Idempotent Loads

An idempotent load produces the same result whether executed once or many times. Non-idempotent INSERT operations (e.g., appending without a MERGE or dedup step) are the most common source of table-level duplicates.

Cardinality Assertion

A cardinality assertion is a testable claim about the expected row count or uniqueness of a key. Asserting that COUNT(*) equals COUNT(DISTINCT key) is the simplest form.

✦ KEY TAKEAWAY

Think of a database table like a spreadsheet where each row is a unique entry in a ledger. If someone accidentally photocopies a page and inserts the copies back into the ledger, the totals at the bottom are now wrong — even though each individual entry looks correct. Duplicate detection is the process of finding those photocopied pages before the accountant sums the column. In SQL terms, this means verifying that the columns you expect to be unique actually are, and that your JOINs don't inadvertently photocopy rows.

SECTION 3

Visual Explanation — Anatomy of Duplication

The top section shows a true duplicate in the orders table — two identical rows with order_id 1001. The bottom section illustrates fan-out: joining the customers table (1 row) to the phones table (2 rows for the same customer) produces 2 result rows, artificially multiplying any downstream aggregation.

The diagram above illustrates the two fundamental mechanisms by which duplicates appear in SQL result sets. In the first case, the source table itself contains rows that violate uniqueness expectations — order_id 1001 appears twice, inflating SUM(amt) from the correct 155 to the erroneous 205. In the second case, the source tables are individually clean, but the JOIN operation produces a Cartesian product across the matching rows. A customer with two phone numbers generates two rows in the join result, and if that result is subsequently aggregated or joined to another table, the customer's data is double-counted. Both scenarios are diagnosed using the same fundamental technique: grouping by the expected key and filtering for groups with more than one row.

SECTION 4

Detection Patterns — The SQL Toolkit

The canonical approach to detecting duplicates in SQL relies on the interplay between GROUP BY and HAVING. The logic is straightforward: group rows by the columns that should form a unique key, then use HAVING COUNT(*) > 1 to retain only those groups that violate uniqueness. This pattern can be extended with window functions for more nuanced analysis, such as identifying which specific rows within a duplicate group to retain or remove.

Pattern 1: GROUP BY + HAVING

DUPLICATE DETECTION QUERY

SELECT key_col, COUNT(*) AS cnt FROM table_name GROUP BY key_col HAVING COUNT(*) > 1 ORDER BY cnt DESC;

Replace key_col with the column(s) expected to be unique. cnt reveals the degree of duplication — values of 2 suggest a simple double-load, while higher values may indicate a recurring pipeline bug.

Pattern 2: Cardinality Assertion

QUICK UNIQUENESS CHECK

SELECT COUNT(*) AS total_rows, COUNT(DISTINCT key_col) AS unique_keys, COUNT(*) − COUNT(DISTINCT key_col) AS excess FROM table_name;

If excess is zero, the key is unique across the table. A non-zero value tells you how many rows are duplicates (though not which ones). This is an O(n) scan and is typically the fastest first check.

Pattern 3: Window Function Approach

ROW_NUMBER FOR DEDUP

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY key_col ORDER BY updated_at DESC ) AS rn FROM table_name ) sub WHERE rn > 1;

This pattern assigns a sequence number within each group of duplicates. Rows with rn = 1 are the 'keepers' (most recently updated), and rows with rn > 1 are the duplicates to inspect or delete.

Pattern 4: JOIN Fan-Out Detection

PRE-JOIN CARDINALITY CHECK

-- Before joining A to B on A.id = B.a_id: SELECT a_id, COUNT(*) AS cnt FROM B GROUP BY a_id HAVING COUNT(*) > 1;

Run this on the right-side table before executing the JOIN. If any a_id appears more than once in B, the JOIN will fan out rows from A. This is not necessarily wrong — it depends on whether you expect a one-to-many relationship — but it should always be a conscious decision.

SECTION 5

Taxonomy of Duplicate Scenarios

Not all duplicates are created equal. Understanding the root cause is essential for choosing the right remediation strategy. The diagram below categorizes the most common scenarios encountered in production systems, organized by whether the duplication originates in the source data, the transformation logic, or the query itself.

This taxonomy organizes duplicate scenarios into three root causes: source-level duplicates (dirty data at ingestion), pipeline duplicates (ETL errors), and query-level multiplicity (JOIN fan-outs). Each leaf node maps to a specific detection strategy.

Common duplicate scenarios with detection and remediation strategies

Scenario	Root Cause	Detection Query	Remediation
Exact Duplicate	Double-submit in application, file loaded twice	`GROUP BY all_columns HAVING COUNT(*) > 1`	DELETE using ROW_NUMBER or ctid; add UNIQUE constraint
Fuzzy Duplicate	Same natural key, different non-key values (e.g., updated address)	`GROUP BY natural_key HAVING COUNT(*) > 1`	Keep latest via ROW_NUMBER ORDER BY updated_at DESC; implement SCD Type 2
Re-Ingestion	ETL job re-run appends data that already exists	`COUNT(*) vs COUNT(DISTINCT pk)`	Use MERGE/UPSERT; truncate-and-reload; add pipeline idempotency
JOIN Fan-Out	One-to-many relationship on join key	Check join-key uniqueness on each side before joining	Aggregate before joining, or use DISTINCT / qualifying subquery

SECTION 6

Worked Example — Diagnosing an Inflated Revenue Report

Suppose you are a data analyst at an e-commerce company. The finance team reports that the monthly revenue dashboard suddenly shows $2.3M, but the expected figure is approximately $1.9M. You suspect duplicate rows. The core tables are orders (grain: one row per order) and order_tags (grain: one row per order-tag pair, since orders can have multiple tags like 'holiday', 'promo'). The revenue query joins these tables and sums order_total.

Diagnosing the Revenue Inflation

Step 1 — Check source table for duplicatesFirst, verify uniqueness of the orders table on its primary key order_id: SELECT COUNT(*) AS total, COUNT(DISTINCT order_id) AS unique_ids FROM orders WHERE order_date >= '2024-12-01';

Result: total = 48,212 and unique_ids = 48,212. No duplicates in the source table.

Step 2 — Examine the revenue queryThe dashboard query looks like:

SELECT SUM(o.order_total) AS revenue FROM orders o JOIN order_tags t ON o.order_id = t.order_id WHERE o.order_date >= '2024-12-01';

This joins orders to order_tags. If an order has multiple tags, the order row is fanned out.

Suspect: the JOIN may be fanning out orders with multiple tags.

Step 3 — Check for fan-out on the join keyVerify whether order_id is unique in order_tags:

SELECT order_id, COUNT(*) AS tag_count FROM order_tags GROUP BY order_id HAVING COUNT(*) > 1 ORDER BY tag_count DESC LIMIT 10;

Result: thousands of orders have 2–4 tags. Confirmed: one-to-many fan-out is multiplying order_total.

Step 4 — Quantify the impactCompare the inflated sum to the correct sum:

SELECT
  (SELECT SUM(order_total) FROM orders WHERE order_date >= '2024-12-01') AS correct_revenue,
  (SELECT SUM(o.order_total) FROM orders o JOIN order_tags t ON o.order_id = t.order_id WHERE o.order_date >= '2024-12-01') AS inflated_revenue;

correct_revenue = 1,904,500 vs. inflated_revenue = 2,311,780. The fan-out inflated revenue by ~21%.

Step 5 — Fix the queryIf tags are needed for filtering only, use EXISTS or aggregate before joining:

SELECT SUM(o.order_total) AS revenue
FROM orders o
WHERE o.order_date >= '2024-12-01'
  AND EXISTS (
    SELECT 1 FROM order_tags t
    WHERE t.order_id = o.order_id
  );

Alternatively, if you need tag data alongside orders, aggregate at the order grain:

SELECT o.order_id, o.order_total,
  STRING_AGG(t.tag_name, ', ') AS tags
FROM orders o
JOIN order_tags t ON o.order_id = t.order_id
WHERE o.order_date >= '2024-12-01'
GROUP BY o.order_id, o.order_total;

Revenue now correctly reports $1,904,500. The fan-out is eliminated.

SECTION 7

Strengths, Limitations & Trade-offs of Detection Methods

Comparison of duplicate detection and prevention methods

Method	Strengths	Limitations
`GROUP BY + HAVING`	Simple, readable, works on all SQL dialects. Immediately shows which keys are duplicated and how many copies exist.	Only shows the key values, not the full duplicate rows. Requires a self-join or subquery to retrieve the actual offending rows.
`COUNT vs COUNT(DISTINCT)`	Fastest single-query check. Answers 'are there any duplicates at all?' in one scan. Ideal for CI/CD pipeline assertions.	Binary answer only — doesn't tell you which keys or how many. Useless for debugging; only useful for alerting.
`ROW_NUMBER() OVER`	Returns full rows, supports deterministic deduplication (keep latest, keep first). Essential for DELETE/UPDATE operations.	Requires window function support (not available in MySQL < 8.0). Higher memory usage due to the window sort.
Pre-join cardinality check	Prevents fan-out before it happens. Teaches developers to reason about join semantics proactively.	Adds an extra query step; can feel tedious on tight deadlines. Sometimes ignored under time pressure.
UNIQUE constraint / dbt test	Enforces at write time (constraint) or build time (dbt). Prevents duplicates from ever entering the table.	Constraints add write latency and can cause load failures. dbt tests run post-load, so bad data may temporarily exist.

✦ KEY TAKEAWAY

Think of these methods as layers of defense in a software testing pyramid. The UNIQUE constraint is your unit test — it catches problems at the point of insertion. The COUNT vs COUNT(DISTINCT) assertion is your integration test — it verifies the end state of a pipeline. The GROUP BY + HAVING query is your debugger — it helps you inspect and diagnose failures after they're detected. A robust data system uses all three.

SECTION 8

Connection to Advanced Topics

Duplicate detection is the entry point to a broader landscape of data quality and database theory. Understanding why duplicates appear — and why they persist — connects directly to topics like normalization theory, slowly changing dimensions (SCDs), entity resolution, and idempotent pipeline design. The table below maps each concept from this lesson to its advanced counterpart.

Mapping lesson concepts to advanced database theory

This Lesson	Advanced Extension	Key Idea
Natural key uniqueness	3NF / BCNF normalization	Proper normalization eliminates redundancy, which is the structural root of many duplicate scenarios. A table in BCNF has no non-trivial functional dependencies that could cause update anomalies.
Fuzzy duplicates (same key, different data)	Slowly Changing Dimensions (SCD Type 2)	SCD Type 2 handles the case where a dimension attribute changes over time by creating a new row with validity dates, turning an apparent duplicate into a versioned history.
`GROUP BY + HAVING` on exact keys	Entity Resolution / Record Linkage	When duplicates can't be detected by exact key matching (e.g., 'Jon Smith' vs 'Jonathan Smith'), probabilistic matching and Levenshtein distance are used.
ETL re-ingestion duplicates	Idempotent Pipeline Design	Advanced pipelines use MERGE/UPSERT, hash-based change detection, or snapshot isolation to guarantee that re-running a load produces identical results.
JOIN fan-out detection	Star Schema Modeling / Grain Alignment	Kimball's methodology explicitly documents the grain of each fact table and enforces that joins between facts and dimensions preserve that grain.

As you advance in your career, duplicate detection evolves from an ad hoc debugging activity into a formalized component of data observability. Modern data platforms incorporate automated freshness, volume, and uniqueness monitors that trigger alerts the moment a pipeline introduces unexpected row counts. Tools like dbt's unique and not_null schema tests, Great Expectations' expect_column_values_to_be_unique, and Monte Carlo's anomaly detection all build on the fundamental patterns introduced in this lesson.

SECTION 9

Practice Problems

PROBLEM 1 — CONCEPTUAL

Explain the difference between a true duplicate in a source table and unexpected multiplicity caused by a JOIN fan-out. Why do both produce the same symptom (inflated aggregates), yet require different remediation strategies?

PROBLEM 2 — BASIC CALCULATION

Given a table students with columns (student_id, email, name, enrolled_date), write a SQL query that finds all email values that appear more than once, along with the count of occurrences. Order results by count descending.

PROBLEM 3 — INTERMEDIATE

You have a table transactions(txn_id, account_id, amount, created_at) where txn_id should be unique but a pipeline bug has introduced duplicates. Write a query using ROW_NUMBER() that returns the full row data for only the duplicate rows (not the originals), keeping the row with the earliest created_at as the 'original'.

PROBLEM 4 — APPLIED

A data engineer has written the following revenue query that is producing inflated results:

SELECT r.region, SUM(o.amount) AS total_revenue
FROM orders o
JOIN promotions p ON o.order_id = p.order_id
JOIN regions r ON o.region_id = r.region_id
GROUP BY r.region;

The promotions table has multiple rows per order (one per applied promo code). Describe how to diagnose the issue and rewrite the query to produce correct revenue figures while still filtering to only orders that have at least one promotion.

PROBLEM 5 — CRITICAL THINKING

Consider a data pipeline that loads daily snapshots of a customers table into a data warehouse using INSERT INTO warehouse.customers SELECT * FROM source.customers every day. The warehouse table has no primary key constraint. After 30 days, how would you: (a) detect the duplication, (b) quantify its extent, (c) recover a clean version of the table, and (d) redesign the pipeline to prevent recurrence? Discuss the trade-offs of your approach.

SUMMARY

Summary

Duplicate detection in SQL revolves around a single core technique: GROUP BY the expected key, then HAVING COUNT(*) > 1. This pattern, combined with the COUNT(*) vs COUNT(DISTINCT key) cardinality assertion, allows you to detect source-level duplicates caused by double-submits, non-idempotent ETL loads, or UNION ALL of overlapping sources. The ROW_NUMBER() window function extends this pattern by letting you identify and retain specific rows within each duplicate group, enabling deterministic deduplication.

Equally important is detecting unexpected multiplicity from JOIN fan-outs, where individually clean tables produce inflated result sets due to one-to-many or many-to-many join relationships. The remedy is to check join-key cardinality before joining, use EXISTS for semi-joins, or pre-aggregate to the correct grain. Prevention is achieved through UNIQUE constraints, MERGE/UPSERT patterns, and automated data quality tests that catch violations before they propagate to dashboards and downstream consumers.