Snowflake Performance and Optimization: Beyond "Just Use a Bigger Warehouse"

Snowflake's separation of storage and compute makes it forgiving in ways Redshift and Databricks aren't — but that same flexibility means it's easy to paper over real inefficiencies by resizing a warehouse instead of fixing the underlying query or schema issue. Here's where the real leverage is.

1. Warehouse sizing: match the workload, not the biggest table

Warehouse size affects parallelism and memory, not just raw speed. A common mistake is sizing up because a query "feels slow" without checking whether it's actually compute-bound or waiting on something else (queuing, external function latency, a poorly-clustered table).

• ▸Start smaller than you think you need and scale up only when
  Code

  query_history

  shows genuine compute saturation, not queuing.
• ▸Use multi-cluster warehouses for concurrency problems (many simultaneous users), not single-warehouse resizing — a bigger warehouse doesn't fix ten people running dashboards at 9am, more clusters serving the same queue does.
• ▸Set
  Code

  AUTO_SUSPEND

  aggressively (60-300 seconds) for ad hoc/BI warehouses — idle compute is the most common source of wasted Snowflake spend, and it's a pure configuration fix with zero query rewriting required.

SQL
SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;

Run this monthly. Warehouses with high credit consumption but low actual query volume are almost always an auto-suspend misconfiguration, not a legitimate compute need.

2. Clustering keys — but only when you actually need them

Snowflake auto-clusters by natural ingestion order by default, which is often good enough. Explicit clustering keys make sense specifically when:

• ▸Table is large (multi-TB) and
• ▸Queries consistently filter or join on columns that don't correlate with ingestion order

SQL
ALTER TABLE sales.orders CLUSTER BY (customer_id, order_date);

-- Check clustering quality before and after
SELECT SYSTEM$CLUSTERING_INFORMATION('sales.orders', '(customer_id, order_date)');

Reclustering has an ongoing background compute cost — don't set a clustering key on a table just because it's large; set it because you've confirmed pruning is poor without one.

3. Result caching and query result reuse

Snowflake automatically caches query results for 24 hours when the underlying data hasn't changed. This is free performance you should design around:

• ▸Keep dashboard queries textually identical where possible (same whitespace, same casing doesn't matter, but same logical query) so they hit the result cache instead of re-executing.
• ▸Be aware that
  Code

  CURRENT_TIMESTAMP()

  or other non-deterministic functions in a query defeat caching — parameterize date ranges explicitly rather than using relative time functions inside frequently-repeated queries.

4. Micro-partition pruning: understand what defeats it

Snowflake's automatic micro-partition pruning is doing most of the work most of the time, but a few patterns silently defeat it:

• ▸Functions wrapped around a filtered column (
  Code

  WHERE YEAR(order_date) = 2026

  instead of
  Code

  WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'

  ) prevent pruning because the predicate isn't directly comparable to partition min/max metadata.
• ▸Wide, unpartitioned scans on tables without a natural ingestion correlation to the filter column — this is exactly the case clustering keys are meant to solve.

5. Dynamic tables over manual incremental logic

For teams still hand-rolling incremental merge logic in stored procedures or orchestrator tasks, Dynamic Tables often replace that complexity outright:

SQL
CREATE DYNAMIC TABLE sales.daily_rollup
  TARGET_LAG = '15 minutes'
  WAREHOUSE = etl_wh
AS
SELECT order_date, SUM(amount) FROM sales.orders GROUP BY order_date;

Snowflake manages the incremental refresh automatically and tracks lineage between dynamic tables — less custom orchestration code to maintain, and one less place for a subtle bug in merge logic to hide.

6. Zero-copy cloning for dev/test instead of full data copies

If your dev and staging environments are built by physically copying production data, stop — zero-copy clones are instant and free until the cloned data diverges from the source:

SQL
CREATE DATABASE dev_db CLONE prod_db;

This isn't just a performance tip, it's a cost tip: teams that clone instead of copy routinely cut their non-production storage costs dramatically, since cloned data only consumes new storage as it's modified.

7. Query profile is where the real answers live

Before guessing at optimizations, pull the query profile (Snowsight → query history → the specific query) and look for:

• ▸Exploding joins (row count growing dramatically between steps) — usually a missing or wrong join condition.
• ▸Spilling to local/remote storage — the warehouse is undersized for that specific query's working set, not necessarily for the workload as a whole.
• ▸Large partition scans relative to partitions pruned — a clustering or filter-predicate problem, not a warehouse-size problem.

The pattern across all of this

Snowflake rewards diagnosing before resizing. It's easy to solve a slow query by bumping the warehouse size, and that fix works — but it's usually the most expensive fix available, applied to a problem that a clustering key, a rewritten predicate, or a smarter caching pattern would have solved for free.