Module 1: Data Warehousing Fundamentals
Course: Advanced Database & Data Warehousing
Learning Objectives
After completing this module, students will be able to:
1. Identify and describe each core component of a data-warehousing architecture.
2. Plan and document the end-to-end steps in building a data warehouse.
3. Compare star, snowflake and fact-constellation schemas for decision support.
4. Explain the roles of extraction, transformation and loading (ETL) tools.
5. Design a monitoring strategy for operational, data-quality and performance KPIs.
1 Introduction
The modern enterprise generates petabytes of structured, semi-structured and unstructured
data. Competitive advantage depends on turning that raw data into timely, trustworthy
information—a challenge that the data warehouse (DW) was invented to address. This module
introduces the components, processes and best practices that underlie every successful DW
implementation.
Definition (W. H. Inmon, 1993)
“A data warehouse is a subject-oriented, integrated, non-volatile and time-variant
collection of data in support of management’s decision-making process.”
2 Data-Warehousing Components
At a high level, a DW environment consists of the elements shown in Figure 1.
Enterprise Data Warehouse Architecture Components
Component summary
# Component Key Responsibilities
1 Source Systems Capture transactions; minimal impact during extraction
2 Staging Area Raw landing, data validation, change-data capture
3 ETL/ELT Clean, conform, transform, load or stream data
4 Enterprise DW Centralised store; supports historical queries
5 Data Marts Performance-tuned, subject-area subsets
# Component Key Responsibilities
6 BI Layer SQL, OLAP, dashboards, data science notebooks
7 Metadata Repository Technical & business metadata, lineage
8 Monitoring Sub-system Data-quality rules, job orchestration KPIs
3 Building a Data Warehouse: Road-Map
1. Business Requirements Elicitation – interview stakeholders, map analytic use-cases.
2. Source‐to-Target Mapping (STM) – document entities, attributes, data-types, refresh
cycles.
3. Architecture Selection – Kimball bottom-up vs. Inmon top-down vs. Data-vault hybrid.
4. Data Modelling – choose star/snowflake schemas; define grain, facts, dimensions.
5. Infrastructure Provisioning – on-prem SMP/MPP, cloud DWaaS (e.g. Snowflake,
BigQuery).
6. ETL Development & Orchestration – build pipelines, implement data-quality rules.
7. Testing & Reconciliation – unit, system, performance, UAT; balance control totals.
8. Deployment & Cut-over – backfill history, incremental loads, parallel run.
9. Operate & Monitor – SLA dashboards, usage telemetry, cost governance.
4 DBMS Schemas for Decision Support
4.1 Multidimensional Model
Facts (quantitative measurements) are analysed along dimensions (contexts such as time,
product, geography). Data cubes enable OLAP operations: roll-up, drill-down, slice, dice, pivot.
4.2 Star Schema
A central fact table connected to denormalised dimension tables via foreign keys. Simplifies joins
and supports bitmap indexing.
Star Schema Design for Data Warehouse
4.3 Snowflake Schema
Dimensions are normalised into sub-dimensions, saving space but requiring extra joins.
Snowflake vs Star Schema Comparison
4.4 Fact-Constellation (Galaxy)
Multiple fact tables share conformed dimensions—ideal for enterprise data-marts.
5 ETL: Extraction, Clean-up & Transformation Tools
Phase Typical Tasks Popular Tools
Fivetran, Informatica, Oracle
Extract Full & incremental loads; CDC via logs; API pulling
GoldenGate
Data typing, standardisation, reference look-ups,
Transform dbt, Talend, PySpark
deduplication, SCD handling
Airflow, Azure Data Factory,
Load/Stream Bulk-load, merge/upsert, micro-batch, streaming sinks
Kafka Connect
ETL Process Flow with Data Quality Monitoring
Data-Quality Rules (examples)
Uniqueness: Primary-key duplicates = 0.
Referential integrity: All foreign keys resolve.
Range: order_amount > 0.
Timeliness: Load latency < 5 min.
6 Monitoring the Data-Warehousing Environment
Key metrics fall into three groups:
1. Pipeline Health – job duration, success/failure, SLA breaches.
2. Data Quality – rule violations, anomaly scores.
3. System Performance & Cost – query latency, concurrency, credit/slot usage.
A typical architecture couples orchestration logs (e.g., Airflow metadata DB) with metric
collectors (Prometheus) and visualises them in Grafana or Power BI.
7 Summary
This module laid the foundation for the course by dissecting the anatomy of a data warehouse,
mapping the build lifecycle, comparing analytic schemas, surveying ETL tooling and outlining a
robust monitoring strategy.
8 Review Questions
1. Describe the four hallmark characteristics of a data warehouse, according to Inmon.
2. Contrast star and snowflake schemas in terms of storage efficiency and query performance.
3. List three change-data-capture techniques and the scenarios in which each is appropriate.
4. Explain why metadata is critical for self-service BI.
5. What KPIs would you track to assure data-quality in production?
9 Suggested Reading
Inmon, W. H. (2023). Building the Data Warehouse (5th ed.).
Kimball, R. & Ross, M. (2019). The Data Warehouse Toolkit (4th ed.).
IBM White-paper (2022). Best Practices for Data Warehousing Environments.
© 2025 Professor ______ — released under CC BY-NC-SA 4.0