Incremental SQL Database Sync to DuckDB with dlt

Stop re-copying whole tables on every sync. This blueprint uses dlt's sql_table source with an incremental cursor: each run reads only rows whose updated_at advanced past the last stored cursor position, merges them into the destination on the table's primary key, and reports the delta size to Slack. The cursor state lives in the destination itself, dlt's _dlt_pipeline_state table, so the flow is stateless from Kestra's point of view: reruns, retries, and even a fresh Kestra instance resume from the right position.

How it works

1. sync_table (io.kestra.plugin.dlt.Run) installs the sql-database extra plus common drivers in beforeCommands, then builds a sql_table source from the SOURCE_DB_URL secret passed as an environment variable.
2. dlt.sources.incremental("updated_at") tells dlt to track the highest cursor value seen and to filter the next extraction with it, server-side, as a WHERE clause.
3. write_disposition: merge deduplicates on the table's primary key, so a row updated twice between runs lands once, with the latest values.
4. The script emits the per-run row delta through Kestra's output protocol; notify posts it to Slack, and the errors block explains that a failed run never advances the cursor, so nothing is skipped.
5. A disabled-by-default hourly Schedule covers business days; the flow's table_name and cursor_column inputs make one flow serve many tables.

What you get

• Sync cost proportional to change volume, not table size.
• Exactly-once-per-row semantics in the destination through primary-key merge.
• Crash safety for free: the cursor advances only on successful loads.
• One parameterized flow for every table that has a usable timestamp cursor.

Who it's for

• Data engineers replicating operational Postgres or MySQL tables into an analytical store without CDC infrastructure.
• Teams whose nightly full-table copies stopped scaling.
• Anyone who wants incremental loading semantics without writing watermark bookkeeping by hand.

Why orchestrate this with Kestra

Incremental sync is a scheduling problem as much as a data problem: something must run the pipeline on cadence, retry transient database failures, parameterize it per table, and alert when the sync stalls. Kestra adds all of that around dlt's unchanged incremental logic, with per-table executions in the history showing exactly how many rows each run moved and when.

Prerequisites

• A persistent path for the DuckDB destination (the warehouse_path input); the incremental cursor is stored there.
• A source database reachable from Kestra workers (Postgres, MySQL, or anything SQLAlchemy supports).
• A source table with a primary key and a timestamp column that updates on change.
• A Slack incoming webhook.

Secrets

• SOURCE_DB_URL: SQLAlchemy connection URL, e.g. postgresql://user:pass@host:5432/db.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

1. Add the two secrets above to your Kestra namespace.
2. Execute with your table and cursor column as inputs; the first run backfills everything, which is expected.
3. Execute again and confirm the Slack message reports a small or zero delta.
4. Set disabled: false on the hourly trigger.

How to extend

• Sync several tables by wrapping this flow in a ForEach over a table list, or use dlt's sql_database() source to reflect a whole schema at once.
• Swap the destination to bigquery, snowflake, or postgres with credentials in task env.
• Add a data quality gate after the sync, failing the flow when the delta is unexpectedly large.
• For sources without a timestamp column, see dlt's CDC options or the Debezium plugins.

Links

• dlt plugin documentation
• dlt sql_database source