ETL Processes: Extraction, Transformation, Loading & BI Pipelines

What is ETL?

ETL stands for Extract, Transform, Load: the three-step process that moves raw data from source systems into a structured data warehouse for analysis.

The ETL Pipeline

flowchart LR
    A["Source Systems\n(ERP, CRM, IoT, etc.)"] -->|"Extract"| B["Staging Area\n(Raw Data Lake)"]
    B -->|"Transform"| C["Data Cleansing\nDeduplication\nData Enrichment"]
    C -->|"Load"| D["Target\n(Data Warehouse/DB)"
    D -->|"Query"| E["BI Tools\n(Power BI, Tableau)"]

Key Idea: ETL is the backbone of BI—without it, data remains siloed and unusable.

In the Real World

1. Ncell’s Customer Churn Prediction Ncell extracts call logs and payment data from its CRM, transforms it to identify inactive users, and loads it into a dashboard to trigger retention campaigns.

2. Daraz’s Order Fulfillment Daraz’s ETL pipeline extracts order data from its e-commerce platform, transforms it to calculate delivery routes, and loads it into a logistics system to optimize Pathao driver assignments.

3. Nepal Rastra Bank’s Financial Monitoring NRB extracts transaction data from banks, transforms it to detect suspicious patterns (e.g., money laundering), and loads it into a fraud-detection system.

Step 1: Extract

How It Works

Extracting data involves pulling it from source systems (e.g., databases, APIs, flat files) into a staging area. Methods include:

• Batch Extraction: Scheduled pulls (e.g., nightly bank transactions).
• Real-time Extraction: Streaming data (e.g., IoT sensors in smart grids).

Example: Khalti’s Transaction Data

Worked Trace: Khalti extracts 10,000 daily transactions from its payment gateway. The ETL tool flags duplicates (e.g., same transaction ID) and converts foreign currencies to NPR before loading.

Step 2: Transform

Key Transformations

Example: NTC’s Network Performance

NTC extracts raw call-drop logs from its towers. The ETL tool:

1. Converts timestamps to UTC.
2. Aggregates drops by tower location.
3. Flags towers with >5% failure rates for maintenance.

Step 3: Load

Loading Strategies

Example: NEPSE’s Stock Data

NEPSE loads daily stock prices into its BI system via incremental load:

• Extracts only new trades from its database.
• Transforms to calculate moving averages.
• Loads into a dashboard for traders.

ETL Tools in Nepal

Challenges & Solutions

Exam Tip

1. Diagram the ETL Flow: Always draw a pipeline (like the Mermaid above) in exams—it’s worth 5+ marks.
2. Tool vs. Technique: Know when to use ETL tools (batch) vs. ELT (cloud, real-time).
3. Real-World Tie: Link ETL to Nepal’s context (e.g., "How would NTC use ETL to reduce call drops?").
4. SQL + ETL: Expect questions on SQL transformations (e.g., JOIN, CASE WHEN) in ETL scripts.

Case Study: Chaudhary Group’s Supply Chain

How ETL Helps:

1. Extracts real-time stock levels from IoT sensors.
2. Transforms to predict demand using historical sales.
3. Loads alerts into the warehouse manager’s app to reorder before shortages.

Summary Table

Final Note: ETL is the bridge between raw data and actionable insights. Master it, and you’ll ace BI implementation questions!