ufo-ai/ufo-core/data-validation

Data Validation

Pre-delivery QA checks, common pitfalls, and result sanity checking.

Pre-Delivery Checklist

Data quality:

• Source tables are correct for this question; freshness noted ("as of" date)
• No unexpected gaps in time series or missing segments
• Null rates checked; nulls handled explicitly (excluded, imputed, or flagged)
• No double-counting from bad joins or duplicate source records

Calculations:

• GROUP BY includes all non-aggregated columns; aggregation grain matches the question
• Rate/percentage denominators are correct and non-zero
• Time period comparisons use equal-length complete periods
• JOIN types are appropriate; row counts verified before and after joins
• Metric definitions match stakeholder expectations

Reasonableness:

• Numbers in plausible range; cross-referenced against dashboards or prior reports
• No unexplained jumps/drops in time series
• Edge cases checked (empty segments, zero-activity periods, new entities)

Common Pitfalls

Join explosion: A many-to-many join silently multiplies rows. Always check COUNT(*) before and after. Use COUNT(DISTINCT entity_id) when counting through joins.

Incomplete period comparison: Comparing a partial month to a full month. Filter to complete periods or compare same number of days.

Denominator shifting: Rate improves because the definition of "eligible" changed, not because performance improved. Use consistent definitions across compared periods.

Average of averages: Averaging pre-aggregated averages ignores group size differences. Aggregate from raw data.

Timezone mismatch: Event timestamps in UTC vs. user-facing dates in local time cause off-by-one-day errors. Standardize to a single timezone before analysis.

Selection bias in segmentation: "Power users have higher revenue" is circular if you defined power users BY revenue. Define segments on pre-treatment characteristics.

Sanity Checks

Red flags: >50% period-over-period change without cause, exact round numbers, rates at exactly 0% or 100%, results that perfectly confirm the hypothesis, identical values across segments.

Cross-validation: Calculate the same metric two ways. Spot-check individual records. Reverse-engineer totals (per-user \* users ~= total).

Gotchas

• LEFT JOIN hides missing data — A LEFT JOIN silently fills unmatched rows with NULLs. If you then filter on a right-table column in WHERE (not ON), it becomes an INNER JOIN. Put right-table filters in the ON clause.
• Rounding before aggregation compounds error — Round display values at the end, not intermediate calculations. Rounding percentages per-row then summing can produce totals that don't add to 100%.
• "No rows returned" is not "zero" — A query that returns no rows for a segment is different from a segment with zero activity. Missing rows won't show up in aggregations; use COALESCE or a complete date/segment spine.
• Dashboard numbers include filters you forgot — When cross-referencing against a dashboard, check its hidden filters (date range, excluded segments, bot filtering). Mismatch is usually a filter difference, not a bug.