Most people think SQL is fast because "the database is optimized." That's true, but it's such a shallow answer it's almost useless. If you've ever had a query run fine on a 50K-row staging table and crawl on a 40M-row production table with the exact same SQL, you already know the real answer is more interesting — and more mechanical — than "the engine is smart."
This post breaks down what actually happens between you hitting Run and getting your rows back, specifically on Snowflake and Redshift, since the two take genuinely different architectural approaches to solving the same problem.
Your SQL is a request, not an instruction
The first thing to internalize: SQL is declarative. You describe what you want, not how to get it. The engine's query optimizer is the component that decides how. It parses your query, checks statistics about your tables (row counts, value distributions, min/max ranges per partition), and evaluates multiple possible execution plans before picking the one it estimates will cost the least.
This is why identical SQL can perform completely differently depending on data size, indexing, or even how recently statistics were refreshed. The query text never changes; the plan behind it does.
Snowflake's architecture: separating storage, compute, and metadata
Snowflake splits itself into three distinct layers, and understanding this split explains almost everything about why it behaves the way it does:
- Storage layer — your data sits in cloud object storage (S3, Azure Blob, or GCS) as compressed, columnar micro-partitions, typically 50–500MB of uncompressed data each.
- Compute layer — "virtual warehouses" are independent MPP compute clusters you spin up to run queries. Multiple warehouses can query the same underlying data simultaneously without contending for resources, because compute is fully decoupled from storage.
- Services layer — this is the part people forget about. It manages metadata, security, and — critically — the optimizer itself.
Why Snowflake queries can be fast without indexes at all
Snowflake doesn't use traditional indexes. Instead, every micro-partition carries metadata about the min/max values of each column stored inside it. When you filter with a WHERE clause, Snowflake's optimizer uses this metadata to skip entire micro-partitions that can't possibly contain matching rows — a technique called partition pruning. If your table is well-clustered on the columns you filter by often, this can mean scanning a tiny fraction of the actual data on disk, even on a table with billions of rows.
Snowflake also processes data using vectorized execution — operating on batches of column values at once rather than row-by-row — and worker nodes exchange data directly during joins, avoiding some of the shuffle-heavy overhead that plagues naive distributed joins.
The catch
None of this is free if your data isn't organized well. If a table isn't clustered on the columns you actually filter by, pruning doesn't help much, and Snowflake falls back to scanning far more micro-partitions than necessary — which shows up directly in your compute cost, since Snowflake bills by warehouse runtime.
Redshift's architecture: a leader node and a fleet of workers
Redshift takes a more classically MPP (massively parallel processing) approach, structured around actual physical clusters rather than fully decoupled layers:
- Leader node — receives your query, parses it, builds the execution plan, and compiles the plan into executable code. It also coordinates communication with your SQL client and handles a small set of functions that run exclusively on the leader node itself.
- Compute nodes — each stores a slice of your table's data and executes its portion of the plan in parallel. Results get sent back to the leader node for final aggregation.
Why distribution keys and sort keys matter so much
Because Redshift's compute nodes each own a physical slice of the data, how that data is distributed across nodes directly determines whether a join can run locally on each node or requires shuffling rows across the network first. If you join two tables on a column that isn't the distribution key, Redshift has to redistribute rows across nodes to align matching keys before the join can proceed — this is often the single biggest hidden cost in a slow Redshift query.
Sort keys work similarly to Snowflake's micro-partition metadata: Redshift tracks the min/max range of sorted columns per block, and can skip blocks that fall outside your filter range entirely — this is Redshift's version of partition pruning, and it depends entirely on your table being sorted on the columns you commonly filter by.
Compiled code, not interpreted SQL
One detail that surprises people: Redshift doesn't re-interpret your SQL text every time you run a query. The leader node compiles the execution plan into actual executable code tailored to your specific query and schema, and that compiled code can be reused for repeated executions of similar queries — meaning stable, frequently-run queries genuinely get faster the more they're run.
Where the two architectures actually diverge in practice
| Aspect | Snowflake | Redshift |
|---|---|---|
| Compute/storage | Fully decoupled, independently scalable | Coupled to node/cluster you provision |
| Data organization | Automatic micro-partitions + clustering | Manual/auto distribution keys + sort keys |
| Pruning mechanism | Micro-partition min/max metadata | Sort key block ranges |
| Join cost driver | Data co-location via clustering | Distribution key alignment across nodes |
| Concurrency model | Multiple independent virtual warehouses | Shared cluster, workload manager (WLM) queues |
| Query execution | Vectorized, direct worker-to-worker exchange | Compiled code per query, reused on repeat runs |
The part that actually matters for you as an engineer
Neither engine's speed comes from magic — it comes from giving the optimizer good conditions to work with. On Snowflake, that means understanding clustering and not fighting it with poorly organized load patterns. On Redshift, that means deliberately choosing distribution and sort keys that match your actual join and filter patterns, not just letting DISTSTYLE AUTO guess forever.
I've seen this play out directly: a query that ran in seconds on a small staging table took over 4 minutes in production. The SQL hadn't changed. What had changed was that the join column in the production table didn't match the distribution key, so Redshift redistributed rows across every node before it could even start the join — pure network and shuffle overhead, invisible in the query text itself. Aligning the distribution key to the join column brought it back down to seconds.
The real lesson
SQL performance isn't about clever syntax. It's a systems problem — the optimizer, the storage layout, the distribution of data across compute, and the statistics the engine has about your tables all matter more than how you phrase your SELECT. Writing fast SQL means understanding what your specific engine needs to make a good decision, and then giving it that, deliberately, instead of hoping the optimizer figures it out on its own.
Once you start thinking in terms of "what will the optimizer actually do with this," you stop debugging queries by rewriting SQL syntax and start debugging them by checking EXPLAIN, clustering keys, and distribution keys — which is a completely different (and much more effective) way to work.
What's the deepest "it wasn't the SQL, it was the architecture" bug you've run into on Snowflake or Redshift?
Top comments (0)