Data validation should test whether expected data arrived, rows preserve intended grain, transformations follow business rules, totals reconcile, and report filters display the intended population. Combine automated checks with targeted analyst review. Define severity and stop-publication conditions before a failure occurs so deadlines do not override material quality problems.
Who this is for: Analysts responsible for recurring reports whose readers need reliable totals, trends, and definitions.
- Validate freshness, completeness, uniqueness, validity, consistency, and reconciliation at the layer where each can fail.
- Set expected ranges from business behavior and history rather than treating any nonempty result as valid.
- Connect failed checks to owners, severity, publication decisions, and visible incident communication.
Map risks from source to report
List the source extracts, joins, transformations, semantic measures, filters, and exports that produce the report. For each boundary, ask how data could become late, missing, duplicated, misclassified, or unauthorized. A successful pipeline run only proves that software completed, not that every expected business record arrived.
Identify critical fields and totals based on report decisions. Revenue amount, customer key, event timestamp, currency, and status may deserve stricter checks than optional notes. Define expected grain and row identity. Without grain, a uniqueness test may reject valid line items or accept duplicate orders hidden across several rows.
Build layered checks
Source checks cover file arrival, partition completeness, schema, row volume, and extract timestamps. Transformation checks cover keys, referential integrity, valid codes, date order, sign rules, and duplicate handling. Semantic checks recalculate governed metrics and ratios. Presentation checks verify defaults, labels, filter state, access, and current-period treatment.
Use exact constraints for impossibilities and tolerance bands for normal variation. Negative quantity may be impossible in one table and a valid return in another. Compare row counts and distributions with recent history, but annotate known events so a real promotion is not automatically suppressed. Maintain checks as business processes evolve.
Reconcile and sample
Tie report totals to authoritative control totals at the same scope, currency, and cutoff. Explain accepted differences such as late entries or ledger adjustments. Then trace a small sample from source record through transformation to visual. Aggregate reconciliation can pass even when individual records are assigned to the wrong category.
Inspect distributions, missingness, top values, and abrupt structural breaks using exploratory methods. Compare new and prior runs by segment. Automated rules catch known failure modes, while analyst review can notice new patterns. Save check results with report version and data timestamp so later questions can be answered.
Define failure response
Classify checks as warning, material degradation, or publication blocker. Assign an owner and response time. If a noncritical segment is late, publication may continue with a prominent limitation. If the primary denominator is incomplete, hold the report. Make this policy before deadline pressure appears.
When an issue escapes, correct the data, notify readers, preserve the original result, and add a regression check. Distinguish data correction from revised business truth. A late source record legitimately changes history, while a duplicated join is an error. Track recurring failures to improve upstream systems rather than normalizing manual repair.
Validate a weekly sales report
A Monday dashboard refresh succeeds, but one region's orders were delayed and a new product hierarchy created duplicate joins.
- Check source partition arrival and compare order counts by region with the same weekday and recent range.
- Test order-line uniqueness and dimension-key uniqueness before joining the new product hierarchy.
- Reconcile gross and net sales with the order system at the same Sunday cutoff and currency policy.
- Trace several orders through category assignment and verify dashboard defaults, refresh label, and incomplete-period warning.
- Block publication for duplicated totals, notify the regional delay separately, and add both failures to automated regression checks.
Pre-publication validation runbook
Execute and retain this runbook for every scheduled reporting release.
- Arrival: source partitions, schema, cutoff, row volume, freshness, and expected business coverage.
- Integrity: grain, unique keys, relationships, valid values, date sequence, nulls, and duplicates.
- Meaning: transformation rules, metric formulas, segment assignments, denominators, and period completeness.
- Reconciliation: authoritative totals, scope, currency, accepted variance, samples, and reviewer sign-off.
- Release: severity, owner, blocker decision, limitation notice, archived results, and regression follow-up.
Common mistakes
- Treating a successful scheduled refresh as proof that all expected source records were present.
- Checking only grand totals and missing a join that moves records into the wrong categories.
- Discovering a critical failure at deadline without a pre-agreed rule for holding publication.
Try one
A report total matches finance, but customer segments differ sharply from last week. Describe the validation sequence.
A strong sequence confirms source cutoff and population, checks segment field changes and dimension-key uniqueness, compares distributions, and traces records across joins and classification logic. Matching totals does not rule out misallocation. The response should identify expected business events, quantify affected rows and decisions, assign severity, document any limitation, and add a focused automated check once the cause is known.
Sources
- NIST exploratory data analysis handbookNIST methods for exploring data, checking assumptions, and revealing structure.
- Microsoft Power BI data sourcesOfficial Microsoft reference for connecting Power BI Desktop to source data.