Documenting Datasets in YAML and Catching Schema Drift

Every team has a table that three people understand. Documentation exists in a wiki page last edited by someone who has left, and the column called coupon turns out to mean something different from what its name suggests. Writing the description beside the schema fixes the first problem, and checking it in CI fixes the second. YAML is a good home: it is readable in a pull request, it has no tool dependency, and a short script can turn it into a page. This post builds the file, the page and the drift check, and shows what the check finds. It belongs to the cluster on YAML in data engineering workflows, and pairs with the enforcement side in defining data contracts in YAML.

The dataset file

One file per dataset, kept in the repository next to the job or migration that creates the table:

dataset: orders
description: One row per customer order.
owner: team-a
columns:
  - {name: id, type: integer, description: Primary key.}
  - {name: status, type: varchar, description: Order state.}
  - {name: amount, type: "decimal(10,2)", description: Total in euros.}
  - {name: coupon, type: varchar, description: Coupon code if used.}

The fields are few on purpose. The description is for a person who has never seen the table, the owner is for the person who needs to ask, and each column has a type and one sentence. Resist adding twenty optional keys; a file with a lot of fields to fill is a file that stays empty. Add a field when someone asks the question it would answer more than twice.

Note the quotes around "decimal(10,2)". Inside braces a comma separates entries, so the unquoted type: decimal(10,2) parses as type: decimal(10 followed by a stray entry. I made exactly that mistake in the first run of this script, and the YAML loaded without complaint. The only sign was later, in the drift check, as a type mismatch between decimal(10 and the table's decimal(10,2). If your descriptions or types contain commas, colons or leading special characters, write the column in block style (one key per line) or quote the values (YAML strings and quoting).

Render it

The page is generated, never edited by hand:

spec = yaml.safe_load(open("orders.dataset.yaml"))
md = [f"# {spec['dataset']}", "", spec["description"], f"Owner: {spec['owner']}", "",
      "| column | type | description |", "|---|---|---|"]
md += [f"| {c['name']} | {c['type']} | {c['description']} |" for c in spec["columns"]]
print("\n".join(md))

The output is plain Markdown:

# orders

One row per customer order.
Owner: team-a

| column | type | description |
|---|---|---|
| id | integer | Primary key. |
| status | varchar | Order state. |
| amount | decimal(10,2) | Total in euros. |
| coupon | varchar | Coupon code if used. |

Commit the YAML only, and generate the Markdown in the build that publishes your docs, so there is nothing to keep in step. The same file can feed other consumers: a static site, a data catalog import, the column comments in the database (comment on column), the checks in defining data contracts in YAML. One source, several outputs, is the reason to use a file instead of a wiki. If a description contains a pipe character, escape it for the table; if it contains Markdown you did not mean, say so in the convention.

Detect drift

A document is wrong the day after someone adds a column. The check compares the file with the live table:

live = {r[0]: r[1].lower() for r in con.execute(
    "select column_name, data_type from information_schema.columns where table_name='orders'").fetchall()}
doc = {c["name"]: c["type"].lower() for c in spec["columns"]}
print("documented but missing in table:", sorted(set(doc) - set(live)))
print("in table but undocumented:", sorted(set(live) - set(doc)))
print("type mismatch:", sorted((k, doc[k], live[k]) for k in doc if k in live and doc[k] != live[k]))

The live table in this test has columns id, status, amount and created. The result:

documented but missing in table: ['coupon']
in table but undocumented: ['created']
type mismatch: []

Two kinds of drift, and neither is visible when you read the file. coupon is documented and does not exist: somebody dropped or never added it, and the doc describes a column readers will look for in vain. created exists and has no description: somebody added it in a migration and nobody wrote it down. A third kind, the type mismatch, is empty here after the quoting fix and is the check that caught the unquoted comma earlier. All three lists should be empty for the build to pass.

Where the comparison needs care:

• Type names differ between your file and the engine. DuckDB reports VARCHAR and DECIMAL(10,2), PostgreSQL reports character varying and numeric. Either normalize both sides to a small vocabulary before comparing, or document engine types exactly and accept that the file belongs to one engine. Lowercasing, as above, handles only the cosmetic part.

• Case and quoting. Compare identifiers the way your engine does. PostgreSQL folds unquoted names to lowercase.

• Schemas. The query filters on table_name alone. In a database with several schemas, add table_schema or the check will merge two tables that share a name.

• Views and materialized objects. information_schema.columns lists views too, but materialized views and external tables may need an engine-specific catalog.

Where it runs

Run the drift check as the last step of the job that builds the table and as a pull-request check, so a migration that adds a column fails until the dataset file is updated in the same change. A failing build is the pressure that keeps the descriptions alive; a warning is read once and ignored. Order the output by what to fix first: missing columns (the document lies), then undocumented ones (the document is incomplete), then type mismatches.

Do not demand descriptions for every column on day one. Start with the rule "every column present must be listed", which a script can enforce, then raise the bar to "every listed column has a non-empty description", then to a minimum length if you must. A repository with 200 tables will not become documented by a mandate. It becomes documented one pull request at a time, if each pull request has to leave its table complete. Pair the rule with an owner field and a nightly job that lists datasets with no owner or an owner who no longer exists in your directory.

dbt projects hold the same information in properties files, and dbt docs generate builds a site from it (writing dbt model properties in YAML, summarizing dbt project metadata from YAML). If you use dbt, document there; the approach in this post is for tables that live outside it.

When YAML dataset documentation is not worth keeping

Nobody reads it. If the tables are used by the team that wrote them, a sentence in a README may be enough. Count your readers before you build a catalog.

The table is temporary. Staging tables and scratch results do not need a dataset file. Document the boundaries, the tables other jobs and people consume.

The descriptions are copies of names. "id: the id" and "status: the status" add nothing. A check cannot tell, so reviewers must; a description should say what a name cannot (units, allowed values, who writes it, what a null means).

The schema changes weekly. If the shape is in flux, document the stable part and tolerate the rest with a pattern, or wait until it settles.

A catalog already exists. Duplicating its content in YAML creates two sources. Either feed the catalog from the files or stay in the catalog.

The metadata views are described in the DuckDB information schema documentation, and column comments in the PostgreSQL COMMENT reference. Tidy the file with the YAML formatter.