We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningAdvanced · Stream & Batch Processing
A central repository of structured, cleaned data optimized for analytical queries. Snowflake, BigQuery, and Redshift are purpose-built data warehouses.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Data Warehouse across a run of lessons, not one page. These 3 alone are about 100 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Bit arrays that make low-cardinality queries blazing fast, the secret weapon of data warehouses.
What is a lakehouse, and what breaks if a table is just a folder of Parquet files? Warehouse, lake and lakehouse in plain words first. Then a lab on 7,052,769 real NYC taxi trips: while a writer added February as 8 files, a reader that listed the folder saw every half-written state, 9 different row counts; a Delta Lake reader saw only 3,475,226 rows or 7,052,769, never anything between. During an overwrite, the folder reader counted 6,806,334 trips for one month that held 3,331,108. In a real race, 5 runs per case, the folder reader saw a half-written table 7 to 9 times per run and failed 955 to 5,003 reads; the Delta reader saw 0 half-written tables and 0 failures.
Centralized repositories optimized for analytical queries, how companies turn raw data into business decisions.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Bitmap Indexpay once,
yours for life
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Bitmap Index
Bit arrays that make low-cardinality queries blazing fast, the secret weapon of data warehouses
Lakehouse Architecture: What a Folder of Parquet Files Gets Wrong
What is a lakehouse, and what breaks if a table is just a folder of Parquet files? Warehouse, lake and lakehouse in plain words first. Then a lab on 7,052,769 real NYC taxi trips: while a writer added February as 8 files, a reader that listed the folder saw every half-written state, 9 different row counts; a Delta Lake reader saw only 3,475,226 rows or 7,052,769, never anything between. During an overwrite, the folder reader counted 6,806,334 trips for one month that held 3,331,108. In a real race, 5 runs per case, the folder reader saw a half-written table 7 to 9 times per run and failed 955 to 5,003 reads; the Delta reader saw 0 half-written tables and 0 failures.
Data Warehousing
Centralized repositories optimized for analytical queries, how companies turn raw data into business decisions
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.
Backfill
Retroactively populating a new data store, index, or column with historical data. Typically done as a batch job when adding a new feature that needs past data.
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.
This page explains the idea. The "Data Lakes, Warehouses, and Lakehouses for ML" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Data Lakes, Warehouses, and Lakehouses for ML lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Data Warehouse in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
