A dashboard can be visually clean and still be wrong. Before I trust a reporting dataset, I like to validate the assumptions underneath it—not just whether the query runs.
Here’s a practical checklist:
1. State the grain
Write down what one row represents (for example, one order line, one ticket event, or one daily account snapshot). If two tables have different grains, joining them without accounting for that difference can inflate counts and sums.
2. Test key uniqueness before joining
For every expected one-to-one or many-to-one join, check whether the lookup key is actually unique:
sql
SELECT customer_id, COUNT(*) AS row_count
FROM dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;
Then compare row counts and distinct business keys before and after the join. A query returning successfully doesn’t mean the relationship is correct.
3. Reconcile totals to a trusted source
Compare record counts and important measures by a stable slice such as date, status, or region. When totals differ, quantify the gap and trace it to exclusions, late-arriving data, duplicates, or definition changes.
4. Check freshness and completeness
Confirm the latest expected date arrived, required fields are populated, and categories haven’t unexpectedly disappeared. These checks catch silent partial loads that can make a dashboard look plausible.
5. Validate business rules explicitly
Document definitions for metrics such as “active,” “resolved,” or “on time.” Encode the rules in tests or assertions, and keep exceptions visible instead of burying them in a chart filter.
6. Make the checks part of the pipeline
Automated tests, run logs, and failure alerts turn one-time detective work into a repeatable control. The goal is to catch a bad refresh before stakeholders make decisions from it.
My rule of thumb: validate the grain, joins, totals, and freshness before debating the chart. What’s the one check you consider non-negotiable in your analytics workflow?
