Duplicates and Outliers

The Cost of Duplicates

Duplicate rows are not a cosmetic problem. They distort every downstream calculation that involves counting or summing. A duplicated transaction inflates revenue. A duplicated customer inflates the customer count. A duplicated survey response overrepresents that respondent's views.

The danger is that duplicates are invisible in most summaries. If you run SELECT COUNT(*) FROM orders, duplicates are counted as distinct orders. If you run SELECT SUM(amount) FROM orders, duplicated amounts are summed twice. The results look plausible but are wrong.

Types of Duplicates

Exact duplicates: Two rows are identical in every column. This is the easiest case. It usually indicates a technical failure: a retry in an ETL pipeline, a double-submit on a web form, or a join that produced a Cartesian product.

Key duplicates: Rows share the same natural key (e.g., order_id, email, customer_id) but differ in other columns. This is harder to detect and harder to resolve. Two rows with customer_id = 42 might be the same customer with different addresses, or two different customers who were assigned the same ID by a buggy system.
Fuzzy duplicates: Rows refer to the same entity but differ in representation. 'John Smith', 'J. Smith', and 'Smith, John' are the same person in three formats. Fuzzy duplicates require domain rules or matching algorithms to detect.

Detecting Exact Duplicates

-- Find exact duplicate rows (all columns identical)
SELECT col1, col2, col3, COUNT(*) AS dup_count
FROM orders
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1;

-- Find duplicate keys with differing attributes
SELECT order_id, COUNT(*) AS occurrences
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

The first query groups by all columns to find rows that are completely identical. The second groups by the natural key to find cases where the same key appears multiple times, which may or may not be a problem depending on the schema.

Resolving Duplicates

Once detected, duplicates must be resolved by keeping one row and removing the rest. The choice of which row to keep matters.

-- Keep the most recent row per key
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

This pattern uses a window function to rank rows within each duplicate group and keeps only the top-ranked row. The ORDER BY clause determines which row survives: most recent, highest priority, or whichever rule matches your business logic.

Never delete duplicates without a rule. Arbitrary deletion (e.g., DELETE FROM orders WHERE rowid NOT IN (SELECT MIN(rowid) FROM orders GROUP BY order_id)) keeps whichever row the database happens to return first. That is not a decision; it is a coin flip.

Fuzzy Duplicate Detection

Fuzzy duplicates require more than exact matching. The same entity appears with different representations: 'John Smith' vs 'J. Smith' vs 'Smith, John'. Detecting these requires normalization followed by matching.

-- Normalize names before matching
WITH normalized AS (
    SELECT
        customer_id,
        UPPER(REGEXP_REPLACE(name, '[^A-Za-z ]', '', 'g')) AS name_clean,
        REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS phone_clean
    FROM customers
)
SELECT
    a.customer_id AS id_a,
    b.customer_id AS id_b,
    a.name_clean,
    b.name_clean
FROM normalized a
JOIN normalized b
    ON a.customer_id < b.customer_id
    AND (a.name_clean = b.name_clean OR a.phone_clean = b.phone_clean);

This query normalizes names by removing punctuation and standardizing casing, then joins on the normalized values. The a.customer_id < b.customer_id condition prevents matching a row to itself and avoids returning both (A,B) and (B,A).

Fuzzy matching is powerful but dangerous. Two different customers named 'John Smith' with the same phone number are almost certainly the same person. Two different customers named 'John Smith' with different phone numbers are almost certainly different people. The matching rule must use enough identifying information to avoid false positives. When in doubt, use multiple matching criteria (name AND phone, or name AND email) rather than a single field.

Outliers: Signal or Noise

An outlier is a value that lies far from the bulk of the data. The critical question is not whether a value is an outlier, but whether it is a valid extreme or a data error. The answer determines whether you keep it, cap it, or remove it.

Valid Extremes vs. Data Errors

A $10,000,000 revenue transaction is an outlier. It may also be legitimate: a single enterprise sale in a dataset dominated by $50 consumer transactions. Removing it would understate revenue and misrepresent the business.

A negative age is an outlier. It is also impossible. No human has a negative age. This is a data error, not a valid extreme.

The distinction requires domain knowledge. Statistical methods can flag outliers, but only domain context can classify them.

The IQR Method

The Interquartile Range (IQR) method is the most robust statistical outlier detector. It defines outliers as values that fall more than 1.5 IQRs below Q1 or above Q3.

-- Calculate IQR bounds for revenue
WITH quartiles AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) AS q3
    FROM orders
    WHERE revenue IS NOT NULL
),
bounds AS (
    SELECT
        q1,
        q3,
        q3 - q1 AS iqr,
        q1 - 1.5 * (q3 - q1) AS lower_bound,
        q3 + 1.5 * (q3 - q1) AS upper_bound
    FROM quartiles
)
SELECT
    o.order_id,
    o.revenue,
    b.lower_bound,
    b.upper_bound,
    CASE
        WHEN o.revenue < b.lower_bound THEN 'low_outlier'
        WHEN o.revenue > b.upper_bound THEN 'high_outlier'
        ELSE 'normal'
    END AS outlier_status
FROM orders o
CROSS JOIN bounds b
WHERE o.revenue < b.lower_bound OR o.revenue > b.upper_bound;

The IQR method is robust because it uses quartiles, which are themselves resistant to outliers. A single extreme value does not shift Q1 or Q3 much, so the bounds remain stable.

The Z-Score Method

The z-score measures how many standard deviations a value is from the mean. Values with |z| > 3 are commonly flagged as outliers.

-- Z-score outlier detection
WITH stats AS (
    SELECT
        AVG(revenue) AS mean_rev,
        STDDEV(revenue) AS std_rev
    FROM orders
    WHERE revenue IS NOT NULL
)
SELECT
    o.order_id,
    o.revenue,
    (o.revenue - s.mean_rev) / s.std_rev AS z_score
FROM orders o
CROSS JOIN stats s
WHERE ABS((o.revenue - s.mean_rev) / s.std_rev) > 3;

The z-score method assumes a roughly normal distribution. For heavily skewed data (income, revenue, web traffic), the mean and standard deviation are themselves distorted by outliers, making the z-score unreliable. In those cases, use the IQR method or a log transformation before computing z-scores.

Domain Thresholds

The most reliable outlier detection uses business rules, not statistics. A temperature column with a value of 500°C is an outlier because no sensor in the dataset operates above 200°C. An age column with a value of 200 is an outlier because no human is 200 years old.

-- Domain-based outlier detection
SELECT *
FROM sensor_readings
WHERE temperature > 200 OR temperature < -50;

SELECT *
FROM customers
WHERE age < 0 OR age > 120;

Domain thresholds are precise and explainable. They should always be the first line of defense, with statistical methods as a backstop for cases where domain limits are unknown.

Handling Outliers: Remove, Cap, or Keep

Once an outlier is detected and classified, you must decide what to do with it. The decision depends on whether the outlier is a data error or a valid extreme, and on how the data will be used.

Removal

Remove outliers that are confirmed data errors. A negative age, a transaction dated in the future, a temperature of 9999°C — these are not real observations. They are artifacts of data collection or processing.

-- Remove confirmed data errors
DELETE FROM customers
WHERE age < 0 OR age > 120;

-- Remove future-dated transactions
DELETE FROM orders
WHERE order_date > CURRENT_DATE;

Removal is appropriate when the outlier is impossible or clearly erroneous. It is not appropriate when the outlier is a valid extreme. Removing a legitimate $10M sale because it is 50 standard deviations from the mean is a mistake.

Capping (Winsorization)

Capping replaces outlier values with the nearest non-outlier bound. Values above the 99th percentile are set to the 99th percentile. Values below the 1st percentile are set to the 1st percentile.

-- Cap revenue at the 99th percentile
WITH pct AS (
    SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY revenue) AS p99
    FROM orders
    WHERE revenue IS NOT NULL
)
UPDATE orders
SET revenue = (SELECT p99 FROM pct)
WHERE revenue > (SELECT p99 FROM pct);

Capping preserves the row (and its other columns) while limiting the influence of the extreme value. It is useful when the outlier is valid but would dominate a sum or mean. Capping is common in financial reporting, where a single extreme value can distort a portfolio's risk metrics.

Keeping with Flagging

When an outlier is a valid extreme, keep it and flag it. Add a column that marks the row as an outlier so downstream analysis can include or exclude it as needed.

-- Flag outliers without removing them
ALTER TABLE orders ADD COLUMN is_outlier INT DEFAULT 0;

WITH bounds AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) AS q3
    FROM orders
    WHERE revenue IS NOT NULL
)
UPDATE orders
SET is_outlier = 1
WHERE revenue < (SELECT q1 - 1.5 * (q3 - q1) FROM bounds)
   OR revenue > (SELECT q3 + 1.5 * (q3 - q1) FROM bounds);

Flagging is the most conservative strategy. It preserves all data and lets each analysis decide how to handle outliers. The cost is added complexity: every downstream query must remember to filter or weight by the flag.

The Decision Framework

1. Is the value impossible? (negative age, future date) → Remove
2. Is the value valid but extreme? (high-value sale) → Keep with flag, or cap if it distorts aggregates
3. Is the value suspicious but unverifiable? → Flag and investigate
4. Does the analysis depend on extremes? (fraud detection, risk modeling) → Keep; outliers are the signal

The last point is critical. In fraud detection, the outliers are the entire point. Removing them would destroy the model. Always consider the downstream use before deciding to remove or cap.