Run a scheduled ELT pipeline in Python: extract Chess.com API data and load it into DuckDB with dltHub

Build a lightweight ELT pipeline in Python that pulls player profiles from the Chess.com public REST API and loads them into DuckDB using dltHub (dlt), all orchestrated by Kestra. This blueprint solves a common ingestion problem: extracting semi-structured JSON from a web API, normalizing nested fields into clean relational tables, and landing them in a fast analytical store without hand-writing schema or transformation code. It is a ready-to-run pattern for API-to-warehouse ELT, prototyping, and local analytics.

How it works

• A single chess_api_to_duckdb task of type io.kestra.plugin.scripts.python.Script runs the pipeline inside a python:slim container using the io.kestra.plugin.scripts.runner.docker.Docker task runner.
• The task declares its dependencies (dlt[duckdb] and requests), so the runtime installs dlt and the DuckDB extra automatically.
• The Python code initializes dlt.pipeline() with pipeline_name='chess_pipeline', destination='duckdb', and dataset_name='player_data'.
• It loops over a list of players (magnuscarlsen, rpragchess), calls the Chess.com endpoint with requests.get(), and raises on HTTP errors via response.raise_for_status().
• It calls pipeline.run(data, table_name='player'), where dlt infers the schema, normalizes the JSON, and loads records into the player table in DuckDB.
• A daily trigger of type io.kestra.plugin.core.trigger.Schedule is included with cron: "0 9 * * *", shipped disabled: true so you can enable it when ready.

What you get

• Automatic schema inference and JSON normalization handled by dlt.
• A portable DuckDB dataset (player_data) ready for fast SQL querying.
• A containerized, reproducible Python environment with pinned dependencies.
• A built-in daily schedule you can flip on with one property change.

Who it's for

• Data engineers building API-to-warehouse ELT pipelines.
• Analytics engineers and analysts who want queryable data without manual transforms.
• Developers prototyping dlt and DuckDB workflows before scaling out.

Why orchestrate this with Kestra

dlt and DuckDB have no native scheduler, retry engine, or cross-pipeline lineage. Kestra adds event and schedule triggers, automatic retries, execution history, and observability around the run. You declare the whole pipeline in version-controlled YAML, run it in an isolated Docker container, and chain it with downstream tasks (dbt, notifications, loads to other systems) that a standalone Python script cannot coordinate on its own.

Prerequisites

• A running Kestra instance with Docker available for the task runner.
• Outbound network access to api.chess.com.

Secrets

• None. The Chess.com public API requires no authentication, so this flow references no secret() values.

Quick start

1. Add this blueprint to your Kestra instance.
2. Execute the flow manually to confirm the DuckDB load succeeds.
3. Inspect the logs to see dlt normalize the JSON into the player table.
4. Set the daily trigger disabled: false to run it every day at 09:00.

How to extend

• Add or parameterize the player list with a Kestra input for dynamic ingestion.
• Swap the DuckDB destination for Postgres, BigQuery, or Snowflake in dlt.pipeline().
• Pull additional Chess.com endpoints (games, stats) into more tables.
• Add a downstream dbt task to model the loaded data.

Links

• Python script plugin docs
• DuckDB orchestration with Kestra