ETL, orchestration, and dbt

Verified 2026-08. How data actually moves and gets transformed, and the tools an interviewer will expect you to name.

ETL or ELT

ELT is the default for cloud analytics, and the reason is reprocessing: keeping the raw landing zone means a transformation bug is fixed by re-running, not by re-extracting from a source that may no longer have the data. ETL still wins when the transformation cannot be expressed in SQL, when data must be masked or de-identified before it lands, or when the target cannot do the compute.

Extraction patterns

• Full snapshot — simple, correct, and expensive. Fine for small dimension tables.
• Incremental by watermark — pull rows with updated_at > last_run. Watch the boundary: use a closed-open interval and account for clock skew and rows committed with an earlier timestamp than their commit time, or you silently drop records.
• Change data capture — read the database’s replication log (Debezium, or the warehouse vendor’s connector). Catches deletes, which watermarks do not, and does not load the source with repeated scans. The standard answer for keeping an analytical copy in sync.
• Event stream — the source publishes domain events and the pipeline consumes them. Cleanest, and requires the producer to cooperate. See ../../10_message_queues/kafka/.

Deletes are the question people miss. A watermark-based incremental load never sees a deleted row, so your warehouse keeps records the source no longer has. CDC, soft deletes, or a periodic reconciliation snapshot are the three answers.

Idempotency and backfills

Every pipeline runs twice eventually — a retry, a backfill, a manual re-run after a fix.

• Make each run idempotent: delete-and-insert the partition, or MERGE on a key, rather than blind INSERT.
• Partition by the logical date the data belongs to, not the date the job ran. Reprocessing then targets a partition.
• Make backfills a first-class operation, parameterised by date range and safe to run concurrently with the scheduled job.

Orchestration

Apache Airflow is the incumbent and the safest name to give — 3.x is current (3.3.0, July 2026). Airflow 3 brought a rearchitected execution model with task isolation, a new UI, and DAG versioning. Its strengths are the operator ecosystem and ubiquity; its historical weaknesses are scheduler overhead and the awkwardness of data-dependent branching.

Alternatives worth naming, with the reason each exists:

The distinction worth articulating: Airflow orchestrates tasks, Dagster orchestrates assets. If your mental model is “these tables must be fresh and correct” rather than “these scripts must run”, the asset model fits better.

dbt

SQL transformations as version-controlled models with dependencies, tests and documentation. It is the standard transformation layer in an ELT stack, and it is where the “data as software” practices — code review, CI, tests, lineage — actually landed.

-- models/marts/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

select
    o.order_id,
    o.customer_id,
    o.ordered_at,
    sum(i.amount) as order_total
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} i using (order_id)
{% if is_incremental() %}
  where o.ordered_at > (select max(ordered_at) from {{ this }})
{% endif %}
group by 1, 2, 3

ref() is the whole idea: it builds the dependency graph, so dbt knows the execution order and can generate lineage and docs from the code itself.

Materializations are the design decision: view (no storage, recomputed per query), table (rebuilt each run), incremental (append or merge only new rows), ephemeral (inlined as a CTE). Incremental is where the subtleties live — late-arriving data and updates to old rows need a lookback window or a MERGE, not a naive where updated_at > max.

Tests are dbt’s underrated feature: unique, not_null, accepted_values and relationships declared in YAML, plus arbitrary SQL tests. Run in CI, they are the “shift-left quality” idea from 01_data_mesh_and_federated_architecture.md made concrete.

Ecosystem note, 2026: dbt Labs and Fivetran completed an all-stock merger on 1 June 2026, and the Rust-based dbt Fusion engine was open-sourced under Apache 2.0 in dbt Core v2.0 — a large parse-time speedup, plus state-aware runs that skip unchanged models. SQLMesh (from Tobiko Data, also acquired by Fivetran) is the main alternative, notable for column-level lineage and virtual data environments. Knowing the consolidation story is a cheap way to sound current.

Data quality

Tests belong in the pipeline, failing the run — not on a dashboard.

• dbt tests for schema and referential assumptions.
• Great Expectations or Soda for richer expectation suites, including distributional checks.
• Freshness checks as first-class: a table that is silently 3 days stale is worse than one that is obviously broken, because people keep trusting it.
• Anomaly detection on volume and null rates to catch upstream changes nobody told you about.

Decide per check whether a failure blocks or warns. Blocking everything means people disable the tests; blocking nothing means nobody looks.

Interview angle

• “ETL or ELT?” - ELT for cloud warehouses, because keeping raw data means a transformation bug is fixed by re-running rather than re-extracting. ETL when the transform cannot be SQL, or when data must be masked before it lands - which is the case in regulated environments.
• “How do you load incrementally?” - a watermark on updated_at for the simple case, CDC when you need deletes and cannot afford repeated source scans. Deletes are the gap in watermark-based loading and the follow-up question.
• “How do you make a pipeline safe to re-run?” - idempotent writes (partition overwrite or MERGE on a key), partitioning by logical date rather than run date, and treating backfill as a parameterised first-class operation.
• “What does dbt actually give you?” - dependency resolution from ref(), so the DAG and lineage come from the code; materialization strategies; and tests plus documentation in version control. It is what made analytics engineering look like software engineering.
• “Airflow or Dagster?” - Airflow for ubiquity and its operator ecosystem; Dagster when you want to reason about assets - tables that must exist and be fresh - rather than tasks that must run. Temporal if the workflow is really a long-running business process rather than a data pipeline.
• “Where do data quality checks belong?” - in the pipeline, failing the run, with freshness treated as a check in its own right. A quality dashboard nobody watches is not a control.