Anomaly detection using DuckDB SQL query and S3 file event trigger

Catch bad data the moment it lands. This event-driven blueprint watches an Amazon S3 bucket and, whenever a new CSV file arrives, runs a DuckDB SQL query directly against the object in S3 to flag anomalous rows (for example, orders where price * quantity does not match the recorded total). Any flagged rows are exported to CSV and emailed to your data team automatically. It pairs DuckDB's fast, zero-copy SQL over object storage with Kestra's S3 file-event trigger so validation happens on arrival instead of on a nightly batch.

How it works

1. The poll_for_new_s3_files trigger (io.kestra.plugin.aws.s3.Trigger) polls the bucket under source_prefix, picks up new files (filter: FILES), and MOVEs each one into destination_prefix, so a file is processed exactly once.
2. The query task (io.kestra.plugin.jdbc.duckdb.Queries) installs and loads the httpfs extension, sets S3 credentials, and runs read_csv_auto against the moved object to select rows that violate the data-quality rule. Results are stored via fetchType: STORE.
3. The csv task (io.kestra.plugin.serdes.csv.IonToCsv) converts the query results into a CSV file.
4. The if_anomalies_detected task (io.kestra.plugin.core.flow.If) checks outputs.query.size. When anomalies exist, send_email_alert (io.kestra.plugin.email.MailSend) sends an email with the anomalous rows attached as anomalies_in_orders.csv.

What you get

• Automatic data-quality checks on every file that lands in S3
• SQL-based validation that reads CSV straight from object storage, no staging database required
• A CSV attachment of exactly the rows that failed the rule
• Email alerts only when there is something to act on

Who it's for

• Data engineers adding validation gates to ingestion pipelines
• Analytics engineers enforcing rules on incoming files
• Teams that want lightweight checks without standing up a warehouse

Why orchestrate this with Kestra

DuckDB has no scheduler and no event listener of its own: it runs a query when you invoke it. Kestra fills that gap. The S3 trigger reacts to file events the moment data arrives, retries and timeouts make polling and queries resilient, every run is captured for full execution lineage, and the entire pipeline (trigger, SQL, conversion, conditional alert) is declared in one readable YAML file instead of glue scripts and cron.

Prerequisites

• An AWS account with an S3 bucket and read/write access to the configured prefixes
• A reachable SMTP server for the email alert
• A Kestra instance with the AWS, JDBC DuckDB, SerDes, and Email plugins

Secrets

• AWS_DEFAULT_REGION: AWS region of the bucket
• AWS_ACCESS_KEY_ID: AWS access key used by the trigger and DuckDB
• AWS_SECRET_ACCESS_KEY: AWS secret key used by the trigger and DuckDB
• EMAIL_PASSWORD: password for the SMTP account sending alerts

Quick start

1. Add the secrets above to your Kestra instance.
2. Set the bucket, source_prefix, and destination_prefix variables to match your S3 layout.
3. Update the email from, to, host, and port on send_email_alert.
4. Save the flow and drop a CSV into the source prefix to see the trigger fire.

How to extend

• Change the WHERE clause in the query task to encode your own data-quality rules.
• Route alerts to Slack, Microsoft Teams, or a webhook instead of (or alongside) email.
• Load validated files into a warehouse such as Snowflake or BigQuery as a follow-up task.
• Point the query at MotherDuck by storing a MOTHERDUCK_TOKEN secret and setting the DuckDB url to jdbc:duckdb:md:my_db?motherduck_token={{ secret('MOTHERDUCK_TOKEN') }}.

Links

• Plugin DuckDB (JDBC)
• Plugin AWS
• DuckDB orchestration