Extract data from an API using Python, then load it to Postgres and S3

This blueprint runs a Python-based ETL pipeline that extracts JSON data from a REST API, loads it into a Postgres table, and archives a copy of the dataset to Amazon S3. It solves a common data engineering need: turning a raw HTTP endpoint into queryable warehouse rows while keeping an immutable file copy in object storage, all without standing up a separate ingestion service or scheduler. Use it as a starting point for API ingestion, reverse ETL staging, or any extract-load workflow that pairs a relational database with cloud object storage.

How it works

1. The api_to_postgres task (io.kestra.plugin.scripts.python.Script) runs Python in a Docker container. Kestra installs the declared dependencies (requests, pandas, psycopg2, sqlalchemy) before the script starts.
2. The script calls the gorest.co.in users API with requests, builds a pandas DataFrame, and tags each row with an inserted_from column.
3. It opens a SQLAlchemy engine to Postgres (using the DB_PASSWORD secret) and appends the rows to the users table via to_sql, then writes users.json as a captured outputFiles artifact.
4. The s3_upload task (io.kestra.plugin.aws.s3.Upload) takes that users.json output file and uploads it to the kestraio bucket in eu-central-1, authenticating with the AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY secrets.

What you get

• A repeatable API to Postgres load with a clean dependency install per run.
• An automatic S3 archive of every extracted dataset for replay and audit.
• Output file passing between tasks, so the script result flows straight into the upload.
• A single declarative YAML definition you can version, review, and promote across environments.

Who it's for

• Data engineers building API ingestion and extract-load pipelines.
• Analytics teams landing third-party data into Postgres for reporting.
• Platform teams that want a portable, container-based Python job under orchestration.

Why orchestrate this with Kestra

A cron job or a standalone script can run the Python, but it cannot give you event-driven triggers, automatic retries on a flaky API call, run-level lineage between the extract and the upload, or a declarative definition the whole team can read. Postgres itself has no scheduler to coordinate the downstream S3 archive. Kestra wires the two tasks together, passes the users.json artifact across them, and gives you logs, replays, and observability for the full pipeline in one place.

Prerequisites

• A reachable Postgres instance with a users table (or permission to create one).
• An Amazon S3 bucket and an IAM identity allowed to put objects.
• Docker available to the Kestra worker for the Python task.

Secrets

• DB_PASSWORD: password for the Postgres connection.
• AWS_ACCESS_KEY_ID: AWS access key for S3 uploads.
• AWS_SECRET_ACCESS_KEY: AWS secret key for S3 uploads.

Quick start

1. Add the DB_PASSWORD, AWS_ACCESS_KEY_ID, and AWS_SECRET_ACCESS_KEY secrets to your Kestra instance.
2. Update the Postgres host and connection string in the script, plus the S3 bucket and region, to match your environment.
3. Execute the flow and confirm rows land in the users table and users.json appears in your bucket.

How to extend

• Point the script at your own API and schema instead of the sample users endpoint.
• Add a Schedule trigger or a webhook trigger to run the pipeline on a cadence or on demand.
• Swap the if_exists="append" strategy for an upsert, or add a dbt or SQL transform task after the load.
• Partition the S3 key by date to keep a time-stamped archive of each run.

Links

• Python plugin docs: https://kestra.io/plugins/plugin-script-python
• AWS plugin docs: https://kestra.io/plugins/plugin-aws
• Postgres orchestration: https://kestra.io/orchestration/postgres