Design a Data Warehouse (Snowflake/BigQuery)

Problem statement

Design a cloud data warehouse in the style of Snowflake or BigQuery: a system optimized for analytical (OLAP) queries over massive structured and semi-structured data, ingesting from many batch and streaming pipelines, storing data in a compressed columnar format, and serving concurrent SQL to thousands of analysts and BI dashboards.

The defining property of this problem is the workload asymmetry. An OLTP database serves millions of small, random, row-oriented transactions where a single-row lookup must take milliseconds. A warehouse serves comparatively few queries — tens of thousands per day rather than billions — but each query touches enormous slices of data: an aggregation over 90 days of events, a join between a 500-billion-row fact table and a dozen dimensions, a window function over a full year of sessions. The design goal is therefore not transaction throughput; it is scan efficiency, aggregation throughput, and interactive latency over petabyte-scale datasets with thousands of concurrent consumers.

Why the problem is distinctive

Three forces shape every decision in this design. First, analytical access is columnar: a query like 'revenue by region by week' reads 3 columns out of 120, so storing data column-by-column and skipping irrelevant files is worth orders of magnitude of I/O. Second, analytical workloads are bursty and multi-tenant: the finance team's month-end close, the data scientists' feature backfills, and thousands of BI dashboard refreshes all compete for the same compute, so isolation and elasticity are correctness features, not luxuries. Third, data is written in bulk and read many times: a warehouse is append-and-rewrite, not update-in-place, which permits immutable file layouts, snapshot isolation, time travel, and zero-copy cloning.

Modern systems prove the category is real and large. Snowflake's S-1 and subsequent filings reported 4,577 customers as of July 2020 with roughly 158% net revenue retention, and FY2025 product revenue above $3.6B with 10,000+ customers. Google's BigQuery, built on the Dremel engine described in Melnik et al. (VLDB 2010), runs serverless over Colossus with slot-based capacity. Amazon Redshift, launched at re:Invent in November 2012, separated storage from compute with RA3 managed-storage nodes in 2019 and added AQUA cache pushdown in 2020. The design below is our own fictional system, but it must stand on the same architectural shoulders.

The four architectural planes

1. Ingestion plane: batch loaders, streaming insert APIs, and CDC appliers that land raw and transformed data into staging and curated tables.
2. Storage plane: an immutable, compressed, columnar file layout in object storage, with micro-partitions, zone maps, and a transactional metadata store that owns ACID, snapshots, and time travel.
3. Compute plane: elastic, stateless virtual warehouses or serverless slot pools that read cached and remote data, execute vectorized MPP pipelines, and spill gracefully.
4. Services plane: the always-on control layer — authentication, RBAC, the optimizer, transaction and metadata management, workload governance, cost metering, and audit.

A strong interview answer keeps these planes separate. Compute can scale to zero and restart without touching data; a region failure degrades query capacity while storage durability is unaffected; a bad pipeline poisons a table version, not the whole warehouse, because metadata snapshots make every load reversible.

Public baseline versus design assumptions

Public evidence anchors the category: Snowflake's SIGMOD 2016 paper describes micro-partitions of 50-500 MB of uncompressed data per file set, an always-on services layer, and ephemeral compute; Google's Dremel paper describes columnar nested storage and tree-shaped execution over tens of billions of rows; AWS documents RA3 nodes with managed storage and AQUA acceleration. Everything else in this answer — fleet sizes, daily terabytes, analyst counts, SLOs — is an explicit design assumption stated where it is used.