From Data Contract to dbt Tests: Generate schema.yml From a File
Writing a dbt schema.yml by hand, column by column, is slow and easy to leave half done. The Data Contract tool can write it for you from a sample file: the same rules as your contract, expressed as dbt tests. This guide shows the output, what it covers and what it leaves out, so you know what to add yourself.
Open the Data Contract Generator
The workflow
- Load a sample of the table in the Data Contract tool.
- Review the suggested rules and switch some on or off.
- Choose the dbt output instead of ODCS, and the warehouse dialect so the data types match.
- Download the `schema.yml` and put it next to the model it describes.
What it writes
This is the real output for an orders table, with the valid-values rule switched on for `status`:
version: 2
models:
- name: orders
description: Generated from a data contract. Review every test before you rely on it.
columns:
- name: order_id
description: ""
data_type: bigint
tests:
- unique
- not_null
- name: customer_email
description: ""
data_type: varchar
tests:
- not_null
- name: status
description: ""
data_type: varchar
tests:
- not_null
- accepted_values:
values:
- paid
- pending
- refunded
- name: amount
description: ""
data_type: float
tests:
- not_null
- name: order_date
description: ""
data_type: date
tests:
- not_nullHow contract rules map to dbt tests
| Contract rule | dbt test |
| --- | --- |
| Required | `not_null` |
| Unique | `unique` |
| Allowed values | `accepted_values` |
| Type | `data_type` on the column |
dbt has no built-in test for a range, a length, a pattern, a minimum row count or duplicate rows. Those need a dbt package or a custom test. The tool has an option for tests that depend on a package; leave it off if you do not use one, and add your own for these rules.
After you generate it
- **Add descriptions.** The `description` fields are empty on purpose. Fill them in, because they become your documentation.
- **Check the data types.** They are written for the dialect you chose. Compare them with the real column types in your warehouse.
- **Check the key name.** The file uses the `tests:` key. If your dbt version or project uses `data_tests:`, rename it.
- **Run it.** `dbt test` runs the tests against the real model, which is stronger than checking a file once.
When to use the contract file, and when dbt
Use the ODCS contract when the data crosses a boundary, such as a file from a vendor or a table another team reads. Use dbt tests for tables you build yourself, so they run on every build. Many teams keep both, generated from the same rules. See data contract example for the comparison.
Know the limits
- The rules come from the sample file you loaded. Review every test before relying on it, as the generated description says.
- It writes the tests and types for one model. Relationships between models (`relationships` tests) are not generated.
- To see what a model change would break downstream, use the SQL Lineage Analyzer on the project. It reads SQL text, so it needs no warehouse connection.
Open the Data Contract Generator
Related tool pages
- ODCS Data Contract Generator: Generate an Open Data Contract Standard (ODCS) v3 YAML file from a CSV, Excel, JSON or Parquet file. Edit the rules, then download. Free, in your browser.
- Validate a CSV Against a Schema: Check a CSV or Excel file against a data contract: required values, types, unique columns, allowed values, ranges, patterns and missing or extra columns. Free, in your browser.
Also try
How to use the Data Contract Generator: step by step
- Load a sample of the table.
- Review and choose the rules.
- Save as dbt schema.yml for your warehouse.
- Add descriptions and run dbt test.
When to use the Data Contract Generator
- Generating a first schema.yml
- Adding not_null, unique and accepted_values tests
- Keeping contract and dbt tests in step
Example
Input: orders.csv: order_id, status, unit_price
Output: kind: DataContract schema: - name: orders properties: - name: order_id logicalType: integer required: true unique: true
Frequently asked questions
How do I generate a dbt schema.yml from a file?
Load a sample in the Data Contract tool, review the rules, and save the result as a dbt schema.yml for your warehouse dialect.
Which dbt tests does it write?
not_null, unique and accepted_values, plus the column data types. Tests for ranges, lengths, patterns and row counts need a package or a custom test.
Does it support dbt packages?
There is an option for tests that depend on a package. Leave it off if you do not use one.
Should I trust the generated tests?
Treat them as a first draft from one sample. Review each one before you rely on it.
Tip: Generate, then read every test. Delete the ones you would not defend. A failing test you do not understand teaches the team to ignore failing tests.