Defining Data Contracts in YAML That Fail the Build

A data contract is a written promise between the team that produces a table and the teams that read it. The promise is small: these columns exist, with these types, never null here, unique there, only these values, no negative amounts. Without it the promise lives in a Slack thread, and the first time a producer renames status or lets a null through, a dashboard breaks at night. YAML suits the written form: it is readable by an analyst, diffable in a pull request, and easy to load into a script that enforces it. This post defines a contract, enforces it with DuckDB and shows what a failing run looks like. It belongs to the cluster on YAML in data engineering workflows, and the file format choices behind it are in validating YAML with JSON Schema and declaring pipelines in YAML for data jobs.

The contract file

Keep the file about the dataset, not about any one engine. It names the dataset, who owns it, a version that changes when the promise changes, and how stale the data may be:

dataset: orders
owner: team-a
version: 2
freshness_hours: 24
columns:
  - {name: id, type: integer, nullable: false, unique: true}
  - {name: status, type: string, nullable: false, allowed: [new, paid, shipped]}
  - {name: amount, type: decimal, nullable: false, min: 0}
  - {name: placed_at, type: timestamp, nullable: false}
  - {name: coupon, type: string, nullable: true}

Four decisions are worth defending. The type values are your own vocabulary (integer, string, decimal, timestamp), mapped to an engine's types in one place, so the same contract can be checked in PostgreSQL, DuckDB or a warehouse. nullable defaults to true in the script, and the file says false where it matters, because a contract that lists only the strict columns is easier to review than one that repeats defaults. allowed is a closed list, which makes adding a new status a visible change to the file and not a silent one in a producer's code. And version is an integer you bump on any change that a consumer could notice.

A flow-style row per column keeps a five-column contract on one screen. When columns carry descriptions, switch to block style, because a long line in braces is where quoting mistakes hide (YAML strings and quoting).

The check

The enforcement is a loop that reads the file and runs one query per rule against information_schema and the table:

contract = yaml.safe_load(open("contract.yaml"))
TYPES = {"integer": "INTEGER", "string": "VARCHAR", "decimal": "DECIMAL", "timestamp": "TIMESTAMP"}
actual = {r[0]: r[1] for r in con.execute(
    "select column_name, data_type from information_schema.columns where table_name = 'orders'").fetchall()}
problems = []
for col in contract["columns"]:
    n = col["name"]
    if n not in actual:
        problems.append(f"{n}: column missing"); continue
    if not actual[n].startswith(TYPES[col["type"]]):
        problems.append(f"{n}: type {actual[n]} is not {TYPES[col['type']]}")
    if not col.get("nullable", True):
        c = con.execute(f"select count(*) from orders where {n} is null").fetchone()[0]
        if c: problems.append(f"{n}: {c} null value(s) in a non-nullable column")
    if col.get("unique"):
        c = con.execute(f"select count(*) from (select {n} from orders group by {n} having count(*) > 1)").fetchone()[0]
        if c: problems.append(f"{n}: {c} duplicated value(s)")
    if "allowed" in col:
        allowed = ", ".join(f"'{a}'" for a in col["allowed"])
        rows = con.execute(f"select distinct {n} from orders where {n} is not null and {n} not in ({allowed})").fetchall()
        if rows: problems.append(f"{n}: values outside the allowed list: {[r[0] for r in rows]}")
    if "min" in col:
        c = con.execute(f"select count(*) from orders where {n} < {col['min']}").fetchone()[0]
        if c: problems.append(f"{n}: {c} value(s) below {col['min']}")

After the loop, a final comparison reports columns that exist in the table but not in the contract, and the script prints the problems and exits with a nonzero status when there are any. The SQL is assembled from names in a file your team controls, which is acceptable for a script you run yourself. When the file can come from elsewhere, read generating SQL from YAML templates safely first, because these f-strings are exactly the pattern that post takes apart.

A table that breaks it

The test table has five rows, each wrong in a different way: id 3 twice, a refunded status the list does not allow, a negative amount and a null status. The run:

contract orders v2: 4 problem(s)
 - id: 1 duplicated value(s)
 - status: 1 null value(s) in a non-nullable column
 - status: values outside the allowed list: ['refunded']
 - amount: 1 value(s) below 0

Four problems, one line each, naming the column and the rule. Note what is absent. The coupon column holds nulls and no complaint is raised, because the contract says it is nullable. The null status is reported once under the null rule and not again under the allowed rule, because the allowed query excludes nulls on purpose: a null is a different failure from a wrong value, and one fault should not produce two lines of noise. The type rule was satisfied, and it is worth seeing it fail once. If a producer changes id to a string, the line reads id: type VARCHAR is not INTEGER, which is the failure that otherwise reaches consumers as a casting error three tables downstream.

Make the report useful to the person who must fix it. A count tells you the size; the failing values tell you where to look. For the duplicated id, print the value and the rows it appears in, and for the range rule print the smallest offender. Keep that detail behind a flag so the default output stays short enough to read in a CI log.

Where the check runs

A contract that nobody runs is documentation. Run it in two places:

• At the producer, before publishing. The job that writes orders checks its own output against the contract and refuses to swap the new table into place on failure. This is the cheap place to catch a fault, because the previous good table is still there to keep serving.

• At the consumer, on arrival. The reading job checks again, because the producer's check can be skipped, changed or wrong. Two checks of the same file cost seconds and remove an argument about whose fault the break was.

Version the file in the repository next to the pipeline code and review changes to it like an API change. A pull request that adds a status to allowed should name the consumers that need to handle it (reviewing YAML config changes in pull requests). If you already use dbt, the same ideas exist natively as model contracts and tests, which defining dbt tests in YAML covers; the hand-written check above is useful where there is no dbt, or where one contract must cover tables in several engines.

Freshness and volume

Two rules in a contract are about the table as a whole and not its columns. freshness_hours: 24 says the newest placed_at must be less than a day old; the check is select max(placed_at) compared with the clock, and it belongs in the same script. A row-count floor ("not fewer than 90 percent of yesterday's rows") catches the failure that column rules cannot see, a load that succeeded and wrote nothing. Both rules need a reference (the clock, the previous run), so decide where that reference lives. Passing the current time into the check, instead of reading it inside, makes the check testable (testing YAML driven jobs).

When a YAML data contract is not the right tool

The rule needs more than one column or table. "Every order has a customer" is a foreign key, and "the sum of items equals the order total" crosses tables. The format above does not express them, and cramming a query language into YAML strings loses the point of a simple file. Put those in SQL tests beside the contract.

The data is wrong but valid. An order placed in 1970 satisfies every rule above. A contract narrows the space of bad data, and does not close it.

Nobody owns the consequence. A contract with no owner, or whose failure pages nobody, becomes a log line. Decide who is told and what stops, or do not write the file yet.

The producer cannot meet it. A contract written by consumers and handed to a team that never agreed to it fails every night and is soon disabled. Agree on the first version together, and loosen a rule in the file, visibly, instead of ignoring the failures.

The table changes shape every week. Early exploratory data does not deserve a contract. Write one when someone depends on the table.

The checks use DuckDB's information schema, and the open data contract format is specified by the Open Data Contract Standard, a larger vocabulary than the sketch here. Validate the file itself with the YAML formatter.