sql-business-logic-review

Purpose

Review SQL as business logic, not just syntax. Detect silent semantic changes and flag places where technically valid SQL can still produce wrong business results.

When to Use

• Reviewing SQL changes that feed reports, metrics, or financial/product logic
• A query was modified and downstream numbers shifted, or might have
• Output looks plausible but the definition of what is being counted may have changed

Inputs

• The SQL under review (diff preferred, full query otherwise)
• The intended grain and business definition, before and after the change

Workflow

1. Establish the intended grain before and after the query or change.

2. Scan for the standard risk patterns:

   • grain mismatches
   • duplicate rows introduced by joins
   • incorrect join keys
   • left vs inner join behavior changes
   • filters that alter population definitions
   • null handling that changes meaning
   • default values that hide data quality issues
   • aggregation mistakes
   • window functions with unsafe partitions or ordering
   • date logic and timezone assumptions
   • incremental logic that can double count, miss rows, or drift
   • metric definitions that no longer match prior intent

3. Ask the review questions:

   1. What is the intended grain before and after this query?
   2. Could this query duplicate or drop rows?
   3. Has the business definition changed even if the SQL still runs?
   4. Are there edge cases around nulls, late-arriving data, or time windows?
   5. What result could look plausible while still being wrong?

4. Propose concrete validation checks, for example:

   -- grain check: the expected key must be unique
   SELECT <key_columns>, COUNT(*)
   FROM (<query under review>)
   GROUP BY <key_columns>
   HAVING COUNT(*) > 1;
   
   -- before/after comparison on a stable slice
   SELECT COUNT(*), SUM(<metric_column>) FROM (<old query>);
   SELECT COUNT(*), SUM(<metric_column>) FROM (<new query>);
   

Output