TECH **SQL for Data Analysis: Handling Duplicates & Deduplication Techniques**

SQL for Data Analysis: Handling Duplicates & Deduplication Techniques

A Hyper-Practical, Zero-Fluff Study Guide

1. What This Is & Why It Matters

Duplicates in your data are like weeds in a garden—they spread fast, choke insights, and make stakeholders question your credibility. In SQL for data analysis, duplicates are rows that appear identical (or nearly identical) in key columns, often due to: - Bad ETL pipelines (e.g., a JOIN gone wrong, or a UNION without DISTINCT).
- User error (e.g., double-submitted forms, manual data entry mistakes).
- System glitches (e.g., a cron job running twice, or a Kafka consumer reprocessing messages).

Why this matters in production:
- Broken aggregations: SUM(), COUNT(), and AVG() will inflate if duplicates exist.
- Misleading dashboards: A "unique users" metric becomes "total logins" if duplicates aren’t handled.
- Wasted compute: Processing 10M rows instead of 5M because of duplicates slows queries and increases cloud costs.
- Compliance risks: GDPR/CCPA require accurate "right to erasure" handling—duplicates can lead to incomplete deletions.

Real-world scenario:
You’re analyzing customer churn for a SaaS company. Your users table has 10K rows, but your COUNT(DISTINCT user_id) returns 8K. After digging, you find that 2K users were duplicated due to a failed migration. Your churn rate calculation is now 20% off—enough to mislead the CEO into making the wrong retention strategy.

This guide will teach you how to detect, handle, and prevent duplicates like a pro.

2. Core Concepts & Components

1. Duplicate Rows

• Definition: Two or more rows with identical values in all columns (or a subset of key columns).
• Production insight: Duplicates aren’t always "evil"—sometimes they’re valid (e.g., multiple orders from the same customer). The problem arises when they’re unintended.

2. Partial Duplicates

• Definition: Rows that match on some columns (e.g., same user_id but different order_date).
• Production insight: These are harder to spot but often more dangerous (e.g., a user’s email changed, but the user_id stayed the same—now you have two "active" records).

3. DISTINCT

• Definition: SQL keyword to return only unique rows from a query.
• Production insight: Overusing DISTINCT is a code smell—it’s often a band-aid for bad joins or missing GROUP BY.

4. GROUP BY + Aggregation

• Definition: Groups rows by one or more columns and applies an aggregate function (e.g., COUNT(), MAX()).
• Production insight: This is your first line of defense against duplicates in aggregations.

5. ROW_NUMBER() (Window Function)

• Definition: Assigns a unique sequential integer to rows within a partition of a result set.
• Production insight: The most flexible way to deduplicate—lets you keep the "first," "last," or "random" row.

6. PARTITION BY

• Definition: Divides a result set into partitions to which window functions are applied.
• Production insight: Critical for deduplicating within groups (e.g., "keep the latest order per customer").

7. HAVING

• Definition: Filters groups after GROUP BY (unlike WHERE, which filters rows before grouping).
• Production insight: Useful for finding groups with duplicates (e.g., HAVING COUNT(*) > 1).

8. UNION vs. UNION ALL

• Definition:
• UNION = Combines results and removes duplicates.
• UNION ALL = Combines results without deduplication.
• Production insight: UNION ALL is faster (no deduplication overhead) but risks duplicates. Use it only if you’re sure the datasets are disjoint.

9. Surrogate Keys (id, uuid)

• Definition: Artificial primary keys (e.g., auto-incrementing id or uuid) to uniquely identify rows.
• Production insight: If your table lacks a surrogate key, deduplication becomes much harder.

10. Natural Keys (Business Keys)

• Definition: Columns that uniquely identify a row based on business logic (e.g., email, ssn, order_number).
• Production insight: Natural keys can change (e.g., a user updates their email), so they’re not reliable for deduplication alone.

3. Step-by-Step Hands-On: Deduplication in Action

Prerequisites

• A SQL environment (PostgreSQL, MySQL, BigQuery, Snowflake, etc.).
• A table with duplicates (we’ll create one).

Task: Deduplicate a users table while keeping the most recent record per email.

Step 1: Create a test table with duplicates

-- PostgreSQL/Snowflake/BigQuery syntax
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    signup_date TIMESTAMP,
    last_login TIMESTAMP
);

-- Insert some duplicates
INSERT INTO users (email, name, signup_date, last_login) VALUES
    ('[email protected]', 'Alice', '2023-01-01', '2023-01-10'),
    ('[email protected]', 'Alice Smith', '2023-01-01', '2023-02-15'), -- Duplicate email
    ('[email protected]', 'Bob', '2023-01-02', '2023-01-12'),
    ('[email protected]', 'Robert', '2023-01-02', '2023-03-20'), -- Duplicate email
    ('[email protected]', 'Charlie', '2023-01-03', '2023-01-15');

Step 2: Identify duplicates

-- Find emails that appear more than once
SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected output:

email               | duplicate_count
--------------------+----------------
[email protected]   | 2
[email protected]     | 2

Step 3: Deduplicate using ROW_NUMBER()

-- Keep the row with the latest `last_login` for each email
WITH ranked_users AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_login DESC) as rn
    FROM users
)
SELECT
    id, email, name, signup_date, last_login
FROM ranked_users
WHERE rn = 1;

Expected output:

id | email               | name        | signup_date       | last_login
---+---------------------+-------------+-------------------+-------------------
2  | [email protected]   | Alice Smith | 2023-01-01        | 2023-02-15
4  | [email protected]     | Robert      | 2023-01-02        | 2023-03-20
5  | [email protected] | Charlie     | 2023-01-03        | 2023-01-15

Step 4: Update the table (if needed)

-- PostgreSQL: Update the table in-place
WITH ranked_users AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_login DESC) as rn
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id FROM ranked_users WHERE rn > 1
);

-- MySQL: Alternative approach (create a new table)
CREATE TABLE users_deduped AS
WITH ranked_users AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_login DESC) as rn
    FROM users
)
SELECT id, email, name, signup_date, last_login
FROM ranked_users
WHERE rn = 1;

-- Drop the old table and rename the new one
DROP TABLE users;
ALTER TABLE users_deduped RENAME TO users;

Step 5: Verify no duplicates remain

SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected output: (No rows returned)

4. ? Production-Ready Best Practices

Security

• Mask PII before deduplication: If email or ssn is involved, use SHA256 or a hashing function to avoid exposing raw data in logs.
  sql SELECT SHA256(email) as email_hash, COUNT(*) as count FROM users GROUP BY email_hash HAVING COUNT(*) > 1;

Cost Optimization

• Avoid SELECT * in deduplication queries: Only pull the columns you need to reduce I/O.
• Use LIMIT in exploratory queries: If you’re checking for duplicates in a 100M-row table, sample first: sql SELECT email, COUNT(*) as count FROM users GROUP BY email HAVING COUNT(*) > 1 LIMIT 1000; -- Sample first

Reliability & Maintainability

• Add a deduplication_log table: Track when and how deduplication was performed.
  sql CREATE TABLE deduplication_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(255),
deduplication_key VARCHAR(255),
rows_removed INT,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
• Use WHERE clauses to scope deduplication: Don’t deduplicate the entire table if you only need to fix recent data.
  sql -- Only deduplicate users who signed up in the last 30 days WITH ranked_users AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY last_login DESC) as rn
FROM users
WHERE signup_date > CURRENT_DATE - INTERVAL '30 days' ) DELETE FROM users WHERE id IN (SELECT id FROM ranked_users WHERE rn > 1);

Observability

• Log before/after row counts:
  ```sql -- Before SELECT COUNT(*) as before_count FROM users;

-- After SELECT COUNT() as after_count FROM users; `` - Set up alerts for duplicate spikes: If yourHAVING COUNT() > 1` query suddenly returns 1000 rows instead of 10, something’s wrong with your ETL.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which SQL clause removes duplicates?"
2. ❌ WHERE (filters rows, not duplicates)
3. ❌ ORDER BY (sorts, doesn’t deduplicate)

4. ✅ DISTINCT or GROUP BY

5. "How do you keep the most recent row per group?"

6. ✅ ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC)

7. "What’s the difference between UNION and UNION ALL?"

8. UNION = Deduplicates (slower).
9. UNION ALL = No deduplication (faster).

Key ⚠️ Trap Distinctions

• DISTINCT vs. GROUP BY:
• DISTINCT removes duplicates from the entire result set.
• GROUP BY groups rows and applies aggregates (e.g., COUNT()).
• ROW_NUMBER() vs. RANK() vs. DENSE_RANK():
• ROW_NUMBER() = Always unique (ties get arbitrary numbers).
• RANK() = Ties get the same rank, with gaps (e.g., 1, 1, 3).
• DENSE_RANK() = Ties get the same rank, no gaps (e.g., 1, 1, 2).

Scenario-Based Question

"You have a transactions table with duplicate transaction_ids. How do you remove duplicates while keeping the row with the highest amount?"
- ✅ Answer: sql WITH ranked_transactions AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY amount DESC) as rn
FROM transactions ) DELETE FROM transactions WHERE transaction_id IN (
SELECT transaction_id FROM ranked_transactions WHERE rn > 1 );

7. ? Hands-On Challenge

Challenge:
You have a products table with duplicate product_ids. Some rows have NULL in the price column. Write a query to: 1. Keep only one row per product_id.
2. Prefer rows where price IS NOT NULL.
3. If multiple rows have non-NULL price, keep the one with the highest price.

Solution:

WITH ranked_products AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY
                CASE WHEN price IS NOT NULL THEN 0 ELSE 1 END, -- Prefer non-NULL
                price DESC -- Highest price first
        ) as rn
    FROM products
)
SELECT product_id, product_name, price
FROM ranked_products
WHERE rn = 1;

Why it works:
- CASE WHEN price IS NOT NULL THEN 0 ELSE 1 END ensures non-NULL rows are ranked first.
- price DESC breaks ties by keeping the highest price.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions – Deep dive into ROW_NUMBER(), RANK(), etc.
2. BigQuery Deduplication Guide – Google’s official docs with real-world examples.
3. SQLZoo: DISTINCT and GROUP BY – Interactive exercises.
4. "SQL for Data Analysis" (O’Reilly) – Chapter 5 covers deduplication in depth.