Extract multiple tables from Postgres using SQL queries and process those

Pull multiple tables out of a Postgres database in parallel, hand the raw CSV exports to a Python task, and turn them into an analytics-ready bestsellers report with Pandas. This blueprint solves a common ETL problem: your transactional data lives in Postgres, but the joins, aggregations, and ranking you need for reporting are easier (and faster) to express in Pandas than in raw SQL, and you want the whole thing to run unattended every morning.

How it works

1. A get_tables step of type io.kestra.plugin.core.flow.Parallel runs two extractions at once, capped at concurrent: 2.
2. Inside it, two io.kestra.plugin.jdbc.postgresql.CopyOut tasks (products and orders) stream SELECT * FROM products and SELECT * FROM orders straight to CSV files in Kestra's internal storage.
3. A pandas task of type io.kestra.plugin.scripts.python.Script mounts both outputs through inputFiles ({{ outputs.products.uri }} and {{ outputs.orders.uri }}) and runs on a Docker taskRunner with the pandas dependency installed.
4. The script reads both CSVs, merges orders with products on product_id, groups by product_name, sums total, sorts descending, and writes the top 10 to bestsellers_pandas.json via outputFiles.
5. A Schedule trigger (every_morning, cron 0 9 * * *) runs the pipeline daily at 09:00.

What you get

• A daily top-10 bestsellers report as a JSON artifact in Kestra storage.
• Parallel table extraction that shortens total runtime.
• Reproducible transforms isolated in a Docker container.
• Connection details and credentials centralized in pluginDefaults.

Who it's for

• Data engineers building scheduled Postgres-to-analytics pipelines.
• Analytics teams who prefer Pandas over complex SQL for reshaping data.
• Platform teams standardizing extract-and-transform jobs on Kestra.

Why orchestrate this with Kestra

Postgres can schedule jobs with pg_cron, but it cannot orchestrate work that leaves the database. Kestra ties the SQL extraction, the Python transform, and the daily schedule into one declarative YAML flow with built-in retries, parallelism, output passing between tasks, and full execution lineage. The Docker task runner keeps Python dependencies reproducible without touching the database server.

Prerequisites

• A reachable Postgres instance (the flow defaults to jdbc:postgresql://{{ vars.db_host }}:5432/ with user postgres).
• products and orders tables sharing a product_id column, plus a total column on orders.
• A Docker-enabled worker for the Python task runner.

Secrets

• DB_PASSWORD: the password for the Postgres postgres user, referenced in pluginDefaults as {{ secret('DB_PASSWORD') }}.

Quick start

1. Add the flow to a namespace and set the DB_PASSWORD secret.
2. Adjust the db_host variable to point at your database.
3. Run the flow manually to confirm both CopyOut tasks and the Pandas script succeed.
4. Leave the every_morning schedule enabled for daily runs.

How to extend

• Add more CopyOut tasks inside Parallel to pull additional tables.
• Swap the Pandas logic for any reshape, enrichment, or scoring you need.
• Push bestsellers_pandas.json to a warehouse, object store, or dashboard with a follow-up task.
• Change the cron, or replace the schedule with a flow or webhook trigger.

Links

• PostgreSQL plugin docs
• Postgres orchestration with Kestra