Chapter 14 · ETL vs ELT Architecture: Staging, Raw, Integration, Presentation, and Transform Ownership
Landing/Raw/Staging/Integration/Presentation Layers and When Each Layer Adds Value
Assign explicit responsibilities to landing, raw, staging, integration, and presentation layers, and persist a layer only when its replay, ownership, or serving value justifies another stored copy.
Learning outcomes
A five-box diagram is easy to draw and expensive to misunderstand. AtlasMart needs a place to receive a file, a trustworthy immutable copy, temporary execution state, reusable governed semantics, and consumer-ready outputs—but those responsibilities do not require five permanent copies of every byte.
Define landing, raw, staging, integration, and presentation as responsibilities rather than mandatory database schemas.
Decide whether each layer should be persisted from replay, ownership, recovery, reuse, latency, and cost requirements.
Explain why landing and raw are different even when both initially contain source-shaped data.
Use execution-scoped staging without losing the immutable evidence needed for replay.
Diagnose the storage and governance cost of persisting every intermediate transform merely because an architecture diagram contains boxes.
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. Layers are contracts, not sacred schema names
| Layer | Primary responsibility | Typical lifetime | AtlasMart Chapter 14 choice |
|---|---|---|---|
| Landing | Receive/hand off source delivery; verify transport identity/completeness. | Short-lived after durable promotion unless operations require retention. | Filesystem receipt buffer. |
| Raw | Preserve source evidence at source-delivery grain with minimal semantic mutation. | Long enough to satisfy replay/audit policy. | Immutable byte-identical CSV per batch. |
| Staging | Execution-scoped parsing, typing, validation, dedupe candidates, join preparation. | Often transient; persist only with recovery/reuse justification. | SQLite TEMP table. |
| Integration | Apply reusable enterprise semantics: identity, revisions, conformance, standard units/codes. | Persisted when shared across consumers or costly/risky to recompute. | Current governed sales rows. |
| Presentation | Expose consumer-oriented star/mart/aggregate/semantic shapes. | Persisted or virtual according to serving requirements. | Daily sales aggregate. |
Names vary across organizations. Some teams call raw “bronze,” integration “silver,” or presentation “gold.” Those labels do not automatically provide dimensional grain, history, quality, or conformance. The responsibility must be written explicitly.
2. Landing is not a second raw archive
Landing is where delivery is observed: file name, transport completion, expected partition/batch identity, sender, byte count, and receipt timestamp. Raw is the durable evidence after acceptance into the analytical platform. If the raw copy is byte-preserving, a later replay can prove exactly which source payload was used. Keeping landing forever as another uncontrolled archive usually adds ambiguity, not safety.
atlasmart_ch14_lab/ landing/erp_sales_B20260921-001.csv # receipt buffer raw/erp_sales_B20260921-001.csv # immutable replay evidence warehouse.db # persisted integration + presentation + metadata warehouse_replay.db # independent acceptance replay lineage_manifest.json # batch/hash/layer evidence# staging is deliberately absent from the filesystem:# it exists only as SQLite TEMP table stg_sales during a run.3. Persist only when a layer has a reason to survive
A useful persistence test is: what consumer, recovery step, evidence requirement, or cost boundary becomes materially worse if this state disappears after the run? If the answer is “none,” persistence can add storage cost, stale-state risk, access-control surface, and unclear ownership.
| Candidate state | Persist? | Reason |
|---|---|---|
| Source CSV after raw promotion | Raw yes; landing no after handoff | Raw is replay evidence; landing has no independent semantic role. |
| Parsed/normalized staging rows | No in this lab | Cheap deterministic recomputation from raw; no consumer depends on them. |
| Current sales integration rows | Yes | Shared governed revision selection reused by presentation outputs. |
| One-off debug sort result | No | No semantic contract or reuse. |
| Daily sales presentation | Yes | Explicit consumer-facing grain and stable serving contract. |
4. Controlled failure: “persist every layer for safety”
If AtlasMart persists landing, raw, three staging variants, two integration copies, and a presentation table for a nine-row fixture, replay does not become more correct. It becomes harder to know which copy is authoritative. A correction can update one copy and leave another stale; permissions must cover more surfaces; retention and deletion scope expand.
The safer pattern is to persist evidence and semantic boundaries, not every computational step. Ephemeral state still needs logs and deterministic code so failure can be diagnosed and rerun.
5. Failure isolation and restart boundaries
If landing→raw promotion fails, do not start transformations. If raw is durable but integration fails, replay from raw without asking the producer to resend. If presentation fails after integration commits, rerun only the presentation dependency when its transform is idempotent. This boundary design reduces blast radius without claiming every organization needs the same number of physical layers.
Chapter 15 will add incremental state and watermarks. This chapter deliberately keeps each batch complete and deterministic so the responsibility of each layer is clear before incremental complexity arrives.
Knowledge check
Check your understanding
- Why are landing and raw different?
- Must staging be persisted?
- Why persist integration in this lab?
- What is the risk of persisting every intermediate step?
- What should happen if presentation fails after integration succeeds?
Review the answers
1. Landing is a delivery/receipt boundary; raw is retained source evidence for replay and audit.
2. No. Persist it only when recovery, reuse, audit, or cost evidence justifies survival beyond a run.
3. It owns reusable governed current-revision sales semantics shared by presentation outputs.
4. Stale copies, ambiguous authority, larger security/retention surface, and unnecessary storage/operational cost.
5. Rerun the presentation dependency from the committed integration state when the transform is idempotent.
Summary and next step
This lesson established the mechanism and production boundaries for Landing/Raw/Staging/Integration/Presentation Layers and When Each Layer Adds Value while preserving AtlasMart’s declared grain, governed metrics, history, and reconciliation evidence. Continue to Immutable Raw Data, Replayability, Audit Columns, Batch IDs, Load Timestamps, and Lineage with those contracts unchanged unless an explicit, tested migration says otherwise.
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.