The SQL Checks I Run Before I Trust a Dataset
A dashboard can look polished and still be wrong. One duplicated join key can inflate a revenue total. A handful of missing customer IDs can disappear from a segment analysis. A new status value can quietly fall outside
A dashboard can look polished and still be wrong.
One duplicated join key can inflate a revenue total. A handful of missing customer IDs can disappear from a segment analysis. A new status value can quietly fall outside an existing report filter.
These problems are easy to miss because the SQL may run successfully. Before I use a dataset for analysis, I check its shape, its key assumptions and the joins that could change the results.
The examples below use an orders table with order_id, customer_id, order_date, order_amount and order_status columns. Theyβre illustrative; use the names and rules that match your actual schema.
Start by checking the tableβs grain
βGrainβ means what one row represents. In an orders table, a row might represent one complete order, or one product line within an order. That distinction affects every count and sum.
If the intended grain is one row per order, order_id should be unique. I check that before calculating totals:
SELECT
order_id,
COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
If this returns records, I donβt immediately delete them. First, I find out whether theyβre accidental duplicates or whether the table contains multiple rows per order by design. A line-item table, for example, may correctly repeat the same order_id.
Understanding the grain prevents a common mistake: applying a fix before understanding what the data represents.
Check completeness and valid values
Next, I look at fields the analysis depends on. If Iβm reporting sales by date, missing dates matter. If Iβm analysing customers, missing customer IDs matter.
SELECT
COUNT(*) AS total_rows,
SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS missing_order_ids,
SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS missing_customer_ids,
SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS missing_order_dates,
SUM(CASE WHEN order_amount IS NULL THEN 1 ELSE 0 END) AS missing_amounts
FROM orders;
A missing value isnβt automatically an error. It may be allowed by the source process, or it may mean something specific. But it should not be silently treated as zero or excluded without understanding the consequence.
I also inspect the values in categorical fields. This can reveal new codes or inconsistent labels before they create unexpected report categories:
SELECT
order_status,
COUNT(*) AS rows
FROM orders
GROUP BY order_status
ORDER BY rows DESC;
If the approved statuses are pending, completed and cancelled, an unexpected value such as complete deserves investigation. It may be a source-system change, a spelling inconsistency or a legitimate new status.
Test joins before trusting the totals
A join can change the number of rows. If the table on the right has multiple records for a key, one order can appear several times in the result. Summing order_amount after that join would overstate sales.
I compare the original row count with the joined row count:
SELECT COUNT(*) AS order_rows
FROM orders;
SELECT COUNT(*) AS rows_after_join
FROM orders AS o
LEFT JOIN customers AS c
ON o.customer_id = c.customer_id;
If the joined count is higher, I check whether customers contains more than one row per customer_id. If itβs lower, I check whether the query uses an inner join or whether some records lack a match.
The counts donβt explain the issue by themselves, but they tell me whether the join has changed the dataset in a way I need to understand.
Turn assumptions into repeatable checks
A few validation queries are useful during exploration. For recurring analysis, I write down the assumptions that matter: what one row represents, which fields are required, which values are allowed and how unmatched records should be handled.
Then I rerun the checks whenever the source or transformation changes. That way, a sudden shift in row counts or categories is visible before it becomes a misleading chart or business decision.
These checks wonβt prove that every value is correct. They do something more practical: they expose where the data needs explanation. Once I know what each row represents and how the key relationships behave, I can decide what the dataset is reliable enough to answer.
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes β full credit and traffic to the original publisher.