ETL and ELT Architecture

Data Engineering & Modern Data Platforms · Lesson 13 · Data Ingestion, ETL and Orchestration

Lesson Overview

The integration patterns from Data Integration Patterns had landed TechLearningPro's progress events and a daily enrollment snapshot. The completion number was still produced by a Python job on a single host that joined those inputs in memory, dropped every column it did not need for completion-v3, and loaded a finished mart. The job was green. Then analytics changed the definition. A learner now counts as complete when a completed event exists, or when the highest checkpoint on the attempt equals the lesson's terminal checkpoint, because an older player never emitted completed. The Python job had never stored checkpoints it considered irrelevant. The raw zone did not have them either. The only copy of the discarded fields was PostgreSQL, and the restatement window of three days was already a negotiation with the primary.

That failure is an architecture failure about where the transform runs, not a bug in the join. Extract-transform-load (ETL) shapes data before the analytical store sees it. Extract-load-transform (ELT) lands a raw, access-controlled copy and applies the business definition in the analytical engine, which can recompute when the definition changes. A third shape, sometimes called extract-transform-load-transform, does a small, lossy-on-purpose transform at the boundary (projection, types, prohibited fields) and leaves the business definition in SQL where it can be replayed. TechLearningPro needs that split. The seat-assignment file still arrives with an email column a human process added. That column must be rejected before a shared raw prefix exists. The completion rule must be replayable from retained events. One slogan, "we are an ELT shop," would either leak the email or make the next definition change impossible.

This lesson places the transform. It does not re-choose the movement pattern, and it does not teach the Python batch harness. Building Batch Data Pipelines with Python builds the restartable job. Data Quality Engineering decides which checks fail a publish. Orchestration is lesson 19. The dimensional grain you are loading was set in Dimensional Modeling and Data Warehousing: one completion row per learner, lesson, and attempt, joined to enrollment at a named as-of date, never to "whatever enrollment looks like now."

Planning assumptions stay labeled. About fifty million progress events on a peak day. A completion fact that is a small fraction of those events. A warehouse or an analytical PostgreSQL that can run the daily SQL in the morning window. A seat file of about forty thousand rows. If the SQL no longer fits the morning bound on one engine, Module 3's Spark discussion is the next design. Switching the letters from ETL to ELT does not, by itself, create a cluster.

Learning Objectives

By the end of this lesson you will be able to:

1. Distinguish extract-transform-load, extract-load-transform, and a split boundary transform plus a replayable business transform.
2. Assign TechLearningPro's completion definition to the analytical SQL layer and the seat-file email rejection to the landing boundary.
3. Draw landing, raw, conformed, and curated zones with an owner and an access rule for each.
4. Write an idempotent restatement of a completion window in standard SQL, joined at lesson-to-course grain rather than learner grain.
5. Explain who owns a source-aligned model and who owns a business definition, and what breaks when both live in a notebook.
6. Judge a proposed "ELT everything" design against replay, prohibited fields, warehouse cost, and the morning freshness bound.

Prerequisites

• Data Integration Patterns: the raw landing already has a completeness marker. This lesson starts after that marker is durable.
• Introduction to Data Engineering: completion-v3, the grain (learner_id, lesson_id, attempt_number), prohibited fields, and the three-day event-time restatement.
• SQL Fundamentals for Data Engineers and Advanced SQL and Window Functions. The walkthrough is SQL a PostgreSQL-compatible engine can run. Warehouse dialects differ on MERGE. This lesson does not invent a vendor procedure.
• Data Modeling Fundamentals and lesson 10, so an enrollment join is not allowed to multiply rows.
• Python 3.11 type hints, as in /learn/python, for the placement review aid. /learn/sql is the query primer.

Detailed Core Concepts

The three letters are a placement decision

Extract is the integration pattern from lesson 12: a snapshot, a watermark window, an API pull, or a file. Extract ends when bytes are durable and complete. It does not mean "the row is meaningful."

Transform is where a definition is applied: types, keys, joins, filters, aggregations, and the rule that turns events into a completion fact. A transform has a version. completion-v3 is a transform. A rename of lessonId to lesson_id is also a transform, and it is a different kind. Mixing them is how a cosmetic rename and a metric change ship in one unversioned script.

Load is the durable write of a transform's output to the store a consumer reads, or, in ELT, the durable write of raw input to the store that will transform it. Load is idempotent for a window, or a rerun doubles the metric.

ETL runs transform before the analytical store is the system of replay. The classic warehouse teams did this because the warehouse was an expensive, closed engine and the ETL host was where procedural code lived. The curated tables were small and clean. The cost was hidden until the definition changed and the pre-image was gone. Kimball's bus architecture assumed conformed dimensions and fact loads. It did not require you to throw away the source extract. Teams threw it away to save disk. That was an operational choice, and it is the choice that stranded completion-v3.

ELT loads the extract into the analytical platform and transforms there, usually in SQL, sometimes in a framework that compiles to SQL. The raw tables are the replay log. A definition change is a new query over retained raw rows, not a plea to the primary. The cost moves to analytical compute and to governance: raw tables contain whatever you loaded, and a shared warehouse with a raw schema that analysts can browse is a second production database with worse discipline. ELT is the right default when the definition will change, the transform is relational, and the raw zone has an owner and an access boundary. It is the wrong default when loading the raw payload would persist a prohibited field, or when the transform is a procedural parse the SQL engine cannot express without becoming a second programming language.

The split this course uses has a precise name only so reviews can say it. Boundary transform, then business transform. At the boundary, before a shared raw table exists: project columns, coerce types you are willing to be strict about, reject prohibited fields, attach source_run_id and ingested_at. That work is allowed to drop email. It is not allowed to drop checkpoints because today's definition ignores them. In the analytical store: source-aligned tables at source grain, then the versioned business model at the mart grain. People call a light boundary step plus a warehouse transform "EtLT." The letters matter less than the rule: anything you drop at the boundary must be something you will never need for a replay, or something you are legally unwilling to store.

Zones and what each one is allowed to mean

Four zones keep those rules visible. They can be schemas, prefixes, or catalogs. The name is not the control. The access rule is.

Landing retention is short because it may hold the email you rejected. Raw retention covers the restatement window and any financial lookback you have actually signed. Curated retention follows the product. Dropping raw to save money while finance can ask for a quarter is the ETL failure mode again, performed later.

The curated completion table is not a copy of raw with nicer names. Its grain is (learner_id, lesson_id, attempt_number). Raw's grain is one row per event_id. A view that is "raw with a WHERE" will double-count retries if the load was at-least-once, and it will not express "terminal checkpoint or completed event." The zones exist so those are different tables with different keys.

Ownership of the transform

Two owners show up, and they are the same split as lesson 1's analytics-versus-engineering border.

The source-aligned model is owned by the squad that runs ingestion. They promise column names, types, a deduplication key, and a freshness of the raw table. They do not promise what completion means. If the player renames a field, this squad changes the boundary projection and keeps the raw contract stable, or they version the raw contract and say so.

The business model is owned jointly. Analytics owns the wording of completion-v3. Data engineering owns the SQL, the restatement, and the failure behavior. A second SQL file in a personal schema that "fixes" completion is a second product. Lesson 3's accountability is the human version of this rule. The technical version is one curated table with definition_version populated by one job.

Inmon's corporate information factory and Kimball's dimensional bus disagree about whether you build a normalized warehouse before marts. This lesson does not settle that argument. It does insist that whatever you call the middle, the business definition is not reimplemented per dashboard, and the source grain is still recoverable.

Idempotent load of a window

ELT does not make reruns safe. A transform that INSERTs the day's completions every morning will double Monday's count if the job retries. The load shape matches the pattern from lesson 12.

For a restated event-time window, replace the keys in that window. Delete or overwrite the curated rows whose business time falls in the window, then insert the result of the query. Do this in one transaction if the engine offers it, so consumers never see the gap. Where a transaction around a large replace is unrealistic, write the new window to a staging table and swap a partition. The consumer's contract is "a version is complete or the previous version is still served." A half-deleted Tuesday is not a version.

INSERT ... ON CONFLICT upserts a key. It fits a current-state dimension. It is the wrong default for a fact window if the new definition must remove a row that the old definition emitted. An upsert updates keys that still exist and leaves the orphan row from the old rule in place. Window replace removes orphans. Use upsert when absence of a new row must not delete the old one, and say which product that is.

Warehouse MERGE statements differ. PostgreSQL 15 and later has MERGE. Snowflake, BigQuery, and Spark SQL each document their own. This lesson shows PostgreSQL-compatible DELETE plus INSERT inside a transaction, which states the replace clearly. Copying that text into a warehouse without reading that engine's documentation is how a restatement locks a table until the morning meeting. The semantics to preserve are the semantics, not the spelling.

What belongs early, and what belongs in the mart SQL

Push a projection and a prohibited-field reject to the boundary. You pay less to store and you do not replicate a secret. Push a filter that is truly about the extract window to the source query, so you do not copy history you will not restate.

Keep in the mart SQL anything a definition change might revise: which event types count, the enrollment predicate, the attempt rule, the handling of a missing terminal checkpoint. Filtering "only completed events" at extract time is how the checkpoint clause of completion-v3 died. The boundary may keep every event type the contract allows and let SQL decide.

Parsing a partner CSV's quirks, dropping email, and rejecting an unknown encoding are boundary work. They are procedural, they must happen before shared storage, and SQL is a poor place to discover that a file was Latin-1. Lesson 15 implements the file side. This lesson only places it.

Cost is a placement constraint

ELT on fifty million raw events a day is reasonable when the business query touches one to three days and the raw table is partitioned by the filter column. It is unreasonable when every dashboard scans the raw JSON because no curated table exists. ELT moved the compute. It did not delete it. A curated, partitioned completion table is what Monday reads. Raw is what the backfill reads. Analysts querying raw because the mart is "missing a column" is a modeled gap, fixed by a versioned change to the mart, not by granting raw to the BI role.

ETL on an undersized host fails the other way. The join of a peak day does not fit the ETL machine, the job spills, and the morning bound slips. The remedy is often "load raw and let the analytical engine join," which is ELT, or "aggregate before the join," which is still a transform you must be able to replay. Module 3 starts when a measured job cannot meet the bound on the current engine. This lesson's job is to keep the inputs that such an engine would need.

Architecture and Design Diagrams

The completion path is ELT after a strict projection. The seat path is a boundary transform that refuses email, then a relational transform in the warehouse. Both curated outputs are the only tables consumers read.

mermaidCopy
flowchart LR
    subgraph extract [Extract with a completeness marker]
        events[Progress incremental partition]
        enroll[Enrollment snapshot]
        seats[Seat file landing]
    end

    subgraph boundary [Boundary transform]
        project[Project contract columns]
        strip[Reject email and unknown headers]
    end

    subgraph analytical [Analytical store]
        rawE[(Raw progress events)]
        rawS[(Raw enrollment snapshot)]
        rawSeat[(Raw seats, no email)]
        sql[completion-v3 SQL]
        mart[(Curated completion)]
    end

    events --> project --> rawE
    enroll --> project --> rawS
    seats --> strip --> rawSeat
    rawE --> sql
    rawS --> sql
    rawSeat --> sql
    sql --> mart

    landing[(Landing prefix, short retention)]
    seats --> landing
    landing -.->|analysts must not read| mart

The dotted refusal matters. Landing can contain the bad file you are investigating. It is not a source for a dashboard. Raw seats exist only after the reject. If the reject fails, raw seats do not update, and the previous curated window stays served.

A second diagram is the restatement of one UTC day. The transaction is the product boundary. Consumers read curated only when the transaction has committed.

mermaidCopy
sequenceDiagram
    participant Job
    participant Raw
    participant Curated
    participant Consumer
    Job->>Raw: Read events and snapshot for the window
    Job->>Curated: BEGIN
    Job->>Curated: Delete curated keys in the event-time window
    Job->>Curated: Insert completion-v3 rows
    Job->>Curated: COMMIT
    Consumer->>Curated: Read after commit
    Note over Job,Curated: A failed statement rolls back. Tuesday stays the previous version.

The third diagram places ownership. Source-aligned tables sit on the ingestion contract. The business model sits on the metric contract. A personal schema is drawn outside the flow because it is not a zone.

mermaidCopy
flowchart TD
    subgraph ingestionOwner [Ingestion owner]
        raw[Raw and source-aligned tables]
    end
    subgraph metricOwner [Analytics wording and engineering job]
        biz[Business SQL definition_version]
        curated[Curated mart]
        biz --> curated
    end
    raw --> biz
    personal[Personal schema SQL]
    raw -.->|must not be a second definition| personal

Technical Explanation

Why the enrollment join has a course key

Enrollment's grain, from the operational model, is one row per learner and course. A progress event names a learner and a lesson. Joining them on learner_id alone multiplies completions by the number of courses that learner is enrolled in, or drops the course predicate entirely. The conformed path is: event to lesson dimension to course_id, then to the enrollment snapshot for that learner, course, and extract date.

The extract date is part of the join. completion-v3 in this course says the learner must have been enrolled as of the UTC date of the event, using that morning's snapshot. Joining to "current enrollment" makes a backfill change history when someone unenrolls next week. The snapshot partition from lesson 12 is what makes the predicate replayable. ELT without retained snapshots is ETL with extra steps: the input you needed has been overwritten.

Terminal checkpoints need the lesson dimension too. The dimension carries terminal_checkpoint for the lesson version you are willing to apply. If the catalog changes that value and you join the current dimension, last quarter's completions move. Lesson 10's slowly changing dimension is the design. This lesson's rule is narrower: the SQL names the dimension version or the as-of date. It does not call a view that means "latest."

Definition version is a column, not a comment

Every curated row carries definition_version. A restatement of three days under completion-v3 replaces those days' rows for that version. If you must compare v2 and v3 for a week, you load v3 beside a retained v2 partition or you accept that v2 is gone. Keeping both forever doubles storage and confuses consumers. The serving contract names the version Monday reads. Older versions are retained only for the audit window you wrote down.

A change to wording is a new version and a backfill of the agreed window. A change to SQL that preserves wording, such as a join fix that corrects a fan-out, is still a behavior change if the numbers move. Treat it as a version or publish a correction note. Silent number movement is the Monday incident again.

Transactions, partitions, and engines that are not PostgreSQL

The walkthrough uses a transaction because PostgreSQL can rollback a delete-plus-insert of a day of completion facts, which are far fewer rows than fifty million events. The raw scan is the large part. The curated write is the small part. If you run the same semantics on an engine that isolates partitions, overwrite the event-date partition of the curated table instead of deleting inside a heap. The reader of that partition sees the old files until the overwrite commits. That is the same consumer promise.

Do not wrap a multi-terabyte raw reload in one transaction and call it safety. Stage the window, validate it (lesson 17), then publish. A transaction that holds locks until the morning meeting has protected consistency and missed the freshness bound. Write down which one you chose.

The ETL host as a liability

An ETL host that stages files on local disk fails in ways the warehouse will not see. The disk fills. A second run on another host cannot see the partial stage. Secrets for the source and the warehouse live on the same box. A "temporary" CSV of seats with email sits in /tmp through the next snapshot. ELT reduces that surface when the boundary job streams the projection into the raw table and does not keep a second full copy on local disk. It does not remove the need for a runner. Lesson 14's checkpoint and lesson 19's scheduler still exist. They should not be the only place the events live.

Security and the shared raw schema

Raw progress events are still personal data in combination: a learner id and a timeline of lessons. Access control is not "raw is internal, so it is fine." Grant the transform role SELECT on raw. Grant the BI role SELECT on curated. Grant nobody SELECT on landing. The email reject is a control only if a curious analyst cannot open the landing prefix. Encryption at rest is assumed and is not a substitute for the projection. Lesson 61 expands the policy. The architectural requirement here is a zone diagram with grants that match the arrows.

Practical Implementation or Design Walkthrough

Problem

Place the completion transform in the analytical store, keep enough raw columns to restate completion-v3, and reject a seat file that still contains email before that file becomes a raw table. Show a window replace that does not fan out on enrollment.

Schemas

These statements are PostgreSQL-compatible on purpose, so they can be read without a vendor account. A warehouse with different types should preserve the keys, the version column, and the replace semantics.

sqlCopy
CREATE TABLE raw_progress_event (
    event_id text PRIMARY KEY,
    learner_id text NOT NULL,
    lesson_id text NOT NULL,
    event_type text NOT NULL,
    attempt_number integer NOT NULL,
    event_time timestamptz NOT NULL,
    checkpoint integer,
    source_run_id text NOT NULL,
    ingested_at timestamptz NOT NULL
);

CREATE TABLE enrollment_snapshot (
    extract_date date NOT NULL,
    learner_id text NOT NULL,
    course_id text NOT NULL,
    status text NOT NULL,
    PRIMARY KEY (extract_date, learner_id, course_id)
);

CREATE TABLE lesson_dim (
    lesson_id text PRIMARY KEY,
    course_id text NOT NULL,
    terminal_checkpoint integer NOT NULL
);

CREATE TABLE lesson_completion (
    learner_id text NOT NULL,
    lesson_id text NOT NULL,
    attempt_number integer NOT NULL,
    completed_at timestamptz NOT NULL,
    definition_version text NOT NULL,
    PRIMARY KEY (learner_id, lesson_id, attempt_number)
);

checkpoint is nullable because a completed event may not carry one. It is present because the definition uses it. Dropping it at the boundary is the defect in the opening story.

The restatement below replaces one half-open event-time window. Run it in a transaction. Bind the timestamps from the job, not from now() inside the statement, so a retry targets the same window.

sqlCopy
BEGIN;

DELETE FROM lesson_completion
WHERE completed_at >= %(window_start)s
  AND completed_at < %(window_end)s
  AND definition_version = 'completion-v3';

INSERT INTO lesson_completion (
    learner_id,
    lesson_id,
    attempt_number,
    completed_at,
    definition_version
)
SELECT
    e.learner_id,
    e.lesson_id,
    e.attempt_number,
    MIN(e.event_time) AS completed_at,
    'completion-v3' AS definition_version
FROM raw_progress_event AS e
JOIN lesson_dim AS d
    ON d.lesson_id = e.lesson_id
JOIN enrollment_snapshot AS s
    ON s.learner_id = e.learner_id
   AND s.course_id = d.course_id
   AND s.extract_date = (e.event_time AT TIME ZONE 'UTC')::date
   AND s.status = 'active'
WHERE e.event_time >= %(window_start)s
  AND e.event_time < %(window_end)s
  AND (
        e.event_type = 'completed'
        OR (
            e.event_type = 'checkpoint'
            AND e.checkpoint = d.terminal_checkpoint
        )
      )
GROUP BY e.learner_id, e.lesson_id, e.attempt_number;

COMMIT;

The GROUP BY matches the curated grain. MIN(event_time) is the first moment the attempt qualified, which is a defined rule, not an accident of join order. If two enrollments could still match, the primary key on the snapshot and the course key on the dimension prevent a fan-out before the aggregate. If the insert raises a duplicate-key error, the transaction rolls back and the previous curated rows remain. That error means the grain broke. Do not catch it and continue.

Placement review aid

The function records where a transform is allowed to run. It is a review aid with the same humility as lesson 12's pattern chooser.

pythonCopy
from __future__ import annotations

from dataclasses import dataclass
from enum import Enum


class Placement(Enum):
    BOUNDARY_ONLY = "boundary_only"
    BUSINESS_SQL = "business_sql"
    SPLIT = "split"


@dataclass(frozen=True)
class TransformCase:
    name: str
    may_contain_prohibited_field: bool
    landing_is_shared_with_analysts: bool
    definition_must_be_replayable: bool
    relational: bool


@dataclass(frozen=True)
class PlacementDecision:
    placement: Placement
    reason: str


def place_transform(case: TransformCase) -> PlacementDecision:
    """Place the transform. Refuse a shared landing that can hold a prohibited field."""
    if case.may_contain_prohibited_field and case.landing_is_shared_with_analysts:
        raise ValueError(f"{case.name}: prohibited field would be visible in landing")

    needs_boundary = case.may_contain_prohibited_field
    needs_sql = case.definition_must_be_replayable and case.relational

    if needs_boundary and needs_sql:
        return PlacementDecision(
            Placement.SPLIT,
            "strip at the boundary, restate the metric in SQL over retained raw rows",
        )
    if needs_sql:
        return PlacementDecision(
            Placement.BUSINESS_SQL,
            "raw rows are retained and the definition is a versioned query",
        )
    if needs_boundary:
        return PlacementDecision(
            Placement.BOUNDARY_ONLY,
            "the transform exists to drop a field before shared storage",
        )
    raise ValueError(f"{case.name}: no replay and no boundary rule; do not invent a job")


def _demo() -> None:
    completion = TransformCase(
        name="completion-v3",
        may_contain_prohibited_field=False,
        landing_is_shared_with_analysts=False,
        definition_must_be_replayable=True,
        relational=True,
    )
    seats = TransformCase(
        name="assigned_seats",
        may_contain_prohibited_field=True,
        landing_is_shared_with_analysts=False,
        definition_must_be_replayable=True,
        relational=True,
    )
    assert place_transform(completion).placement is Placement.BUSINESS_SQL
    assert place_transform(seats).placement is Placement.SPLIT
    leaked = TransformCase(
        name="seats_in_public_bucket",
        may_contain_prohibited_field=True,
        landing_is_shared_with_analysts=True,
        definition_must_be_replayable=True,
        relational=True,
    )
    try:
        place_transform(leaked)
    except ValueError as exc:
        assert "prohibited" in str(exc)
    else:
        raise AssertionError("shared landing was accepted")
    print(
        f"completion={place_transform(completion).placement.value} "
        f"seats={place_transform(seats).placement.value}"
    )


if __name__ == "__main__":
    _demo()

How you would run it

Apply the SQL on a PostgreSQL instance you are allowed to use, with the four tables empty and a handful of fixture rows: one enrolled learner, one checkpoint equal to terminal_checkpoint, one extra enrollment in another course that must not duplicate the fact, and one seat file header list checked before load. The Python aid does not need that database.

bashCopy
python3 transform_placement.py

Expected output: completion=business_sql seats=split.

What this walkthrough does not do

It does not extract from the primary. Lesson 12 did that placement. It does not schedule the transaction. It does not call a warehouse SDK. If your engine's publish primitive is a partition overwrite, keep the DELETE/INSERT semantics and replace the syntax from that engine's own documentation. It does not implement checkpoint-terminal logic beyond the predicate shown. A lesson with no lesson_dim row fails the join and disappears. Lesson 17 turns that disappearance into a counted quarantine rather than a silent drop. Until that check exists, compare raw event counts with curated counts for the window and explain the gap.

Enterprise Real-World Scenarios

Restating completion without rereading the primary

The definition change that opened this lesson was rebuilt as ELT under labeled assumptions. Raw progress retained event_id, the five contract fields, and checkpoint, partitioned by insert date, for the three-day restatement plus a thirty-day audit the squad wrote down. The curated table held completion-v3 only. The backfill of the incident week ran as seven window transactions, each idempotent. The distinct completion count matched a hand query on raw. The primary was not asked for historical checkpoints, because they were in raw. The player path did not read the mart.

The enrollment predicate used the snapshot extract_date, not a live join. A learner who unenrolled on Friday stayed complete for Wednesday's event if Wednesday's snapshot said active. That surprised an instructor. The surprise was the contract, explained in the metric note, not a bug in the join. Changing it would be completion-v4, with a backfill and a consumer note. It would not be an edit to the live enrollment table.

Cost stayed inside the morning bound because Monday reads curated, and the raw scan is one job, not one scan per dashboard. The squad recorded the raw scan's bytes so a later growth in the thirty-day audit is a measured decision. ELT did not mean "analysts query raw."

The seat file that was almost a raw table

Customer success delivered the nightly assignment CSV with email, learner_id, course_id, and seat_status. An ELT proposal created an external table over the landing prefix and a SQL model that dropped email in the SELECT. The drop was real in the curated output and irrelevant in the landing prefix, which the BI role could already browse because "external tables are how ELT works here."

The repair was the split. Landing stayed private to the ingestion role and expired in seven days. The boundary job failed the file if email was a header, and otherwise loaded learner_id, course_id, seat_status, and source_run_id into raw seats. The business SQL joined raw seats to the enrollment snapshot to produce an assigned-seat fact at (learner_id, course_id, extract_date). A retry replaced that date's partition. The curated fact never had a column the boundary had rejected.

The incident note's useful line: a transform that exists only in the serving query does not undo a column that already landed. Placement is a privacy control when the field is prohibited, and a replay control when the field is part of a future definition. Those are different fields. Email is the first. checkpoint is the second. The architecture treats them differently on purpose.

Best Practices

• Decide placement per field class: prohibited fields die at the boundary; definition inputs survive into raw.
• Keep landing unreadable to analysts, with short retention. Keep raw readable to the transform role.
• Put definition_version on every curated row and serve one version to a given consumer.
• Replace a business-time window, in a transaction or a partition swap, so a retry cannot double and an old rule cannot leave orphans.
• Join enrollment through lesson to course and through an as-of snapshot date. Do not join facts to "current" operational rows during a backfill.
• Own source-aligned tables in the ingestion squad and the business SQL with the metric owner. One curated job, not a personal schema.
• Push extract-window filters to the source. Keep semantic filters in the versioned SQL.
• Grant BI on curated. A missing column is a versioned model change, not a raw grant.
• Record raw bytes scanned for the backfill so ELT cost stays a number.
• When the morning SQL cannot meet the bound after you have partitioned and projected, take the measurement to Module 3. Do not throw away raw to make the job look smaller.

Common Mistakes and Anti-Patterns

ELT as "load every column, transform later." Later does not delete the email that landed, and later does not stop a dashboard from reading it. The repair is a private landing, a rejecting boundary, and a raw table that never had the column.

ETL as "the warehouse should stay small." The host keeps the only copy of the fields the next definition needs, then the host's disk is cleaned. The repair is to retain projected raw for the replay window you have actually promised, even if the curated table is small.

Upsert that cannot delete. ON CONFLICT DO UPDATE leaves completions that the new rule no longer emits. The repair is a window replace. Use upsert for current-state dimensions where absence in today's feed must not erase yesterday's row, and write that sentence on the job.

Join on learner only. Enrollment is per course. The fan-out inflates completion and still looks like a successful SQL job. The repair is the lesson dimension's course_id and a primary key that makes a double match impossible.

Two owners, two definitions. The certified job says completion-v3. A personal schema adds "exclude internal staff" and becomes the number in a deck. The repair is one served table. Filters that change meaning are a new version with a consumer list.

Business filter pushed into the extract. The incremental job keeps only event_type = 'completed' to save space. The checkpoint clause cannot be backfilled. The repair is to project all contract event types into raw and let SQL apply the rule. Save space by not storing prohibited and unused payload fields, not by storing only today's interpretation.

A transaction that misses the bound. The delete-plus-insert holds locks through a raw scan of a quarter because someone restated "just to be safe." Consumers time out. The repair is the agreed window, partitioned raw, and a publish swap if the engine's transaction cannot cover the scan.

Troubleshooting and Design Review

Symptom: A backfill of completion-v3 cannot see checkpoint events for last month.

Likely cause: the boundary projection dropped checkpoint or non-completed event types. Landing has already expired.

Investigation: inspect raw columns and a sample of event_type values. Compare with the source for one learner if the source still has the rows. Check landing retention before you assume the file is gone.

Corrective action: widen the projection from the source for the window you are allowed to reread. Retain the new raw. Do not scrape analyst laptops for CSV copies. If the source no longer has the events, the backfill's limit is an honest gap, written on the version note.

Symptom: Completion counts jump after a retry that "succeeded twice."

Likely cause: the job inserts without deleting the window, or two runs overlap and both insert.

Investigation: count curated rows for the grain. Look for duplicates. Read the job log for two commits of the same window_start.

Corrective action: replace the window in one transaction. Add a primary key so a second insert fails and rolls back. Rerun once the replace is the only path.

Symptom: Completions are higher than distinct learners who finished, and the ratio looks like the number of courses per learner.

Likely cause: the enrollment join is on learner_id only.

Investigation: explain the join. Count matches per event before the aggregate. Compare with a query that includes course_id.

Corrective action: join through lesson_dim. Keep the primary key on the snapshot. Restate the affected window under a version note if the served numbers were wrong.

Symptom: Tuesday's curated table is empty and the job log shows a rollback.

Likely cause: the delete committed outside the transaction that failed, or a constraint failed after the delete in an engine that auto-commits.

Investigation: confirm the transaction boundaries in the script. Look for a driver setting that commits each statement.

Corrective action: one transaction or a partition swap. Restore Tuesday from the previous partition if you have it, or rerun the replace from raw. Do not leave an empty day served as "zero completions."

Symptom: Warehouse cost doubles and analysts say the mart is missing a column they now read from raw.

Likely cause: ELT raw was granted broadly, and the mart lagged the question.

Investigation: identify who scans raw. Identify the column. Decide whether it belongs in a new definition version.

Corrective action: revoke BI on raw, add the column through the model if it is allowed, and restate. A prohibited column is a removal incident, not a feature request.

A design review asks: Which fields die at the boundary, and which must be replayable? What is the raw retention relative to the restatement window? What is the curated grain and the definition version? Does the load replace a window or upsert? Does the enrollment join name a course and an as-of date? Who can read landing, raw, and curated? What does the consumer see if the transaction rolls back? A review that debates ETL versus ELT as identities, without those answers, has not placed the transform.

Interview Questions and Answers

Q1. What is the difference between ETL and ELT, in terms of replay rather than product fashion?

ETL applies the business definition before the analytical store keeps the row, so the store often has the finished fact and not the inputs. ELT loads a retained raw copy and applies the definition in the analytical engine, so a new definition is a new query over those inputs. I choose ELT when the definition will change, the transform is relational, and raw is access-controlled. I still transform at the boundary when a field must never be stored. The letters name where the irreversible step happens.

Q2. When would you still refuse to load raw and transform later?

When the payload contains a prohibited field and the landing place is readable by anyone other than the ingestion job. A SQL view that omits the column does not erase the file or the external table. I reject or strip before shared storage, keep landing private, and load a projected raw table. I would also refuse a raw load that is an unbounded SELECT * from the primary with no window, because that is an operational incident wearing an ELT label.

Q3. Why did TechLearningPro's in-memory completion job fail the definition change?

It dropped checkpoints it did not need for the previous rule, and it did not retain those events in a raw zone. The new rule needed them. The primary still had some of them, inside a short window, and was the wrong system to scan for a backfill. The architecture repair is a boundary projection that keeps every contract field the definition family needs, raw retention covering the restatement, and versioned SQL. The repair is not a larger ETL host running the same drop.

Q4. How do you restate three days of a fact table without doubling rows?

Replace the curated keys whose business time falls in the window: delete those rows and insert the query result, in one transaction, or overwrite that partition and publish the swap. An insert-only retry doubles. An upsert leaves rows the new rule no longer produces. I bind the window in the job so a retry names the same interval, and I put a primary key on the grain so a broken join fails the transaction instead of publishing a fan-out.

Q5. Why is joining completions to enrollment on learner_id alone a modeling bug that ELT will not save you from?

Enrollment is one row per learner and course. Events are one row per learner and lesson. The join multiplies facts by the learner's other courses. The engine will happily compute it. The fix is a lesson dimension that supplies course_id, a snapshot key of extract date plus learner plus course, and an aggregate that matches the curated grain. I want that primary key in place so the bad join is a failed constraint when it duplicates a completion key, not a quiet inflation.

Q6. Who should own the SQL that defines a metric?

Analytics owns the meaning with the business consumer. Data engineering owns the job, the restatement, and the technical contract of the inputs. The ingestion squad owns source-aligned raw tables and does not silently change the metric. A personal schema is not an owner. One curated table carries definition_version. A wording change is a new version and a backfill. A SQL edit that moves the number is treated as a behavior change even if the author called it a refactor.

Q7. What do landing, raw, and curated each guarantee?

Landing guarantees only that the producer bytes were stored for a short, private investigation. It may contain fields you will reject. Raw guarantees a projected, typed source grain without prohibited fields, retained long enough to replay. Curated guarantees the business grain, one definition version, and the freshness bound. Consumers of the Monday number read curated. Using the zones as three copies of the same table wastes money and erases the guarantees.

Q8. How do you decide that a transform is too big for the warehouse SQL and belongs in another engine?

I measure the restatement scan, the morning runtime, and the bytes read after the table is partitioned on the filter and the query reads curated for serving. If one engine still misses 06:00 UTC for the agreed window, I take that measurement to a distributed engine in Module 3, and I keep the same grain, version, and raw retention. I do not adopt Spark because ELT "sounds big," and I do not delete raw so the remaining query looks cheap.

Q9. A teammate suggests MERGE because DELETE plus INSERT feels old. What do you protect?

The published semantics: one window, idempotent retry, orphans from the previous rule removed, consumers never seeing a half-applied day. MERGE can express that on engines that implement it, and the syntax is vendor-specific, so I read that engine's statement rather than inventing a clause. On PostgreSQL I am willing to use a transaction with DELETE and INSERT because the curated day is small and the rollback story is obvious. The tool is acceptable when the consumer promise is the same.

Q10. Where should a CSV encoding fix live, and where should the completion predicate live?

The encoding fix, the header check, and the email rejection live at the boundary, before shared raw. They are about whether we are willing to store the file. The completion predicate lives in versioned SQL over raw events, the lesson dimension, and the enrollment snapshot. Putting the predicate in the file parser makes the next rule change a code deploy against data you no longer kept. Putting the encoding fix in the warehouse SQL means the bad file is already in a table analysts can read.

Q11. What does the consumer see if the restatement fails halfway?

The previous committed version of that window, still labeled with its time and definition version. That requires a transaction or an atomic partition publish. If the failure is visible as an empty day or a partial day, the job is wrong even when the error was caught. I would rather serve a stale Tuesday with a timestamp than a zero that looks like a product disaster. The alert fires because the new version did not publish, not because we hid the failure.

Q12. How does this lesson relate to change data capture and to streams?

CDC and streams change how raw rows arrive. They do not decide whether the business definition is applied before you retain inputs. A change feed landed into a raw change table and reduced in SQL is still ELT. A stream processor that emits only the finished completion and discards the event is the ETL failure mode at a lower latency. I place the definition where it can be restated, and I let lesson 16 and Module 4 decide the feed. The curated grain stays (learner_id, lesson_id, attempt_number) either way.

Hands-On Exercises

Exercise: Place the transforms for a completion mart

Problem statement. Design the zones and the restatement for a lesson-completion mart and a seat-assignment fact. Use your own volumes. Do not copy the planning numbers from this lesson as if you had measured them.

Tasks.

1. List the fields of a progress event, an enrollment snapshot, and a seat file. Mark each field as boundary-drop, raw-retain, or curated.
2. Draw the four zones and the grants: who can read landing, raw, and curated. Include one prohibited field and one field a future definition might need.
3. Write the curated grain and the definition_version rule. State what a retry of the same window does.
4. Write the restatement query so the enrollment join cannot fan out. Include the as-of date. Use a transaction or describe a partition swap with the same consumer promise.
5. Implement place_transform for the completion case, the seat case, and a case that must raise because landing is shared and contains email.
6. Apply the six-part lens to the placement. Label cost as an assumption tied to a scan you named.
7. Describe a definition change that adds a rule your raw zone can still compute, and one change it cannot. Say what you tell the consumer in the second case.
8. State what Monday's reader sees if the transaction rolls back.

Expected deliverables. A zone diagram, the field list, the SQL or the swap description, the runnable placement checks, and the failure note.

Evaluation rubric.

You pass when a reviewer can point to the field that must survive a definition change and to the statement that prevents a retry from doubling the day.

Lesson Summary

ETL, ELT, and a split boundary step are decisions about which transform is allowed to destroy information. TechLearningPro keeps contract fields, including checkpoints, in an access-controlled raw zone and applies completion-v3 as versioned SQL that replaces an event-time window. It rejects email at the seat-file boundary, before any shared table exists. Curated rows carry the grain from lesson 10 and a definition version. Consumers read that table. Analysts do not browse landing, and they do not get a second completion in a personal schema.

The enrollment join names a course and a snapshot date, because a learner-only join multiplies facts and a join to "current" enrollment rewrites history. A retry replaces the window. A failure leaves the previous commit in place.

Building Batch Data Pipelines with Python takes the boundary half of this design and makes it a restartable job: configuration, a checkpoint that moves only after a durable write, validation, and a quarantine that does not pretend to be the metric.

Further Reading

• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit — grain, fact loads, and conformed dimensions. The toolkit's load patterns assume you know which history you kept.
• William H. Inmon, Building the Data Warehouse — the corporate information factory's argument for a warehouse of record before departmental marts. Useful as an ownership argument, not as a mandate to skip raw retention.
• Martin Kleppmann, Designing Data-Intensive Applications — derived data and the ability to reprocess. ELT is that idea applied to a warehouse.
• Joe Reis and Matt Housley, Fundamentals of Data Engineering — the lifecycle stage this lesson places inside storage and transformation.
• PostgreSQL documentation: INSERT and transactions — the replace semantics used in the walkthrough. /learn/postgresql and /learn/sql are the primers.
• PostgreSQL 15 MERGE — read it when you choose that statement. Other engines document their own syntax. Do not transplant clauses across vendors.
• Continue this course: Building Batch Data Pipelines with Python, then API and File-Based Data Ingestion.