Build an Order Quality Pipeline with dbt

Use dbt and DuckDB to flag messy orders and report usable revenue.

Introduction

30 Second Summary

Revenue figures can look precise even when the order records underneath them are messy. A malformed value can stop a report or make its result untrustworthy.

In this project, you will build a local order-quality pipeline with dbt and DuckDB. The finished pipeline turns malformed source values into visible quality flags before revenue reaches the summary.

What You'll Build

You will run the finished pipeline and see a two-row report with two valid orders, three invalid orders, and $215.49 of usable order value.

By the end of this project, you'll have:

  • A complete local build that loads the source data, transforms each order, and checks the results automatically.
  • A business-readable summary that separates trustworthy revenue from orders requiring investigation.
  • Visible data-quality flags that identify invalid order identifiers, dates, totals, and statuses.
  • Secret Mission: Add a singular business-rule test that proves a negative order total can never escape the invalid-total flag.

Are there any prerequisites?

No prior experience with dbt or DuckDB is needed because the project includes setup for every required tool on Windows. Everything runs locally at no cost.

Before We Start

Before any hands-on work, lock in the local order-quality pipeline you are building and why it matters. Your goal is to protect business reporting by separating malformed orders from trustworthy revenue.

Set Up Your Local Analytics Environment

Revenue checks become unreliable when each command uses a different package version. This local pipeline needs one repeatable environment before it can process malformed orders.

Python runs the pinned dbt packages against DuckDB. Visual Studio Code gives you a workspace for the project.

A virtual environment isolates this project's packages from the rest of your computer. That boundary keeps every later command reproducible.

In this step, get ready to:
  • Install the local tools that support your analytics workspace.
  • Create an isolated environment inside the order-quality folder.
  • Install the pinned packages and verify the dbt command.
Install Python and Visual Studio Code

Python provides the runtime for dbt. The Python extension for Visual Studio Code connects that runtime to your workspace.

Both installers use guided setup screens. Their default installation options are sufficient for this project.

  • Open the Python Windows downloads page.
  • Download the 64-bit Windows installer for Python 3.13.16.
  • Run the downloaded Python installer.
  • Complete the Python installer with its default options.
  • Open the Visual Studio Code download page.
  • Download the current 64-bit Windows build.
  • Run the downloaded Visual Studio Code installer.
  • Complete the Visual Studio Code installer with its default options.

That completes the system-level installation. Both tools are now ready to connect inside one workspace.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search field.
  • Press Enter to launch Visual Studio Code.

You should see the Visual Studio Code start screen. The editor is installed and ready for its Python integration.

  • Select the Extensions view from the left sidebar.
  • Search for Python.
  • Install the extension named Python.

You should see the Python extension listed among your installed extensions. Visual Studio Code can now discover Python interpreters.

  • Open a new terminal panel in Visual Studio Code.
  • Confirm the Python installation by running this command:
py -3 --version

What does this command check?

The py -3 --version command asks the Windows Python launcher for the installed Python 3 version. Its result tells you whether the required runtime is available to your workspace.

✔️ I see Python 3.13.16

Your Python installation is ready. Keep Visual Studio Code open for the workspace setup.

ⓧ I see an older Python version

The project uses Python 3.13.16 so every pinned package runs against the same interpreter.

  • Return to the Python Windows downloads page.
  • Download the 64-bit Windows installer for Python 3.13.16.
  • Run the downloaded installer.
  • Complete the installation with its default options.
  • Close the current Visual Studio Code terminal panel.
  • Open a fresh terminal panel.
  • Check the installed version again by running:
py -3 --version

What should this confirm?

The fresh terminal detects the newly installed interpreter. The version output should now identify Python 3.13.16.

ⓧ Command not found

Windows cannot currently find a Python 3 installation. Installing the required Windows build gives the launcher a valid interpreter.

  • Open the Python Windows downloads page.
  • Download the 64-bit Windows installer for Python 3.13.16.
  • Run the downloaded installer.
  • Complete the installation with its default options.
  • Close the current Visual Studio Code terminal panel.
  • Open a fresh terminal panel.
  • Verify the installation by running:
py -3 --version

What should this confirm?

The fresh terminal gives Windows another chance to detect the installed launcher. The output should identify Python 3.13.16.

Create the workspace and virtual environment

The order-quality folder keeps your project files in one location. Its .venv folder holds a private copy of the Python environment used by this pipeline.

  • Click File in the Visual Studio Code menu bar.
  • Select Open Folder.
  • Choose Desktop in the folder picker.
  • Create a new folder on the Desktop.
  • Enter order-quality as the folder name.
  • Select the order-quality folder as your workspace.

You should see order-quality at the top of the Explorer sidebar. The workspace is empty and ready for its isolated environment.

The interpreter picker can be a little fiddly because Windows may list several Python entries.

  • Press Ctrl+Shift+P to open the Command Palette.
  • Select Python: Create Environment.
  • Select Venv as the environment type.
  • Select the Python 3.13.16 interpreter.

Environment creation can take about a minute while Visual Studio Code prepares the isolated interpreter. You should then see a .venv folder in the Explorer sidebar.

  • Open a new terminal panel in Visual Studio Code.

You should see .venv named in the terminal prompt. That signal confirms the selected environment activated automatically.

Why use a virtual environment?

The .venv environment keeps this project's packages separate from other Python projects on your computer. Package upgrades elsewhere cannot silently change this pipeline.

Install the pinned analytics stack

A requirements file records the exact package versions behind the pipeline. Anyone rebuilding the workspace can install the same dbt Core release with the same DuckDB adapter.

  • Create requirements.txt inside the order-quality folder using the Explorer sidebar.
  • Add the pinned package list by pasting this code:
dbt-core==1.12.6
dbt-duckdb==1.11.0
duckdb==1.5.6

What does this file define?

  • The dbt-core==1.12.6 line pins the command-line framework that builds models and runs tests.
  • The dbt-duckdb==1.11.0 line adds the adapter that connects dbt to a DuckDB database.
  • The duckdb==1.5.6 line pins the local analytics engine used by the adapter.
  • Save requirements.txt.

Package installation can take a few minutes while Python downloads the pinned dependencies. Terminal activity during that wait means the installation is still progressing.

  • Install the pinned packages into the active environment by running:
python -m pip install -r requirements.txt

What does this command do?

The command asks Python to run pip inside the active environment. Pip reads requirements.txt before installing each exact version into .venv.

You should see the installation finish without a dependency conflict. The dbt command is now available inside this workspace.

Package installation failed?

  • Confirm .venv appears in the terminal prompt before rerunning the installation command.
  • Check that requirements.txt is saved directly inside the order-quality folder.
  • Compare every package name and version with the full file below.

Help me diagnose my pinned dbt package installation. You can also ask for help in the NextWork community.

✔️ Awesome, I've got everything!

Good work. Your saved requirements.txt now captures the complete pinned package stack.

ⓧ I'd like to double check the full code

  • Compare your saved requirements.txt with this full file:
dbt-core==1.12.6
dbt-duckdb==1.11.0
duckdb==1.5.6

What should match?

Each line pins one package with a double equals sign. The package names and version numbers must match exactly for the environment to remain reproducible.

Before you check, which dbt Core version and adapter do you expect the terminal to report?

  • Verify the dbt installation by running:
dbt --version

What does this command show?

The dbt --version command reports the installed dbt Core version. It also lists the adapters available to dbt in the active environment.

You should see dbt Core 1.12.6 with a DuckDB adapter entry at version 1.11.0. This proves the pinned analytics stack is active.

dbt command unavailable?

  • Open a fresh Visual Studio Code terminal so the selected .venv environment activates.
  • Confirm the terminal prompt names .venv.
  • Rerun the requirements installation command if the earlier package installation did not finish.

Help me restore the dbt command in my VS Code virtual environment. You can also ask for help in the NextWork community.

Strong start. Your isolated dbt and DuckDB toolchain is ready to build a reliable revenue pipeline.

Next up, you will connect the project to a local database and load the five raw orders.

Load and Preview the Raw Orders

Your dbt environment is ready. It still needs a project configuration before it can load data into DuckDB.

In this step, you will configure the project in Visual Studio Code. You will then load a deliberately messy CSV and preview its five orders in the terminal.

In this step, get ready to:
  • Create the dbt project directories and configuration files.
  • Load the raw order seed into a persistent DuckDB database.
  • Preview all five source orders in the terminal.
Configure the dbt project

The dbt_project.yml file defines where dbt finds project resources. The profiles.yml file connects those resources to your local database.

  • Return to the Explorer sidebar in Visual Studio Code.
  • Use the new-folder icon beside order-quality to create models.
  • Use the new-folder icon beside models to create staging.
  • Use the new-folder icon beside models to create marts.
  • Use the new-folder icon beside order-quality to create seeds.
  • Use the new-folder icon beside order-quality to create tests.

You should now see models/staging, models/marts, seeds, and tests in the Explorer.

  • Use the new-file icon beside order-quality to create dbt_project.yml.
  • Configure the project paths and model materializations by pasting this code into dbt_project.yml:
name: order_quality
version: '1.0.0'
config-version: 2
profile: order_quality

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]

models:
  order_quality:
    staging:
      +materialized: view
    marts:
      +materialized: table

What does this configuration do?

  • The name and profile values connect the project to a profile with the same name.
  • The path settings tell dbt where to find models, seeds, and tests.
  • The staging configuration builds inspectable views.
  • The marts configuration builds persistent tables for business-facing results.
  • Save dbt_project.yml from Visual Studio Code's File menu.
  • Confirm dbt_project.yml is listed beside requirements.txt in the Explorer.

Does the project file look different?

  • Check that dbt_project.yml sits directly inside order-quality.
  • Check that the nested lines use spaces for indentation.
  • Remove any file extension that Visual Studio Code added after dbt_project.yml.
  • Help me check my dbt project configuration.

✔️ Awesome, I've got everything!

Your project paths and materializations are now saved in dbt_project.yml.

ⓧ I'd like to double check the full code

name: order_quality
version: '1.0.0'
config-version: 2
profile: order_quality

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]

models:
  order_quality:
    staging:
      +materialized: view
    marts:
      +materialized: table

What should this file contain?

This reference contains the complete project configuration for this step. Its indentation determines which materialization belongs to each model directory.

  • Use the new-file icon beside order-quality to create profiles.yml.
  • Configure the local DuckDB connection by pasting this code into profiles.yml:
order_quality:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './order_quality.duckdb'
      schema: main
      threads: 4

How does this profile work?

  • The dev target holds the connection settings used by this project.
  • The duckdb adapter stores the project data in a local database file.
  • The path value keeps the database inside order-quality.
  • The main schema keeps the project's database objects together.
  • Save profiles.yml from Visual Studio Code's File menu.
  • Confirm profiles.yml is listed beside dbt_project.yml in the Explorer.

Is the profile missing from the Explorer?

  • Check that you created profiles.yml directly inside order-quality.
  • Check that the file name ends with .yml.
  • Check that order_quality matches the profile value in dbt_project.yml.
  • Help me fix my local dbt profile.

✔️ Awesome, I've got everything!

Your order_quality profile now points to the local DuckDB database.

ⓧ I'd like to double check the full code

order_quality:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './order_quality.duckdb'
      schema: main
      threads: 4

What should this file contain?

This reference contains the complete connection profile for this step. The project uses these values to create and reuse order_quality.duckdb.

Add and load the raw order seed

A seed turns a small project CSV into a database table. This seed keeps the malformed values intact so you can diagnose them in later transformations.

  • Use the new-file icon beside seeds to create raw_orders.csv.
  • Add the five source orders by pasting this data into seeds/raw_orders.csv:
order_id,customer_id,order_date,status,order_total
1001,C001,2026-10-01,completed,125.50
1002,C002,2026-10-02,returned,89.99
1003,C003,2026-10-03,processing,N/A
1004,C004,not-a-date,completed,210.00
1005,C005,2026-10-05,mystery,-20.00

What is in this seed?

  • Each row represents one source order.
  • The columns capture identifiers, dates, status text, and order totals.
  • The malformed values stay in the source data so the pipeline can expose their impact.
  • Save seeds/raw_orders.csv from Visual Studio Code's File menu.
  • Confirm raw_orders.csv is listed inside seeds in the Explorer.

Does the CSV look misaligned?

  • Check that every row contains five comma-separated values.
  • Check that the header is the first line in raw_orders.csv.
  • Check that no blank line appears between the header and the first order.
  • Help me check my raw order seed.

✔️ Awesome, I've got everything!

Your source seed now contains five orders with their original values preserved.

ⓧ I'd like to double check the full code

order_id,customer_id,order_date,status,order_total
1001,C001,2026-10-01,completed,125.50
1002,C002,2026-10-02,returned,89.99
1003,C003,2026-10-03,processing,N/A
1004,C004,not-a-date,completed,210.00
1005,C005,2026-10-05,mystery,-20.00

What should this file contain?

This reference contains the complete seed for this step. It has one header row followed by five source orders.

  • Validate the project configuration and database connection by running this command in the activated terminal:
dbt debug

What does this command check?

This command checks the project configuration, profile, adapter, and DuckDB connection. It catches setup problems before data loading begins.

You should see the project, profile, adapter, and connection checks succeed.

That's the connection locked in. Your project can now reach the same local database on every run.

Does a connection check fail?

  • Confirm the terminal is inside the open order-quality folder.
  • Compare the order_quality profile name across both YAML files.
  • Check the indentation beneath outputs in profiles.yml.
  • Help me diagnose a failed dbt connection check.
  • Load the CSV into DuckDB by running this command in the same terminal:
dbt seed

What does this command load?

This command reads project CSV seeds and loads them as database tables. The raw_orders.csv file becomes a table named raw_orders.

You should see a completion summary showing that the seed loaded successfully.

Good work. Your messy source data now has a persistent home in DuckDB.

Does the seed fail to load?

  • Confirm raw_orders.csv is inside the seeds directory.
  • Check that seed-paths points to seeds in dbt_project.yml.
  • Check that every data row contains the same number of comma-separated values as the header.
  • Help me fix a dbt seed failure.
Preview the raw orders

A raw preview proves that the database contains the same records as the source file. It also exposes the values that the quality layer must handle.

Before you run the preview, do you expect dbt to preserve the malformed strings exactly as they appeared in the CSV?

  • Preview the loaded raw_orders table by running this command:
dbt show --select "raw_orders"

What does this command show?

This command selects the raw_orders seed and prints its first five rows in the terminal. The preview lets you inspect the loaded database table without opening the DuckDB file.

You should see all five orders. The preview includes N/A, not-a-date, and the negative total -20.00.

That first table is live. You can now see the exact data-quality problems that the pipeline needs to contain.

Do you see fewer than five orders?

  • Confirm raw_orders.csv contains one header row followed by five data rows.
  • Run the seed command again after saving the CSV.
  • Check that the preview selects raw_orders.
  • Help me find missing rows in my raw order preview.

Your raw orders are loaded and visible. Next up, you will build the first staging model and see what these malformed values do to direct casts.

Let the Naive Model Break

Your raw_orders seed is now a five-row table in DuckDB. Each source value remains exactly as it appeared in the CSV.

Directly casting source strings looks reasonable when the first rows are clean. One malformed value can decide the fate of the entire transformation.

In this step, get ready to:
  • Create a naive dbt model with direct type conversions.
  • Run the selected model to expose a conversion failure.
  • Preview the raw orders to trace the failure to its source values.
Create the naive staging model

A dbt model can use ref('raw_orders') to read the seed from the project dependency graph. This first model requests analytics-friendly types with direct casts.

  • In the Visual Studio Code file tree from earlier, expand models.
  • Expand staging.
  • Right-click staging.
  • Select the option to create a new file.
  • Name the file stg_orders.sql.
  • Add the naive transformations by pasting this code:
select
    cast(order_id as integer) as order_id,
    trim(customer_id) as customer_id,
    cast(order_date as date) as order_date,
    lower(trim(status)) as status,
    cast(order_total as decimal(10, 2)) as order_total
from {{ ref('raw_orders') }}

What does this model do?

  • The ref('raw_orders') function points the model at the seeded orders.
  • The reference creates the dependency that controls build order.
  • The cast(order_id as integer) expression requests numeric order identifiers.
  • The cast(order_date as date) expression requests calendar dates.
  • The cast(order_total as decimal(10, 2)) expression requests totals with two decimal places.
  • The trim() and lower() functions standardize customer identifiers and status text.
  • Save models/staging/stg_orders.sql.

✔️ Awesome, I've got everything!

Your saved staging model now requests integer identifiers, dates, normalized statuses, and decimal totals.

ⓧ I'd like to double check the full code

  • Compare your saved models/staging/stg_orders.sql with this complete reference:
select
    cast(order_id as integer) as order_id,
    trim(customer_id) as customer_id,
    cast(order_date as date) as order_date,
    lower(trim(status)) as status,
    cast(order_total as decimal(10, 2)) as order_total
from {{ ref('raw_orders') }}

What should match?

This reference is the complete naive model for this step. The selected columns, conversions, aliases, and source reference should match your saved file.

Run the fragile transformation

A direct cast asks the database to convert every selected value into one requested type. Every seeded row now has to satisfy those requests.

Before you run this, do you think every value in the five-row seed can satisfy the types your model requests?

  • Build the naive staging model by running this command in the terminal from earlier:
dbt build --select "stg_orders"

This failure is intentional

You will see the run end with a Conversion Error. The stg_orders model is marked as failed.

DuckDB cannot turn at least one source string into the requested analytics type. The failed run proves that a direct cast makes the entire transformation depend on every value being clean.

Seeing a different failure?

  • Confirm the activated .venv appears in your terminal prompt.
  • Confirm models/staging/stg_orders.sql is saved inside the existing staging directory.
  • Check the model against the full-code reference if the terminal reports a syntax problem.
  • Help me diagnose a different failure in my naive dbt staging model.
Trace the failure to the raw values

The failed build identifies conversion as the problem. The unchanged seed reveals which source strings conflict with the requested types.

  • Preview the five source orders again by running this command:
dbt show --select "raw_orders"

What does the preview prove?

  • The preview still displays all five source rows because the failed model did not alter the seed.
  • The N/A value appears in order_total. It conflicts with the requested decimal conversion.
  • The not-a-date value appears in order_date. It conflicts with the requested date conversion.
  • The -20.00 value satisfies a decimal conversion. Its negative value represents a separate business-quality concern.

Now that you have found the malformed values, which conversion do you think DuckDB will reject first on another run?

  • Repeat the selected build to leave the intended failure visible in your terminal:
dbt build --select "stg_orders"

What should you see?

The run ends with a Conversion Error instead of creating stg_orders successfully. Your raw seed remains queryable even though the staging transformation fails.

You have now proved that one malformed string can stop a full staging model.

Next, you will preserve every source row while turning each quality problem into a visible signal.

Turn Bad Values Into Quality Signals

Your previous run proved that one dirty value can stop an entire dbt model. DuckDB rejected the direct conversion before the staging view could be created.

This step uses TRY_CAST to turn failed conversions into null values. The staging model then preserves every order with explicit quality flags.

In this step, get ready to:
  • Convert raw strings with TRY_CAST.
  • Label malformed records with explicit quality flags.
  • Preview the completed staging view with its overall quality status.
Build the typed and validated layers

A common table expression gives each transformation stage a clear purpose. This model separates the raw source from type conversion and validation.

  • Return to models/staging/stg_orders.sql in the Explorer sidebar.
  • Replace the current query with this typed and validated staging query:
with source as (
    select *
    from {{ ref('raw_orders') }}
),

typed as (
    select
        try_cast(order_id as integer) as order_id,
        trim(customer_id) as customer_id,
        try_cast(order_date as date) as order_date,
        lower(trim(status)) as status,
        try_cast(order_total as decimal(10, 2)) as order_total
    from source
),

validated as (
    select
        *,
        order_id is null as has_invalid_order_id,
        order_date is null as has_invalid_order_date,
        order_total is null or order_total < 0 as has_invalid_order_total,
        status is null
            or status not in ('completed', 'processing', 'returned')
            as has_invalid_status
    from typed
)

select
    *
from validated

What does this model do?

  • The source stage reads raw_orders through ref(). This reference gives dbt the dependency needed for build order.
  • The typed stage converts identifiers to integers.
  • The typed stage converts dates into the date type.
  • The typed stage converts totals into decimal values.
  • The text functions remove extra spaces from customer identifiers. They also normalize status values to lowercase.
  • The identifier flag catches values that could not become integers.
  • The date flag catches values that could not become dates.
  • The total flag catches failed conversions. It also catches negative totals.
  • The status flag catches values outside the three approved statuses.
  • Save models/staging/stg_orders.sql.

Before you rebuild, do you think one malformed value can still stop the model? The next command tests your prediction.

  • Rebuild the staging model in the VS Code terminal by running:
dbt build --select "stg_orders"

What does this command do?

The selected build compiles the updated staging model. It creates the stg_orders view when every conversion completes safely.

That's the crash fixed. The staging view now builds while all five source rows remain available.

Before you preview the view, which source values do you expect to become null? The next result reveals how the model handled them.

  • Preview the typed rows and their flags by running:
dbt show --select "stg_orders"

What should I see?

The preview contains all five orders. The malformed total becomes null for order 1003.

The malformed date becomes null for order 1004. Order 1005 keeps its negative total and receives the invalid-total flag.

Still seeing a conversion failure?

  • Check that the identifier conversion uses try_cast(order_id as integer).
  • Check that the date conversion uses try_cast(order_date as date).
  • Check that the total conversion uses try_cast(order_total as decimal(10, 2)).
  • Confirm that the terminal is still using the activated .venv from your order-quality workspace.
  • Help me diagnose why my defensive dbt staging model still fails.
Add the overall quality status

The individual flags explain each problem. A final CASE expression combines them into one business-readable status.

  • Scroll to the final query in models/staging/stg_orders.sql.
  • Find these lines:
select
    *
from validated

What is missing here?

The current query exposes every typed column and quality flag. It does not yet combine those flags into an overall status.

  • Replace the final query with this quality-status expression:
select
    *,
    case
        when has_invalid_order_id
            or has_invalid_order_date
            or has_invalid_order_total
            or has_invalid_status
            then 'invalid'
        else 'valid'
    end as quality_status
from validated

How is the status decided?

The condition checks the four quality flags. Any true flag assigns invalid to quality_status.

Rows with four false flags receive valid. This gives later models one field for separating usable orders from records needing investigation.

  • Save models/staging/stg_orders.sql.
  • Rebuild the completed staging model by running:
dbt build --select "stg_orders"

What does this build prove?

The successful build proves that malformed values no longer stop the transformation. It also recreates the view with the new quality_status column.

Your staging view now has safe analytics types and an overall quality label.

Seeing a SQL error after the edit?

  • Check that the final query reads from validated.
  • Check that each flag name matches the aliases defined in the validated stage.
  • Check that end as quality_status appears before from validated.
  • Help me fix the final CASE expression in my dbt staging model.

✔️ Awesome, I've got everything!

  • Save models/staging/stg_orders.sql with every quality layer in place.

ⓧ I'd like to double check the full code

  • Compare models/staging/stg_orders.sql with the complete file below:
with source as (
    select *
    from {{ ref('raw_orders') }}
),

typed as (
    select
        try_cast(order_id as integer) as order_id,
        trim(customer_id) as customer_id,
        try_cast(order_date as date) as order_date,
        lower(trim(status)) as status,
        try_cast(order_total as decimal(10, 2)) as order_total
    from source
),

validated as (
    select
        *,
        order_id is null as has_invalid_order_id,
        order_date is null as has_invalid_order_date,
        order_total is null or order_total < 0 as has_invalid_order_total,
        status is null
            or status not in ('completed', 'processing', 'returned')
            as has_invalid_status
    from typed
)

select
    *,
    case
        when has_invalid_order_id
            or has_invalid_order_date
            or has_invalid_order_total
            or has_invalid_status
            then 'invalid'
        else 'valid'
    end as quality_status
from validated
Verify all five orders

Before you preview the finished view, how many orders do you think each quality status contains? The terminal output tests your prediction.

  • Preview the completed staging model by running:
dbt show --select "stg_orders"

What should I see?

  • The preview contains all five source orders.
  • Orders 1001 and 1002 have a valid quality status.
  • Order 1003 has an invalid quality status because its total could not be converted.
  • Order 1004 has an invalid quality status because its date could not be converted.
  • Order 1005 has an invalid quality status because its total is negative.
  • Order 1005 also has an invalid-status flag because mystery is outside the approved status list.

You turned a pipeline crash into an inspectable data-quality result. Two orders are ready for analysis while three remain visible for investigation.

Do the status counts look wrong?

  • Confirm that the total flag checks for null values.
  • Confirm that the total flag checks for values below zero.
  • Confirm that the status list contains completed, processing, and returned.
  • Confirm that the final status checks all four quality flags.
  • Help me trace incorrect quality statuses in my five-row dbt staging preview.

Your staging model now protects the pipeline while preserving every source record. Next, you'll turn these flags into a tested business summary.

Publish and Test the Quality Summary

Your staging view now preserves all five orders. Its quality flags separate two valid orders from three invalid orders.

Stakeholders need a concise answer about usable revenue. A final dbt model publishes that answer as a tested DuckDB table.

In this step, get ready to:
  • Publish a business-facing order-quality summary.
  • Protect the staging model and summary with automated data tests.
  • Verify the complete pipeline from the terminal output.
Create the quality summary

The summary uses SQL aggregates to turn row-level flags into two business-readable groups. Its ref('stg_orders') call creates the dependency on your staging view.

  • Select the models/marts folder in the Explorer sidebar.
  • Create models/marts/order_quality_summary.sql with the file-shaped icon at the top of the Explorer sidebar.

You will see the empty order_quality_summary.sql file open in the editor.

  • Build the summary query by pasting this code into models/marts/order_quality_summary.sql:
select
    quality_status,
    count(*) as order_count,
    sum(
        case
            when quality_status = 'valid' then order_total
            else cast(0 as decimal(10, 2))
        end
    ) as usable_order_value,
    sum(case when has_invalid_order_id then 1 else 0 end) as invalid_id_count,
    sum(case when has_invalid_order_date then 1 else 0 end) as invalid_date_count,
    sum(case when has_invalid_order_total then 1 else 0 end) as invalid_total_count,
    sum(case when has_invalid_status then 1 else 0 end) as invalid_status_count
from {{ ref('stg_orders') }}
group by quality_status

What does this summary do?

  • The quality_status grouping creates one row for valid orders. It creates another row for invalid orders.
  • The count(*) expression calculates how many orders belong to each group.
  • The usable_order_value calculation includes order_total only for valid rows. Invalid rows contribute zero.
  • The four issue counts preserve the reasons that orders need investigation.
  • The ref('stg_orders') call tells dbt where the summary gets its data. This dependency controls the build order.
  • Save models/marts/order_quality_summary.sql.
  • Build the current project by running:
dbt build

What does this command do?

This command processes project resources in dependency order. The staging view is ready before dbt creates the summary table.

Good. Your business-facing table now exists in order_quality.duckdb.

Summary build failed?

  • Confirm that order_quality_summary.sql sits inside models/marts.
  • Compare every parenthesis in the sum expressions with the code above.
  • Check that the dependency name is exactly stg_orders.
  • Ask for help with the summary model error.

✔️ Awesome, I've got everything!

Your summary model is saved. The project now builds the order_quality_summary table from stg_orders.

ⓧ I'd like to double check the full code

Compare your complete models/marts/order_quality_summary.sql file with this version.

select
    quality_status,
    count(*) as order_count,
    sum(
        case
            when quality_status = 'valid' then order_total
            else cast(0 as decimal(10, 2))
        end
    ) as usable_order_value,
    sum(case when has_invalid_order_id then 1 else 0 end) as invalid_id_count,
    sum(case when has_invalid_order_date then 1 else 0 end) as invalid_date_count,
    sum(case when has_invalid_order_total then 1 else 0 end) as invalid_total_count,
    sum(case when has_invalid_status then 1 else 0 end) as invalid_status_count
from {{ ref('stg_orders') }}
group by quality_status

What should this file contain?

The full file groups staged orders by quality_status. It calculates the usable value plus each issue count.

Add the model tests

A YAML schema file lets dbt test the structure of both models. These generic data tests catch missing identifiers plus unexpected quality labels.

  • Select the models folder in the Explorer sidebar.
  • Create models/schema.yml with the file-shaped icon at the top of the Explorer sidebar.

You will see the empty schema.yml file open in the editor.

  • Define the model descriptions plus the data tests by pasting this code into models/schema.yml:
version: 2

models:
  - name: stg_orders
    description: Safely typed orders with explicit quality flags and one row per source order.
    columns:
      - name: order_id
        description: Source order identifier converted to an integer when possible.
        data_tests:
          - not_null
          - unique
      - name: quality_status
        description: Overall valid or invalid label derived from the four quality flags.
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: ['valid', 'invalid']

  - name: order_quality_summary
    description: Business-facing counts and usable order value grouped by quality status.
    columns:
      - name: quality_status
        data_tests:
          - not_null
          - unique
      - name: order_count
        data_tests:
          - not_null

What do these tests protect?

  • The not_null tests reject missing values in required output columns.
  • The unique tests protect one-row-per-identifier behavior in staging. They also protect one-row-per-status behavior in the summary.
  • The accepted_values test limits quality_status to valid or invalid.
  • The summary tests ensure every group has a status. They also ensure every group has an order count.
  • Save models/schema.yml.

Before you run this, do you expect the seed plus both models to satisfy every data test?

  • Build the complete dependency graph by running:
dbt build

What happens during this build?

  • The run loads the CSV seed into DuckDB.
  • The run builds stg_orders before order_quality_summary.
  • The run executes the tests after their required models are available.

That is the complete graph protected. You will see the seed plus both models finish successfully. Every data test also passes.

Seeing a model or test failure?

  • Check the spacing in models/schema.yml because each nested level needs consistent indentation.
  • Confirm that the model names match stg_orders plus order_quality_summary exactly.
  • Compare the accepted_values nesting with the full file below.
  • Ask for help with the failing dbt test.

✔️ Awesome, I've got everything!

Your schema file is saved. The complete dbt build now passes its model tests.

ⓧ I'd like to double check the full code

Compare your complete models/schema.yml file with this version.

version: 2

models:
  - name: stg_orders
    description: Safely typed orders with explicit quality flags and one row per source order.
    columns:
      - name: order_id
        description: Source order identifier converted to an integer when possible.
        data_tests:
          - not_null
          - unique
      - name: quality_status
        description: Overall valid or invalid label derived from the four quality flags.
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: ['valid', 'invalid']

  - name: order_quality_summary
    description: Business-facing counts and usable order value grouped by quality status.
    columns:
      - name: quality_status
        data_tests:
          - not_null
          - unique
      - name: order_count
        data_tests:
          - not_null

What should this file contain?

The full file describes both models. It attaches structural tests to their business-critical columns.

Inspect the final quality report

The complete build proves that the pipeline is structurally sound. The final preview turns those passing resources into the answer a stakeholder needs.

Before you preview the summary, what order counts plus usable value do you expect its two rows to report?

  • Preview the business-facing summary by running:
dbt show --select "order_quality_summary"

What does this command show?

This command compiles the selected summary model. It runs the query before previewing its first rows in the terminal.

You will see two rows grouped by quality_status. The valid row shows 2 orders with 215.49 of usable order value.

The invalid row shows 3 orders with zero usable order value. Those records remain available for investigation through the quality counts.

Summary preview missing or incorrect?

  • Confirm that the complete build finished successfully before previewing the summary.
  • Check that order_quality_summary.sql references stg_orders.
  • Compare the valid-value condition with the summary code above.
  • Ask for help with an incorrect quality summary.

You have turned messy source values into a tested business result. The pipeline now separates trustworthy revenue from orders that need investigation.

Secret mission

Guard the Negative-Order Rule

Your quality summary depends on every negative order total activating the invalid-total flag. Add a singular business-rule test that catches any negative order that escapes this protection.

Clean Up Your Resources

Clean Up Your Resources

Everything in this project runs locally, so there are no ongoing charges or remote resources to manage. Decide whether to keep the workspace, pause your work, or remove the local resources.

Resources you used:

  • The local order-quality project folder stores your entire Visual Studio Code workspace. Its source files include dbt configuration, the seed, two models, generic tests, and the singular business-rule test. Its generated resources include .venv, the pinned packages, the persistent DuckDB database at ./order_quality.duckdb, target, and logs.

Keep everything running

No action needed. Choose this if you want to inspect the passing pipeline or extend it with more analytics models.

  • Leave the order-quality folder unchanged so the database and passing tests remain available.
  • Return to the existing Visual Studio Code workspace when you want to continue building the pipeline.

Pause - I'll come back to this later

Exit the active virtual environment while keeping every project file. This leaves the workspace ready for your next session.

  • Switch back to the Visual Studio Code terminal from earlier.
  • Exit the active .venv environment by running this command:
deactivate

What does this command do?

The deactivate command exits the current virtual environment. Your installed packages and project files remain inside .venv.

  • Close Visual Studio Code when you are ready to leave the project.
  • Reopen the order-quality folder in Visual Studio Code when you return.
  • Select the same .venv interpreter to reactivate the pinned environment.

Delete - I don't want to use this again

Deleting your workspace can feel final. The lighter cleanup option preserves every source file while removing resources that dbt can rebuild.

  • Switch back to the Visual Studio Code terminal from earlier.
  • Exit the active .venv environment by running this command:
deactivate

Why exit the environment first?

The deactivate command releases the terminal from .venv. This prepares the environment folder for deletion.

Lighter Cleanup
  • Delete .venv from the Visual Studio Code Explorer sidebar.
  • Delete order_quality.duckdb from the Visual Studio Code Explorer sidebar.
  • Delete target from the Visual Studio Code Explorer sidebar.
  • Delete logs from the Visual Studio Code Explorer sidebar.

You should still see models, seeds, tests, dbt_project.yml, profiles.yml, and requirements.txt in the Explorer sidebar.

Complete Removal
  • Close Visual Studio Code.
  • Press the Windows key to open your device search.
  • Search for your device's file browser.
  • Open the matching file-browser app.
  • Navigate to the parent folder that contains order-quality.
  • Permanently delete the order-quality folder through the file browser.

You should no longer see order-quality in its parent folder.

All clear. The virtual environment, database, generated artifacts, and source files have been removed.

Nice Work!

Nice Work!

You did it! Your local dbt pipeline now turns five messy orders into a tested DuckDB quality summary.

Every malformed value stays visible. Trustworthy revenue remains ready for reporting.

You've learned how to:

  • Load a messy CSV as a dbt seed. Use model dependencies to build the raw table, staging view, and summary table in order.
  • Trace malformed source values to a planned conversion failure. Preserve every order with defensive conversions and explicit quality flags.
  • Publish a tested quality summary that reports two valid orders. Confirm three invalid orders and $215.49 of usable order value.
  • Complete the optional Secret Mission with a singular business-rule test. It prevents a negative order total from escaping the invalid-total flag.

Ready to quiz yourself?