Data Warehouse

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.