Checking Duplicates And Outliers

Why this matters

As a BI Analyst, you turn raw data into decisions. Duplicates inflate counts, mislead KPIs, and break joins. Outliers skew averages, distort trends, and can hide real issues or opportunities. Reliable dashboards start with clean inputs.

• Customer analytics: deduplicate emails before counting active users.
• Sales dashboards: check order_id uniqueness to avoid double revenue.
• Operations: flag sensor spikes so SLAs and alerts stay trustworthy.

Who this is for

• BI Analysts and data-focused PMs who own dashboards and reports.
• Junior data practitioners learning practical quality checks.
• Anyone asked to explain unexpected counts or odd spikes.

Prerequisites

• Basic SQL (SELECT, GROUP BY, WHERE).
• Comfort with spreadsheets or a scripting tool (e.g., Python) for quick calculations.
• Understanding of your dataset’s “grain” (what one row represents).

Concept explained simply

Duplicates are rows that represent the same real-world entity more than once (exactly the same row or sharing the same business key like email or order_id). Outliers are values far from the rest of the data; they can be errors or legitimate rare events. You detect both to prevent wrong metrics and to drive better decisions.

Mental model

Think of your dataset as a collection of labeled boxes on shelves. Duplicates are two boxes with the same label; you only want one per label. Outliers are boxes that are too big or too small to fit with the others; you inspect them before deciding to keep or move them.

Core techniques

Worked examples

-- 1) Find duplicate order_ids
SELECT order_id, COUNT(*) AS cnt
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- 2) Inspect all rows for duplicate order_ids
SELECT o.*
FROM orders o
JOIN (
  SELECT order_id
  FROM orders
  GROUP BY order_id
  HAVING COUNT(*) > 1
) d USING(order_id);

-- 3) Deduplicate by latest status_date
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY status_date DESC
  ) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1; -- one row per order_id

import pandas as pd
s = pd.Series([12,13,14,15,16,17,18,55,19,20,21,22,100])
q1, q3 = s.quantile(0.25), s.quantile(0.75)
iqr = q3 - q1
lower, upper = q1 - 1.5*iqr, q3 + 1.5*iqr
outliers = s[(s < lower) | (s > upper)]
print(q1, q3, lower, upper)
print(outliers.tolist())  # [55, 100]

Step-by-step recipe

1. Confirm the grain: what does one row represent? Choose the key that must be unique (e.g., order_id).
2. Profile duplicates: count duplicates by key and inspect samples.
3. Pick a deduping rule: latest timestamp, highest quality flag, or merge logic.
4. Profile outliers: choose IQR or z-score; compute fences; review tails.
5. Decide action: keep, cap, correct, or exclude. Document your rule.
6. Re-run metrics after cleaning: confirm numbers now make sense.

Exercises

Practice mirrors the tasks below. Compare your answers with the provided solutions. Use the checklist to self-review.

-- Sample rows
customer_id | email
------------+-----------------
1           | a@example.com
2           | b@example.com
3           | a@example.com
4           | c@example.com
5           | c@example.com

-- 1) Emails with duplicate counts
SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- 2) All rows with duplicate emails
SELECT c.*
FROM customers c
JOIN (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
) d USING(email);

Self-check checklist

• I can explain the data grain and key used for uniqueness.
• I can count and inspect duplicate keys.
• I can implement a deterministic dedupe rule (e.g., latest record).
• I can compute IQR fences and/or z-scores and flag outliers.
• I document cleaning decisions and re-validate metrics after changes.

Common mistakes and how to self-check

• Using the wrong key for uniqueness. Self-check: Write a one-sentence "row grain" statement and verify with stakeholders.
• Dropping legitimate rare events. Self-check: Sample outliers and annotate cause (error vs true). Keep if business-valid.
• Relying only on average to spot outliers. Self-check: Always view distribution (min, Q1, median, Q3, max).
• Partial dedup without a tie-breaker. Self-check: If two rows have same key and timestamp, define a secondary rule.
• Forgetting to re-check downstream joins. Self-check: After dedupe, re-run joins and confirm row counts and KPIs.

Practical projects

• Customer 360: Build a deduped customer dimension using email and phone with a clear survivorship rule.
• Sales quality monitor: A daily job that counts duplicate order_ids and flags outlier order_values.
• Marketing list hygiene: Standardize and deduplicate campaign contact lists; measure impact on send volume.

Learning path

• Before this: Data profiling basics (distributions, null checks).
• This lesson: Duplicates and outliers—detect, decide, document.
• Next: Handling missing values, standardization, and validation rules across pipelines.

Quick Test

The quick test is available to everyone; only logged-in users get saved progress.

Next steps

• Automate: Turn your duplicate and outlier checks into repeatable queries or notebook cells.
• Document: Add your rules to a shared data quality playbook.
• Monitor: Schedule counts of duplicates and outliers and alert on thresholds.

Mini challenge

You receive a weekly orders extract where order_id should be unique. This week, duplicate order_ids increased from 0.2% to 3%. Draft a 5-step plan to identify the source, stop the growth, and patch existing data without breaking dashboards. Include how you will validate success.