Scheduled Vertica rollup exported to Amazon S3

Run a production-grade daily rollup from Vertica to Amazon S3 with Kestra. This blueprint aggregates the previous day's sales in Vertica, guards against empty result sets, converts the stored ION dataset to CSV, and uploads the report to a date-partitioned S3 key. Retries, timeouts, and a Slack failure alert make it safe to leave unattended, and an empty-day assertion stops silent shipping of header-only files when an upstream load is late or missing.

How it works

1. A io.kestra.plugin.core.trigger.Schedule trigger fires daily at 30 6 * * * in Etc/UTC, so the run time does not drift with the worker zone or DST.
2. daily_rollup (io.kestra.plugin.jdbc.vertica.Query) aggregates the report day's sales by product, binding the date via the parameters map as :report_date (prepared statement) and storing the result set as ION with fetchType: STORE.
3. assert_not_empty (io.kestra.plugin.core.execution.Fail) checks outputs.daily_rollup.size and fails loudly when there are zero rows for the report day.
4. to_csv (io.kestra.plugin.serdes.csv.IonToCsv) converts the stored ION dataset into CSV with a header row.
5. upload_to_s3 (io.kestra.plugin.aws.s3.Upload) writes the CSV to your bucket under {{ inputs.s3_prefix }}/sales_rollup_<date>.csv.
6. A flow-level errors block posts a Slack alert via io.kestra.plugin.slack.notifications.SlackIncomingWebhook on any failure, including the report date and execution id.

What you get

• Daily CSV report in S3 under a date-partitioned key, ready for downstream BI or sharing.
• An explicit empty-day guard so a missing upstream load does not ship a misleading zero-revenue report.
• Retries (constant, 3 attempts) and 15-minute timeouts on the Vertica query and S3 upload to survive transient failover or throttling.
• Slack notifications when the run fails, with the report date and execution id.
• Centralized credentials in pluginDefaults keyed by plugin group, not repeated on every task.

Who it's for

• Analytics and data engineering teams running Vertica and shipping CSV extracts to downstream consumers.
• Platform teams replacing brittle cron plus bash plus aws cli scripts with an observable, retryable workflow.
• BI engineers who need a reliable date-partitioned drop in S3 for Athena, Snowflake external tables, or partner exchanges.

Why orchestrate this with Kestra

• Event-aware: replace the daily Schedule with a Flow trigger so the rollup fires the moment the upstream load flow finishes, eliminating fixed wait windows.
• Built-in retries and timeouts on every task, declared in YAML next to the task definition.
• The trigger date drives the SQL parameter and the S3 key, so backfills and replays from the Triggers tab target the correct day automatically.
• Lineage and outputs (outputs.daily_rollup.uri, outputs.to_csv.uri, outputs.upload_to_s3) are first-class and queryable, unlike a shell script.
• Vertica's own scheduler can run SQL, but it cannot orchestrate the format conversion, S3 upload, empty-day assertion, Slack alerting, and namespace-scoped secrets in one declarative artifact.

Prerequisites

• A reachable Vertica cluster holding the source table, with the JDBC URL set in the vertica_url variable.
• An Amazon S3 bucket and AWS credentials with s3:PutObject on that bucket. Enable bucket versioning if you need immutable history (a re-run overwrites the same key).
• A Slack incoming webhook URL for failure alerts.

Secrets

• VERTICA_USERNAME: Vertica database user for the JDBC connection.
• VERTICA_PASSWORD: password for that Vertica user.
• AWS_S3_BUCKET: destination S3 bucket name for the daily report.
• AWS_DEFAULT_REGION: AWS region of the bucket, for example us-east-1.
• AWS_ACCESS_KEY_ID: AWS access key ID with write access to the bucket.
• AWS_SECRET_ACCESS_KEY: AWS secret access key paired with the access key ID.
• SLACK_WEBHOOK: Slack incoming webhook URL used for failure alerts.

Quick start

1. Add the secrets above to your Kestra namespace.
2. Update the vertica_url variable to your JDBC URL and adjust the source_table and s3_prefix inputs.
3. Adjust the SQL in daily_rollup to match your schema, keeping the :report_date bind variable.
4. Save the flow. Run it once manually to verify connectivity, then enable the daily Schedule trigger.

How to extend

• Replace the Schedule with a Flow trigger so the rollup runs the moment your upstream load flow finishes.
• Add a io.kestra.plugin.notifications.slack.SlackIncomingWebhook success notification with a link to the S3 object.
• Fan out by region or business unit using a ForEach task and parameterized SQL.
• Write Parquet instead of CSV with io.kestra.plugin.serdes.parquet.IonToParquet for cheaper downstream querying.
• Materialize the same dataset into a warehouse (Snowflake, BigQuery, Redshift) by adding a load task in parallel to the S3 upload.

Links

• Vertica plugin: https://kestra.io/plugins/plugin-jdbc-vertica
• AWS plugin: https://kestra.io/plugins/plugin-aws
• AWS orchestration overview: https://kestra.io/orchestration/aws