Data Warehousing Concepts
Topic 2 of 12 · In Business Intelligence · September 9, 2026
Master the foundations of data warehousing: dimensions vs facts, star and snowflake schemas, slowly changing dimensions, and the Kimball vs Inmon debate.
Overview
A data warehouse is a central, read-only repository that stores historical data from across an organization, structured specifically for analysis and reporting. Unlike operational databases that handle day-to-day transactions, a warehouse is optimized for querying large volumes of historical data — answering questions like "How did revenue change over the last five years across all regions?"
Data warehousing is the backbone of every serious BI system. Without a warehouse, dashboards query live operational databases — slow, risky and unable to handle complex cross-source analysis. This topic teaches you the conceptual foundations: how data is organized (dimensions and facts), how schemas are designed (star vs snowflake), how historical changes are tracked (slowly changing dimensions), and the two major design philosophies (Kimball vs Inmon). These concepts directly feed into your ETL and Power BI modeling work in the next topics.
Before You Start
- Keep the star schema you drew for the fundamentals mini project (fact_sales plus dimensions) open to compare against the schemas in this topic
- Open dbdiagram.io or get pen and paper ready to draw your own star and snowflake schemas as you work
- Reopen your e-commerce sales dataset with order, product and geography columns so example data matches the exercises
- Have the restated fact_sales CREATE TABLE DDL from the fundamentals topic on screen to reuse while designing tables
- Revise the four warehouse characteristics (subject-oriented, integrated, non-volatile, time-variant) enough to state them from memory before reading
Learning Objectives
- Define what a data warehouse is and how it differs from an operational database
- Explain the purpose and structure of a data warehouse
- Distinguish between fact tables and dimension tables
- Design a star schema and explain why it is the preferred BI modeling pattern
- Compare star schema with snowflake schema and understand trade-offs
- Implement the three types of slowly changing dimensions (SCD Type 1, 2 and 3)
- Define data marts and explain their relationship to the warehouse
- Compare the Kimball and Inmon approaches to warehouse design
- Understand the difference between a data warehouse and a data lake
Prerequisites
Continue from Business Intelligence Fundamentals for context on the BI system. No database experience required — warehousing concepts are taught from scratch.
What Is a Data Warehouse?
A data warehouse (DW or DWH) is a system that pulls together data from many different sources within an organization for reporting and analysis. The key characteristics, defined by Bill Inmon (the "father of data warehousing"), are:
- Subject-oriented — organized around business subjects (customers, products, orders) not applications
- Integrated — combines data from multiple sources with consistent naming and encoding
- Non-volatile — data is loaded and read; it is not updated in place like a transactional DB
- Time-variant — every record is linked to a point in time, enabling historical analysis
Think of a warehouse as a curated library: raw data arrives from messy sources, gets cleaned, organized into a consistent structure, and sits on shelves (tables) that analysts can browse without breaking anything.
Dimensions and Facts
Every warehouse table is one of two types:
Fact tables store measurable business events — numbers that can be summed, averaged or counted. A fact row typically contains foreign keys to dimension tables plus one or more measures (sales amount, quantity, discount, cost). Facts are the "verbs" of your model: ordered, shipped, clicked.
Dimension tables store descriptive context — the "nouns" that tell you who, what, where, when and how. A dimension row contains attributes like customer name, product category, store city, or order date. Dimensions answer the questions "by what?" and "filtered by what?"
Example: in a sales warehouse, the fact is a row in fact_sales (order_id, date_key, product_key, customer_key, store_key, quantity, revenue). The dimensions are dim_date (year, quarter, month), dim_product (name, category, brand), dim_customer (name, segment, city) and dim_store (city, region, manager).
-- Fact table: measures + foreign keys to dimensions
CREATE TABLE fact_sales (
sale_key INT PRIMARY KEY,
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
store_key INT REFERENCES dim_store(store_key),
quantity INT,
revenue DECIMAL(12,2),
cost DECIMAL(12,2)
);
-- Dimension table: descriptive attributes
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(200),
category VARCHAR(100),
brand VARCHAR(100),
unit_cost DECIMAL(10,2)
);Star Schema
The star schema is the most common warehouse design for BI. It consists of one central fact table surrounded by dimension tables — when drawn, it looks like a star. Each dimension is a single, denormalized table (all attributes in one flat table, no sub-tables).
Why star schema works for BI:
- Simple joins — every query joins the fact to one or more dimensions with a single, predictable join path
- Fast queries — fewer joins = better performance on large datasets
- Intuitive for users — business users can understand "fact table in the middle, dimensions around it"
- Tool-friendly — Power BI, Tableau and SSAS are optimized for star schemas
For most BI projects, star schema is the right default. Only move to snowflake when normalization requirements are strong and the trade-off is justified.
Snowflake Schema
A snowflake schema normalizes dimension tables into sub-dimensions. Instead of dim_product containing category directly, you might have dim_product → dim_category → dim_department. The schema looks like a snowflake: a star with branching sub-tables.
When snowflake makes sense:
- Dimensions are very large and have clear hierarchical normalization needs
- Storage is expensive and data duplication must be minimized
- Source systems already provide normalized data and ETL cost to denormalize is high
Trade-offs vs star schema:
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Query simplicity | Fewer joins, easier to write | More joins, harder to read |
| Performance | Faster (less join overhead) | Slower on complex hierarchies |
| Storage | More duplication in dimensions | Less duplication (normalized) |
| Maintenance | Easier to update | More tables to maintain |
| BI tool support | Excellent (default pattern) | Good, but may require views to flatten |
Slowly Changing Dimensions (SCDs)
Dimension attributes change over time: a customer moves cities, a product changes category, a salesperson changes region. Slowly Changing Dimensions are the techniques for handling these changes correctly. The three classic types:
Type 1 — Overwrite: Replace the old value with the new one. No history is kept. Use when the old value was wrong or history does not matter.
Type 2 — Add a new row: Insert a new dimension row with a new surrogate key, effective start/end dates, and a "current" flag. The old fact rows still point to the old key; new facts point to the new key. Full history is preserved.
Type 3 — Add a new column: Store the previous value in a separate column (e.g. previous_city). Limited history (only the last change). Simpler than Type 2 but does not scale to many changes.
-- SCD Type 2 example: dim_customer with history
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- surrogate key
customer_id INT, -- natural key
name VARCHAR(200),
city VARCHAR(100),
effective_date DATE, -- when this version started
expiry_date DATE, -- NULL if current
is_current BOOLEAN -- TRUE for active row
);
-- Old fact rows → old customer_key (old city)
-- New fact rows → new customer_key (new city)
-- Both are correct for their time periodData Marts and the Warehouse vs Lake
A data mart is a subset of the warehouse focused on a single department or business function: a finance mart, a marketing mart, a sales mart. Marts make queries faster (less data to scan) and permissions simpler (finance sees only finance data).
Data warehouse vs data lake:
| Aspect | Data Warehouse | Data Lake |
|---|---|---|
| Data type | Structured, cleaned, modeled | Raw — structured, semi-structured, unstructured |
| Purpose | Reporting and BI | Data science, ML, exploration |
| Schema | Schema-on-write (designed before load) | Schema-on-read (interpreted at query time) |
| Users | Analysts, BI developers | Data engineers, data scientists |
| Cost | Higher (storage + compute for modeling) | Lower (cheap object storage) |
Modern architectures often use a lakehouse — a lake with warehouse-like governance and performance (e.g. Databricks Lakehouse, Snowflake with external tables).
Kimball vs Inmon: Two Philosophies
The two most influential approaches to warehouse design:
Kimball (Bottom-Up): Ralph Kimball's approach starts with dimensional modeling. You build data marts first, one business process at a time (orders mart, inventory mart), and integrate them into a consistent enterprise warehouse through conformed dimensions (shared dimension tables used across all marts). The bus architecture maps each process to dimensions.
Inmon (Top-Down): Bill Inmon's approach starts with a normalized enterprise data model (3NF). You build one comprehensive warehouse first, then derive data marts from it as views or ETL copies. The warehouse is the single source of truth; marts are projections for specific uses.
Hands-On Practice
Use the e-commerce sales dataset from your previous exercises or any multi-table dataset with products, customers and transactions.
- Identify what the fact table would be (transactions) and list the measures (revenue, quantity, cost).
- List at least 4 dimension tables and the attributes each would contain.
- Draw a star schema diagram (pen and paper or a free tool like dbdiagram.io) showing the fact and dimension tables with their keys and key attributes.
- Decide how you would handle a customer who changes city — implement SCD Type 2 with SQL DDL for the customer dimension.
- Decide whether this dataset is better suited to a star or snowflake schema. Justify your choice.
- Compare your design with the Kimball approach: which business process does it model? What conformed dimensions would you need if you added an inventory mart?
Deeper Worked Examples
Adding an inventory mart without breaking the sales mart
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Common Mistakes
- Using a transactional database schema (3NF) for BI queries — it works for OLTP but is slow and confusing for analytics
- Forgetting surrogate keys — natural keys change, surrogate keys are stable warehouse identifiers
- Not handling SCDs — overwriting historical dimensions silently corrupts historical reports
- Building a data lake and calling it a warehouse — they serve different purposes and require different structures
- Choosing snowflake schema by default — star is simpler and faster for most BI use cases
- Ignoring conformed dimensions — if two marts use different dimension definitions, cross-mart analysis breaks
- Designing the warehouse around source systems instead of business processes
Real-World Usage
Every enterprise BI system is built on a data warehouse (or lakehouse). A bank's risk dashboard queries a warehouse that consolidates data from core banking, loans, trading and customer systems. A retail chain's inventory report sits on a warehouse that blends POS data, supplier feeds and warehouse management data. The warehouse is where data becomes a shared, trustworthy asset — and the star schema design you learn here is the blueprint for every Power BI model you will build in the coming topics.
Mini Project
Business problem: An online education platform has courses, instructors, students and enrollment records. The CEO wants dashboards showing enrollment trends, course popularity, instructor performance and student demographics.
Dataset: create a mock dataset or find a public education dataset with at least 1,000 enrollment records, 50 courses, 20 instructors and 500 students.
Objective: design a complete star schema for this business domain.
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
| Deliverable | What It Proves |
|---|---|
| Business process definition | You think in processes, not tables |
| Star schema diagram | You can model facts and dimensions |
| SQL DDL for all tables | You can implement a warehouse schema |
| SCD decision for course category | You understand historical dimension handling |
| Sample data in each table | The schema is valid and queryable |
Key Takeaways
- A data warehouse is a subject-oriented, integrated, non-volatile, time-variant collection of data for reporting
- Fact tables store measures (numbers to aggregate); dimension tables store descriptive attributes
- Star schema: one fact table surrounded by denormalized dimensions — the default for BI
- Snowflake schema: normalized dimensions — saves storage but adds query complexity
- SCD Type 1 overwrites; Type 2 adds rows (preserves history); Type 3 adds columns
- Data marts are department-specific subsets of the warehouse
- Data warehouse (structured, schema-on-write) vs data lake (raw, schema-on-read)
- Kimball (bottom-up, dimensional) is more widely adopted in BI practice than Inmon (top-down, normalized)
Key Terms & Glossary
| Term | Meaning |
|---|---|
| Star schema | A warehouse design with one central fact table surrounded by denormalised dimension tables, giving simple, fast joins; it looks like a star when drawn. |
| Grain | The level of detail stored in a fact table, such as one row per order line or one row per enrollment per day; a fact table cannot answer questions below its grain. |
| Surrogate key | A stable, system-generated identifier used inside the warehouse, separate from the natural business key, so references do not break when business codes change. |
| Slowly changing dimension | The technique for tracking changes to dimension attributes; Type 1 overwrites, Type 2 adds a new versioned row and preserves full history, Type 3 stores the previous value in a column. |
| Conformed dimension | A shared dimension table used consistently across multiple fact tables or data marts, which is what makes cross-mart analysis such as sales joined to inventory possible. |
| Schema-on-write vs schema-on-read | A warehouse defines and validates the schema before load (schema-on-write), while a data lake stores raw data and interprets the schema at query time (schema-on-read). |
Learning Resources
The Data Warehouse Toolkit — Ralph Kimball
The foundational book on dimensional modeling. Chapters on retail, finance and e-commerce are especially practical.
Coursera — Data Warehousing for Business Intelligence
University of Colorado course covering warehouse design, ETL and dimensional modeling.
Microsoft Learn — Data Warehouse Concepts
Microsoft's official documentation on Azure Synapse Analytics and data warehouse architecture.
dbdiagram.io
A free, browser-based tool for drawing star and snowflake schemas using a simple text syntax.
YouTube — Kimball Group Videos
Original lectures from the Kimball Group on dimensional modeling best practices.
Wikipedia — Slowly Changing Dimension
Clear reference on SCD Type 1, 2, 3 and newer types (Type 4, Type 6).
Chapter Quiz
Chapter Quiz — Data Warehousing Concepts
Facts versus dimensions, star versus snowflake, SCDs and the lake — the modeling decisions every BI model rests on.
Question 1 of 5
In a sales warehouse, which table stores measurable events such as revenue and quantity alongside foreign keys?
Knowledge Check
End-of-Topic Exam
Hint: Only one type creates a new row with effective dates and an is_current flag.
Model answer: Type 1 overwrites the city column, losing all history and silently corrupting earlier reports, so it fails the requirement. Type 3 adds a previous_city column and keeps only the very last change. Type 2 adds a new row with the new city, effective_date, expiry_date and an is_current flag: existing fact rows keep the old customer_key pointing at the Cairo row while new facts point at the Alexandria row, so each report shows the city that was true at the time of the order. Type 2 is the correct choice; answers that explain why the other two are insufficient gain full marks.
Hint: Think joins, query speed and BI tool optimisation, then storage and normalisation.
Model answer: The star schema wins because it uses fewer joins, since dimensions are denormalised and flat, making queries faster than navigating a snowflake's branching sub-tables; SQL is simpler to write and debug; and BI tools are optimised for the star pattern. The snowflake can win when storage cost is a genuine constraint, the source hierarchy is already fully normalised, and the ETL cost of denormalising is high, at the price of slower, more complex queries.
Hint: The four words begin subject, integrated, non-volatile, time.
Model answer: A warehouse is subject-oriented (organised around business subjects such as customers and products rather than applications), integrated (consistent naming and encoding across sources), non-volatile (data is loaded and read, not updated in place like an OLTP database) and time-variant (every record is tied to a point in time). Time-variant means historical analysis is possible: a report showing revenue by region for the last five years works because each fact row carries the date it relates to, rather than being overwritten.