Data Warehouse Architecture & Modelling: Schemas, ETL, and OLAP

TAKEAWAYS:

• Data Warehouse Architecture follows a layered design (source → staging → integration → data marts) to enable scalable analytics.
• Star Schema is the most common modelling technique, optimizing query performance with a central fact table and dimension tables.
• ETL (Extract, Transform, Load) pipelines clean and transform raw data into a structured warehouse format.
• OLAP (Online Analytical Processing) enables multidimensional analysis via cube operations (slice, dice, drill-down).
• Data Marts are department-specific subsets of a data warehouse, improving query efficiency for targeted analytics.
• Slowly Changing Dimensions (SCD) handle historical data updates (Type 1, 2, or 3) without breaking referential integrity.

1. Data Warehouse Architecture: The 4-Layer Model

A data warehouse (DW) is not a single database but a structured, integrated, and time-variant repository designed for analytical processing (OLAP), not transactional (OLTP). Its architecture follows four layers:

1.1 The Four Layers

flowchart LR
    A["Source Systems (OLTP DBs, APIs, Files)"] --> B["Staging Area (Raw Data Lake)"]
    B --> C["Data Integration (ETL/ELT)"]
    C --> D["Data Warehouse (Star/Snowflake Schema)"]
    D --> E["Data Marts (Departmental Subsets)"]
    E --> F["OLAP Engine (Cube Processing)"]
    F --> G["Frontend (BI Tools: Tableau, Power BI)"]

• Source Layer: OLTP databases (e.g., bank transactions, eSewa payments), APIs (e.g., Daraz order logs), or flat files (e.g., Excel sales reports).
• Staging Layer: Raw data is dumped here without transformation (e.g., a copy of all Ncell customer records).
• Integration Layer: ETL/ELT processes clean, transform, and load data into the DW schema.
• Data Warehouse Layer: The core analytical database (star/snowflake schema).
• Data Marts: Subject-oriented subsets (e.g., a "Marketing Data Mart" for eSewa campaign analysis).
• OLAP Layer: Enables multidimensional queries (e.g., "Show Pathao driver earnings by district and month").

1.2 Why This Matters: Real-World Example

eSewa’s Fraud Detection System

• Source: Raw transaction logs (user ID, amount, timestamp, device IP).
• Staging: All logs stored in a data lake (unstructured).
• ETL: Fraud rules (e.g., "same IP, 10 transactions in 1 minute") flag suspicious activity.
• Data Warehouse: Star schema with Fact_Transactions (foreign keys to Dim_User, Dim_Device, Dim_Location).
• Data Mart: A "Fraud Analytics Mart" for the security team.
• OLAP: "Drill down to see all transactions from IP X in the last hour."

2. Data Warehouse Schemas: Star vs. Snowflake vs. Fact Conglomerate

The schema design determines query performance and storage efficiency. The three main types:

2.1 Star Schema: The Gold Standard for OLAP

graph TD
    A["Fact_Sales"] --> B["Dim_Date"]
    A --> C["Dim_Product"]
    A --> D["Dim_Store"]
    A --> E["Dim_Customer"]

• Structure:
  • 1 Fact Table (numeric metrics, e.g., sales_amount, quantity).
  • Multiple Dimension Tables (descriptive attributes, e.g., product_name, store_location).
• Pros:
  • Simple to query (fewer joins).
  • Faster performance for OLAP tools (e.g., Tableau).
• Cons:
  • Redundant data if dimensions have hierarchies (e.g., city → district → country).
• Example: A Daraz sales DW might have:

  Fact_Sales (order_id, product_id, store_id, date_id, quantity, amount)
  Dim_Product (product_id, category, brand, price)
  Dim_Date (date_id, day, month, year, quarter)
  

2.2 Snowflake Schema: Normalized Dimensions

graph TD
    A["Fact_Sales"] --> B["Dim_Date"]
    A --> C["Dim_Product"]
    C --> D["Dim_Category"]
    C --> E["Dim_Brand"]
    A --> F["Dim_Store"]
    F --> G["Dim_District"]
    F --> H["Dim_Country"]

• Structure:
  • Dimension tables are further normalized (e.g., Dim_Product splits into Dim_Category, Dim_Brand).
• Pros:
  • Reduces redundancy (saves storage).
  • Better for data integrity (e.g., if a brand name changes).
• Cons:
  • Slower queries (more joins).
  • Complex to maintain.
• Example: A NTC telecom DW might snowflake Dim_Customer into: Dim_Customer → Dim_Address → Dim_City → Dim_District.

2.3 Fact Conglomerate Schema: Hybrid Approach

• Combines star and snowflake by:
  • Keeping frequently queried dimensions denormalized (star-like).
  • Normalizing less-frequent dimensions (snowflake-like).
• Example: In a bank loan DW, Dim_Customer is snowflaked (Customer → Address → City), but Dim_LoanType remains flat (star-like) because it’s queried often.

2.4 Comparison Table

3. ETL (Extract, Transform, Load): The Data Pipeline

ETL is the engine that turns raw data into a usable DW. Let’s break it down with a real example: Pathao’s Driver Earnings Report.

3.1 The ETL Process

flowchart LR
    A["Extract: Pathao API"] --> B["Transform: Clean & Aggregate"]
    B --> C["Load: Star Schema DW"]
    C --> D["OLAP Cube"]
    D --> E["Dashboard: Driver Earnings by City"]

3.2 Step-by-Step: Pathao Driver Earnings DW

Step 1: Extract

• Source: Pathao’s ride logs (JSON API calls).
• Data Sample:

  {
    "ride_id": "R1001",
    "driver_id": "D45",
    "passenger_id": "P78",
    "start_time": "2023-10-01 08:30",
    "end_time": "2023-10-01 08:45",
    "distance_km": 5.2,
    "fare": 450,
    "rating": 4.5
  }
  

Step 2: Transform

• Cleaning:
  • Remove rides with distance_km = 0 (fraud).
  • Standardize start_time to UTC.
• Aggregation:
  • Sum fare by driver_id and date.
  • Calculate avg_rating per driver.
• Schema Mapping:
  • ride_id → Fact_Rides (foreign key to dimensions).
  • driver_id → Dim_Driver (name, vehicle, city).
  • date → Dim_Date (year, month, day, quarter).

Step 3: Load

• Insert into star schema:

  Fact_Rides (ride_id, driver_id, date_id, distance_km, fare, rating)
  Dim_Driver (driver_id, name, vehicle_type, city_id)
  Dim_Date (date_id, day, month, year, quarter)
  

Step 4: OLAP Processing

• Create a cube for "Driver Earnings by City and Month":

  SELECT
      d.city,
      DATE_TRUNC('month', dr.start_time) AS month,
      SUM(f.fare) AS total_earnings,
      AVG(f.rating) AS avg_rating
  FROM Fact_Rides f
  JOIN Dim_Driver d ON f.driver_id = d.driver_id
  GROUP BY d.city, DATE_TRUNC('month', dr.start_time)
  

4. OLAP and Multidimensional Analysis

OLAP enables "slice-and-dice" analysis using cubes. Key operations:

4.1 The OLAP Cube

graph TD
    A["Measure: Sales"] --> B["Dimension 1: Product"]
    A --> C["Dimension 2: Time"]
    A --> D["Dimension 3: Region"]
    A --> E["Dimension 4: Customer Segment"]

• Axes:
  • Rows: Dimensions (e.g., Product, Region).
  • Columns: Dimensions (e.g., Time, Customer).
  • Depth: Measures (e.g., Sales, Profit).
• Example: A NEPSE stock DW cube might have:
  • Measures: volume, price_change.
  • Dimensions: stock_symbol, date, sector.

4.2 OLAP Operations

4.3 Worked Example: Kathmandu Traffic Routes

Scenario: The Kathmandu Metropolitan City wants to analyze traffic congestion using a DW.

• Fact Table: Fact_Traffic (route_id, vehicle_count, time_id, congestion_level).
• Dimensions:
  • Dim_Route (route_id, start_point, end_point, length_km).
  • Dim_Time (time_id, hour, day_type [weekday/weekend]).
  • Dim_VehicleType (vehicle_type_id, type [car/bike/bus]).

Query: "Show the top 3 congested routes during rush hours (7-9 AM) on weekdays."

WITH RushHour AS (
    SELECT route_id, SUM(vehicle_count) AS total_vehicles
    FROM Fact_Traffic
    WHERE time_id IN (
        SELECT time_id FROM Dim_Time
        WHERE hour BETWEEN 7 AND 9 AND day_type = 'weekday'
    )
    GROUP BY route_id
)
SELECT
    r.route_id,
    r.start_point,
    r.end_point,
    rh.total_vehicles,
    rh.total_vehicles / r.length_km AS congestion_density
FROM RushHour rh
JOIN Dim_Route r ON rh.route_id = r.route_id
ORDER BY rh.total_vehicles DESC
LIMIT 3;

Output:

5. Data Marts: Departmental Subsets of a DW

A data mart is a subset of a DW tailored for a specific department (e.g., Finance, Marketing). Types:

5.1 Independent vs. Dependent Data Marts

5.2 Example: Ncell’s Customer Churn Data Mart

• Source: Central DW with Fact_Calls, Fact_DataUsage, Dim_Customer.
• Extract:
  • Customers who reduced data usage by >30% in the last 3 months.
  • Customers who switched to NTC (competitor analysis).
• Schema:

  Fact_Churn (customer_id, churn_date, reason_code, next_carrier_id)
  Dim_Customer (customer_id, join_date, plan_type, city)
  Dim_UsagePattern (usage_id, avg_data_usage_MB, avg_call_duration_min)
  

• Query: "Find customers in Lalitpur who churned to NTC due to high data costs."

SELECT
    c.customer_id,
    c.join_date,
    u.avg_data_usage_MB,
    f.churn_date,
    f.reason_code
FROM Fact_Churn f
JOIN Dim_Customer c ON f.customer_id = c.customer_id
JOIN Dim_UsagePattern u ON f.customer_id = u.customer_id
WHERE c.city = 'Lalitpur'
  AND f.next_carrier_id = 'NTC'
  AND f.reason_code = 'HighDataCosts';

6. Slowly Changing Dimensions (SCD): Handling Historical Data

Dimensions change over time (e.g., a customer moves, a product’s price changes). SCD strategies:

6.1 SCD Types

6.2 Worked Example: NEPSE Stock Symbol Change (SCD Type 2)

Scenario: The stock symbol for Nepal Bank Limited changes from NBL to NBLBANK in 2023.

• Old Record (2022): Dim_Stock (stock_id=1, symbol="NBL", name="Nepal Bank", valid_from="2020-01-01", valid_to="2023-01-01")
• New Record (2023): Dim_Stock (stock_id=2, symbol="NBLBANK", name="Nepal Bank", valid_from="2023-01-02", valid_to=NULL)

Query to Find Historical Prices:

SELECT
    s.symbol,
    f.trade_date,
    f.price
FROM Fact_Trades f
JOIN Dim_Stock s ON f.stock_id = s.stock_id
WHERE s.name = 'Nepal Bank'
ORDER BY f.trade_date;

Output:

Exam Tip

1. Diagrams Are Key:

   • Draw star/snowflake schemas for any DW question.
   • Label ETL pipelines with extract/transform/load steps.
   • Sketch an OLAP cube for multidimensional analysis questions.

2. Real-World Mapping:

   • eSewa: Use star schema for transaction analysis.
   • Pathao: Apply ETL to driver earnings data.
   • NTC: Snowflake schema for customer hierarchies.

3. Common Pitfalls:

   • Confusing OLTP vs. OLAP: OLTP = transactions (e.g., bank deposits), OLAP = analytics (e.g., "Which branch has the highest loan default rate?").
   • SCD Types: Remember Type 2 is for full history, Type 1 overwrites.
   • Data Marts: Independent = standalone, dependent = from DW.

4. Numerical Questions:

   • Always show SQL-like steps (even if not required).
   • For OLAP operations, describe the axis changes (e.g., "dice to select only Kathmandu and Q1 2023").

Final Visual Summary

mindmap
  root((Data Warehouse Architecture))
    Star Schema
      Fact Table
      Dimension Tables
      Example: Daraz Sales
    Snowflake Schema
      Normalized Dimensions
      Example: NTC Customer
    ETL Pipeline
      Extract: API/DB
      Transform: Clean/Aggregate
      Load: DW Schema
    OLAP Cube
      Slice/Dice/Drill-Down
      Example: Traffic Routes
    Data Marts
      Independent vs. Dependent
      Example: Ncell Churn
    SCD Types
      Type 1: Overwrite
      Type 2: History (valid_from/to)
      Type 3: Limited History