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.