One CDC pattern, two builds: what changes between Airflow and Dagster

In August I built the same change data capture pipeline twice. Both builds read a public API into PostgreSQL, stream every change out of the write-ahead log with Debezium into Redpanda, land the events in ClickHouse and build dbt models on top. The World Bank build is orchestrated by Airflow. The Kiva build is orchestrated by Dagster.

I expected the orchestrator to be the big difference between them. Reading the two side by side, it was not. The biggest differences in whether the numbers could be trusted came from four choices that have nothing to do with Airflow or Dagster, and in all four the Kiva build had taken a shortcut that the World Bank build avoided. I have since fixed all four. This essay walks through each one, with the code before and after, because the comparison taught me more than either build did alone.

1. Kiva public APIFunded loans, no key
2. Python ingestionRetry with backoff, writes only changed loans
3. PostgreSQL 16Logical replication for change capture
4. Debezium 2.5Write-ahead log through pgoutput, plus a heartbeat
5. RedpandaKafka-compatible event log
6. ClickHouseReplacingMergeTree versioned by LSN
7. dbtStaging, intermediate and marts
8. GrafanaLoan analytics over the marts

Orchestration, across every stage

Dagster: ingestion, a blocking parity check, then dbt build; GitHub Actions CI

Observability, across every stage

Prometheus and Grafana; cdc-monitor: row drift, heartbeat lag, connector state; alert email through Mailpit

The orchestrator is not in the CDC path

Debezium runs all the time. No scheduler starts it and no task "runs CDC": a change committed in PostgreSQL reaches ClickHouse in seconds whether or not a pipeline run is in progress.

What an orchestrator owns are the two batch edges on either side of the stream. One pulls from the API into PostgreSQL. The other builds the dbt models once the rows have arrived in ClickHouse. The World Bank DAG says so in its own documentation: there is no CDC task, because a DAG cannot orchestrate a stream without pretending it is a batch.

That changes what the choice of tool can decide. Swapping Airflow for Dagster changes how the edges are wired, not how the data moves. So most of what makes a CDC pipeline correct never touches the orchestrator.

Four shortcuts, and what fixing each one took

A re-run should change nothing

Both builds poll their API on a schedule and upsert on the business key. The question is what happens to a row that has not changed. The World Bank upsert writes only when a hash of the source row differs. The Kiva upsert updated every loan it received and stamped the time of the run, so every run sent every loan through the stream again as an update that changed nothing. The change stream described the scheduler instead of the data.

The fix is one clause:

INSERT INTO raw_data.kiva_loans AS t (id, name, status, funded_amount, ...)
VALUES (...)
ON CONFLICT (id) DO UPDATE SET
    status = EXCLUDED.status,
    funded_amount = EXCLUDED.funded_amount,
    updated_at = CURRENT_TIMESTAMP
WHERE t.status IS DISTINCT FROM EXCLUDED.status
   OR t.funded_amount IS DISTINCT FROM EXCLUDED.funded_amount;

Now a run over unchanged data writes nothing. On the first runs after the change, most pages reported no new or changed loans, and ClickHouse held exactly one version of each loan.

That fix exposed a second shortcut hiding behind the first. The Kiva monitor measured lag from the newest replicated updated_at, and that number only kept moving because every run rewrote every row. With re-runs free, a quiet afternoon would have looked like a stalled pipeline. Lag needed its own clock, so the Kiva build now has the heartbeat the World Bank build already had: a one-row table that Debezium updates every ten seconds, which flows through CDC like any other change. Lag is the age of the newest beat in ClickHouse, and it grows only when something has actually stopped.

The log should decide which version wins

ClickHouse keeps one row per key in a ReplacingMergeTree table, and the version column decides which. The World Bank build used the log position of each change, the LSN. The Kiva build used the moment ClickHouse received the event:

toUInt64(now()) AS _version

That holds while every event arrives once and in order, and goes wrong the first time an old event is delivered again after a restart, or when two changes to one loan land within the same second. The fix is small: ask Debezium to add the log position to every event ("transforms.unwrap.add.fields": "op,ts_ms,lsn"), read it as a column in the Kafka engine table, and use it as the version:

__lsn AS _version

Now the order of commits in PostgreSQL decides, whatever order the events arrive in. It is the same rule as in the first essay in this series.

Wait on evidence, not on time

dbt must not build on a warehouse that has received only part of what was just written. Its tests would pass, because a partial copy is internally consistent. The World Bank DAG has a gate that polls until ClickHouse holds what PostgreSQL holds, with a deadline. The Kiva asset waited a fixed three seconds and hoped:

time.sleep(3)  # "to ensure Debezium CDC and Redpanda have fully streamed the records"

On a quiet laptop three seconds is plenty. On a busy machine, or after the connector restarts, it is not, and dbt builds on whatever has arrived. The fix is a blocking asset check between ingestion and dbt. It polls until ClickHouse holds exactly the live loans PostgreSQL holds and the newest change has arrived, because counts alone miss an update:

@asset_check(asset=ingest_kiva_api_to_postgres, blocking=True)
def cdc_caught_up() -> AssetCheckResult:
    reached, source, target = wait_for_parity(read_postgres, read_clickhouse, timeout_seconds=120)
    return AssetCheckResult(passed=reached, metadata={"postgres_rows": source.rows, "clickhouse_rows": target.rows})

Blocking means dbt does not run when the check fails. I tested it the way the first essay recommends, by breaking the pipeline on purpose: pause the connector, change a loan, run the job. The check waited out its deadline and failed, dbt never ran, and the monitor's lag passed the alert threshold. After resuming, every change the replication slot had held arrived, and the next run passed.

Test each layer before building the next

The World Bank DAG runs dbt build, which tests each model before building anything on top of it. The Kiva asset ran dbt run and then dbt test, so everything was built first and problems were reported after they had reached the marts. The fix is one word in a command line.

The same drill showed why it matters. The loan I changed while the connector was paused got a status the staging tests do not accept. dbt build failed that test and skipped everything downstream of it, so no mart was built on the bad value. With dbt run first, the marts would have been rebuilt and the test would have complained afterwards.

One more: pin what you install

Rebuilding the stack from a fresh clone turned up a fifth problem that neither comparison would have found. The repository asked for any dbt-clickhouse release from 1.7 up, and the newest one sends a setting the pinned ClickHouse server does not know, so dbt could not connect at all. The requirement now has a ceiling. A pipeline that promises to start with one command has to pin what it installs, or the promise expires on someone else's release day.

Where the tools did differ

Making a failed run look failed

The World Bank DAG ends in a join task with the trigger rule all_done, so every run finishes cleanly. But all_done also succeeds when something upstream failed, so on its own the run would read green after a failure. Airflow needs one more task to keep the run honest:

end = EmptyOperator(task_id="end", trigger_rule=TriggerRule.ALL_DONE)
watcher = BashOperator(task_id="watcher", bash_command="exit 1", trigger_rule=TriggerRule.ONE_FAILED)
for task in dag.tasks:
    if task.task_id not in ("watcher", "end"):
        task >> watcher

The watcher can only run when something failed, and it fails on purpose, so the run's state tells the truth. In Dagster there is nothing to add: when an asset or a blocking check fails, the run fails. That is a real difference between the tools, and it cuts in Dagster's favour.

Watching between runs

A scheduled run notices a dead connector only while it is running. Both builds watch the stream from outside the schedule, in different ways.

The World Bank build adds a second, cheap DAG that runs every five minutes and only checks heartbeat lag and the health of the replication slot. Prometheus alert rules cover the same ground, on purpose: they read the same thresholds from the same environment variables as the gates, so the DAG and the alert cannot disagree about what too much lag means, and every rule has a runbook and a promtool test in CI.

The Kiva build keeps monitoring out of the orchestrator entirely. A small exporter compares row counts on both sides, measures heartbeat lag and asks the Kafka Connect API whether the connector and its tasks are running, and Grafana alert rules, provisioned from files, email an alert when any of them goes wrong.

Checks inside the orchestrator are easy to run and easy to see. Checks outside it keep working when the orchestrator itself is down. The World Bank build has both, which is why it overlaps on purpose.

What each tool made easier

Airflow made the gates first-class. Each task has its own retries and timeout, so ingestion retries a flaky public API while a gate never retries, because a gate failing is a real condition and retrying only delays the alert. The runbook sits on the DAG's page in the Airflow UI, where the person debugging is already looking.

Dagster made the pipeline read as data. Two assets with one dependency say what exists and in what order, a blocking check sits exactly where the wait belongs, and a failure fails the run without help.

If I were choosing for a team

I would choose the orchestrator the team can run well: the one they already know, or the one with a managed service they trust. Then I would hold it to the same list, because the list is what the two builds actually disagreed on.

1. Make re-runs free. Write a row only when it changed, and give lag its own clock.
2. Version by log position, never by arrival.
3. Wait on evidence, not on time, before building on replicated data.
4. Test each layer before building the next.
5. Make a failed run look failed.
6. Watch the stream from outside the schedule, and break it on purpose to prove the alarm works.
7. Pin what you install.

None of these depends on Airflow or Dagster. All of them answer the same question from a different side: how would we know if these numbers were wrong?

Try it

Both pipelines run on a laptop with one command. The World Bank repository holds the gates, the watcher, the liveness DAG and the tested alert rules. The Kiva repository holds the Dagster assets, the blocking check, the heartbeat and the Grafana alerting, and its README explains each fix. The home page compares the two builds stage by stage, and CDC Starter is the smallest version of the pattern, with every check from the first essay.