If you’ve been following along with our Newsletters, you will know that we’re often providing thoughts on how best to interact with the ever changing landscape of AI. The truth is that while these tools are very useful, they’re still far from perfect. We spend a lot of time pairing AI with analytics, and the pattern is the same across clients and our own work: AI is fantastic at getting you to a plausible first draft, but it is just as happy to give you something that reads cleanly while hiding a quiet mistake. The good news is that most of the bad outcomes collect in a handful of places. Build a short ritual around these checks and you will catch the vast majority of errors before they ship!
1) Row count and key count after joins
What it catches: accidental inner-izing of outer joins, silent duplication from many-side tables, missing rows from null join keys.
60-second check: compare the base table’s row count and distinct primary key count to the post-join result. If you expected to preserve the base grain, both numbers should match.
How to do it:
-- Base
SELECT
COUNT(*) as base_rows,
COUNT(DISTINCT o.order_id) as base_keys
FROM core.orders o;
-- After join
SELECT
COUNT(*) as joined_rows,
COUNT(DISTINCT o.order_id) as joined_keys
FROM core.orders o
LEFT JOIN core.order_items oi ON oi.order_id = o.order_id;
If joined_keys drops, you turned your left join into an inner join somewhere. If joined_rows jumps far above base_rows, it’s likely some duplication slipped in.
2) Total reconciliation against an independent view
What it catches: math that “works” but answers the wrong question, missed filters, sign mistakes, or duplicate multiplication that survives DISTINCT in the wrong place.
60-second check: pick a total you can sanity-check without fancy SQL. If you are summing order revenue from order_items, reconcile it to a trusted orders ledger or to a back-of-the-envelope daily total. The totals do not need to match to the penny, but they should rhyme.
How to do it (this will depend on your data, but here are some examples):
- SUM(o.order_total) from orders vs SUM(oi.extended_price) from items grouped to orders
- Yesterday’s revenue vs the figure in your finance export
- Active users in product vs active users in your analytics tool for the same window
3) Checking for filters for optional matches in the JOIN, not the WHERE
What it catches: the classic bug where a left join is quietly converted to an inner join because the filter lands in WHERE. This is one we correct in our chats all the time.
60-second check: read the intent. If you need to keep base rows with no match, move predicates on the right table into the ON clause.
Before (drops customers with zero YTD orders):
Before (drops customers with zero YTD orders):
SELECT
c.customer_id,
COUNT(o.order_id) AS orders_ytd
FROM core.customers c
LEFT JOIN core.orders o
ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY 1;
After (preserves them):
SELECT
c.customer_id,
COUNT(o.order_id) AS orders_ytd
FROM core.customers c
LEFT JOIN core.orders o
ON o.customer_id = c.customer_id
AND o.order_date >= '2025-01-01'
GROUP BY 1;
If you intended to exclude customers with zero YTD orders, the original is fine; the check is about making sure the filter placement matches the goal.
4) Guard the grain on many-to-one joins
What it catches: duplicated dollars and inflated counts when a filter requires the “many” side but the metric lives on the “one” side. This is the exact orders ↔ order_items trap we’ve called out before.
Scenario: “Sum revenue for customers on orders that contain item category = 'Widget'.” Naïve code multiplies revenue when an order has multiple Widget lines.
Naïve (dupes revenue):
SELECT
o.customer_id,
SUM(o.order_total) AS revenue
FROM core.orders o
LEFT JOIN core.order_items oi
ON oi.order_id = o.order_id
WHERE oi.item_category = 'Widget'
GROUP BY 1;
Fix 1 (semi-join via DISTINCT):
WITH widget_orders AS (
SELECT DISTINCT
order_id
FROM core.order_items
WHERE item_category = 'Widget'
)
SELECT
o.customer_id,
SUM(o.order_total) AS revenue
FROM core.orders o
JOIN widget_orders w
ON w.order_id = o.order_id
GROUP BY 1;
Fix 2 (EXISTS, no CTE needed):
SELECT
o.customer_id,
SUM(o.order_total) AS revenue
FROM core.orders o
WHERE EXISTS (
SELECT 1
FROM core.order_items oi
WHERE oi.order_id = o.order_id
AND oi.item_category = 'Widget'
)
GROUP BY 1;
The validation is simple: if the metric comes from the “one” table, make the “many” table a filter, not a multiplier.
5) Date boundaries and time zones
What it catches: off-by-one windows, partial days, and quiet shifts from UTC to local that move records across the boundary. This is one of the most common outside corrections we see generally.
60-second check: verify inclusivity and the clock. If your source is UTC and your reporting is local, confirm whether the filter occurs before or after conversion, and prefer half-open ranges.
How to do it:
-- Safer half-open window in UTC
WHERE created_at_utc >= '2025-01-01'::timestamp
AND created_at_utc < '2025-02-01'::timestamp;
-- Or convert once, then filter on the converted value
WHERE DATE_TRUNC('day', CONVERT_TIMEZONE('UTC','America/New_York', created_at_utc))
BETWEEN '2025-01-01' AND '2025-01-31';
As a final sweep, scan the first and last day for suspicious spikes or dips. If yesterday is half a day smaller than the prior seven, your boundary is off.
The 60-second checklist card
- Row and key counts match expectation after joins: If the base grain should be preserved, both should hold steady.
- Totals reconcile to an independent view: Pick one number and make sure it rhymes.
- Optional-match filters live in the JOIN: Keep the outer join outer unless you intend otherwise.
- Many-to-one joins protect the grain: Use CTEs or EXISTS to pre-aggregate the many side.
- Windows are half-open and clocks are explicit: No mystery midnights.
Thanks for reading! Want more SQL practice? Be sure to check out our free Daily SQL Challenge tool - Inner Join. For a limited time we’re offering a free lifetime subscription if you sign-up, so don’t miss out!