Data Warehouse
Also: DWH, Enterprise Data Warehouse, EDW
A central repository that consolidates data from many source systems into a structured, query-optimized store designed for analytics, reporting, and business intelligence.
What it is
A data warehouse is a central, integrated repository built to store large volumes of historical and current data from multiple source systems for analysis and reporting. Unlike an operational database (OLTP) tuned for fast inserts and updates, a data warehouse is optimized for analytical queries (OLAP): scanning, aggregating, and joining large datasets to answer business questions.
Data is loaded through ETL (Extract, Transform, Load) or ELT pipelines that clean, standardize, and reshape raw inputs into consistent, query-ready tables. The result is a single, trusted source of truth across the organization.
Why it matters
- Single source of truth: Marketing, finance, and operations report from the same consistent numbers.
- Performance: Columnar storage and structured models make aggregations over millions of rows fast.
- Historical analysis: It retains time-stamped snapshots, enabling trend and year-over-year analysis.
- Governance: Centralized access control, data quality rules, and lineage support compliance.
How it is used in practice
Most warehouses organize data with dimensional modeling (star or snowflake schemas):
- Fact tables hold measurable events (sales, clicks, transactions).
- Dimension tables hold descriptive context (customer, product, date, region).
Analysts query the warehouse with SQL, and BI tools (dashboards) sit on top. Data engineers manage pipelines, scheduling, and modeling layers. Modern cloud warehouses separate storage from compute, letting teams scale query power independently and pay per use.
Concrete example
A retailer collects orders from an e-commerce platform, in-store point-of-sale systems, and a CRM. Each night an ELT pipeline loads these sources into the warehouse. A `fact_sales` table records every line item, linked to `dim_customer`, `dim_product`, `dim_store`, and `dim_date`.
A finance analyst then runs a single query to compute monthly revenue by region, while a marketing analyst measures campaign-driven sales, both from the same governed data. Without the warehouse, each team would pull conflicting numbers from isolated systems.
Related concepts
A data warehouse differs from a data lake (raw, schema-on-read storage) and a data mart (a smaller, department-focused subset). Many organizations combine these into a lakehouse architecture.
Frequently asked questions
What is a data warehouse in simple terms?
A data warehouse is a central repository that consolidates data from multiple source systems into a structured store optimized for analysis and reporting. It holds both current and historical data, and it is tuned for analytical queries (OLAP) such as scanning, aggregating, and joining large tables, rather than for the fast inserts and updates an operational database (OLTP) handles.
What is the difference between a data warehouse, a data lake and a data mart?
A data warehouse stores cleaned, modeled data ready to query; a data lake stores raw data with schema-on-read, so structure is applied at query time; a data mart is a smaller subset of a warehouse scoped to one department. Many organizations combine warehouse and lake capabilities into a lakehouse architecture.
Why do teams need a warehouse if each system already has its own reports?
Because isolated systems produce conflicting numbers. A warehouse gives marketing, finance, and operations the same governed figures, keeps time-stamped history for trend and year-over-year analysis, and centralizes access control, data quality rules, and lineage. That combination is what makes it a single source of truth rather than one more reporting tool.
What is the difference between ETL and ELT when loading a warehouse?
ETL transforms data before loading it into the warehouse; ELT loads raw data first and transforms it inside the warehouse using its own compute. Both pipelines do the same job, cleaning, standardizing, and reshaping raw inputs into query-ready tables, but ELT is common on modern cloud warehouses where storage and compute scale separately and you pay per use.
How are fact and dimension tables organized in a star schema?
In dimensional modeling, fact tables hold measurable events (sales, clicks, transactions) and dimension tables hold the descriptive context around them (customer, product, store, date). A retailer might have a fact_sales table recording every line item, linked to dim_customer, dim_product, dim_store, and dim_date, which lets finance compute monthly revenue by region and marketing measure campaign-driven sales from the same tables.