Knowledge
dbt with Data Contracts: Stop drifting, start syncing
Introducing data contracts into a dbt project usually ends with the same schema maintained twice: once in the contract, once as dbt YAML. The two start drifting apart immediately.
datacontract dbt sync closes that copying gap: it merges the contract into an existing dbt project, in place.
Two commands do the work:
# find all data contracts in the current directory
# and ensure they are specified in your dbt model YAML files
datacontract dbt sync
# run all tests from the contract using dbt and report the results
datacontract dbt test
If we integrate those into CI, the contract stops being a document people are supposed to keep in step with the code. It becomes the direct source of truth for your dbt project.
In this article, we'll see how it works on a simple orders model.
The starting point
Let's assume that you have already set up a dbt project. If you are starting from scratch instead, you might want to take a look at our Data Product Builder first.
In our case, we start with this orders model on Databricks:
select
order_id,
internal_flag,
customer_id,
status,
amount,
ordered_at,
email
from {{ ref('stg_orders') }}
dbt sync does not require properties files to exist (if they don't, they'll just get generated). But in this case, we find that someone already documented the columns:
version: 2
models:
- name: orders
description: One row per order.
columns:
- name: order_id
description: Unique identifier for the order.
data_tests:
- unique
- not_null
# Internal ops field, deliberately outside the contract.
- name: internal_flag
description: Fulfilment queue marker used by the ops team.
data_tests:
- not_null
- name: customer_id
description: Identifier of the customer who placed the order.
data_tests:
- not_null
- relationships:
to: ref('customers')
field: customer_id
- name: status
description: Current status of the order.
data_tests:
- not_null
- name: amount
description: Total order amount.
data_tests:
- not_null
- name: ordered_at
description: Timestamp the order was placed.
data_tests:
- not_null
- name: email
description: Customer email address captured at order time.
data_tests:
- not_nullIn our example, we are working with a dbt project that already defined its models in properties files. dbt sync will edit them in-place (or generate new properties files if they don't exist).
Now, how to guarantee the shape of this data to consumers of our dbt project? Data contracts are the answer: a standalone, versioned document that states the schema, the types, the quality rules and who is accountable for them — readable by a consumer who has no access to our repo and no reason to learn dbt, and checkable by a machine. Let's start by adding a data contract from what we have.
Adding a data contract
There's no reason to write the first contract by hand. datacontract import dbt reads dbt's manifest.json and infers the columns, descriptions, primary keys, required fields and foreign keys from the tests and constraints already in the project.
So we let dbt produce the manifest first, then import from it:
dbt parse
datacontract import dbt \
--source target/manifest.json \
--model orders \
--id orders \
--output orders.odcs.yaml
From here, the contract is ours to shape. We drop internal_flag, an ops field that consumers have no business relying on, and add further specifics: the warehouse the data lives in, the four possible status values, more specific data types, the currency of the amount, or the length and shape of the email.
version: 1.0.0
kind: DataContract
apiVersion: v3.1.0
id: orders
name: orders_demo
status: draft
schema:
- name: orders
physicalType: view
description: One row per order.
logicalType: object
physicalName: orders
properties:
- name: order_id
description: Unique identifier for the order.
primaryKey: true
primaryKeyPosition: 1
logicalType: string
required: true
unique: true
- name: internal_flag
description: Fulfilment queue marker used by the ops team.
logicalType: string
required: true
- name: customer_id
description: Identifier of the customer who placed the order.
customProperties:
- property: references
value: customers.customer_id
logicalType: string
required: true
- name: status
description: Current status of the order.
logicalType: string
required: true
- name: amount
description: Total order amount.
logicalType: string
required: true
- name: ordered_at
description: Timestamp the order was placed.
logicalType: string
required: true
- name: email
description: Customer email address captured at order time.
logicalType: string
required: true
customProperties:
- property: dbt_version
value: 1.11.9version: 1.0.0
kind: DataContract
apiVersion: v3.1.0
id: orders
name: Orders
status: active
description:
purpose: One row per customer order, from checkout through fulfilment.
usage: Order volume and revenue reporting, and any downstream model that needs order state.
limitations: Rebuilt nightly, so not suitable for real-time use. Excludes baskets that never became orders.
servers:
- server: production
type: databricks
catalog: playground
schema: orders_demo
schema:
- name: orders
physicalName: orders
description: One row per order.
properties:
- name: order_id
description: Unique identifier for the order.
logicalType: integer
primaryKey: true
required: true
- name: customer_id
description: Identifier of the customer who placed the order.
logicalType: integer
required: true
relationships:
- to: customers.customer_id
- name: status
description: Current status of the order.
logicalType: string
required: true
customProperties:
- property: enum
value:
- placed
- shipped
- completed
- returned
- name: amount
description: Total order amount in USD.
logicalType: number
physicalType: DECIMAL(10,2)
required: true
- name: ordered_at
description: Timestamp the order was placed.
logicalType: timestamp
required: true
- name: email
description: Customer email address captured at order time.
logicalType: string
required: true
logicalTypeOptions:
maxLength: 255
pattern: "^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$"Syncing the contract into the project
After changing the contract, it might be tempting to manually port back the refinements to the dbt properties files. But we can do better: let's try out dbt sync!
datacontract dbt syncorders.odcs.yaml: Synced 1 model: updated 1 YAML file, wrote 2 singular SQL tests.
~ models/orders.yml
+ tests/datacontract_cli/orders/orders__1_0_0__orders__email__length.sql
+ tests/datacontract_cli/orders/orders__1_0_0__orders__email__pattern.sql
orders.odcs.yaml: 1 item in the project is not declared in the contract; pass --prune to remove:
- orders: column `internal_flag`
Run `datacontract dbt test` to execute the generated tests.
What happened? The Data Contract CLI first searched for data contracts in the current directory and its subdirectories (*.odcs.yaml). It found our newly added contract, as well as the existing properties file models/orders.yml. All information that was specified in the contract but not in the file got added, together with some meta information.
Two of the checks, namely the length and the pattern for emails, could not be expressed without adding external packages to the dbt project. To avoid that dependency, dbt sync generated singular SQL tests instead, which we'll see later.
By default, it does not delete anything from the properties files that is not explicitly overridden in the data contract. This applies to comments and manually added tests, but also to columns like our internal_flag that are missing from the contract. As the hint in the console output suggests, we can consider the --prune option if we want that column removed.
Let's take a look at the modified properties file. For instance, you can now find the accepted values for status, the updated description for amount, or auto-generated human-readable check names.
version: 2
models:
- name: orders
description: One row per order.
columns:
- name: order_id
description: Unique identifier for the order.
data_tests:
- unique
- not_null
# Internal ops field, deliberately outside the contract.
- name: internal_flag
description: Fulfilment queue marker used by the ops team.
data_tests:
- not_null
- name: customer_id
description: Identifier of the customer who placed the order.
data_tests:
- not_null
- relationships:
to: ref('customers')
field: customer_id
- name: status
description: Current status of the order.
data_tests:
- not_null
- name: amount
description: Total order amount.
data_tests:
- not_null
- name: ordered_at
description: Timestamp the order was placed.
data_tests:
- not_null
- name: email
description: Customer email address captured at order time.
data_tests:
- not_nullversion: 2
models:
- name: orders
description: One row per order.
columns:
- name: order_id
description: Unique identifier for the order.
data_tests:
- unique:
description: Check that field order_id has no duplicate values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__order_id__field_unique
contract_versions:
- 1.0.0
generated: false
- not_null:
description: Check that field order_id has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__order_id__field_required
contract_versions:
- 1.0.0
generated: false
data_type: INT
# Internal ops field, deliberately outside the contract.
- name: internal_flag
description: Fulfilment queue marker used by the ops team.
data_tests:
- not_null
- name: customer_id
description: Identifier of the customer who placed the order.
data_tests:
- not_null:
description: Check that field customer_id has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__customer_id__field_required
contract_versions:
- 1.0.0
generated: false
- relationships:
to: ref('customers')
field: customer_id
description: Check that field customer_id references ref('customers').customer_id
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__customer_id__field_relationships
contract_versions:
- 1.0.0
generated: false
data_type: INT
- name: status
description: Current status of the order.
data_tests:
- not_null:
description: Check that field status has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__status__field_required
contract_versions:
- 1.0.0
generated: false
- accepted_values:
values:
- placed
- shipped
- completed
- returned
config:
meta:
datacontract_cli:
check: orders__status__field_enum
include_in_tests: true
contract_versions:
- 1.0.0
generated: true
description: Check that field status only contains enum values ['placed', 'shipped', 'completed', 'returned']
data_type: STRING
- name: amount
description: Total order amount in USD.
data_tests:
- not_null:
description: Check that field amount has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__amount__field_required
contract_versions:
- 1.0.0
generated: false
data_type: DECIMAL(10,2)
- name: ordered_at
description: Timestamp the order was placed.
data_tests:
- not_null:
description: Check that field ordered_at has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__ordered_at__field_required
contract_versions:
- 1.0.0
generated: false
data_type: TIMESTAMP
- name: email
description: Customer email address captured at order time.
data_tests:
- not_null:
description: Check that field email has no missing values
config:
meta:
datacontract_cli:
include_in_tests: true
check: orders__email__field_required
contract_versions:
- 1.0.0
generated: false
data_type: STRING
config:
meta:
datacontract_cli:
contract_id: orders
Running dbt sync a second time now does nothing at all:
orders.odcs.yaml: Synced 1 model: updated 0 YAML files.
orders.odcs.yaml: 1 item in the project is not declared in the contract; pass --prune to remove:
- orders: column `internal_flag`
Run `datacontract dbt test` to execute the generated tests.The tests we get, including the ones dbt can't express
Every rule in the contract maps to a real dbt test: required to not_null, primaryKey to unique, an enum to accepted_values, and a relationships entry to dbt's relationships test with the target rewritten to a ref(). Sync writes the ones the project is missing and adopts the ones it already has.
But some properties like maxLength and pattern have no generic dbt test. Therefore, they get expressed as singular SQL tests:
-- AUTO-GENERATED by `datacontract dbt sync`. Do not edit.
-- Source contract: orders@1.0.0 (model: orders, check: email__length)
{{ config(meta={"datacontract_cli": {"check": "orders__email__field_length", "contract_versions": ["1.0.0"], "generated": true, "include_in_tests": true, "model": "orders", "field": "email", "description": "Check that field email has a length of at most 255"}}) }}
SELECT *
FROM {{ ref('orders') }}
WHERE "email" IS NOT NULL
AND (LENGTH("email") > 255)Running the tests
Now that the contract is in sync with our dbt project, we could just use dbt's built-in dbt test command to verify the shape of our data. But now that we are working with data contracts, datacontract dbt test provides a wrapper command with some extra features: it scopes to one or more data contracts, displays human-friendly check names, changes its return code depending on the check results, and offers to export the check results.
datacontract dbt test orders.odcs.yamlContract orders@1.0.0 — /Users/you/orders-demo/orders.odcs.yaml
╭────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────┬─────────╮
│ Result │ Check │ Field │ Details │
├────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────┼─────────┤
│ passed │ Check that field amount has no missing values │ amount │ │
│ passed │ Check that field customer_id has no missing values │ customer_id │ │
│ passed │ Check that field customer_id references ref('customers').customer_id │ customer_id │ │
│ passed │ Check that field email has no missing values │ email │ │
│ passed │ Check that field email has a length of at most 255 │ email │ │
│ passed │ Check that field email matches regex pattern ^[^@\s]+@[^@\s]+\.[^@\s]+$ │ email │ │
│ passed │ Check that field order_id has no missing values │ order_id │ │
│ passed │ Check that field order_id has no duplicate values │ order_id │ │
│ passed │ Check that field ordered_at has no missing values │ ordered_at │ │
│ passed │ Check that field status only contains enum values ['placed', 'shipped', 'completed', 'returned'] │ status │ │
│ passed │ Check that field status has no missing values │ status │ │
╰────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴─────────╯
🟢 dbt tests passed. Ran 11 tests. Took 0.00406 seconds.
Seed a bad status value and the same command reports it, in the contract's own words rather than dbt's:
Contract orders@1.0.0 — /Users/you/orders-demo/orders.odcs.yaml
╭────────┬───────────────────────────────────────────────────────┬─────────────┬────────────────────────────────────────────────────╮
│ Result │ Check │ Field │ Details │
├────────┼───────────────────────────────────────────────────────┼─────────────┼────────────────────────────────────────────────────┤
│ failed │ Check that field status only contains enum values │ status │ failures=1 | Got 1 result, configured to fail if │
│ │ ['placed', 'shipped', 'completed', 'returned'] │ │ != 0 │
│ passed │ Check that field amount has no missing values │ amount │ │
│ passed │ Check that field customer_id has no missing values │ customer_id │ │
│ passed │ Check that field customer_id references │ customer_id │ │
│ │ ref('customers').customer_id │ │ │
│ passed │ Check that field email has no missing values │ email │ │
│ passed │ Check that field email has a length of at most 255 │ email │ │
│ passed │ Check that field email matches regex pattern │ email │ │
│ │ ^[^@\s]+@[^@\s]+\.[^@\s]+$ │ │ │
│ passed │ Check that field order_id has no missing values │ order_id │ │
│ passed │ Check that field order_id has no duplicate values │ order_id │ │
│ passed │ Check that field ordered_at has no missing values │ ordered_at │ │
│ passed │ Check that field status has no missing values │ status │ │
╰────────┴───────────────────────────────────────────────────────┴─────────────┴────────────────────────────────────────────────────╯
🔴 dbt tests failed, found the following errors:
1) status Check that field status only contains enum values ['placed', 'shipped', 'completed', 'returned']: failures=1 | Got 1
result, configured to fail if != 0Making it true, not just intended
Everything so far still relies on people remembering to run sync. That's not a source of truth, that's a habit. If we hand the job to CI instead, it stops being optional.
There's no --check flag, and there doesn't need to be one: sync is idempotent, so re-syncing and diffing is the check. If someone edited a contract-owned field in the dbt YAML directly, sync overwrites it back and the working tree is dirty. If nobody did, sync changes nothing and the diff is empty.
- name: Sync data contracts into the dbt project
run: datacontract dbt sync contracts/*.odcs.yaml
- name: Fail if the dbt project has drifted from the contracts
run: git diff --exit-code
- name: Run the contract's tests
run: |
datacontract dbt test contracts/*.odcs.yaml \
--publish https://api.entropy-data.com/api/test-results
Drift is no longer something we discover months later when a consumer's dashboard breaks. It's a red build, on the commit that caused it, with a diff naming exactly which field disagreed. Changing the schema now means changing the contract, because changing anything else doesn't survive CI.
Publishing the results for data consumers
A contract that only exists in a team's repo is visible only to the team. But we can fix that: add --publish to send each test run to Entropy Data. Anyone interested in using the orders table can see for themselves if the guarantees they were promised are holding, over time, without having to ask.
Summary
Introducing data contracts into a hand-maintained dbt project leads to drift between the two. The fix isn't more discipline, it's removing the second copy: keep the schema in the contract, sync it into the project, and let CI refuse any commit where the two disagree. What you get in return is a dbt project that is still yours, but transparent in its guarantees for consumers.
Sign up on Entropy Data for free, or explore the clickable demo.