Download a CSV from a URL and Bulk-Load It into PostgreSQL with COPY

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

1. 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.
2. 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.
3. 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.
4. 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

1. Add the POSTGRES_URL, POSTGRES_USER, and POSTGRES_PASSWORD secrets to your Kestra instance.
2. Create the destination table (the public.raw_data default expects columns matching the CSV header).
3. Execute the flow, adjusting csv_url, target_table, and field_separator as needed.
4. 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

• Plugin docs: PostgreSQL
• Orchestrate PostgreSQL with Kestra