Build a Python pipeline to load JSON API data into DuckDB using dlt

Load JSON data from a REST API straight into a DuckDB database using dlt (data load tool), orchestrated by Kestra in a containerized Python task. This blueprint solves a common ELT problem: you have a JSON API and you want analytics-ready tables without hand-writing schema definitions, CREATE TABLE statements, or type-mapping code. dlt infers the schema, normalizes nested JSON, and creates the tables for you, while Kestra handles scheduling, retries, container isolation, and output capture.

How it works

A single task, dlt_pipeline (io.kestra.plugin.scripts.python.Script), runs an inline Python script inside a Docker container. The task uses the io.kestra.plugin.scripts.runner.docker.Docker runner with the python:3.11-slim image and installs dlt[duckdb] and requests via the dependencies property at runtime.

The script:

1. Fetches records from https://dummyjson.com/products with requests and calls raise_for_status() so HTTP errors fail the task.
2. Extracts the products array from the JSON payload.
3. Creates a dlt pipeline (dummyjson_products_pipeline) with destination='duckdb' and dataset_name='products'.
4. Calls pipeline.run(data, table_name='product'), letting dlt infer the schema, normalize the JSON, and write the product table.

The outputFiles property captures dummy_products.duckdb as a Kestra output, so the resulting database file is downloadable from the execution.

What you get

• A queryable DuckDB file produced on every run, with no manual DDL.
• Automatic schema inference and JSON normalization handled by dlt.
• A reproducible, isolated Python environment via Docker.
• The database artifact captured as a Kestra output for download or downstream tasks.

Who it's for

• Data engineers prototyping ELT pipelines before scaling to a warehouse.
• Analysts who want REST API data in DuckDB for local exploration.
• Teams evaluating dlt as an ingestion layer inside an orchestrator.

Why orchestrate this with Kestra

dlt is an excellent loading library, but it has no scheduler, no retry engine, and no execution history of its own. Kestra adds declarative YAML pipelines, event and schedule triggers, automatic retries on transient API failures, full execution lineage and logs, and container-based isolation so dependencies never collide. You get the dlt loading experience with production-grade orchestration around it, something the library alone cannot provide.

Prerequisites

• A Kestra instance with the Docker task runner available.
• Network access from the worker to the target API.

Secrets

This blueprint targets a public API and writes to a local DuckDB file, so no secrets are required. To pull from an authenticated API, add a secret such as API_TOKEN and reference it with {{ secret('API_TOKEN') }}.

Quick start

1. Add this flow to a namespace on your Kestra instance.
2. Confirm the Docker task runner can pull python:3.11-slim.
3. Execute the flow.
4. Download dummy_products.duckdb from the execution outputs and query the product table.

How to extend

• Point the script at your own JSON API and adjust the extracted key and table_name.
• Add a Schedule trigger to run ingestion on a cron, or a webhook trigger for event-driven loads.
• Swap the dlt destination to Postgres, BigQuery, or Snowflake for a production warehouse.
• Chain a downstream DuckDB or dbt task to transform the loaded tables.

Links

• https://kestra.io/plugins/plugin-script-python
• https://kestra.io/orchestration/duckdb