What is Data Warehouse?
A data warehouse is a centralised analytical database that consolidates data from many source systems into a structure optimised for reporting and analysis — separated from the transactional systems that run the business day to day.
Transactional databases are tuned for fast individual reads and writes (the order, the customer record, the invoice). Analytical workloads need the opposite: scanning millions of rows fast, joining across many dimensions, slicing by time. A data warehouse is the analytical layer — usually built on columnar storage (Synapse, Snowflake, BigQuery, Redshift, Fabric) — that the BI environment reads from. The warehouse decouples the analytical workload from operational systems so reports do not slow down checkout, and operational schema changes do not break reports overnight.
What it includes
- Columnar analytical storage (Synapse, Snowflake, BigQuery, Fabric)
- Staging layer — raw source data, lightly transformed
- Modelling layer — fact and dimension tables, business-defined
- Mart layer — narrow tables built for specific use cases
- Orchestration (dbt, Airflow, Azure Data Factory) for transformations
- Documentation, lineage, and testing on every transformation
How it works
Choose the platform
Match the warehouse to the company’s wider stack. Microsoft-heavy: Fabric or Synapse. Multi-cloud: Snowflake. GCP-native: BigQuery. AWS-native: Redshift or Snowflake-on-AWS.
Land raw data
ELT (not ETL) for most modern stacks. Extract from sources, load into a staging schema, transform inside the warehouse where compute is cheap.
Model dimensionally
Star schema for the business-modelling layer. Slowly Changing Dimensions for customer / product history. Date dimension for every time-based analysis.
Add tests and documentation
dbt tests on every model — uniqueness, nullability, referential integrity. Every column documented before it ships to a dashboard.
Govern access
Row-level security where needed. Service accounts for tools. Audit who reads what. Quarterly review of permissions.
Frequently asked
Data warehouse vs data lake vs lakehouse?
Warehouse: structured, schema-on-write, optimised for SQL. Lake: unstructured, schema-on-read, optimised for raw storage of everything. Lakehouse: hybrid — file storage with table semantics. For most B2B reporting, a warehouse (or lakehouse) is sufficient.
Do we need a warehouse if we have Power BI?
For very small companies — no, Power BI can read directly from source systems. For anyone with more than two source systems and recurring reporting needs — yes, a warehouse pays back its setup cost within months.
On-prem or cloud?
Cloud, almost always. The economics of columnar cloud warehouses (separating storage from compute) make on-prem hard to justify outside of strict data-residency or air-gapped scenarios.