Where the Data Goes Next

Take one product and watch what it asks of its database over a single morning. The wine-cellar application — the running example for this entire course — stores collectors' bottles, their acquisition prices, and a nightly feed of market valuations. Its operational schema is the one from Guide Nº 06: cellars, wines, regions, bottles, and market_prices.

The application's traffic is thousands of requests per minute, each one touching a handful of rows: fetch this cellar and its 38 bottles, insert one bottle, set consumed_at on another, read one wine's most recent price. Every one of those queries is selective — it knows exactly which rows it wants, arrives at them through an index, and returns in single-digit milliseconds. This is OLTP: online transaction processing, optimized for latency and concurrency, where success means a p99 under 50 ms while 400 writers commit concurrently.

The business asks a different shape of question entirely. "Which regions appreciate fastest over five years?" touches no single row in particular. It scans 42 million rows of market_prices, joins them to wines and regions, groups, and returns twelve rows — one per region. It is unselective by nature: the answer is a property of the whole population, so the whole population must be visited. This is OLAP: online analytical processing, optimized for throughput per query, where success means the scan finishes in seconds rather than minutes and nobody cares about p99 concurrency because there are eleven analysts, not forty thousand users.

Notice what is not the difference. It is not data volume, complexity, or importance. It is selectivity: the transactional query knows which rows it wants before it starts, and the analytical query cannot know, because the answer is a property of all of them. Every architectural decision in the rest of this course descends from that single asymmetry.

The anti-pattern has a name and a very reliable symptom curve. Pointing BI at the production database looks like this: a dashboard tool holds a connection to the primary Postgres instance, refreshes on a schedule, and each refresh issues the five-year scan. The symptom is that application latency degrades on a timetable — a p99 that is fine at 8:45 and terrible at 9:00, recovering by 9:20, every weekday. Engineers chase it as an application problem for weeks because it looks like traffic, and the dashboard is invisible from the application's telemetry.

Two separate damages are happening, and keeping them apart is what lets you argue correctly about fixes.

The first is contention. The scan pulls 6.72 GB through a buffer cache sized for the transactional working set, evicting the pages the checkout path depends on; those requests now go to disk. On Postgres specifically, the long-running query also holds an old snapshot open, which blocks vacuum from reclaiming dead tuples, so table bloat grows and index scans get slower for everyone. Meanwhile the writes commit fine, so nothing errors — it just gets slow, which is the hardest failure to diagnose.

The second is physics. Even alone on an idle machine, the scan is slow, because row storage forces it to read all five columns of every row to compute an average over one. That damage is not caused by sharing; it is caused by layout, and no amount of isolation repairs it.

Now the byte arithmetic, because it is the argument that ends the meeting. Take market_prices, the nightly feed of valuations: five years of daily observations across the wine catalogue, 42 million rows. Its columns are id (8 bytes), wine_id (8), source (a text label, averaging 24 bytes stored), observed_at (8), and price_cents (8) — plus per-row overhead. Call the effective stored width 160 bytes per row once page headers, tuple headers, and alignment padding are counted. Total: 6.72 GB.

In row storage, the unit of storage is the record. Every field of row 1 sits contiguously, then every field of row 2, packed into 8 KB pages. This is exactly what an OLTP workload wants: SELECT * FROM bottles WHERE cellar_id = 8812 follows an index to three pages and gets 38 complete records out of three fetches. The layout is not a compromise — it is optimal for the access pattern that wants whole records.

It is catastrophic for the other one. SELECT avg(price_cents) FROM market_prices needs 8 bytes per row and must read 160 to get them. The storage engine has no choice: the bytes it wants are scattered every 160 bytes across 6.72 GB, and disks and page caches do not deal in 8-byte fragments. The query reads all 6.72 GB to use 336 MB of it. Twenty times more data crosses the wire than the query has any use for.

Columnar storage inverts the unit. All 42 million price_cents values sit contiguously in one segment; all 42 million source values in another. Now the same aggregate reads exactly one segment: 42,000,000 × 8 bytes = 336 MB, and nothing else is touched. That is the 20× from layout alone.

Columnar layout buys a second win almost for free, and it comes from homogeneity. A column is one type with one distribution, so the encoder can exploit structure that a row-wise page — a jumble of integers, text, and timestamps interleaved — never offers.

Columnar compression in the wine-cellar feed is concrete. The source column holds four distinct values across all 42 million rows: livex, wine-searcher, auction-house, retail-panel. Dictionary encoding replaces every occurrence with a 2-bit code and stores the four strings once: a 1 GB column becomes about 11 MB. The observed_at column is sorted and regular, so delta encoding stores the first timestamp and then mostly the integer 1 (one day later) — run-length encoding collapses those runs to almost nothing. Even price_cents, genuinely varying, delta-encodes well because a wine's price moves in small increments from observation to observation.

Compression matters more than a storage bill, because in the modern warehouse it is also a compute bill. Most warehouse pricing is a function of bytes scanned or compute-seconds burned, and both fall when the encoded segment is smaller. Better still, some predicates never decompress at all: with a dictionary, WHERE source = 'livex' becomes a comparison against a 2-bit code, and segment-level min/max metadata lets the engine skip whole segments whose range excludes the filter. That skipping is the mechanism behind partition pruning, which module 8 shows is the single biggest lever on a warehouse invoice.

In the wine-cellar's Postgres, the 6.72 GB of market_prices lives on a disk attached to the instance running the query engine. You rent that instance by the hour whether it is answering queries or idle at 3 a.m., its disk must be large enough for all your data, and its CPU must be large enough for your worst query. Storage and compute are welded together and you buy the maximum of both, permanently.

Separation of storage and compute unwelds them. One copy of the data sits in object storage — durable, replicated, effectively unbounded, priced at pennies per gigabyte-month and charged whether or not anyone reads it. The query engines are stateless clusters that hold no data at all: a cluster starts, reads the segments it needs from object storage, computes, returns the answer, and can be destroyed. Its bill is compute-seconds, and if nothing is querying, that bill is zero.

Follow the consequences, because they explain the rest of the course. Retention becomes cheap. Keeping five years of raw event data costs storage, not compute, so the ELT instinct in module 3 — land everything raw, forever, transform later — goes from wasteful to obvious. Capacity stops being a plan. There is no "sizing the warehouse"; there is only how much compute you chose to run in a given hour. The bill's shape inverts. A classical database's cost is roughly fixed and its efficiency question is "will it fit?"; a warehouse's cost is roughly variable and its efficiency question is "how many bytes did that query scan?" — which is why module 8 is about queries and not about instances.

Once compute is stateless and data is shared, two properties appear that a single-server database cannot offer at any price.

Elasticity means compute is sized to the query rather than to the peak. The nightly rebuild of the wine-cellar marts gets a large cluster for eleven minutes; the executive dashboard gets a small one that runs for four seconds an hour; an analyst's exploratory session gets a medium one that auto-suspends after five idle minutes. None of these is a capacity decision anyone had to make in advance, and none of them costs anything overnight.

Isolation is the more interesting one. Multiple independent clusters read the same storage simultaneously. The wine-cellar deployment runs three: an ingestion and transformation cluster, a BI cluster serving dashboards, and an ad-hoc cluster for analysts and data scientists. When someone in the ad-hoc cluster writes an accidental cross join that runs for forty minutes and consumes an entire cluster's memory, the dashboards do not notice. There is no shared buffer cache to evict, no shared lock manager, no shared CPU — the only shared thing is immutable storage, and reading the same object from two places does not create contention.

This is precisely the property that module 1 showed a single database cannot have. There, the analytical scan and the transactional workload fought because they shared one engine's memory and I/O. Here, workloads are separated by construction rather than by scheduling.

A data lake came first and was a simpler idea: put files in object storage. Parquet, JSON, CSV, logs — cheap, schemaless until read, and able to hold anything. What it lacked was everything that makes a database a database. No transactions, so a reader could observe a half-finished write. No schema enforcement, so a producer could change a column's type on Tuesday and nobody learned until a query failed on Thursday. No efficient updates or deletes, which mattered enormously the day a privacy request required removing one user's rows from four years of files. The honest history is that many organizations built lakes, discovered they had built a swamp, and loaded the useful subset into a warehouse anyway.

An open table format is the fix, and it is a specification rather than a product: a metadata layout sitting over ordinary columnar files in object storage that tracks which files constitute a table at each version. That indirection is enough to deliver atomic commits, schema evolution, row-level updates and deletes, and time travel — read the table as of last Tuesday by reading the file list from last Tuesday. A lakehouse is that arrangement: warehouse semantics over lake storage, with the deliberate property that the table format is open, so multiple engines can read and write the same tables rather than one vendor's engine owning them.

The honest positioning, in two sentences: the lakehouse is convergence, not revolution — warehouses have been adding openness and lakes have been adding semantics until the categories now overlap substantially — and it matters when you have volume, variety, or engine-independence requirements that an integrated warehouse serves badly. Below that threshold it is an architecture you are maintaining instead of a product you are using.

Concretely, for the wine-cellar product: 4.1 TB, structured relational data plus one clickstream, one SQL engine, three analysts and two data engineers. The integrated warehouse is the correct choice, and adopting a lakehouse here would mean hand-assembling catalog, table format, engine, and governance to gain openness nobody has asked for. The threshold gets crossed by things like: petabyte volumes where storage economics dominate; genuinely heterogeneous data including images, audio, or model artifacts alongside tables; needing a SQL engine and a Spark job and an ML framework to read the same tables without copies; a hard requirement that the data not be locked inside one vendor's proprietary format. Those are real requirements and organizations really have them. Most organizations, at most stages, do not.

You now have the two foundations: analytical questions need a columnar layout, and the modern warehouse delivers it as separated storage and elastic compute. Everything remaining in this course is the machinery between the production database and the number on the screen. Here is that machinery in one picture — the map the rest of the course zooms into, one segment per module.

The simplest way to get data out of a source system is to ask for all of it on a schedule. A batch extract connects at 02:00, runs SELECT * FROM cellars, and writes the result to the warehouse's raw layer. It is a photograph of the source at a moment.

Its virtues are real and underrated. It is trivially debuggable — you can run it by hand and read its output. It has no persistent state to corrupt, so recovering from a failure means running it again. It works against anything that will answer a query, including vendor APIs and systems whose internals you cannot touch. For the wine-cellar's market price feed, which publishes a daily file of the previous day's observations, a nightly batch pull is not a compromise; it is exactly right, because the source itself only changes once a day.

Full extracts stop scaling, so teams move to incremental extracts: SELECT * FROM cellars WHERE updated_at > :last_run. This is where the silent failures live, and there are three of them.

Hard deletes vanish silently. If a row is deleted from cellars, there is no row left to have an updated_at greater than anything. The warehouse keeps its copy forever. The wine-cellar counts would drift upward month over month against production, and nobody would have an event to investigate — the divergence has no timestamp.

Untouched timestamps miss updates. updated_at is maintained by application code or a trigger, and every path that writes without maintaining it is invisible to your extract. A data-fix script run by an engineer at 3 a.m., a bulk backfill, an ORM path that writes one column directly — each produces a row whose contents changed and whose timestamp did not.

Intra-day transitions collapse. A batch sees states, not transitions. If a cellar was created at 09:00, renamed at 11:00, and soft-deleted at 16:00, the 02:00 photograph shows one row in its final state. Any question of the form "how long do cellars typically stay active before deletion?" is unanswerable, and you will not discover that until someone asks it a year later.

Every durable database already maintains a perfect, ordered record of everything that has ever happened to it: the write-ahead log. Postgres writes each change to the WAL before applying it to the heap, because that is how it survives a crash. Replication reads the same log. Change data capture is the observation that if the log is good enough to rebuild the database, it is good enough to feed the warehouse.

A CDC connector registers as a logical replication consumer and receives every insert, update, and delete as a structured event with the row's before and after images and a log position that totally orders them. Its properties are the mirror image of batch's: nothing escapes it, because escaping it would mean escaping the log the database itself depends on; deletes arrive as first-class events; and every intermediate state is preserved, so the transition questions become answerable. Latency drops from a nightly cycle to seconds or minutes.

CDC's costs are operational rather than conceptual. The replication slot the connector holds is a promise the database must keep: if the consumer stops, Postgres retains WAL segments for it, and an unmonitored stalled connector will fill the primary's disk and take production down — the one way an analytics component can cause an outage in the system it reads. Schema drift needs handling, because a column added to production arrives mid-stream. Ordering guarantees are per-table or per-partition rather than global, so cross-table consistency at a point in time is not free. And initial snapshots — the historical rows that predate the connector — are a separate, usually large operation.

Both modes so far extract from a database, which bounds them absolutely: they can only deliver what the database stores. The wine-cellar's Postgres knows that cellar 8812 contains 38 bottles. It does not know that its owner opened the valuation page nine times last week, sorted by appreciation twice, and never clicked through to the sell flow. That behavior was never written anywhere, because the application had no reason to write it.

An instrumentation event is a record emitted at the edge — browser, mobile client, or server handler — at the moment something happens, sent to a collector, and landed in the warehouse as its own stream. The wine-cellar emits three: cellar_viewed, bottle_added, and valuation_checked, each carrying a timestamp, an anonymous or authenticated actor id, the cellar id, and a small typed property bag.

The distinction from CDC is worth stating precisely, because teams routinely conflate them: CDC records what the database became; events record what happened. There is overlap — bottle_added corresponds to an insert CDC will also see — and that overlap is a useful reconciliation check, not a redundancy to eliminate. But the non-overlapping parts are the interesting ones. CDC will never deliver a page view, a search with no results, a form abandoned before submit, or a feature someone looked at and declined. Every funnel, engagement, and retention question lives in exactly that gap.

Events also have the opposite failure profile from CDC. They are schema'd at write time by application code, so a client that ships a bug, an ad blocker, or a user on a flaky connection produces gaps and duplicates that no log guarantees against — delivery is at-least-once at best and lossy in practice. Bot traffic pollutes them. They are, in short, a less reliable and more expressive source, which is why they need their own quality tests rather than the ones you would write for a relational extract.

For thirty years the acronym was ETL: extract, transform, load. Data was pulled from the source, cleaned and reshaped on an intermediate machine, and only the finished result was loaded into the warehouse. That order was not a philosophy. It was arithmetic. Warehouse storage was expensive and warehouse compute was a fixed, scarce appliance you had sized for the year, so loading anything you did not need was waste, and transforming inside the warehouse meant spending the scarcest resource you owned.

Module 2's architecture deletes both premises. Object storage costs pennies per gigabyte-month, so landing everything is cheap. Compute is elastic and billed by the second, so transformation in the warehouse is no longer a raid on a fixed budget. When the constraints invert, the optimal order inverts with them, and you get ELT: extract, load, then transform — land the source data essentially unmodified, and do all reshaping in SQL against it.

Three consequences make this more than a reordering.

The raw record survives. This is the big one. When a definition changes — and it will; module 6 is largely about that — you recompute history from raw. Under ETL, history was whatever the old transform produced, and the inputs are gone. Under ELT you re-run the model over five years of retained raw data and get a corrected series in an afternoon.

Transformation becomes SQL. Which means it becomes text, which means it goes in git, gets reviewed, gets tested, and gets rebuilt on every change — module 4's entire subject. ETL transformations lived in proprietary tools' drag-and-drop canvases where diffing two versions was not a thing you could do.

Loading decouples from modeling. The ingestion team's contract becomes "land the source faithfully," full stop. They do not need to know what a cellar means to the business, and the analytics team does not have to file a ticket to get a new column — it is already in raw.

Three layers, three rules. The discipline is worth more than any individual model, because it lets someone who has never seen your project predict where a given piece of logic lives.

Raw is immutable evidence. It is what the source said, when it said it, unmodified. Nothing writes to it but ingestion, and nothing edits it ever. Its contract to the layers above is fidelity, not quality: raw may contain duplicates, nulls, wrong types, and rows the business considers invalid, and all of that is correct behavior. The raw layer is the only artifact in the stack that can prove what a source actually sent.

A staging model is one model per source table, and its rule is: rename, cast, deduplicate, and nothing else. No joins across sources. No business filters. No aggregation. stg_cellars turns raw_pg.cellars into one row per cellar with real types and clean names, resolving the CDC change stream to current state. It does not decide whether a soft-deleted cellar is "active," because that is a business question and this layer does not answer those. The contract staging owes upward is one clean row per entity, with the source's meaning intact.

A data mart joins and aggregates staged models for consumption. This is where business logic lives, exclusively: filters that encode a definition, derived measures, the star schemas of module 5. The contract a mart owes is a table shaped for a class of questions, whose logic is written down in one place.

Once transformation is SQL against landed data, it is text — and everything Guide Nº 18 established about managing text applies without modification. The practice that made this mainstream is dbt, and having named it once, this module will teach the practice generically, because the ideas outlive any tool.

A model is one file containing one SELECT. It does not say CREATE TABLE, does not say INSERT, does not manage its own dependencies. It declares what it is; the framework decides how it exists.

Dependencies are inferred, not declared. When fct_price_observations references stg_bottles through a reference function rather than a hard-coded table name, the framework knows the edge and assembles the whole project into a directed acyclic graph. Nobody maintains a build order by hand, and nobody can create a cycle by accident — which matters more than it sounds, because hand-maintained ordering is the single most common source of "the numbers are wrong on Mondays."

Materialization is declared per model, not implemented in it. The same SELECT can be a view (cheap, always current, recomputed per query), a table (expensive to build, fast to read), or incremental (only new partitions processed). Changing that decision is a one-line configuration change, so the performance-versus-freshness trade-off becomes tunable rather than a rewrite.

A data test is an assertion about a model that runs on every build. Four kinds carry most of the weight, and each catches a specific failure.

Not-null on a key column catches an upstream schema or join change that started producing orphan rows. Unique on the entity key catches a broken deduplication or a CDC replay that landed a row twice — the failure that silently doubles a count. Accepted values on a low-cardinality column catches the day the price vendor adds a fifth source label your CASE statement does not handle and quietly buckets into an else. Relationship tests catch referential breakage: a bottle whose wine_id has no matching wine, which is exactly the row that vanishes in an inner join and takes its value out of a total with it.

Beyond those, the tests that earn their keep are business-rule tests — assertions that encode a domain invariant a schema cannot express. For the wine-cellar: no bottle has consumed_at earlier than acquired_at; no price observation is negative or above a sanity ceiling; every unconsumed bottle in fct_price_observations has at least one price observation in the trailing 30 days. That last one is the test that catches a dead vendor feed before a dashboard does.

Here is the actual staging layer for the wine-cellar, against the raw tables module 3 landed. These four models are the inputs module 5 builds the star schema from, so the SQL that follows is not illustrative — it is the project.

The CDC-sourced models share one shape, and it is worth reading carefully: raw holds one row per change, so staging must resolve the change stream to current state. Ordering by the log sequence number rather than by a timestamp is deliberate, since two changes within the same millisecond are ordered by LSN and not by clock.

-- models/staging/stg_cellars.sql
-- Contract: one row per cellar, current state, source meaning intact.
-- Note: deleted_at is passed through, NOT filtered. Whether a
-- soft-deleted cellar counts is a business question (see marts).

with ranked as (
    select
        *,
        row_number() over (
            partition by id order by _cdc_lsn desc
        ) as rn
    from raw_pg.cellars
)

select
    id                            as cellar_id,
    owner_id                      as owner_id,
    name                          as cellar_name,
    created_at::timestamptz       as created_at,
    deleted_at::timestamptz       as deleted_at,
    _loaded_at                    as _loaded_at
from ranked
where rn = 1
  and _cdc_op <> 'D'    -- hard-deleted rows drop out here; the
                        -- tombstone stays in raw as evidence

-- models/staging/stg_bottles.sql
-- Contract: one row per bottle, current state. Monetary values in
-- integer cents throughout the project — no floats touch money.

with ranked as (
    select
        *,
        row_number() over (
            partition by id order by _cdc_lsn desc
        ) as rn
    from raw_pg.bottles
)

select
    id                            as bottle_id,
    cellar_id                     as cellar_id,
    wine_id                       as wine_id,
    acquired_at::date             as acquired_date,
    acquisition_price_cents::bigint as acquisition_price_cents,
    consumed_at::date             as consumed_date
from ranked
where rn = 1
  and _cdc_op <> 'D'

-- models/staging/stg_wines.sql
-- Contract: one row per wine. region_id is passed through un-joined;
-- resolving it to a region name is a mart concern (see dim_wine).

with ranked as (
    select
        *,
        row_number() over (
            partition by id order by _cdc_lsn desc
        ) as rn
    from raw_pg.wines
)

select
    id            as wine_id,
    producer      as producer,
    label         as label,
    vintage::int  as vintage,
    region_id     as region_id
from ranked
where rn = 1
  and _cdc_op <> 'D'

The price feed is batch rather than CDC, so its staging model has a different job: the vendor occasionally re-publishes a day's file, which lands the same observation twice with different load timestamps. Deduplication here is on the natural key, keeping the most recently loaded copy — a correction, if one arrives, wins.

-- models/staging/stg_market_prices.sql
-- Contract: one row per wine per source per observation date.
-- Vendor re-publishes occasionally; last load wins.

with ranked as (
    select
        *,
        row_number() over (
            partition by wine_id, source, observed_at::date
            order by _loaded_at desc
        ) as rn
    from raw_api.market_prices
)

select
    wine_id                as wine_id,
    source                 as price_source,
    observed_at::date      as observed_date,
    price_cents::bigint    as price_cents
from ranked
where rn = 1
  and price_cents > 0

Note the one filter in that last model, price_cents > 0, and why it does not violate the staging rule. It is not a business definition; it discards observations the vendor's own contract says are invalid sentinel values. The test is whether a reasonable person could disagree about it: nobody argues that a price of zero cents is a real market observation, whereas people argue constantly about whether a soft-deleted cellar is active. The first is cleaning; the second is a definition, and definitions go upstairs.

The tests, declared alongside the models:

# models/staging/schema.yml
models:
  - name: stg_cellars
    columns:
      - name: cellar_id
        tests: [not_null, unique]        # catches CDC replay double-load
      - name: owner_id
        tests: [not_null]                # catches an orphaned cellar
  - name: stg_bottles
    columns:
      - name: bottle_id
        tests: [not_null, unique]
      - name: wine_id
        tests:
          - not_null
          - relationships:               # catches a bottle whose wine
              to: ref('stg_wines')       # vanished — the row that would
              field: wine_id             # silently drop from a join
  - name: stg_market_prices
    columns:
      - name: price_source
        tests:
          - accepted_values:             # catches the vendor adding a
              values: ['livex',          # fifth source label
                       'wine-searcher',
                       'auction-house',
                       'retail-panel']
    tests:
      - dbt_utils.expression_is_true:
          expression: "price_cents between 1 and 20000000"

Dimensional modeling divides everything into two kinds of table, and the division is unusually clean.

A fact table holds measurements: numbers that occurred, each row tied to a moment and to the entities it concerns. It is tall and narrow — hundreds of millions of rows, a dozen columns, mostly foreign keys and numeric measures. In the wine-cellar it will hold what each bottle was worth on each valuation date.

A dimension holds the nouns you slice by: wine, cellar, region, date. Dimensions are short and wide — tens of thousands of rows, many descriptive columns — and they carry every attribute you might want to filter or group by. dim_wine holds the producer, the label, the vintage, and — deliberately — the region name, copied from the region table.

A star schema is one fact table joined directly to its dimensions, one hop each, with no dimension joining to another. Drawn out, it looks like a star, which is where the name comes from; the more important property is that any question is answerable with joins from the center outward and never a chain.

That last property is the whole point, and it is worth seeing arithmetically. In the operational schema, "average market price by region" requires market_prices → wines → regions: three tables, two joins, and the join to wines is 42 million rows against 61,000. In the star, region_name sits on dim_wine already, so it is one join from a fact to a small dimension. Add cellar owner and vintage decade to the question and the operational version grows another two joins while the star version grows none.

Before a single column is chosen, one sentence must be written: what does one row of this fact table represent? That is the grain, and it is the most consequential decision in analytical modeling — more than the column list, more than the technology, more than the schema layout — because every measure, every join, and every aggregate is either valid at the grain or silently wrong.

The wine-cellar's fact table declares its grain as: one row per unconsumed bottle per valuation date. Read what that sentence forecloses. A bottle consumed on 3 March has rows through 2 March and none after — so "portfolio value" naturally excludes drunk wine without any filter needing to remember to. A bottle appears once per date, so counting rows for one date counts bottles. And because the grain is per-bottle rather than per-wine, a cellar holding six bottles of the same Barolo contributes six rows, which is what makes summing values correct.

The test for whether a measure belongs at a grain is additivity: does summing it across any slice produce a true number? market_price_cents at bottle-day grain is additive — sum across a cellar and you get that cellar's value; across a region and you get the region's. unrealized_gain_cents is additive for the same reason. An appreciation percentage stored per row is not additive, and no aggregate over it is meaningful; averaging stored percentages weights a $40 bottle equally with a $4,000 one. Percentages are computed at query time from summed components, never stored as measures and never averaged.

Now the derivation. On the left is Guide Nº 06's normalized schema, arrived at by asking "how do I store this safely?" On the right is the star, arrived at by asking "how do I answer questions about this?" Every difference between them is a deliberate trade.

The fact table itself, built from module 4's staging models:

-- models/marts/fct_price_observations.sql
--
-- GRAIN: one row per unconsumed bottle per valuation date.
--
-- A bottle appears from its acquisition date until the day before it
-- is consumed. Measures are all additive at this grain; ratios are
-- NOT stored — compute them from summed components at query time.

with valuation_dates as (
    select date_day as valuation_date
    from dim_date
    where date_day between date '2021-07-12' and current_date
      and is_week_start                -- weekly snapshot cadence
),

latest_price as (
    select
        wine_id,
        observed_date,
        price_cents,
        row_number() over (
            partition by wine_id, observed_date
            order by case price_source
                       when 'livex'         then 1
                       when 'auction-house' then 2
                       when 'wine-searcher' then 3
                       else 4
                     end
        ) as source_rank
    from stg_market_prices
)

select
    b.bottle_id,
    b.wine_id,
    b.cellar_id,
    v.valuation_date,
    p.price_cents               as market_price_cents,
    b.acquisition_price_cents,
    p.price_cents - b.acquisition_price_cents
                                as unrealized_gain_cents
from stg_bottles b
cross join valuation_dates v
left join latest_price p
       on p.wine_id       = b.wine_id
      and p.observed_date = v.valuation_date
      and p.source_rank   = 1
where b.acquired_date <= v.valuation_date
  and (b.consumed_date is null or b.consumed_date > v.valuation_date)

Three details in that model are load-bearing. The left join to prices is deliberate: a bottle with no observation on a given date should still exist in the fact with a null market price, because dropping it would silently shrink the portfolio — an inner join here is the join-path bug module 6 diagnoses. The consumed-date predicate implements the grain sentence directly, so "unconsumed" is enforced by the model rather than remembered by every consumer. And no percentage is stored, only cents, because percentages are not additive.

The dimensions are straightforward, with one deliberate denormalization:

-- models/marts/dim_wine.sql
-- One row per wine. region_name is copied in from stg_regions so the
-- most common analytical question needs one join, not two. Safe
-- because this table is rebuilt from source on every run.

select
    w.wine_id,
    w.producer,
    w.label,
    w.vintage,
    (w.vintage / 10) * 10   as vintage_decade,
    w.region_id,
    r.region_name,          -- denormalized on purpose
    r.country
from stg_wines w
left join stg_regions r on r.region_id = w.region_id

Here is the payoff. The question the course opened with — which regions appreciate fastest? — against the star:

select
    w.region_name,
    count(*)                                as bottles,
    sum(f.acquisition_price_cents) / 100.0  as acquisition_value,
    sum(f.market_price_cents)      / 100.0  as market_value,
    round(
        100.0 * sum(f.market_price_cents)
              / nullif(sum(f.acquisition_price_cents), 0) - 100.0
    , 1)                                    as appreciation_pct
from fct_price_observations f
join dim_wine w on w.wine_id = f.wine_id
where f.valuation_date = date '2026-07-12'
  and f.market_price_cents is not null
group by w.region_name
order by appreciation_pct desc
limit 5;

One join. One GROUP BY. The result:

Compare that to the operational path. On Postgres, the same question requires joining bottles → wines → regions and correlating each bottle to the most recent price per wine as of a date, which is a window function over 42 million rows plus a four-table join, and then remembering — every single time, in every analyst's hand-written version — to exclude consumed bottles and soft-deleted cellars. The star answers it in one join because module 4's staging models did the cleaning and this module's fact table did the shaping, once, in a file with a name.

"Data quality issue" is not a diagnosis. It is what people say when they have not looked. Two numbers computed from the same warehouse differ for exactly three mechanical reasons, and each one leaves a distinctive fingerprint in the size and shape of the gap.

Different filters. One query applies a predicate the other does not. This is the most common cause by a wide margin and produces a gap that is stable in absolute terms: the difference is a fixed set of rows. In the wine-cellar, one path counts all cellars with recent activity and the other adds deleted_at is null. The gap is 500 cellars — 12,400 against 11,900 — and it stays 500 whether you slice by month, by region, or by owner cohort, because it is a population difference rather than a computation difference. Fingerprint: a constant, unexplained offset.

Different grains. One query counts rows at one grain, another at a different one. The gap here is multiplicative rather than additive. A dashboard reporting "wines held" that counts bottles returns 434,000; one that counts distinct wines returns 61,000. The ratio, 7.1, is the average number of bottles per wine — a meaningful number that looks nothing like an error. Fingerprint: a ratio that is stable across slices and happens to equal an average multiplicity.

Different join paths. Same tables, same filters, different route between them — most often an inner join where the other used a left join. Total portfolio value computed through fct → dim_wine → dim_region with inner joins is $182.9M; through the star's intended single hop with a left join it is $186.2M. The missing $3.3M is the value of bottles whose wines — 1,840 of them, imported from a vendor catalogue three years ago and never backfilled — have a null region_id. The inner join dropped them silently. Fingerprint: a gap that appears only in some slices — specifically the ones containing the orphaned rows — and vanishes in others.

Learn the fingerprints and the first hour of an investigation collapses into a few minutes. A constant 500 is not a rounding problem, not a refresh timing problem, and not a "data quality issue" — it is a filter, and you can go find it.

Take the real investigation. The gap is 500, constant, and identical in every slice the analyst tries — so by the fingerprints above it is a filter, and the work is now to find which one and where. The technique is a lineage walk: start at each number and move upstream, one artifact at a time, until the paths converge; the divergence is at the last node they do not share.

The two queries, side by side. The diff is one line:

-- CEO dashboard tile (saved inside the BI tool)
select count(distinct c.cellar_id) as active_cellars
from stg_cellars c
join stg_app_events e on e.cellar_id = c.cellar_id
where e.event_at >= current_date - interval '90 days';
-- → 12,400

-- Finance snippet (pasted into the board-deck spreadsheet)
select count(distinct c.cellar_id) as active_cellars
from stg_cellars c
join stg_app_events e on e.cellar_id = c.cellar_id
where e.event_at >= current_date - interval '90 days'
  and c.deleted_at is null;                   -- ← the entire dispute
-- → 11,900

Five hundred cellars were soft-deleted by their owners and continued generating events for ninety days afterward — which sounds impossible until you look: the deletion sets deleted_at and hides the cellar in the owner's list, but shared read-only links keep working, and each visit emits cellar_viewed. The two numbers are precise answers to two different questions, and the finance number is the one a board should see.

The tempting resolution is to declare the finance number correct and correct the dashboard. Do that and you have fixed one instance and preserved the machine: the next metric, the next analyst, the next quarter, and the divergence recurs with different numbers. The structural fix is a metrics layer — a place where a metric is defined once, in version control, and every consumer obtains the number by requesting the metric rather than by writing SQL that computes it.

A definition is complete when it specifies five things: the measure (what is counted or summed), the grain (at what level of detail), the filters (which population), the join path (which route through the models), and the owner (who decides when it changes). Missing any one of them leaves room for two implementations to differ.

# metrics/active_cellars.yml
metric: active_cellars
label: Active cellars
owner: growth-analytics            # who approves changes
description: >
  Cellars that exist (not soft-deleted) and show owner activity in the
  trailing 90 days. Counts the customer relationship, not the account
  record — a cellar the owner deleted is not active even if shared
  read-only links keep generating view events.

measure:    count_distinct(cellar_id)
grain:      one row per cellar
model:      mart_cellar_activity
filters:
  - deleted_at is null                       # excludes 500 soft-deleted
  - last_event_at >= current_date - 90       # trailing-window activity
join_path:  stg_cellars → stg_app_events (left join on cellar_id)

caveats:
  - Event delivery is at-least-once and lossy (ad blockers, offline
    sessions), so this is a floor, not a census. Do not reconcile it
    to the operational cellar count.
  - Trailing 90 days is measured against the run date, so historical
    values are not restatable from a snapshot alone.

dimensions: [region, owner_cohort, created_month, plan_tier]

Two properties of that file do the work. First, it is executable: the BI tool, the notebook, the board-deck query, and the AI assistant all resolve active_cellars through it and receive generated SQL, so no consumer is in a position to compute it differently. Second, it is reviewable: it lives in git, changes arrive as pull requests, and the question "who changed what active means, and when?" is answered by git log rather than by asking around.

Note that the caveats section is not decoration. It records that the metric depends on a lossy source and must not be reconciled against the operational count — a fact that would otherwise be rediscovered, at cost, by every new analyst who notices the numbers do not match and opens an investigation.

A definition file is not self-maintaining. Metric governance is the small set of practices that keep it true: an owner, a change process, and a deprecation path.

Ownership means a named team, not "data." The owner of active_cellars is the team that will be asked to defend the number in a board meeting, which is what makes them the right approver for a change to it.

Change review means a definition change is a pull request with an impact statement: which dashboards move, by how much, and whether history is restated or the series breaks at a date. A definition change that silently shifts a reported series is worse than a wrong number, because the wrong number is at least stable enough to notice.

Deprecation means metrics are retired explicitly. When active_cellars is superseded by engaged_cellars, the old definition remains, marked deprecated, resolving to its old value and emitting a warning to consumers — because a metric that disappears takes six dashboards with it and gets reimplemented by hand within a week, which is how you acquire an eighth copy of a definition you were trying to consolidate.

Module 4 established what a test is. The operational question is where to put them, and the answer follows one rule: a test belongs at the boundary where the failure it catches first becomes detectable. Testing earlier is impossible; testing later means the bad data has already propagated into models that will need rebuilding.

That yields two families with genuinely different jobs.

Ingestion boundary tests assert that the source behaved. Volume: the price feed landed 23,000 ± 15% rows, so a nightly file with 900 rows fails loudly instead of quietly halving a series. Schema: the columns and types the contract promised are present, so a vendor's silent addition of a column is a notification rather than a discovery. Distribution: the share of rows carrying each price_source value is within a few points of its trailing average, which catches the vendor switching a supplier without telling anyone. These tests do not know what a cellar is. They know what the pipe should look like.

Mart contract tests assert that the business rules hold. Grain uniqueness on (bottle_id, valuation_date). No bottle consumed before it was acquired. Portfolio value within a plausible band of the prior run. Reconciliation of the cellar count against the operational system to within 0.5%. These tests require domain knowledge and are the ones that catch a wrong meaning rather than a wrong shape.

Every other failure mode in this course announces itself somehow. A broken model fails a build. A wrong join produces a number someone eventually questions. Staleness announces nothing, because stale data renders identically to fresh data. The chart draws, the total displays, the sparkline slopes upward — and the entire picture is six days old.

What makes this more than an annoyance is that stale data is often plausible. A portfolio value that has not updated since Tuesday still looks like a portfolio value; it is in the right range, moves in the right direction historically, and passes every intuition an executive can apply in the eight seconds they look at it. The failure is only detectable by comparison against a clock, and no dashboard shows a clock unless someone built one.

Freshness is therefore a per-consumer SLA, not a global property, because different consumers genuinely need different things and pretending otherwise produces either wasted engineering or false confidence:

Lineage is the provenance graph of the whole stack: which source columns feed which staging models feed which marts feed which metrics feed which dashboards. It is generated from the model DAG rather than maintained by hand, which is the property that makes it trustworthy — a hand-drawn lineage diagram is a description, and module 6 established what happens to descriptions.

Lineage answers two questions, and they run in opposite directions.

Forward, before a change: blast radius. An application engineer proposes dropping cellars.name from production Postgres. Without lineage, the honest answer is "probably fine" and the actual answer arrives as an incident. With lineage, the query takes seconds and returns: raw_pg.cellars.name → stg_cellars.cellar_name → dim_cellar.cellar_name → mart_cellar_portfolio → 4 dashboards, 1 metric dimension, and the support tool's customer view. Now the conversation is a schedule rather than a surprise, and the engineer gets a real answer instead of a shrug.

Backward, after a number goes wrong: root cause. This is the walk you already took in Figure 6.1. Starting from the disagreeing numbers and moving upstream until the paths converge is a lineage traversal, and it is fast in proportion to how much of the graph is machine-generated. In a stack where three of the nodes are saved queries inside a BI tool and one is a spreadsheet, the walk stalls at exactly those nodes — which is the practical argument for the metrics layer restated as an operational one.

Column-level lineage is worth the extra cost over table-level. Table-level tells you dim_cellar is affected; column-level tells you only consumers of cellar_name are affected and the eleven dashboards using cellar_id are not. That difference is what turns a change from a broad freeze into a targeted one.

The pipeline is a directed acyclic graph whose edges are the reference relationships module 4 established. Orchestration executes that graph on a schedule, and the single most important thing it does is not scheduling — it is failure semantics.

When stg_market_prices fails, its descendants must not run. Not run-and-warn, not run-with-yesterday's-data: not run. This feels harsh the first time a dashboard goes blank at 8 a.m., and it is the correct behavior, because the alternative is serving a portfolio value computed from a partial price load — a number that is wrong, plausible, and indistinguishable from a right one. A blank tile prompts a question; a wrong number prompts a decision.

A backfill is recomputing history: the seven days of prices that never landed, or five years of a metric whose definition just changed. Backfills are ordinary and frequent, and they are where the pipeline's most expensive bug lives.

Consider a model materialized incrementally as insert into fct_price_observations select … where valuation_date = :run_date. It works perfectly every night. Run it twice for the same date — because a job was retried, because an operator re-ran a failed DAG, because a backfill overlapped a scheduled run — and that date now has every row twice. Portfolio value for that day doubles. No error, no constraint violation, no test failure unless someone wrote the grain uniqueness assertion.

-- NOT idempotent: second run doubles the partition
insert into fct_price_observations
select ... where valuation_date = :run_date;

-- Idempotent: N runs converge to the same state
delete from fct_price_observations
 where valuation_date = :run_date;
insert into fct_price_observations
select ... where valuation_date = :run_date;
-- (wrapped in one transaction, so a crash between the two
--  statements cannot leave the partition empty)

BI tools consume marts and metrics and render them. The good ones are genuinely valuable and mostly interchangeable; the interesting question is not which tool but what has to be true before any of them helps.

Self-serve analytics — anyone in the company answering their own questions without filing a ticket — is a real and achievable outcome. It works exactly when the semantic work is already done: governed marts with declared grain, defined metrics that resolve one way, and dimensions that mean what their names say. Under those conditions a marketing manager slicing active_cellars by acquisition channel gets a correct number, because the definition is executed for them and the only thing they contributed was the question.

Absent those conditions, self-serve does not fail quietly. It manufactures disagreement at scale. Give forty people SQL access to raw and staging tables and you have not distributed insight — you have created forty opportunities for module 6's divergence, each one authored in good faith, each one defensible on its own terms, and now arriving in meetings simultaneously.

Dashboards are the most visible consumer and not the most demanding one. Two others are worth naming, because both assume this course's machinery was built correctly and neither can verify it.

Experimentation (Guide Nº 10) consumes assignment data and outcome data at declared grains. Every statistical method in that guide — power calculations, variance estimation, sequential testing — assumes its inputs are correct in ways it cannot check: that one row per user per experiment means exactly that, that the outcome metric means the same thing in treatment and control, that late-arriving events are handled identically on both arms. A grain violation in the assignment table does not make an experiment noisy; it makes it wrong in a direction nobody will notice, because the statistics remain internally consistent and simply describe a population that does not exist. The uncomfortable relationship is that the more sophisticated the method, the more thoroughly it launders a bad input into a confident conclusion.

AI features consume governed metrics and modeled tables. Point a natural-language assistant at raw tables and you have rebuilt module 6's disagreement machine with two aggravating properties: the assistant will infer a definition — it has to, since the question requires one and the tables do not supply one — and it will state the result in fluent, unhedged prose. A human analyst who guesses at a definition usually signals the guess; a language model's output carries no such tell, and the reader has no artifact to inspect. This makes governance matter more for AI consumers, not less: an assistant that resolves active_cellars through the metrics layer returns 11,900 and can cite the definition; one composing SQL against staging tables returns whichever of 12,400, 11,900, or 10,700 its inference happened to reach, with equal confidence in all three cases.

Warehouse pricing comes in two common shapes: bytes scanned — you pay per byte the query reads — or compute-seconds — you pay for cluster size × time. They differ in detail and agree on the thing that matters: cost tracks data volume touched, not result size. A query returning twelve rows can cost ninety dollars.

That single decoupling is the origin of nearly every surprise invoice, because every feedback signal a developer receives is about latency. The query returns in nine seconds; nine seconds feels fine; nothing in the interface mentions that it scanned 18.4 TB. The invoice arrives thirty days later as an aggregate with no attribution. Contrast that with the operational database of Guide Nº 06, where an expensive query announces itself immediately by making the application slow — the warehouse removed that feedback loop along with the contention.

Work the wine-cellar's example. The question is "weekly active cellars by region, last quarter," refreshed hourly, at $5 per terabyte scanned.

Three levers, in order of leverage. Partition pruning — physically organizing a table by date and filtering on that column so the engine skips segments entirely — is the largest and the least effort. Column selection is free and forgotten: select * in a warehouse is not a convenience, it is a bill. Pre-aggregation converts a per-query cost into a fixed nightly one, which is the right trade whenever a query runs more often than its inputs change; the hourly refresh here reads data that updates once a day, so 23 of every 24 runs recompute an unchanged answer.

And the two things that feel like fixes and are not. A bigger cluster buys latency, not money — module 2's arithmetic. Adding LIMIT 100 changes nothing at all: the scan happens first and the limit discards rows afterward, so the bytes are already paid for. That second one catches almost everyone once.

Nine anti-patterns, consolidated. Each is named, given the symptom you will actually observe, the underlying diagnosis, and the corrective — with the module that teaches it.

Read the symptom column on its own and notice the shared property: not one of these announces itself as a data problem. They appear as a slow checkout page, a slow team, an argument in a meeting, a surprising invoice, a customer email. That is the practical reason the supply-chain frame is worth carrying around — the failures surface at the consumption end and originate somewhere upstream, and knowing the chain is what lets you walk from one to the other.