Back to all blogs
    7 min read

    🏗️ The Data Modeling Process & Bus Matrix

    datawarehousedatamodelingbusmatrix

    🏗️ Modeling Process & Design

    Analogy

    Building a data model is exactly like building a house.

    1. First, you draw a rough sketch on a napkin (Conceptual Model).
    2. Then, you hire an architect to draw the exact blueprints and measurements (Logical Model).
    3. Finally, the builders use the blueprints to lay the actual bricks and pipes (Physical Model).

    Data Modeling is the process of planning out exactly how your tables will look and connect to each other before you actually build the database.

    📐 1. The 3 Stages of Data Modeling

    Data modeling always happens in three phases, moving from high-level business ideas down to deep technical details.

    StageWhat is it?Who uses it?Analogy
    1. Conceptual Data Model (CDM)A high-level overview. It just shows the main business concepts (e.g., "Customers buy Products"). No technical details.Business LeadersThe rough sketch on a napkin.
    2. Logical Data Model (LDM)Adds details. It defines all the attributes (columns) and relationships (e.g., "Customer has Name, Email, Phone"). Still independent of any specific database software.Data ArchitectsThe official architectural blueprint.
    3. Physical Data Model (PDM)The actual technical implementation. It defines exactly how it will be built in a specific tool like Snowflake (e.g., VARCHAR(50), Primary Keys, Indexes).Database DevelopersThe actual bricks and pipes being laid.

    🛠️ 2. The 4-Step Dimensional Modeling Process

    When an architect sits down to design a Star Schema, they follow a famous 4-step process invented by Ralph Kimball (the pioneer of Data Warehousing).

    Step 1: Identify the Business Process

    What is the business actually trying to measure?

    • Example: Are we measuring Retail Sales? Hospital Admissions? Bank Loans?

    Step 2: Declare the Grain

    What does exactly one row in the Fact table represent? (This must be decided before anything else).

    • Example: One row = one single item scanned at the checkout register.

    Step 3: Identify the Dimensions

    How will business users want to filter and group this data? (The Who, What, Where, When).

    • Example: Date, Store, Product, Customer, Cashier.

    Step 4: Identify the Facts

    What are the actual numbers we are measuring at the declared grain?

    • Example: Quantity Sold, Sales Amount ($), Discount Amount ($).
    Tip

    If a number doesn't match the grain you chose in Step 2, you cannot put it in the Fact table!

    🔑 3. Surrogate Keys

    After the 4 steps, you must assign keys to link the tables together. But in a Data Warehouse, we never trust the source system's IDs.

    Instead, we create Surrogate Keys.

    • A Surrogate Key is a meaningless, auto-incrementing integer (1, 2, 3, 4...) generated by the Data Warehouse itself.

    Why use them?

    1. What if two different source systems both have a "Customer ID 100"? They will crash when combined.
    2. If we use SCD Type 2 (keeping history by adding new rows), we will have two rows for the same customer. They can't both have the same Primary Key. A Surrogate Key solves this (Row 1 gets Key 101, Row 2 gets Key 102).

    🚌 4. The Enterprise Data Warehouse Bus Matrix

    Analogy

    Imagine planning public transport for a city. The Bus Matrix is the map that shows which bus routes (Business Processes) stop at which bus stops (Dimensions).

    When planning an entire Enterprise Data Warehouse, you can't build it all at once. You have to plan how different Data Marts will share the same Dimensions (Conformed Dimensions).

    The Bus Matrix is a simple grid used for enterprise planning.

    Business Process (Fact)DateProductStoreEmployeeCustomer
    Retail Sales✅✅✅✅✅
    Inventory Tracking✅✅✅
    Payroll✅✅

    Why is the Bus Matrix important?

    1. Planning: It helps the team plan which Data Marts to build first.
    2. Reusability: If you build the Date and Store dimensions for the Sales team, you can reuse them for the Inventory team later. No wasted effort!

    🧪 Practice Drill

    text
    // Try answering these:
// Q1. Which data modeling stage defines the exact data types (like `VARCHAR(50)`) and database indexes?
// Q2. What are the 4 steps of Ralph Kimball's Dimensional Modeling process, in order?
// Q3. Why do Data Warehouses use Surrogate Keys instead of just using the ID from the source system?
// Q4. A team uses a grid to map out which Business Processes (Fact tables) will use which Dimension tables across the whole company. What is this tool called?
// Q5. Match the stage to the analogy:
// | Analogy | Stage |
// |---|---|
// | The architectural blueprint showing all rooms and measurements. | ? |
// | The actual bricks being laid to build the house. | ? |
// | A rough sketch of the house on a napkin. | ? |
    💡 Click for Solutions

    A1. The Physical Data Model (PDM).

    A2.

    1. Identify the Business Process
    2. Declare the Grain
    3. Identify the Dimensions
    4. Identify the Facts

    A3. Because source system IDs might conflict (two systems having ID 100), and because SCD Type 2 requires us to create multiple rows for the same customer, which is impossible if we rely on a single source ID.

    A4. The Enterprise Data Warehouse Bus Matrix.

    A5.

    AnalogyStage
    The architectural blueprint showing all rooms and measurements.Logical Data Model (LDM)
    The actual bricks being laid to build the house.Physical Data Model (PDM)
    A rough sketch of the house on a napkin.Conceptual Data Model (CDM)

    ← Previous Topic