Part C: ETL Pipeline (Extract, Transform, Load)

File: loadAnalyticsDB.PractII.VarmaA.R

Purpose: The "data factory" - reads 1.18M transactions from SQLite + CSV, cleans them, aggregates them, and loads into our analytics warehouse.

Analogy: Like a factory assembly line - raw materials (messy data) go in one end, finished products (clean aggregated data) come out the other.

🎯 ETL Flow Overview

SQLite (1.08M rows)  ┐
                     ├──→ Extract → Transform → Aggregate → Load → MySQL (5K rows)
CSV (98K rows)       ┘

Key Transformation: 1,181,863 raw transactions → 5,427 daily summaries

🔧 The Core ETL Process

Step 1: Extract (Read from sources)

• SQLite: 1,083,131 streaming transactions
• CSV: 98,732 supplemental transactions
• Challenge: Can't load all at once (memory crash)
• Solution: Batch processing (50,000 rows at a time)

Step 2: Transform (Clean and enrich)

• Map user_id → country_id (using lookup table)
• Map asset_id → sport (using lookup table + inference)
• Filter out records with missing data
• Handle NAs in numeric fields