Data Pipelines and Orchestration for Data Engineers (2026)

Airflow remains dominant — but here's when to consider alternatives

Apache Airflow has been the dominant data-orchestration tool since roughly 2018 and remains so in 2026. The reasons are operational, not aesthetic: the provider / operator ecosystem covers every major data system (Snowflake, BigQuery, Databricks, S3, dbt, Spark, Postgres, Kafka, every cloud provider); the managed offerings (AWS MWAA, Google Cloud Composer, Astronomer's hosted Airflow) take the operational burden off the data team; and a decade of accumulated tribal knowledge means most senior data engineers can debug an Airflow problem from memory. Maxime Beauchemin, Airflow's creator, has written extensively about the problem space (maximebeauchemin.medium.com) and his "functional data engineering" essay is still the canonical case for idempotent, partition-driven pipelines.

The modern Airflow pattern is the TaskFlow API (Airflow 2.0+), which lets you write DAGs as decorated Python functions. The TaskFlow style eliminates most of the boilerplate of the older PythonOperator / XCom pattern:

from datetime import datetime, timedelta
from airflow.decorators import dag, task

@dag(
    dag_id="user_events_daily",
    start_date=datetime(2026, 1, 1),
    schedule="0 2 * * *",  # 02:00 UTC daily
    catchup=False,
    max_active_runs=1,
    default_args={"retries": 3, "retry_delay": timedelta(minutes=5)},
    tags=["analytics", "user_events"],
)
def user_events_daily():
    @task
    def extract(logical_date: str) -> str:
        # Idempotent extract — partition path is deterministic.
        return f"s3://raw/user_events/dt={logical_date}/"

    @task
    def load(s3_uri: str) -> int:
        # MERGE INTO warehouse; returns row count.
        return run_warehouse_merge(s3_uri)

    @task
    def validate(row_count: int) -> None:
        if row_count == 0:
            raise ValueError("Zero rows landed — upstream broken")

    s3_uri = extract("{{ ds }}")
    rows = load(s3_uri)
    validate(rows)

user_events_daily()

What this DAG gets right: the schedule is a plain cron string; catchup=False stops Airflow from retroactively backfilling on deploy; max_active_runs=1 stops two concurrent runs from racing on the same partition; retries=3 with a 5-minute delay handles transient network errors; {{ ds }} templates the logical date so the partition path is deterministic. The Airflow docs (airflow.apache.org/docs/apache-airflow/stable/index.html) are canonical.

When to consider an alternative:

• Dagster when the team wants software-defined assets, asset lineage as a first-class concept, strong typing on inputs / outputs, and IO managers that decouple compute from storage. Dagster's blog (dagster.io/blog) covers the asset-first model in depth.
• Prefect when DAGs are dynamic (number of tasks unknown until runtime), workloads are event-driven (not cron-driven), or developer ergonomics matter more than the operator ecosystem. Prefect 3.x flows are plain Python without DAG declaration overhead.
• Kestra, Mage, Argo Workflows for niche use cases (Kestra for declarative-YAML-friendly teams, Mage for notebook-flavored workflows, Argo for Kubernetes-native compute graphs). None of these have crossed Airflow's share at general data-engineering shops in 2026.

The senior bar: you can articulate why your team is on Airflow (or off it), and the answer is operational and ecosystem-driven, not "the docs looked nice."

ETL vs ELT in 2026

The ETL-vs-ELT distinction is one of the few terminology debates in data engineering that genuinely matters. The order of the letters reflects where the transformation happens — and that choice cascades through your stack architecture, cost model, and debugging story.

ETL (Extract, Transform, Load) — the legacy pattern. A pipeline tool (Informatica, Talend, custom Python) reads from the source, transforms in-flight (often on a dedicated transformation server), then loads the result into the destination. The transform happens before the data lands.

ELT (Extract, Load, Transform) — the modern pattern. The pipeline lands raw or near-raw data into the warehouse first; transformation happens inside the warehouse using SQL (typically dbt). The transform is a downstream concern, not part of the loader.

Why ELT won for warehouse-resident analytics:

• Warehouse compute became cheap and parallel. Snowflake, BigQuery, Databricks SQL, Redshift, and ClickHouse all execute SQL transformations at massive parallelism. The economics flipped: it's cheaper to land raw data and let the warehouse transform than to maintain a dedicated ETL fleet.
• Raw data is a debugging asset. When a downstream metric looks wrong, the data engineer needs the original raw rows to triage. ELT preserves them; ETL discards them.
• SQL is the universal transformation language. Every analyst, analytics engineer, and data scientist on the team can read and contribute to SQL transformations. ETL pipelines written in proprietary tools or one-off Python are illegible to most of the team.
• dbt + Snowflake / BigQuery / Databricks emerged as a stack. The combination of dbt for transformation and a modern columnar warehouse for compute became the de-facto modern data stack. The dbt blog (getdbt.com/blog) and Joe Reis's Fundamentals of Data Engineering (O'Reilly) cover this stack and its trade-offs in depth.

When ETL still applies:

• PII redaction at source. When the destination team is not authorized to see raw PII, transformation must happen before landing. The redaction layer is part of the pipeline, not a downstream model.
• Streaming ingestion with tight transformation requirements. Kafka Streams, Flink, and ksqlDB transform in-flight because the destination is a real-time consumer, not a warehouse. Confluent's blog (confluent.io/blog) covers CDC and streaming transformation patterns extensively.
• Compute-bound transformations the warehouse can't handle. ML feature engineering with custom Python, image / video processing, large-scale graph algorithms — none of these run well in SQL, so the transformation happens in Spark / Beam / Ray before the warehouse load.
• Operational data stores (not warehouses). If the destination is Postgres / MySQL serving an operational app, ELT in the destination is rarely an option. The transform must happen upstream.

The connector layer is its own decision. Fivetran dominates the managed-connector market for SaaS-source ingestion (Salesforce, HubSpot, Stripe, Zendesk); the trade-off is per-MAR pricing that scales unpredictably. Airbyte is the open-source / self-hosted alternative; the trade-off is operational burden. Custom Singer / Meltano taps are the build-your-own option for sources without managed coverage. The 2026 default at Fortune 500 / SaaS-tier shops: Fivetran for SaaS sources where the spend is justified, Airbyte for everything else, Debezium for CDC from operational databases.

Idempotency + backfill: the senior-DE bar

The single biggest signal of a senior+ data engineer is that their pipelines are idempotent. The pipeline can run once, twice, or fifty times on the same logical partition and produce identical results. This is not a nice-to-have; it is the precondition for backfill, retry, disaster-recovery, and most operational sanity.

An idempotent pipeline has three properties:

• Deterministic partitioning. Every input row belongs to a stable partition (commonly a date / hour). The pipeline operates on one partition at a time, never on "everything since last run."
• Stateless task logic. Tasks read inputs by partition key and write outputs by partition key. They do not maintain across-run state in local files, in-memory variables, or task-instance side channels.
• Replace-or-merge writes. Loads either fully replace the destination partition or use MERGE / INSERT-ON-CONFLICT semantics that produce the same result regardless of pre-existing state.

The canonical idempotent INSERT pattern in a Postgres / Redshift / Snowflake-flavored warehouse — note the deduplication via row_number on a stable business key:

-- Idempotent merge of a single daily partition.
-- Safe to run any number of times; result depends only on inputs.

BEGIN;

-- 1. Delete the target partition (idempotent — works whether or not it exists).
DELETE FROM analytics.user_events
WHERE  event_date = DATE '2026-04-29';

-- 2. Insert deduplicated rows from the raw landing table.
-- Dedup on (user_id, event_id) keeping the latest by ingest_ts.
INSERT INTO analytics.user_events
  (event_date, user_id, event_id, event_type, event_payload, ingest_ts)
SELECT  event_date, user_id, event_id, event_type, event_payload, ingest_ts
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (
              PARTITION BY user_id, event_id
              ORDER BY     ingest_ts DESC
            ) AS rn
    FROM    raw.user_events_landing
    WHERE   event_date = DATE '2026-04-29'
) ranked
WHERE   rn = 1;

COMMIT;

What this pattern gets right: the entire operation is wrapped in a transaction so it commits atomically; the DELETE-then-INSERT replaces the partition wholesale (idempotent); ROW_NUMBER deduplicates on a stable business key with a deterministic tiebreaker (ingest_ts); the partition column (event_date) is the only filter — no "all rows since X" semantics that depend on prior state.

The pattern this replaces — the broken anti-pattern: INSERT INTO analytics.user_events SELECT * FROM raw.user_events_landing WHERE ingest_ts > (SELECT MAX(ingest_ts) FROM analytics.user_events). This anti-pattern reads existing destination state to decide what to write — meaning two concurrent runs race, replays produce different results, and a failed mid-run leaves the destination in an unrecoverable partial state.

Backfill is the operational consequence of idempotency. A backfill is just running the pipeline for an older partition. Senior data engineers design pipelines so that backfilling one day, one hour, or six months is a single command:

• Airflow: airflow dags backfill --start-date 2026-04-01 --end-date 2026-04-29 user_events_daily
• Dagster: backfill a partition set from the UI or via the GraphQL API; Dagster's asset model makes partition-aware backfill native.
• dbt: dbt run --select tag:daily --vars '{"start_date": "2026-04-01", "end_date": "2026-04-29"}' against incremental models with date-aware sql.

Retry semantics distinguish transient from permanent failures. Senior+ pipelines retry only on transient failures (network blips, rate limits, transient warehouse errors) and fail fast on permanent ones (schema mismatch, auth failure, malformed data). Airflow's default exponential backoff with a retry cap is the right starting point; the wrong move is "retries=10" on every task without distinguishing failure modes.

dbt-as-orchestrator pattern

One of the quieter shifts in the modern data stack is that dbt has become the in-warehouse orchestrator. The pattern: a thin scheduler (Airflow, Dagster, dbt Cloud) triggers a dbt run; dbt itself owns the dependency graph, the materialization order, and the test execution for everything inside the warehouse. The external orchestrator is a thin wrapper around dbt, not a parallel DAG of SQL templates.

The reasons this pattern won:

• dbt's ref() macro builds the DAG automatically. Every {{ ref('model_name') }} creates a dependency edge; dbt computes the topological order at compile time. You don't maintain a separate DAG file.
• Tests, docs, and lineage live alongside the SQL. dbt tests (not_null, unique, custom data tests) run in the same dbt run that materialized the models. A separate orchestrator can't do this without re-implementing dbt's graph.
• Selectors enable fine-grained partial runs. dbt run --select state:modified+ tag:nightly runs only changed models and their downstream graph; --exclude skips known-broken parts. This is the operational primitive for incremental development.
• on-run-end / on-run-start hooks handle warehouse-level concerns. Grants, vacuum, materialized-view refreshes, and audit-log writes run as hooks tied to the dbt run, not as a separate Airflow task.

The canonical pattern — a dbt run with selectors and on-run-end hooks:

# Run only the modified models and their downstream graph (state-aware).
dbt run \
  --select state:modified+ tag:nightly \
  --exclude tag:experimental \
  --vars '{"start_date": "2026-04-29"}' \
  --target prod

# In dbt_project.yml, on-run-end runs after a successful dbt run.
# on-run-end:
#   - "GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE bi_reader"
#   - "{{ audit_log_run_complete() }}"

# Run tests separately so failures are visible in CI without re-materializing.
dbt test --select state:modified+ --target prod

The external orchestrator (Airflow / Dagster) typically wraps this with a single task per dbt project (or per major dbt subgraph), plus tasks for the upstream loaders (Fivetran sync, Airbyte sync) and downstream consumers (Reverse-ETL push, dashboard refresh, ML feature export). The orchestrator does not maintain its own task-per-SQL-model DAG — that would duplicate dbt's graph and create drift.

The 2026 idiomatic stack:

1. EL layer: Fivetran or Airbyte lands raw data on a schedule.
2. Warehouse: Snowflake / BigQuery / Databricks / Redshift holds raw + staging + analytics schemas.
3. dbt: owns staging → intermediate → marts transformation, plus tests and docs.
4. Airflow / Dagster: schedules the EL sync, then the dbt run, then any downstream consumers. One DAG per logical pipeline, one task per logical step.
5. Reverse-ETL (Hightouch, Census): syncs warehouse marts back to operational tools (Salesforce, HubSpot, Marketo).

The senior+ bar: you can describe this stack from memory, you understand which tool owns which slice of the orchestration graph, and you don't reach for the external orchestrator to duplicate work dbt already does. The dbt blog (getdbt.com/blog) and Reis & Housley's Fundamentals of Data Engineering (O'Reilly) cover this layered architecture in depth.