A dashboard shows yesterday's orders down 90%. Find and fix the query bug behind the fake drop.
A daily-orders tile shows yesterday 90% below normal. Constraints: orders is loaded by a batch ETL whose partition for a given day backfills around noon, so yesterday was only partly loaded when the tile ran. A _load_status(load_date, is_complete) table records when each day's partition is fully landed.
-- Daily orders tile — yesterday reads 90% below normal
SELECT order_date, COUNT(*) AS orders
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '14 days'
GROUP BY order_date
ORDER BY order_date;Explain why yesterday looks empty, and add a guard that excludes incomplete partitions.
The drop is a data-completeness artifact — yesterday's partition is still loading, so COUNT sees only the rows landed so far. Confirm it in seconds via load status. Fix the tile to drop any date not marked complete: join _load_status and filter on is_complete.
- ✗Treating an incomplete partition as a real collapse
- ✗Widening the window instead of guarding completeness
- ✗Blaming dedup rather than data freshness
- →What one check confirms partition completeness in under a minute?
- →How would you stop this from paging the team next time?
Yesterday's number is low not because orders were missing, but because the partition is still backfilling (around noon): COUNT(*) sees only the rows that have landed so far. The seconds-fast check is to consult _load_status for whether the partition is complete. The fix is an inner join to _load_status filtered on is_complete, so incomplete days never reach the tile:
-- Guard: only count days whose partition is fully loaded
SELECT o.order_date, COUNT(*) AS orders
FROM orders o
JOIN _load_status s
ON s.load_date = o.order_date AND s.is_complete
WHERE o.order_date >= CURRENT_DATE - INTERVAL '14 days'
GROUP BY o.order_date
ORDER BY o.order_date;The INNER JOIN on is_complete drops yesterday until it is marked complete — the tile renders only whole days, and a partial partition never shows as a collapse.
