ETL Processes: Extraction, Transformation, Loading & Data Integration

What is ETL?

ETL stands for Extract, Transform, Load—the three-step process to move raw data from source systems into a data warehouse or data lake for analysis. It is the backbone of Business Intelligence (BI), enabling organizations to turn scattered data into actionable insights.

graph LR
  A["Source Systems
(SAP, Excel, APIs, etc.)"] -->|Extract| B["ETL Process"]
  B -->|"Transform"| C["Clean, Enrich, Aggregate
(Example: Deduplicate, Format, Join Tables)"]
  C -->|"Load"| D["Data Warehouse
(Snowflake, Redshift, BigQuery)"]
  D -->|"Analyze"| E["BI Tools
(Power BI, Tableau, Looker)"]

Simplified ETL pipeline with transformation details Key Idea: ETL ensures data is consistent, structured, and ready for analytics.

1. The Three Phases of ETL

A. Extraction

• Definition: Pulling data from source systems (databases, APIs, flat files, cloud services).
• Methods:
  • Full Extraction: Copy all data (used for initial loads).
  • Incremental Extraction: Only fetch new/changed data (faster, used for updates).
  • Real-time Extraction: Streaming data (e.g., IoT sensors, transaction logs).

Real-World Example:

• Nepal Rastra Bank (NRB) extracts daily transaction data from banks (Nabil, Global IME) to monitor inflation and economic trends.
• Daraz extracts customer order data from its e-commerce platform to update inventory in real time.

mindmap
  root((Extraction Methods))
    Full Extraction
      "All data at once
      (e.g., monthly bank statements)"
    Incremental Extraction
      "Only new/changed data
      (e.g., daily transaction logs)"
    Real-time Extraction
      "Continuous stream
      (e.g., IoT sensor data)
      --> NRB Example: Daily bank transactions
      --> Daraz Example: Order updates
      --> NTC Example: Call detail records"

B. Transformation

• Definition: Cleaning, structuring, and enriching raw data to fit the target schema.
• Common Tasks:
  • Data Cleaning: Fixing errors (missing values, duplicates).
  • Data Integration: Merging data from multiple sources (e.g., customer data from CRM + sales data from ERP).
  • Data Enrichment: Adding derived fields (e.g., calculating profit margins from sales and cost data).
  • Data Aggregation: Summarizing data (e.g., monthly sales instead of daily).
  • Data Conversion: Changing formats (e.g., JSON to CSV).

Worked Example: Nabil Bank Loan Data

• Source: Loan application data (income, credit score, loan amount).
• Transformation Steps:
  1. Clean: Remove incomplete applications.
  2. Enrich: Add risk score using a formula: Risk Score = (Income Stability × 0.4) + (Credit Score × 0.5) + (Loan Amount × 0.1)
  3. Aggregate: Group loans by region to identify high-risk areas.

C. Loading

• Definition: Writing transformed data into the target system (data warehouse, database, or data lake).
• Methods:
  • Batch Loading: Scheduled updates (e.g., nightly loads).
  • Real-time Loading: Immediate updates (e.g., stock market data).
  • Trickle Loading: Small, frequent updates (e.g., social media analytics).

Real-World Example:

• NTC (Nepal Telecom) loads call detail records (CDRs) into a data warehouse to analyze network usage patterns and predict outages.
• Pathao loads driver and rider location data in real time to optimize route matching.

flowchart TD
  A["Load Methods"] --> B["Batch Loading
  (Scheduled: Hourly/Daily)"]
  A --> C["Real-time Loading
  (Immediate: Stock prices, CDRs)"]
  A --> D["Trickle Loading
  (Small updates: Social media analytics)"]
  B -->|"Example"| E["Nepal Rastra Bank
  Monthly economic reports"]
  C -->|"Example"| F["Pathao
  Driver location updates"]
  D -->|"Example"| G["NTC
  Network usage analytics"

2. ETL Tools and Technologies

3. Challenges in ETL

4. ETL vs. ELT: Key Differences

While ETL transforms data before loading, ELT (Extract-Load-Transform) loads raw data first, then transforms it in the target system (e.g., cloud data warehouses like Snowflake).

Real-World Example:

• Nepal Stock Exchange (NEPSE) uses ELT to load raw stock data into Google BigQuery, then transform it for real-time analytics.

5. ETL in Action: Case Study – Daraz Nepal

Problem: Daraz needed to analyze customer purchase patterns across multiple warehouses to optimize inventory and reduce shipping costs.

Solution:

1. Extract: Pull order data from MySQL databases and APIs (real-time and batch).
2. Transform:
   • Clean: Remove canceled orders.
   • Enrich: Add customer segmentation (e.g., "Prime Members," "First-Time Buyers").
   • Aggregate: Calculate monthly sales by product category.
3. Load: Store in Snowflake data warehouse.
4. Analyze: Use Tableau to visualize trends (e.g., "Electronics sales spike in June").

Result:

• Reduced overstocking by 20%.
• Improved delivery times by 15% using route optimization.

flowchart TD
    A["Daraz Order Data\n(MySQL, APIs)"] -->|"Extract"| B["Clean & Enrich\n(Python, Talend)"]
    B -->|"Transform"| C["Snowflake Data Warehouse"]
    C -->|"Analyze"| D["Tableau Dashboards\n(Sales Trends)"]

## In the Real World

1. Nabil Bank – Loan Risk Assessment

   • ETL Idea Used: Data Transformation (cleaning loan applications, calculating risk scores).
   • How: Raw loan data is extracted from core banking systems, transformed to compute risk scores, and loaded into a data warehouse. Analysts use these scores to approve/reject loans.
   • Impact: Reduced default rates by 12% in 2023.

2. Daraz – Inventory Optimization

   • ETL Idea Used: Incremental Extraction + Aggregation.
   • How: Daraz extracts real-time order data and historical sales trends, transforms it to predict demand, and loads it into a dashboard. This helps warehouses stock only high-demand products.
   • Impact: Cut warehouse costs by 18% in 2022.

3. NTC – Network Performance Monitoring

   • ETL Idea Used: Real-time Loading + Data Aggregation.
   • How: NTC’s ETL pipelines pull call detail records (CDRs) and network logs in real time, transform them to detect congestion, and load aggregated data into a Grafana dashboard. Engineers use this to predict and prevent outages.
   • Impact: Reduced network downtime by 30% in 2023.

## Exam Tip

1. Define ETL Clearly:

   • Always explain Extract, Transform, Load in your own words. Examiners check if you understand the purpose of each phase.

2. Compare ETL vs. ELT:

   • Expect a short-answer or long-answer question on differences. Use the table above but add one real-world example (e.g., "NEPSE uses ELT for stock data").

3. Worked Examples Are Key:

   • Always solve a numerical or scenario-based question (e.g., "How would you design an ETL pipeline for Nabil Bank’s loan data?").
   • Show steps: Extraction source → Transformation logic → Loading target.

4. Tools and Challenges:

   • Name 2 ETL tools (e.g., Informatica, Talend) and 2 challenges (e.g., data quality, performance) with Nepali examples.

5. Diagrams Save Marks:

   • Draw a simple ETL flowchart (like the one above) in exams. Even if not asked, it proves you understand the process flow.

## Quick Revision Checklist

• Can you list 3 extraction methods and give a Nepali example for each?
• What are 4 transformation tasks? Give one formula (e.g., risk score).
• How does batch loading differ from real-time loading? (Use NTC or Pathao as an example.)
• Draw an ETL flowchart for a scenario (e.g., "ETL for a school’s student attendance data").
• What is ELT, and why might a company like NEPSE prefer it over ETL?