The Data Loop
Source, ingestion, raw, transformation, validation, storage model, serving, consumer, feedback — nine stages that are a design order forwards and a diagnostic order backwards.
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.
Given any data problem, in what order should I reason about it — and which direction do I walk during an incident?
Two people, at two different times. The engineer designing a new dataset, who needs an order to think in so that no stage is skipped. And the engineer at 22:00 with a wrong number, who needs an order to *search* in so the investigation terminates.
One unit here is one stage boundary — an arrow, not a box. The boxes are systems and vary wildly between platforms; the arrows are where a guarantee is made or lost, and they are the same nine transitions everywhere. Reasoning about boxes produces tool arguments; reasoning about arrows produces designs.
Reason about whatever part of the problem is most visible — usually the SQL, because that is where the business logic lives and where the last change was made. Start there, and widen if it does not explain anything.
The SQL is correct and the answer is wrong, because the extract that fed it skipped rows that committed after their timestamp was assigned. Hours are spent reading a transformation that was never at fault (Incremental Extraction).
- The SQL is correct and the answer is wrong, because the extract that fed it skipped rows that committed after their timestamp was assigned. Hours are spent reading a transformation that was never at fault (Incremental Extraction).
- The investigation has no termination condition. Without an order, "I have checked everything I can think of" is the stopping rule, and it stops before the source (Debugging a Data Incident).
- Design skips stages that have no obvious owner. Validation and feedback are the two most commonly skipped, and they are precisely the two that would have caught the omission (Data Tests).
- Two engineers investigating the same incident start at different stages and reach different conclusions, both defensible, neither complete.
- The consumer stage is treated as outside the system, so a filter added in the BI tool — which changes the number after every upstream guarantee has been honoured — is never examined (Two Dashboards, Two Numbers).
What is actually happening
- The loop is not a description of a tool stack. It is a list of concerns that must be addressed somewhere, in an order fixed by dependency: you cannot validate what you have not transformed, or transform what you have not landed. Platforms collapse several concerns into one product; the concerns do not disappear when the boxes merge.
- Forwards it is a design order. Each stage asks one question — where does this come from, how does it arrive, what is kept untouched, what shape is it given, what is asserted, how is it stored, how is it served, who reads it, how would we know it broke — and skipping any of them leaves a gap that shows up as an incident later.
- Backwards it is a diagnostic order, and this is the property that earns it a lesson. Nobody debugs a data platform forwards, because the report is always "this number looks wrong" and the number is at the end. The productive direction is upstream, one stage at a time, asking the same question at each: is the affected period complete and correct here?
- The walk terminates because the question has a yes/no answer at every stage and the answers are monotone in practice: once you find a stage where the data is right, the fault is downstream of it, and once you find one where it is wrong, the fault is at or upstream of it. It is a search over an ordered list, not an open-ended investigation (Lineage Debugging).
- The final stage closes the loop. Feedback and observability feed back into the source and the ingestion decisions — freshness that is chronically missed changes the schedule, volume anomalies change the contract, a recurring class of incident changes the design. A platform without that edge is a line, and a line does not improve (Data Observability).
Nine stages, nine promises
Read the guarantees column from top to bottom before reading anything else. It starts strong, weakens at every hop, and reaches the consumer promising nothing at all — which is the shape of every data platform that has ever existed and the reason this domain is a discipline rather than a toolchain.
Two stages are worth pausing on. Validation guarantees only that the assertions somebody wrote hold on the rows they ran against; it never guarantees the data is correct, and treating a green test suite as correctness is the same error as treating a green DAG as correctness. Feedback guarantees detection, not prevention, and only for the classes it actually measures.
The failsBy column is where the lesson is dense. Each one is a specific, recurring, boring mechanism — not a category of risk — and each has a named lesson elsewhere in this domain that is nothing but a long answer to that one row.
- 1Source
Produces the record: an application write, a device event, a third-party API, a file drop.
guarantees Whatever its own storage promises at commit — atomicity, durability, its own constraints. Nothing about emission, ordering across systems, or history.
fails by Overwriting the past, hard-deleting a row, or committing business logic that is itself wrong. All three are correct behaviour from the source's point of view.
- 2Ingestion
Extracts or receives the record and moves it out of the producing system.
guarantees Whatever its predicate or log position captures — usually at-least-once, with ordering only within one source and one partition.
fails by A time-based watermark against timestamps assigned before commit; falling behind log retention, at which point changes are gone rather than late.
- 3Raw
Persists what arrived, exactly as it arrived, including fields nobody currently uses.
guarantees Byte-level preservation and replayability. Explicitly not deduplicated, not ordered, not complete.
fails by Being "cleaned" on the way in, which destroys the only copy that could have proved what really happened; or retention expiring on the only recoverable copy.
- 4Transformation
Cleans, casts, joins, deduplicates, aggregates and models into the shape consumers need.
guarantees Only what its tests assert — by default, nothing. Deterministic only if it is a pure function of its inputs.
fails by Fan-out joins, a
GROUP BYat the wrong grain, a dependence onnow()that makes yesterday's run unreproducible today. - 5Validation
Asserts completeness, uniqueness, types, ranges and distribution before anything is published.
guarantees That the assertions you wrote hold on the rows they ran against. Never that the data is correct.
fails by Running after publish, so it notifies instead of blocking; or passing every test while the metric means the wrong thing.
- 6Storage model
Lays the validated result out as facts, dimensions and history at a declared grain.
guarantees The declared grain and keys, if asserted; scan efficiency, if the layout was chosen for the query pattern.
fails by A grain that drifts from its documentation; history overwritten in place, which makes "what was true in March" unanswerable forever.
- 7Serving
Exposes the model to consumers — a warehouse table, a mart, an API, an extract.
guarantees Atomic visibility if published atomically, and a stable schema if a contract says so. Neither is automatic.
fails by A partial publish that is readable; a backfill overwriting the table while consumers query it.
- 8Consumer
Reads the served data and turns it into a decision, a chart, a forecast, a model or a retrieval.
guarantees Nothing. Every guarantee upstream can be honoured and the number can still change here.
fails by A filter or join added in the BI layer; re-aggregating a pre-aggregated mart; joining two tables at different grains.
- 9Feedback
Observes freshness, volume, schema, quality and lineage, and feeds what it finds back into earlier stages.
guarantees Detection for the classes it measures, at the interval it runs. Never prevention.
fails by Living inside the pipeline it monitors, so a pipeline that did not run is a monitor that did not run either.
The last arrow points back to the first. Every incident should change a decision at an earlier stage — a schedule, a contract, an assertion — or the loop is a line with a curve drawn on it.
Debugging is the same loop, walked backwards
Design runs left to right because of dependency: you cannot validate what you have not landed. Diagnosis runs right to left because of evidence: the only thing you know at the start of an incident is that the last stage produced something wrong.
The walk is mechanical. At each stage, ask one question — is the affected period complete and correct here? — and answer it with a count and an aggregate rather than by reading code. The first "no" walking upstream is where the incident lives; everything downstream of it is a symptom and everything upstream is innocent.
What makes this so much better than free reasoning is that it terminates and that it is shareable. Two engineers walking the same loop reach the same stage, and the handover at shift change is a stage number rather than a narrative.
The template below is deliberately dull. Under incident pressure the goal is not elegance; it is the same query, parameterised, run at nine points, so the numbers are comparable and nobody has to invent a new query while a controller is waiting.
Open the transformation that was deployed most recently, read the SQL, look for something suspicious. If nothing is found, read the model upstream of it. Widen until something looks wrong or the shift ends.
Count records for the affected period at the consumer, then at serving, then the model, the transformation output, raw, and the source. Stop at the first stage where the count is right. The fault is between that stage and the one below it.
Reading code searches a space with no ordering and no termination condition, and it is biased towards the most recent change — which is the cause in a minority of data incidents, because most causes are upstream and arrived without a deploy. Counting per stage searches an ordered list of nine, each step is a cheap query rather than an act of judgement, and it terminates whether or not anything was deployed.
1-- Run this once per stage, changing only the relation and the time column.2-- Compare the four numbers across stages; the first stage where they are3-- right is the boundary the fault sits below.4 5SELECT6 count(*) AS records,7 count(DISTINCT order_id) AS distinct_keys,8 sum(amount_minor) AS measure,9 max(event_ts) AS newest10FROM raw_orders -- then stg_orders, fct_orders, revenue_daily11WHERE event_ts >= DATE '2026-03-01'12 AND event_ts < DATE '2026-03-02';13 14-- records vs distinct_keys diverging -> duplication at or above this stage15-- records right, measure wrong -> a cast, a filter or a definition16-- records low at one stage only -> loss on the arrow into this stage17-- newest far behind the period -> the data has not arrived yet, at allFour numbers, not one. A single count(*) cannot distinguish duplication from loss from a bad cast, and those three have completely different responses. Partition predicates on both ends keep the walk affordable when it runs against production during an incident.
The edge that makes it a loop
Eight stages are a pipeline. The ninth is what turns it into a loop, and it is the one most platforms leave for later — which means the platform cannot learn from its own incidents and repeats each class of failure until somebody happens to remember the last one.
Feedback has to be measured from outside. A freshness check that runs as the last task of the pipeline reports on every failure except the one that matters most, because a pipeline that did not run also did not run its own monitor. This is not a subtlety; it is the single most common gap in data observability (Freshness Monitoring).
The other half of the edge is organisational rather than technical: an incident that resolves without changing anything at an earlier stage has not closed the loop. The change can be small — one assertion, one schedule, one contract clause — but there has to be one, and it belongs to a stage upstream of where the symptom appeared (Data Incidents).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Freshness of the newest complete record, evaluated by an external scheduler | The serving stage is producing at the interval it promised. | A paused DAG, a stopped source, a job that missed its window because of skew, an upstream that went silent. | Everything about data that is present. Fresh and wrong passes cleanly, and it fires falsely whenever the source genuinely produced nothing. |
| Record count per stage per period, compared with the adjacent stage | No arrow in the loop is losing or multiplying records. | Loss localised to one ingestion arrow; fan-out localised to one transformation; a partial publish between model and serving. | Losses offset by duplicates within the same run; anything already wrong at the source; the consumer stage, which has no count to compare. |
| Schema and contract compatibility at the ingestion boundary | The producer has not changed the shape of what it emits. | Added, removed, renamed and retyped fields, before they reach a transformation that would absorb them silently. | Semantic change — a field whose meaning moved while its name and type did not. There is no type for meaning (Semantic Changes). |
| Time-to-detection recorded per incident | How long the platform was wrong before anyone knew. | The absence of coverage itself: a rising number means incidents are being found by consumers rather than by signals. | Incidents nobody ever found, which are by definition absent from the numerator and are the reason this metric flatters a platform. |
| Lineage edges emitted by the transformation tool as it runs | What actually feeds what, as of the last run. | A stale mental model; the blast radius question during an incident; an orphaned dataset with no downstream reader. | Logic in the consumer stage — BI filters, notebook joins, spreadsheet lookups — which no upstream tool can observe (Column-Level Lineage). |
Only the first of these can run when the pipeline does not. That is why it belongs to a scheduler the pipeline does not control, and why it is worth building before the other four.
How to build it
Most important first.
- Address every stage explicitly when designing a dataset, including the ones with no obvious owner. Write one sentence per stage; a stage you cannot write a sentence for is a stage nobody has decided (Dataset Documentation).
- Write the guarantee on each arrow, not just the mechanism. "CDC to the raw layer" is a box diagram; "at-least-once, ordered per source table, complete only while the connector stays inside log retention" is a design (CDC Failure Modes and the Retention Deadline).
- Make the backwards walk cheap before you need it. One saved query per stage, answering "how many records for period P are here", turns a two-hour investigation into nine queries (Debugging a Data Incident).
- Put validation between transformation and serving, not after it. A test that runs after publish is a notification, not a gate, and consumers have already read the bad data (Contract Enforcement).
- Treat the consumer stage as inside the system. Filters, joins and calculated fields in the BI layer are transformations with no tests, no lineage and no review, and they are where a surprising share of wrong numbers live (The Metrics Layer).
- Close the loop deliberately: every incident should change something at an earlier stage, or the loop is decorative (Data Incidents).
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.
- The loop guarantees coverage, not correctness. Walking it means no concern was skipped; it says nothing about whether the decision at each stage was a good one.
- The backwards walk guarantees termination, which is its real value under pressure. It cannot guarantee speed — a stage with no observability is a stage where the walk stalls, and that is exactly what the feedback stage exists to fix.
- Guarantees weaken monotonically along the loop. The strongest promise in the whole chain is at the source, applies only inside the source, and every stage after it inherits at most what its weakest predecessor offered (The Fundamental Data Journey).
- Nothing in the loop guarantees that two stages agree about grain. That has to be asserted per boundary, and it is the omission that produces the most confidently wrong numbers (Grain: What Does One Row Represent?).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that tests the loop itself is an end-to-end reconciliation on a closed period — source count and a summed measure against the serving table. It is the only check that observes both ends of the loop at once (Reconciliation).
- Complement it with a per-stage record count for the same period, so a divergence can be localised to one arrow instead of searched for across nine stages.
- Both miss the consumer stage entirely. Nothing in the warehouse can see a filter applied in a BI tool, and no upstream test will ever catch it.
- End-to-end freshness is the sum of every stage's delay, dominated by the coarsest schedule in the chain. Adding a fast stage to a slow loop changes nothing a consumer can perceive.
- The loop makes it obvious where freshness is actually decided, which is almost never where people assume. A streaming ingestion feeding an hourly transformation gives hourly data, and calling that platform real-time misleads every consumer who hears it (Batch vs Streaming Ingestion).
- The feedback stage has its own latency, and it is the one nobody measures: the gap between a dataset becoming wrong and somebody knowing. That number, not the pipeline's latency, is what determines how much damage an incident does (Data Incidents).
- Stages get added and merged as a platform grows, and each addition is a schema change for everyone downstream of it. Inserting a staging layer between raw and transformation changes what consumers depend on even when no column changes (Model Layering).
- The most dangerous evolution is reordering: moving a filter from transformation into ingestion, or validation from before publish to after. History was produced under the old order and the two periods are no longer comparable, with nothing in the schema to show it.
- A merged stage still owes its guarantee. When a warehouse absorbs ingestion and transformation into one product, the questions "what did it capture" and "what did it assert" remain separate and still need separate answers (ELT: Load First, Transform Where the Data Lives).
- Recovery is a forwards replay from the earliest stage that is still correct — which is exactly the stage the backwards walk just identified. The walk is therefore not only diagnosis; it selects the reprocessing boundary (Reprocessing vs Retrying).
- Re-running the whole loop is almost always wrong. It is slower, it re-derives correct data, and it re-exposes every downstream consumer to a publish they did not need (Planning a Backfill).
- The loop is recoverable up to its earliest immutable copy. If raw was preserved, everything from transformation onward is a re-run; if raw was cleaned in place, the recovery point is the source, and if the source has since mutated there is no recovery point at all (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- A stage with no observability, where the backwards walk stalls and the investigation becomes guesswork. Ingestion is usually the one.
- Validation placed after publish, so it reports rather than prevents, and everyone treats its alerts as noise (Quality Alerting).
- The feedback edge missing entirely: incidents are resolved, nothing upstream changes, and the same class recurs quarterly.
- The consumer stage excluded from the model, so BI-layer logic is invisible to lineage and to every test (Column-Level Lineage).
- The loop used as a diagram rather than a procedure — drawn on a wiki, never walked during an incident, and therefore never improved.
- "The loop is an architecture." It is a list of concerns. A platform with three systems still has nine concerns, and pretending it has three is how validation and feedback go missing.
- "Start at the source." Start at the *consumer*, when designing, and at the consumer again when debugging. The source is where the design ends and where the investigation terminates, not where either begins (Who Actually Consumes This Data).
- "If every stage succeeded, the loop succeeded." Each stage can succeed while the composition is wrong — most obviously when the grain changes between two stages and nothing asserts the new one (Grain: What Does One Row Represent?).
- "Observability is the last stage, so it comes last." It is drawn last and it is built early, because a platform without it cannot diagnose its own first incident.
Operating it
- Record count per stage per period, as one chart with nine series. It makes the backwards walk a glance instead of nine queries, and it is the single highest-value chart most platforms do not have (Pipeline Metrics).
- Freshness per serving dataset with its stated SLO on the same axis, measured by an observer that does not depend on the pipeline running (Freshness Monitoring).
- Time-to-detection per incident: how long the data was wrong before anyone knew. This is the metric that tells you whether the feedback stage exists (Data Incidents).
- Lineage edges emitted by the transformation tool as it runs, so the graph is generated rather than remembered (Data Lineage).
- At 10x the loop is unchanged and the stages get slower. The first symptom is the coarsest stage missing its window, which converts into staleness rather than into wrongness (Incremental Processing).
- At 100x, stages start being split for throughput — one transformation becomes six models — and the number of arrows grows faster than the number of boxes. Lineage stops being optional at exactly this point (The Transformation DAG).
- Consumer growth changes only the last two stages, and it changes them a lot: eighty consumers means eighty definitions unless a serving contract prevents it (The Metrics Layer).
- Each stage costs a copy and a compute pass. A stage that no consumer reads and no reprocess depends on is pure cost with a name, and deleting it is the cheapest optimisation available (Compute Waste).
- The backwards walk costs scans, and it costs them at the worst moment — under incident pressure, against production. Pre-writing the per-stage count queries with partition predicates is what keeps that affordable (Partition Pruning).
- Observability is the stage most often cut for cost and the one whose absence is most expensive, because it converts every incident into a full-loop search (What Actually Drives Data Platform Cost).
- A stage-by-stage design is slower than writing the query and shipping it. That is a genuine cost and it is worth paying when the dataset will be depended on, and not worth paying for a one-off analysis.
- Instrumenting every arrow costs storage and compute continuously in exchange for speed during incidents that may not happen. Platforms under-invest here because the payoff is invisible until it is urgent.
- The loop is a linear teaching shape and real platforms are DAGs — one source feeds several models, one model joins several sources. The linear version makes the walk teachable and occasionally makes it wrong; when a stage has two upstreams, you walk both (DAGs in Data Pipelines).
Dataset review questions
This lesson uses the shared review exercise.
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 nine concerns and their dependency order hold for any movement of data between systems, including a single scheduled script that reads an API and writes a table. Which systems implement which concern varies completely and several concerns are routinely merged into one product.
- SIMPLIFIEDThe loop is drawn as a line with one feedback edge; real lineage is a DAG with fan-out and fan-in, so the backwards walk branches wherever a model has more than one upstream. The linear shape is what makes it teachable and is not what a lineage graph looks like.
- ORG-SPECIFICStages map to teams differently in every company, and the arrows that cross a team boundary are the ones that need a written contract. In a single-team platform the same nine concerns exist and none of the arrows need negotiating, which is why small platforms feel so much faster.
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 guarantee that each arrow in this loop inherits — delivery semantics, per-partition ordering, and what a replay actually replays. Every entry in the
guaranteescolumn above is borrowed from that domain rather than established here. - — DevOps / Production Engineering owns the incident-response discipline the backwards walk borrows: a defined first responder, a shared timeline, a stage-numbered handover, and a review that must change something upstream before it closes.