Courses › System Design › Key Technologies

BigQuery & Snowflake

Cloud data warehouses: storage/compute separation, partitioning, ELT, star schemas and lakehouses.

BigQuery and Snowflake (along with Amazon Redshift and Databricks SQL) are cloud data warehouses: managed, columnar SQL engines that let analysts query terabytes to petabytes of historical data without running any servers. In interviews they're where data lands for reporting, business intelligence and offline analysis — the destination of your batch pipelines.

What makes them different from your app database

  • Columnar storage — queries read only the columns they need, heavily compressed. Same idea as ClickHouse.
  • Separation of storage and compute — data lives in cheap object storage; compute clusters spin up independently. Many teams can query the same data without competing for resources, and you pay for compute only while queries run.
  • Massively parallel processing — a query is split across hundreds or thousands of workers.
  • Fully managed — no indexes to tune, no servers to patch; scaling is automatic or a setting.

Designing tables for cost and speed

  • Partition by date so queries over "last 7 days" skip everything else.
  • Cluster (BigQuery) or define clustering keys (Snowflake) on common filters like customer_id, so the engine can skip blocks.
  • Denormalize — wide fact tables with nested fields are normal; joins at warehouse scale are costly.
  • **Avoid SELECT *** — with scan-based pricing, reading every column costs money.
  • Star schema — a large fact table (one row per event or order) joined to small dimension tables (customers, products, dates).

Getting data in

(App databases) -> [CDC: Debezium] -> {Kafka} -> [Loader] -> (Warehouse: raw)
(S3: event logs) -> [Spark / batch ETL] -> (Warehouse: raw)
(Warehouse: raw) -> [dbt / SQL transforms] -> (Warehouse: modeled tables) -> [BI dashboards]
  • ETL vs ELT — modern stacks load raw data first and transform it inside the warehouse with SQL (ELT, often with dbt).
  • Change data capture streams database changes in near real time; batch loads land files from S3 on a schedule.
  • Freshness is usually minutes to hours, which is fine for analytics.

Lakehouses

Data lakehouses (Databricks, or tables in Apache Iceberg/Delta Lake format on S3) blur the line: warehouse-style SQL and transactions over open file formats in object storage, which Spark, Trino and the warehouses can all read.

When to use one

  • Company-wide reporting, BI dashboards and ad-hoc analysis
  • Joining data from many systems (orders, payments, marketing, product events)
  • Training-data preparation and offline ML features

When not to

  • Serving user-facing requests — queries take seconds and cost money per run; serve from an OLTP store, cache or ClickHouse instead
  • Real-time counters with sub-second freshness — use a stream processor like Flink

[!INTERVIEW] Data platform "Operational databases stream changes via CDC into Kafka and land in Snowflake every few minutes; product events go to S3 and load hourly. dbt models build partitioned fact and dimension tables, and BI tools query those. Anything user-facing reads precomputed aggregates exported to a key-value store, never the warehouse directly." See Design an Enterprise Data Platform.