Data Warehouse

Updated September 30, 20262 min read

In short

A data warehouse is a central database built for analytics that collects historical data from many sources so teams can run large reporting queries quickly.

What is a data warehouse?

A data warehouse is a database built for analyzing data rather than for running an application. It gathers information from many sources, such as the production database, payment systems, and web analytics, and stores it in one place with a consistent structure. Analysts and business intelligence tools then query it to answer questions like how revenue changed month by month.

Data usually arrives through pipelines called ETL (extract, transform, load) or ELT, where raw data is loaded first and transformed inside the warehouse. Most warehouses use columnar storage, which keeps each column's values together, so a query that sums one column across millions of rows reads only the data it needs. Tables are often organized in a star schema, with a central fact table of events, such as sales, surrounded by dimension tables that describe them, such as customers and products.

Think of a data warehouse as a company's archive and reading room: the busy shop floor keeps working, while copies of every receipt are organized in a separate room where people can study trends without getting in anyone's way. Keeping analytics separate also protects the production database from slow, heavy queries.

A data warehouse is often confused with a regular operational database and with a data lake. An operational database handles many small, fast reads and writes for an application, a style called OLTP (online transaction processing), while a warehouse handles fewer but much larger analytical queries, called OLAP (online analytical processing). A data lake stores raw files in any format cheaply, while a warehouse stores cleaned, structured data that is ready to query.

Key takeaways

A data warehouse is optimized for analytics, not for running an application.
It combines data from many sources into one consistent structure.
Data is loaded through ETL or ELT pipelines.
Columnar storage makes large aggregate queries fast.
OLTP databases run apps, while OLAP warehouses answer business questions.

Example

An analytical query on a star schemasql

-- Monthly revenue per product category in 2026
SELECT
  d.month,
  p.category,
  SUM(f.amount) AS revenue
FROM fact_sales AS f
JOIN dim_date    AS d ON f.date_id = d.id
JOIN dim_product AS p ON f.product_id = p.id
WHERE d.year = 2026
GROUP BY d.month, p.category
ORDER BY d.month, revenue DESC;

Readers ask

What is the difference between a data warehouse and a database?

A data warehouse is a type of database, but it is designed for large analytical queries over historical data. A regular application database is designed for many small, fast reads and writes that keep an app running.

What is the difference between a data warehouse and a data lake?

A data lake stores raw data of any kind, such as logs, images, and JSON files, usually in cheap object storage. A data warehouse stores cleaned, structured data with a defined schema, so it is easier and faster to query.

What is ETL?

ETL stands for extract, transform, load: data is pulled from source systems, cleaned and reshaped, and then loaded into the warehouse. In ELT, the raw data is loaded first and transformed inside the warehouse.

Often compared

Data Warehouse vs Data LakeA data warehouse stores cleaned, structured data in a fixed schema for fast queries, while a data lake keeps raw data of any type and adds structure on read.