Lesson and practical work
Professional outcomes
- Create validation queries for coverage, uniqueness, completeness, and reconciliation.
- Use control totals and exception outputs rather than visual inspection alone.
- Identify why an apparently reasonable query may still be unreliable.
- Explain validation evidence in a portfolio or interview.
Need a refresher?Validation is part of the query, not an afterthoughtView concise foundation
A professional analysis includes control queries. At minimum check date coverage, row and key counts, duplicate keys, missing join matches, invalid categories, metric ranges, and reconciliation to an independent total.
The monthly report looks reasonable—but the latest day is missing
Revenue is down 7% and every query runs. A coverage check shows the source loaded only through the 29th while the comparison month contains 31 days. The result is computationally correct and analytically unfit for a full-month conclusion.
Build a compact SQL QA pack
-- Coverage
SELECT MIN(order_date), MAX(order_date), COUNT(*) FROM orders;
-- Key uniqueness
SELECT order_id, COUNT(*)
FROM orders GROUP BY order_id HAVING COUNT(*) > 1;
-- Population and reconciliation
SELECT status, COUNT(*) AS rows, SUM(net_revenue) AS revenue
FROM orders GROUP BY status;
-- Missing dimension matches
SELECT COUNT(*) AS unmatched_customers
FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;Record expected outcomes and thresholds. A query returning zero duplicate keys is evidence only if order_id is supposed to be unique.
What the result means—and does not mean
Validation does not guarantee truth, but it makes important failure modes observable. The missing two days may fully or partly explain the decline; the correct communication is to withhold the full-month conclusion until coverage is comparable.
Analyst's checklist
- Check min/max dates and refresh timestamps.
- Compare rows with distinct business keys.
- Profile status and category distributions.
- Quantify unmatched join keys.
- Reconcile totals before and after transformations.
Common mistakes and failure modes
- Treating zero rows returned as proof without an expectation.
- Checking only the final output, not intermediate stages.
- Using fixed tolerances unrelated to business materiality.
- Running QA manually with no repeatable record.
Challenge yourself
A dashboard total matches Finance exactly, but one region is overstated and another understated by the same amount.
- Why did total reconciliation fail to detect the issue?
- Which dimensional checks would expose it?
- What type of mapping problem might cause this?
Show hint
Aggregate agreement can hide offsetting classification errors.
Show analytical guidance
Reconcile at multiple levels, inspect unknown and changed mappings, and compare region assignment over time. Control totals need the same dimensional depth as the decision.
How this may appear in an interview
What validation queries do you routinely run?
What a strong candidate should communicate
Describe checks for coverage, uniqueness, nulls, accepted values, join cardinality, unmatched keys, ranges, and reconciliation. Then explain how failures affect release decisions and communication.
How would you automate these checks in a production workflow?
Where this skill becomes evidence
Publish a QA summary with test name, expectation, result, status, and action. Do not expose sensitive records; show professional validation design.
Starter SQL
-- Add coverage, key, population, and reconciliation checks
SELECT MIN(order_date), MAX(order_date), COUNT(*)
FROM orders;Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
Why can a correct total hide a regional mapping problem?
Show guidance
Design a five-query QA pack for monthly revenue.
Show guidance
Which validation failures should block dashboard publication?
Show guidance
Move from reviewing expert reasoning to performing the analysis.
Guided practice can add the synthetic dataset, multi-step assignment, validation checks, solution comparison, project workflow, and progress tracking.
- Work with the data
- Make and defend assumptions
- Validate the result
- Build portfolio evidence