Introduction to testing in dbt

Intermediate dbt

Mike Metzger

Data Engineer

What is a test?

  • Assertions / validations of dbt objects
    • Models
    • Sources and seeds
  • Used to verify data is as expected
    • Null values
    • Values in range
    • Relationships between data
    • Custom tests (will be covered later)
Intermediate dbt

Test types

dbt offers three kinds of tests

  1. Built-in (4 pre-defined)
  2. Singular*
  3. Generic*

$$

  • will be covered later
Intermediate dbt

Built-in tests

  • unique
    • Verify all values are unique
  • not_null
    • Verify all values are not null
  • accepted_values
    • Verify all values are within a specific list
    • values: [a, b, c, d]
  • relationships
    • Verifies a connection to a specific table / column
    • to: ref('table')
    • field: id
Intermediate dbt

Where to apply tests?

  • models/model_properties.yml
    • File can be named anything
    • models/schema.yml
  • Defined in the tests: subheading
version: 2

models:
  - name: taxi_rides_raw
    columns:
      - name: tpep_pickup_datetime
        tests:
          - not_null
      - name: payment_type
        tests:
          - not_null
          - accepted_values:
              values: [1, 2, 3, 4, 5, 6]
Intermediate dbt

Running tests

  • dbt test
    • dbt test --select modelname
  • Verify output passes
  • If failure, check against compiled sql

dbt failed test output

Intermediate dbt

Finding failures

  1. Must find compiled SQL code
  2. In the target/compiled/projectname/models/model_properties.yml directory
    • target/compiled/nyc_yellow_taxi/models/model_properties.yml/
  3. Find the appropriate .sql file (matching the failed test)
  4. Copy the contents into database client and verify where issue exists
  5. Remove from data and re-run dbt run / dbt test
Intermediate dbt

Let's practice!

Intermediate dbt

Preparing Video For Download...