2
Accepted answer
Prefer a staging table plus validation gate before promoting to prod tables.
A report filtering customers with total revenue over 10000 used WHERE instead of HAVING and just silently returned wrong numbers instead of erroring.
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE SUM(amount) > 10000
GROUP BY customer_id;That actually failed to parse in our case, so the real bug was different. We'd filtered on WHERE order_date >= without realizing it excluded rows the sum needed before grouping. How do people avoid this class of mistake?
Accepted answer
Prefer a staging table plus validation gate before promoting to prod tables.
Treat WHERE as row-level and HAVING as group-level, always. If the filter depends on an aggregate, it belongs in HAVING, full stop.
We saw the same issue, fixing the partition filter dropped runtime 60%.
We saw the same issue, fixing the partition filter dropped runtime 60%.
Quick plain-English version: the database is doing more work than it needs to because it can't tell in advance which rows actually match. An index is basically a shortcut list so it doesn't have to check every single row.
For date-range bugs like yours, write the filter as a CTE with a comment stating the grain, so the next reader can tell whether it's pre- or post-aggregation.
Start with the execution plan, numbers beat guesses.
Add a dbt test that recomputes the aggregate two ways, with and without the filter, and asserts the difference is expected. That catches silent grain bugs in CI.
Note that merge on Delta still needs unique keys defined correctly.
Another path: push the compute to the warehouse if the data's already there.
Sign in to reply.
© 2026 Lakebench, operated by Hunnurji Rao. Bengaluru, Karnataka, India.
No cluster. No install. Just the tab.