ETL vs ELT

Context: FIT3003_MOC · the write path of the Data Warehouse delivery chain · the unit’s labs are ETL (Model 3: PL/SQL builds the TempFact, then the fact); a Data Lakehouse is ELT · (W12 emerging-topics deck — not a book chapter) Parent Framework: Data Engineering

Quick Revision

• 🎯 Objective: both move source data into an analytical platform ➔ they differ only in where and when the transform runs — before the load (ETL) or inside the target after it (ELT).
• 📦 Core Components: ETL ➔ transform in a staging step | ELT ➔ land raw, transform with the target’s compute | extraction ➔ batch pull · file landing · CDC | conflict rule ➔ latest timestamp, then source priority.
• ⚡ Key Constraint: they are complementary, not rivals — the choice depends on workload, architecture and constraints; say why the target’s compute or the staging step should do the work.

📝 How It Works

1. The two orders

• ETL ➔ Extract from sources ➔ Transform and validate ➔ Load the prepared data into the warehouse.
• ELT ➔ Extract ➔ Load into the target platform ➔ Transform there, using the platform’s own processing.
• Modern pipeline capabilities (either order) ➔ incremental loading · workflow orchestration · data-quality testing · error handling and recovery · monitoring and lineage.

2. Extraction — three ways in

• Batch pull ➔ JDBC read $+$ an incremental filter (WHERE login_time > last_load_time) so each run takes only new rows.
• File landing ➔ Auto Loader watches S3 / ADLS and streams each new file into Raw.
• CDC (change data capture) ➔ Debezium ➔ Kafka: every operational insert/update becomes an event — the streaming form of the active warehouse idea (Ch 18).

3. Conflict resolution — Raw ➔ Cleaned

• Rule ➔ for each key, the latest timestamp wins; ties broken by source priority.
• Concurrent writers ➔ Delta’s optimistic concurrency: two writers proceed, the loser retries.

⚙️ Core Implementation

🔹 File landing into Raw (Spark Auto Loader)

Stream new CSV files into raw.lab_activities

(spark.readStream.format('cloudFiles')
   .option('cloudFiles.format', 'csv')
   .option('cloudFiles.schemaLocation', '/chk/lab')
   .load('s3://uni-raw/lab_activities/')
 .writeStream
   .option('checkpointLocation', '/chk/lab')
   .toTable('raw.lab_activities'))
# batch alternative: spark.read.format('jdbc')
#   ... WHERE login_time > last_load_time

💡 Common Mistake: No checkpoint ➔ the checkpoint location is what remembers which files were already loaded; without it a restart re-ingests everything.

🔹 Conflict resolution into Cleaned (Delta MERGE)

Keep one current row per StudentID

MERGE INTO cleaned.student s
USING (SELECT * FROM raw.student_raw
  QUALIFY ROW_NUMBER() OVER (PARTITION BY StudentID
    ORDER BY updated_at DESC, source_priority) = 1) u
ON s.StudentID = u.StudentID
WHEN MATCHED AND u.updated_at > s.updated_at
  THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

💡 Common Mistake: Reading QUALIFY as Oracle ➔ this is Spark/Delta SQL; in Oracle the same filter is an inline view with row_number() … as rn and an outer where rn = 1 ➔ OLAP Ranking and Top-N.

⚖️ Core Decision Matrix

Approach	Transform runs	What lands in the target	Fits	Unit example
ETL	in a staging step before load	already cleaned, conformed, aggregated data	a warehouse engine that should hold only curated tables	Oracle Model 3: Temp DB ➔ PL/SQL ➔ star
ELT	inside the target, after load	raw data first, transformed in place	a platform with elastic compute that also keeps raw data (lake/lakehouse)	Raw ➔ Cleaned ➔ Facts & Dims on Spark/Delta

When It Flips: when the target has cheap storage and scalable compute, landing raw first (ELT) keeps the untouched copy and lets the platform do the heavy transform; when the target should only ever see curated data, transform first (ETL).

📊 Exam Execution Trace & Applied Exercises

Manual Execution Trace — the MERGE above

raw.student_raw for S7 (source_priority: $1$ = most trusted); cleaned.student holds S7 at updated_at $=$ 09:00.

Step	Row(s) considered	Rule fired	Result
1	(10:05, prio 2) · (10:05, prio 1) · (09:30, prio 1)	ORDER BY updated_at DESC	the two 10:05 rows tie on top
2	(10:05, prio 2) · (10:05, prio 1)	then source_priority ascending	(10:05, prio 1) gets ROW_NUMBER $=1$
3	u = (10:05, prio 1) vs s = 09:00	MATCHED AND u.updated_at > s.updated_at	UPDATE SET * ➔ cleaned row now 10:05
4	a new key S9 in u	NOT MATCHED	INSERT *

Final Extracted Output: one row per student, holding the latest, most-trusted version; a stale raw row (older than the cleaned copy) never overwrites.

🧠 Active Recall

The lakehouse is called ELT. Where in it do cleaning and conflict resolution happen, and why does that order suit it?

Answer

• Short answer: after loading — raw data lands in Raw/Bronze first, then is transformed and de-conflicted into Cleaned/Silver inside the platform.
• Why: Raw is kept ➔ append-only Bronze preserves exactly what arrived, so a bad transform can be re-run; the target owns the compute ➔ Spark scales the transform in place, so no separate staging server is needed — the opposite of Model 3, where PL/SQL cleans in the Temp DB before the star is loaded.

Why does the MERGE guard the update with u.updated_at > s.updated_at when ROW_NUMBER already picked the latest raw row?

Answer

• Short answer: ROW_NUMBER picks the latest row within Raw; the guard compares it with what Cleaned already holds.
• Why: Late or replayed data ➔ a re-delivered old file can be the newest row in this batch yet older than the cleaned copy; without the guard it would roll the student back.