Webhook-Driven CSV Ingestion into DuckDB with dlt

Replace the cron job that polls a drop folder with ingestion that runs the moment files arrive. An upstream system, an SFTP hook, an application export, or a vendor delivery, calls the flow's webhook after finishing a drop. dlt's filesystem source then globs the location (local mount, S3, GCS, or anything fsspec speaks), streams every matching CSV through read_csv, and appends the rows into DuckDB. dlt records which file every row came from, so bad deliveries can be traced and surgically deleted later.

How it works

1. on_files_announced (io.kestra.plugin.core.trigger.Webhook) exposes a URL keyed by a secret string; the upstream system calls it once a drop is complete, which avoids the classic half-written-file race that folder polling suffers from.
2. load_files (io.kestra.plugin.dlt.Run) builds a filesystem source from the source_uri and file_glob inputs and pipes it through read_csv, so parsing happens lazily, file by file.
3. write_disposition: append fits the immutable-drop model: every delivery adds rows, and dlt's _dlt_load_id plus file metadata give each row lineage.
4. The task emits per-table row counts as outputs; notify posts them to Discord, and the errors block flags the drop location when a malformed file breaks parsing.

What you get

• Event-driven ingestion with no polling schedule to tune and no half-file races.
• One flow for local mounts and object stores alike, since fsspec URIs abstract the storage.
• Row-level lineage back to the source file for audits and selective reloads.
• Inputs for location and glob, so the same flow serves multiple vendors or feeds.

Who it's for

• Teams receiving recurring CSV deliveries from vendors, partners, or legacy exports.
• Data engineers replacing fragile watch-folder scripts with observable, retryable ingestion.
• Anyone standardizing file landing zones on S3 or GCS with dlt as the parser.

Why orchestrate this with Kestra

File ingestion fails in operational ways: a truncated file, an unexpected delimiter, a vendor renaming columns. Kestra gives the dlt source a webhook front door, retries, per-delivery execution history with the exact inputs used, and failure alerts that name the drop location. The upstream system needs nothing more than one HTTP call to hand off responsibility.

Prerequisites

• A persistent path for the DuckDB landing file (the warehouse_path input).
• A file drop location reachable from Kestra workers (mounted path or object store credentials via task env).
• An upstream system able to call a webhook URL after completing its drop.
• A Discord incoming webhook for reports.

Secrets

• DISCORD_WEBHOOK_URL: Discord incoming webhook URL.

Quick start

1. Add the DISCORD_WEBHOOK_URL secret and replace the webhook trigger key with a strong random string.
2. Drop a couple of CSVs into the location and execute the flow manually with matching inputs.
3. Confirm the Discord message reports the loaded row count, then wire the upstream system to the webhook URL.

How to extend

• Point source_uri at s3:// or gs:// buckets, passing storage credentials through task env.
• Switch to read_parquet or read_jsonl for other formats; the pipeline shape stays identical.
• Add a post-load quality gate that fails the flow when row counts are zero, catching empty deliveries.
• Follow with the dlt analytics chain blueprint to aggregate the landing tables after each load.

Links

• dlt plugin documentation
• dlt filesystem source