SQL Query Optimization Questions cheat sheet
Plans, pruning, join order, window costs, and the rewrites interviewers use to test whether you can actually make a query cheaper.
Reading a plan
What is the first thing you look at in a query plan?- The most expensive node by actual time or bytes scanned, not the first operator. Estimated versus actual row counts next — a large gap means stale stats or a correlated predicate, and every later join is then planned on a lie. Interviewers want the habit of reading the plan before rewriting SQL.
Why do warehouses not use indexes the way Postgres does?- They store data in large compressed column files and prune with min/max metadata per file or micro-partition. A B-tree lookup is the wrong primitive when a query already scans billions of rows. Clustering, partitioning, and Search Optimization replace indexes; treating a warehouse like OLTP is the classic wrong answer.
When is a sequential scan actually the right plan?- When the predicate is not selective enough. In Postgres, above roughly 5 to 10 percent of the table, random index lookups lose to a sequential scan. In a warehouse, a filter that matches most partitions will scan most of the table no matter how clever the SQL looks.
What does spilling in a Snowflake profile or Spark UI tell you?- The query needed more memory than the warehouse or executor had. Local spill is a warning; remote spill is a hard size signal. Resizing up is correct here. Adding a clustering key is not — clustering does not give a query more memory.
How do you tell a pruning problem from a compute problem?- If bytes scanned barely drop after adding a filter, pruning failed. If bytes scanned look right but elapsed time is high, the remaining work is compute or a join. The Query Profile or INFORMATION_SCHEMA.QUERY_HISTORY bytes_scanned column answers this in one number.
Filters and pruning
Why does WHERE date(ordered_at) = '2026-08-01' scan the whole table?- A function on the column hides it from the pruner. Compare the column to a range instead: ordered_at >= '2026-08-01' AND ordered_at < '2026-08-02'. The same bug appears as CAST(ts AS DATE) in BigQuery and as DATE_TRUNC in Snowflake.
What is partition elimination and how do you verify it happened?- The engine skips files or micro-partitions whose metadata cannot match the filter. Verify with bytes scanned versus table size, or with Snowflake's partitions_scanned versus partitions_total. A 1 TB table returning yesterday's rows should scan a few gigabytes, not a terabyte.
When does clustering help a query, and when does it just cost money?- It helps when queries repeatedly filter a large table on a low-to-moderate cardinality column that matches the clustering key. It costs money on every write forever. Tables under a terabyte and tables that are queried by a different column than they are clustered on are the usual places clustering burns credits for nothing.
Why can SELECT * be a cost problem even with a tight WHERE?- Columnar engines still read every selected column. A 200-column fact table scanned for three measures pays for the other 197. Name the columns. This is the cheapest rewrite in BigQuery and Snowflake on-demand billing.
What is predicate pushdown, and when does it fail?- The engine applies filters as close to the scan as possible, including inside Parquet or Iceberg readers. It fails across some UDFs, after a DISTINCT, and when a subquery is materialized. Wrapping a source in a CTE that the engine cannot inline is a common way to lose it.
Joins
Broadcast join versus shuffle join — when is each correct?- Broadcast the small side when it fits in memory on every worker, typically tens to a few hundred megabytes. Shuffle both sides when neither is small. Broadcasting a growing dimension is the silent production failure; yesterday it fit, today it OOMs.
What is a fan-out join and how do you detect it?- Joining on a key that is not unique on one side multiplies rows, so revenue doubles and nobody notices until finance does. Detect it by comparing row counts before and after the join, or by asserting uniqueness on the join key in dbt. The interviewer is looking for grain, not just join syntax.
Why does joining two large tables on a skewed key stall?- One key holds most of the rows, so one partition does almost all the work while the rest sit idle. Fixes are salting the key, isolating the hot key, or using AQE skew join in Spark. Explaining only 'add more workers' is the wrong answer.
When should you pre-aggregate before a join?- When the join key's grain is coarser than the fact. Aggregating daily orders to customer-day before joining a customer dimension cuts shuffle volume by orders of magnitude. Joining first and aggregating after is the shape that makes a warehouse look slow.
Does join order matter in a cost-based optimizer?- Yes, because cardinality estimates drive it. The optimizer will pick a bad order if stats are stale or a filter is correlated. Hints exist but are a last resort; fixing stats or rewriting the filter is the durable fix. Interviewers use this to see if you treat the optimizer as infallible.
Window functions and aggregations
Why can a window function be more expensive than a self-join?- It sorts or hashes the whole partition. A running total over an unfiltered fact table sorts every row. Restrict the input first, and prefer ROWS BETWEEN when RANGE is not required — RANGE must look at peer values and costs more.
COUNT(DISTINCT) versus approximate count — when is each acceptable?- Exact distinct is a shuffle of every unique value and dominates large-cardinality columns. HyperLogLog or APPROX_COUNT_DISTINCT is fine for dashboards where 2 percent error is invisible. Finance totals are not dashboards. Know which number you are publishing.
How do you rewrite a correlated subquery that runs once per row?- Turn it into a join against a pre-aggregated set, or a window function. In Postgres, EXPLAIN ANALYZE showing loops equal to the outer row count is the tell. Warehouses often decorrelate automatically, but only if the subquery is simple enough.
GROUPING SETS versus multiple GROUP BYs — which is cheaper?- GROUPING SETS (or ROLLUP) scans the input once and produces several grains. Three separate GROUP BY queries scan three times. Use it for the common dashboard that needs day, week, and month from the same fact.
Rewrites that actually work
Give three rewrites that commonly cut warehouse spend in half.- Filter on the partition or clustering column without wrapping it in a function. Select only needed columns. Replace a DISTINCT over a join with a QUALIFY ROW_NUMBER on the correct key. Those three, in that order, are what I actually do in incident reviews.
When is CREATE TABLE AS SELECT cheaper than a view?- When many consumers run the same expensive logic. A view re-runs it every time; a table pays once. The tradeoff is freshness. Materialize what dashboards hit a thousand times a day, leave the rest as views or incremental models.
How do you make an incremental load cheaper without losing late data?- A lookback window, not a strict greater-than-max. WHERE ordered_at >= (SELECT MAX(ordered_at) FROM this) - INTERVAL '3 days' reprocesses a few days and stays correct. The strict max misses anything that arrived late.
What is the difference between optimizing the query and optimizing the table?- Query changes are local and reversible. Table changes — clustering, partitioning, file compaction — help every query and cost on every write. Start with the query. Change the table only when several important queries share the same access pattern.
Process and culture
How do you decide a query is 'fast enough'?- Against the SLA, not against a feeling. A dashboard that loads in three seconds is done even if the plan looks ugly. A nightly job that finishes at 09:30 is late even if every operator looks efficient. Interviewers are testing whether you optimize for users or for aesthetics.
What do you do when a query is slow and you cannot change the SQL?- Change the physical layout: clustering, search optimization, a covering mart, or a larger warehouse for that workload only. Isolate the warehouse so the slow query cannot starve everyone else. Then go back and get permission to change the SQL, because layout is rent you pay forever.
How do you prevent a 'fixed' query from regressing?- A test on row counts and a CI check on bytes billed or a runtime threshold. Without that, the next refactor reintroduces SELECT * and the bill comes back. Query tags plus a weekly ACCOUNT_USAGE review catch the ones tests miss.
What is a question you would ask before touching a slow query?- What changed. Volume, a new join, a missing partition filter after a schema change, or a warehouse that was resized down. Optimizing a query that was fine last week without asking this is how you waste a day rewriting something that is not the problem.
From DataLane — tutorials at/blog, practice SQL live in theplayground.