Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 4: Optimizing SQL

Optimizing SQL

Part 4 · Snowflake · step 4

Optimising SQL: read less, compute less

The query is correct but slow. The fix is almost always in how it's written.

· · ·

Ismail's first lever, before touching any warehouse, is the SQL itself. Snowflake is fast, but you can make any query crawl. Nearly every slow query is doing one of two things: reading more data than it needs, or making the engine do expensive work it could skip. Two habits fix most of it, filter early and ask for less.

STEP 0

the full tableevery partition1 TB
WHERE

STEP 1 · PRUNE ROWS

matching partitionsonly what can match40 GB
SELECT

STEP 2 · PRUNE COLUMNS

3 columns, not 40columnar storage wins2 GB

STEP 3

join
aggregatecheap now
every technique below is one of these arrows
shrink the data before the expensive operators see it

Filter early, and on the bare column

Filter on the raw column so pruning kicks in. Some functions wrapped around a column still let Snowflake compare against the partition min/max, many don't, and when they don't it reads everything. A range on the bare column always works, so write that by default and let the profile prove anything fancier.

-- risky: depending on the function, this may hide the column from pruning
WHERE to_date(created_at) = '2024-05-25'

-- do: filter the bare column with a range
WHERE created_at >= '2024-05-25'
  AND created_at <  '2024-05-26'

The silent pruning killer: implicit type conversion

Pruning compares your filter value against each partition's min/max for that column. If the column's stored type doesn't match what you compare it to, Snowflake casts the column to make the comparison, and a cast on the column is a function on the column: the min/max no longer line up and the whole table gets scanned. The classic case is a timestamp that arrived as a string.

-- event_ts arrived as VARCHAR (a common raw-load mistake).
-- Filtering it as a date casts the COLUMN to compare -> pruning dies -> full scan.
WHERE event_ts::timestamp >= '2024-05-25'      -- column cast, no pruning

-- Fix at the source: cast once in staging so the column IS a real TIMESTAMP...
-- stg_events.sql:  event_ts::timestamp AS event_ts
-- ...then every downstream filter prunes on the bare, correctly-typed column:
WHERE event_ts >= '2024-05-25'::timestamp
  AND event_ts <  '2024-05-26'::timestamp

The rule of thumb: cast the literal to match the column, never the column to match the literal. If a filter you expected to prune is scanning everything, check the types on both sides before you touch anything else.

Dedup with QUALIFY, and say which row wins

A SELECT DISTINCT on a model that "shouldn't" have duplicates is usually hiding a join that fanned out, the fix belongs in the join, not on top of it. When you genuinely need one row per key (the latest version of each order, say), DISTINCT can't help: two versions of the same order differ in updated_at, so both survive. QUALIFY filters on a window function, the same way HAVING filters on an aggregate, and lets you state which row survives.

Don't expect it to be automatically cheaper. A window function sorts inside each partition, and on a huge table that sort can cost as much as the DISTINCT it replaces, check the profile if it matters. The win is correctness: the surviving row is the one you chose, not an accident of the data.

-- doesn't work: two versions of order 42 differ in updated_at, both survive
SELECT DISTINCT order_id, status, updated_at FROM orders

-- do: keep the latest row per order, explicitly
SELECT *
FROM orders
QUALIFY row_number() OVER (
  PARTITION BY order_id ORDER BY updated_at DESC) = 1

Joins: the four rules

Joins are where good queries go to die, and it's nearly always one of four mistakes.

1. Shrink the inputs before the join

A join is only as cheap as the rows reaching it. For a plain WHERE on an inner join you don't have to do anything: the optimizer pushes the filter below the join on its own, and writing it in a CTE first produces the same plan. Where it does matter is everything the optimizer can't move for you: aggregations (sum the many-side to one row per key, then join), filters on the outer side of a LEFT JOIN, and filters on computed values.

-- avoid: join 50M order lines to orders, then aggregate
SELECT o.order_date, sum(l.amount_eur) AS revenue
FROM fct_order_lines l
JOIN fct_orders o ON o.order_id = l.order_id
GROUP BY o.order_date;

-- do: collapse the lines to one row per order first, then join 2M rows
WITH per_order AS (
  SELECT order_id, sum(amount_eur) AS amount_eur
  FROM fct_order_lines
  GROUP BY order_id
)
SELECT o.order_date, sum(p.amount_eur) AS revenue
FROM per_order p
JOIN fct_orders o ON o.order_id = p.order_id
GROUP BY o.order_date;

Check it in the profile rather than trusting it: the optimizer sometimes does this for you too, and the rows flowing into the Join node tell you who won.

2. Never put OR in a join condition

This is the single worst thing you can do to a join. Snowflake can match rows fast when the condition is a plain equality, because it can hash both sides and pair them up. An OR destroys that: there's no single key to hash on, so the engine falls back to comparing every row against every row. Ten thousand rows joined to ten thousand rows is a hundred million comparisons.

-- avoid: OR in the ON clause, close to a cartesian product
SELECT o.order_id, c.country
FROM orders o
JOIN customers c
  ON c.id = o.customer_id OR c.legacy_id = o.customer_id;

-- do: one equality per join, then stack the two results
SELECT o.order_id, c.country
FROM orders o JOIN customers c ON c.id = o.customer_id
UNION                                   -- UNION, not UNION ALL: a customer matching
SELECT o.order_id, c.country             -- on both ids would otherwise appear twice
FROM orders o JOIN customers c ON c.legacy_id = o.customer_id;

UNION also merges rows that are genuinely identical, which is fine here because order_id is unique. If you can guarantee no row matches on both keys, UNION ALL is cheaper, it skips the dedup. Even better, fix it upstream: resolve the legacy id to the current one once in staging, and every downstream join becomes a single equality.

The same logic applies to any non-equality condition in a join: ON a.x <> b.x, ON a.d BETWEEN b.start AND b.end, or a function wrapped around the join key. They all force row-by-row comparison. Sometimes a range join is genuinely what you need, and then reach for it knowingly. For the most common one, "attach the price that was valid when the order happened", Snowflake has ASOF JOIN, which matches each row to the closest earlier one without the cross-product.

3. Join on columns of the same type

A VARCHAR id joined to a NUMBER id forces a cast on every row and blocks pruning on that column. Fix the type once in staging rather than casting in twelve downstream queries.

4. Watch the fan-out, it's a correctness bug too

A join on a non-unique key multiplies rows. Join one 100€ order to its three shipment records and that order appears three times, so SUM(amount) reports 300€ of revenue that never existed. The query runs fine, the dashboard smiles, and finance reconciles the difference three weeks later. Aggregate the many-side to one row per key before joining, or test the grain like part 2 taught.

Ask for the columns, not the whole table

Snowflake stores data columnar: inside every micro-partition each column is a separate, independently-compressed block, and the header records the byte range of each one. So a query reads only the columns it names, by fetching just those byte ranges, and skips the rest of the partition. That's the mechanism behind a boring-sounding rule: SELECT * on a 40-column table can read many times the data of SELECT order_id, amount for the exact same rows, because it drags every column's block off disk when you needed two. Name the columns you need, always, and a wide table stops being expensive to query narrowly.

When the basics are done: Ismail's five extra levers

Pruning fixed, columns named, joins ordered. Ismail's next pass is five smaller levers, each shaving real work:

Lever
Why it pays
APPROX_COUNT_DISTINCT
Exact COUNT(DISTINCT) on a high-cardinality column is memory-hungry, a classic spill source. The approximate version costs a fraction for roughly 1-2% error, which a "daily active users" tile never notices. One trap: you can't add up daily approximate counts to get a weekly one (the same user is counted every day). Store an HLL_ACCUMULATE state per day, merge the days with HLL_COMBINE, and turn the result into a number with HLL_ESTIMATE.
Drop mid-model ORDER BY
Sorting is one of the most expensive operators, and sorting a table nobody reads in order is pure waste. Sort at the edge, in the BI tool or the final query. The one exception is sorting on purpose by the column you filter on, to cluster the table at write time (step 3).
Don't trust LIMIT to save money
A bare SELECT ... LIMIT 10 can stop early, Snowflake reads a few partitions and returns. Add an ORDER BY, a GROUP BY, a window or a join, and every input row has to be processed before the first ten exist, so the query reads and bills the full scan. Two exceptions: an ORDER BY col LIMIT n on a column whose min/max let Snowflake rule partitions out can stop early too (top-K pruning), and previews with a plain LIMIT stay cheap. Only filters prune reliably.
Know what your LIKE costs
LIKE '%term%' starts with a wildcard, so no partition can be ruled out: every row gets read. If you actually mean a prefix, write the prefix ('term%'), which can prune when the table is roughly sorted on that column. If you really need a substring on a huge table, that's what search optimization ON SUBSTRING(col) is for (step 3), Enterprise edition, billed serverless, so reserve it for lookups that genuinely matter.
Query Acceleration Service
One scan-heavy outlier a day doesn't justify a bigger warehouse all day. QAS (Enterprise edition) lends serverless workers to just those scans, billed separately, and the warehouse stays small for everything else. SYSTEM$ESTIMATE_QUERY_ACCELERATION('<query_id>') tells you whether a given query would benefit before you turn it on.
-- the cheap distinct
SELECT approx_count_distinct(user_id) FROM events;

-- lend the small warehouse serverless muscle for outlier scans
ALTER WAREHOUSE bi_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

Secure views: privacy has a performance price

One more trap hides in the DDL. A secure view hides its definition and protects its underlying data from non-owners, which is exactly right when you share data across accounts or enforce row-level privacy. But that protection works by disabling optimizer shortcuts: Snowflake deliberately skips optimizations, like pushing your filters down into the view, that could let a clever query infer something about rows the view is supposed to hide. The result: the same query on a secure view can scan far more data and run visibly slower than on a plain view. The rule is simple, make a view secure when a real sharing or privacy requirement demands it, and never as a default habit, because every unnecessary SECURE is paid for on every query, forever.

What to avoid, and what to do instead

avoid
do instead
why
func(col) = x
a range on the bare col
keeps partition pruning alive
SELECT *
name the columns you need
columnar: fewer columns, less IO
SELECT DISTINCT to hide dupes
fix the join, or QUALIFY row_number()
you choose the surviving row, no hidden fan-out
ORDER BY (unneeded)
drop it
sorting a large result is costly
LIKE '%term%' on a huge table
a prefix if that's what you mean, else search optimization ON SUBSTRING
a leading wildcard scans every row
SECURE views by default
plain views, secure only when sharing or privacy demands it
secure views skip optimizations, so they scan more
join, then aggregate
aggregate the many-side, then join
plain filters get pushed down for you, aggregations don't
ON a.x = b.x OR a.y = b.y
two equality joins, then UNION
OR blocks hash join, forces row-by-row
joining mismatched types
cast once in staging
a per-row cast kills pruning
NOT IN (subquery)
NOT EXISTS, or a LEFT JOIN … IS NULL
NOT IN returns nothing at all if the subquery has a NULL

Case study: the Monday-morning revenue dashboard

Everything above, applied to one real query. IsmaFrost's finance dashboard refreshes at 8am, and one Monday the refresh takes 14 minutes on a Medium warehouse. Before reading a line of SQL, Ismail opens the query profile, and it tells the whole story: TableScan eats 91% of the time, 2,748 of 2,750 partitions were scanned, and 6.4 GB spilled to local storage. The tiles cover the last 30 days.

The profile said
The cause in the SQL
The fix
2,748 / 2,750 partitions
WHERE to_char(event_ts, 'YYYY-MM-DD') >= ...: the column is turned into text, so its min/max are useless for pruning.
Range filter on the bare column → 61 partitions
38 columns read
SELECT * feeding a dashboard that shows five numbers.
Name six columns → a fraction of the IO
6.4 GB spilled
Exact COUNT(DISTINCT user_id) over the 30 days, for one "unique buyers" tile.
APPROX_COUNT_DISTINCT → spill gone
Join fan-out
Events joined to dim_menu_item, which keeps every past version of a dish: three rows per item, so every event came out three times.
Join on the version valid at the event's time (the capstone's date-range join) → a third of the rows, and the right revenue

Result: 14 minutes down to 40 seconds, on the same Medium warehouse, and the morning credit burn dropped with it. Nobody touched the warehouse size, because the warehouse was never the problem: the query read roughly 45 times more data than the dashboard needed. That's the pattern to internalise. When a query is slow, open the profile first, it points at the waste, and the waste is almost always in the SQL.

The whole idea, in one line
Read fewer rows (filter, prune), read fewer columns (project), and drop work the result doesn't need (a sort, a distinct, an extra join). Almost all SQL tuning is one of those three.
Watch for spilling
If the query profile shows bytes spilled to local or remote storage, the warehouse ran out of memory and is paging to disk, which is slow. Read less first, if it's genuinely a big job, size the warehouse up (next step).

Lesson quiz

  1. orders.customer_id is VARCHAR and customers.id is NUMBER. What goes wrong when you join them?

    1. A cast runs on every row and blocks pruning on that column
    2. Nothing: Snowflake stores both types the same way inside
    3. The join returns no rows because the types can't compare
    4. Snowflake refuses to run the join until both types match
  2. A table holds several versions per order_id. Which query returns only the latest version of each order?

    1. SELECT order_id, max(status), max(updated_at) FROM order_versions GROUP BY order_id
    2. ORDER BY updated_at DESC LIMIT 1
    3. SELECT DISTINCT order_id, status, updated_at
    4. QUALIFY row_number() over (partition by order_id order by updated_at desc) = 1
  3. A model joins ON c.id = o.customer_id OR c.legacy_id = o.customer_id and takes 40 minutes. Which rewrite follows the lesson and keeps the result the same?

    1. One join ON coalesce(c.legacy_id, c.id) = o.customer_id
    2. The same OR join on a warehouse twice the size
    3. Two equality joins, combined with UNION
    4. Two equality joins, combined with UNION ALL