Build an ETL Pipeline from a REST API Using Python, Polars, and DuckDB

Build a lightweight ETL and ELT pipeline that pulls JSON from a REST API, transforms it with Python and Polars, and runs analytical SQL with DuckDB, without standing up a warehouse, a Spark cluster, or any external infrastructure. This blueprint solves the everyday data engineering problem of turning a raw API response into a clean, queryable dataset using a single declarative YAML workflow that is reproducible, observable, and scheduled.

How it works

1. The api task (io.kestra.plugin.core.http.Request) calls a REST endpoint (the DummyJSON products API by default) and returns the JSON body.
2. The python task (io.kestra.plugin.scripts.python.Script) runs on a Docker taskRunner (io.kestra.plugin.scripts.runner.docker.Docker) with the python:slim image and a Polars dependency. It parses the API body with a jq expression, builds a Polars DataFrame, selects the brand and price columns, and writes products.csv as an outputFile.
3. The sql_query task (io.kestra.plugin.jdbc.duckdb.Query) mounts that CSV as an inputFile, runs read_csv_auto over it, aggregates average price per brand with GROUP BY and ORDER BY, and uses fetchType: STORE to persist the result set.

What you get

• A working extract, transform, load pattern wired end to end.
• Fast, in-memory transformation with Polars inside an isolated container.
• Analytical SQL on flat files with DuckDB, no database server required.
• Stored, previewable query output you can inspect in the Kestra UI or pass downstream.

Who it's for

• Data engineers prototyping ingestion and transformation logic.
• Analytics engineers who want SQL over API data without a warehouse.
• Python developers building API-driven analytics and ad-hoc ETL jobs.

Why orchestrate this with Kestra

DuckDB and Polars are excellent engines but they have no scheduler, no retry logic, and no way to chain an HTTP call to a Python step to a SQL step. Kestra fills that gap: event and schedule triggers fire the flow automatically, per-task retries handle flaky API calls, passing outputFiles between tasks gives you clear data lineage, and the whole pipeline lives in version-controlled, declarative YAML instead of glue scripts.

Prerequisites

• A running Kestra instance.
• Docker available to the worker for the Python task runner.

Secrets

This blueprint calls a public API and uses no secrets. If you point it at a private API, add a secret such as {{ secret('API_TOKEN') }} and pass it as an authorization header on the api task.

Quick start

1. Add this blueprint to your namespace.
2. Execute it as is to fetch the sample products feed.
3. Open the sql_query task output to see average price per brand.
4. Swap in your own API endpoint and adjust the columns and SQL.

How to extend

• Replace the endpoint with your own REST API and add auth headers.
• Expand the Polars logic for joins, type casting, or filtering.
• Rewrite the DuckDB SQL for windowing, joins, or richer aggregations.
• Add a schedule or webhook trigger and downstream load tasks for a full ELT flow.

Links

• Plugin: HTTP / core
• Plugin: Python scripts
• Plugin: DuckDB JDBC
• DuckDB orchestration