SQL Query Optimization for Columnar Warehouses

Your slow query likely scanned more data than it needed, shuffled too many rows between workers, or spilled to disk. On Snowflake and BigQuery, sql query optimization is about pruning and distribution: read less, move less, stay in memory. Start with the profile, then rewrite to align filters with time-based pruning, cluster on common predicates, tame many-to-many joins, and trim windows. That path will improve database performance and reduce cost. The examples below are the sql query optimization techniques we use in production to optimize your sql queries without cargo-culting OLTP indexing advice. If you’re optimizing sql queries for analytics, measure scanned bytes, shuffle, and spill for each change—you’ll see execution time track those metrics.

Read the execution profile first

The fastest way to improve performance is to look at what actually ran. Profiles show query execution stages, not guesses. On warehouses, table scans, big shuffles, and spill dominate sql performance.

Warehouse	Where to look	Signals to watch
Snowflake	Query Profile	Pruning effectiveness, Join build vs probe size, Bytes spilled (local/remote), row/column counts
BigQuery	Execution details	Input bytes, Partitions scanned, Shuffle bytes, Slot time, Stage parallelism

What to scan for: high “partitions scanned,” large “shuffle bytes,” and any spill. The optimizer relies on statistics to estimate cardinality; skew, null-heavy keys, or poorly written filters yield bad plans and slow queries. You’re looking for the bottleneck stage.

• Snowflake: expand the heaviest operators; read “Bytes spilled,” “Rows produced,” and “Pruning.”
• BigQuery: use EXPLAIN or the Execution details DAG for per-stage bytes and slot usage. Docs: BigQuery EXPLAIN, Snowflake Query Profile.

Baseline your own system: record scanned bytes, shuffle bytes, and wall-clock; each rewrite should reduce at least one. That is how you learn sql query optimization on your workload and server size.

Pruning wins: time-based pruning and clustering done right

On columnar systems, “use indexes” translates to “align filters with time pruning and cluster on selective columns.” Most outcomes are decided before the first operator runs.

-- BigQuery: create a table that prunes well
CREATE TABLE dataset.orders
PARTITION BY DATE(created_at)
CLUSTER BY customer_id, status AS
SELECT * FROM dataset.raw_orders;

-- Snowflake: define a clustering key when filters are selective
ALTER TABLE ORDERS CLUSTER BY (CUSTOMER_ID, STATUS);

Rewrite filters to be sargable against the date column. Avoid wrapping it in functions.

-- Before: defeats pruning
SELECT COUNT(*)
FROM dataset.orders
WHERE DATE(created_at) BETWEEN '2025-01-01' AND '2025-01-31';

-- After: prunes to January ranges efficiently
SELECT COUNT(*)
FROM dataset.orders
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

Filtering early on the clustered/time column reduces scanned bytes and memory. In BigQuery, clustering co-locates related rows, shrinking shuffle in joins and windows. Set it once in your dbt model to match your relational schema and usage patterns:

-- dbt model config for BigQuery
{{
  config(
    materialized='table',
    partition_by={'field': 'created_at', 'data_type': 'timestamp'},
    cluster_by=['customer_id','status']
  )
}}

If a filter can’t prune (e.g., free text), derive a search key (substr, hash bucket) and cluster on it. For model design that yields efficient queries on large datasets, see our data modeling patterns that hold under load.

Joins that don’t explode: control cardinality

Exploding joins are the top production failure. A fact-to-fact join or a dimension with duplicates turns a 40M-row fact into billions. The execution plan will show a huge build/probe ratio and massive shuffle.

-- Before: many-to-many join explodes
SELECT f.order_id, d.segment
FROM fact_orders f
JOIN dim_customers d
  ON f.customer_id = d.customer_id;  -- d has duplicates over time

-- After: de-dup the dimension first, then inner join
WITH d_max AS (
  SELECT customer_id, MAX(updated_at) AS updated_at
  FROM dim_customers
  GROUP BY customer_id
)
SELECT f.order_id, d.segment
FROM fact_orders f
JOIN d_max m
  ON m.customer_id = f.customer_id
JOIN dim_customers d
  ON d.customer_id = m.customer_id AND d.updated_at = m.updated_at;

Other fixes:

• Pre-aggregate the larger side before the join if you don’t need row-level detail.
• Filter early on both sides so the optimizer can broadcast a small build side.
• Avoid joins on low-cardinality or null-heavy keys; coalesce only when semantics hold.

Need to join more than one field? Build a composite key identically on both sides (e.g., CONCAT(country, '-', sku_id)) and cluster by it when common. If you’re changing histories, do a MERGE as a final step rather than carrying extra rows through the core join.

For repo-level guardrails that keep joins tidy, see our dbt repo performance work and why your dbt run is slow.

Window functions: powerful, but they sort and shuffle

Windows force sorts and often reshuffle data. On Snowflake you’ll see big sort operators; on BigQuery, large shuffle bytes and long stages. Limit the window’s scope and avoid a deep nest of windows.

-- Before: ranks across the whole table
SELECT user_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM events;

-- After: filter first, then window on the reduced set, and QUALIFY
WITH recent AS (
  SELECT user_id, created_at
  FROM events
  WHERE created_at >= CURRENT_DATE() - INTERVAL '30' DAY
)
SELECT user_id, created_at
FROM recent
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1;

Consistent wins:

• Project only needed columns before the window; wide rows swell memory and spill risk.
• Replace some windows with grouped aggregates plus a join back to pick winners.
• Prefer QUALIFY (Snowflake, BigQuery) instead of nesting subqueries just to filter window results.

If you must rank globally, do it in stages: rank within a date bucket aligned to clustering, then a small second pass. That yields better performance with less shuffle.

Spill: detect it, then cut row width or reshuffle

Spill crushes query performance. In Snowflake, “Bytes spilled to local/remote storage” is the red flag. In BigQuery, look for stages with huge shuffle bytes and low parallelism—your memory per worker is overrun by wide rows or massive groups.

-- Before: wide rows and late filters
SELECT *
FROM events e
JOIN sessions s USING (session_id)
WHERE DATE(e.created_at) = '2025-03-01';

-- After: project early, filter early, avoid functions on the date column
WITH e1 AS (
  SELECT session_id, created_at, event_type
  FROM events
  WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02'
), s1 AS (
  SELECT session_id, user_id
  FROM sessions
)
SELECT e1.session_id, e1.event_type, s1.user_id
FROM e1
JOIN s1 USING (session_id);

Other anti-spill moves:

• Cast high-entropy VARIANT/JSON to typed columns upstream; late parsing inflates memory per row.
• Materialize big intermediates as transient/temporary tables to reset memory pressure.
• Right-size compute briefly if a truly large join is unavoidable. Decide when to scale vs refactor using our Snowflake cost optimization guidance.

Re-run and compare: scanned bytes down, spill gone, fewer seconds in the heaviest operator. That is how you improve query execution reliably.

CTE vs subquery: what the optimizer actually does

On modern warehouses, non-recursive CTEs are usually inlined; a CTE is not a cache unless you force it. Reusing the same CTE multiple times can cause the work to execute more than once.

-- Before: same CTE referenced twice; may compute twice
WITH filtered AS (
  SELECT * FROM orders WHERE created_at >= '2025-01-01'
)
SELECT COUNT(*) FROM filtered
UNION ALL
SELECT COUNT(DISTINCT customer_id) FROM filtered;

-- Option A (portable): stage to a temp table
CREATE TEMP TABLE t_filtered AS
SELECT * FROM orders WHERE created_at >= '2025-01-01';
SELECT COUNT(*) FROM t_filtered;
SELECT COUNT(DISTINCT customer_id) FROM t_filtered;

-- Option B (BigQuery): force a materialized CTE
WITH filtered AS MATERIALIZED (
  SELECT * FROM orders WHERE created_at >= '2025-01-01'
)
SELECT COUNT(*) FROM filtered;

CTEs vs subqueries are mostly style unless you hit recomputation. Choose the form that lets the optimizer push filters down and prune. If you spot duplicate base scans in the profile, materialize. For production-grade incrementals, see our dbt incremental models guide.

Platform differences, pitfalls, and a compact FAQ

Do SQL optimization techniques differ by database platform?

Yes. Snowflake leans on data pruning and can recluster; BigQuery needs explicit time and cluster definitions. Both are columnar and distributed database engine designs, unlike Microsoft SQL Server or Oracle OLTP engines that rely on B-tree indexes.

Does indexing improve SQL query performance here?

“Indexes” as in nonclustered b-trees don’t exist. Use time pruning and clustering to get index-like effects on selective filters and joins. On OLTP you might write:

-- OLTP (SQL Server) example, not for columnar warehouses
CREATE NONCLUSTERED INDEX ix_users_email
ON dbo.Users (user_id)
INCLUDE ( email, first_name )  -- alpha order is fine
;

On warehouses, that syntax won’t apply; rely on clustering and date filters instead.

When should you optimize sql queries?

When profiles show high scanned bytes, large shuffle, or spill; when costs spike; before promoting a model to prod; or when an analyst reports slow queries. This is normal performance tuning.

Any common pitfalls?

• Functions on the date filter (no pruning).
• Many-to-many joins and skewed keys.
• Windows on unfiltered, wide datasets.
• CTEs reused without materialization.

Resources to learn and apply optimization techniques?

To learn sql query optimization, study profiles and rewrite against your own data. For sql query optimization techniques that stick in analytics, start with vendor docs linked above, then our warehouse pieces: why your dbt run is slow and modeling best practices under real load. For orchestration, see Airflow + dbt integration. These will help you optimize and tune efficiently.

---

If you want a second set of hands to tune and optimize your sql queries across a real codebase—dbt models, Airflow DAGs, and warehouse settings—start with a focused review: dbt Repo Performance or contact us.