Run DDL queries and load data to Postgres

This blueprint builds a complete CSV-to-Postgres ingestion pipeline that downloads a remote CSV file, creates the destination table with a DDL statement, and bulk loads the rows into PostgreSQL. It solves a common ETL problem: getting flat-file data into a relational database reliably, with the schema defined up front and the load step decoupled from extraction so each step can be retried and observed independently.

How it works

1. The extract task (io.kestra.plugin.core.http.Download) fetches a remote orders.csv file over HTTP and stores it in Kestra internal storage, exposing its location as outputs.extract.uri.
2. The query task (io.kestra.plugin.jdbc.postgresql.Query) runs a create table if not exists DDL statement to define the orders schema (order id, customer name and email, product id, price, quantity, total) before any data lands.
3. The load_to_postgres task (io.kestra.plugin.jdbc.postgresql.CopyIn) streams the downloaded file into the target table using Postgres COPY, with format: CSV and header: true so the column header row is skipped.

The connection target and table name are driven by the db and table variables, keeping the flow easy to repoint at another database or schema.

What you get

• A repeatable, idempotent load: the DDL is safe to re-run and the bulk copy is fast.
• Clean separation of extract, schema, and load steps that each surface their own logs and outputs.
• Credentials kept out of the flow via {{ secret('DB_PASSWORD') }}.

Who it's for

• Data engineers landing flat files into Postgres staging tables.
• Analytics teams who need a dependable raw-ingest step ahead of transformations.
• Anyone migrating ad hoc psql scripts into a managed, observable pipeline.

Why orchestrate this with Kestra

Postgres has no native scheduler or pipeline engine of its own. Kestra adds event and schedule triggers, automatic retries on transient failures, full execution lineage across the download, DDL, and load steps, and a declarative YAML definition you can version control. The bulk COPY path stays fast while gaining the orchestration, monitoring, and dependency management that plain SQL scripts lack.

Prerequisites

• A reachable PostgreSQL instance and a JDBC URL.
• Network access to the remote CSV source.

Secrets

• DB_PASSWORD: the password for the Postgres user used by the query and load_to_postgres tasks.

Quick start

1. Add the DB_PASSWORD secret to your Kestra instance.
2. Update the db variable to point at your Postgres JDBC URL and set table to your target table.
3. For local testing, start Postgres with Docker:

   docker run -d --name mypostgres -v mypostgresdb:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=topSecret42 -e POSTGRES_DB=postgres postgres:latest
   

4. Execute the flow and confirm the rows land in your table.

How to extend

• Swap the extract URI for your own CSV source or an upstream API.
• Add a Schedule or Flow trigger to run the load on a cadence or on upstream completion.
• Insert a transformation step (dbt, SQL, or DuckDB) after the load to model the raw data.
• Adjust the DDL to match your real schema or add indexes and constraints.

Links

• Postgres plugin docs
• Postgres orchestration with Kestra