Lesson and practical work
Professional outcomes
- Explain table grain and why it controls valid aggregation.
- Use row counts, distinct keys, ranges, null checks, and reconciliation professionally.
- Recognize incomplete periods, duplicates, invalid values, and definition changes.
- Communicate confidence and limitations without making the work sound unusable.
Need a refresher?Data quality is fitness for a specific useView concise foundation
Clean data is not an absolute state. Data is trustworthy for an analysis when grain, coverage, definitions, lineage, and known limitations are suitable for the decision. A technically valid table can still be inappropriate for a metric.
A revenue report is 18% above Finance
Your dashboard sums invoice lines and reports $2.36M. Finance reports $2.00M. Before changing formulas, determine whether both reports represent the same population: invoice status, accounting date, tax, credits, currency, and cutoff may differ.
Build a validation ladder
- Coverage: latest date, row count, and source systems.
- Grain: one row per invoice, line, or payment?
- Keys: duplicate invoice IDs and one-to-many relationships.
- Validity: null dates, negative values, invalid status, currency.
- Reconciliation: reproduce a known total under the Finance definition.
- Movement: explain adjustments from raw to reported totals.
Invoice-line total 2.36M
Less tax excluded by Finance 0.14M
Less draft and void invoices 0.09M
Less credits posted in period 0.13M
Reconciled net invoiced revenue 2.00MWhat the result means—and does not mean
The difference was not necessarily a data error; it was a definition mismatch. The useful outcome is a reconciliation bridge and a documented definition. Future dashboards should expose that definition and test it automatically.
Analyst's checklist
- State the grain of every source table.
- Compare COUNT(*) with distinct business keys.
- Check minimum and maximum dates before trend analysis.
- Profile nulls, invalid categories, and unexpected signs.
- Reconcile an aggregate with an independent source.
Common mistakes and failure modes
- Removing duplicates without defining the correct record.
- Treating all negative revenue as invalid.
- Using a partial month in a full-month comparison.
- Saying the data is wrong before reconciling definitions.
Challenge yourself
After joining invoices to payments, invoice revenue doubles for customers who paid in installments.
- What is the grain of each table?
- Which value should be aggregated before the join?
- How would you prove the correction is reliable?
Show hint
One invoice can have multiple payment rows. Aggregate payments to invoice grain or keep invoice revenue in a separate aggregate.
Show analytical guidance
The join is structurally valid but analytically unsafe for summing invoice revenue. Compare pre- and post-join row counts and totals, then reconcile the correction.
How this may appear in an interview
How do you validate an analysis before presenting it?
What a strong candidate should communicate
Confirm definitions and grain, inspect coverage and keys, profile anomalies, reconcile totals, test edge cases, review transformations, and communicate material limitations. Give an example where validation changed the result.
What if there is no trusted report to reconcile against?
Where this skill becomes evidence
Add a validation appendix with grain, key tests, date coverage, reconciliation checks, and known limitations. A concise QA record makes a synthetic project more credible.
Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
What does table grain mean, and why does it matter after a join?
Show guidance
Design five checks for a monthly invoice report.
Show guidance
How would you explain an unresolved 1.5% difference from Finance?
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