Build a monthly sales report by querying S3 CSVs with DuckDB and emailing the result

Turn raw monthly order files into a finished sales report without standing up a warehouse. This flow extracts CSV data into Amazon S3 with Python, aggregates it directly over the S3 files using an in-process DuckDB engine, formats the result as CSV, and emails the report to stakeholders on the first day of every month. It is a lightweight, fully automated reporting pipeline that combines object storage, SQL analytics, and delivery in a single declarative workflow.

How it works

1. raw_data_to_s3 runs a Python io.kestra.plugin.scripts.python.Script task on a Docker task runner. It uses requests and boto3 to download twelve monthly order CSV files and upload each one to the target S3 bucket, emitting the S3 URIs as Kestra outputs.
2. query runs io.kestra.plugin.jdbc.duckdb.Queries. It loads the DuckDB httpfs extension, sets the S3 region and credentials, then runs read_csv_auto('s3://kestraio/monthly_orders/*.csv') to sum totals by month. fetchType: STORE writes results to Kestra internal storage with a PT30S timeout.
3. csv converts the stored Ion result into CSV via io.kestra.plugin.serdes.csv.IonToCsv.
4. email sends the CSV as an attachment using io.kestra.plugin.email.MailSend over SMTP.
5. The monthly io.kestra.plugin.core.trigger.Schedule trigger fires on cron 0 9 1 * *, at 09:00 on the first of each month.

What you get

• A hands-off monthly report delivered straight to inboxes.
• SQL analytics directly over S3 files, no separate database to provision.
• A reusable extract, aggregate, format, and deliver pattern.
• Full run history and logs for every monthly execution.

Who it's for

• Data and analytics engineers automating recurring reports.
• Operations and finance teams who need monthly sales numbers by email.
• Teams wanting cheap analytics over object storage without a warehouse.

Why orchestrate this with Kestra

DuckDB has no scheduler, no retry semantics, and no way to chain extraction, formatting, and email delivery. Kestra wraps the whole pipeline in declarative YAML: the Schedule trigger handles recurring runs, task-level retries and timeouts add resilience, outputs pass cleanly between Python, DuckDB, SerDes, and email tasks, and every run is logged with full lineage for auditing.

Prerequisites

• A Kestra instance with Docker available for the Python task runner.
• An S3 bucket (replace kestraio with your own) and an SMTP account.

Secrets

• AWS_ACCESS_KEY_ID
• AWS_SECRET_ACCESS_KEY
• AWS_DEFAULT_REGION
• EMAIL_PASSWORD

Quick start

1. Add the four secrets above to your Kestra instance.
2. Replace the kestraio bucket name with your own bucket.
3. Update the from, to, and SMTP host/port values in the email task.
4. Add the flow and run it manually, then let the monthly schedule take over.

How to extend

• Swap in MotherDuck by pointing the DuckDB url at jdbc:duckdb:md: with a MOTHERDUCK_TOKEN secret.
• Persist results to a table or push to Slack instead of email.
• Parameterize the year and report period with flow inputs.
• Add a chart or PDF rendering step before delivery.

Links

• DuckDB plugin docs
• DuckDB orchestration