Snowflake, BigQuery, and Redshift changed data engineering by separating storage from compute and scanning columnar data in parallel. Learn what makes them fast and how to model for them.
The defining architectural shift of the cloud warehouse (Snowflake, BigQuery, Redshift) is decoupling where data lives (cheap object storage) from the engines that query it (elastic compute). You store data once and spin compute up and down independently, so a heavy analytics job never competes with another team, and you pay for compute only while querying. This is why modern ELT — load everything raw, transform later — became affordable.
Old warehouse: storage + compute welded together -> scale one, pay for both.
Cloud warehouse: SEPARATED.
STORAGE columnar files in cheap object storage (S3/GCS), one copy
COMPUTE independent, elastic clusters ("virtual warehouses" / slots)
that read that storage in parallel
Consequences:
- many compute clusters read the same data without copying it
- scale compute up for a big job, down to zero when idle -> pay per use
- "just load it all raw and transform later" (ELT) is now cheap
Snowflake, BigQuery, Redshift (RA3), Databricks SQL all work this way.Warehouses are fast because they combine columnar storage (previous lessons) with massively parallel processing: a query is split across many nodes that each scan a slice of the data at once. Add heavy compression and the ability to skip whole blocks via metadata, and a query can touch billions of rows in seconds. You do not tune indexes like OLTP — you help the engine prune and distribute.
Why a warehouse scans billions of rows in seconds:
COLUMNAR read only the columns the query needs
MPP split the scan across N nodes, each does 1/N of the work
COMPRESSION similar column values pack ~10x smaller -> less I/O
PRUNING skip storage blocks whose min/max can't match the filter
You don't create OLTP-style indexes here. Instead you help PRUNING +
distribution with partitioning / clustering (next).The main performance lever you control is helping the engine read less. Partitioning (BigQuery) or clustering/sort keys (Snowflake, Redshift) physically organize data — usually by date — so a query with a date filter skips everything outside the range. Modeling with a good partition/cluster key on large fact tables is the highest-impact tuning a data engineer does in a warehouse.
-- BigQuery: partition the fact table by day, cluster by common filters
CREATE TABLE fact_sales
PARTITION BY DATE(ordered_at) -- prune to the days queried
CLUSTER BY store_id, product_id -- co-locate common filter values
AS SELECT ... ;
-- Snowflake equivalent: a clustering key
ALTER TABLE fact_sales CLUSTER BY (ordered_at, store_id);
-- A query filtering "WHERE ordered_at >= '2024-01-01'" now scans only
-- those partitions instead of the whole table -> far less cost + time.Because compute is metered (by scanned bytes in BigQuery, by warehouse-seconds in Snowflake), how you model and query is directly a cost decision. Selecting only needed columns, filtering on the partition key, materializing expensive transforms once, and right-sizing compute are all part of the data engineer’s job — performance and cost are the same lever.
In a cloud warehouse, query design == the bill:
SELECT * -> scans every column -> pay for all of them
SELECT col1, col2 ... -> scans two columns -> cheap
no partition filter -> full-table scan
WHERE partition_key ... -> prunes to a slice
Cost-aware habits: select only needed columns, always filter the
partition/cluster key, materialize expensive repeated transforms once
(dbt), and size compute to the job. Perf and cost are one lever.