#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.
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.
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.
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.
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.
