Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 41 ยท Projects and Practice

dbt Practice Exercises

Twelve dbt exercises on tests, documentation, materializations, incremental logic, packages and CI, with solutions. SQL parts run in your browser.

Reading about dbt and using it are different skills. These twelve exercises cover tests, documentation, materializations, incremental models, packages, environments and CI. Because dbt itself cannot run on this site, each exercise has two kinds of work: writing dbt configuration (YAML and Jinja, which you do on paper or in your own project and compare with the solution) and writing the SQL that dbt would run (which you can execute in the editors on the shop and bookstore databases).

Try each one before opening the solution. Your answer may differ and still be right.

Tests

1. Write the YAML (Beginner)

stg_orders has a primary key order_id, a foreign key customer_id to stg_customers.customer_id, and a status column with values pending, shipped, delivered, cancelled. Write the properties file with every sensible generic test.

Show solution
YAML
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['pending', 'shipped', 'delivered', 'cancelled']

On older dbt versions use tests: instead of data_tests: and put to and field directly under relationships. See relationships tests.

2. The SQL of a test (Beginner)

Write the query that not_null runs on customers.email, and the one unique runs on it.

SQL Exercise 2: not_null and unique on email ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT id FROM customers WHERE email IS NULL;

SELECT email, COUNT(*) AS n_records
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Both return no rows on the sample data, so both tests would pass.

3. A singular test (Intermediate)

Write a singular test that fails when an order item's unit_price differs from the product's current price by more than 50 percent. Return the failing items.

SQL Exercise 3: price drift ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
-- tests/assert_item_prices_close_to_list_price.sql
select i.id, i.unit_price, p.price
from {{ ref('stg_order_items') }} as i
join {{ ref('stg_products') }} as p on p.product_id = i.product_id
where abs(i.unit_price - p.price) > 0.5 * p.price

Run the SQL part (without Jinja) in the editor to see how many items would fail on the sample data; a business rule like this one often needs a threshold or warn severity.

Documentation

4. Doc block (Beginner)

Write a doc block for order_status and reference it from a column description.

Show solution
MARKDOWN
{% docs order_status %}
The lifecycle state of an order: pending, shipped, delivered or cancelled.
{% enddocs %}
YAML
- name: status
  description: "{{ doc('order_status') }}"

Materializations

5. Pick the materialization (Beginner)

For each model choose view, table, ephemeral or incremental: (a) stg_customers, 12,000 rows, only used by other models; (b) fct_page_views, 4 billion rows, append-only; (c) dim_products, 3,000 rows, queried by a dashboard; (d) a 10-line helper used by one model.

Show solution

(a) view, (b) incremental (append or microbatch), (c) table, (d) ephemeral. See materializations.

6. First run versus later runs (Intermediate)

Simulate an incremental model: create fct_books from books on the first run, add two new books to the source, then load only rows with id greater than the maximum already loaded.

SQL Exercise 6: incremental simulation ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE TABLE fct_books AS SELECT * FROM books WHERE id <= 8;

INSERT INTO books (id, title, author_id, genre, published, price, stock)
VALUES (101, 'New Arrival A', 1, 'Fiction', 2025, 12.0, 5),
       (102, 'New Arrival B', 1, 'Fiction', 2025, 14.0, 7);

INSERT INTO fct_books
SELECT * FROM books WHERE id > (SELECT MAX(id) FROM fct_books);

SELECT COUNT(*) FROM fct_books;

The final count is 12: the first eight books, then books 9 and 10 and the two new ones, because all of them have an id above the loaded maximum (8). The filter is what is_incremental() guards.

7. Spot the bug (Intermediate)

An incremental model filters with where occurred_at > (select max(occurred_at) from {{ this }}). The source sometimes delivers events a day late. What goes wrong and how do you fix it?

Show solution

Late rows have older timestamps than the current maximum, so they never pass the filter. Fix by filtering on a load timestamp (loaded_at), or by reprocessing a lookback window with unique_key set. Details in late-arriving data.

8. Upsert (Intermediate)

Write an upsert that updates a book's price when the id exists and inserts it otherwise (the idea behind merge).

SQL Exercise 8: merge as upsert ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE TABLE book_prices (id INTEGER PRIMARY KEY, price REAL);
INSERT INTO book_prices SELECT id, price FROM books;

INSERT INTO book_prices
SELECT id, price FROM price_updates WHERE true
ON CONFLICT (id) DO UPDATE SET price = excluded.price;

SELECT * FROM book_prices WHERE id IN (1, 500);

Packages, environments, CI

9. packages.yml (Beginner)

Add dbt_utils so that any 1.x version from 1.1.0 upward is allowed, and install it.

Show solution
YAML
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]
Terminal
dbt deps

10. Schema naming (Intermediate)

With +schema: marts on a folder and the default generate_schema_name, in which schema does a model land for target schema dbt_ben? And in analytics?

Show solution

dbt_ben_marts and analytics_marts. Override generate_schema_name to use marts alone in production. See targets.

11. Slim CI command (Intermediate)

Write the command that builds only changed models and their children in CI, reading unbuilt parents from production.

Show solution
Terminal
dbt build --select state:modified+ --defer --state prod-artifacts

12. Freshness as SQL (Intermediate)

Write the query a freshness check would run: report the newest orders.ordered_at and its age in days, measured from 2025-12-31.

SQL Exercise 12: freshness in days ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT MAX(ordered_at) AS newest,
       CAST(julianday('2025-12-31') - julianday(MAX(ordered_at)) AS INTEGER) AS days_old
FROM orders;

A source freshness check compares exactly this age with warn_after and error_after. See deploying jobs.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. A test returns zero rows. What does it mean?

    Failing rows indicate problems, so none means success.
  2. 2. Which is the best materialization for a 4-billion-row append-only table?

    Rebuilding that table every run would be very slow and expensive.
  3. 3. What does dbt build --select state:modified+ need?

    State selection compares the project with the manifest in the state folder.

Summary

  • Tests, docs, materializations and CI each have a small number of core patterns.
  • Practice writing the SQL that dbt generates, since that is what runs in the warehouse.
  • Compare your answers with the solutions and revisit the matching lessons.

Next: dbt Interview Questions