Orchestration and transformation without building a data team

The first data pipeline is a scheduled script. It works. The second one depends on the first, so someone adds a sleep. The third fails halfway and leaves a table half-loaded, and the fix is a manual truncate. By the tenth, there is a directory of scripts, an undocumented ordering, and one person who knows which ones can be safely rerun.

That is the point at which an orchestrator earns its place, and the point at which most teams install one and immediately misuse it.

Airflow coordinates, it does not process

The single most common mistake is doing the work inside the orchestrator. A task that pulls ten million rows into the scheduler's memory, transforms them in Python and writes them back makes the orchestrator a bottleneck, a memory risk and a single point of failure.

The orchestrator's job is to say what runs, in what order, under what conditions, and to record what happened. The work belongs where the data is: a query executed in the warehouse, a job submitted to a processing cluster, a container running elsewhere.

The practical test: if a task cannot be safely killed and restarted, it is doing too much.

Idempotency and the interval

The property that makes a pipeline operable is that rerunning a period produces the same result as running it once. Without it, every failure becomes a manual investigation into what was partially written.

Two habits deliver most of this. Process a bounded interval rather than "everything since last time", so a run is defined by the window it covers and can be repeated. And write with replace semantics for that window, deleting and reinserting the partition rather than appending, so a rerun overwrites rather than duplicates.

Backfilling is the same mechanism pointed at the past, and a pipeline built this way backfills trivially while one built on appends does not.

Task dependencies are not data dependencies

An orchestrator knows task B runs after task A. It does not know that B reads a table A writes. When someone adds task C that also writes that table, the graph looks fine and the data is wrong.

Two things help. Express the dependency on the data where the tool allows it, using dataset or asset-aware scheduling so a downstream job triggers when its input is updated rather than at a fixed time. And keep the transformation dependency graph in the transformation tool, which is what dbt is for, rather than expressing every model dependency as an orchestrator task.

The shape that works: the orchestrator runs a small number of coarse steps, one of which is "run the transformations", and the transformation tool resolves the fine-grained graph itself.

dbt, and the part that matters most

dbt turns transformation into version-controlled SQL with a dependency graph, and its most valuable feature is not the templating. It is the tests.

A handful of assertions per model catches the failures that otherwise surface as a wrong number in a board deck: this column is never null, this key is unique, this foreign key exists in the parent table, this categorical column only contains these values. Freshness checks on sources catch the upstream system that stopped delivering.

That is the whole data quality programme for most organisations, and it costs a few lines per model. The teams that skip it are the teams that discover a pipeline has been silently wrong for six weeks.

Two other properties worth having: lineage generated from the graph, so you can answer what breaks if this column changes, and documentation living beside the model rather than in a wiki.

Materialisation is where cost decisions live. A view costs nothing to store and recomputes on every query. A table costs storage and is fast to read. Incremental models process only new rows and are the right answer for large fact tables, at the cost of more complexity and a need to occasionally rebuild fully. Choose per model, and remember that a heavy model rebuilt hourly against a warehouse priced per byte scanned is exactly the failure in BigQuery bills that triple overnight.

Extraction, and the part not to build

Getting data out of source systems is the least interesting and most tedious part of this, and it is where teams burn months writing connectors for systems whose APIs change.

Managed extraction services and the open source connector ecosystems exist precisely for this. Use them for the standard sources: the CRM, the payment processor, the advertising platforms, the production database replica. Write custom extraction only for the genuinely proprietary systems where nothing exists.

The pattern that has won is extract and load first, transform afterwards inside the warehouse. Land the raw data, keep it, and do the shaping in SQL where it is testable and rerunnable. The alternative, transforming in flight, means a logic change requires re-extracting from a source that may not let you.

When you do not need Airflow

This is the part the tooling ecosystem does not tell you. Airflow is a distributed system with a scheduler, a metadata database, workers and an upgrade path. Running it yourself is a real operational commitment, and even the managed versions are not free.

With five pipelines and no complex dependencies, a scheduled job in your existing platform plus dbt's own dependency resolution covers it. Cloud-native schedulers, a workflow service, or simply cron on a container with good alerting are all reasonable, and all are less to operate.

The lighter orchestrators are a genuine middle ground with a better local development story and less machinery. And the managed Airflow offerings on each cloud are worth the premium over self-hosting for almost everyone, because the operational work is the part with no differentiating value.

Adopt a heavyweight orchestrator when you have enough pipelines, enough cross-team dependencies or enough backfill complexity that coordination is the actual problem. Not before.

The things people forget

• Scheduler time is not data time. A run scheduled at midnight processes the previous interval. Getting this wrong produces off-by-one-day errors that are found weeks later.
• Time zones and daylight saving. Schedule in a fixed offset and handle the conversion explicitly.
• Alert on freshness, not just on failure. A pipeline that succeeds while its source has stopped updating is the worse failure, because nothing goes red.
• Credentials in the orchestrator. It holds access to every system it touches, which makes it a high-value target and puts it squarely in the scope of a Kubernetes Secret is base64 and secret handling generally.
• Personal data flows through here. The warehouse copy is subject to the same retention and deletion obligations as the source, which is one of the rows people forget in GDPR as engineering controls.
• Cost per run is invisible by default. A model rebuilt hourly instead of daily is a twenty-four-fold cost increase that no alert will tell you about.

What to do this week

Take your most important dashboard, trace back to the tables feeding it, and check whether any of them have a test asserting the obvious: that the primary key is unique and that yesterday's data arrived. If not, add those two tests. They take ten minutes and they are the difference between finding a problem on the day it happens and finding it in a quarterly review. We start the data workstream of a cloud engagement with exactly that check.