ETL vs ELT and the Data Pipeline

It's 3:40 am and your phone buzzes. The nightly load failed at step six of nine, half the fact rows are in, and the CEO's dashboard refreshes at 6. If your pipeline was built the way this lesson describes, you re-run it and go back to sleep. If it wasn't, you're awake until breakfast. The pipeline fills the layers from Lesson 3, on a schedule, with checks. Its two classic shapes are ETL and ELT, and the order of those letters changes the tools you use, the money you spend and whether you can fix mistakes later.

The three verbs

• Extract: pull data out of a source. A SQL query against the POS database, a CSV export from the ERP, an API call to the courier, a file dropped on a shared folder.
• Transform: clean it (trim, cast types, fix dates), integrate it (map codes, look up keys), apply rules (net = gross minus returns), and reshape it into facts and dimensions.
• Load: write it into the target tables, either replacing everything (full load) or adding and updating (incremental load).

ETL versus ELT

	ETL	ELT
Where transformation runs	A separate ETL server or tool (SSIS, Informatica, Talend, Python scripts)	Inside the warehouse, using its SQL engine (dbt, stored procedures, Spark on a lakehouse)
What lands in the warehouse	Only clean, modelled data	Raw data first, then modelled tables built from it
Raw history	Usually not kept	Kept; you can re-transform old data when rules change
Era and reason	1990s to 2010s, when warehouse compute was the expensive part	Cloud era, when storage is cheap and the warehouse scales compute on demand
Skill needed	Tool-specific drag-and-drop plus scripting	Mostly SQL, with version control
Still common in	On-premise SQL Server / Oracle shops, banks	Snowflake, BigQuery, Synapse, Databricks projects

The practical difference shows up the day the business changes a rule. Say finance decides delivery charges no longer count as net sales, and they want history restated. In ETL, the raw delivery charge was thrown away during transformation, so you have to re-extract three years from the source (if the source still has it). In ELT the raw rows are still in the warehouse. You change one SQL model and rebuild. We've lived through both versions of that day, and only one of them ended on time.

Full load versus incremental load

A full load truncates the target and reloads everything. Simple, safe, fine for a 5,000-row product list, hopeless for 500 million sales rows. An incremental load picks up only what changed since the last run, which means the pipeline needs a way to spot changes.

• Timestamp column: WHERE updated_at > :last_run_time. Easiest, but it relies on the source maintaining the column honestly, and it misses hard deletes. One ERP we extracted from updated updated_at on screen edits but not on bulk imports, so a whole price revision slipped past us for a week.
• Change data capture (CDC): read the database's own transaction log (SQL Server CDC, Debezium, Fivetran). Catches inserts, updates and deletes with no load on the source tables.
• Snapshot comparison: load the whole source into staging and diff it against yesterday's copy. Works for any source, costly for large ones.

Whatever the method, a good pipeline is idempotent. Run it twice for the same day and you get the same result, not duplicated rows. The usual trick is to load by merge (insert if new, update if changed) rather than plain insert. That's the difference between a re-run and a disaster at 3:40 am.

A night in the life of a pipeline

The diagram shows a typical order. Dimensions load before facts, because every fact row has to look up its dimension keys, and a product that sold for the first time yesterday must already exist in dim_product. Marts build after facts. BI refreshes last. An orchestrator (Apache Airflow, Azure Data Factory, Dagster, or cron plus a shell script in smaller shops) runs the steps in order, retries failures, alerts someone, and records row counts for every step. On most of our Pakistani projects that alert goes to a WhatsApp group, because nobody reads email at 4 am.

An incremental merge, line by line

-- Step 1: extract only yesterday's changes into staging (done by the extract tool)
-- stg.pos_sales now holds rows where updated_at >= '2025-08-14'

-- Step 2: transform + load facts with a merge, so re-runs are safe
INSERT INTO dw.fact_sales
      (date_key, store_key, product_key, customer_key,
       receipt_no, line_no, quantity, net_amount)
SELECT TO_CHAR(TO_DATE(s.dt, 'DD/MM/YYYY HH24:MI'), 'YYYYMMDD')::INT,
       st.store_key,
       p.product_key,
       COALESCE(c.customer_key, 0),            -- 0 = unknown customer
       s.txn_no,
       s.line_no::SMALLINT,
       s.qty::INT,
       REPLACE(s.amt, ',', '')::NUMERIC(12,2)
FROM stg.pos_sales s
JOIN dw.dim_store    st ON st.store_code   = TRIM(s.shop)
JOIN dw.dim_product  p  ON p.sku           = s.item AND p.is_current
LEFT JOIN dw.dim_customer c ON c.phone     = s.cust_ph AND c.is_current
ON CONFLICT (receipt_no, line_no) DO UPDATE
  SET quantity   = EXCLUDED.quantity,
      net_amount = EXCLUDED.net_amount;

-- Step 3: record the outcome for the orchestrator
INSERT INTO ops.load_log (step, run_date, rows_affected)
VALUES ('fact_sales', CURRENT_DATE, (SELECT COUNT(*) FROM stg.pos_sales));

Three things to notice. The ugly cleaning (TRIM, REPLACE, date parsing) happens exactly here and nowhere else. An unmatched customer becomes key 0 rather than dropping the sale. And ON CONFLICT makes a second run harmless. The extract step is usually a short Python script; if you've never written one, start with our free Python track.

A Daraz seller's first pipeline: a Lahore seller with 12,000 orders a month pulled order data into Excel by hand until the file stopped opening. Their first pipeline was a Python script on a PKR 2,000-a-month cloud VM. It pulled from the seller API every hour, loaded raw JSON into PostgreSQL (ELT), and a handful of SQL views built a sales fact on top. Six months later they moved the same SQL to BigQuery with almost no changes, because the transformation logic lived in SQL, not in a tool. That portability is the quiet argument for ELT that vendor slides never mention.

Batch, micro-batch and streaming

Nightly batch is still the default, and it's enough for most management reporting. Micro-batch (every 5 to 15 minutes) suits fraud, stock-outs and delivery tracking. True streaming (Kafka, event by event) is only needed when seconds matter. Each step up costs more engineering. Start with batch and shorten the interval when a business need proves it, not when a vendor demo does.

What to carry forward

• ETL transforms before loading, on a separate tool. ELT loads raw first and transforms in the warehouse with SQL, keeps raw history for replay, and is the cloud default.
• Incremental loads need change detection (timestamps, CDC or snapshot diff) and must be idempotent, usually via merge.
• Dimensions, then facts, then marts, then BI refresh, under an orchestrator that logs and alerts.
• Start with nightly batch and shorten the interval only when the business proves the need.

Homework for tonight

1. The finance team changes the definition of net sales and wants three years restated. Describe what happens in an ETL pipeline versus an ELT pipeline.
2. List the steps and order for a nightly load of a school warehouse with dim_student, dim_class, dim_date and fact_attendance. Which step must come first and why?
3. Explain what would go wrong if the merge above used a plain INSERT and the orchestrator retried the step after a network failure halfway through.