Data warehouse

A data warehouse is a central database built for analysis rather than for running day-to-day transactions. Data is extracted from operational systems, cleaned, integrated and loaded into well-defined tables, so that analysts can query consistent figures across the whole business.

It is the oldest and most proven analytics architecture, and still the backbone of most business reporting.

Diagram

flowchart LR
    crm[CRM] --> etl[ETL / ELT pipelines]
    erp[ERP] --> etl
    app[(App database)] --> etl
    etl --> stage[(Staging)]
    stage --> dw[(Data warehouse<br/>star schemas)]
    dw --> mart1[(Finance mart)]
    dw --> mart2[(Sales mart)]
    mart1 & mart2 --> bi[BI dashboards and reports]

How it works

Schema on write. Data is shaped to a defined schema before it’s stored, so everything in the warehouse is structured and validated.
ETL or ELT. Pipelines extract data from source systems and transform it either before loading (ETL) or inside the warehouse after loading (ELT, common with modern cloud warehouses).
Dimensional modeling. Data is typically organized into fact tables (measurable events such as sales) and dimension tables (context such as customer, product, date), forming star schemas.
Data marts. Subsets of the warehouse are tailored for individual departments.
Columnar storage. Modern warehouses store data by column, which makes large aggregate queries fast.

Pros

One version of the truth: consistent, reconciled numbers across departments.
Fast analytical queries: storage and indexing are optimized for aggregation.
High data quality: validation happens on the way in.
Mature ecosystem: BI tools, skills and modeling practices are well established.

Cons

Rigid: new sources or questions often need schema changes and new pipelines first.
Structured data only: poor fit for logs, documents, images or raw event streams.
Central bottleneck: one data team owning every pipeline can become slow to respond.
Cost at scale: storing everything in premium warehouse storage gets expensive.

When to use it

Business intelligence, financial reporting and dashboards built on well-understood data.
Regulated reporting where figures must be reconciled and auditable.
Organizations whose analytics questions are mostly known in advance.

When to avoid it

Large volumes of raw or unstructured data kept for data science or machine learning. Consider a data lake or lakehouse.
Exploratory work where the questions aren’t known yet.

Common pitfalls

Modeling source systems instead of business processes. Copying operational tables one-to-one produces a warehouse nobody can query easily.
No ownership of definitions. If “active customer” is defined differently in three reports, the warehouse has failed at its main job.
Reporting directly on staging data, which bypasses the cleaning the warehouse exists to provide.

Data lake
Lakehouse
Data mesh
Change data capture