SQL Lab — ETL and Data Import

🖥️

SQL Terminal

📦

Data Sources

🔄

Pipeline Status

📌 PIPELINE STAGES

○Create staging tables

○Load CSV data (LOAD DATA)

○Merge with INSERT SELECT

○Handle NULLs with COALESCE

○Deduplicate with ON DUPLICATE KEY

○Validate row counts match

0 / 6 complete

SQL TERMINAL — etl_db (staging)

MySQL 8.0 — etl_db (staging environment) Three source systems: CRM, ERP, eCommerce platform Task: Extract, transform and merge 3 sources into unified customers table ⚠ Sources use different schemas and NULL conventions — normalise before merge ──────────────────────────────────────

mysql>

DATA SOURCE MANIFEST

CRM System (Salesforce export)

File: crm_export.csv (2,841 rows)
Columns: customer_id, name, email, phone, country
Issues: phone numbers as text, some NULL emails

PENDING

ERP System (SAP extract)

File: erp_customers.csv (1,940 rows)
Columns: erp_id, full_name, email_addr, region
Issues: no phone field, uses 'N/A' for missing emails

PENDING

eCommerce Platform

Table: ecom_db.users (3,120 rows)
Columns: uid, display_name, email, signup_date
Issues: duplicates with CRM data (same email)

PENDING

ETL PIPELINE PROGRESS

01Create staging tables (staging_crm, staging_erp, staging_ecom)○

02LOAD DATA INFILE into staging tables○

03INSERT SELECT with COALESCE and NULL handling○

04ON DUPLICATE KEY UPDATE to handle cross-source dupes○

05Row count validation — source total vs unified table○

MISSION BRIEFING

ETL & DATA IMPORT — FUSION RETAIL GROUP

SCENARIO

Fusion Retail Group has just acquired two companies. They now have customer data scattered across three systems: their own CRM, the acquired ERP, and an eCommerce platform. All three use different schemas. Your job is to build an ETL pipeline that stages all three sources, transforms and normalises the data, and merges them into a single unified customers table — handling NULLs, deduplicating by email, and validating final row counts.

PIPELINE STEPS

CREATE TABLE staging_crm (id INT, name VARCHAR(100), email VARCHAR(100), phone VARCHAR(20), country CHAR(2));
LOAD DATA INFILE '/tmp/crm_export.csv' INTO TABLE staging_crm FIELDS TERMINATED BY ',' IGNORE 1 ROWS;
INSERT INTO customers (name, email, phone, country) SELECT name, NULLIF(email,'N/A'), COALESCE(phone,'Unknown'), country FROM staging_crm;
INSERT INTO customers ... ON DUPLICATE KEY UPDATE name = VALUES(name);
SELECT COUNT(*) FROM customers; — validate total

KEY FUNCTIONS

COALESCE(a, b, c) — returns first non-NULL value
NULLIF(val, 'N/A') — convert sentinel strings to NULL
ON DUPLICATE KEY UPDATE — upsert on unique constraint
LOAD DATA INFILE — fast bulk import from CSV

Display Mode