Data Warehouse Architecture & Modelling: Schemas, ETL, Star/Snowflake

TAKEAWAYS:

• A data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data designed to support decision-making, not transaction processing.
• Dimensional modelling uses fact tables (measures) and dimension tables (descriptors) to organize data in star or snowflake schemas for efficient querying.
• ETL (Extract, Transform, Load) pipelines clean, standardize, and consolidate data from heterogeneous sources (e.g., databases, APIs, logs) before loading into the warehouse.
• OLTP vs. OLAP systems serve different purposes: OLTP handles transactions (e.g., bank withdrawals), while OLAP supports complex analytical queries (e.g., market trend analysis).
• Data marts are specialized subsets of data warehouses for specific departments (e.g., a sales team’s regional performance dashboard).
• Metadata (data about data) is critical for governance, lineage tracking, and enabling self-service analytics in modern BI tools.

1. Definitions and Core Concepts

What is a Data Warehouse?

A data warehouse (DW) is a centralized repository that stores historical and integrated data from multiple sources to enable decision-support queries, trend analysis, and business intelligence (BI). Unlike OLTP (Online Transaction Processing) systems (e.g., bank transaction databases), DWs are optimized for OLAP (Online Analytical Processing)—complex, aggregated queries.

Key Characteristics:

• Subject-oriented: Organized by business areas (e.g., sales, finance, customer).
• Integrated: Resolves inconsistencies (e.g., merging customer IDs from CRM and ERP).
• Time-variant: Captures historical data (e.g., monthly sales over 5 years).
• Non-volatile: Data is not updated in place; instead, new snapshots are appended (unlike OLTP tables).

Visual Analogy: Think of a DW as a library’s card catalog (subject-indexed, historical, read-only for research), not a bank teller’s ledger (real-time, transactional).

Why Build a Data Warehouse?

• Unify siloed data: Combine ERP (SAP), CRM (Salesforce), and log files into one queryable format.
• Enable cross-functional analysis: Answer questions like “Which product categories drove 2023 revenue growth in Kathmandu vs. Pokhara?”
• Support predictive modeling: Train ML models on historical patterns (e.g., Daraz’s demand forecasting).
• Compliance and audit trails: Track data lineage for regulations (e.g., GDPR, Nepal’s data protection laws).

2. Data Warehouse Architecture

A DW system consists of three layers:

flowchart TD
    A["Operational Systems\n(OLTP: Databases, APIs, IoT)"] -->|"ETL"| B["Staging Area\n(Temporary, raw data)"]
    B -->|"ETL"| C["Data Warehouse\n(Star/Snowflake schemas, OLAP)"]
    C --> D["Data Marts\n(Specialized subsets for teams)"]
    C --> E["Metadata Repository\n(Data about data: lineage, definitions)"]
    D --> F["End Users\n(Business analysts, BI tools like Tableau, Power BI)"]

Key Components:

1. Operational Systems: Source systems (e.g., eSewa’s transaction database, Ncell’s call detail records).
2. Staging Area: Raw, unprocessed data (e.g., JSON logs from Daraz’s servers).
3. Data Warehouse: Structured, optimized for queries (e.g., NEPSE’s stock price history).
4. Data Marts: Subsets for specific teams (e.g., Pathao’s driver performance dashboard).
5. Metadata Repository: Tracks data sources, transformations, and definitions (e.g., “dim_date.date_key = fact_sales.order_date”).

3. Dimensional Modelling: Star and Snowflake Schemas

Dimensional modelling organizes data into facts (measures) and dimensions (descriptors). Two common schemas:

A. Star Schema

• Central fact table linked to dimension tables via foreign keys.
• Simple to query but can lead to data redundancy if dimensions have nested hierarchies.

graph TD
    A["Fact_Sales<br/>(sales_id, product_id, customer_id, date_key, amount)"] --> B["Dim_Product<br/>(product_id, product_name, category)"]
    A --> C["Dim_Customer<br/>(customer_id, name, segment)"]
    A --> D["Dim_Date<br/>(date_key, month, quarter, year)"]

Example: Daraz’s Sales Analysis

• Fact Table: Fact_Orders (order_id, product_id, customer_id, order_date, total_amount).
• Dimensions:
  • Dim_Product (product_id, name, category, price).
  • Dim_Customer (customer_id, location, purchase_frequency).
  • Dim_Date (order_date, month, holiday_flag).

Query Example: “What was the total sales amount for electronics products in Kathmandu during December 2023?”

SELECT SUM(amount)
FROM Fact_Orders
JOIN Dim_Product ON Fact_Orders.product_id = Dim_Product.product_id
JOIN Dim_Customer ON Fact_Orders.customer_id = Dim_Customer.customer_id
JOIN Dim_Date ON Fact_Orders.order_date = Dim_Date.date_key
WHERE Dim_Product.category = 'Electronics'
  AND Dim_Customer.location = 'Kathmandu'
  AND Dim_Date.month = 'December'
  AND Dim_Date.year = 2023;

B. Snowflake Schema

• Normalizes dimension tables to reduce redundancy (e.g., split Dim_Product into Product and Category).
• More storage-efficient but complex joins slow down queries.

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

Trade-off Table:

4. ETL (Extract, Transform, Load) Pipelines

ETL processes raw data into a usable format for the DW. Steps:

1. Extract: Pull data from sources (e.g., NTC’s call records, Khalti’s payment logs).
2. Transform: Clean, standardize, and enrich (e.g., convert dates to YYYY-MM-DD, resolve duplicate customer IDs).
3. Load: Insert into the staging area or DW.

Example: eSewa’s Transaction Data Pipeline

1. Extract: Pull JSON logs from eSewa’s transaction API every 15 minutes.
2. Transform:
   • Parse transaction_id, amount, timestamp.
   • Standardize currency (all to NPR).
   • Resolve user_id conflicts (merge accounts with the same mobile number).
3. Load: Append to Fact_Transactions in the DW.

ETL Tools:

• Open-source: Apache NiFi, Talend.
• Commercial: Informatica, Microsoft SSIS, AWS Glue.

5. Data Marts vs. Data Warehouses

Real-World Example:

• NEPSE’s DW stores stock price history for all listed companies.
• NEPSE’s Sales Data Mart (for traders) might only include Nepal Investment Bank’s stock trends.

6. Metadata and Data Governance

Metadata is data about data (e.g., “Dim_Date.date_key is a surrogate key for order_date”). Critical for:

• Lineage tracking: “This sales report uses data from eSewa’s 2023 logs.”
• Data quality: Flagging incomplete records (e.g., NULL customer locations).
• Self-service BI: Users know how to interpret dimensions (e.g., “Dim_Product.category = ‘Electronics’ includes smartphones and laptops.”).

Example: Daraz’s Metadata

{
  "table": "Fact_Orders",
  "description": "Daily sales transactions",
  "columns": [
    {"name": "order_id", "type": "INT", "key": "primary"},
    {"name": "product_id", "type": "INT", "foreign_key": "Dim_Product.product_id"}
  ],
  "source": "Daraz ERP System",
  "last_updated": "2024-05-20"
}

7. Real-World Applications

In the Real World

1. eSewa’s Fraud Detection

   • Idea: Association Rule Mining (Unit 6) on transaction data to find patterns like “User A frequently transfers NPR 10,000 to User B at 3 AM.”
   • DW Role: The Fact_Transactions table in eSewa’s DW feeds this analysis, with dimensions like user_id, amount, and transaction_time.

2. Daraz’s Inventory Forecasting

   • Idea: Time-series forecasting (Unit 8) on historical sales data to predict stockouts.
   • DW Role: The Dim_Date and Fact_Sales tables in Daraz’s DW enable queries like “How many units of Product X were sold in July 2023 by region?”

3. NEPSE’s Market Trend Analysis

   • Idea: OLAP cubes (Unit 3) to slice stock price data by sector (e.g., “How did banking stocks perform vs. tourism stocks in 2023?”).
   • DW Role: The Fact_StockPrices table, linked to Dim_Date and Dim_Sector, powers these reports.

Worked Example: Bank Loan Default Prediction

Scenario: A bank wants to predict loan defaults using historical data from NMB Bank.

DW Schema:

graph TD
    A["Fact_Loans<br/>(loan_id, customer_id, amount, term_months, default_flag)"] --> B["Dim_Customer<br/>(customer_id, credit_score, income)"]
    A --> C["Dim_Date<br/>(loan_date, month, year)"]

Steps:

1. Extract: Pull loan data from NMB’s OLTP system (e.g., SQL query to export loans table).
2. Transform:
   • Clean: Remove loans with NULL credit_score.
   • Enrich: Add default_flag (1 = defaulted, 0 = paid).
   • Standardize: Convert loan_date to Dim_Date.date_key.
3. Load: Append to Fact_Loans in the DW.
4. Analyze: Use Classification (Unit 7) to build a model predicting default_flag based on credit_score and income.

Query to Build the Fact Table:

INSERT INTO Fact_Loans (loan_id, customer_id, amount, term_months, default_flag, loan_date)
SELECT
    l.loan_id,
    l.customer_id,
    l.amount,
    l.term_months,
    CASE WHEN l.status = 'Defaulted' THEN 1 ELSE 0 END,
    l.loan_date
FROM loans l
WHERE l.credit_score IS NOT NULL;

OLAP Query for Insights: “What is the average loan amount for customers with a credit score < 600 who defaulted in 2023?”

SELECT AVG(amount)
FROM Fact_Loans
JOIN Dim_Customer ON Fact_Loans.customer_id = Dim_Customer.customer_id
JOIN Dim_Date ON Fact_Loans.loan_date = Dim_Date.date_key
WHERE Dim_Customer.credit_score < 600
  AND Fact_Loans.default_flag = 1
  AND Dim_Date.year = 2023;

8. Advantages and Disadvantages

9. Exam Tip

• Focus on these high-weightage topics:
  1. Compare OLTP vs. OLAP (tables, queries, tools).
  2. Draw and label a star/snowflake schema (include fact and dimension tables).
  3. Explain ETL with a real example (e.g., how Ncell processes call data).
  4. Discuss metadata’s role in data governance (1–2 sentences).
  5. Relate DW to data mining (e.g., “The DW feeds association rules for market basket analysis.”).
• Common Pitfalls:
  • Confusing data warehouse with data lake (DW is structured; lake is raw).
  • Forgetting to mention time-variance in definitions.
  • Drawing schemas without primary/foreign keys.
• Formula/Calculation: None, but query writing (e.g., SQL joins) is tested.
• Diagram: Always include a star schema in your answer—examiners love it.

Practice Question (Model Answer Style)

Q: Design a star schema for NTC’s call detail data, including fact and dimension tables. Explain how ETL would process raw call logs.

Answer: Star Schema:

graph TD
    A["Fact_Calls<br/>(call_id, customer_id, duration_sec, start_time, cost)"] --> B["Dim_Customer<br/>(customer_id, plan_type, location)"]
    A --> C["Dim_DateTime<br/>(start_time, hour, day_of_week)"]
    A --> D["Dim_Tariff<br/>(tariff_id, rate_per_sec)"]

ETL Process:

1. Extract: Pull JSON logs from NTC’s call servers (e.g., {"call_id": "X123", "duration": 180, "start": "2024-05-20T10:00:00"}).
2. Transform:
   • Parse duration_sec (convert minutes to seconds).
   • Resolve customer_id using NTC’s CRM system.
   • Map start_time to Dim_DateTime (e.g., hour=10, day_of_week="Wednesday").
   • Apply Dim_Tariff.rate_per_sec to calculate cost.
3. Load: Append to Fact_Calls with foreign keys to dimensions.

Why Star?

• Simple joins for queries like “Total call costs for premium plan users in Kathmandu during peak hours.”
• Scalable for NTC’s large call volume.