Loading CSV data into PostgreSQL with row-by-row INSERT statements is slow, brittle, and hard to schedule reliably. This blueprint downloads a CSV file from any public URL, normalizes it through Kestra's serialization layer, and bulk-loads it into a PostgreSQL table using the native COPY FROM STDIN protocol, the fastest path for ingesting large CSV datasets into Postgres. It is a ready-to-run CSV to PostgreSQL ingestion pipeline for ELT and data loading workloads.
How it works
- The
download_csv task (io.kestra.plugin.core.http.Download) fetches the CSV from the csv_url input and stores it in Kestra's internal storage for reliable, stateless processing.
- The
csv_to_ion task (io.kestra.plugin.serdes.csv.CsvToIon) parses the raw CSV into Kestra's typed ION format, honoring the field_separator input and the header flag so column types are normalized regardless of source encoding.
- The
ion_to_csv task (io.kestra.plugin.serdes.csv.IonToCsv) re-serializes the ION data into a clean, consistent CSV ready for the bulk loader.
- The
load_to_postgres task (io.kestra.plugin.jdbc.postgresql.CopyIn) streams that file straight into the target_table using format: CSV and header: true, bypassing row-by-row insert overhead.
What you get
- A reproducible, observable ingestion pipeline instead of an ad hoc upload script.
- High-throughput loads that handle large datasets in a single
COPY operation.
- Clean, type-normalized CSV regardless of the source file's quirks.
- Parameterized runs via the
csv_url, target_table, and field_separator inputs.
Who it's for
- Data engineers building ELT pipelines that land raw CSV before SQL transformation.
- Analytics teams ingesting public datasets, partner exports, or vendor dumps.
- Teams migrating spreadsheet-based workflows into a relational database.
Why orchestrate this with Kestra
PostgreSQL's COPY command moves data fast, but Postgres has no built-in scheduler, retry logic, or pipeline lineage to get the file there in the first place. Kestra closes that gap: add an event or schedule trigger to run on new files, set retries on the download and load tasks, and capture full execution lineage across the HTTP fetch, serialization, and load steps. Everything is declarative YAML, versioned and observable, with no glue scripts to maintain.
Prerequisites
- A reachable PostgreSQL instance and a target table that already exists.
- A publicly reachable CSV URL (the default points to the Titanic dataset).
Secrets
Configure these Kestra secrets before running:
POSTGRES_URL: JDBC connection URL for the target database.
POSTGRES_USER: database user with insert privileges on the target table.
POSTGRES_PASSWORD: password for that user.
Quick start
- Add the
POSTGRES_URL, POSTGRES_USER, and POSTGRES_PASSWORD secrets to your Kestra instance.
- Create the destination table (the
public.raw_data default expects columns matching the CSV header).
- Execute the flow, adjusting
csv_url, target_table, and field_separator as needed.
- Inspect the execution and confirm the rows landed in PostgreSQL.
How to extend
- Add a task to run
CREATE TABLE IF NOT EXISTS before loading.
- Insert a Python or script transform to remap or clean columns mid-pipeline.
- Send a Slack notification with the loaded row count on success.
- Chain into a dbt transformation run after ingestion for a full ELT flow.
Links