Properties in YAML

Concepts covered: Data tests

Tests on a column are declared in a YAML properties file in the models folder, under the model's name and then the column's name. dbt reads every .yml file under the project's model paths when it parses the project, so the file's name is yours to pick and 1 file can describe several models. What ties a block to a model is the name under models:, which must match the model's file name without .sql.

models:
  - name: stg_orders
    description: One row per order placed in the shop.
    columns:
      - name: order_id
        description: Assigned by the checkout service when the order is placed.
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'delivered']
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

Tests under data_tests: come in 2 shapes. A test with nothing to configure, such as unique, is written as its bare name, and not_null is written the same way. A test that needs input is a mapping whose single key is the test name, with the inputs beneath arguments:. accepted_values needs its list, and relationships needs the model to search and the column there. The to: value uses ref() just as a model would, so dbt draws an edge from stg_customers to the test and builds that model before the test runs.

The keys dbt reads in a properties file

models: one entry per model the file describes
name: the model's file name, or the column as the SELECT names it
description: stored with the node, shown to people, never run
data_tests: bare test names, or a test name over its arguments
arguments: the inputs the test's template needs

The file becomes 5 test nodes

Each entry under data_tests: becomes a node with a name dbt assembles from the test, the model, the column and, where present, the arguments. The file above yields 5 test nodes, named as follows.

Declared on	Test	Node name dbt prints
order_id	unique	unique_stg_orders_order_id
order_id	not_null	not_null_stg_orders_order_id
status	accepted_values	accepted_values_stg_orders_status__placed__shipped__delivered
customer_id	not_null	not_null_stg_orders_customer_id
customer_id	relationships	relationships_stg_orders_customer_id__customer_id__ref_stg_customers_

Those names are what the build prints, so a line reading FAIL 2 not_null_stg_orders_customer_id is enough to tell which rule failed and on which model column, without opening the file. The argument values are part of the name, which is why adding cancelled to the accepted list produces a test node with a new name and the old one disappears from the project.

After the file is saved, dbt test parses it, compiles each of the 5 nodes into the query the previous section showed, sends them in turn and prints 5 lines. The accepted list travels into the not in clause. For relationships, to: and field: become the parent query, and in every template the column name is pasted wherever order_id or status stood.

Descriptions and columns dbt never verifies

description is text dbt stores with the model or column it describes. People read it and dbt can generate documentation from it, but it changes no SQL. The columns: list is also a declaration rather than a check. dbt does not compare it with the warehouse when it parses, so a column that exists only in the YAML surfaces when a test on it runs and the warehouse answers that the column does not exist. That outcome prints as ERROR, meaning the query itself broke, which is different from FAIL, meaning the query ran and returned rows.

Put a not_null test on 3 versions of the same column and the 3 outcomes separate cleanly. The typo never reaches the rows at all: the warehouse refuses the query, so there is no count to report, and dbt prints ERROR with the warehouse's message in the log.

not_null declared on	What the warehouse does	Printed
order_id, with 1 null in the model	runs the query, returns 1 row	FAIL 1
order_idd, a typo	rejects the query: no such column	ERROR
order_id, every row filled	runs the query, returns 0 rows	PASS

Do

Match each name to the model file and the column as the SELECT spells it
Put unique and not_null together on every id column
Nest a test's inputs under arguments, with to written as a ref()
Keep the properties file beside the models it describes

Avoid

Declare a test on a column the model does not select; it will ERROR, never PASS
Rely on accepted_values alone to catch a null category
Misspell a model name in YAML; dbt only warns, and that block's tests never run
Expect description to validate anything

A project with many models usually keeps 1 properties file per folder of models, each named for what the folder holds, because a single file for every model in the project grows past what anyone will read.

About this lesson

This section is part of the Testing: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.