SQL Anti-Patterns That Quietly Multiply Your Warehouse Bill

Most warehouse bills do not blow up because of one bad query. They blow up because five mediocre habits get copy-pasted into forty models, and each one scans two to ten times more data than the result needs. Nobody notices, because every individual query still finishes.

I have cleaned up enough of these to have a short list. These five show up in almost every codebase I audit, they are cheap to fix, and they compound: a SELECT * feeding a fan-out join feeding a DISTINCT is three of them stacked in one model.

None of this is exotic. That is the point. The expensive SQL in production is rarely clever. It is ordinary SQL written without thinking about what the engine has to touch.

SELECT * is a contract you did not mean to sign

Columnar warehouses — Snowflake, BigQuery, Redshift, DuckDB — store each column separately. A query that reads three columns from a 200-column event table scans a sliver of the bytes. SELECT * scans all of it.

The damage is worst in intermediate models. A staging model that does SELECT * from a wide source forces every downstream model to carry the width, and on BigQuery you are billed on bytes scanned, so the invoice tracks the widest lazy model in the chain, not the final output. The partitioning and clustering guide covers the pruning side; this is the column side of the same bill.

There is a second cost. SELECT * binds you to the source schema. When someone adds a 4 KB JSON blob column upstream, every model in your chain silently starts scanning it. A data contract with an explicit column list is also a performance tool.

-- Anti-pattern: staging model that carries all 212 columns
create or replace table staging.stg_events as
select * from raw.app_events;

-- Fix: name the columns you actually ship downstream
create or replace table staging.stg_events as
select
  event_id,
  user_id,
  event_type,
  event_ts,
  session_id,
  properties:plan::string as plan
from raw.app_events;

The rewrite is boring. It also cut a client’s daily staging scan from 1.8 TB to 140 GB, because the raw table carried request and response payloads nobody downstream had ever queried.

Functions on filter columns kill pruning

Warehouses prune with metadata: partition boundaries in BigQuery, micro-partition min/max in Snowflake, zone maps elsewhere. Pruning works by comparing stored column statistics against your predicate. Wrap the column in a function and the stored metadata no longer applies, so the engine scans everything and evaluates the function per row.

flowchart LR
  q["WHERE date(event_ts) = today"] --> fn[Function wraps the column]
  fn --> noprune[Metadata cannot be compared]
  noprune --> full[Full scan]
  q2["WHERE event_ts >= today"] --> raw[Raw column in predicate]
  raw --> prune["Pruned by min and max stats"]
  prune --> small[Small scan]

Keep the column naked on one side of the comparison. Move the math to the other side.

The classic offenders:

-- Anti-pattern: every one of these scans the full table
where date(event_ts) = current_date
where cast(order_date as varchar) like '2026-04%'
where upper(country_code) = 'DE'
where event_ts + interval '5 hour' >= '2026-04-01'

-- Fix: sargable predicates, same logic
where event_ts >= current_date
  and event_ts < current_date + interval '1 day'

where order_date >= '2026-04-01'
  and order_date < '2026-05-01'

where country_code in ('DE', 'de')  -- or fix casing at ingest

where event_ts >= timestamp '2026-04-01' - interval '5 hour'

The rule I give reviewers: the column stands alone on one side of the operator, and all arithmetic moves to the literal side. The upper() case is really a data quality problem — normalize casing at ingest with a quality check, not in every downstream predicate.

Time zone conversions in predicates are the sneakiest version. where convert_timezone('UTC', 'America/New_York', event_ts) >= ... scans everything. Convert the boundary instead, and read the date and time gotchas post before you trust the boundary you converted.

DISTINCT as a bug fix

DISTINCT has a legitimate job: enumerating unique values of a small projection. The anti-pattern is DISTINCT added because a join doubled the row count and the numbers looked wrong.

That DISTINCT does not fix the bug. It hides it, expensively. The engine still produces the fanned-out intermediate result, then sorts or hashes the whole thing to collapse duplicates. You pay for the fan-out and for the cleanup, and the model breaks again the moment a duplicate row has a differing column.

-- Anti-pattern: orders doubled by a dirty dimension, patched over
select distinct
  o.order_id,
  o.amount,
  c.segment
from fct_orders o
join dim_customers c on c.customer_id = o.customer_id;

-- Fix: make the dimension unique at its declared grain
with customers_dedup as (
  select *
  from dim_customers
  qualify row_number() over (
    partition by customer_id
    order by updated_at desc
  ) = 1
)
select
  o.order_id,
  o.amount,
  c.segment
from fct_orders o
join customers_dedup c on c.customer_id = o.customer_id;

QUALIFY with ROW_NUMBER states the grain and the tiebreaker in one place — the pattern is in the window functions post. The deeper fix is upstream: a uniqueness test on dim_customers that fails the build. I wrote up the whole failure class in the fan-out postmortem, because the DISTINCT patch is almost always covering that exact bug.

Grep your repo for select distinct on more than three columns. Each hit is either a grain bug or a model that cannot state its grain. Both are worth a ticket.

OR across join conditions

An OR inside a join predicate looks harmless and is one of the most expensive shapes you can hand an optimizer:

-- Anti-pattern: matches on either key
select p.payment_id, o.order_id
from payments p
join orders o
  on p.order_id = o.order_id
  or p.legacy_ref = o.legacy_ref;

Hash joins need a single equality key. With an OR, the engine cannot build one hash table that serves both conditions, so most planners fall back to a nested-loop or a broadcast comparison — effectively a cross join with a filter. On two 100M-row tables that is not a slow query, it is a dead one.

Split it into two clean equi-joins and union, deduplicating on the key you actually care about:

with by_order as (
  select p.payment_id, o.order_id
  from payments p
  join orders o on p.order_id = o.order_id
),
by_legacy as (
  select p.payment_id, o.order_id
  from payments p
  join orders o on p.legacy_ref = o.legacy_ref
  where p.order_id is null   -- only rows the first join missed
)
select * from by_order
union all
select * from by_legacy;

Each branch gets a hash join and normal pruning. The WHERE in the second branch keeps the union from double-matching. Same result, two orders of magnitude less work on every engine I have run it on. You can reproduce the plan difference on a few million rows in the /playground/ — DuckDB shows the nested-loop fallback clearly in EXPLAIN.

Row-by-row UDF calls

SQL engines are fast because they operate on sets. A UDF called once per row — especially an external or Python UDF — turns a vectorized scan into a loop with per-call overhead. If the UDF does network I/O, you have built a distributed retry storm.

flowchart TD
  rows[100M rows] --> udf{UDF per row?}
  udf -->|yes| loop[100M invocations plus overhead]
  loop --> slow[Hours and a fat bill]
  udf -->|no| set["Set-based SQL or batched UDF"]
  set --> vec[Vectorized execution]
  vec --> fast[Minutes]

The engine can only vectorize what you express as a set operation.

Most UDFs I find in the wild are reimplementing something SQL already does: date math, string parsing, tier bucketing. Those should be CASE expressions or built-ins. For the rest, use the engine’s batched form — Snowflake vectorized Python UDFs receive whole Arrow batches, BigQuery remote functions accept row batches — or take the work out of the warehouse entirely and run it in a DuckDB or Python step where a loop is at least honest about being a loop.

The test I apply in review: if the UDF body has no state and no I/O, it should be SQL. If it has I/O, it should not be in a query at all.

Pitfalls

Fixing the query instead of the model. These patterns live in dbt models and views, not ad-hoc queries. Fix the model once; every consumer inherits it.

Trusting runtime instead of bytes scanned. A bad query on a big warehouse finishes fast. Check bytes scanned and partitions pruned in the query profile, not the stopwatch.

EXCEPT/EXCLUDE as a SELECT * pardon. SELECT * EXCEPT (blob) is better than bare *, but it still breaks when the next wide column arrives. Name what you ship.

Rewriting predicates without checking semantics. Moving a time zone conversion to the literal side changes which rows land on the boundary. Diff the row counts before and after.

Assuming the optimizer will save you. Some engines rewrite trivial date(col) = predicates into ranges. None of them rescue an OR-join or a per-row external UDF. Write it right.

FAQ

Is SELECT * ever fine? Ad-hoc exploration, LIMIT 100, and the innermost layer of a SELECT count(*) FROM (...) where the optimizer prunes columns anyway. In a persisted model, no.

How do I find these in an existing codebase? Grep for select *, distinct, or inside on clauses, and date(/cast( inside where. Then sort your query history by bytes scanned and read the top twenty. The two lists overlap.

Does DISTINCT ever beat GROUP BY? They compile to the same plan on modern engines. The problem is not DISTINCT the keyword, it is DISTINCT as a substitute for knowing your join grain.

My OR-join is on the same column, like status IN two values. Same problem? No. OR on one column against literals is a normal filter and prunes fine. The pathological case is OR connecting predicates that reference different column pairs across the two tables.

Are correlated subqueries in SELECT the same anti-pattern as UDFs? Close cousin. Engines decorrelate some of them into joins; when they cannot, you get per-row execution. If the subquery reads another table, rewrite it as a join or a window function.

What this means for data engineers

Every one of these five is a query that asks the engine to touch more data than the answer requires. Column lists control width. Sargable predicates control which partitions load. Grain-correct joins control row explosion. Equi-joins keep hash joins possible. Set-based logic keeps execution vectorized.

Fix them in the model layer, add a uniqueness test where the DISTINCT used to be, and put the grep list into your PR review checklist. The bill drops within a week, and unlike a warehouse downsize, nothing gets slower.

The habits are free. The absence of them is what you are paying for.