Data and AI engineering fundamentals

ETL transforms data in a separate processing tier before it lands, because storage was expensive and the warehouse could not be trusted with heavy computation. ELT lands the raw data first and transforms it inside the warehouse or lakehouse, usually in SQL. ELT won because object storage became almost free and warehouse compute became elastic and billed separately, so there is no longer a reason to discard the raw form. The real prize is replayability: when a transformation turns out to be wrong, you fix the logic and re-derive from the raw layer rather than asking the source system for last quarter again — which it frequently cannot give you. The costs are that raw storage grows forever unless somebody owns retention, and that transformation now competes with analyst queries for the same warehouse compute.

That running it again over the same logical window leaves the same state as running it once. The mechanism is almost always that a run owns a slice of the output and replaces that slice wholesale — delete-and-insert inside one transaction, an atomic partition overwrite, or a merge keyed on a business identifier. Appending is what breaks it, because an append has no way to know it already happened. Idempotency matters more here than almost anywhere else in engineering because every orchestrator retries, every operator eventually reruns a day by hand, and neither of those is a failure you get to prevent. A pipeline that is only correct when it runs exactly once is a pipeline that is occasionally wrong.

The job succeeded at loading and then failed afterwards, which is the ordinary case rather than the unlucky one.

Daily job for 2026-07-26, appends into fact_orders

  22:00  read source WHERE order_date = '2026-07-26'    -> 412,908 rows
  22:04  INSERT INTO fact_orders ...                       412,908 rows appended
  22:06  downstream aggregate step throws, task marked FAILED
  22:11  scheduler retries the task from the beginning
  22:15  INSERT INTO fact_orders ...                       412,908 rows appended

  fact_orders holds 825,816 rows for one day.
  Revenue for 26 July is exactly double. Nothing errored.

The insert was durable and the task was not, so the retry replayed a step that had already committed. Note that no alert can catch this: every task eventually reported success, row counts grew as expected for a busy day, and the only evidence is a revenue figure that looks like a good Sunday.

The fix is to make the run own the partition rather than add to it:

BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-07-26';
INSERT INTO fact_orders (order_id, order_date, customer_key, amount_minor)
SELECT order_id, order_date, customer_key, amount_minor
  FROM stg_orders
 WHERE order_date = DATE '2026-07-26';
COMMIT;

Three details decide whether this actually works. The delete and the insert must be one transaction or an interrupted run leaves the day empty, which is a different wrong answer rather than a safe one. The partition predicate must be derived from the run's logical date, not from now(), or a rerun of an old day deletes today. And where the output is late-arriving by nature — a fact that can be corrected weeks later — the honest shape is a merge on the business key rather than a window overwrite, because the window you would have to replace grows without bound.

The tempting non-fix is "check whether rows exist before inserting". That is a read-modify-write across two systems with no shared transaction, so two concurrent runs both see nothing and both insert.

Running a pipeline over historical windows, either because the logic changed or because the data was wrong. It is dangerous for three reasons that are easy to discover the hard way. It multiplies load: six months of daily jobs launched at once is 180 times the normal concurrency against the same source and warehouse, which is how a backfill becomes an incident in the operational database. Downstream consumers see history change underneath them, so a report someone already sent now disagrees with itself. And a backfill that is not idempotent duplicates 180 days rather than one. The disciplined version runs windows in bounded batches, uses a separate resource pool, and is announced to whoever depends on the numbers.

A directed acyclic graph of tasks with declared dependencies, so the scheduler knows what may run in parallel and what must wait. What the orchestrator owns is narrow and worth stating precisely: it decides when to start a task, records whether the process exited zero, retries on failure, and refuses to start a task whose upstreams have not succeeded. That is all. It does not know whether the data was correct, whether the source had finished writing, or whether a task that exited zero produced any rows at all. Most data incidents live in that gap, which is why data-aware scheduling — triggering on the arrival or freshness of a dataset rather than the completion of a task — has become the direction of travel.

Every task succeeded, every dependency was respected, and the answer published to the business was zero.

02:00  extract_orders          SUCCESS    0 rows read
02:05  transform_orders        SUCCESS    0 rows written
02:10  publish_daily_revenue   SUCCESS    revenue for 26 July = 0.00
07:30  the upstream export finally lands in the bucket
09:00  finance asks why yesterday earned nothing

DAG status: green. Retries used: 0. Alerts fired: 0.

The scheduler enforced task ordering perfectly and had no opinion about data existing. extract_orders read an empty prefix, exited zero, and every downstream task did exactly what it was told with the nothing it was given.

The first fix is a sensor or freshness assertion at the boundary: the extract does not begin until the expected file or watermark is present, and it fails rather than succeeding when the wait times out. Failing is the important half — an extract that logs a warning and continues has converted a loud problem into a quiet one.

The second is to treat an implausible row count as a failure. Zero rows is almost never right for a daily fact load, and a bound against the trailing median catches both the empty case and the accidental full reload. This has to be an assertion that stops the run, not a dashboard tile, because the whole failure mode is that publication continued.

The third is to make publication conditional. Gold-layer outputs should swap atomically to a new snapshot only when the checks pass, so a bad run leaves yesterday's correct numbers in place. A consumer reading slightly stale data knows how to behave; a consumer reading confident zeroes does not.

A retry is the orchestrator re-executing a task it just saw fail, usually within minutes and usually for a transient reason. A rerun is a human re-executing a window that already completed, because the output was wrong or the code changed. A backfill is a rerun across many windows. They are worth distinguishing because they need different safety properties: a retry needs the task to be idempotent, a rerun needs the output partition to be replaceable, and a backfill needs concurrency limits and a downstream communication plan. Conflating them produces the common design where retries are safe and a manual rerun quietly corrupts a month.