Data Validation

Data Validation Skill

Pre-delivery QA checklist, common data analysis pitfalls, result sanity checking, and documentation standards for reproducibility.

Pre-Delivery QA Checklist

Run through this checklist before sharing any analysis with stakeholders.

Data Quality Checks

• Source verification: Confirmed which tables/data sources were used. Are they the right ones for this question?
• Freshness: Data is current enough for the analysis. Noted the "as of" date.
• Completeness: No unexpected gaps in time series or missing segments.
• Null handling: Checked null rates in key columns. Nulls are handled appropriately (excluded, imputed, or flagged).
• Deduplication: Confirmed no double-counting from bad joins or duplicate source records.
• Filter verification: All WHERE clauses and filters are correct. No unintended exclusions.

Calculation Checks

• Aggregation logic: GROUP BY includes all non-aggregated columns. Aggregation level matches the analysis grain.
• Denominator correctness: Rate and percentage calculations use the right denominator. Denominators are non-zero.
• Date alignment: Comparisons use the same time period length. Partial periods are excluded or noted.
• Join correctness: JOIN types are appropriate (INNER vs LEFT). Many-to-many joins haven't inflated counts.
• Metric definitions: Metrics match how stakeholders define them. Any deviations are noted.
• Subtotals sum: Parts add up to the whole where expected. If they don't, explain why (e.g., overlap).

Reasonableness Checks

• Magnitude: Numbers are in a plausible range. Revenue isn't negative. Percentages are between 0-100%.
• Trend continuity: No unexplained jumps or drops in time series.
• Cross-reference: Key numbers match other known sources (dashboards, previous reports, finance data).
• Order of magnitude: Total revenue is in the right ballpark. User counts match known figures.
• Edge cases: What happens at the boundaries? Empty segments, zero-activity periods, new entities.

Presentation Checks

• Chart accuracy: Bar charts start at zero. Axes are labeled. Scales are consistent across panels.
• Number formatting: Appropriate precision. Consistent currency/percentage formatting. Thousands separators where needed.
• Title clarity: Titles state the insight, not just the metric. Date ranges are specified.
• Caveat transparency: Known limitations and assumptions are stated explicitly.
• Reproducibility: Someone else could recreate this analysis from the documentation provided.

Common Data Analysis Pitfalls

Join Explosion

The problem: A many-to-many join silently multiplies rows, inflating counts and sums.

How to detect:

-- Check row count before and after join
SELECT COUNT(*) FROM table_a;  -- 1,000
SELECT COUNT(*) FROM table_a a JOIN table_b b ON a.id = b.a_id;  -- 3,500 (uh oh)

How to prevent:

• Always check row counts after joins
• If counts increase, investigate the join relationship (is it really 1:1 or 1:many?)
• Use COUNT(DISTINCT a.id) instead of COUNT(*) when counting entities through joins

Survivorship Bias

The problem: Analyzing only entities that exist today, ignoring those that were deleted, churned, or failed.

Examples:

• Analyzing user behavior of "current users" misses churned users
• Looking at "companies using our product" ignores those who evaluated and left
• Studying properties of "successful" outcomes without "unsuccessful" ones

How to prevent: Ask "who is NOT in this dataset?" before drawing conclusions.

Incomplete Period Comparison

The problem: Comparing a partial period to a full period.

Examples:

• "January revenue is $500K vs. December's $800K" -- but January isn't over yet
• "This week's signups are down" -- checked on Wednesday, comparing to a full prior week

How to prevent: Always filter to complete periods, or compare same-day-of-month / same-number-of-days.

Denominator Shifting

The problem: The denominator changes between periods, making rates incomparable.

Examples:

• Conversion rate improves because you changed how you count "eligible" users
• Churn rate changes because the definition of "active" was updated

How to prevent: Use consistent definitions across all compared periods. Note any definition changes.

Average of Averages

The problem: Averaging pre-computed averages gives wrong results when group sizes differ.

Example:

• Group A: 100 users, average revenue $50
• Group B: 10 users, average revenue $200
• Wrong: Average of averages = ($50 + $200) / 2 = $125
• Right: Weighted average = (100*$50 + 10*$200) / 110 = $63.64

How to prevent: Always aggregate from raw data. Never average pre-aggregated averages.