learning-it by example

#14 Data engineering and pipelines 6.2 min

dbt and DuckDB: A Full Project, Tests, and a Bad Row

Build a complete dbt project on DuckDB with models and tests, then watch a test catch a bad row that dbt run lets through.

1. What does the ref function do in a dbt model?
2. In the dbt and DuckDB shop project with clean data, what did dbt run and dbt test report?
3. After a second copy of order 4 was added, dbt run still passed. What does that show?
4. In a dbt profile for DuckDB the path is shop.duckdb. From which folder is that relative path resolved when dbt runs?
5. What does a YAML test such as unique on a column turn into when dbt test runs?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Add the duplicate by hand

    Copy the code folder first. Run python add_duplicate.py, then open models/raw_orders.sql and find the row it added. Run add_duplicate.py a second time. Before you open the file again, predict how many rows for order 4 it will contain, then check. This step needs only Python.

    A hint

    The script replaces the text of the last row with the last row plus one more copy of order 4.

  2. Test the customer column

    In models/schema.yml add a not_null test for the customer column of stg_orders. Predict how many tests dbt test will run and whether they all pass on the original data, then run dbt run and dbt test. This needs dbt-core, dbt-duckdb and duckdb at the versions in requirements.txt.

    A hint

    Every order in raw_orders has a customer.

  3. Break the profile name

    In dbt_project.yml change profile: shop to profile: shopp and run dbt run. Predict what kind of problem dbt reports. Put the name back afterwards.

    A hint

    The video says the profile name in both files must match exactly, or dbt cannot find it.

  4. Test the raw model too

    Run add_duplicate.py once on a fresh copy. Add a unique test for the id column of raw_orders in schema.yml. Predict how many tests dbt test will run now and which of them will fail, then run dbt run and dbt test.

    A hint

    Test names follow the pattern unique_modelname_columnname, as in unique_stg_orders_id.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

A tiny made-up project with one planted duplicate row. A passing run only proves the tests you wrote, and a failing test reports bad data without fixing or blocking it. The wrong revenue figure is reasoned, not printed. One adapter and one version of each package.

Up next in Data engineering and pipelines

More in Data engineering and pipelines

All Data engineering and pipelines videos

Related videos