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
WHERESTEP 1 · PRUNE ROWS
SELECTSTEP 2 · PRUNE COLUMNS
STEP 3
aggregatecheap now
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. -- ...then every downstream filter prunes on the bare, correctly-typed column: WHERE event_ts >= '2024-05-25'::timestamp AND event_ts < '2024-05-26'::timestampsql: event_ ts: : timestamp AS event_ts
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_ FROM fct_date, sum( l. amount_ eur) AS revenue order_ JOIN fct_orders o ON o.order_id = l.order_id GROUP BY o.lines l order_ -- 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_date; order_ GROUP BY order_id ) SELECT o.lines order_ FROM per_order p JOIN fct_orders o ON o.order_id = p.order_id GROUP BY o.date, sum( p. amount_ eur) AS revenue 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_ -- 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.id OR c.legacy_id = o. customer_ id; customer_ 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.id 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:
APPROX_COUNT_DISTINCTCOUNT(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.ORDER BYLIMIT to save moneySELECT ... 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.LIKE costsLIKE '%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.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_ (user_id) FROM events; -- lend the small warehouse serverless muscle for outlier scans ALTER WAREHOUSE bi_wh SET ENABLE_distinct QUERY_ QUERY_ACCELERATION = TRUE 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
colCase 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.
WHERE to_char(event_ts, 'YYYY-MM-DD') >= ...: the column is turned into text, so its min/max are useless for pruning.SELECT * feeding a dashboard that shows five numbers.COUNT(DISTINCT user_id) over the 30 days, for one "unique buyers" tile.APPROX_COUNT_DISTINCT → spill gonedim_menu_item, which keeps every past version of a dish: three rows per item, so every event came out three times.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.

