Data Engineering Pipeline example, Getting started with Kestra

A compact, end-to-end ETL (extract, transform, load) pipeline that demonstrates the core data engineering pattern in Kestra: pull raw JSON from a public API, reshape it in Python, then run analytical SQL on the cleaned data with DuckDB. It solves the classic glue problem of stitching together an HTTP download, a Python transform, and a SQL aggregation as one reproducible, observable workflow instead of three disconnected scripts. This is an ideal first flow for anyone learning how Kestra passes files between tasks and orchestrates a polyglot data pipeline.

How it works

1. extract uses io.kestra.plugin.core.http.Download to fetch the raw products JSON from a public API endpoint and store it in Kestra internal storage.
2. transform runs io.kestra.plugin.scripts.python.Script on a python:3.11-alpine container. It receives the downloaded file through inputFiles, keeps only the columns requested in the columns_to_keep input, and writes a cleaned products.json via outputFiles.
3. query runs io.kestra.plugin.jdbc.duckdb.Queries, loads the DuckDB json extension, reads the cleaned file with read_json_auto, and computes the average price per brand. With fetchType: STORE, the result is persisted as a Kestra output you can preview and download in the UI.

What you get

• A working three-stage extract, transform, load pipeline you can run in one click.
• File hand-off between tasks via inputFiles and outputFiles, no manual paths.
• A configurable columns_to_keep array input to control which fields survive the transform.
• Aggregated results (average price per brand) stored as a downloadable output.

Who it's for

• Data engineers prototyping ingestion and transformation logic.
• Analytics engineers who want lightweight SQL aggregation without a warehouse.
• Developers new to Kestra learning how tasks share data.

Why orchestrate this with Kestra

DuckDB is a powerful in-process analytical engine, but it has no scheduler, no retry logic, and no way to coordinate an upstream API call or a Python step. Kestra wraps the whole pipeline in declarative YAML: add event or schedule triggers, set automatic retries on the flaky network download, capture inputs and outputs for full data lineage, and observe every run in one place. The Python and SQL stages stay focused on logic while Kestra handles dependencies, file passing, and execution state.

Prerequisites

• A running Kestra instance.
• Outbound network access so the extract task can reach the public API.
• Docker available for the Python script task runner.

Secrets

This flow uses public data and requires no secrets. To adapt it to an authenticated source, add credentials with {{ secret('YOUR_SECRET_NAME') }} and configure them in your Kestra secret backend.

Quick start

1. Add this flow to your Kestra instance.
2. (Optional) Adjust the columns_to_keep input to select different fields.
3. Execute the flow from the UI.
4. Open the query task output to preview and download the average price per brand.

How to extend

• Swap the public API URL for your own source, or parameterize it as an input.
• Add an event or schedule trigger to run the pipeline automatically.
• Replace the DuckDB step with a warehouse plugin (Snowflake, BigQuery, Postgres) to load results downstream.
• Add a notification task to post the aggregated metrics to Slack.

Links

• Plugin documentation
• DuckDB orchestration with Kestra