Load a CSV into Vertica and run a columnar analytics query

Run a re-run-safe extract, load, and analyze cycle against a Vertica columnar analytics database with Kestra. This blueprint downloads a remote CSV, parses it to ION, creates a target table if needed, truncates the staging table, batch-inserts rows over JDBC, and then runs a columnar aggregation query, all wrapped in retries, timeouts, concurrency control, and a Slack failure alert. It is a practical template for repeatable CSV ingestion into a Vertica MPP warehouse without writing custom scripts.

How it works

1. io.kestra.plugin.core.http.Download streams {{ inputs.source_url }} into Kestra internal storage with a 10 minute timeout and a constant retry policy.
2. io.kestra.plugin.serdes.csv.CsvToIon parses the CSV (header: true) into ION so each row can be bound to a prepared statement.
3. io.kestra.plugin.jdbc.vertica.Query (task create_table) issues idempotent DDL to create the columnar target table if it does not already exist.
4. io.kestra.plugin.jdbc.vertica.Query (task truncate_staging) truncates the staging table so a re-run reloads instead of appending duplicates.
5. io.kestra.plugin.jdbc.vertica.Batch chunk-inserts the ION rows (1,000 per chunk) with named-column binding, so the load survives a CSV column reorder.
6. io.kestra.plugin.jdbc.vertica.Query (task revenue_by_product) aggregates revenue per product with fetchType: STORE, persisting the result set as an ION file.
7. On any failure, the flow-level errors block posts a Slack alert via io.kestra.plugin.slack.notifications.SlackIncomingWebhook with the execution id.

What you get

• Idempotent truncate-and-reload pipeline from a public or internal CSV endpoint into Vertica.
• Centralized JDBC connection through pluginDefaults so URL, username, and password are declared once.
• Chunked batch inserts (1,000 rows) with named-column binding, plus a stored aggregation result available at {{ outputs.revenue_by_product.uri }}.
• Row counters via {{ outputs.batch_insert.rowCount }} and {{ outputs.batch_insert.updatedCount }} for downstream assertions.
• Slack-on-failure notifications and concurrency.limit: 1 to prevent interleaved loads.

Who it's for

• Data engineers operationalizing CSV-to-Vertica ingestion outside ad hoc scripts.
• Analytics engineers who need a repeatable staging-table refresh before BI or dbt models run.
• Platform teams standardizing JDBC loaders with retries, timeouts, and alerting across a Vertica MPP estate.

Why orchestrate this with Kestra

Vertica is a database, not a scheduler. It has no native way to fetch a remote CSV, retry a transient HTTP failure, serialize concurrent loaders, or page an on-call engineer when a load fails. Kestra adds declarative YAML, event and schedule triggers, task-level retries and timeouts, flow-level error handling, execution-level concurrency caps, and lineage across the HTTP download, the JDBC DDL, the batch insert, and the analytical query, so the whole load is observable as one execution rather than scattered cron jobs.

Prerequisites

• A reachable Vertica cluster. Update the vertica_url variable (default jdbc:vertica://vertica-host:5433/analytics) to your JDBC URL.
• The connecting user must have CREATE, TRUNCATE, and INSERT privileges on the target schema.
• A Slack incoming webhook for failure alerts.

Secrets

• VERTICA_USERNAME: Vertica database user used for the JDBC connection.
• VERTICA_PASSWORD: password for that Vertica user.
• SLACK_WEBHOOK: Slack incoming webhook URL for failure notifications.

Quick start

1. Add the VERTICA_USERNAME, VERTICA_PASSWORD, and SLACK_WEBHOOK secrets to your namespace.
2. Edit the vertica_url variable to point at your cluster.
3. Extend the target_table allow-list and adjust the column list in create_table and batch_insert to match your CSV schema.
4. Execute the flow with the default source_url to validate end-to-end, then point it at your real CSV endpoint.

How to extend

• Swap io.kestra.plugin.jdbc.vertica.Batch for Vertica's native COPY bulk loader when ingesting hundreds of MB or more.
• Add a schedule or webhook trigger so new CSV drops auto-load.
• Insert a row-count assertion after batch_insert to fail loudly on empty loads.
• Chain a dbt or downstream model run after revenue_by_product to materialize marts.
• Replace the Slack alert with PagerDuty, Microsoft Teams, or email notifiers for different on-call routes.

Links

• Vertica JDBC plugin: https://kestra.io/plugins/plugin-jdbc-vertica