Chapter 14 · ETL vs ELT Architecture: Staging, Raw, Integration, Presentation, and Transform Ownership
Immutable Raw Data, Replayability, Audit Columns, Batch IDs, Load Timestamps, and Lineage
Preserve immutable source evidence and make every replay attributable with batch IDs, load timestamps, source hashes, deterministic outputs, and explicit lineage edges.
Learning outcomes
A replay is meaningful only if the input identity is stable. If yesterday's “raw” file can be overwritten under the same batch name, an identical SQL script can produce a different answer and nobody can reconstruct which source evidence supported the published report.
Distinguish immutable raw evidence from parsed raw tables and downstream transformed state.
Attach batch IDs, source-file identity, event time, load time, transform IDs, and hashes without confusing audit metadata with business semantics.
Replay the same immutable batch into an independent database and compare semantic checksums.
Detect a raw mutation rather than silently accepting different bytes under the same batch identity.
Explain what a checksum proves and what correctness claims still require row/grain/metric tests.
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. Immutability is an identity rule
For AtlasMart batch B20260921-001, the raw file
name, exact bytes, and SHA-256 are treated as one evidence
identity. A rerun may read those bytes many times, but it must
not mutate them. If a producer sends corrected data, give it a
new governed revision/batch identity or correction event; do not
overwrite the old evidence and pretend history never changed.
def promote_raw(landing, raw): incoming = landing.read_bytes() incoming_hash = sha256_bytes(incoming) if raw.exists(): if sha256_bytes(raw.read_bytes()) != incoming_hash: raise RuntimeError("immutable raw batch already exists with different bytes") else: raw.write_bytes(incoming) return incoming_hash2. Audit columns answer operational questions, not business-time questions
| Field | Meaning | Do not misuse as |
|---|---|---|
| event_ts | When the business event occurred. | Load/certification timestamp. |
| recorded_at | When the source/correction record was recorded. | Dimension as-of event time unless policy says so. |
| batch_id | Pipeline/source-delivery identity. | Business transaction identifier. |
| source_file | Raw evidence location/name. | Proof that the row is correct. |
| load_ts | When this local load wrote the row. | Historical sales date. |
| transform_id | Version/name of semantic action. | Metric owner or business definition by itself. |
Chapter 11 already showed why event time and load time cannot be substituted in SCD as-of joins. Layering must preserve those semantics, not flatten all timestamps into “updated_at.”
3. Deterministic replay needs canonical semantic state
A raw byte hash proves the input file is identical. An integration checksum proves the selected ordered rows are identical under a stated canonical serialization. A presentation checksum does the same for the aggregate. None proves that 690 USD is the correct business definition without the earlier grain, revision, quality, and metric contracts.
def canonical_hash(rows): payload = json.dumps(rows, sort_keys=True, separators=(",", ":"), ensure_ascii=False) return hashlib.sha256(payload.encode("utf-8")).hexdigest()first = load_database(raw, Path("warehouse.db"), batch_id, "dev")second = load_database(raw, Path("warehouse_replay.db"), batch_id, "replay")assert first["integration_sha256"] == second["integration_sha256"]assert first["presentation_sha256"] == second["presentation_sha256"]Expected Chapter 14 fixture hashes after generation-time validation:
| Evidence | Expected SHA-256 |
|---|---|
| Raw CSV bytes | e59a09c3d89855c024bd6f2cf283c9b1a65aa769f8a0656e57e76e1f88dd716b |
| Integration semantic rows | 548c4361d9071b4d5e85e2a507ec4d86b42c6c1e90bb7f2d92dedf5a63826dce |
| Presentation semantic rows | c2516669eed256c512ad207494c0cf3086d5d2aa2262e28ae09d4795e3b73690 |
4. Controlled failure: mutate raw “to fix the data”
Suppose an operator opens raw O1002 revision 1 and changes 200 to 190, then deletes revision 2 because “190 is the right answer.” The presentation can still show 690, but the authorized restatement trail from Chapter 11 has been destroyed. Replaying an older report is no longer possible.
The repair is append/version semantics: preserve both revisions, let integration choose the current governed revision, and retain the correction event. The raw hash makes accidental in-place edits detectable.
5. Lineage is a graph of evidence, not a comment
The lab records edges
landing file → raw file → TEMP staging → int_sales_current →
mart_sales_daily, each with a transform ID. Production lineage can add
columns/jobs/metrics/reports and standardized event formats, but
the minimum useful question remains: “which exact upstream
evidence and transformation produced this published object?”
Security applies to lineage too. Raw paths, row samples, and debug metadata can reveal sensitive context; expose only what authorized operators and consumers need.
Knowledge check
Check your understanding
- Why is an immutable raw hash useful?
- Should load_ts determine historical customer version?
- What does equal presentation checksum prove?
- Why is that checksum not enough to prove correctness?
- What is the minimal lineage path in this lab?
Review the answers
1. It detects byte changes under the same evidence identity and anchors replay to a specific input.
2. No; business event time does under the existing AtlasMart history policy.
3. The canonical presentation rows are identical for the two local replays.
4. Canonical serialization can faithfully reproduce semantically wrong logic; grain/metric/quality tests are still required.
5. Landing → raw → staging → integration → presentation, with explicit transform IDs.
Summary and next step
This lesson established the mechanism and production boundaries for Immutable Raw Data, Replayability, Audit Columns, Batch IDs, Load Timestamps, and Lineage while preserving AtlasMart’s declared grain, governed metrics, history, and reconciliation evidence. Continue to Transformation Modularity, Reusable Intermediate Models, Dependency Graphs, and Environment Promotion 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.