The intake checklist, in order:

1. Schema checks:

Row count (seem right?).
Column list and types. Any unexpected nulls?
Primary key uniqueness.
Foreign key coverage (all user_id values exist in users table).

2. Basic distribution checks:

MIN, MAX, AVG, STDDEV for numerics. Any obviously impossible values (negative revenue, dates from 1970, ages over 200)?
Top-N and bottom-N frequencies for categoricals. Any leaky sentinel values ('N/A', 'TEST', 'null' as string)?
COUNT(*) vs COUNT(DISTINCT col) — how many duplicates?

3. Temporal checks:

Earliest and latest timestamps. Backfill edge cases?
Distribution by day/week. Any gaps? Any anomalous spikes?
Event order sanity — does checkout_at > cart_at > session_start_at?

4. Business logic:

Does the data match known facts? Q4 revenue should be ~15-20% higher than Q1.
Does the row count match what we expect from other sources (e.g., compare to billing totals)?

5. Relational integrity:

Joins with parent tables don't lose rows silently (fan-out or missing keys).
Cross-reference with ledger / source-of-truth table if available.

Concrete SQL pattern:

-- Schema + counts
SELECT COUNT(*) FROM user_transactions;
SELECT COUNT(DISTINCT transaction_id) FROM user_transactions;  -- should equal above

-- Distribution profile
SELECT 
  MIN(amount), MAX(amount), AVG(amount), STDDEV(amount),
  COUNT(*) AS n, COUNT(amount) AS non_null_n
FROM user_transactions;

-- Temporal distribution
SELECT DATE_TRUNC('day', created_at), COUNT(*) 
FROM user_transactions 
GROUP BY 1 ORDER BY 1;  -- look for gaps or spikes

-- Referential integrity
SELECT COUNT(*) FROM user_transactions t
LEFT JOIN users u ON t.user_id = u.id
WHERE u.id IS NULL;  -- orphaned transactions

What the checks catch:

Pipeline failures. A gap in the day distribution catches a silent drop.
Schema drift. A new column appearing or a type change catches upstream changes.
Data quality bombs. 'Revenue' is 15% negative — refunds? errors? flipped sign?
Integration bugs. User IDs don't match users table → join will silently drop rows.

What NOT to skip:

Comparing row count across days. Easy to miss, high-signal.
Referential integrity. 'Ah, all our revenue is great!' until you realize half the transactions can't be attributed to a user.

Output:

A profiling notebook (or checkbox doc) that summarizes:

What the data looks like.
What you validated.
Open questions for the data owner.

What senior candidates add:

Automate the checks. Tools like Great Expectations, Soda, dbt tests. Run on every ingest, alert on failures.
Update the checks as you learn. Every data-quality incident should produce a new test that catches it going forward.
Reference data contracts. Formal expectations between producer and consumer reduce the reactive validation work.

What validation checks do you run on a new dataset?

Draft your answer

Stuck? Peek at a hint

More data cleaning questions