“Data engineering is the development, implementation, and maintenance of systems and processes that take in raw data and produce high-quality, consistent information that supports downstream use cases, such as analysis and machine learning.”Joe Reis, Matt Housley · Fundamentals of Data Engineering · 2022 · Fundamentals of Data Engineering (2022), cap. 1 — definiția ingineriei datelor
A rerun for the same window must give the same result — that is idempotency.
ETL transforms data in a separate tool before loading it; ELT loads it raw and transforms it with SQL inside the warehouse, where compute is cheap, the SQL lives in git and lineage can be read from the queries. The new order makes extraction thin (copy faithfully) and transformation rich (model, test, document). Orchestration is the graph of tasks: dependencies, schedule, retries, alerts, a view of what ran and what did not. The parameter of a run is the time window, never "now" — otherwise yesterday's run can no longer be repeated. Idempotency is the property that makes everything repairable: rerunning for the same window produces exactly the same rows. You get it by deleting and reloading the partition, or by merging on a key — never by blind appends, which double the data on the second run. With it, a backfill is just a loop over old windows, and a 3 a.m. failure is fixed with a rerun, not an investigation. Incremental loads carry a watermark and a window for late-arriving data; small files get compacted; everything is tested in CI, like any code.
Think of a pipeline as a function, not a one-off event. If you pass it a time window as a parameter, the same input always yields the same output. That changes the nature of failures. A pipeline that blindly appends rows is like a cashier printing a receipt twice and charging both amounts. An idempotent one is like an online form: you can hit submit again and get the same confirmation. Reparability then becomes a design decision, not a hope. And if every run is a pure function over a window, a natural question follows: who decides the order and timing of these calls?
The previous passage ended with a question: who orders these calls? The answer is the orchestrator. It holds the graph of tasks, fires them when dependencies are ready, and retries what failed. The crucial detail: it never says "run now", but "run yesterday's window". So a failed run can be repeated tomorrow with the same input. Think of a baker who bakes to order, not on a whim: every order has a date, so an order missed on Tuesday can be baked identically on Wednesday. With windows as parameters, a backfill becomes trivial: the same recipe, applied chunk by chunk over the past. What remains to be seen is what guarantees a rerun won't spoil something already correct.
The previous passages showed who orders the runs. Now: which mechanisms make a run repeatable in practice. There are two main recipes. First, delete the window's partition and reload it from scratch, like replacing one sheet in a notebook rather than gluing one over another. Second, merge on key: update existing rows and insert only new ones, like a ledger where you correct a line instead of rewriting the whole book. What is forbidden is blind appending, which doubles the data on the second run. One problem remains: late-arriving data. That is why incremental loads carry a watermark and a lateness window, like a mailbox that still waits for letters after the collection truck has passed. But how do you verify all these mechanisms actually work before production?
Repeatable and correct are different things. A run can produce the same rows ten times and still be wrong. That is why transformations are tested like any code: assertions checking that keys are unique, totals match, no required field is empty. A failed test stops the pipeline before bad data reaches reports. Think of quality control in a canning factory: every can is checked before labeling, not after it sits on the shelf. And the tests live in the same repository as the code, running automatically on every change, like a recipe tasted at each ingredient change. So repairability becomes verified trust, not a promise. One last piece remains: who sees this whole process, and how does anyone figure out what happened at 3 a.m.
The previous passages covered the mechanics. One last question remains: who keeps the transformation alive? When SQL lives in version control, the border between data engineer and analyst blurs. The engineer maintains the pipes: windows, idempotency, orchestration. The analyst models meaning: what counts as an active customer, what makes a sale. It is like a kitchen: one keeps the stove working, the other decides the recipe, and both sign off on the dish. From this comes the need for data contracts: columns, types, and quality thresholds become written promises that downstream users can verify. A monthly billing report no longer breaks silently because someone renamed a column; the contract shouts first. That closes the loop: the pipeline is no longer a pipe maintained in silence, but a product with users, promises, and an owner. The next step of the lesson is choosing where your transformation ends.
The passages so far treated the warehouse as an endless room. In practice, you choose where transformation stops and raw storage begins. Raw data stays untouched, so it grows without control: an online shop that faithfully copies every click ends up with thousands of tiny files per day, and every query opens all of them. That is why periodic compaction exists, like sorting a pile of small receipts into one monthly folder. The reverse question also appears: what do you transform at the source, before copying? The practical rule: at the source only what is too bulky or too sensitive; everything else in the warehouse, where it stays visible and testable. Filtering access logs at the boundary is fine; computing revenue there is a trap, because it slips out of everyone's sight. The next question is who pays for all these choices.
The parameter of a run is never "now", but a time window. If the pipeline reads the current clock, yesterday's run can no longer be repeated, because "now" has moved. With windows, a run is just a function with arguments: January 5th yields the same rows no matter how often you call it. This turns a backfill from a heroic operation into a boring loop over old windows, run in parallel. Think of an accountant redoing March's statement: she works from March's figures, not today's. Then comes the natural next question: how often do you run each window, and who decides the schedule?
Earlier passages covered windows and tests. What remains is the concrete mechanics of idempotency: how to make a rerun safe at the row level. Two paths exist. First: delete the window's partition and reload it from scratch. Second: merge on a key, meaning update the old row if the key exists and insert only if it doesn't. Both produce the same rows on a second run. The third path, blind appending, is the trap: an incremental pipeline that only inserts will double Tuesday's orders if it runs twice. Think of a shopping cart: if the customer adds another item, you update their cart, not create a new cart next to the old one. But merge has a condition: the key must be stable and complete. A key that changes over time, like a customer's address, turns the update into lost history. Then you choose deliberately: current row or history with validity periods. And the choice of key decides whether your pipeline can be repaired or only cleaned up by hand.
Idempotency is not a virtue you hope for; it is a property you verify. Take the same window, run it twice, and compare the results: identical or not. The simplest method is delete-and-reload of the partition, like replacing a page in a notebook rather than pasting another on top. When a full reload is too expensive, you use a merge on a key: the new row replaces the old one instead of sitting beside it. Blind appending looks faster, but the second run doubles March's sales, and a revenue report starts lying without looking broken. That is why the idempotency test lives in continuous integration, next to code tests. How do you apply it to huge volumes, where a full reload costs too much?
Why it matters A pipeline that cannot be rerun without doubling the data turns every incident into a manual clean-up operation.