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

  • 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_null

How 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

  1. Load a sample of the table.
  2. Review and choose the rules.
  3. Save as dbt schema.yml for your warehouse.
  4. 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.