SQL Query Planning And Optimization

What's being tested

Demonstrates reading and reasoning about a execution plan to find bottlenecks, choose better join strategies, and rewrite SQL for lower cost. Interviewers probe familiarity with the cost-based optimizer, cardinality estimates, and practical tools like `EXPLAIN` to validate fixes. At `Snowflake`, this maps to keeping queries predictable, reducing compute costs, and diagnosing skewed stages or bad statistics.

Patterns & templates

• Filter pushdown — apply WHERE predicates early; reduces rows shipped between stages and lowers memory/CPU usage.

• Projection pruning — select only needed columns to avoid unnecessary I/O and wide-row materialization.

• Join choice template — use hash join for large unsorted inputs if build-side fits memory; merge join for pre-sorted streams; nested-loop for tiny inner side.

• Join reordering / bushy plans — join smaller selective tables first to reduce intermediate cardinalities; optimizer cost = sum(plan costs).

• Use EXPLAIN / profile — inspect row counts, operator costs, and parallelism; compare estimated vs actual cardinalities to find bad stats.

• Rewrite anti/exists patterns — NOT EXISTS often outperforms NOT IN with NULLs; convert correlated subqueries to joins when safe.

• Window vs aggregation tradeoff — prefer GROUP BY for aggregates; use window functions like ROW_NUMBER() OVER (PARTITION BY...) only when per-row ordinal is required.

Common pitfalls

Pitfall: Trusting optimizer estimates — large discrepancies between estimated and actual row counts often indicate stale/missing statistics or data skew.

Pitfall: Adding indexes or clustering without measuring — unnecessary maintenance can increase write cost and not improve the hot query.

Pitfall: Overusing SELECT * — forces full-column materialization, hidden I/O, and wider shuffle/serialize costs.

Practice these

The practice cards below cover canonical variants — solve all of them and time yourself.