The OLTP to OLAP Journey
Production database, extraction, transport, raw landing, transformation, analytical storage, BI query — what each hop buys, what it actually promises, and where the shape of the data changes underneath you.
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.
By what route does a committed operational row become a number in an analytical query, and what does each hop along that route promise?
The analyst reading the last hop, who sees a number and cannot see any of the six systems behind it. Everything this lesson does is make those six systems, and specifically their promises, visible to someone who will never look at them (Trusting Data).
The grain changes four times along this route and tracking it is the point. One committed row becomes one change record, becomes one line in a landed file, becomes one reconstructed entity, becomes one fact row, becomes one cell in an aggregate. Confusing any two of those produces a number that reconciles perfectly against the wrong thing (Grain: What Does One Row Represent?).
Treat the route as plumbing — "the data lands in the warehouse somehow" — and put the engineering attention on the SQL at the end, because that is where the business logic lives and that is what the analysts read.
The SQL is correct and the answer is wrong, because the extract used WHERE updated_at > :last_run against a source that assigns updated_at before commit. Rows whose transaction committed after the window closed were skipped, permanently and silently (Incremental Extraction).
- The SQL is correct and the answer is wrong, because the extract used
WHERE updated_at > :last_runagainst a source that assignsupdated_atbefore commit. Rows whose transaction committed after the window closed were skipped, permanently and silently (Incremental Extraction). - A single order appears twice in the fact table. The transformation is blameless: the transport delivered the change twice, which it is entitled to do and which the pipeline was supposed to absorb (At-Least-Once Delivery, Deduplication).
- The month-over-month trend is fake, because the extraction method changed between the two months and the periods have different completeness. Nothing failed and both numbers are internally consistent (Batch vs Streaming Ingestion).
- A backfill overwrites a partition while a scheduled report is querying it, and the 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. An operational database 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 a poor landing zone for unfiltered raw bytes (OLTP vs OLAP).
- The arrows 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, and it is usually undocumented (Data Contracts).
- Extraction is the hop with the widest variation in what it promises. Reading a replica gives you current state and no history of how it got there; a timestamp-window query gives you whatever its predicate happened to match; reading the database's own log gives you every committed change in commit order (CDC vs Polling, Change Data Capture).
- The grain transformation at each hop is the part most often skipped. A change record is not an order — it is a *change to* an order, and several of them describe one order's life. Collapsing them into "the order" is a 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 a re-run rather than a data loss; staging exists so cleaning can be re-run without re-modelling; curated exists so consumers depend on a stable contract rather than on whatever shape the source happened to have that quarter (Raw, Staging, Curated: Layers by Purpose).
The route, stage by stage
Read the pipeline below by its guarantee column rather than its description column. The description tells you what each stage does, which is usually obvious; the guarantee tells you what you are allowed to conclude at that point, which usually is not.
The property that makes this a data-engineering diagram rather than an architecture one is that the guarantees only ever weaken going down. No hop can strengthen what it received. A transactional warehouse fed by an at-least-once transport contains an at-least-once table, and the only thing that changes that is an explicit deduplication on a business key somewhere in the transformation.
The other thing to notice is the last row. The BI layer promises nothing, applies its own joins and filters, and is invisible to every test, contract and lineage tool upstream of it. A remarkable share of "the number is wrong" incidents terminate there.
- 1Operational commit
The application writes rows inside one transaction and commits.
guarantees Atomicity and durability of the transaction; the change is present in the database log in commit order.
fails by Committing business logic that is wrong. Nothing downstream can detect it, because everything downstream faithfully reproduces it.
- 2Extraction
Gets the change out: a replica read, a timestamp-window query, or a reader on the database's own log.
guarantees Method-dependent. A log reader promises every committed change at least once in commit order; a timestamp window promises only what its predicate matched.
fails by A
WHERE updated_at > :last_runwindow missing rows whose timestamp was assigned before a commit that landed after the window closed — permanently, and with no error. - 3Transport
Moves change records to the analytical side, usually through a durable partitioned log.
guarantees Durability and replay within retention; ordering per partition only, never across partitions.
fails by Retention expiring while a consumer is behind, at which point the changes are gone rather than late.
- 4Raw landing
Writes what arrived to object storage, untouched, partitioned by arrival time.
guarantees That what arrived is preserved exactly and everything downstream can be rebuilt from it.
fails by Being cleaned or deduplicated on the way in, which destroys the only evidence of what the source actually sent.
- 5Transformation
Reconstructs entities from change records, cleans, casts, joins and models into facts and dimensions.
guarantees Only what its tests assert. By default, nothing at all.
fails by Choosing the latest change per key by arrival time rather than commit order, so an out-of-order update wins and the entity is left in a state it was never in.
- 6Analytical storage
Holds the modelled result in a columnar, partitioned layout built for scans.
guarantees Scan performance for the shapes the layout anticipated, and — if published atomically — that no reader sees a half-written state.
fails by A backfill overwriting a partition that consumers are mid-query on.
- 7BI query
Aggregates the model into the number on the tile.
guarantees Nothing. It applies whatever joins, filters and date grains the tile was configured with.
fails by A filter set inside the BI tool that no model, test or lineage graph upstream can see.
Seven stages, one strong guarantee, and it is the first one. Every stage below either preserves what it received or weakens it, which is why the useful question at any hop is not "did this succeed" but "what am I now entitled to believe".
The shape changes on the way
Between the first hop and the last, the data does not merely move — it is re-expressed in a different vocabulary. Natural keys become surrogate keys so history can be versioned. A mutable status becomes a value as of a processing point. Money in the transaction currency becomes a converted measure. Two timestamps appear that the source never had.
Each of those is a deliberate, correct modelling decision, and each is a place where a consumer's assumption can be wrong without anything failing. Read the impact rows below by their silent flag: the one change that produces a loud error is the harmless one, and every change that produces a wrong number produces it quietly.
This is the concrete reason the analytical model needs its own documentation rather than inheriting the source's. A column called net_revenue in the warehouse and a column called total_cents in the application are not the same fact expressed differently; they are different facts, and the difference is a business rule that lives in the transformation (Dataset Documentation).
- orders.id — bigint, primary key
- orders.customer_id — bigint, foreign key to the live customer row
- orders.status — text, mutable, overwritten in place
- orders.total_cents — integer, in the transaction currency
- orders.currency — char(3)
- orders.created_at — timestamptz
- orders.updated_at — timestamptz, mutable
- fct_orders.order_key — surrogate key
- fct_orders.customer_key — surrogate key pointing at the customer version valid at order time
- fct_orders.order_status — text, as of the last change processed
- fct_orders.net_revenue — numeric, converted to the reporting currency
- fct_orders.order_ts — timestamp, UTC
- fct_orders.source_commit_ts — timestamp, UTC, when the change committed at the source
- fct_orders._loaded_at — timestamp, UTC, when this pipeline processed it
change Natural keys become surrogate keys, a mutable status becomes a value as of a processing point, an integer amount in the transaction currency becomes a converted measure, and two timestamps appear that the source never had: when the change committed, and when we processed it.
| Consumer | Effect | How it shows up |
|---|---|---|
| An analyst joining `fct_orders` back to a raw extract of `orders` on `id` | The join key does not exist under that name. The query errors immediately and gets fixed in five minutes. | Loudly — it raises |
| A dashboard summing `net_revenue` | The measure is in one reporting currency at a rate chosen by the transformation. Totals move the day the rate source changes, with no schema change anywhere. | Silently — no error, wrong result |
| A report grouping by `order_status` | Status is a snapshot as of the last processed change, not a historical fact. Re-running the same report next month returns different numbers for the same past orders. | Silently — no error, wrong result |
| A model joining on `customer_key` | Gets customer attributes as of order time rather than as of today — usually what was wanted, and almost never what was expected. | Silently — no error, wrong result |
| A freshness check reading `_loaded_at` | Measures processing time, not event time. A pipeline reprocessing old data looks perfectly fresh while delivering nothing new. | Silently — no error, wrong result |
Reading the route 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 a debugging tool rather than documentation, and why the arrows carry as much information as the boxes.
The method is one question repeated at each node: is the affected period complete and correct *here*? The first "no" walking upwards is where the incident lives, and everything above it can be eliminated. Everything below it is contaminated and will need reprocessing once the cause is fixed.
The walk terminates in one of three places, and they call for different people. The source is wrong — a genuine business change or an application bug, and not a data incident. A hop lost or duplicated data — an ingestion or delivery problem. Or a transformation is wrong — the code did exactly what it was told and what it was told does not match the definition anyone believes (Debugging a Data Incident).
- Revenue tile in the BI tool
holds One number, and a filter set stored inside the BI tool.
could corrupt A filter, join or date grain configured in the tile that nothing upstream can see, test or version.
↑ reads from - `fct_orders`
holds One row per order, with converted measures and dimension keys.
could corrupt A fan-out join against a dimension with duplicate keys; a status filter that excludes a value which did not exist when the filter was written.
↑ reads from - `stg_orders`
holds One row per order, reconstructed as the latest change per order id.
could corrupt Choosing "latest" by arrival rather than by commit order; dropping delete records instead of tombstoning the entity.
↑ reads from - Raw change files
holds Every delivered change record exactly as received, partitioned by arrival.
could corrupt Duplicates from redelivery, which are normal and must be absorbed rather than prevented; a gap where the connector was down.
↑ reads from - Connector position in the source log
holds One offset into the operational database's change log.
could corrupt Falling behind log retention; restarting from a fresh snapshot and re-emitting history; missing DDL events entirely.
↑ reads from - Operational `orders` table
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, and the data platform is faithfully reporting it.
Six nodes, one question each. The value of writing it down before an incident is that during one, the argument about who owns the problem is usually longer than the investigation would have been.
How to build it
Most important first.
- Draw this route for your own system and write the grain and the guarantee on every arrow. Most architecture arguments dissolve the moment both are written down (The Fundamental Data Journey).
- Prefer log-based extraction where the source supports it, because it is the only method that promises every committed change rather than whatever a predicate matched (Change Data Capture).
- Land raw exactly as received, including the fields nothing currently uses. Storage is the cheapest thing on this route and the field you discard today is the one next quarter's question needs (The Raw Landing Zone).
- Reconstruct entities by commit order, not by arrival order, and make that explicit in the model rather than implicit in a
max(loaded_at)(CDC Ordering and Transaction Boundaries). - Publish the final table atomically, so no consumer can observe a half-built dataset, and keep the previous version long enough to roll back to (Atomic Publish, Rolling Back Data).
- Emit lineage as you build rather than documenting it 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 commit: atomic and durable, ordered on the primary's log. This is the strongest promise on the route and it is the first hop.
- Extraction: entirely method-dependent. Log-based reading promises every committed change at least once in commit order; a timestamp-window query promises only what the predicate matched, which is weaker than almost everyone assumes (Incremental Extraction).
- Transport: durable and replayable within retention, ordered per partition and not across partitions. Retention is therefore a recovery-window decision rather than a storage one (Retention and Replay).
- Transformation: only what its tests assert, and by default nothing. Determinism is a property you have to design for, not one you get (Idempotent Data Pipelines).
- Publish: atomic per publish if you built it that way. A pipeline that writes a table in ten statements has ten observable intermediate states, whatever the warehouse's transactional capabilities are.
- The composition promises the weakest link, not the strongest. A transactional warehouse fed by an at-least-once transport holds an at-least-once table until something deduplicates it on a business key (Deduplication).
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. It is the only check that observes the whole route at once (Reconciliation).
- Then check each hop against its predecessor, so a loss can be localised to one arrow instead of searched for across six systems. Row count per hop per run is the highest-value chart most platforms do not have.
- Both miss anything that is wrong identically at both ends, and both are silent about periods that are still open — which is exactly where late data and in-flight loads live (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 delivers hourly data, and calling the platform real-time because one hop is fast misleads every consumer who hears it.
- The route can be made fresher at any hop, and the return is zero unless it is the coarsest one. Optimising a fast hop is the most common wasted freshness work in this domain (Cost vs Freshness).
- Freshness is per dataset, not per platform. Publishing one number for "the warehouse" hides the one table that has not updated since Friday (Freshness Monitoring).
- A source schema change 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 change from one that breaks weekly (CDC and Schema Drift, Schema Evolution).
- Adding a hop is itself a breaking change for everyone downstream of it, and deserves the same compatibility conversation as a column rename (Backward Compatibility).
- The most dangerous evolution is reordering the route — 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, with no schema change to point at (Semantic Changes).
- The route is recoverable up to the earliest immutable copy. If raw is retained, a transformation bug costs a bounded re-run; if raw was cleaned in place, it costs the data (Keeping Raw History: The Recovery Position and the Liability).
- Replaying from the transport is recovery of the same kind, bounded by retention rather than by storage — which is why retention should be argued as a recovery-window decision (Retention and Replay).
- Re-running the entire route is rarely right. Find the earliest hop that is still correct and reprocess forward from there, into a location consumers are not reading, validated before publish (Reprocessing vs Retrying, Validating a Backfill Before You Publish).
What can go wrong
- A hop that silently produces zero rows, which every subsequent hop then processes successfully (The Pipeline Succeeded. The Data Is Wrong.).
- Two hops disagreeing about grain, so a join fans out and the fact table gains rows nobody inserted (Duplicate Rows).
- Transport retention expiring while a consumer is behind, converting late data into missing data (CDC Failure Modes and the Retention Deadline).
- A dimension overwritten in place, so re-running last year's report gives different numbers than it did last year (SCD Type 2 in Practice).
- The mitigation failing: a raw layer that was quietly "cleaned" on ingest to save storage, so the one copy that could have proved what the source sent no longer exists.
- "More hops means a better architecture." Hops are justified by reprocessing boundaries and contract boundaries. A hop with neither is a copy with a name and a schedule (Raw, Staging, Curated: Layers by Purpose).
- "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?).
- "The warehouse is transactional, so we get exactly-once." Transactional publish makes the *output write* atomic. It says nothing about input consumption or state, so a duplicate delivered upstream is committed exactly once as a duplicate (Exactly-Once: Input Consumption, State Update, Output Write).
- "Freshness is a platform property." It is per dataset, and averaging it across a platform hides precisely the table that is broken (Freshness Monitoring).
- Every hop is a new copy of personal data in a new system with its own access model, and the copy inherits the obligations of the original without inheriting its controls (PII in Pipelines).
- A deletion request against the source removes one row from one system. Honouring it along this route requires knowing every hop that retained it, which is a lineage question you can only answer if you recorded lineage while building (Deletion Requests, Data Lineage).
Operating it
- Row count per hop per run on one chart. A drop between two adjacent hops localises the fault immediately and is the single highest-value chart on this route (Pipeline Metrics).
- The connector's position relative to the head of the source log, which is the leading indicator for the retention failure rather than a lagging one (CDC Failure Modes and the Retention Deadline).
- Freshness per serving dataset with its stated SLO on the same axis (The Freshness SLO).
- Lineage edges emitted by the transformation tool itself, so the graph is generated rather than maintained by hand (Data Lineage).
- At 10x, the coarse hops start missing their windows and incremental processing stops being an optimisation (Incremental Processing).
- At 100x, the physical layout at the storage hop dominates everything else — partitioning, file size and sort order decide whether the final query is possible at all (Physical Data Layout).
- Consumer growth moves the bottleneck to the last hop. A model that was comfortable for four analysts becomes the argument for a mart when eighty dashboards query it every minute (Data Marts).
- Every 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 with a name (What Actually Drives Data Platform Cost).
- The expensive mistake on this route is rebuilding every layer nightly when only the last day changed, because the cost then scales with all of history rather than with new data (Compute Waste, Incremental Processing).
- Transport retention is a direct trade of storage against recovery window, and it is the one cost on this route that is genuinely worth increasing (Retention and Replay).
- 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 reference diagram had them (Raw, Staging, Curated: Layers by Purpose).
- Log-based extraction gives the strongest completeness guarantee available and costs a connector to operate, a position to monitor, and a schema-drift problem you did not previously have (CDC vs Polling).
- Keeping raw forever is the strongest recovery position and the clearest privacy liability. Retention is where those two arguments meet and neither wins outright (Data Retention).
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, transport, land, transform, model, serve — is stable across stacks. Which product implements which concern varies completely, and some platforms collapse three of them into one managed service.
- SIMPLIFIEDDrawn as a straight line. Real platforms fan out — one source feeding several models — and fan in — one model joining several sources — so a genuine lineage graph is a DAG and the failure analysis has to follow every branch rather than one path.
- SOURCE-SPECIFICWhat the extraction hop can promise depends on the source: a database with a logical replication stream can give commit-ordered completeness, whereas a third-party SaaS API usually offers a paginated endpoint with a mutable-timestamp filter and no way to detect a missed record at all.
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 every arrow on this route inherits: at-least-once delivery, per-partition ordering, what a replay actually replays, and why a total order across partitions is not something a transport can hand you for free.
- — DevOps / Production Engineering owns how the transformation code on this route is versioned, tested, deployed and rolled back. A data model is software; a change to it is a deploy, and the reason a metric moved is very often a merge.