Orchestration Concepts

ETL transforms data in a separate processing tier before writing it to the target. ELT loads source-shaped data into the target first and transforms it there, using the target's own compute, keeping the raw input queryable.

A task is idempotent when running it again for the same input window leaves the target in the same state as one successful run. Orchestrators retry, replay and re-run tasks constantly, so anything else duplicates data.

It fires every fifteen minutes, at :00, :15, :30 and :45, during hours 09 through 17 inclusive, Monday to Friday. That is 36 firings per weekday, the last one at 17:45, and none at weekends.

The downstream task's trigger policy — the rule saying which combination of upstream terminal states lets it start. The default everywhere is all upstreams succeeded, so one failed branch blocks the join and the run ends incomplete rather than publishing partial data.

Elastic columnar warehouses over cheap object storage made loading raw and transforming with SQL cheaper and faster than provisioning a separate ETL cluster. Transformation became rented per query, written in SQL, and rerunnable against retained raw data.

An append has no way to remove what a previous attempt wrote, so every re-run adds another copy. Fix it by making the write replace the slice: delete-then-insert for that window in one transaction, a merge on a natural key, or an atomic partition swap.

Exit status only proves the code ran. Catching this needs data-level metrics recorded per run: output row counts compared with recent history, input-to-output ratios, null and distinct rates on key columns, and dataset freshness — with thresholds that fail the run.

Retry failures whose cause may differ next attempt: timeouts, throttling, preempted workers, transient locks. Deterministic causes — bad input data, a schema mismatch, a missing permission, a code bug — fail identically every time, so retries only delay the alert and burn compute.

A scheduled run is named for the data interval it covers, not for the clock time it starts. A daily interval only closes at midnight, so the run firing then is responsible for the day that just ended.

Write to a staging location first, run the checks against it, and publish only if they pass — the write-audit-publish pattern. Make the check a task that downstream work depends on, so a failure leaves the previous good version in place and stops dependent tasks from running.

Failure alerts fire on errors, but data goes stale in ways that produce no error: a run that succeeded over an empty source, a run still going and simply late, a paused pipeline that never started. Monitor the dataset's age, not the job's exit status.

Data lineage is the recorded graph of which datasets each pipeline job read and which it wrote. It answers where a table's numbers came from, what breaks if an upstream column changes, and which reports a failed job affected.

The orchestrator marks that attempt failed, waits the configured delay, then re-runs the whole task body from the start. Only when the retry budget is exhausted does the task count as failed and downstream work stop.

Only ordering: the downstream task will not start until the upstream one reaches a terminal state the downstream accepts. The edge moves no data, shares no memory, and spans no transaction — data travels through storage both tasks agree on.

Skipped is a terminal state distinct from success, and the default rule for starting a task is that all upstreams succeeded. A skipped parent does not satisfy it, so the child skips too, and the skip cascades down the whole branch.

Transformation logic changes and has bugs. An untouched raw copy lets you rebuild every downstream table from what actually arrived, without re-extracting from sources that overwrite rows, purge history, throttle large reads or have since changed shape.

Catchup is the scheduler filling in windows it never ran — every interval between the pipeline's start date and now — automatically as part of normal scheduling. A backfill is a human explicitly asking for a stated range to be re-run, usually because the logic or the source changed.

Because the wall clock moves and the interval does not. A task scoped by the current date reads and writes whatever today happens to be, so retries after midnight, replays and backfills all land in the wrong slice and collide with each other.

Table-level lineage says table B was built from table A; column-level says B.total came from A.amount and A.qty. Table-level over-reports impact — every downstream consumer looks affected — while column-level narrows a schema change to the handful of fields that actually break.

Retries only fire on failure. A task that hangs never fails, so it runs forever, holds its worker slot and blocks everything downstream. A timeout converts hanging into a failure, which is what the retry policy and alerting can then act on.

Half-open bounds make consecutive intervals tile the timeline with no gap and no overlap. A record whose timestamp lands exactly on a boundary belongs to exactly one run, so nothing is processed twice and nothing is skipped.

Size a task around what you want to retry, observe, parallelise and rerun. Split where failure causes differ or where a step is not safely repeatable; keep together work that shares state or must commit as one visible effect.

Orchestrator workers are sized for coordination, not data. Pulling a dataset into a worker makes its memory the pipeline's ceiling, lets one heavy task starve co-located tasks, wastes the target engine's parallelism, and makes every retry replay the whole transfer.

Move work out only when the language, not the size, is wrong: per-row model scoring, library-dependent parsing, calls to external services, or data that never needs to enter the warehouse. Set-based joins and aggregations should stay pushed down.

Separate computing data from delivering it. Put external effects — email, webhooks, partner file drops — in a step that only fires for current runs, gated on a backfill flag or on the window being recent, so a replay recomputes tables without re-notifying anyone.

Restarting from the failure point assumes the completed steps' outputs are still valid and the failed step left nothing behind. It is unsafe when the failed step wrote partially, when earlier steps captured a source snapshot that has since moved, or when a shared staging area was cleared.

It depends on the concurrency policy. Either two runs execute at once and contend for the same source and target, or the pipeline is capped at one active run and the queue backs up, so each run starts later than the last and lag grows without bound.

Attribute spend per model first — a handful usually dominate. Most of the overrun is shape and schedule, not location: full rebuilds that should be incremental, refreshes nobody reads, per-row logic. Move work off the warehouse only where measurement says the pricing genuinely misfits.

Size it first: windows times per-run cost and duration. Then chunk into coarser windows where the transform allows, run it on isolated compute with a hard concurrency cap, checkpoint progress so it resumes, build into a shadow target and swap after validation.