Schema Contract Checks with Soda

Schema drift rarely announces itself; it shows up as a dbt model that stopped compiling, a dashboard full of NULLs, or an export that silently dropped a column. This blueprint makes the schema itself a tested contract: io.kestra.plugin.soda.Scan runs a SodaCL schema check listing the columns that must exist and the legacy columns that must not. Drift fails the check, the check fails the task, and the errors block posts a Discord alert naming the table and pointing at the exact failing condition, one loud failure at the source instead of many confusing ones downstream.

How it works

1. load_orders (io.kestra.plugin.jdbc.duckdb.Queries) creates the orders table with the contracted columns in the DuckDB file at warehouse_path. It exists so the blueprint runs anywhere; in production the table is whatever your loads produce.
2. schema_scan (io.kestra.plugin.soda.Scan) receives the connection through the configuration map, which must define a data source named kestra, and a SodaCL schema check through the checks map: when required column missing lists the columns every consumer depends on, and when forbidden column present lists columns that were deliberately removed and must not return. The requirements list adds soda-core-duckdb.
3. The task's failure semantics carry the alert: a failed schema check sets the task to FAILED and the execution jumps to the errors block, so drift and alert cannot be separated by a forgotten notification task.
4. confirm_contract logs the passing verdict from {{ outputs.schema_scan.result }}, leaving a per-execution record that the contract held at scan time.
5. A disabled-by-default hourly Schedule trigger keeps the detection window within one load cycle.

What you get

• A schema contract as versioned SodaCL, reviewable in the same pull request that changes the flow.
• Drift detection at the table, hours before it would surface as broken models or empty dashboards.
• Forbidden-column enforcement, catching rollbacks and reintroduced legacy fields that additive checks miss.
• An execution history that doubles as an audit trail of when the schema was last verified.

Who it's for

• Analytics engineers whose dbt models depend on upstream tables owned by other teams.
• Data platform teams formalizing data contracts without adopting a contract framework.
• Anyone who has debugged a broken dashboard back to a column renamed three days earlier.

Why orchestrate this with Kestra

A schema check is only useful on a cadence, with alerting, and with a record of every verification. Kestra provides the schedule, routes the failure to Discord through the errors block with no extra wiring, and preserves each verdict as structured outputs in the execution history. The Scan task ships the Soda container image, so the check runs identically on any worker with nothing installed.

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 drift 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 log line reports the contract holding.
3. Add legacy_id to the required-columns list and rerun to watch the drift path: failed check, failed task, Discord alert.
4. Point the check at your real table, edit the column lists to match its contract, and enable the hourly_contract_check trigger.

How to extend

• Add when wrong column type to the schema check to pin column types as well as names, using the type names your warehouse reports.
• Add when schema changes: any for change-over-time detection; this variant compares against previous scans and requires a Soda Cloud connection in the configuration map.
• Point the configuration map at Snowflake, BigQuery, or Postgres with credentials from {{ secret('...') }}, and add the matching soda-core-* package to requirements.
• Chain a quality gate behind the contract check, as shown in the Soda quality gate blueprint, so downstream work is blocked while the schema is broken.

Links

• Soda plugin documentation
• SodaCL schema checks