Data Warehousing: Architecture, ETL, and Business Use Cases

TAKEAWAYS:

• A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile repository that stores historical data for analytical processing, unlike operational databases.
• The three-tier architecture (data sources → ETL → data warehouse → front-end tools) ensures scalability, performance, and security in BI systems.
• ETL (Extract, Transform, Load) processes clean, integrate, and load data into the warehouse, while ELT (Extract, Load, Transform) is gaining traction in cloud-based systems.
• Star and snowflake schemas optimize query performance for OLAP, but require careful denormalization to balance speed and storage.
• Data marts are department-specific subsets of a data warehouse, improving agility but risking inconsistency if not aligned with the enterprise model.
• Real-world applications include Nabil Bank’s fraud detection, Daraz’s customer segmentation, and NTC’s network performance analytics.

1. What is a Data Warehouse?

A data warehouse (DW) is a centralized, structured repository designed to support business intelligence (BI) and analytical processing. Unlike transactional databases (OLTP), it is:

• Subject-oriented: Organized by business themes (e.g., sales, customers).
• Integrated: Consolidates data from multiple sources (ERP, CRM, IoT).
• Time-variant: Stores historical data for trend analysis.
• Non-volatile: Data is never updated or deleted; only new data is added.

Why Do We Need Data Warehouses?

Operational databases (e.g., SAP, Oracle) are optimized for transactions (CRUD operations), not analytics. A DW solves this by:

• Separating analytics from operations (no performance impact on live systems).
• Enabling complex queries (e.g., "What was the sales trend in Kathmandu over 5 years?").
• Supporting data consistency across departments.

2. Data Warehouse Architecture

The three-tier architecture is the standard model:

graph TD
    A["Data Sources"] -->|"Extract"| B["ETL Layer"]
    B -->|"Transform"| C["Data Warehouse"]
    C -->|"Query"| D["Front-End Tools (BI, Dashboards)"]

Key Components:

1. Data Sources:

   • Internal: ERP (e.g., SAP), CRM (e.g., Salesforce), SCM (e.g., Oracle).
   • External: Web logs, social media, IoT sensors (e.g., NTC’s network devices).
   • Example: Daraz’s DW pulls data from its e-commerce platform, payment gateways (Khalti), and logistics (Pathao).

2. ETL Layer:

   • Extract: Pulls raw data from sources (e.g., daily sales from Daraz’s database).
   • Transform: Cleans, standardizes, and enriches data (e.g., converting currency to NPR, handling missing values).
   • Load: Writes data into the warehouse (e.g., daily snapshots into a "sales_fact" table).

3. Data Warehouse:

   • Stores fact tables (quantitative data, e.g., sales amounts) and dimension tables (descriptive attributes, e.g., product name, date).
   • Example: Nabil Bank’s DW stores loan applications (fact) linked to customer details (dimension).

4. Front-End Tools:

   • BI tools (Power BI, Tableau), SQL clients, or custom dashboards.
   • Example: NEPSE’s stock analysis dashboard uses DW data to show historical price trends.

3. Data Warehouse Schemas

Two dominant designs optimize query performance:

A. Star Schema

• Simplest design: one fact table connected to multiple dimension tables via foreign keys.
• Pros: Fast queries, easy to understand.
• Cons: Redundancy in dimension tables.

Worked Example: Daraz’s Sales Analysis

• Fact Table: sales (columns: sale_id, amount, date_id, product_id, store_id, customer_id).
• Dimension Tables:
  • date_dim (columns: date_id, year, month, day, quarter).
  • product_dim (columns: product_id, name, category, price).
• Query: "Show total sales of electronics in Kathmandu for Q1 2024."

  SELECT SUM(amount) AS total_sales
  FROM sales
  JOIN product_dim ON sales.product_id = product_dim.product_id
  JOIN store_dim ON sales.store_id = store_dim.store_id
  JOIN date_dim ON sales.date_id = date_dim.date_id
  WHERE product_dim.category = 'Electronics'
    AND store_dim.city = 'Kathmandu'
    AND date_dim.quarter = 1 AND date_dim.year = 2024;
  

B. Snowflake Schema

• Normalized dimension tables (sub-dimensions split into separate tables).
• Pros: Reduces redundancy, saves storage.
• Cons: Slower queries due to joins.

Comparison Table: Star vs. Snowflake

4. Data Marts vs. Data Warehouses

A data mart is a subset of a DW tailored for a specific department or function.

Real-World Example: Nabil Bank

• Enterprise DW: Stores all customer transactions, loans, and ATM data.
• Data Marts:
  • Fraud Detection Mart: Focuses on suspicious transactions (e.g., sudden large withdrawals).
  • Customer Service Mart: Tracks complaints and resolution times.

5. ETL vs. ELT: What’s the Difference?

Example: Khalti’s Transaction Processing

• ETL: Khalti’s legacy system transforms raw payment data (e.g., converting USD to NPR) before loading into the DW.
• ELT: A modern cloud setup (e.g., using Snowflake) loads raw data first, then applies transformations for real-time fraud detection.

6. Real-World Applications of Data Warehousing

A. Nabil Bank: Fraud Detection

• Problem: High-volume transactions require real-time monitoring.
• Solution: DW integrates transaction logs, customer profiles, and historical fraud patterns.
• Outcome: Reduces false positives in fraud alerts by 40%.

B. Daraz: Customer Segmentation

• Problem: Personalized marketing needs granular customer data.
• Solution: DW combines purchase history, browsing behavior, and demographics.
• Outcome: Targeted discounts increase repeat purchases by 25%.

C. NTC: Network Performance Analytics

• Problem: Identifying congestion hotspots in Kathmandu’s fiber network.
• Solution: DW aggregates call detail records (CDRs), weather data, and traffic patterns.
• Outcome: Predictive maintenance reduces downtime by 30%.

D. Pathao: Driver Performance Tracking

• Problem: Optimizing driver routes and ride pricing.
• Solution: DW stores GPS data, ride durations, and surge pricing rules.
• Outcome: Dynamic pricing adjusts in real-time based on demand.

7. Challenges in Data Warehousing

8. Emerging Trends

1. Cloud Data Warehouses:

   • Example: Google BigQuery, Amazon Redshift.
   • Benefit: Pay-as-you-go pricing, auto-scaling.

2. Real-Time Data Warehouses:

   • Example: Apache Kafka + Snowflake.
   • Use Case: NTC’s real-time network monitoring.

3. AI/ML Integration:

   • Example: AutoML in Tableau for predictive analytics.
   • Use Case: Daraz’s demand forecasting.

4. Data Lakes + Warehouses:

   • Example: Delta Lake (Databricks).
   • Benefit: Store raw and processed data in one place.

In the Real World

1. Nabil Bank’s Loan Approval System

   • Idea Used: Data Warehouse + Predictive Analytics
   • How: The bank’s DW integrates customer credit scores, income data, and loan history. An ML model (trained on historical data) predicts default risk before approval. For example, a customer with a 75% approval probability gets a lower interest rate than one with 50% probability.
   • Impact: Reduced non-performing loans (NPLs) by 15% in 2023.

2. Daraz’s "Frequently Bought Together" Recommendations

   • Idea Used: OLAP + Association Rule Mining
   • How: Daraz’s DW stores every product view and purchase. Using OLAP cubes, the system identifies patterns like "Customers who buy iPhones also buy AirPods." This is displayed on product pages to boost cross-selling.
   • Impact: Increased average order value (AOV) by 20% in Kathmandu.

3. Pathao’s Surge Pricing Algorithm

   • Idea Used: Real-Time Data Warehouse + Time-Series Analysis
   • How: Pathao’s DW ingests real-time data on driver availability, traffic (from Google Maps API), and ride demand. During peak hours (e.g., 6–8 PM in Thapathali), the system dynamically adjusts prices by 2–3x to balance supply and demand.
   • Impact: Reduced driver wait times by 40% and increased revenue by 18%.

4. NTC’s Fiber Network Outage Prediction

   • Idea Used: Historical Data Warehouse + Weather Integration
   • How: NTC’s DW combines call logs, network device logs, and weather data (humidity, temperature). A predictive model flags high-risk areas (e.g., hilly regions in Pokhara) where outages are likely during monsoons.
   • Impact: Proactive repairs reduced outage duration by 25%.

5. Khalti’s Anti-Money Laundering (AML) System

   • Idea Used: Data Marts + Graph Analytics
   • How: Khalti’s AML data mart links transactions, beneficiary details, and suspicious activity reports. Graph algorithms detect unusual patterns (e.g., a user sending small amounts to 50 different accounts in a day).
   • Impact: Flagged 30% more suspicious transactions in 2023.

Exam Tip

How This Unit is Tested

1. Definitions & Concepts (20–30%):

   • Expect questions on data warehouse vs. database, star vs. snowflake schema, and ETL vs. ELT.
   • Example Question: "Differentiate between a data mart and a data warehouse, giving one Nepali business example for each."

2. Architecture & Components (30–40%):

   • Draw and explain the three-tier architecture.
   • Identify components in a given scenario (e.g., "Which layer handles data cleansing in NTC’s DW?").
   • Example Question: "Label the missing components in the following data warehouse architecture diagram (showing data sources, ETL, DW, and front-end)."

3. Worked Examples & SQL (20–30%):

   • Write SQL queries to extract data from a star schema (e.g., "Find top 5 products by sales in 2023").
   • Trace ETL steps for a real scenario (e.g., "How would Daraz load daily sales data into its DW?").
   • Example Question: "Given a sales_fact table and product_dim table, write a query to find the total sales of electronics in Kathmandu for Q1 2024."

4. Case Studies & Applications (10–20%):

   • Explain how a Nepali company (e.g., Nabil Bank, Daraz, NTC) uses DW for BI.
   • Compare two schemas (star vs. snowflake) for a given use case.
   • Example Question: "How does Pathao use a data warehouse to implement surge pricing? Draw a simplified architecture."

5. Challenges & Trends (10%):

   • Discuss scalability issues in DWs or emerging trends like cloud DWs.
   • Example Question: "What are the challenges of implementing a data warehouse for a small business like a local bakery in Pokhara? Suggest solutions."

Top 5 Exam Strategies

1. Memorize the Three-Tier Architecture: Always draw it in exams.
2. Practice SQL on Star Schemas: Use sample tables from textbooks.
3. Relate to Nepali Companies: Link every concept to Nabil Bank, Daraz, or NTC.
4. Compare Star vs. Snowflake: Know when to use each (speed vs. storage).
5. ETL Steps: Break it into Extract → Clean → Transform → Load for any scenario.

Sample Exam Question & Answer

Question: "Explain the role of a data warehouse in Nabil Bank’s fraud detection system. Draw a simplified architecture and describe the ETL process for transaction data."

Answer: Nabil Bank uses a data warehouse to centralize transaction data, customer profiles, and historical fraud patterns for real-time monitoring. The system flags suspicious activities (e.g., sudden large withdrawals) using ML models trained on DW data.

graph TD
    A["Sources"] -->|"Extract"| B["ETL Layer"]
    B -->|"Transform"| C["Data Warehouse"]
    C -->|"Query"| D["Fraud Detection Model"]
    C -->|"Dashboard"| E["Security Team"]

ETL Process:

1. Extract: Pull transaction logs from core banking systems, ATMs, and mobile apps every hour.
2. Transform:
   • Clean: Remove duplicates, handle missing values (e.g., null merchant IDs).
   • Enrich: Join with customer dimension (credit score, transaction history) and location dimension (branch/ATM risk profile).
   • Flag: Apply rules (e.g., "withdrawal > 50% of daily limit = high risk").
3. Load: Write to the DW’s transactions_fact table with a risk_score column.
4. Analyze: The fraud model (e.g., a random forest classifier) queries the DW to predict fraud probability in real-time.

Impact: Reduced false positives by 40% and detected 25% more fraud cases in 2023.