Data Warehouse Explained: Concepts and Architecture

A data warehouse is a database built for analysis rather than for running the business day to day. It gathers data from many operational systems, cleans and integrates it, keeps its history, and organises it so questions such as "how did margin by region change over three years?" are quick to ask and give the same answer to everyone. This guide explains why warehouses exist, how they are structured, and the core ideas that make them work.

Why analytics needs its own store

Operational databases, known as OLTP systems, record business events as they happen: an order placed, a payment received. Microsoft's architecture guide describes them as highly normalised and optimised for heavy writes (Microsoft Learn: OLTP). That design is right for their job and wrong for analytics, for three reasons the same guide lists:

• Heavy aggregations hurt the application. Summing millions of transactions is resource-intensive and can slow or block the transactions the business depends on.
• Normalised data makes analytical queries complex, with many joins that business users cannot write unaided.
• History does not belong there. Keeping every transaction forever slows operational queries, so history is commonly moved to a data mart or warehouse.

Analytical stores are the opposite: read-heavy, cleansed, holding history for trend analysis, and able to run complex queries without disturbing transactional systems (Microsoft Learn: OLAP). OLTP vs OLAP shows the difference on a real database.

The layers of a warehouse

Most warehouses, whatever the platform, move data through the same layers:

1. Sources: application databases, SaaS tools, files, event streams.
2. Ingestion: batch loads or change data capture bring data in, usually with ELT, loading first and transforming inside the platform.
3. Raw or staging layer: data exactly as received, kept so everything downstream can be rebuilt.
4. Cleaned and integrated layer: types fixed, duplicates removed, customers from different systems matched to one identity.
5. Modelled layer: fact and dimension tables shaped for analysis, often grouped into subject-area data marts.
6. Semantic layer: business definitions of measures, friendly names, relationships and security, which BI tools use so everyone calculates revenue the same way.

Lakehouse platforms often name layers 3 to 5 bronze, silver and gold; medallion architecture covers that convention.

Core ideas

• Facts and dimensions. Facts are the measurements of a business process, such as quantities and amounts; dimensions are the context used to filter and group them, such as date, customer and product. Together they form a star schema.
• Grain. Every fact table has a declared grain, exactly what one row represents, such as one order line. Mixing grains is the classic source of double counting.
• History. Warehouses keep the past. When a customer moves region, a slowly changing dimension decides whether old sales stay with the old region; slowly changing dimensions covers the options.
• Conformed dimensions. Shared dimensions, one date table and one customer table used by every fact, let different processes be compared on the same terms.
• Integration. The warehouse is where "customer" is defined once, even when five systems each have their own version.

The Kimball Group's catalogue of dimensional modelling techniques is the standard reference for these patterns (Kimball Group: techniques).

Modelling approaches

Three families of design are common. Dimensional modelling builds star schemas directly for analysis and is the most widely used. A normalised enterprise model integrates data in a relational core first and builds dimensional marts from it. Data vault separates business keys, relationships and descriptive history into hubs, links and satellites, favouring auditability and change. Many warehouses combine them, for example a data vault core with star-schema marts on top; data modelling: star, snowflake and data vault compares them.

Platforms today

Traditional warehouses ran on a single large server. Modern cloud warehouses, such as Snowflake, BigQuery, Redshift and Fabric, spread queries across many machines and separate storage from compute, so capacity scales with demand; Snowflake explained looks at one closely. Lakehouses bring warehouse features, transactions and schema control, to open files in a data lake. The concepts above apply to all of them.

Where warehouses go wrong

• Building everything before answering anything. Start from real questions and deliver a first subject area quickly.
• Unclear ownership of definitions, so revenue means three things; the semantic layer and a data owner fix this.
• Copying source tables unchanged and calling it a warehouse, which moves all the joining and cleaning into every report.
• No tests, so broken loads are discovered by executives.
• Ignoring cost: on cloud platforms, unbounded queries and forgotten schedules spend real money, so monitor usage from the first week.
• Stale data: analytical stores refresh more slowly than operational systems, so agree freshness expectations explicitly.

To see the layers built for real, follow how to build a data warehouse.

Data warehouse concepts, star and snowflake schemas, OLTP versus OLAP, data modelling and data lakes are part of the data warehousing module of the Data Engineering course.

Sources

• Online transaction processing (OLTP) (Azure Architecture Center, Microsoft Learn) — Microsoft Learn
• Online analytical processing (OLAP) (Azure Architecture Center, Microsoft Learn) — Microsoft Learn
• Dimensional modeling techniques (Kimball Group) — Kimball Group