intermediate9 min readUpdated 2026-06-08

Data Warehouses

Data warehouses are centralized, schema-on-write analytical databases optimized for complex queries across large volumes of structured, historical data,.

Quick answer: Data Warehouses

Data Warehouses is covered here as a practical system design concept. Data warehouses are centralized, schema-on-write analytical databases optimized for complex queries across large volumes of structured, historical data,. Use it to understand the Databases idea, where it appears in production systems, and how to discuss it clearly in interviews.

Focus on the problem being solved, the main design choice, the failure mode, and the tradeoff you would defend in front of a senior engineer. This page is part of the DosiBridge system design knowledge graph, so it links related concepts, interview problems, tradeoffs, and references instead of leaving the topic isolated.

Aspect	Details
What it is	A centralized analytical database using columnar storage and MPP to execute complex queries across terabytes of structured, historical data for business intelligence
When to use	Business intelligence dashboards, executive reporting, historical trend analysis, cross-department analytics, and any workload requiring complex aggregations and joins across structured data
When NOT to use	Transactional workloads (OLTP), real-time operational queries, storing unstructured data (images, logs), or when data volumes are small enough for a regular PostgreSQL instance
Real-world example	Airbnb uses Snowflake as their primary data warehouse, enabling thousands of employees to run analytical queries across booking, pricing, and host data without impacting operational databases
Interview tip	Clearly distinguish OLTP vs OLAP — operational databases handle many small read/write transactions; data warehouses handle few complex analytical queries scanning massive datasets
Common mistake	Running analytical queries directly against production OLTP databases instead of a warehouse — heavy analytical queries compete with transactional workloads and degrade application performance
Key tradeoff	Data warehouses provide fast analytical query performance and governance but require structured schemas, ETL pipelines, and higher per-GB storage costs compared to data lakes

Why This Matters

Data warehouses exist because operational databases are terrible at analytics. A query like "what was our revenue by product category by quarter for the last 3 years, broken down by region" requires scanning millions of rows with complex joins and aggregations — this would cripple a transactional database serving live user requests. Data warehouses solve this with columnar storage (reads only the columns needed), MPP (distributes query execution across many nodes), and workload isolation (analytics don't affect operations). Understanding data warehouses vs data lakes vs operational databases is a core architectural skill that comes up in nearly every system design discussion involving analytics.

System architecture diagram for Data Warehouses showing how services, databases, and caches connect — System architecture for Data Warehouses

The Building Blocks

Columnar Storage: Data is stored column-by-column rather than row-by-row, enabling queries to read only needed columns. A query selecting 3 columns from a 100-column table reads 97% less data.
Massively Parallel Processing: Queries are broken into fragments and executed simultaneously across many compute nodes, enabling a single query to scan terabytes in seconds by distributing the work.
Star & Snowflake Schema: Data is modeled as fact tables (events/transactions) surrounded by dimension tables (categories, dates, locations) — optimized for analytical joins and aggregations.
Separation of Storage and Compute: Modern warehouses (Snowflake, BigQuery) decouple storage from compute, allowing independent scaling — store petabytes cheaply and spin up compute only when queries run.
Workload Isolation: Warehouses provide separate compute clusters (virtual warehouses) for different teams and workloads, ensuring a heavy data science query doesn't slow down the executive dashboard.

Under the Hood

Data warehouses use fundamentally different storage and execution strategies than transactional databases. Data is stored in columnar format — all values for a single column are stored contiguously on disk. This is optimal for analytical queries that typically access a few columns from tables with hundreds of columns. Columnar storage also enables excellent compression because values in the same column tend to be similar (e.g., a status column with 5 possible values compresses to nearly nothing with dictionary or run-length encoding).

Step-by-step diagram showing how Data Warehouses processes a request from start to finish — How Data Warehouses works step by step

Query execution uses MPP (Massively Parallel Processing). When a query arrives, the optimizer creates an execution plan and distributes fragments across compute nodes. Each node processes its slice of data in parallel — scanning, filtering, and aggregating locally — then the coordinator merges partial results. For a 10TB table scan distributed across 100 nodes, each node scans only 100GB, completing in seconds what would take minutes on a single machine.

Modern cloud warehouses separate storage from compute entirely. Snowflake stores all data in S3/Azure Blob/GCS and spins up ephemeral compute clusters (virtual warehouses) to execute queries. This means you pay for storage continuously (cheap) and compute only when queries run (expensive but elastic). BigQuery goes further with a serverless model — you submit a query and Google provisions exactly the compute needed, billing per byte scanned. This separation enables features like zero-copy cloning (create a copy of a table instantly without duplicating data), time travel (query historical snapshots), and concurrency scaling (automatically adding compute nodes during peak periods).

How Companies Actually Do This

Airbnb Uses Snowflake as their primary data warehouse, processing petabytes of booking, search, and pricing data to power analytics for thousands of employees — from executive dashboards to data science experiments.

Comparison table for Data Warehouses contrasting approaches, tradeoffs, and when to use each — Comparing key aspects of Data Warehouses

Spotify Uses Google BigQuery to analyze petabytes of listening data, enabling features like Spotify Wrapped (which processes an entire year of listening history for 500+ million users in a single batch computation).

Walmart Operates one of the largest Teradata data warehouses to analyze point-of-sale data from 10,500+ stores, optimizing inventory, supply chain decisions, and pricing across millions of SKUs.

Common Pitfalls

Modeling data like an OLTP system — using normalized schemas with many small tables and foreign keys; warehouses perform best with denormalized star schemas optimized for analytical joins
Not partitioning large tables — without partitioning by date or other common filter columns, every query scans the full table even when only recent data is needed, wasting compute and money
Running warehouses as always-on compute — in modern pay-per-query or elastic warehouses (Snowflake, BigQuery), leaving compute running 24/7 wastes money; auto-suspend idle clusters and use serverless where possible

Data flow diagram for Data Warehouses showing how requests and responses move through the system — Data flow through Data Warehouses

Interview Questions Worth Practicing

How would you design the data warehouse schema for an e-commerce company that needs to analyze orders, products, customers, and marketing campaigns?
Explain the difference between OLTP and OLAP databases. Why can't you use one database for both workloads?
Compare Snowflake, BigQuery, and Redshift — what are the key architectural differences and when would you choose each?

The Tradeoffs

Structure vs Flexibility: Warehouses enforce schema-on-write for clean, governed data, but adding new data sources requires schema design and ETL pipeline development upfront
Cost vs Performance: Columnar storage and MPP deliver fast analytical queries but cloud warehouses charge per-query or per-compute-hour, which can be expensive for heavy workloads
Centralization vs Autonomy: A centralized warehouse provides a single source of truth but can become a bottleneck if one team's ETL pipeline or governance policy blocks another team's access to data

Component diagram for Data Warehouses showing each building block and its responsibility — Key components of Data Warehouses

How to Explain This in an Interview

Here is how I would explain Data Warehouses in a system design interview:

Start by contrasting OLTP (operational databases handling many small transactions — fast writes, row-based storage) with OLAP (analytical databases handling few complex queries scanning millions of rows — columnar storage, MPP). Explain columnar storage: storing data by column instead of by row means a query selecting 3 columns from a 100-column table reads 97% less data, and similar values compress extremely well. Describe MPP: queries are split into fragments distributed across compute nodes that process data in parallel — a 10TB scan across 100 nodes means each scans only 100GB. Mention the separation of storage and compute in modern warehouses: Snowflake stores data in S3 and spins up ephemeral compute clusters, so you pay for storage always and compute only when needed. Discuss schema design: star schemas with fact tables (events) and dimension tables (categories) are the standard pattern. Close with the data warehouse vs data lake distinction: warehouses are for structured analytics with governance, lakes are for raw data storage with flexibility, and lakehouses aim to combine both.

Interview preparation checklist for Data Warehouses with key points to mention and mistakes to avoid — Interview tips for Data Warehouses

Original DosiBridge notes for Data Warehouses

This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study Data Warehouses is to connect the concept to a production service where growth exposes the first weak assumption in the original design. That framing changes the design discussion from definitions to operating choices.

What makes this topic distinct

Query Shape: For Data Warehouses, decide the query shape before choosing tools; otherwise the design looks clean but fails when data changes.
Write Amplification: For Data Warehouses, decide the write amplification before choosing tools; otherwise the design looks clean but fails when warehouses changes.
Replication Lag: For Data Warehouses, decide the replication lag before choosing tools; otherwise the design looks clean but fails when data changes.
Migration Safety: For Data Warehouses, decide the migration safety before choosing tools; otherwise the design looks clean but fails when warehouses changes.
Data Ownership: For Data Warehouses, decide the data ownership before choosing tools; otherwise the design looks clean but fails when data changes.

Production review question

If a team adopts Data Warehouses, what observable signal proves it helped instead of only making the architecture diagram more fashionable?

Practical metric to track

Track data-warehouses health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For Data Warehouses, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.