SQL for ETL

What is ETL?

ETL stands for Extract, Transform, Load.

• Extract: Get data from source.
• Transform: Clean and format data.
• Load: Save to destination (Data Warehouse).

SQL is heavily used in the Transform stage (ELT pattern).

Data Cleaning Techniques

1. Handling NULLs

Replace missing values with a default.

SELECT COALESCE(PhoneNumber, 'Unknown') as Contact FROM Users;

2. Formatting Strings

Standardize text data (e.g., lowercase emails).

UPDATE Users SET Email = LOWER(TRIM(Email));

3. Dealing with Duplicates

Remove duplicate records based on specific columns.

DELETE FROM Users
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM Users
    GROUP BY Email
);

4. Data Type Conversion

Cast strings to numbers or dates.

SELECT CAST(OrderDateString AS DATE) as OrderDate FROM RawOrders;