Data Warehouse
A central repository of structured, cleaned data optimized for analytical queries. Snowflake, BigQuery, and Redshift are purpose-built data warehouses.
What is Data Warehouse?
In short
A data warehouse is a central database built to store large volumes of structured, cleaned historical data from many source systems and run fast analytical queries over it. Unlike an operational database that handles day to day transactions, a warehouse is tuned for reporting and analysis across billions of rows, and is the engine behind tools like Snowflake, Google BigQuery, and Amazon Redshift.
What a data warehouse actually is
A data warehouse is a database designed for one job: answering analytical questions over huge amounts of historical data. Questions like "what was revenue per region per month for the last three years" or "which 100 customers drove the most usage last quarter." These queries scan millions or billions of rows, and a normal application database would choke on them.
The data inside a warehouse comes from other systems. Your orders live in a Postgres database, your web events live in event logs, your support tickets live in Zendesk, your ad spend lives in Google and Facebook. A warehouse pulls all of that into one place, cleans it, and reshapes it into a consistent model so analysts can join across sources without going to five different teams.
The key word is analytical. A warehouse is OLAP (online analytical processing), not OLTP (online transaction processing). It is optimized for reading and aggregating large slices of data, not for inserting a single order or updating one user's password thousands of times per second.
How it works under the hood
Most modern warehouses store data in a columnar format. Instead of writing all the fields of a row together, they store each column separately. So all 500 million order amounts sit in one contiguous block. When you run SUM(amount), the engine reads just that one column and skips everything else, which cuts the data scanned by an order of magnitude versus a row store.
Columnar layout also compresses extremely well, because values in a single column are similar. A column of country codes with 200 distinct values compresses to a fraction of its raw size. Warehouses lean on this hard. BigQuery and Snowflake routinely compress raw data 5 to 10 times.
Data gets in through ETL or ELT pipelines. ETL transforms data before loading it; ELT loads raw data first and transforms it inside the warehouse with SQL, which is the common pattern today using tools like dbt. Modern cloud warehouses also separate storage from compute: the data sits in object storage like S3, and you spin up compute clusters on demand to query it, so storage and query power scale independently.
Queries are usually written in SQL. The engine parallelizes a query across many nodes, each scanning a slice of the columns, then merges the partial results. This massively parallel processing (MPP) design is why a warehouse can aggregate a billion rows in seconds.
When to use one and the trade-offs
Reach for a data warehouse when you need to analyze data across multiple sources, run heavy aggregations, or power dashboards and business intelligence tools like Tableau, Looker, or Metabase. It is the right home for reporting, financial analysis, product analytics, and anything that asks "what happened" over a long time window.
Do not use it as your application's primary database. Warehouses are slow for single-row lookups and point updates, and many do not enforce row-level constraints the way an OLTP database does. If a user clicks a button and needs an instant response, that query belongs in Postgres or DynamoDB, not the warehouse.
The main trade-off is freshness versus cost and scale. Data lands in the warehouse on a schedule, so it is usually minutes to hours behind reality, not real time. You also pay for compute per query or per cluster-hour, so a careless analyst running full table scans all day can run up a large bill. A data lake stores raw unstructured files cheaply but offers weaker query performance and governance, while a warehouse trades some storage flexibility for fast, structured SQL.
A concrete example
Imagine an e-commerce company. Orders are written to a Postgres database, clickstream events stream into Kafka, and marketing spend sits in a SaaS tool. None of these can answer "what is our return on ad spend per channel per week."
An ELT pipeline (Fivetran or Airbyte to extract, dbt to transform) loads raw orders, events, and spend into Snowflake every hour. dbt models join them into clean tables: a fct_orders fact table, a dim_customer dimension, a daily ad_spend table. This star schema, with one central fact table surrounded by dimension tables, is the classic warehouse model.
Now an analyst writes one SQL query joining orders to ad spend by channel and week. Snowflake scans only the columns it needs across compressed columnar storage, runs in parallel across its compute warehouse, and returns the answer in a few seconds over hundreds of millions of rows. A Looker dashboard sits on top, refreshing every morning so leadership sees yesterday's numbers without anyone touching the source systems.
Where it is used in production
Snowflake
Cloud data warehouse that fully separates storage from compute, letting teams resize query clusters independently and pay per second of compute used.
Google BigQuery
Serverless warehouse where you run SQL with no clusters to manage and pay per terabyte scanned; powers analytics at companies like Spotify and Twitter.
Amazon Redshift
AWS columnar MPP warehouse that loads data from S3 and runs parallel SQL across nodes for dashboards and reporting.
Airbnb
Runs a large internal warehouse on top of data pipelines so analysts and data scientists can query bookings, pricing, and host metrics with SQL.
Frequently asked questions
- What is the difference between a data warehouse and a database?
- A regular (OLTP) database handles many small reads and writes for an application, like creating an order or updating a profile. A data warehouse (OLAP) is built to scan and aggregate huge amounts of historical data for analysis and reporting. Warehouses store data in columns for fast aggregation, while transactional databases store data in rows for fast single-record access.
- What is the difference between a data warehouse and a data lake?
- A data lake stores raw, often unstructured files (JSON, logs, images) cheaply in object storage with little upfront modeling. A data warehouse stores structured, cleaned data in a defined schema tuned for fast SQL queries. Lakes are flexible and cheap to store; warehouses are faster and more governed to query. Many companies use both, with a lake feeding the warehouse.
- What is ETL versus ELT in a data warehouse?
- ETL means Extract, Transform, then Load: you clean and reshape data before it lands in the warehouse. ELT means Extract, Load, then Transform: you load raw data first, then transform it inside the warehouse using SQL. ELT is the common modern pattern because cloud warehouses are powerful enough to do the transformation themselves, often using dbt.
- Why are data warehouses columnar?
- Analytical queries usually touch a few columns across many rows, like summing one amount column over a billion records. Storing each column separately lets the engine read only the columns it needs and skip the rest, cutting data scanned dramatically. Columnar storage also compresses far better because values within one column are similar.
- Is a data warehouse real time?
- Usually not. Data lands on a schedule through batch pipelines, so the warehouse is typically minutes to hours behind the source systems. For real-time needs, teams add streaming pipelines (Kafka, materialized views, or tools like Materialize) or query the operational database directly, rather than relying on the warehouse.
Learn Data Warehouse hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
Related lessons
Lessons that touch on Data Warehouse as part of a larger topic.
Bitmap Index
Bit arrays that make low-cardinality queries blazing fast, the secret weapon of data warehouses
intermediate · database types storage
Data Warehousing
Centralized repositories optimized for analytical queries, how companies turn raw data into business decisions
intermediate · database types storage
Data Lakes, Warehouses, and Lakehouses for ML
Data warehouse vs data lake vs lakehouse, explained for ML. Learn schema-on-write vs schema-on-read, open table formats like Delta and Iceberg, time travel, and why ML needs both cheap raw storage and fast structured access.
ml-intermediate · data engineering for ml
See also
Related glossary terms you might want to look up next.
Data Lake
A centralized repository that stores raw data at any scale in its native format. Unlike a data warehouse, data doesn't need to be structured or cleaned before loading.
ETL
Extract, Transform, Load: a pipeline that extracts data from sources, transforms it into the desired format, and loads it into a destination like a data warehouse.
Batch Processing
Processing large volumes of data in scheduled chunks rather than in real time. Think nightly reports, ETL jobs, and data warehouse loads.
Stream Processing
Processing data continuously as it arrives, rather than in batches. Powers real-time analytics, fraud detection, and live dashboards.
Exactly-Once Processing
A processing guarantee where each message is processed exactly one time, even in the face of failures. Achieved through idempotent consumers and transactional producers.
Checkpointing
Periodically saving the state of a stream processing job so it can recover from failures without reprocessing everything from the beginning. Flink and Spark use distributed checkpoints.