I once inherited a revenue report that had been green for fourteen months. Every morning it ran, populated a dashboard, and nobody questioned it. Then a new CFO asked why the data warehouse said Q2 regional revenue was $4.1M while the finance system said $1.3M. Same source data. The query had no error, no failed test, no warning. It had one join. Somewhere upstream, an orders table was joined to order_items, and a downstream SUM was quietly counting each order’s total once for every line item on it. A three-line-item order got counted three times. For fourteen months.
The engineer who wrote it was competent. The SQL was readable. It followed every rule in the “write clean SQL” playbook — named columns, tidy formatting, sensible aliases. And it was still catastrophically wrong, because clean and correct are different things. That gap is what actually separates senior SQL from junior SQL, and it’s a deeper gap than the usual advice about CTEs and naming suggests. The readability stuff is real and I’ll get to it, but if that’s all you fix, you’ll write beautifully formatted queries that overstate revenue by 3x. Senior SQL is different along three axes at once — correctness, cost, and change-safety — and only the third is about how the code looks. This isn’t abstract, by the way: I’ve argued that SQL is the highest-leverage skill on a data team right now, and this is exactly why — the language is easy, the judgment is not.
TL;DR
- → A query that runs clean can still be wrong; the most expensive SQL bugs produce plausible numbers, not errors.
- → Track the grain of every CTE — joining before you aggregate is the single most common cause of silently inflated totals.
- →
NOT IN (subquery)returns zero rows if the subquery contains a single NULL; seniors useNOT EXISTSfor anti-joins. - → Deduping with
QUALIFY ROW_NUMBER()and a tie-brokenORDER BYis reproducible;SELECT DISTINCTand untiedROW_NUMBERare not. - → Wrapping a filter column in a function (
DATE(ts) = ...) and usingSELECT *both defeat the engine’s ability to skip data, and that shows up on the bill. - → CTEs aren’t just prettier — each one is a debugging checkpoint you can query in isolation when a number looks wrong.
- → Junior engineers optimize to make the query run; senior engineers optimize so the next change, and the next reader, don’t create a new bug.
Senior SQL isn’t clever SQL
There’s a myth that senior engineers write dense, impressive queries full of tricks. The opposite is true. The most senior SQL I’ve reviewed is almost boring to read — because the author spent their cleverness on being right and cheap, not on being concise. The comparison above captures the pattern: at every decision point there’s a lazy default that works on your laptop against 100 rows, and a deliberate choice that survives contact with production data. The rest of this article is the “why” behind the right-hand column, with real SQL for each.
Correctness #1: the grain trap that triples your revenue
This is the bug from the intro, and it’s worth seeing in code because it looks completely innocent. The business question: total spend per customer. There’s an orders table and an order_items table. Here’s the query that ran for fourteen months:
-- WRONG: order_total is counted once per line item
SELECT
o.customer_id,
SUM(o.order_total) AS total_spend
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'refunded'
GROUP BY o.customer_id;The join changes the grain. Before the join, orders has one row per order. After joining order_items, an order with three line items becomes three rows — each carrying the full order_total. The SUM then adds that total three times. Nothing errors; the number is just inflated by however many items the average order has.
The join multiplied the rows; the SUM multiplied the money. The fix is to aggregate at the right grain before joining.
The senior version aggregates order_items down to one row per order first, so every later step operates on a known grain:
WITH order_revenue AS ( -- grain: one row per order
SELECT
oi.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM order_items oi
GROUP BY oi.order_id
)
SELECT
o.customer_id, -- grain: one row per customer
SUM(orv.order_total) AS total_spend
FROM orders o
JOIN order_revenue orv ON orv.order_id = o.order_id
WHERE o.status <> 'refunded'
GROUP BY o.customer_id;The habit that prevents this bug isn’t a syntax rule — it’s asking “what is the grain of this result?” after every join and every CTE. Seniors annotate it, as in the comments above. This is the same class of silent failure I wrote about in why passing dbt tests still ship bad data: uniqueness and not-null tests would both pass on the wrong query above, because the rows really are unique and non-null. They’re just counted too many times.
Correctness #2: NOT IN will betray you
You need a win-back list: customers with no orders in the last 90 days. The obvious query:
-- WRONG if orders.customer_id contains any NULL
SELECT customer_id, email
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - 90
);If a single row in that subquery has a NULL customer_id — a guest checkout, a bad load, a soft-deleted account — this query returns zero rows. Not an error. An empty result that looks like “nobody qualifies.” The reason is SQL’s three-valued logic: NOT IN is defined as “not equal to any,” and comparing anything to NULL yields UNKNOWN, which poisons the whole condition so no row is ever selected. This is spec-mandated behavior, documented in PostgreSQL’s logical operators reference, and it behaves the same across every compliant engine.
The senior default is an anti-join with NOT EXISTS, which is immune to the NULL problem because it tests row existence, not value equality:
SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - 90
);Rule of thumb worth internalizing: reach for NOT EXISTS over NOT IN every time a subquery is involved, unless you have proven the column is non-nullable. It’s not a style preference; it’s a correctness guarantee.
Correctness #3: dedup that returns the same rows twice
Deduplication is where juniors reach for SELECT DISTINCT and seniors reach for a window function — and the difference is about determinism, not taste. Say raw_orders has duplicate order_ids from an at-least-once ingestion, and you want the latest version of each. DISTINCT can’t express “latest”; it just collapses fully identical rows. And ROW_NUMBER without a tie-break silently picks an arbitrary row when the sort column ties — so the same pipeline can emit different rows on different runs, which is a nightmare to debug.
The senior pattern uses QUALIFY to filter on a window function inline, with a deterministic ordering:
SELECT order_id, customer_id, status, order_total, updated_at
FROM raw_orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingested_at DESC -- tie-break = determinism
) = 1;
+----------+-------------+----------+-------------+---------------------+
| order_id | customer_id | status | order_total | updated_at |
+----------+-------------+----------+-------------+---------------------+
| 101 | 4471 | shipped | 150.00 | 2026-07-20 14:02:11 |
| 102 | 2210 | placed | 200.00 | 2026-07-21 09:15:40 |
| 103 | 8890 | returned | 250.00 | 2026-07-21 18:44:02 |
+----------+-------------+----------+-------------+---------------------+The second column in the ORDER BY is the tell. A junior stops at updated_at DESC; a senior adds a unique tie-breaker so that when two rows share an updated_at, the query still picks the same one every single run. Reproducibility is a correctness property, and non-deterministic SQL is a close cousin of the “same input, different output” problem — except here you can actually eliminate it.
Cost: write SQL the engine is allowed to skip
Correctness keeps you employed; cost keeps you promoted. On columnar cloud warehouses, two junior habits quietly multiply what a query scans. Consider:
-- Scans every column, every micro-partition
SELECT *
FROM events
WHERE DATE(event_ts) = '2026-07-01';Two problems. SELECT * forces the engine to read every column, including fat JSON and audit fields nobody asked for. And wrapping event_ts in DATE() means the optimizer can’t use the raw timestamp to prune partitions — it has to compute a function over every row first. The senior rewrite selects only what it needs and expresses the filter as a half-open range on the bare column, which lets the engine skip whole partitions it knows can’t match:
SELECT event_id, user_id, event_type
FROM events
WHERE event_ts >= '2026-07-01'
AND event_ts < '2026-07-02';If you want the mechanics of why this works — how pruning and partition elimination actually happen — I walked through it in what really happens when you run a query in Snowflake, and why re-running the same query can be nearly free in how the warehouse cache actually works.
The cost math
Put rough numbers on it. Say events is 2 TB across 40 columns, roughly evenly sized, holding one year of data. The junior query scans close to the full 2 TB. Selecting only the three columns you need cuts that to about 150 GB (three of forty columns). Adding the prunable date range drops it to one day out of ~365 — on the order of 400 MB actually read. That’s a ~5,000x reduction in bytes scanned, on a warehouse you’re billed for by the second, from two changes that took ten seconds to make. This is the same discipline behind not rebuilding models that didn’t change, which is the whole premise of dbt state-based selection — and it’s why teams serious about spend even push analytics to cheaper engines like DuckDB for dev workloads.
Change-safety: CTEs as checkpoints, not decoration
This is the axis the “clean SQL” advice usually covers, and it’s genuinely important — just not sufficient on its own. Structuring a query as a sequence of named CTEs (recent_orders → order_revenue → customer_summary) does more than read nicely. Each CTE is a checkpoint you can inspect in isolation: when a customer is missing from the output, you don’t mentally execute a nested monster, you run SELECT * FROM order_revenue WHERE order_id = 8821 and walk the stages until the row disappears. That’s the difference between a five-minute debug and a fifty-minute one.
The other half of change-safety is treating your SELECT list as a contract. SELECT * in a model means that the day someone adds a gdpr_deletion_requested column upstream, it silently flows into every downstream dashboard — the exact class of break I covered in how one renamed column kills a pipeline. An explicit column list is a promise about what this query returns, and promises are what let the next engineer change things without fear. If you want this enforced at the project level rather than per query, the layering conventions in structuring dbt projects in Snowflake are the natural home for it.
The gotchas nobody warns you about
DISTINCT is often a fan-out cover-up. If you added SELECT DISTINCT to “fix” duplicate rows, you probably have a grain bug upstream and are papering over it. DISTINCT hides the symptom and keeps the doubled aggregates.
BETWEEN on timestamps is a silent off-by-one. event_ts BETWEEN '2026-07-01' AND '2026-07-02' includes midnight of the second day, double-counting boundary rows. Half-open ranges (>= and <) are the senior default for exactly this reason.
COUNT(column) and COUNT(*) are not the same. COUNT(col) skips NULLs; COUNT(*) counts rows. Using the wrong one turns a data-quality problem into a wrong metric that looks fine.
RANK and ROW_NUMBER dedup differently. RANK() assigns ties the same number, so QUALIFY RANK() = 1 can keep multiple rows per key. For “exactly one row per key,” it must be ROW_NUMBER().
ORDER BY inside a CTE or view is not guaranteed to hold. The engine is free to reorder unless the outermost query sorts. If order matters downstream, sort where the data is consumed, not where it’s defined.
The one principle
Junior engineers write SQL that returns the right answer today; senior engineers write SQL that can’t quietly start returning the wrong one. The clean formatting, the CTEs, the explicit columns — those are the visible surface. Underneath is a habit of assuming your data is messier than the demo, your query will be re-run on data you haven’t seen, and the next person to touch it won’t know what you knew. Write for that world, and “senior SQL” stops being a style and becomes a form of insurance.
Related reading: Why passing dbt tests still ship bad data · What really happens when you run a query · How one renamed column kills a pipeline · Why SQL is the most valuable skill in AI · Snowflake QUALIFY reference · PostgreSQL three-valued logic
