data-validation-join-explosion

name	data-validation-join-explosion
description	Sub-skill of data-validation: Join Explosion (+6).
version	1.0.0
category	data-analytics
type	reference
scripts_exempt	true

Join Explosion (+6)

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.

Timezone Mismatches

The problem: Different data sources use different timezones, causing misalignment.

Examples:

• Event timestamps in UTC vs. user-facing dates in local time
• Daily rollups that use different cutoff times

How to prevent: Standardize all timestamps to a single timezone (UTC recommended) before analysis. Document the timezone used.

Selection Bias in Segmentation

The problem: Segments are defined by the outcome you're measuring, creating circular logic.

Examples:

• "Users who completed onboarding have higher retention" -- obviously, they self-selected
• "Power users generate more revenue" -- they became power users BY generating revenue

How to prevent: Define segments based on pre-treatment characteristics, not outcomes.