The Fundamental Data Journey
Application to PostgreSQL to extract to lake to transformation to warehouse to mart to dashboard — and what each arrow is actually promising.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Why does a number have to pass through six systems to reach a dashboard, and what does each of those hops buy?
An analyst who wants to answer "revenue by country, last twelve months" without waiting minutes, without taking production down, and without discovering that the answer changes between Tuesday and Wednesday.
The grain changes at almost every hop, and tracking that change is the point of this lesson. One order row becomes one CDC change record, becomes one event, becomes one line in a file, becomes one fact row, becomes one cell in an aggregate. Confusing any two of those produces a wrong number that reconciles against the wrong thing.
Treat the journey as a plumbing detail — "the data lands in the warehouse somehow" — and reason only about the SQL at the end. The transformation is where the business logic lives, so that is where the attention goes.
The SQL is correct and the answer is wrong, because the extract that fed it used WHERE updated_at > :last_run against a source whose updated_at is assigned before commit, so rows that committed late were skipped forever (Incremental Extraction).
- The SQL is correct and the answer is wrong, because the extract that fed it used
WHERE updated_at > :last_runagainst a source whoseupdated_atis assigned before commit, so rows that committed late were skipped forever (Incremental Extraction). - The analyst compares this month to last month and the trend is fake, because the ingestion method changed in between and the two periods have different completeness.
- A single order appears twice in the fact table. The transformation is blameless — the event log delivered it twice, which it is entitled to do (At-Least-Once Delivery).
- The warehouse table is queried while a backfill is halfway through overwriting it, and a scheduled report captures a partially-written state that never existed as a consistent whole (Atomic Publish).
- Nobody can say which system is authoritative for
customer_country, because it exists in the CRM, in the orders table and in the dimension, and all three disagree (Source of Truth).
What is actually happening
- Each hop exists because the previous system is bad at what the next one needs. Postgres is excellent at serving one order and poor at scanning two years of them. Object storage is excellent at holding two years cheaply and poor at answering a query. A warehouse is excellent at the query and expensive as a landing zone for unfiltered raw bytes.
- The arrows between them are where guarantees are made and lost. An arrow is not a pipe; it is a contract with a delivery semantic, an ordering property, a schema and a failure mode (Data Contracts).
- The grain transformation at each hop is the part most often skipped. A CDC record is not an order — it is a *change to* an order, and three of them may describe one order's life. Collapsing them into "the order" is a deliberate modelling decision with a right and a wrong answer (What a CDC Event Contains).
- Layers exist for reprocessing, not for tidiness. Raw exists so a transformation bug is recoverable. Staging exists so cleaning is separated from modelling and can be re-run alone. Curated exists so consumers depend on a stable contract rather than on whatever shape the source happened to have (Raw, Staging, Curated: Layers by Purpose).
- The mart at the end is a serving decision, not a modelling one: a narrower, pre-aggregated, purpose-built copy that trades flexibility and freshness for query cost and simplicity (Data Marts).
The chain, and where the grain changes
The canonical journey is short to write and easy to underestimate. What makes it worth studying is not the boxes but the transitions: at four separate points, what "one row" means changes, and every one of those points is a place where a metric can silently become wrong.
Track the word "one" through the table below. If you cannot state what one row of a dataset represents, you cannot write a correct aggregate against it — and a COUNT(*) against a dataset whose grain you have misremembered will return a confident, precise, wrong number.
| Stage | One row is | Breaks if |
|---|---|---|
| PostgreSQL `orders` | One order, in its current state. | You assume it holds history. It holds the latest value of every column and has forgotten the rest. |
| CDC stream | One *change* to one order — an insert, update or delete. | You count rows and call it orders. Three updates to one order are three records and one order. |
| Raw files in the lake | One delivered change record, possibly delivered more than once. | You treat the file as a deduplicated set. At-least-once delivery means duplicates are normal, not exceptional. |
| Staging model | One order, reconstructed as the latest change per order id. | The "latest" is chosen by arrival order rather than by the source's commit order, so an out-of-order update wins. |
| `fct_orders` | One order at a declared grain — or one order *line*, which is a different table. | Order-level and line-level facts are joined without care, multiplying every order-level measure by its line count. |
| Revenue mart | One country-day, with revenue pre-aggregated. | Someone joins it back to an order-level table, re-aggregates, and double counts. |
| Dashboard tile | One number, with the grain now entirely invisible. | The BI tool applies a filter or a join the model never anticipated, changing the metric after every upstream check has passed. |
Four grain changes in eight hops. Each is legitimate; each is a place where a wrong assumption produces an answer that looks completely normal.
Application ↓ PostgreSQL ↓ CDC / Batch Extract ↓ Data Lake ↓ Transformation ↓ Warehouse ↓ Data Mart ↓ Dashboard
Reading the journey backwards during an incident
Nobody debugs a data platform forwards. The report is always "this number looks wrong", and the only productive direction is upstream: which model produced it, what feeds that model, what fed that.
This is why lineage is not documentation but a debugging tool, and why the arrows carry as much information as the boxes. At each hop the question is the same and the answer localises the fault: does this dataset contain what it should for the affected period?
The walk terminates in one of three places. The source is wrong — a genuine business change or an application bug, and not your incident. A hop lost or duplicated data — an ingestion or delivery problem. Or a transformation is wrong — the code did what it was told and what it was told does not match the definition anyone believes.
- Executive dashboard tile
holds One aggregated number and a filter set defined in the BI tool.
could corrupt A filter or join added in the BI layer that no model or test can see.
↑ reads from - `revenue_daily` mart
holds One row per country-day with revenue pre-summed.
could corrupt An aggregation at the wrong grain, or a stale refresh that lags the model it derives from.
↑ reads from - `fct_orders`
holds One row per order with measures and dimension keys.
could corrupt A fan-out join against a dimension with duplicate keys; a filter that drops a status category.
↑ reads from - `stg_orders`
holds One row per order, reconstructed from change records.
could corrupt Choosing the latest change by arrival rather than commit order; failing to handle deletes.
↑ reads from - Raw change files
holds Every delivered change record, exactly as received.
could corrupt Duplicates from redelivery; a gap where the connector was down and retention expired.
↑ reads from - CDC connector
holds A position in the source database's log.
could corrupt Falling behind retention; restarting from a snapshot and re-emitting history; missing DDL changes.
↑ reads from - PostgreSQL `orders`
holds The authoritative current state of every order.
could corrupt Nothing, from this domain's point of view — if it is wrong here, it is an application incident.
Ask one question at each node: is the affected period complete and correct *here*? The first "no" walking upstream is where the incident lives.
Why not just query the production database
This is the right first question and it deserves a real answer rather than a reflex. For a single product with one database, modest data and a few analysts, querying a read replica is simpler, fresher and cheaper than any pipeline, and building a platform instead is a mistake that will cost a year.
The reasons to move are specific, and you should be able to name which one applies. Isolation: analytical scans are starting to affect production. History: questions now require what was true in the past, and the operational schema overwrites it. Multiple sources: the answer requires joining data that does not live in that database. Shape: the queries scan far more than they filter, and no index helps because there is nothing selective to index.
If none of those applies, the honest recommendation is a replica, a scheduled query and a saved report. That answer will not appear in a vendor's architecture diagram, which is a reason to trust it rather than a reason to doubt it.
Usually the largest and the most controllable — decided by layout and by whether people select the columns they need.
A nightly full rebuild costs proportional to all history rather than to the day that changed.
Driven by joins and wide aggregations; grows with skew because one task moves far more than its share.
Cheap per byte and permanent — it is the driver that accumulates silently because nothing ever deletes.
Almost always the smallest line and almost always the first one people try to optimise.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical warehouse-centric platform, shown to establish an ordering — not measurements, and not transferable to a specific bill. The ordering is the teaching: layout and reprocessing dominate, and the thing that looks expensive on the invoice is rarely the thing to fix.
What is the actual constraint that a read replica is failing to satisfy?
when One source, queries return acceptably, no historical questions, production unaffected.
cost None. Revisit when one of the rows below becomes true — and keep the raw event history in the meantime, because that is the part you cannot recreate later.
when Analytical queries are competing with the application for the same resources, or the replica lags under analytical load.
cost A second system to operate, and a copy that can diverge. Buys availability independence.
when Questions require prior states the operational schema overwrites — a customer's tier at the time of an order.
cost Modelling work (slowly changing dimensions) and storage that grows without bound. Buys answers that are otherwise impossible.
when The answer joins the product database with a payment provider, a CRM and an event stream.
cost Ingestion per source, each with its own schema drift and failure behaviour. Buys questions nobody could previously ask.
when Queries scan most of a large table and aggregate; indexes do not help because the predicate is not selective.
cost Columnar storage and a different engine — the largest architectural jump of the four. Buys scans that are feasible rather than merely slow.
How to build it
Most important first.
- Draw the journey for your own system before choosing tools, and write the grain and the guarantee on every arrow. Most architecture arguments dissolve once both are written down.
- Name one authoritative source per business concept, and make everything else explicitly a copy (Source of Truth).
- Land raw exactly as received, including the fields you do not currently use. Storage is the cheapest thing in the chain and the field you discard today is the one a question next quarter needs (The Raw Landing Zone).
- Put the boundary where the schema stabilises: consumers should depend on curated models, never on raw or on the source's table shape (Model Layering).
- Make the final publish atomic, so no consumer can observe a partially-built dataset (Atomic Publish).
- Record lineage as you build, not afterwards. A lineage graph reconstructed from memory six months later is a work of fiction (Data Lineage).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Source → extract: whatever the extract's predicate captures. If the predicate is time-based, the guarantee is only as good as the source's clock and commit semantics — which is weaker than most people assume (CDC vs Polling).
- Extract → lake: usually at-least-once with no ordering. Files land; nothing promises they land once, in order, or completely within a window.
- Lake → transform: deterministic if the transformation is a pure function of its inputs, and non-deterministic the moment it references the current time, a mutable dimension, or a non-idempotent merge.
- Transform → warehouse: atomic per publish if you built it that way, and not otherwise. Warehouses give transactional writes; a pipeline that writes in ten separate statements has ten separate observable states.
- Warehouse → dashboard: nothing. The BI layer can and does apply its own joins and filters that change the number after every guarantee upstream has been honoured.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Check the *ends* against each other: a scheduled reconciliation of a closed period, source versus serving table, on both row count and a summed measure. This is the only check that observes the whole journey at once.
- Check each hop's row count against the previous hop's, so a loss can be localised to an arrow rather than searched for across six systems.
- It misses anything that is wrong identically at both ends, and it says nothing about the periods that are still open — which is where late data lives (Late-Arriving Data).
- End-to-end freshness is the sum of every hop's delay, dominated by the coarsest schedule. A five-minute stream feeding an hourly transformation gives hourly data, and calling the platform "real-time" because one hop is fast misleads every consumer who hears it.
- A mart adds another interval on top of the warehouse it derives from. Consumers reading the mart are always further behind than consumers reading the model it came from, and rarely know it.
- Freshness is per-dataset, not per-platform. Publishing one number for "the warehouse" hides the one table that has not updated since Friday.
- A schema change at the source propagates along every arrow, and each hop chooses whether to absorb it, reject it or pass it on. Deciding that per-hop in advance is what separates a platform that survives upstream changes from one that breaks weekly (Schema Evolution).
- Adding a hop is itself a schema change for everyone downstream of it, and it deserves the same compatibility discussion as a column rename.
- The most dangerous evolution is a *reordering* of the journey — moving a filter from the transformation into the extract, say — because history was built under the old shape and the two periods are no longer comparable.
- The journey is recoverable up to the earliest immutable copy. If raw is retained, a transformation bug costs a re-run; if raw was cleaned in place, it costs the data.
- Replaying from an event log is recovery of the same kind, bounded by retention rather than by storage. Retention is therefore a recovery-window decision, not a cost decision, and should be argued as one (Retention and Replay).
- Re-running the whole journey is rarely right. Identify the earliest hop that is still correct and reprocess forward from there (Reprocessing vs Retrying).
What can go wrong
- A hop that silently produces zero rows, which every subsequent hop processes successfully.
- Two hops disagreeing about grain, so a join fans out and the fact table gains rows nobody added.
- Retention on an intermediate hop expiring before a downstream consumer needed to replay through it.
- A mart that has drifted from its parent model because it was patched directly during an incident and never reconciled.
- A "temporary" direct query from a dashboard to the source database that became permanent and now breaks whenever the application migrates (Data Engineering Anti-Patterns).
- "More layers means better architecture." Layers are justified by reprocessing boundaries and contract boundaries. A layer with neither is a copy with a name.
- "The lake replaces the warehouse." They answer different questions: one stores anything cheaply, the other queries structured data fast. Lakehouse table formats narrow the gap; they do not erase the distinction (Lake vs Warehouse vs Lakehouse).
- "If every hop succeeded, the journey succeeded." Each hop can succeed while the composition is wrong — most obviously when a grain changes and nothing asserts the new one (Grain: What Does One Row Represent?).
- "Freshness is a platform property." It is a per-dataset property, and averaging it across a platform hides exactly the table that is broken.
Operating it
- Row count per hop, per run, as a single chart. A drop between two adjacent hops localises the fault immediately and is the highest-value chart in most platforms.
- Freshness per serving dataset with its SLO drawn on the same axis (The Freshness SLO).
- Lineage edges emitted by the transformation tool itself, so the graph is generated rather than maintained (Data Lineage).
- At 10x, the coarse-grained hops start missing their windows and incremental processing becomes mandatory rather than an optimisation.
- At 100x, the physical layout of the lake and warehouse dominates everything else: partitioning, file size and clustering decide whether a query is possible at all (Physical Data Layout).
- Consumer growth changes which hop is the bottleneck. A model that was fine for four analysts becomes the case for a mart when it is queried by eighty dashboards a minute.
- Each hop costs storage for its copy and compute for its transformation. A layer that no consumer reads and no reprocess depends on is pure cost and should be deleted.
- Marts trade storage and pipeline complexity for query cost. That is usually a good trade when many people run a similar query, and a poor one when a model is queried rarely by a few analysts.
- The expensive mistake is a full rebuild of every layer nightly when only the last day changed — the cost scales with history rather than with new data (Compute Waste).
- Every layer added buys reprocessability and isolation and costs storage, latency and one more thing that can be stale. Platforms with seven layers usually have three that exist because a diagram had them.
- A mart makes one query pattern fast and every other one impossible without going back to the model. That is a good trade only when the pattern is genuinely stable.
- Keeping raw forever is the strongest recovery position and the clearest privacy liability. Retention is where those two arguments meet and neither side wins outright (Data Retention).
Where the grain changes
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
One committed change to one row. This is the first grain change: an order is no longer one record, it is a sequence of them.
guarantees Every committed change is emitted at least once, in log order per source. Not exactly once, not transactionally grouped once the events are split across topics, and not ordered against any other source.
Counting rows in the change stream counts *changes*, not orders. An order inserted and then updated twice is three records, so a COUNT(*) over raw CDC reports three orders and nobody can see that it is wrong by looking at it.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe sequence of concerns — capture, land, transform, validate, model, serve — is stable across stacks. Which systems implement which concern varies completely, and some platforms collapse several into one product.
- SIMPLIFIEDThe linear chain is a teaching shape. Real platforms fan out (one raw source feeding several models) and fan in (one model joining several sources), and a genuine lineage graph is a DAG rather than a line.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns the delivery and ordering semantics that each arrow in this journey inherits — at-least-once, per-partition ordering, and what a replay actually replays.