Test and document your project

Add tests to your models

Adding tests to a project helps validate that your models are working correctly. So let's add some tests to our project!

• dbt Cloud
• dbt CLI

1. Create a new YAML file in the models directory, named models/schema.yml
2. Add the following contents to the file:

models/schema.yml

version: 2

models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

3. Execute dbt test, and confirm that all your tests passed.

• dbt Cloud
• dbt CLI

Passing tests when using dbt Cloud

info

When you run dbt test, dbt iterates through your YAML files, and constructs a query for each test. Each query will return the number of records that fail the test. If this number is 0, then the test is successful.

FAQs

 What tests are available for me to use in dbt? Can I add my own custom tests?

Out of the box, dbt ships with the following tests:

• unique
• not_null
• accepted_values
• relationships (i.e. referential integrity)

You can also write your own custom schema tests.

Some additional custom schema tests have been open-sourced in the dbt-utils package, check out the docs on packages to learn how to make these tests available in your project.

 How do I test one model at a time?

Running tests on one model looks very similar to running a model: use the --models flag (or -m flag), followed by the name of the model:

dbt test --models customers

Check out the model selection syntax documentation for full syntax, and test selection examples in particular.

 One of my tests failed, how can I debug it?

To debug a failing test, find the SQL that dbt ran by:

• dbt Cloud:
  • Within the test output, click on the failed test, and then select "Details"
• dbt CLI:
  • Open the file path returned as part of the error message.
  • Navigate to the target/compiled/schema_tests directory for all compiled test queries

Copy the SQL into a query editor (in dbt Cloud, you can paste it into a new Statement), and run the query to find the records that failed.

 Does my test file need to be named `schema.yml`?

No! You can name this file whatever you want (including whatever_you_want.yml), so long as:

• The file is in your models/ directory¹
• The file has .yml extension

Check out the docs for more information.

¹If you're declaring properties for seeds, snapshots, or macros, you can also place this file in the related directory — data/, snapshots/ and macros/ respectively.

 Do all my tests go in one file?

No! You can use as many files as you want! Some folks find it useful to have one file per model, we tend to have one per directory.

 Why do model and source yml files always start with `version: 2`?

Once upon a time, the structure of these .yml files was very different (s/o to anyone who was using dbt back then!). Adding version: 2 allowed us to make this structure more extensible.

Currently, Version 2 is the only supported version for these files. We kept version: around as a required key so that in the future, if we need to introduce a new structure for these files, we'll be able to do this more easily.

 What tests should I add to my project?

We recommend that every model has a test on a primary key, that is, a column that is unique and not_null.

We also recommend that you test any assumptions on your source data. For example, if you believe that your payments can only be one of three payment methods, you should test that assumption regularly — a new payment method may introduce logic errors in your SQL.

In advanced dbt projects, we recommend using sources and running these source data-integrity tests against the sources rather than models.

 When should I run my tests?

You should run your tests whenever you are writing new code (to ensure you haven't broken any existing models by changing SQL), and whenever you run your transformations in production (to ensure that your assumptions about your source data are still valid).

Document your models

Adding documentation to your project allows you to describe your models in rich detail, and share that information with your team. Here, we're going to add some basic documentation to our project.

• dbt Cloud
• dbt CLI

1. Update your models/schema.yml file to include some descriptions, such as those below.

models/schema.yml

version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: first_order_date
        description: NULL when a customer has not yet placed an order.

  - name: stg_customers
    description: This model cleans up customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null

  - name: stg_orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

2. Execute dbt docs generate to generate the documentation for your project. dbt introspects your project and your warehouse to generate a json file with rich documentation about your project.
3. [CLI] Execute dbt docs serve to launch the documentation in a local website. [Cloud] Click the link above the file tree in the develop interface to launch documentation in a new tab.

tip

Great work ⭐️! You've just built your first dbt project that's tested and documented!

FAQs

 How do I write long-form explanations in my descriptions?

If you need more than a sentence to explain a model, you can:

1. Split your description over multiple lines (yaml docs), like so:

version: 2

models:
- name: customers
  description: >
    Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
    tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
    quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
    consequat.

2. Use a docs block to write the description in a Markdown file.

 How do I share my documentation with my team members?

If you're using dbt Cloud to deploy your project, and have the Team Plan, you can have up to 50 read only users, who will be able access the documentation for your project.

Extra exercises

• dbt Cloud
• dbt CLI

• Write a test that fails, for example, omit one of the order statuses in the accepted_values list. What does a failing test look like? Can you debug the failure?
• Run the tests for one model only. If you grouped your stg_ models into a directory, try running the tests for all the models in that directory.
• Use a docs block to add a Markdown description to a model.