Introduction to testing in DBT

What is a test

---

• Assertions / validations of dbt object
  • Models
  • Sources, seeds, snapshots
• Used to verify data is as expected
  • Null values
  • Values in range
  • Relationships between data
  • Custom tests

Test Types

---

• Build-in
• Singular
• Generic

Built-in tests

---

• unique
  • Verify all values are unique
• not_null
  • Verify all values are not null
• accepted_values
  • Verfiy 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

Where to apply tests?

---

• models/model_properties.yml
  • File can be named anything
  • models/schema.yml
• Defined in the tests: subheading
  실제 테스트는 YAML 내 열 이름 옵션 아래의 테스트 하위 제목 아래에 정의됩니다.

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]

Running Test

---

• dbt test
  • dbt test --select modelname
    특정 모델에 대한 테스트만 하고 싶다면
• Verify output passes
• If failure, check against compiled sql
  만약 테스트가 실패한 부분을 찾으려면, 몇가지 단계를 더 수행해야합니다.

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 verfiy where issues exists
   쿼리를 DB에서 직접 실행해서, 문제를 찾아봅니다.
5. Remove from data and re-run dbt run / dbt test