Duplicates
The same business entity appears more than once when it should be unique.
Analysis · Module 05
Data Quality & Validation.
A polished chart built on broken data is still broken. Validation is how analysts earn the right to make a recommendation.
Core idea
Before explaining why the business changed, prove that the data did not change underneath you.
01
Context
The dashboard says one city collapsed overnight.
“Dhaka looks terrible today. Before we call Operations, can you confirm whether this is real?”
This is where a strong analyst resists storytelling. A dramatic metric can be caused by a real operational problem, a delayed pipeline, a changed mapping, duplicated rows or a broken filter.
Your first job is not diagnosis. It is trust classification: can this result be used yet?
02
Failure modes
Learn the shapes of bad data.
Missingness
Nulls or absent rows are concentrated in important segments or periods.
Broken joins
Keys fail to match or one-to-many relationships multiply measures.
Freshness
Recent data is incomplete, late or stalled.
Definition drift
A field or business rule changed but the analysis did not.
Impossible values
Dates, amounts, statuses or rates violate business constraints.
Rule
Do not treat a surprising business result as interesting until you have ruled out boring data failures.
LIVE
Duplicate rows · interactive visual
One repeated entity can quietly move the KPI.
ordersorder_id should be unique
1001$25
1002$40
1002$40DUPLICATE
1003$45
Row count34
Distinct orders3
SUM(GMV)$110$150
Trust statusPassStop
✓ Row count = distinct key count × Row count moved; business entity count did not
Uniqueness is behaving as expected.
This is why COUNT(*) and COUNT(DISTINCT order_id) belong in your first-pass QA.
03
First-pass QA
Compare the shape of the data before the KPI.
| Check | Yesterday | Today | Change |
|---|---|---|---|
| orders row count | 148,220 | 154,870 | +4.5% |
| distinct order_id | 148,220 | 148,230 | +0.01% |
| gross_value sum | $3.84M | $4.09M | +6.5% |
| null city_id | 0.3% | 7.8% | +7.5 pp |
| payment rows / order | 1.12 | 1.48 | +32% |
| last event loaded | 09:58 | 08:14 | -104 min |
The row count rose while distinct orders barely moved. Null city IDs spiked. Payment rows per order increased. And the newest event is almost two hours older than yesterday's comparable load.
Those are strong reasons to investigate the pipeline before interpreting a city-level decline.
04
Control queries
Use simple queries to challenge complex ones.
Uniqueness and duplicate check
SELECT
COUNT(*) AS row_count,
COUNT(DISTINCT order_id) AS distinct_orders,
COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows
FROM orders
WHERE created_at::date = CURRENT_DATE;Freshness check
SELECT
MAX(updated_at) AS latest_row_updated_at,
MAX(created_at) AS latest_business_event_at
FROM orders;Join inflation check
WITH before_join AS (
SELECT
COUNT(DISTINCT order_id) AS orders,
SUM(gross_value) AS gmv
FROM orders
),
after_join AS (
SELECT
COUNT(DISTINCT o.order_id) AS orders,
SUM(o.gross_value) AS gmv
FROM orders o
LEFT JOIN payments p
ON o.order_id = p.order_id
)
SELECT * FROM before_join
UNION ALL
SELECT * FROM after_join; 05
Challenge
Decide whether the result is trustworthy.
Exercise · 15 minutes
Business incident or data incident?
- Which QA signals are most concerning in the snapshot?
- What would you check to explain the jump in payment rows per order?
- How could null city_id create an apparent Dhaka decline?
- Would you send the -34% number to leadership yet? Why?
- What is the minimum evidence needed before declaring a real business issue?
- How would you communicate uncertainty while the investigation is open?
Reveal one defensible response
StatusNot decision-ready
ReasonNull city mapping and freshness changed materially.
Next checkReconcile unmapped orders and source-event freshness.
Stakeholder message“The decline may be real, but current city-level data has integrity issues. I would not act on the -34% yet.”
06
AI assist
Ask AI to generate tests, not just answers.
Data-quality review prompt
I have an analysis showing Dhaka completed orders down 34% day over day.
Before explaining the business cause, act as a data-quality reviewer.
Known observations:
- table row count +4.5%
- distinct order_id roughly flat
- null city_id increased from 0.3% to 7.8%
- payment rows per order increased from 1.12 to 1.48
- newest loaded business event is 104 minutes older than yesterday's comparable load
Your job:
1. Rank the most plausible data-quality risks.
2. Propose SQL checks for uniqueness, nulls, freshness and join inflation.
3. State what evidence would make the result decision-ready.
4. Do not explain the business decline until the integrity checks pass. 07
Validation
The analyst QA checklist.
01
Did row count and distinct business-key count move as expected?
02
Are supposed-to-be-unique keys actually unique?
03
Did null rates change by field, segment or date?
04
Did a join change the number of base entities?
05
Do sums and rates reconcile to a simpler source query?
06
Is the latest available timestamp fresh enough for this decision?
07
Are extreme or impossible values explainable?
08
Could a schema or business-rule change explain the anomaly?
PRACTICE THIS NOW ↓
Interactive case · L04
Dashboard Trust Incident
A city dashboard says orders collapsed 34%. Inject duplicates, null mappings and stale data, then decide whether to escalate.
08
Worked conclusion
Validation changes what you are allowed to say.
“Dhaka demand collapsed 34%, so Operations needs to investigate immediately.”
“The city-level decline is not yet trustworthy because city mapping and data freshness changed materially.”
Reprocessed city mapping, complete event freshness and reconciled order counts should reproduce the decline before escalation.
Separate data confidence from business confidence. A plausible number is not automatically a usable number.
Analyst habit
Every important analysis should contain at least one control number you already trust, one freshness check and one explicit reconciliation step.