Unit testing dbt models - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main purpose of unit testing in dbt models?

Unit testing in dbt models ensures that each model produces the correct output by testing small parts of the data transformation logic independently.

beginner

How do you define a test for a dbt model?

You define a test by writing SQL queries that check for expected conditions, such as no null values or unique keys, and then include these tests in your dbt project.

beginner

What is a common built-in test type in dbt for checking uniqueness?

The built-in test type 'unique' checks that all values in a column are unique, helping to catch duplicate records.

intermediate

Why is it important to run dbt tests regularly during development?

Running dbt tests regularly helps catch errors early, ensures data quality, and prevents broken models from affecting downstream analysis.

intermediate

What is the difference between schema tests and data tests in dbt?

Schema tests check data properties like uniqueness or null values defined in the schema.yml file, while data tests are custom SQL queries that validate specific business logic or data conditions.

Which dbt test checks that a column has no null values?

A relationships

B unique

C accepted_values

D not_null

The 'not_null' test ensures that there are no null values in the specified column.

Where do you typically define schema tests in a dbt project?

A In the model SQL file

B In the dbt_project.yml file

C In the schema.yml file

D In the profiles.yml file

Schema tests are defined in the schema.yml file alongside model definitions.

What command runs all tests in a dbt project?

A dbt run

B dbt test

C dbt compile

D dbt seed

The 'dbt test' command runs all tests defined in the project.

Which type of test would you write to check a complex business rule in dbt?

A Data test

B Schema test

C Unique test

D Not null test

Data tests are custom SQL queries used to validate complex business rules.

What happens if a dbt test fails?

A The test result is logged and the failure is reported

B The dbt project automatically fixes the error

C The model is deleted

D Nothing, tests do not affect the project

When a test fails, dbt reports the failure so you can investigate and fix the issue.

Explain how you would create and run a unit test for a dbt model to check for duplicate values in a column.

Think about using built-in tests and the dbt test command.

Describe the difference between schema tests and data tests in dbt and give an example of when to use each.

Consider the scope and complexity of the test.

Practice

1. What is the main purpose of unit testing in dbt models?

easy

A. To check data models for correctness and quality

B. To create new data models from scratch

C. To deploy dbt projects to production

D. To write SQL queries faster

Solution

1. Step 1: Understand unit testing in dbt

   Unit testing in dbt is designed to verify that data models behave as expected and contain correct data.

2. Step 2: Identify the main goal

   The main goal is to check correctness and quality of data models, not to create or deploy them.

3. Final Answer:

   To check data models for correctness and quality -> Option A

4. Quick Check:

   Unit testing = correctness and quality check [OK]

Hint: Unit tests check data correctness, not creation or deployment [OK]

Common Mistakes:

• Confusing testing with model creation
• Thinking tests deploy models
• Assuming tests speed up SQL writing

2. Which of the following is the correct syntax to define a unique test on a column in a dbt YAML file?

easy

A. tests:
- unique: column_name

B. tests: unique: column_name

C. tests: - unique: column_name

D. tests:
unique: - column_name

Solution

1. Step 1: Recall YAML test syntax in dbt

   Tests are listed as a YAML list under the 'tests' key, each with a dash and test name.

2. Step 2: Match correct indentation and list format

   tests: - unique: column_name shows 'tests:' followed by a list item '- unique: column_name', which is correct YAML syntax for dbt tests.

3. Final Answer:

   tests: - unique: column_name -> Option A

4. Quick Check:

   YAML lists use dash (-) before items [OK]

Hint: YAML lists need dash (-) before each test [OK]

Common Mistakes:

• Missing dash (-) before test name
• Incorrect indentation
• Placing test name and column incorrectly

3. Given this YAML snippet for a dbt test:

columns:
  - name: user_id
    tests:
      - unique
      - not_null

What will dbt test check for the user_id column?

medium

A. It checks that user_id values are duplicated and null

B. It checks that user_id values are unique and not null

C. It only checks if user_id is unique

D. It only checks if user_id is not null

Solution

1. Step 1: Read the tests listed for user_id

   The YAML shows two tests: 'unique' and 'not_null' for the user_id column.

2. Step 2: Understand what each test does

   'unique' ensures no duplicate values; 'not_null' ensures no missing values.

3. Final Answer:

   It checks that user_id values are unique and not null -> Option B

4. Quick Check:

   unique + not_null = no duplicates, no nulls [OK]

Hint: Multiple tests run together on the same column [OK]

Common Mistakes:

• Thinking tests check opposite conditions
• Assuming only one test runs
• Confusing unique with not_null

4. You wrote this test in your YAML file:

columns:
  - name: order_id
    tests:
      unique
      not_null

But when you run dbt test, it fails with a syntax error. What is the likely cause?

medium

A. Using 'tests' instead of 'test' key

B. Incorrect indentation of 'name' key

C. Missing quotes around test names

D. Missing dash (-) before 'unique' and 'not_null' tests

Solution

1. Step 1: Check YAML list syntax for tests

   Each test under 'tests' must be a list item starting with a dash (-).

2. Step 2: Identify missing dashes

   The snippet shows 'unique' and 'not_null' without dashes, causing YAML syntax error.

3. Final Answer:

   Missing dash (-) before 'unique' and 'not_null' tests -> Option D

4. Quick Check:

   YAML lists need dash (-) before each item [OK]

Hint: Always put dash (-) before each test in YAML list [OK]

Common Mistakes:

• Omitting dash (-) before tests
• Wrong indentation levels
• Confusing 'tests' with 'test' key

5. You want to ensure that the email column in your users model is always unique and never empty. Which YAML test configuration correctly applies these tests and how would you run them?

hard

A. Define in YAML:columns:
- email:
tests:
- unique
- not_nullThen run dbt test

B. Define in YAML:columns:
- name: email
tests:
unique
not_nullThen run dbt run

C. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt test

D. Define in YAML:columns:
- name: email
tests:
- unique
- not_nullThen run dbt run

Solution

1. Step 1: Write correct YAML test syntax

   The tests must be listed as a YAML list with dashes under 'tests' for the 'email' column, as shown in Define in YAML:
   

   columns:
     - name: email
       tests:
         - unique
         - not_null

   
   Then run dbt test.

2. Step 2: Choose the correct dbt command to run tests

   To execute tests, you must run dbt test, not dbt run. So this configuration with dbt test is correct.

3. Final Answer:

   Define tests with dashes and run dbt test -> Option C

4. Quick Check:

   YAML list + dbt test command = correct testing [OK]

Hint: Use dashes for tests and run 'dbt test' to execute [OK]

Common Mistakes:

• Running 'dbt run' instead of 'dbt test'
• Incorrect YAML list syntax
• Misnaming column key