Extract data, transform it, and load it in parallel to S3 and Postgres

Build a complete ETL pipeline that pulls JSON from a public REST API, enriches each record with a timestamp, and loads the result in parallel into a PostgreSQL table and an Amazon S3 bucket. This blueprint solves the common integration problem of moving API data into a relational warehouse and an object store at the same time, without writing glue scripts or wiring up a standalone scheduler, using declarative YAML and Kestra's built-in serializers for JSON, ION, and CSV.

How it works

1. download uses io.kestra.plugin.core.http.Download to fetch the user payload over HTTP GET and store it in Kestra internal storage.
2. ion converts the raw response to Kestra's ION format with io.kestra.plugin.serdes.json.JsonToIon, then json round-trips it back with io.kestra.plugin.serdes.json.IonToJson.
3. add_column runs io.kestra.plugin.graalvm.python.FileTransform to enrich every row with an inserted_at UTC timestamp.
4. parallel (io.kestra.plugin.core.flow.Parallel) fans out into two io.kestra.plugin.core.flow.Sequential branches that run at once.
5. The Postgres branch serializes to CSV with io.kestra.plugin.serdes.csv.IonToCsv, creates public.raw_users via io.kestra.plugin.jdbc.postgresql.Query, then bulk loads it with io.kestra.plugin.jdbc.postgresql.CopyIn.
6. The S3 branch serializes to JSON and uploads users.json to the kestraio bucket with io.kestra.plugin.aws.s3.Upload.

What you get

• A reproducible extract, transform, and dual-load pipeline in one file.
• Parallel delivery to a relational store (Postgres) and an object store (S3).
• Row-level enrichment with an ingestion timestamp for auditability.
• Format conversions (JSON, ION, CSV) handled by native Kestra tasks.

Who it's for

• Data engineers landing API data into a Postgres warehouse.
• Analytics teams that also need a raw JSON copy in S3 for archival or replay.
• Platform teams replacing brittle cron plus shell ETL scripts.

Why orchestrate this with Kestra

Postgres and S3 have no native scheduler that can fetch an API, branch into parallel loads, and retry each step independently. Kestra adds event and schedule triggers, per-task retries, full execution lineage across the extract and both load branches, and a declarative YAML definition you can version and review. The Parallel task runs the Postgres and S3 loads concurrently, something a plain SQL scheduler or a bucket lifecycle rule cannot coordinate on its own.

Prerequisites

• A reachable PostgreSQL instance (the flow targets host.docker.internal:5432).
• An Amazon S3 bucket (the flow uses kestraio).
• GraalVM Python available for the FileTransform enrichment step.

Secrets

• AWS_DEFAULT_REGION
• AWS_ACCESS_KEY_ID
• AWS_SECRET_ACCESS_KEY

Quick start

1. Add this flow to a Kestra namespace.
2. Set the three AWS secrets listed above.
3. Point the Postgres url, username, and password at your database and confirm the kestraio bucket and region.
4. Execute the flow and check public.raw_users plus users.json in S3.

How to extend

• Swap the API URL in download for your own endpoint.
• Adjust the add_column Python to derive more fields or clean data.
• Add more Sequential branches under Parallel for extra destinations.
• Attach a Schedule or Webhook trigger to automate runs.

Links

• plugin-jdbc-postgres
• plugin-aws
• Postgres orchestration