Validate Data Skill

QA an analysis before it reaches stakeholders. Systematically check methodology, accuracy, completeness, and potential bias to catch errors before they cause bad decisions.

Workflow

Step 1: Understand What Was Done

Before checking the work, understand the analysis:

1. What question was being answered?
2. What data was used? Sources, tables, date ranges.
3. What methodology was applied? Aggregations, filters, joins, statistical methods.
4. What conclusions were drawn?
5. Who is the audience? What decisions will this inform?

Step 2: Methodology Review

Check the analytical approach for logical soundness:

Data Selection

• Is the date range appropriate for the question?
• Are the right tables being used? Could a different source be more accurate?
• Are filters correctly applied? Are important populations being excluded?
• Is the data fresh enough? Check the last-updated timestamp.
• Are there known data quality issues in the source tables?

Aggregation Logic

• Is the grain correct? (One row per what?)
• Are metrics calculated at the right level before aggregating up?
• Could joins be creating duplicates (fan-out)?
• Is COUNT vs COUNT(DISTINCT) used correctly?
• Are weighted averages used where needed (instead of simple averages)?

Comparison Methodology

• Are comparisons apples-to-apples? Same time period lengths, same populations?
• Are percentages using the correct denominator?
• Are changes measured in absolute or relative terms, and is the right one used?
• Are seasonal effects accounted for? (Comparing to same period last year vs. prior period)

Step 3: Accuracy Checks

Run these numerical validations:

Sanity Checks

# Does the total match a known source of truth?
assert abs(computed_revenue - finance_reported_revenue) / finance_reported_revenue < 0.02, \
    f"Revenue differs by {abs(computed_revenue - finance_reported_revenue):,.0f}"

# Do the parts sum to the whole?
segment_total = segment_breakdown['revenue'].sum()
assert abs(segment_total - overall_total) < 1.0, \
    f"Segments sum to {segment_total:,.0f} but total is {overall_total:,.0f}"

# Are percentages in valid range?
assert all(0 <= p <= 1 for p in pct_values), "Percentages outside 0-100% range"

# Do percentages sum to 100%?
assert abs(sum(pct_values) - 1.0) < 0.001, f"Percentages sum to {sum(pct_values):.4f}"

Cross-Validation

# Compute the same metric a different way
revenue_from_orders = orders_df['amount'].sum()
revenue_from_line_items = line_items_df['line_amount'].sum()
assert abs(revenue_from_orders - revenue_from_line_items) < 100, \
    "Revenue from orders vs line items differs significantly"

# Check against a known benchmark
assert 0.01 < conversion_rate < 0.20, \
    f"Conversion rate of {conversion_rate:.2%} is outside expected range (1-20%)"

Boundary Checks

# No future dates
assert df['date'].max() <= pd.Timestamp.today(), "Data contains future dates"

# No negative values where they should not exist
assert (df['quantity'] >= 0).all(), "Negative quantities found"

# No nulls in required fields
assert df['customer_id'].notna().all(), "Null customer IDs found"

# Row count reasonableness
assert 1000 < len(df) < 10_000_000, f"Row count {len(df):,} seems unusual"

Step 4: Completeness Check

Verify nothing important is missing:

• Are all segments/categories represented? Compare unique values to a master list.
• Are there gaps in the time series? Check for missing dates or periods.
• Is there survivorship bias? Are you only looking at current customers and missing churned ones?
• Are edge cases handled? Zero values, nulls, new accounts, recently changed statuses.
• Did any filters accidentally remove too much data? Check row counts at each step.

# Date completeness
expected_dates = pd.date_range(start_date, end_date, freq='D')
actual_dates = df['date'].dt.normalize().unique()
missing = set(expected_dates) - set(actual_dates)
if missing:
    print(f"WARNING: {len(missing)} missing dates: {sorted(missing)[:5]}...")

# Category completeness
expected_categories = {'organic', 'paid', 'referral', 'direct', 'email'}
actual_categories = set(df['channel'].unique())
missing_cats = expected_categories - actual_categories
if missing_cats:
    print(f"WARNING: Missing categories: {missing_cats}")

Step 5: Bias Assessment

Check for analytical biases:

Selection Bias

• Are you only analyzing data that is available, missing important populations?
• Is the sample representative of the population you want to generalize to?
• Are you selecting on the dependent variable (looking at successes only)?

Survivorship Bias

• Are you only looking at entities that still exist (active users, current customers)?
• Should the analysis include churned/deleted/inactive entities?

Simpson's Paradox

• Could the overall trend reverse when broken down by subgroups?
• Test by running the analysis for each major segment separately.

Cherry-Picking

• Was the time period chosen to support a particular narrative?
• What happens if you extend the window by a week in either direction?

Confounding Variables

• Could an unmeasured third variable explain the observed relationship?
• Was there a product change, holiday, or external event during the analysis period?

Step 6: Presentation Review

Check how results are communicated:

• Are charts misleading? (Truncated y-axis, inconsistent scales, 3D effects)
• Is precision appropriate? (Too many decimal places for the audience)
• Are comparisons fair? (Different population sizes, different time periods)
• Are error bars or confidence intervals shown where appropriate?
• Are caveats and limitations clearly stated?
• Is the conclusion supported by the data, or does it overreach?

Output Format

## Validation Report

### Status: [PASS / PASS WITH CAVEATS / FAIL]

### Checks Performed
| Check | Status | Notes |
|-------|--------|-------|
| Methodology | [Pass/Fail] | [Details] |
| Accuracy | [Pass/Fail] | [Details] |
| Completeness | [Pass/Fail] | [Details] |
| Bias | [Pass/Fail] | [Details] |

### Issues Found
1. **[Severity: Critical/Major/Minor]**: [Description and impact]
2. **[Severity]**: [Description]

### Recommendations
- [What to fix before sharing]
- [What caveats to add]

### Confidence Level
[How confident are we that this analysis is correct and ready to share?
High / Medium / Low with explanation]

Examples

Example 1: Pre-Presentation Check

Review a quarterly business review deck. Verify KPI calculations match finance systems, check that segment breakdowns sum to totals, validate year-over-year comparisons.

Example 2: SQL Query Validation

Check a complex query's results. Run the same calculation a different way, verify join correctness by checking row counts, confirm filters match the stated scope.

Example 3: A/B Test Results Review

Validate experiment analysis. Check sample sizes, verify randomization, look for novelty effects, confirm the metric definition matches the hypothesis.

Best Practices

1. Always compare to a known source of truth. If finance says revenue was $10M, your analysis should match within a small tolerance.
2. Check the "obvious" things. Do the numbers pass a smell test? Is a 500% growth rate plausible?
3. Compute the same number two ways. If two independent paths give the same answer, confidence is high.
4. Look at the data, not just the summary. Scan raw rows. Outliers and errors are often visible in the detail.
5. Question the denominator. Most analytical errors come from wrong denominators in rates and percentages.
6. Check what happens at the boundaries. First day, last day, new customers, edge cases.
7. Ask "what is missing?" The absence of data is often more important than its presence.
8. Verify causality claims. Most business data is observational. Correlation does not imply causation.
9. Run the analysis on a subset you can verify manually. If you can hand-calculate one customer's numbers and they match, that builds confidence.
10. Document what you checked. Future analysts should know what validation was performed.