This repository is dedicated to my practical learning journey in Data Warehouse Modelling. The goal is to evolve from simple dimensional implementations to complex architectures, documenting the design patterns and methodologies used to structure data for analytics.
Currently, the repository contains the following projects:
-
Sales Warehouse: Fundamental implementation of a Star Schema for sales data.
- Focus: Star Schema, Surrogate Keys, B-Tree Indexes, Python + SQLite.
-
Subscription Warehouse: Tracking user history with SCD Type 2 and Snowflake Schema.
- Focus: SCD (Slowly Changing Dimensions) Type 2, Snowflake Schema, DuckDB (Columnar Storage).
-
Order Fulfillment Warehouse: Tracking e-commerce orders through a multi-stage pipeline.
- Focus: Accumulating Snapshot Fact Table, Role-Playing Dimensions, Degenerate Dimensions, Junk Dimensions, DuckDB.
-
Inventory Snapshot Warehouse: Monitoring stock levels and category hierarchies over time.
- Focus: Periodic Snapshot Fact Table, Parent-Child Hierarchies, Recursive CTEs, Semi-Additive Measures.
-
Healthcare Bridge Warehouse: Handling Many-to-Many relationships and semantic abstractions.
- Focus: Bridging Tables, Multi-valued Dimensions, Semantic Layer (Views), Revenue Allocation.
Simple 1-line summaries of every concept covered across the projects.
- Star Schema: A central fact table surrounded by dimension tables — the most common DW layout, optimized for fast reads.
- Transactional Fact Table: Records one row per individual business event (e.g., one row per sale), never updated after insert.
- Surrogate Key: An internal integer ID created by the DW to identify dimension records, independent of the source system's ID.
- Natural Key: The original ID from the source system (e.g.,
customer_id = 'C001') — kept for traceability but not used for joins. - B-Tree Index: A data structure that speeds up lookups on a column by organizing values like a sorted tree — avoids scanning millions of rows.
- Snowflake Schema: A Star Schema where some dimensions are further normalized into sub-tables (e.g., Plans → Tiers), reducing redundancy but adding joins.
- SCD Type 2: Instead of overwriting a changed record, a new row is inserted with the new values — preserving the full history of what changed and when.
- Effective Dating (
valid_from/valid_to): Columns that define the active period of each historical row in an SCD Type 2 dimension. - Columnar Storage (DuckDB): Data stored column-by-column instead of row-by-row — much faster for analytical queries that read few columns across many rows.
- Accumulating Snapshot Fact Table: One row per pipeline process (e.g., one per order), with a dedicated date column for each stage — updated as each milestone is reached.
- Role-Playing Dimension: The same physical dimension table (e.g.,
dim_date) joined multiple times in the same query, each time with a different alias representing a different business role (placed date, shipped date, etc.). - Degenerate Dimension: An attribute from the source system with business meaning (e.g.,
order_number) stored directly in the fact table — no separate dimension table is needed because there are no extra attributes to describe it. - Junk Dimension: A single table that groups together miscellaneous boolean flags (e.g.,
is_gift,is_express) that don't belong to any existing dimension — all possible combinations are pre-generated so the fact only stores one key. - Lag Measure: A pre-calculated numeric column in the fact table representing the time elapsed between two pipeline milestones (e.g.,
days_to_deliver) — avoids computing it at query time.
- Periodic Snapshot Fact Table: Stores the "state" of things at fixed intervals (e.g., every day). Unlike transactions, it records the current balance, even if nothing changed since yesterday.
- Parent-Child Hierarchy: A dimension table that points to itself (e.g., Category → Parent Category). It handles flexible, deep hierarchies without adding multiple columns for every level.
- Recursive CTE: A special SQL query pattern used to traverse parent-child relationships, allowing you to walk up or down the entire tree of categories.
- Semi-Additive Measure: A numeric value (like stock quantity) that can be summed across products or warehouses, but cannot be summed over time (as it represents a balance, not a flow).
- Bridging Table: A table used to model Many-to-Many relationships between a fact and a dimension (e.g., one encounter with many diagnoses).
- Multi-valued Dimension: A dimension where a single fact record can be associated with multiple dimension members simultaneously.
- Weight Factor: A technique in bridge tables used to allocate fact measures (like billing amount) across multiple dimension members to avoid double-counting.
- Semantic Layer: A logical layer of Views that abstracts complexity, providing clean and pre-calculated metrics for the end user.
This repository serves as a roadmap for mastering different data architecture styles and modelling techniques:
- Star Schema: Denormalized structure optimized for read performance and simplicity.
- Snowflake Schema: Normalized dimensional modelling for storage efficiency and data integrity.
- Fact Table Types:
- Transactional Facts (individual events).
- Periodic Snapshots (status over time).
- Accumulating Snapshots (processes with multiple milestones).
- Dimension Types:
- SCD (Slowly Changing Dimensions) Type 2
- Role-Playing Dimensions (e.g., multiple dates in one fact).
- Junk Dimensions (grouping miscellaneous flags).
- Degenerate Dimensions (stored directly in the fact table).
- Bridging Tables: Handling many-to-many relationships.
- Hierarchies: Modelling parent-child relationships and ragged hierarchies.
- Technical Keys: Surrogate Keys vs Natural Keys vs Business Keys.
- Semantic Layer: Modelling definitions and metrics for end-user consumption.
- Indexing Strategies: B-Trees, Bitmap indexes, and their impact on query plans.
- Partitioning & Clustering: Organizing data physically to optimize massive scans.
- Columnar Storage Impact: How OLAP engines (Parquet, DuckDB) change modelling decisions compared to Row-based systems.
- Vector Modelling: Structuring unstructured data for AI-powered semantic search (RAG).
- Modelling Specifications: SQL (DDL), DBML.
- Storage Engines: SQLite (Row), PostgreSQL (Hybrid), DuckDB (Columnar).
- Languages: Python (for data generation and schema testing).
This repository focuses on how to structure data, leaving transport and scheduling (ETL/Orchestration) to dedicated repositories.