1. Testing, Testing…

You can find the video summary for this section here.

dbt Tests allow you to validate your data models in either:

  • Models

  • Freshness of data

  • and other items

There are two type of tests:

  • Built-in, default, schema tests: applied in the .yml file, returns the number of records that do not pass an assertion

  • Custom data tests

Our tests our configured in our dbt Repo in the dbt_project.yml configuration

test-paths: ["tests"]

The following is a visual example showing the default, custom, and path for our tests.


1.1. Default tests

The following is a great way to get started on dbt Tests:


As an example, we work within the .yml files to create “schema tests”: Using dbt test command, we test the following schema configurations:

-- mart_schema.yml
version: 2

  - name: some_table_name
      - name: order_id
          - unique
          - not_null
      - name: status
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

1.2. Custom Tests

Notice, there is a test/ folder in our repository. This is a place reserved for creating custom data tests outside of our built-in tests.

For example, let’s create a test assert_number_of_rows_catalog_sales.sql in the test folder

-- test/assert_number_of_rows_catalog_sales.sql
-- Count the total number of rows >= 0.
-- Therefore return records where this isn't true to make the test fail.
-- Note: This is an equivalent to yml confiug tests -not_null.

    COUNT(*) AS total_rows
FROM {{ ref('catalog_sales_fct' )}}
HAVING not(total_rows >= 0)

You can find so much more about dbt tests from the dbt team here.