Ingest a REST API into DuckDB with dlt

Turn any REST API into a queryable DuckDB dataset with about twenty lines of Python. This blueprint runs an inline dlt pipeline through io.kestra.plugin.dlt.Run: two @dlt.resource functions pull users and todos from a REST API, and dlt handles everything ingestion scripts usually get wrong, schema inference, typing, normalization of nested JSON, and merge semantics on primary keys so reruns update rows instead of duplicating them. After each load, the flow surfaces per-table row counts as Kestra outputs and posts them to Slack.

How it works

1. load_rest_api (io.kestra.plugin.dlt.Run) executes the inline Python script in the task's container image. Each @dlt.resource declares a primary_key and write_disposition: merge, so the pipeline is rerun-safe by construction.
2. dlt.pipeline(...) targets the duckdb destination with a raw dataset name; dlt creates tables, infers the schema, and versions it.
3. After the run, the script reads pipeline.last_trace.last_normalize_info.row_counts, filters out dlt's internal tables, and emits the counts through Kestra's output protocol, making them available as {{ outputs.load_rest_api.vars.row_counts }}.
4. notify posts the per-table counts to Slack; the errors block posts a distinct alert when the extract or load fails.
5. A disabled-by-default Schedule trigger refreshes the dataset daily at 06:00.

What you get

• Rerun-safe ingestion: merge on primary key means executing twice never duplicates a row.
• Schema inference and evolution handled by dlt, including nested JSON flattened into child tables.
• Row counts as first-class flow outputs, ready for downstream tasks or alerting thresholds.
• A pattern that swaps to any REST API by editing the resource functions, and to any dlt destination by changing one argument.

Who it's for

• Data engineers replacing hand-rolled requests-plus-pandas ingestion scripts that break on schema drift.
• Analytics teams who want API data queryable in DuckDB without standing up a warehouse.
• Anyone evaluating dlt who wants a working, scheduled, observable pipeline in one copy-paste.

Why orchestrate this with Kestra

dlt solves extraction and loading, but a Python script still needs somewhere to run on schedule, retry on transient API failures, keep an execution history, and tell someone when it breaks. Kestra provides exactly that around the unchanged dlt code: the Run task ships with a container image that has dlt preinstalled, outputs flow into notifications and downstream tasks, and every load is auditable in the execution history.

Prerequisites

• A persistent path for the DuckDB file (the warehouse_path input), so merges see previous loads.
• A Slack incoming webhook for load reports.
• No API credentials are needed for the demo endpoints; for authenticated APIs, pass tokens through task env from {{ secret('...') }}.

Secrets

• SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

1. Add the SLACK_WEBHOOK_URL secret to your Kestra namespace.
2. Execute the flow and check the Slack message lists row counts for users and todos.
3. Point the resource functions at your real API and set disabled: false on the daily trigger.

How to extend

• Swap the destination to postgres, bigquery, or snowflake by changing the destination argument and providing credentials through task env variables.
• Add incremental loading with dlt.sources.incremental for endpoints that expose an updated-at cursor, as shown in the dlt incremental sync blueprint.
• Chain analytics on the loaded data with io.kestra.plugin.jdbc.duckdb.Query, as shown in the dlt analytics chain blueprint.
• Enforce schema stability with dlt schema contracts, as shown in the schema contract guard blueprint.

Links

• dlt plugin documentation
• dlt documentation