Data Warehouse

Definition

A data warehouse is a centralized repository that stores structured, cleaned, and integrated data from multiple source systems, organized specifically to support business intelligence, reporting, and OLAP-style analytical queries.

Overview

Unlike an operational OLTP database, which is optimized to serve a single application's live transactions, a data warehouse consolidates data pulled from many different source systems — a company's order database, its CRM, its marketing platform — into one place, reshaped into a consistent, analysis-friendly schema. This consolidation is what lets an analyst ask cross-cutting questions ("how did marketing spend correlate with sales last quarter?") that no single source system could answer on its own. Data typically arrives in a warehouse through ETL pipelines (transforming data before loading it) or, increasingly, ELT pipelines (loading raw data first and transforming it inside the warehouse using its own compute), with tools like dbt and Apache Airflow commonly orchestrating these transformations. Once loaded, data is usually organized using dimensional modeling patterns like the star schema or snowflake schema, which structure data around business-meaningful facts (sales, orders) and dimensions (time, product, customer) to make common analytical queries fast and intuitive. Modern cloud data warehouses — Snowflake, BigQuery, and Amazon Redshift — separate storage from compute and use columnar storage engines under the hood, letting organizations scale analytical query capacity independently of how much historical data they retain. This distinguishes a data warehouse from a data lake, which stores raw, often unstructured or semi-structured data without imposing a schema upfront, and from a data lakehouse, which attempts to combine the flexibility of a lake with the structure and performance guarantees of a warehouse. Designing and populating a data warehouse is a core responsibility of data engineering teams, and the skills involved — dimensional modeling, pipeline orchestration, and warehouse SQL — are covered together in courses like dbt & Analytics Engineering.

Key Concepts

• Centralizes structured data from multiple source systems into one analysis-ready store
• Populated via ETL or ELT pipelines, often orchestrated by tools like Airflow or dbt
• Organized using dimensional modeling (star or snowflake schema)
• Optimized for OLAP-style analytical and reporting queries, not live transactions
• Modern cloud warehouses separate storage from compute for independent scaling
• Columnar storage engines accelerate large-scale aggregation queries
• Distinct from a data lake (raw, schema-less) and a data lakehouse (hybrid)

Use Cases

Frequently Asked Questions

Related in Database