Warehouse Query Engines

Separation of storage and compute

Modern warehouses decouple:

• Storage — columnar files on object store (S3, GCS, Colossus)
• Compute — elastic clusters that spin up per query

You pay for storage continuously; compute only when queries run (ideally).

BigQuery (Dremel lineage)

• Colossus — distributed storage
• Dremel — execution engine with columnar scan and tree architecture
• Slots — compute units; autoscale or reservation

Partitioning + clustering prune bytes scanned — billed on data read.

Snowflake

Three layers:

1. Database storage — micro-partitions (columnar, compressed)
2. Compute — virtual warehouses (independent scale)
3. Cloud services — metadata, optimizer, auth

Multi-cluster warehouses — auto-scale compute for concurrency.

Databricks SQL / Photon

• Delta Lake on object storage
• Photon — vectorized C++ engine for SQL
• SQL warehouses — serverless or provisioned

Unity Catalog unifies governance across Spark and SQL.

Why dbt models are slow

Cause	Fix
Full table scan	Incremental + partition filter
No clustering	Cluster on filter columns
Cartesian join	Fix grain in intermediate
Tiny warehouse	Right-size or auto-scale
JSON shredding	Pre-parse in silver Spark

Link to Databases (columnar/search) for file format theory — here: query execution economics.

Interview answer template

"dbt mart scans 10TB because where order_date is missing — partition prune fails. Add partition column, switch to incremental merge, cluster on customer_id for join queries, and use a larger warehouse only for the mart build — not interactive default."