CSE Roadmap

Specialist tier · about 2 hours · 6 parts

Part 3 of 6 · 2 min read

ETL

Think of ETL like moving into a new house. First a mover collects the boxes from the old place (extract). Then things get sorted and repacked to fit the new shelves, and anything broken or duplicated gets thrown out (transform). Finally the boxes go onto the new shelves (load).

In plain words: Extract, transform, load is the three-step process that fills a warehouse. Extraction pulls data out of each source system. Transformation cleans it up and converts it to the warehouse's schema: fixing misspelled names, correcting inconsistent addresses, removing duplicate records from different sources (deduplication), and converting units so every source agrees. Load writes the cleaned, converted data into the warehouse tables. Some newer systems reorder this to extract, load, transform (ELT): raw data lands in the warehouse first, and transformation happens afterward using the warehouse's own processing power. Either way, this usually runs on a schedule rather than instantly, so warehouse data is typically some hours old, not live.

A worked example. Each night at 2 AM, a job extracts yesterday's rows from orders and tickets, converts total_cents into dollars for the warehouse's sales_fact table, drops any ticket row that is an exact duplicate of one already loaded, and writes the result into the warehouse. A manager querying the warehouse at 9 AM sees everything through yesterday, not this morning's orders yet.

The key lines.

-- A simplified transform-and-load step, run nightly against the warehouse
INSERT INTO sales_fact (order_id, customer_id, order_date, total_dollars)
SELECT order_id, customer_id, order_date, total_cents / 100.0
FROM staging_orders
WHERE order_id NOT IN (SELECT order_id FROM sales_fact);

ETL, data cleansing

ETL is the pipeline that extracts data from source systems, transforms it to fit the warehouse's schema, and loads it into the warehouse; ELT runs the same three steps with load before transform. Data cleansing is the part of transformation that fixes errors, such as misspelled names or inconsistent addresses, and removes duplicate records collected from more than one source.

Example: the nightly job above is a tiny ETL pipeline: it extracts from staging_orders, transforms cents to dollars, and loads only the rows not already present.