Chapter 14 · ETL vs ELT Architecture: Staging, Raw, Integration, Presentation, and Transform Ownership
ETL vs ELT: Compute Location, Pushdown, Auditability, Cost, Lock-In, and Tooling Tradeoffs
Choose ETL or ELT from compute placement, auditability, cost, lock-in, and failure-isolation evidence rather than treating either acronym as a modernity label.
Learning outcomes
AtlasMart must rebuild a corrected 690-USD sales state from immutable evidence. One engineer proposes transforming every file in Python before the warehouse sees it; another wants to load raw records first and push SQL transformations into the database. The architectural question is not which acronym sounds newer, but where each semantic decision executes and what evidence remains if it fails.
Define ETL and ELT by where transformation executes, not by tool branding or chronological fashion.
Compare compute placement, pushdown, auditability, latency, cost, lock-in, and operational ownership as separate decision axes.
Explain why both ETL and ELT can preserve immutable raw evidence—or destroy it—depending on pipeline design.
Demonstrate equivalent AtlasMart business controls under Python-before-load and SQL-after-load transformation paths.
Reject universal “ELT is modern” and “ETL is cleaner” claims without workload and governance evidence.
Chapter 14 preserves the accepted AtlasMart production state established through Chapters 11–13: eight current paid order-line facts, five paid orders, ten units, 690 USD paid GMV, 425 USD cost-at-sale, 265 USD gross profit, and the governed inventory snapshot of 137 units. Chapter 13's Q-prefixed malformed training batch remains isolated quality-test evidence. This chapter changes where transformations execute and which intermediate states are persisted; it does not silently redefine sales grain, correction history, metric formulas, or customer identity.
The mandatory lab is synthetic, local, and free. It uses
Python 3 standard library plus its bundled
sqlite3 module. Generation-time validation ran
with Python 3.13.5 and SQLite 3.46.1; learners should record
their own python --version and
sqlite3.sqlite_version because behavior and
optimizer details can vary. The lab demonstrates layering,
replay, lineage, and deterministic controls—not cloud pricing,
distributed exactly-once guarantees, production durability, or
vendor-specific warehouse performance.
1. ETL and ELT differ first in transform location
ETL means extract source data, transform it in a processing environment, then load the transformed result into the target analytical store. ELT means extract, load source-shaped evidence into the target platform, then transform it using compute in or near that platform. Neither definition says whether the pipeline is batch or streaming, dimensional or lakehouse, open-source or proprietary.
The same dimensional contract can be implemented either way. AtlasMart still needs one current row per order line, Chapter 11 correction semantics, Chapter 13 quality rules, and the same 690/425/265 controls. Moving transformation changes execution and operations; it does not authorize metric drift.
| Decision axis | ETL pressure | ELT pressure | What must be measured or governed |
|---|---|---|---|
| Compute location | Transform engine has required libraries/security/isolation. | Target engine can efficiently express transformations and already holds data. | CPU/memory/scan work, data movement, failure domains. |
| Pushdown | Limited if source/target cannot safely execute logic. | Potentially high when target optimizer can execute joins/aggregations close to data. | Actual plan/runtime/bytes; no universal speed claim. |
| Auditability | Strong if pre-load artifacts and transform versions are retained. | Strong if immutable raw loaded data and SQL versions are retained. | Input identity, transform version, output controls. |
| Cost | Separate transform compute/storage may add movement and duplicated infrastructure. | Target compute may be convenient but can increase billed scans/warehouse time. | Provider/region/pricing model or local measured work. |
| Lock-in | Custom code/runtime APIs can lock in too. | Vendor SQL/extensions can increase target dependence. | Portable semantics versus product-specific syntax. |
| Failure isolation | Transformation can fail before touching target presentation objects. | Raw load may succeed while downstream SQL fails independently. | Transactional boundaries, retry/replay policy. |
2. The same business rule can execute in different places
Consider O1002: raw evidence contains revision 1 at 200 USD and
an authorized revision 2 at 190 USD. A transformation must
select the latest governed revision for
(order_id,line_no). ETL can perform that selection
in Python before loading the current fact. ELT can load both
revisions and execute the selection in SQL. The acceptance
criterion is semantic equivalence plus retained evidence, not
identical implementation syntax.
# ETL-shaped pseudocode: transform before target loadlatest = {}for row in extracted_rows: key = (row["order_id"], row["line_no"]) if key not in latest or row["revision_no"] > latest[key]["revision_no"]: latest[key] = rowload_target(list(latest.values()))# ELT-shaped SQL: load revisions first, transform in target# SELECT s.* FROM stg_sales s# WHERE NOT EXISTS (# SELECT 1 FROM stg_sales newer# WHERE newer.order_id=s.order_id# AND newer.line_no=s.line_no# AND newer.revision_no>s.revision_no# );Either path is incomplete if it discards the superseded 200-USD source evidence without a governed raw/correction history. Likewise, ELT is not automatically auditable merely because “raw” exists if later jobs mutate those raw rows.
3. Controlled failure: architecture by slogan
Wrong: “Use ELT because cloud warehouses are fast.” This skips workload size, target features, scan/compute pricing, security boundaries, transformation language fit, and replay requirements. Wrong: “Use ETL so dirty data never enters the warehouse.” That can erase malformed source evidence before Chapter 13 quarantine/repair logic can explain it.
Repair: separate immutable evidence from certified serving data, benchmark representative transformations in the real execution environment, version the logic, and reconcile both paths to the same dimensional controls. This chapter's local lab intentionally uses a tiny dataset, so it demonstrates correctness and lineage—not performance superiority.
4. AtlasMart decision for the mandatory lab
The local lab chooses an ELT-like path only as an execution harness: CSV bytes are promoted to immutable raw storage, rows are loaded into SQLite, a temporary staging table normalizes execution state, SQL selects the current revision, and SQL builds a daily presentation table. This does not make SQLite the course architecture or imply production ELT is always preferable.
| Contract | Lab choice | Reason |
|---|---|---|
| Raw evidence | Persist exact CSV bytes + SHA-256 | Replay and mutation detection. |
| Staging | SQLite TEMP table | No independent consumer/recovery requirement in this tiny lab. |
| Integration | Persist current governed order-line state | Reusable semantic boundary for multiple marts. |
| Presentation | Persist daily sales aggregate | Consumer-ready result with explicit grain. |
| Batch metadata | Persist hashes, batch/environment identity | Audit and replay comparison. |
5. Production judgment
ETL versus ELT is a placement decision nested inside a larger architecture. Security may require masking or tokenization before data reaches a shared target; conversely, central SQL governance may reduce duplicate business logic. Pushdown can reduce movement, but only measured plans and costs support a performance claim. Migrating between the two should preserve raw identity, transformation versions, control totals, and rollback checkpoints so execution style can change without rewriting business meaning.
Lesson 2 therefore stops debating acronyms and defines what each layer is responsible for—and when a layer is not worth persisting.
Knowledge check
Check your understanding
- What is the defining mechanical difference between ETL and ELT?
- Does ELT guarantee immutable raw data?
- Why can ETL still be highly auditable?
- What does this tiny SQLite lab prove about performance?
- What must remain invariant if AtlasMart changes ETL/ELT style?
Review the answers
1. Where transformation executes relative to loading into the analytical target.
2. No. Immutability is a separate storage/governance contract.
3. If it retains source evidence, transform versions, batch identity, and reconciled outputs.
4. Nothing universal; it proves deterministic semantic and replay mechanics only.
5. Declared grain, history/key rules, metric formulas, data-quality decisions, lineage, and reconciled controls.
Authoritative references
- Python documentation — csvStandard-library CSV reader/writer used for the deterministic landing/raw fixture.
- Python documentation — hashlibSHA-256 fingerprints used as byte and semantic reproducibility evidence; hashes do not establish business correctness by themselves.
- Python documentation — sqlite3DB-API interface used by the local ELT-style execution harness.
- SQLite — CREATE TABLETable and constraint semantics used by persisted raw/integration/presentation state.
- SQLite — TEMP tablesSupports the chapter's execution-scoped staging example without implying that every staging layer must be temporary in production.
- Kimball Group — Dimensional Modeling TechniquesPrimary reference for the dimensional semantics that the presentation layer must preserve regardless of ETL/ELT execution style.
- OpenLineage documentationOptional reference for standardized lineage concepts; OpenLineage is not required by the local lab.
- dbt Developer HubOptional later-course tooling reference for modular SQL/dependency/promotion ideas; dbt is not a prerequisite for this chapter.