Nightly Warehouse Audit with Soda

Per-pipeline quality checks catch what each pipeline breaks; nobody watches the tables in between. This blueprint runs one scheduled io.kestra.plugin.soda.Scan over several tables at once, each with its own SodaCL block inside a single checks map, and posts a Discord digest every night with the failed and total check counts. Because the scan runs with allowFailure: true, the digest posts on bad nights too, which is exactly when it matters, while a scan that cannot run at all raises its own alert through the errors block.

How it works

1. seed_warehouse (io.kestra.plugin.jdbc.duckdb.Queries) creates and refreshes small orders, customers, and payments tables in the DuckDB file at warehouse_path. It exists so the blueprint runs anywhere; a real warehouse is already populated by your load pipelines.
2. audit_scan (io.kestra.plugin.soda.Scan) receives the connection through the configuration map, which must define a data source named kestra, and one SodaCL block per table through the checks map: row counts everywhere, missing_count on the columns joins depend on, duplicate_count on the customer key. The requirements list adds soda-core-duckdb.
3. allowFailure: true converts failed checks into a WARNING state, so the flow always reaches the digest instead of dying before reporting.
4. send_digest (io.kestra.plugin.discord.DiscordIncomingWebhook) computes the failed-check count from the per-check outcome fields in {{ outputs.audit_scan.result.checks }} with a jq filter, and posts it alongside the total count and the hasFailures and hasWarnings flags, one comparable line per night.
5. The errors block alerts when the audit itself cannot run, and a disabled-by-default Schedule trigger runs the audit at 02:00 after the load window closes.

What you get

• One audit covering every table you care about, defined as a single declarative SodaCL document versioned with the flow.
• A digest that always posts, so a missing message means the monitor broke, never that the night was quiet.
• Failed and total check counts computed from structured scan outputs, giving the channel a comparable daily number.
• Soda metrics emitted as Kestra metrics on every audit, building a per-table quality time series in the execution history.

Who it's for

• Data platform teams responsible for a warehouse fed by pipelines owned by different people.
• Analytics engineers who want one morning number instead of a folder of per-pipeline check logs.
• Anyone who has discovered a duplicate customer key weeks after it started.

Why orchestrate this with Kestra

A warehouse audit needs a scheduler, a container to run Soda in, digest formatting, and its own failure alerting. Kestra provides all four: the Schedule trigger sets the cadence, the Scan task ships the Soda container so nothing is installed on workers, the digest is built from structured task outputs rather than parsed logs, and the errors block guarantees a broken audit is louder than a failing table.

Prerequisites

• A persistent path for the DuckDB file (the warehouse_path input) visible to the task containers. For production warehouses, swap the configuration map to Snowflake, BigQuery, or Postgres and add the matching soda-core-* package to requirements.
• A Discord incoming webhook for the digest and alerts.

Secrets

• DISCORD_WEBHOOK_URL: Discord incoming webhook URL.

Quick start

1. Add the DISCORD_WEBHOOK_URL secret to your Kestra namespace.
2. Execute the flow and confirm the digest reports 0 of 6 checks failed.
3. Change a check to duplicate_count(customer_id) < 0 and rerun to see the failed count rise while the digest still posts.
4. Point the checks at your real tables, remove seed_warehouse, and enable the nightly_audit trigger.

How to extend

• Point the configuration map at Snowflake, BigQuery, or Postgres with credentials from {{ secret('...') }}, and add soda-core-snowflake, soda-core-bigquery, or soda-core-postgres to requirements.
• Add more tables by adding more checks for <table> blocks; the digest counts adapt automatically.
• Follow a failed audit with an explicit block on downstream work, as shown in the Soda quality gate blueprint.
• Page on-call instead of posting a digest when failures cross a threshold, as shown in the Soda incident escalation blueprint.

Links

• Soda plugin documentation
• SodaCL reference