Modern Data Architecture Overview

Data Engineering & Modern Data Platforms · Lesson 02 · Data Engineering Foundations

Lesson Overview

After the completion-rate incident in lesson 1, someone on the TechLearningPro platform channel pasted a reference diagram: an operational database, a lake, a warehouse, a streaming layer, a feature store, and three reverse-ETL arrows, all feeding "AI." The diagram had every box a conference talk had mentioned. It did not say which box was allowed to answer "how many learners completed lesson one yesterday," which box the player was forbidden to read, or what would be restated when a late progress event arrived on Thursday.

That diagram is the failure mode of this lesson. Modern data architecture is not a catalog of products. It is a set of storage and compute shapes, each with a job, a consistency story, and a cost, chosen because a consumer's grain and freshness require them. The shapes this course will keep distinct are the operational system, the data warehouse, the data lake, the lakehouse, the streaming path, and the serving layer. You can draw all of them. You may not need all of them. A shape that does not change a decision lens answer—correctness, freshness, scale, reliability, security, cost—is decoration.

This lesson stays at overview altitude. Dimensional modeling is lesson 10. File formats are lesson 11. ETL and ELT mechanics are lesson 13. Spark, Kafka, Iceberg, and cloud warehouses each have their own later lessons. /learn/postgresql remains the primer for the operational engine. /learn/kafka remains the primer for the log. What you owe this lesson is the ability to place the TechLearningPro completion mart in one shape, to reject the shapes that would make the Monday number worse, and to draw a marketplace analytics flow with the same discipline.

Planning assumptions, carried forward from lesson 1 and still labeled as assumptions:

• The player and enrollment service use PostgreSQL as the system of record. Peak progress volume is on the order of fifty million events a day.
• Instructor studio and the Monday review need the previous UTC day by 06:00, with a three-day event-time restatement window.
• The player must never read the analytical estate to decide resume or entitlement.
• One squad operates the analytical estate at this stage of the company. A design that needs a second platform team is a staffing request, not a diagram.

Learning Objectives

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

1. Separate an operational store from an analytical store by workload, consistency, and who is allowed to be slowed down.
2. Explain warehouse, lake, and lakehouse as responses to specific failures, including the failure each one reintroduces when the contract is missing.
3. Place batch and streaming paths against a freshness bound, and describe lambda and kappa architectures without treating either as a default.
4. Identify a serving layer and say what it is not allowed to compute that the curated mart should have already defined.
5. Choose a minimal architecture for the lesson-completion product and record the alternative you rejected.
6. Draw a marketplace analytics flow that keeps raw files, curated facts, and live product reads in different shapes.
7. Score a proposed box on the six-part decision lens and remove it when it does not change an answer.
8. Read a short SQL sketch of raw, curated, and served relations as a logical contract, independent of the engine that will store them.

Prerequisites

• Lesson 1: grain, source of record, derived data, event time versus ingestion time, and the completion-mart contract. This lesson does not redefine completion.
• You can read a data-flow diagram and a simple CREATE TABLE statement. Query technique is lesson 6. Storage internals are lesson 8.
• You do not need a cloud account, Spark, or Kafka running locally. Where a technology is named, the point is the property it is being asked to provide.

If the operational database is still a blur, spend an hour in /learn/postgresql on tables, transactions, and why a long analytical scan hurts a live system. Then return. This lesson will still ask you which scan is allowed to run there.

Detailed Core Concepts

Operational systems

An operational system (often called OLTP, online transaction processing) exists to perform the business action. TechLearningPro's enrollment insert and the player's progress update are operational. They are small, they are frequent, they touch a few rows, and they must commit or roll back as a unit. PostgreSQL fits because a transaction can make "payment captured, entitlement written" durable together, and because a primary key lookup for "resume this learner" stays cheap when the working set fits memory and the indexes match the access path.

The properties that make an operational store good at that job make it a poor place for the Monday review:

• Rows are current. History is an accident of audit columns, not a designed grain.
• A scan of fifty million events competes with the learner who is trying to save a checkpoint.
• The schema is shaped for the application write, not for "distinct learners who completed lesson one."
• Analysts with a warehouse password on the primary will eventually run the query that times the player out. This is not a hypothetical. It is the reason analytical copies exist.

A read replica removes some of the load and none of the modeling problem. The replica still has the operational schema, still lags by a replication delay you must state, and still invites SELECT * thinking. Use a replica for incident debugging and for a controlled extract. Do not call the replica a warehouse.

Warehouses

A data warehouse is a store and a modeling discipline for analytical questions over historical, integrated data. It is read-mostly. Loads arrive in batches or micro-batches. Queries aggregate large ranges. Bill Inmon's formulation emphasized a centralized, subject-oriented, integrated, time-variant, non-volatile collection. Ralph Kimball's formulation emphasized dimensional models, one per business process, with conformed dimensions so that "learner" and "course" mean the same thing on every fact. Lesson 10 teaches you to build those models. The architectural consequence matters now.

The warehouse earns its keep when three things are true. The question is historical or cross-system. The operational schema would make the question expensive or ambiguous. More than one consumer must share the answer. The TechLearningPro completion mart meets all three. Enrollment lives in one service. Progress events live in another shape even when they share a database today. The definition of completion is not a column in either schema. A warehouse table at the grain (learner_id, lesson_id, attempt_number) with event-time dates is the integrated form.

Warehouses fail in a familiar way. They become a second copy of every operational table, loaded by SELECT *, with no grain. The engine can be excellent and the asset can still be a swamp that happens to speak SQL. They also fail when a team treats them as real-time operational stores: row-by-row updates from the player, synchronous reads on the request path, and a latency budget the warehouse was never given. A warehouse can be fast. "Fast" is not the same contract as "the player may block on you."

This course does not bind the mart to a vendor. Snowflake, BigQuery, Redshift, and PostgreSQL used analytically are all capable of holding a curated fact at this volume assumption, and they differ in separation of storage and compute, concurrency, and cost shape. Those differences are lessons 53 through 58. An overview that pretends one of them is mandatory is an advertisement. The requirement is a SQL-accessible, historical, integrated fact that can replace a day without doubling it.

Lakes

A data lake stores data in files, usually in object storage, often in an open columnar format such as Apache Parquet, at a cost and flexibility a warehouse load of the 2000s did not offer. Schema can be applied when the data is read. Raw progress payloads can land before every consumer has agreed a column. That is the feature. It is also the failure mode.

A lake becomes a swamp when files have no owner, no partition convention, no schema version, and no check that email did not land in the object. Analysts then disagree because they parsed different versions of the JSON with different null rules. Compute engines scan more bytes than a warehouse would have, and the bill arrives for a dataset nobody serves.

Use a lake, or a raw zone that behaves like one, for the TechLearningPro progress extract because the source shape must be replayable and because definition completion-v4 does not exist yet. Do not serve the Monday number from ad-hoc file reads. Serving belongs to a table with a grain. The raw zone's contract is the one lesson 1 already wrote: projected columns, rejected prohibited fields, immutable batches, a retention period.

Object storage is not a database. A successful put of a file is not an atomic replacement of "Tuesday's facts." If two jobs write the same prefix, readers can see a mix. That single sentence is why lakehouse table formats exist, and why a team that ignores it will publish a half-written day.

Lakehouses

A lakehouse puts table semantics on files in object storage: a catalog of tables, a transaction log of commits, schema enforcement or evolution, and the ability to replace a partition as a commit rather than as a folder of hope. Apache Iceberg, Delta Lake, and Apache Hudi are the formats this course will examine in lessons 50 and 51. They are not interchangeable in their details. They are interchangeable in the problem they were built to solve: readers should not see a partial write, and a restatement of Tuesday should be a commit that replaces Tuesday.

Properties you may rely on at this altitude, and only these:

• A commit is the unit of visibility. Readers on the previous snapshot do not see a half-loaded day.
• The table has a schema. A new column is a migration, not a surprise key in one file.
• Time travel, where the format supports it, lets you read the snapshot that was served on Monday morning. That is an audit feature. It is not a substitute for a definition version column if the meaning changed inside the schema.
• Concurrent writers need a conflict policy. "We will only have one job" is a policy until a backfill overlaps the daily job. The format can detect the conflict. It cannot invent the business rule.

What you must not invent is a vendor-only API and call it the standard. This lesson uses SQL for the logical model and names the open formats as the place where atomic replacement will live in production. A teaching environment may implement the same tables in PostgreSQL. The semantics you are practicing—the grain, the replace-by-day, the prohibited columns—are the architecture. The file format is an implementation that later lessons open up.

Lakehouses do not remove modeling. A swamp with ACID commits is a consistent swamp. They also do not remove the operational database. The player still needs a row lookup in PostgreSQL. Pointing the player at an object-storage table to save a service is how you trade a millisecond primary-key read for a planning problem you did not measure.

Streaming paths

A streaming path moves events continuously from a log into a consumer or a table. Apache Kafka is the log this course will use when a log is justified; the mechanics live in /learn/kafka and in module 4. At overview altitude the log gives you three properties: an ordered, replayable history per partition; the ability for more than one consumer to read without polling the database; and a retention window that is independent of the warehouse.

Those properties are valuable when at least one of the following is true. The operational database must not be polled. More than one consumer needs the same event. A consumer's freshness bound is shorter than a batch cycle you can actually run. Progress events may meet the first two before they meet the third. The completion mart's consumer does not meet the third. A support tool that tails "this learner's last checkpoint" might. Those are different products. Putting both on one architecture slide and giving them the same latency budget is how the mart becomes a streaming job nobody needed.

Lambda architecture, as Nathan Marz described it, runs a batch path that recomputes correct history and a speed path that serves approximate recent results, with a merge at query time. The benefit is honesty about approximation. The cost is two implementations of the definition. They will drift, which is the completion-rate incident with extra steps.

Kappa architecture, associated with Jay Kreps' argument for the log, uses the stream as the source and recomputes by replaying it, so there is one code path. The benefit is a single definition. The cost is that reprocessing and serving still have to be designed, and a daily metric does not become simpler because the transport is a log.

For TechLearningPro's completion mart the overview decision is a batch transform over retained raw events, which you may land from a log or from a file extract. The definition runs once. If a later consumer needs a five-minute provisional count, that is a second product with "provisional" in the name, not a silent speed path merged into the official rate. Module 4 reopens this if the freshness bound changes. The reopening condition is written in the decision record later in this lesson.

Serving layers

The serving layer is where a consumer reads. It might be the warehouse itself, a BI extract, an internal API in front of the mart, or a specialized store. Serving is allowed to optimize reads. It is not allowed to invent a second definition.

Feature stores and reverse ETL appear in platform conversations. A feature store serves model features with a point-in-time story. Reverse ETL copies a curated result back into an operational tool, such as an email system. Both are serving patterns. Both are out of scope for the completion mart until a consumer signs a contract. Drawing them now would add paths that can write into systems of record, which is the arrow lesson 1 forbade.

How to choose

Choose the smallest set of shapes that satisfies the lens.

1. Leave the fact in the operational store when the question is "the current row," the scan is cheap, and you are not building a shared historical definition.
2. Add an extract and a raw zone when you must replay, retain a source shape, or protect the primary from scans.
3. Add a curated warehouse or lakehouse table when more than one consumer must share a grain and a definition, or when history must be restated as a commit.
4. Add a log when polling the database is the problem or a second consumer needs the same feed. Do not add it only as a badge.
5. Add a speed path only for a named provisional product.
6. Add a serving API when the consumer is an application with a latency bound the warehouse connection should not sit on. The API reads the mart. It does not recompute completion.

A proposal that adds a box has to say which numbered step it is and which lens answer changes if the box is removed. If none change, remove the box.

Architecture and Design Diagrams

The minimal architecture for the completion product uses three shapes. Operational PostgreSQL stays the system of record. Object storage holds the raw zone. A curated table, in a warehouse or a lakehouse, holds the mart. No streaming consumer is on the official path.

mermaidCopy
flowchart LR
    subgraph oltp [Operational shape]
        player[Lesson player]
        enroll[Enrollment service]
        pg[(PostgreSQL primary)]
        replica[(Read replica for extracts)]
        player --> pg
        enroll --> pg
        pg --> replica
    end

    subgraph lake [Raw file shape]
        raw[(Object storage: daily event batches)]
    end

    subgraph curated [Analytical table shape]
        job[Daily transform and restatement]
        mart[(Completion mart: one grain, one definition)]
        job --> mart
    end

    replica -->|projected columns only| raw
    raw --> job
    mart --> bi[Monday review SQL]
    mart --> api[Instructor studio API]
    player -.->|must not read| mart
    api -.->|must not write| pg

The extract starts at the replica so a bad analytical query cannot take the primary's connections. The replica is still operational schema. The projection from lesson 1 applies at this arrow. The job replaces mart partitions for the restatement window in one commit. The studio API reads a publication timestamp and shows it. Support does not appear on this diagram, because support's question is operational and belongs on PostgreSQL.

A marketplace example, Fieldnote Retail from lesson 1 extended with a campaign question, shows the same shapes with one extra source. Marketing wants a provisional same-day gross. Finance wants closed net revenue. Those are two serving products. They are not two copies of the same unlabeled number.

mermaidCopy
flowchart TD
    orders[(Order PostgreSQL)] --> extract[Nightly and hourly extracts]
    returns[Returns system] --> extract
    extract --> rawfiles[(Raw zone: order and return events)]
    rawfiles --> financejob[Net revenue transform]
    financejob --> finmart[(Finance fact: sale and return rows)]
    rawfiles --> provisional[Provisional gross aggregate]
    provisional --> email[Marketing email labeled provisional]
    finmart --> close[Daily close at 08:00 UTC]
    email -.->|must not be added to| close

Lambda would have drawn the provisional path and the finance path as two implementations of "revenue" merged in the BI tool. This diagram refuses the merge. Kappa would have drawn a log as the only source and replayed it for both. Fieldnote may adopt a log later if the order database cannot support the hourly extract. That condition is not met by the volume assumption of a few hundred thousand orders. The hourly extract is allowed to be wasteful and simple. The official number still comes from the finance fact.

A decision flow keeps the next architecture review from restarting at the tool list.

mermaidCopy
flowchart TD
    q[Which decision and which freshness bound?]
    op{Is it a current-row operational read?}
    rawneed{Must we replay or protect the primary?}
    shared{Must many consumers share one definition?}
    fast{Is there a second consumer inside the batch cycle?}
    q --> op
    op -->|yes| pgread[Read PostgreSQL or a replica]
    op -->|no| rawneed
    rawneed -->|yes| land[Land a projected raw batch or log]
    rawneed -->|no| shared
    land --> shared
    shared -->|yes| mart[Curated table with an atomic day replacement]
    shared -->|no| query[A reviewed query, not a new platform]
    mart --> fast
    fast -->|yes| label[Separate provisional product, labeled]
    fast -->|no| serve[Serve the curated table]

Technical Explanation

Workload shapes, stated as access patterns

Architecture arguments stay vague until the access pattern is written down.

Columnar storage matters for the aggregate pattern because the query reads a few columns for many rows. Row storage matters for the point read because the query wants most of one row. You do not need a file-format lesson to use that distinction. You do need it to resist "put everything in one engine."

Atomic replacement is the restatement mechanism

Lesson 1 required a restatement of event-time days to replace facts, not append them. The architecture has to make that replacement atomic from the consumer's point of view.

In PostgreSQL, used here as the teaching engine and as a legitimate small-warehouse choice, one transaction can delete the restatement window and insert the new facts, and readers in the READ COMMITTED isolation level either see the old day or the new day, not a mix of the statement's intermediate state, as long as they are not themselves writing the table. A reader that started before the commit continues to see the snapshot it started with, under REPEATABLE READ or SERIALIZABLE. The practical serving rule is simpler: the studio API reads only after the job publishes a row in mart_publication for that run. Consumers that ignore the publication table and scan mid-load will see whatever the engine's isolation gives them. The publication row is the contract. Isolation is the backstop.

In a lakehouse format the same idea is a metadata commit. Readers pinned to the previous snapshot do not observe the new files until they refresh. The job does not delete files out from under a current reader if the format is being used as designed. Lesson 50 shows the log. The overview rule is: if your "commit" is a folder rename that readers poll without a snapshot, you do not have this property, and a Monday query can count a mixture of two definition runs.

Schema-on-read and schema-on-write

Schema-on-write means the store rejects a row that does not match the table. PostgreSQL and warehouse tables do this. The completion mart should do this. A missing attempt_number is a load failure, not a null that becomes attempt zero in a BI tool.

Schema-on-read means files are stored as they arrived, and the reader applies a schema. Raw JSON in object storage is schema-on-read. It is appropriate for the raw zone only if the ingestion contract already rejected prohibited and unparseable batches. Schema-on-read is not an excuse to skip the contract. It is a decision about where evolution happens. When the player adds a field, old files remain readable if the reader treats the field as optional. The mart does not gain a column until the transform and the consumers agree.

Apply schema-on-write at the boundary you serve. Apply a versioned, explicit schema-on-read at the raw boundary. Applying neither is the swamp. Applying schema-on-write to raw payloads too early forces a migration before you know which fields matter, and teams respond by stopping ingestion. That response is worse than a disciplined raw schema.

Consistency boundaries

Name the boundary, or the architecture will invent one in production.

• The operational transaction boundary is the player's write and the enrollment write. The mart is outside it. A committed checkpoint can be invisible to the warehouse for hours. That is acceptable because no operational decision waits on the mart.
• The publication boundary is the mart commit plus the publication row. Official numbers are read from a published snapshot only.
• The provisional boundary, if it exists, is labeled and is not added to the official number.
• Identity consistency is not attempted by copying the identity database into the lake. Learner id is a key. Attributes such as email are fetched, if ever, through a governed path that this overview does not open.

Cross-system transactions between PostgreSQL and object storage do not exist in any simple form. The extract can fail after the replica read and before the object put. The watermark design from lesson 1 covers that: a batch is identified, a retry of the same batch is idempotent, and the mart job does not consume a batch the contract rejected. Do not invent a two-phase commit between the primary and the lake. You will not operate it.

Cost shape, without a fake price list

Storage of raw Parquet or compressed JSON for a year of progress events is usually cheaper than keeping the same bytes inside a warehouse that charges for both storage and the history of every revision. Compute is the other way around: an engine that scans raw JSON for every analyst will cost more than a curated columnar table scanned by those same analysts. The architecture splits them so that exploration of new definitions can read raw data under a quota, and the official metric reads the mart.

A streaming cluster that runs all night to feed a daily metric pays for hours of brokers and consumers the freshness bound does not need. A warehouse that is sized for the Monday concurrency and left at that size until Thursday pays for idle time if the vendor's billing is always-on. Those are reasons to separate storage and compute, or to use a modest PostgreSQL analytical instance while the squad is small. They are not reasons to skip the curated table.

Write the cost question as: which bytes are stored twice, for how long, and which compute runs when nobody is looking? If you cannot answer, the diagram has a box you have not priced.

Security placement by shape

Operational PostgreSQL is inside the product network, with application roles that can write progress and a replica role that can only read the projected columns. Object storage for the raw zone is not a public bucket. The extract role can put objects and cannot read the identity store. The mart role can be queried by the BI warehouse role and the studio API role. Analysts do not get the raw bucket by default, because raw files are where a contract miss would sit, and because a wider raw schema is a larger blast radius. Break-glass access to raw data is time-limited and logged.

None of this requires a specific cloud product. It requires the diagram to show roles at the arrows. A single shared credential labeled data-eng on every box is an architecture that has postponed its first incident.

What this overview refuses to decide

It does not pick Iceberg versus Delta versus Hudi. It does not size a cluster. It does not design a star schema. It does not claim PostgreSQL will hold the mart at ten times the planning assumption. The reopening conditions are explicit in the walkthrough. An overview that decides those things without measurements is the conference diagram this lesson started by rejecting.

Practical Implementation or Design Walkthrough

Problem

Record the architecture decision for the completion mart in a form a later team can reverse, and make the logical tables real enough to query. The production raw zone may be object storage. The teaching tables below are PostgreSQL 16, because the contract is SQL and because a squad of one can operate them. The comments say which table is standing in for a file prefix.

Options that were real

Decision: B now, with raw batches as files (or as a raw_progress_event table in teaching environments) and the mart in PostgreSQL. Move the mart to a lakehouse or warehouse engine when a measured limit is hit, not when a diagram is redrawn. Do not adopt C until a second consumer needs the feed or the replica extract misses the freshness bound. D is the likely destination for the raw zone if file listing and partial writes become the incident. It is not a prerequisite for publishing completion-v3.

Reopening conditions, all of them measurable:

• The daily job plus restatement misses 06:00 UTC on two peak days, and the cause is compute or storage rather than a logic bug.
• More than one production consumer needs the event feed without polling the replica.
• Analyst concurrency on the mart starves the load transaction.
• A partial file write is observed by a reader.

Logical schema

Run this in a database you can throw away. It is standard PostgreSQL. GENERATED ALWAYS AS IDENTITY and timestamptz are PostgreSQL features documented in the version 16 manual. They are not a vendor warehouse dialect.

sqlCopy
-- Teaching stand-in for the raw zone. In production these rows are
-- immutable files. The grain of a raw row is event_id.
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 CHECK (event_type IN ('checkpoint', 'completed')),
    attempt_number integer NOT NULL CHECK (attempt_number >= 1),
    checkpoint_number integer,
    event_time timestamptz NOT NULL,
    ingestion_time timestamptz NOT NULL DEFAULT now(),
    source_batch_id text NOT NULL,
    CONSTRAINT raw_progress_event_no_email CHECK (learner_id NOT LIKE '%@%')
);

-- Curated fact. Grain: one completion per learner, lesson, and attempt.
CREATE TABLE lesson_completion (
    learner_id text NOT NULL,
    lesson_id text NOT NULL,
    attempt_number integer NOT NULL,
    event_time timestamptz NOT NULL,
    definition_version text NOT NULL,
    source_event_id text NOT NULL,
    PRIMARY KEY (learner_id, lesson_id, attempt_number)
);

-- Consumers read a day only after a row exists here for that publication.
CREATE TABLE mart_publication (
    publication_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    definition_version text NOT NULL,
    event_day date NOT NULL,
    published_at timestamptz NOT NULL DEFAULT now(),
    source_batch_id text NOT NULL,
    UNIQUE (definition_version, event_day, source_batch_id)
);

The check constraint on learner_id is a backstop, not the ingestion contract. The contract checker from lesson 1 runs before insert. A serving query that ignores mart_publication is a review defect even though PostgreSQL will happily answer it.

A small decision record in Python

The guild keeps the decision next to the code that reviews proposals. This reviewer does not open a cloud account. It checks that a proposal names a consumer freshness bound, a shape, and a reopening condition. Standard library only. Python 3.11+.

pythonCopy
from dataclasses import dataclass

ALLOWED_SHAPES = frozenset({
    "operational",
    "raw_files",
    "curated_table",
    "stream",
    "serving_api",
    "provisional",
})


@dataclass(frozen=True)
class ShapeProposal:
    name: str
    shape: str
    consumer: str
    freshness_minutes: int
    changes_lens_answer: str
    reopening_condition: str

    def __post_init__(self) -> None:
        if self.shape not in ALLOWED_SHAPES:
            raise ValueError(f"unknown shape: {self.shape}")
        if self.freshness_minutes < 0:
            raise ValueError("freshness_minutes must be >= 0")
        if not self.consumer.strip() or not self.reopening_condition.strip():
            raise ValueError("consumer and reopening_condition are required")
        if not self.changes_lens_answer.strip():
            raise ValueError("state which lens answer this shape changes")


def reject_unjustified_stream(proposals: tuple[ShapeProposal, ...]) -> tuple[str, ...]:
    """A stream is unjustified when every consumer can wait at least a day."""
    findings: list[str] = []
    for proposal in proposals:
        if proposal.shape == "stream" and proposal.freshness_minutes >= 24 * 60:
            findings.append(
                f"{proposal.name}: stream does not change a daily freshness bound"
            )
        if proposal.shape == "provisional" and "provisional" not in proposal.consumer.lower():
            findings.append(
                f"{proposal.name}: provisional shape requires a consumer labeled provisional"
            )
    return tuple(findings)


def _demo() -> None:
    mart = ShapeProposal(
        name="completion mart",
        shape="curated_table",
        consumer="Monday review",
        freshness_minutes=24 * 60,
        changes_lens_answer="shared definition and atomic restatement",
        reopening_condition="job misses 06:00 UTC on two peak days",
    )
    badge = ShapeProposal(
        name="progress topic",
        shape="stream",
        consumer="Monday review",
        freshness_minutes=24 * 60,
        changes_lens_answer="none beyond transport",
        reopening_condition="a second consumer must not poll the replica",
    )
    findings = reject_unjustified_stream((mart, badge))
    assert findings == (
        "progress topic: stream does not change a daily freshness bound",
    )
    assert reject_unjustified_stream((mart,)) == ()
    print("stream_rejected=1")


if __name__ == "__main__":
    _demo()

The reviewer is intentionally narrow. It will not stop a bad warehouse model. It will stop the badge. Human review still reads changes_lens_answer and decides whether the sentence is true. An empty finding list means "not obviously a badge," not "build it."

Expected teaching output

python3 architecture_review.py prints stream_rejected=1. Loading the SQL and inserting one raw event with an email-shaped learner_id fails the check constraint. Inserting a completion and forgetting mart_publication is allowed by the engine and forbidden by the serving rule; the studio API in a later exercise must refuse to read when no publication exists for that day.

Operational note

The daily job's transaction is: build the replacement rows for the restatement window in a staging table, delete those days from lesson_completion inside the definition version, insert the staging rows, insert mart_publication, and commit. If the process dies before commit, consumers still see the previous publication. If you commit the delete in an earlier transaction than the insert, consumers see a hole. That hole is an architecture defect, not a SQL typo. Lesson 6 will write the INSERT ... SELECT. This lesson only fixes the boundary around it.

Enterprise Real-World Scenarios

The completion mart, after the boxes were removed

The platform squad arrived with diagram D plus a stream plus a feature store, because the annual plan said "modern data platform." The consumer list was still the Monday review and instructor studio. Freshness was still the previous day. The replica was healthy at the planning volume.

The review kept PostgreSQL for operations, files for raw batches, and a curated table for the mart. The stream was recorded as option C with a reopening condition: a second production consumer that must not poll the replica, or a freshness bound under one hour for an official number. The feature store was removed until a model had a signed feature contract. Instructor studio got a thin API because embedding a warehouse driver in the web tier would couple deploys and because the page needs one course's counts, not an analyst's scan. The API reads published snapshots only.

Two weeks later an analyst asked for "completions in the last hour" for a launch-day war room. That request was granted as a provisional query on the replica with a banner, limited to launch day, not as a new streaming mart. The official rate did not move when the war-room query did. The architecture held because the provisional product was labeled and then deleted.

The cost note in the decision record was qualitative and honest: one extra database role, one bucket, one job, no always-on stream. The quantitative bill was left for lesson 58, when there is a bill to read.

Marketplace analytics for a staff-training marketplace

The case is Northline Marketplace, a teaching system in which independent instructors sell courses to companies. It is not the TechLearningPro production ledger. Buyers asked for a weekly dashboard: gross merchandise value, refunds, and lesson completion of purchased courses. The first architecture copied the operational order tables into a warehouse every hour and let each account manager write SQL.

The redraw, using this lesson's rules:

• Operational: orders, entitlements, and payouts stay in PostgreSQL. Card data stays at the payments vendor. The extract role cannot read card columns because those columns are not in the replica view.
• Raw: order events, refund events, and progress events land as separate batches. Refunds do not overwrite orders.
• Curated: a sales fact at grain (order_line_id, event_type) and the completion mart at its existing grain. They share a conformed course_id. Lesson 10 will draw the dimensions. The overview only forbids a single wide table that mixes money and checkpoints, because the restatement windows differ: money restates for the refund policy, completion restates for three days.
• Serving: the weekly dashboard reads the curated facts. A "live GMV" ticker on the internal TV was rejected. The consumer could not name an action they would take between hourly refreshes that they would not take the next morning.
• Stream: not adopted. The order rate in the case is tens per second at peak, and the replica supports an hourly extract. The reopening condition is replica CPU or replication lag breaching the extract window on two campaign days.
• Security: company buyers see only their tenant's rows. That is a serving-layer filter with a tenant key on the fact, tested by a query that attempts a cross-tenant read and must return zero. It is not "a separate warehouse per customer," which this squad cannot operate.

The dashboard stopped moving when an account manager refreshed it mid-load, because it keys off mart_publication. That single behavior justified the curated shape more clearly than any tool comparison.

Best Practices

• Start from the access pattern and the freshness bound. Add a shape only when removing it changes a lens answer.
• Keep the system of record operational. Analytical stores are derived and rebuildable.
• Put atomic replacement around restatement. A publication record tells consumers which snapshot is official.
• Use schema-on-write for served facts and a versioned contract for raw files. Do not use "schema-on-read" as a reason to skip validation.
• Separate provisional numbers from closed numbers in the name, the table, and the picture.
• Extract from a replica or an existing export, with a column projection. Do not scan the primary for the Monday meeting.
• Record the engine you chose as replaceable when the data model is standard SQL and the files are in an open format.
• Write reopening conditions as measurements: missed deadlines, lag, concurrency, partial reads.
• Give each arrow a role. A shared admin credential is not an architecture.
• Leave dimensional design, file-format internals, and vendor features to their lessons. Name the property you need, not the feature list.

Common Mistakes and Anti-Patterns

The reference architecture as a checklist. Every box from a vendor slide is copied into the design before a consumer is named. The team then has to staff a stream, a lake, a warehouse, and a feature store to produce yesterday's count. The repair is the decision flow in this lesson: operational read, raw landing, curated table, optional provisional product. Boxes that survive must change a lens answer.

The replica is the warehouse. Analysts are given the replica and told to be careful. They will be careful until the quarter closes. Definitions fork. A long join locks nothing in PostgreSQL's default isolation and still consumes IO the replica needs for extracts. The repair is a curated mart and a revocation of ad-hoc access to wide operational views. Keep a narrow extract role.

Lambda with two silent definitions. The batch job and the speed job are written by different people, and the BI tool adds them. Completion, or revenue, depends on which path answered. The repair is one official definition. A speed path is a different product with a label, or it does not ship.

Folder rename as a commit. The job deletes Tuesday's prefix and uploads new files while a query lists the prefix. Counts dip and then double. The repair is a real snapshot: a database transaction or a lakehouse commit, plus a publication row consumers must honor.

Lakehouse as a synonym for "no model." The format guarantees commits. It does not pick a grain. Teams move the swamp into Iceberg and declare the architecture modern. The repair is the curated table with a primary key or an equivalent merge key, a definition version, and a consumer who reads only that table.

Serving-layer mathematics. The studio API recomputes completion from raw events "so it can be faster," and the Monday review uses the mart. The two numbers diverge for the same reason as lesson 1. The repair is to serve a published aggregate. Speed comes from precomputation and a cache, not from a second formula.

Tool lock as a badge of seriousness. A design is rejected because it uses PostgreSQL for the mart rather than a cloud warehouse, despite meeting the bound. The repair is the reopening condition. Replace the engine when the measurement trips, and keep the SQL grain so the move is a migration rather than a rewrite of the meaning.

Troubleshooting and Design Review

Symptom: The Monday number changes between 05:50 and 06:10 with no late data.

Likely cause: readers are querying the mart during the delete-and-insert, and the two statements are not in one transaction. Alternatively, two jobs overlap.

Investigation: read mart_publication for duplicate runs. Compare transaction boundaries in the job. Look for a second writer.

Corrective action: one transaction for replacement and publication. A lock or a job-level lease so the backfill cannot overlap. Consumers select the latest publication and the facts it names.

Symptom: The primary's checkpoint latency rises when the daily job starts.

Likely cause: the extract is reading the primary, or the mart lives in the same instance and the load competes with the player.

Investigation: identify the connection role and the server. pg_stat_activity on the primary will show the extract if it is in the wrong place. Confirm whether the analytical tables share the instance.

Corrective action: move the extract to the replica. If the mart shares an instance with the player, move the mart. Sharing was a teaching convenience, not a requirement. The player path has no business on analytical storage.

Symptom: Two engineers describe the raw zone as both "the source of truth" and "temporary landing."

Likely cause: the architecture never separated system of record from replay input. People use "source of truth" for whichever copy they have.

Investigation: ask which store wins for live resume, and which store is allowed to be deleted and rebuilt.

Corrective action: update the diagram's nouns. PostgreSQL is the system of record for position. The raw zone is the replay input for the mart. Neither phrase is "the data."

Symptom: A lakehouse migration is proposed after one slow query.

Likely cause: the slow query scans raw JSON, or it joins operational tables, and the proposed fix changes the engine instead of the access pattern.

Investigation: capture the query, the bytes scanned, and the freshness it needed. Ask whether a curated columnar table on the current engine would answer it.

Corrective action: curate the fact first. Re-measure. Open the engine question only if the reopening condition is actually true. Lesson 31 is the performance lesson for Spark; do not skip ahead of a missing mart.

Symptom: Marketing's provisional gross is quoted in the board note as net revenue.

Likely cause: the serving layer did not label the product, or a dashboard unioned the two facts.

Investigation: find the query behind the note. Search for unions between provisional and finance tables.

Corrective action: rename objects so the provisional path cannot be selected by accident, revoke broad access, and publish a single official metric list. This is a serving-contract failure, not a visualization bug.

A design review for this lesson walks the decision flow out loud. For each box: what access pattern, what freshness, what commit means, who may read, who may write, and what measurement removes the box. If the author cannot answer for a box, the box is a drawing.

Interview Questions and Answers

Q1. How do you distinguish an operational store from a warehouse without naming vendors?

The operational store serves the business transaction: small writes, point reads, a current-state schema, and a latency bound on the user path. The warehouse serves historical, integrated questions shared by more than one consumer, loaded by batch or micro-batch, with a grain designed for aggregation. I judge a design by which queries are allowed to run where. A current resume read belongs on the operational store. A quarter of completions belongs on the curated mart. A read replica is still operational. It is an extract source, not a warehouse, until the data is remodeled and the scan can no longer hurt the product.

Q2. What failure produced data lakes, and what failure do lakes reintroduce?

Warehouses were awkward and expensive for raw, semi-structured, and large files, and teams wanted to keep data before every schema was agreed. Lakes, usually files in object storage, made that cheap. They reintroduce ungoverned data: no owner, no atomic update, schema decided differently by every reader, and analytical scans over JSON that cost more than the warehouse they replaced. I use a lake as a raw, contracted, retention-limited zone. I do not serve an official metric from ad-hoc file reads.

Q3. What does a lakehouse add that a bucket of Parquet files does not?

A commit that readers observe or do not observe as a whole, a table schema, and a catalog so "Tuesday" is a snapshot rather than a prefix listing. Iceberg, Delta Lake, and Hudi are different implementations of that idea, and this course treats their internals later. None of them chooses a grain or a metric definition. A partial file upload that readers can list is the defect the commit exists to remove. If the design still publishes by folder rename, the format is not being used.

Q4. When would you choose a lambda architecture, and why do many teams regret it?

Lambda is a batch path for correct recomputation and a speed path for a low-latency approximation, merged at query time. I would consider it only when a consumer truly needs the approximation and has agreed it is approximate. Teams regret it because the two paths implement the business rule twice and drift, so the merged number depends on which path is ahead. For an official daily completion rate I want one definition. A provisional product can exist beside it, labeled, without being merged into the closed figure.

Q5. What is kappa architecture in one paragraph, and when is it heavier than the problem?

Kappa treats the log as the system you replay: one code path computes results, and history is recomputed by reading the log again rather than by maintaining a separate batch implementation. That removes the dual-definition risk of lambda if you actually use one job. It is heavier than the problem when the source is a database you can extract once a day, the consumer's bound is the next morning, and you would be operating brokers, retention, and replay machinery for a single batch consumer. I adopt the log when polling hurts the database or several consumers need the same feed. The primer for the log itself is /learn/kafka.

Q6. Where should a metric definition live if a serving API needs to be fast?

In the curated mart, computed by the owned job and marked published. The API reads that result, caches it if the page requires it, and returns the publication time. If the API recomputes the metric from raw events, it becomes a second definition with a second freshness story. Speed is a serving concern. Meaning is a curation concern. Mixing them is how instructor studio and the Monday review diverge.

Q7. A director asks why the mart is still PostgreSQL. How do you answer?

I separate the logical architecture from the engine. The logical pieces are a projected raw zone, a curated grain, an atomic restatement, and a publication record. PostgreSQL can hold those pieces inside the current volume assumption and the current squad's skills, and the SQL is portable. I have written reopening conditions: missed deadlines caused by the engine, analyst concurrency starving loads, or a partial-read incident on files. When one of those is measured, we move the mart to a warehouse or lakehouse engine without rewriting the definition. Choosing a cloud warehouse first would add a cost and an operational skill the consumer has not asked for.

Q8. How do you keep a provisional same-day number from being quoted as the closed number?

Give it a different table, a different name, and a consumer that includes the word provisional. Do not union it with the finance or completion fact in the official dashboard. Restrict grants so the board extract cannot read it. Put the publication rule on the official fact only. In review I look for queries that add the two. The architecture fails if a screenshot can hide which product it came from. Labeling in a chart subtitle is not enough if both products sit in one unlabeled dataset.

Q9. What is the consistency boundary between a checkpoint commit and the completion mart?

There is no distributed transaction, and there should not be one. The player commits the live position in PostgreSQL. The mart publishes later, on its schedule, from a projected copy. A learner can be "complete" in the player and not yet in the mart. Support and the player read PostgreSQL for that question. Official historical rates read a published mart snapshot. I document the gap as the freshness contract rather than closing it with a two-phase commit between the database and object storage.

Q10. Which access pattern would make you reject object storage as the serving store for instructor studio?

A point read, one course, on a page that must return in tens of milliseconds, served to many instructors at once. Object storage is the raw landing zone, not an OLTP index. I would also reject a warehouse scan embedded in the web request if it forces the page to depend on analyst concurrency and long planning times. The studio reads a small published aggregate through an API. The warehouse remains the place for the open-ended Monday questions.

Q11. How do shared dimensions show up in an overview, before you draw a star schema?

As a warning and a key. The sales fact and the completion fact must share course_id and learner_id meanings, or the marketplace dashboard will add incompatible rows. I do not need the full dimensional model to forbid a wide table that mixes refunds and checkpoints with different restatement windows. Lesson 10 builds the dimensions. The overview's job is to keep facts that change on different clocks in different tables and to name the conformed keys they will share.

Q12. What would you delete first from a reference architecture slide that includes a lake, a warehouse, a stream, a feature store, and reverse ETL?

Whatever does not have a consumer and a freshness bound. In the completion-mart case that is the feature store and reverse ETL immediately, then the stream, until a second consumer or a tighter official bound appears. I would keep a raw zone and a curated table. I delete by asking "which lens answer changes if this box is gone?" and I write the reopening condition for each deleted box so the deletion is a decision, not a preference.

Hands-On Exercises

Exercise: Draw a marketplace analytics architecture

Problem statement. Northline Marketplace needs a weekly view of gross sales, refunds, and course completion for each buyer tenant. Produce the architecture. Use your own volumes and bounds. Do not copy the lesson's numbers.

Tasks.

1. List consumers and one decision each. Mark one requested metric as out of scope or provisional, and say why.
2. Name the operational systems of record and the extracts. Include a "must not read" and a "must not write" relationship.
3. Choose raw, curated, and serving shapes. For each, state the access pattern and the commit or publication rule.
4. Define two fact grains that must not be collapsed into one table. Name the conformed keys they share.
5. Decide whether a log is in the design. If it is not, write the measurement that would add it.
6. Draw the data flow in Mermaid or an equivalent, including the provisional path if you have one, and an arrow that forbids merging it into the closed number.
7. Write four reopening conditions that would force you to change an engine or add a shape.
8. Describe the tenant-isolation rule at the serving layer and one test that would fail if it broke.

Expected deliverables. A two-page decision record, the diagram, the grain notes, and the reopening list.

Evaluation rubric.

You pass when a reviewer can remove any box and you can say, without inventing a new goal, what gets worse.

Lesson Summary

Modern data architecture is a small set of shapes matched to access patterns. Operational stores run the product. Raw files preserve replay. Curated tables, in a warehouse or a lakehouse, publish a shared definition with an atomic restatement. Streams and provisional paths are separate decisions, justified by a consumer the batch path cannot serve. Serving layers read the published definition. They do not invent another one.

The TechLearningPro completion mart does not need every box on a reference slide. It needs a replica extract, a raw zone, a curated grain, and a publication record, with the stream written down as a reopening condition rather than as a default. Northline's sales fact and completion fact stay apart because their clocks differ, and they share keys so a later dimensional model can conform them.

Lesson 3 assigns owners to these shapes. A diagram without an accountable squad for the extract, the job, and the publication row will fail in the same way as a diagram with too many boxes: the Monday number will move, and nobody will have the right to stop the query that moved it.

Further Reading

• Joe Reis and Matt Housley, Fundamentals of Data Engineering — architecture generations, warehouses, lakes, and the warning against tool-first design.
• Martin Kleppmann, Designing Data-Intensive Applications — OLTP versus analytics, derived data, and batch versus stream. The chapter on batch processing is the right companion to the restatement discussion.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit — why analytical schemas are not operational schemas. Lesson 10 does the modeling.
• William Inmon, Building the Data Warehouse — the classical warehouse properties. Read them as requirements, then compare them to the lakehouse commit model.
• Jay Kreps, "The Log: What every software engineer should know about real-time data's unifying abstraction," and his writing on turning the database inside out — the argument behind kappa, to be used when a log is justified.
• Apache Iceberg spec, Delta Lake protocol, and Apache Hudi — the table formats named in this lesson. Lessons 50 and 51 open them. Do not treat a blog summary as the spec.
• PostgreSQL 16 documentation — isolation and transactions, the teaching engine for atomic replacement. Primer: /learn/postgresql.
• Continue this course: /learn/data-engineering/data-engineering-roles-and-responsibilities. The Kafka primer, when you need the log's mechanics, is /learn/kafka.