Airflow & dbt

A green DAG and a wrong number are not mutually exclusive

Modern stacks are better instrumented and no more honest about missing rows. Decim applies the same reconciliation, and the same scepticism about what the definition actually covers.

Request a demo See how it works

Fig. 1 — Task success is not data success

Every task exits zero. Three downstream partitions never received a row.

Why a green DAG proves so little

Airflow records whether a process exited zero. That is its job, and it is a different question from whether the data is right.

This is not a criticism of Airflow. A scheduler that tried to have opinions about row counts would be a worse scheduler, and the separation is correct. But it does mean the green tick in the UI is answering a narrower question than most people read it as answering.

Consider the ways a task exits zero having done the wrong thing. It queried a source that was empty and wrote nothing. It used a date bound computed from an execution date that was not what anyone expected, and loaded a window that had already been loaded. It retried after a partial commit and wrote half the rows twice. A sensor with soft_fail=True did not find its file, skipped, and downstream ran anyway against yesterday's data.

Every one of those produces a DAG run that is entirely green. None of them raise an exception, because none of them are errors in the sense the runtime understands — they are correct executions of an instruction that was wrong, or correct executions against input that was missing.

Definition sites

What the DAG file does not contain

The same problem as SSIS and SQL Agent, in a more modern costume: much of what decides behaviour lives in a database, not a repository.

The DAG file is genuinely in version control, which makes it tempting to treat as the definition. It is not. Connections, variables, pools, paused state and every manual intervention live in the metadata database, and a change to any of them alters what runs with no diff for anyone to review.

Where Airflow's behaviour is actually defined

What	Where it lives	In git?	Consequence
Connections and credentials	Metadata database / secrets backend	No	A repointed connection changes the target with no diff anywhere
Variables	Metadata database	No	Frequently hold thresholds, feature flags and date bounds
Pools and concurrency	Metadata database	No	Decides what actually ran in parallel, and what starved
Dynamically generated DAGs	A config table or a YAML file read at parse time	Partly	The repository holds the generator, not the graph
Paused / unpaused state	Metadata database	No	A paused DAG produces no failures and no rows
Manual clears and marks	Metadata database	No	Someone marking a task success leaves no trace outside Airflow

The dynamically generated DAG deserves particular attention, because it is the same structure as the config-driven engine described under custom ETL services. When a DAG is generated by iterating over rows in a config table, the repository holds the generator. The graph — the thing that actually ran — is data, and reading the Python tells you how the loop works and nothing about what it produced.

This is why topology treats the repository as one discovery source among several, and marks each node with whether the definition and the runtime observation agree.

dbt

Where dbt tests stop

They validate the rows in the table. They are structurally unable to see the rows that never arrived.

A well-tested dbt project is a genuine asset, and unique, not_null, accepted_values and relationships catch a great deal. What they all share is that they are assertions over the rows present. If 455,000 rows were diverted upstream and never reached the model, every one of those tests passes, because everything that did arrive is perfectly valid.

The tests that would catch it are the ones about arrival rather than content — source freshness, recency, and a row-count comparison against upstream. These are the tests most projects skip, partly because they are less obviously about "quality" and partly because a naive absolute threshold becomes noisy within a quarter.

models/sources.yml — the tests that see absence yaml

# The dbt test that would have caught it — and why most projects lack it.
# freshness and row_count checks assert something about arrival, which is
# exactly what a model-level test cannot see.

sources:
  - name: raw
    schema: raw
    tables:
      - name: transactions
        loaded_at_field: ingested_at
        freshness:
          warn_after:  { count: 2,  period: hour }
          error_after: { count: 6,  period: hour }
        tests:
          - dbt_utils.recency:
              datepart: hour
              field: ingested_at
              interval: 6

models:
  - name: fct_transactions
    tests:
      # Compares this run against the trailing average rather than a fixed
      # bound. A hardcoded threshold is either noisy or useless within a quarter.
      - dbt_utils.equal_rowcount:
          compare_model: ref('stg_transactions')

Note equal_rowcount comparing against ref('stg_transactions') rather than a hardcoded number. A fixed bound is either so loose it never fires or so tight it fires every time volume moves; a comparison against upstream asks the question that actually matters, which is whether this model lost rows relative to its own input.

Even so, this only catches loss inside the dbt boundary. Rows diverted before they reached raw.transactions are outside every test in the project, which is where source freshness earns its place: it is the only assertion in a dbt project that can fail because something did not happen.

Failure modes on a modern stack, and what distinguishes them

Failure mode	Cause	What proves it
Task succeeded, wrote nothing	Empty source, silent early return, wrong date bound	Row count per partition against the trailing baseline
Task succeeded, wrote twice	A retry after a partial commit, non-idempotent insert	Duplicate keys per partition; count against distinct count
dbt tests all pass	Tests validate the rows present, not the ones absent	Source freshness and equal_rowcount against upstream
Sensor timed out into success	`soft_fail=True` turns a missing file into a skip	Sensor task state against the manifest of expected files
Backfill overlapped a scheduled run	Two writers, same partition	Concurrent run ids writing the same `load_date`
A connection was repointed	Metadata change, no commit	Connection host history against the first bad run

Reconciliation

Reconciling across tasks

The check nothing in the stack performs: did the number of rows that entered a stage match the number that left it?

Airflow knows tasks ran. dbt knows models built. Neither compares a count at one stage against a count at the next, because neither owns both stages. That comparison is the entire substance of most data-loss investigations, and on most stacks nobody is doing it.

Where OpenTelemetry or OpenLineage is emitted, this becomes arithmetic rather than inference — per-stage counts arrive as span attributes and the reconciliation is exact. Where it is not, counts have to be read from the tables themselves, which works but ties you to what the current state happens to be.

Backfills and the idempotency assumption

The most common way a modern pipeline produces wrong numbers while everything reports success.

Every backfill rests on an assumption: that re-running a task for a past interval produces the same result as running it the first time. When the model is a pure transformation of an immutable source, that holds. It stops holding the moment the source is mutable, the load uses INSERT rather than a merge on a key, or the task derives its window from wall clock time rather than the logical date.

The failure is quiet and it compounds. A backfill overlapping a scheduled run puts two writers on the same partition; both succeed; the partition now holds roughly twice the rows for part of its range. Nothing errors, no test on uniqueness fires unless there is a genuine key constraint, and the discrepancy surfaces weeks later in a report that nobody can reconcile.

The tell is a partition whose row count is anomalous in the upward direction, which is why a reconciliation should flag deviation in both directions. Loss is what people look for; duplication is what they find later.

A worked sequence

Same method as every other stack: establish an interval before widening the search.

Find the first run that was wrong, not the first that failed

These are almost never the same run. Work from row counts per partition, because task state cannot distinguish a load that wrote 400,000 rows from one that wrote zero — both are success.

Partitions that should exist, and what is actually there

-- The reconciliation the DAG cannot do: rows per partition, against the
-- partitions that were supposed to be written. Absent partitions are the
-- finding — and they are invisible to any query that only reads what exists.
WITH expected AS (
    SELECT generate_series(
        date_trunc('day', now() - interval '14 days'),
        date_trunc('day', now()),
        interval '1 day'
    )::date AS load_date
),
actual AS (
    SELECT load_date, count(*) AS rows
    FROM analytics.fct_transactions
    WHERE load_date >= now() - interval '14 days'
    GROUP BY load_date
)
SELECT
    e.load_date,
    coalesce(a.rows, 0) AS rows,
    CASE WHEN a.load_date IS NULL THEN 'MISSING PARTITION' END AS flag
FROM expected AS e
LEFT JOIN actual AS a USING (load_date)
ORDER BY e.load_date;

Check duration against the task's own history

This is the one genuinely strong signal the metadata database holds. A task whose median runtime is four minutes and which completed in twenty seconds did not do its job quickly — it did nothing successfully.

Duration against a 30-run trailing average

-- The useful question the metadata database *can* answer: did a task that
-- succeeded take a suspiciously different amount of time than usual?
-- A load that normally runs 4 minutes and finished in 20 seconds succeeded
-- at doing nothing.
SELECT
    ti.task_id,
    ti.run_id,
    EXTRACT(epoch FROM (ti.end_date - ti.start_date))       AS duration_s,
    AVG(EXTRACT(epoch FROM (ti.end_date - ti.start_date)))
        OVER (PARTITION BY ti.task_id
              ORDER BY ti.start_date
              ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)     AS trailing_avg_s
FROM task_instance AS ti
WHERE ti.dag_id = 'load_transactions'
  AND ti.state = 'success'
  AND ti.start_date >= now() - interval '30 days'
ORDER BY ti.start_date DESC;

Read task state around the window

Now that you have a bounded interval, look at what else happened in it: retries, upstream skips, sensors that soft-failed, and any task whose try_number is above one. A retry after a partial write is the usual explanation for a duplicated partition.

Task instances in the window

-- Airflow's metadata database is the scheduler's own record. Task state
-- tells you whether the process exited zero — nothing more.
SELECT
    ti.dag_id,
    ti.task_id,
    ti.run_id,
    ti.state,
    ti.start_date,
    ti.end_date,
    EXTRACT(epoch FROM (ti.end_date - ti.start_date)) AS duration_s,
    ti.try_number,
    ti.hostname
FROM task_instance AS ti
WHERE ti.dag_id = 'load_transactions'
  AND ti.start_date >= now() - interval '3 days'
ORDER BY ti.start_date DESC, ti.task_id;

4
Check what changed outside the repository

Connections, variables, pool sizes and paused state. This is where modern stacks fail in the same way legacy ones do — the change that caused the incident was made in a UI and left no diff. Compare the metadata database against the last known-good run rather than against the repository.
5
Only now, correlate with commits

With an interval established, the DAG file and dbt model history become useful. Starting here instead produces a plausible commit and a wrong answer, because on any active project there is always a plausible commit.
Bound the search to the interval you proved
```
git log --since="2026-08-05 01:50" --until="2026-08-05 02:15" \
       -- dags/ models/ --name-only --pretty=format:"%h %an %ad %s"
```

Common questions

We use Airflow properly. Is there really a gap?

The gap is not a maturity problem — it is structural. Airflow is a scheduler, and a scheduler's job is to record whether a process exited zero. It has no opinion about how many rows that process should have written, because it cannot have one. Everything on this page follows from that single fact, and it applies equally to Dagster, Prefect and Step Functions.

Doesn't OpenLineage solve this?

It helps considerably, where it is emitted. OpenLineage gives you input and output datasets per run, which turns lineage from inference into record. What it does not give you is the reconciliation — knowing that a task wrote to a table is different from knowing it wrote the right number of rows — and coverage stops at the edge of the instrumented stack, which is usually where the awkward integrations begin.

How is this different from Monte Carlo or Bigeye?

Those are detection tools, and good ones: they learn a table's normal behaviour and alert when it deviates. That answers is something wrong. Decim starts after that alert fires and answers why, which needs evidence from outside the warehouse — scheduler metadata, connection history, source code, the reject path. They are complementary, and running both is a reasonable position.

Do you need access to our Airflow metadata database?

Read-only, and it is the highest-value source for this stack because it holds the connections, variables, pools and manual state changes that the repository does not. If you prefer, the REST API covers most of it at the cost of some history. The agent runs inside your network either way.

What about dbt Cloud rather than dbt Core?

Run results and manifest artefacts are the useful surface, and both are available. run_results.json gives per-model status and timing; manifest.json gives the model graph and test definitions. Together they answer which models ran, which tests were defined, and — often more usefully — which were not.

Our pipeline is half Airflow and half a legacy SQL Agent job.

That is the common case rather than an edge case, and it is the reason topology is assembled from several sources rather than one. See SQL Server & SSIS for the other half, and pipeline topology for how a graph spans both.

Related: SQL Server & SSIS and custom ETL services for the stacks a modern platform usually sits alongside; investigations for the method; and all solutions by stack, role and failure mode.

Get started

Bring us a pipeline that broke last week

The fastest way to evaluate this is a real incident you already know the answer to. If Decim gets it wrong, that is a far more useful demo than one where it doesn't.

Request a demo Read the docs