Most SQL written for analysis is treated as disposable: run it, get the number, paste it into the slide. The query lives in a Slack message or a browser tab history, and the next time someone needs the same number they write a new query from scratch, possibly differently. When the two numbers disagree in a meeting, nobody can explain why without reconstructing the logic from memory.
SQL that drives decisions is not disposable. It is reasoning expressed in a formal language, and like any reasoning it can be wrong in ways that are not immediately visible: a join that silently multiplies rows, a filter applied to the wrong scope, a date truncation that shifts one group by a day relative to another. The only reliable protection against those errors is the same protection applied to production code: version control, formatting standards, sanity tests, and a second reader.
Formatting as a readability contract
Unformatted SQL is not just aesthetically unpleasant: it is harder to review, harder to debug, and harder to hand off. The formatting conventions I follow are not personal preference. They are the minimum required for a second person to read the query without reconstructing its intent from scratch.
-- Formatted: intent is visible at a glance
SELECT
o.customer_id,
DATE_TRUNC('month', o.created_at) AS order_month,
COUNT(*) AS order_count,
SUM(o.amount_usd) AS revenue_usd,
AVG(o.amount_usd) AS avg_order_value_usd
FROM orders o
WHERE o.status = 'completed'
AND o.created_at >= '2024-01-01'
AND o.created_at < '2025-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;The rules behind this formatting: keywords in uppercase, one selected expression per line, aliases aligned, WHERE conditions indented under the clause with boolean operators at the start of each line rather than the end. The alias alignment is not cosmetic: it lets a reviewer scan the SELECT list vertically and spot a missing alias or a miscalculated column without reading each line fully.
CTEs instead of subqueries, always. A subquery buried inside a FROM clause requires the reader to mentally evaluate it before understanding the outer query. A CTE names the intermediate step, explains its purpose in the name, and can be inspected independently.
WITH completed_orders AS (
SELECT *
FROM orders
WHERE status = 'completed'
AND created_at >= '2024-01-01'
),
monthly_revenue AS (
SELECT
customer_id,
DATE_TRUNC('month', created_at) AS order_month,
SUM(amount_usd) AS revenue_usd
FROM completed_orders
GROUP BY 1, 2
)
SELECT *
FROM monthly_revenue
ORDER BY customer_id, order_month;Each CTE is a named, testable unit. A reviewer can read completed_orders and verify it does what the name says before reading how it is used.
The four sanity tests I run on every analytical query
Sanity tests are not unit tests in the engineering sense: they are manual checks that catch the most common silent failures before a number enters a report or a slide.
Row count before and after joins. The most expensive silent failure in analytical SQL is a join that fans out: a many-to-many relationship where both sides have multiple matching rows, producing a Cartesian product that multiplies every aggregate. The check is simple: count the rows in each table before the join, count the rows after, and verify the result is consistent with the expected cardinality.
-- Before: how many orders?
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- 48,302
-- After joining to customers: still 48,302?
SELECT COUNT(*)
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';
-- 48,302 ✓ (if 52,100: the join fanned out, investigate)NULL propagation check. A SUM over a column with NULLs silently ignores them. An AVG divides by the count of non-NULL values, not the total row count. Whether that is correct depends on what the NULL means in that column. Check null rates on every column used in an aggregate before relying on the result.
Boundary condition verification. Date filters are the most common source of off-by-one errors in SQL. BETWEEN '2024-01-01' AND '2024-12-31' behaves differently depending on the column type: for a DATE column it is inclusive on both ends and correct; for a TIMESTAMP column it excludes December 31 after midnight and misses a full day. I always use half-open intervals (>= start AND < end) and verify the boundary by running SELECT MIN(), MAX() on the date column in the filtered result.
Reconciliation against a known total. Before using a query result, I check its total against an independently known figure: a financial report, a system dashboard, a previously validated dataset. A query that produces a revenue total 3% lower than the finance report is wrong until explained. The explanation may be legitimate (different date logic, different currency handling), but it must be explicit, not assumed.
Peer review for analytical SQL
The errors that sanity tests catch are structural. The errors that peer review catches are semantic: the query is correctly written but answers the wrong question. A second reader who was not inside the analyst's head when the query was written will ask the questions the analyst stopped asking after the first hour: why this filter, why this grain, what does this join assume about the relationship between these tables?
Effective SQL review requires the reviewer to understand the business question the query is meant to answer. A reviewer who only checks SQL syntax is not doing peer review: they are doing formatting inspection. The review that catches errors is the one where the reviewer reads the business question, reads the query, and asks: does this query answer that question?
In practice this means: keep analytical SQL in version control (a /queries folder in the project repository, not a browser history), write a comment at the top of each file stating the business question it answers and the expected output, and request review before the result goes into any stakeholder-facing artifact. The overhead is one pull request and fifteen minutes of a colleague's time. The alternative is a wrong number in a board deck.
Patterns that indicate a query needs review
Some SQL patterns are not wrong by themselves but reliably indicate a place where something can go wrong silently and should be checked explicitly.
DISTINCT in a SELECT. DISTINCT is often added to fix a row duplication that the join created. It fixes the symptom and hides the cause. When I see DISTINCT, I ask why the join produced duplicates and fix the join logic rather than suppressing the duplicates.
Aggregating before joining. Pre-aggregating one side of a join to force one-to-one cardinality is sometimes correct. It is also sometimes evidence that the grain was not clearly defined before the query was written. Write down the grain of the expected result first, then write the query that produces it.
Hardcoded date offsets. WHERE created_at >= CURRENT_DATE - 90 produces different results depending on when the query runs, which makes the result non-reproducible. If the analysis is meant to cover a specific period, use explicit dates. If it is meant to be a rolling window, document that intent explicitly and test what happens at period boundaries.
SQL treated as disposable produces disposable analysis. SQL treated as code produces analysis that can be reproduced, reviewed, updated, and handed to the next person with a confidence that goes beyond "trust me, I ran the query last Tuesday."