Build a Tested dbt Sales Pipeline

Create a tested local sales analytics pipeline using dbt and DuckDB.

Introduction

30 Second Summary

A sales total can look convincing even when one order was counted twice or has no customer attached. A polished report only matters when every number can be trusted.

In this project, you will build a local sales analytics pipeline with dbt and DuckDB. The pipeline turns messy customer and order CSV feeds into tested revenue reports.

What You'll Build

You will watch your terminal expose an inflated revenue total of 1890 before your tested pipeline produces a trusted total of 1140.

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

  • Load raw sales data from customer and order CSV feeds with one repeatable command.
  • Expose bad assumptions when flawed orders inflate gross revenue to 1890. See automated tests identify duplicate order IDs and missing customer IDs.
  • Query trusted reports that show four valid orders with 1140 in gross revenue. Show a customer ranking led by Marco Silva with 500.
  • Secret Mission: Ingest a refunds feed and calculate a tested net revenue total of 940.

Are there any prerequisites?

You should know basic SQL. You also need Python 3.10 or later plus Visual Studio Code on Windows.

Before We Start

This first checkpoint connects your local sales analytics pipeline to the business problem it solves. Your goal is trusted revenue reporting that duplicate or ownerless orders cannot corrupt.

Set Up the Local Analytics Project

Trusted revenue depends on a project that runs the same way each time. A self-contained environment prevents global package conflicts from changing your results.

You'll organize the workspace in Visual Studio Code. A local dbt environment will connect every later model to one persistent DuckDB database.

In this step, get ready to:
  • Create the complete sales-pipeline workspace structure.
  • Isolate dbt 2.0.9 in an activated virtual environment.
  • Validate a persistent DuckDB development target.
Create the project workspace

A predictable folder tree gives each model layer a clear home. Visual Studio Code treats the opened folder as one workspace.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search bar.
  • Press Enter to open Visual Studio Code.
  • Select File from the top menu.
  • Select Open Folder....
  • Select New Folder in the folder picker.
  • Enter sales-pipeline as the folder name.

Visual Studio Code may show a Workspace Trust prompt after opening the folder. This project contains only files you create yourself.

  • Select Select Folder to open sales-pipeline.
  • Select Yes, I trust the authors if the Workspace Trust prompt appears.

You should now see sales-pipeline at the top of the Explorer sidebar.

  • Create models with New Folder in the Explorer.
  • Select the new models folder.
  • Create staging with New Folder inside models.
  • Create intermediate with New Folder inside models.

The Explorer should now show models/staging beside models/intermediate.

  • Create marts with New Folder inside models.
  • Select the top-level sales-pipeline folder in the Explorer.
  • Create seeds with New Folder inside sales-pipeline.

Your workspace now separates raw feeds from staging models. It also gives intermediate logic and reporting marts their own folders.

Build the isolated dbt environment

The integrated Windows PowerShell terminal starts inside the folder that Visual Studio Code has open. You can use it to verify Python before installing anything.

  • Select View from the top menu.
  • Select Terminal to open the integrated terminal.
  • Check the installed Python version by running:
python --version

What does this command check?

The command prints the Python runtime version available to this terminal. dbt 2.0.9 requires Python 3.10 or later.

✔️ The version is supported

Your terminal reports Python 3.10 or later. Python can install the pinned dbt package inside this project.

ⓧ The version is too old

Your current Python installation is below the minimum version required by dbt 2.0.9.

  • Install Python 3.10 or later from the official Python downloads for Windows.
  • Close the current Visual Studio Code terminal after installation.
  • Return to the View menu.
  • Select Terminal to start a fresh PowerShell session.
  • Repeat the version check shown above.

ⓧ Python is unavailable

The terminal cannot find a Python installation that it can run.

  • Install Python 3.10 or later from the official Python downloads for Windows.
  • Close the current Visual Studio Code terminal after installation.
  • Return to the View menu.
  • Select Terminal to start a fresh PowerShell session.
  • Repeat the version check shown above.

A virtual environment keeps this project's packages separate from other Python projects. The .venv folder stores that isolated environment inside sales-pipeline.

  • Create the virtual environment by running:
python -m venv .venv

What does this command do?

Python creates a local environment in .venv. Packages installed after activation stay isolated inside this project.

  • Check the Explorer for the new .venv folder.

You should see .venv at the top level of sales-pipeline.

  • Activate the virtual environment by running:
.\.venv\Scripts\Activate.ps1

What does activation change?

Activation points the current terminal at the Python executable inside .venv. Future package commands now affect this project environment.

✔️ The environment is active

Your PowerShell prompt now includes the virtual environment name. The terminal is ready for the dbt installation.

ⓧ Activation was blocked

PowerShell can block local activation scripts through its execution policy. The documented fallback allows locally created scripts for your Windows user account.

  • Update the current user's execution policy by running:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

What does this command change?

The command updates the execution policy for your Windows user account. This lets PowerShell run the activation script created on your computer.

  • Activate the virtual environment again by running:
.\.venv\Scripts\Activate.ps1

What should happen now?

PowerShell should activate the environment successfully. Your prompt should include the virtual environment name.

Still unable to activate the environment?

Confirm that the terminal is inside sales-pipeline. Check that the Explorer contains .venv.

Use Help me activate the project virtual environment.

The isolated environment is ready for one pinned dependency. dbt 2.0.9 includes the local DuckDB adapter used by this project.

  • Install the pinned dbt package by running:
python -m pip install dbt==2.0.9

What does this installation provide?

The package installs dbt 2.0.9 inside .venv. Its built-in DuckDB adapter connects dbt to a local database without cloud credentials.

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

What does the version check prove?

The command confirms that the activated environment can find dbt. It also prints the installed version details.

You should see dbt 2.0.9 in the version details. Your project now has its own working analytics toolchain.

Can't run dbt after installation?

Check that the virtual environment is still active in the current terminal. A new terminal needs the activation command before it can use the project installation.

Use Help me find the dbt installation in my virtual environment.

Configure the DuckDB target

The project file defines where dbt finds models and seeds. It also sets the materialization used by each model layer.

  • Create dbt_project.yml inside sales-pipeline with New File... in the Explorer.
  • Paste this project configuration into dbt_project.yml:
name: sales_pipeline
version: '1.0.0'
config-version: 2
profile: sales_pipeline

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

models:
  sales_pipeline:
    staging:
      +materialized: view
    intermediate:
      +materialized: table
    marts:
      +materialized: table

What does this configuration control?

  • The project name links this file to the sales_pipeline profile.
  • The path settings tell dbt to look in models for SQL models.
  • The seed setting points dbt at the seeds folder for CSV ingestion.
  • The layer settings make staging models views. Intermediate models and marts become tables.
  • Save dbt_project.yml.
  • Check the top level of the Explorer for dbt_project.yml.

You should see dbt_project.yml beside models and seeds.

Does the project file look different?

Confirm that the filename ends in .yml. Check that each nested YAML level uses spaces consistently.

Use Help me compare my dbt project configuration.

✔️ The project file matches

Great. Save dbt_project.yml before creating the connection profile.

ⓧ I'd like to double check the full code

Compare your complete dbt_project.yml with this reference.

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

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

models:
  sales_pipeline:
    staging:
      +materialized: view
    intermediate:
      +materialized: table
    marts:
      +materialized: table

The profile gives dbt a concrete database target. A relative path keeps the persistent database inside sales-pipeline.

  • Create profiles.yml inside sales-pipeline with New File... in the Explorer.
  • Paste this connection profile into profiles.yml:
sales_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './sales_pipeline.duckdb'
      schema: main
      threads: 4

How does the profile connect?

  • The dev target tells dbt which output to use.
  • The duckdb type selects the built-in local adapter.
  • The database path stores data in sales_pipeline.duckdb inside the project folder.
  • The profile uses the main schema with four execution threads.
  • Save profiles.yml.
  • Check the top level of the Explorer for profiles.yml.

You should now see both YAML files at the top level of sales-pipeline.

Does the profile fail to load?

Confirm that sales_pipeline uses the same spelling in both YAML files. Check that profiles.yml sits beside dbt_project.yml.

Use Help me check my local DuckDB profile.

✔️ The profile matches

Your two configuration files now point to the same project profile. Save both files before testing the connection.

ⓧ I'd like to double check the full code

Compare your complete profiles.yml with this reference.

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

Before you run the final check, predict whether the project checks and connection checks will both succeed.

  • Validate the project and DuckDB connection by running:
dbt debug

What does this command verify?

The command checks the dbt installation and project configuration. It also checks whether the profile can connect to the local DuckDB target.

You should see the project checks and DuckDB connection checks finish successfully. The Explorer should list sales_pipeline.duckdb in the project root.

That's the setup locked in. Every later seed and model now has one validated local database target.

Did a debug check fail?

Confirm that the PowerShell terminal is inside sales-pipeline. Check that the virtual environment remains active.

Compare the sales_pipeline profile name across both YAML files. Confirm that the database path begins with ./.

Use Help me diagnose my dbt debug failure.

Your local analytics project is connected and ready. Next up, you'll load the customer and order feeds into the persistent database.

Ingest the Raw Sales Data

Your local dbt project can already connect to DuckDB. The next goal is to turn two raw CSV files into queryable database tables.

Transformations need a repeatable ingestion boundary before they can depend on raw records. Loading the same files as seeds gives every later model a dependable starting point.

In this step, get ready to:
  • Create a four-row customer seed.
  • Create a six-row order seed containing the intentional data quality problems.
  • Load two queryable seed tables into DuckDB.
Create the customer seed

A seed starts as a small CSV file inside the project. dbt loads its rows into a database table with the same name.

  • In the Visual Studio Code file sidebar from earlier, select the seeds folder.
  • Click the new-file icon at the top of the file sidebar.
  • Type raw_customers.csv into the filename field.
  • Press Enter to create the file.
  • Paste the following data into seeds/raw_customers.csv:
customer_id,customer_name,segment
C001,Aisha Khan,SMB
C002,Marco Silva,Enterprise
C003,Jamie Lee,SMB
C004,Sofia Chen,Mid-Market

What does this data define?

This seed establishes the customer lookup data that later reports use for names and segments.

  • The customer_id column gives each customer a stable identifier.
  • The customer_name column stores the report-ready name.
  • The segment column places each customer in a business category.
  • Save seeds/raw_customers.csv.
  • Confirm raw_customers.csv appears under seeds in the file sidebar.

The editor now shows one header row followed by four customer rows.

Can't find the customer seed?

  • Check that raw_customers.csv sits directly inside the existing seeds folder.
  • Check that the filename ends with .csv.
  • Check that the first row starts with customer_id.
  • Help me check the location and contents of my customer seed.
Create the order seed

The order feed contains the sales activity that drives revenue. It also contains a duplicate order and a row without customer ownership.

  • In the Visual Studio Code file sidebar, select the seeds folder.
  • Click the new-file icon at the top of the file sidebar.
  • Type raw_orders.csv into the filename field.
  • Press Enter to create the file.
  • Paste the following data into seeds/raw_orders.csv:
order_id,customer_id,order_date,product,quantity,unit_price
O1001,C001,2026-10-01,Starter Plan,1,120.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1003,C003,2026-10-03,Starter Plan,3,120.00
O1004,,2026-10-04,Pro Plan,1,250.00
O1005,C004,2026-10-05,Analytics Add-on,2,80.00

What does this data contain?

Each row records one order event with its customer reference and sales values.

  • The two O1002 rows are identical duplicates.
  • The O1004 row has a blank customer_id value.
  • The quantity column stores the number of units sold.
  • The unit_price column stores the price of one unit.
  • Save seeds/raw_orders.csv.
  • Confirm raw_orders.csv appears under seeds in the file sidebar.

The editor now shows one header row followed by six order rows.

Does the order file look different?

  • Check that both O1002 rows remain in the file.
  • Check that O1004 has an empty value between its first two commas.
  • Check that every row has six comma-separated positions.
  • Help me compare my order seed with the required rows.

✔️ Awesome, I've got everything!

Both seed files are saved inside seeds. Your raw feeds are ready for ingestion.

ⓧ I'd like to double check the full code

Your cumulative project files should match these references.

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

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

models:
  sales_pipeline:
    staging:
      +materialized: view
    intermediate:
      +materialized: table
    marts:
      +materialized: table
sales_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './sales_pipeline.duckdb'
      schema: main
      threads: 4
customer_id,customer_name,segment
C001,Aisha Khan,SMB
C002,Marco Silva,Enterprise
C003,Jamie Lee,SMB
C004,Sofia Chen,Mid-Market
order_id,customer_id,order_date,product,quantity,unit_price
O1001,C001,2026-10-01,Starter Plan,1,120.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1003,C003,2026-10-03,Starter Plan,3,120.00
O1004,,2026-10-04,Pro Plan,1,250.00
O1005,C004,2026-10-05,Analytics Add-on,2,80.00
Load and preview the seed tables

The seed operation reads every CSV file from the configured seed-paths location. It writes each file into the persistent local database as a table.

  • Return to the activated PowerShell terminal from earlier.
  • Load both CSV files into the persistent DuckDB database by running:
dbt seed

What does this command do?

dbt reads the files inside seeds. It creates the raw_customers table and the raw_orders table in the configured database schema.

Running the command again reloads the authored seed data. This makes the ingestion boundary repeatable.

You'll see both seed resources complete successfully in the terminal summary.

Did a seed fail to load?

  • Confirm the terminal prompt is inside the sales-pipeline folder.
  • Confirm the .venv virtual environment remains activated.
  • Check both CSV files for missing commas or extra values.
  • Help me diagnose why my dbt seeds did not load.

Before you preview the order table, which rows do you expect to stand out?

  • Preview every ingested order by running:
dbt show --inline "select * from {{ ref('raw_orders') }}"

What should I see?

dbt resolves raw_orders to the seed table in DuckDB. The inline query returns the table rows directly in your terminal.

You'll see six rows in the preview. Two rows use O1002 as the order_id.

You'll also see a blank customer_id for order O1004.

Can't preview the order rows?

  • Confirm the earlier seed command completed successfully.
  • Confirm the file is named raw_orders.csv.
  • Confirm dbt_project.yml still points seed-paths to seeds.
  • Help me debug the missing raw order preview.

That is your first pipeline win. Two raw files now become queryable tables through a repeatable command. Next, you'll see what happens when a report trusts those messy order rows.

Expose the Naive Revenue Report

Your raw sales feeds now sit in DuckDB as queryable seed tables. This step turns those rows into the pipeline's first revenue report with dbt.

The raw orders still contain a duplicate sale plus an ownerless sale. You will let those rows inflate revenue before automated tests expose the broken assumptions.

In this step, get ready to:
  • Calculate revenue directly from every raw order row.
  • Aggregate the naive rows into a sales summary.
  • Run automated tests that reveal the broken assumptions.
Create the naive order model

An intermediate model prepares detailed rows for downstream reports. This temporary version trusts every record in raw_orders.

  • In the Visual Studio Code file sidebar, select the models/intermediate folder.
  • Use the file creation control at the top of the file sidebar.
  • Enter int_orders.sql as the file name.
  • Press Enter.
  • Paste this SQL into models/intermediate/int_orders.sql:
select
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price,
    quantity * unit_price as revenue
from {{ ref('raw_orders') }}

What does this model do?

  • The ref('raw_orders') function creates a dependency on the existing seed table.
  • The quantity * unit_price expression calculates revenue for each order row.
  • The direct selection retains all six raw rows.
  • Save models/intermediate/int_orders.sql.
  • Build the temporary model in the activated PowerShell terminal by running this command:
dbt run

What does this command do?

The command compiles the project models against your local profile. It creates int_orders as a table in sales_pipeline.duckdb.

Good progress. You should see int_orders complete successfully in the terminal.

Did the model fail to build?

  • Confirm that int_orders.sql is inside models/intermediate.
  • Check that the final reference uses raw_orders.
  • Help me diagnose the model build.
Define quality rules and the sales summary

A data test declares a rule that trustworthy rows must satisfy. The intermediate model needs rules for unique orders plus valid customer ownership.

  • In the file sidebar, select the models/intermediate folder.
  • Use the file creation control at the top of the file sidebar.
  • Enter schema.yml as the file name.
  • Press Enter.
  • Paste these test definitions into models/intermediate/schema.yml:
version: 2

models:
  - name: int_orders
    description: Trusted order rows with one record per owned order.
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('raw_customers')
                field: customer_id

What do these tests protect?

  • The unique test searches for repeated order identifiers.
  • The not_null tests search for missing identifiers.
  • The relationships test checks populated customer identifiers against raw_customers.
  • Each test passes when it returns zero failing rows.
  • Save models/intermediate/schema.yml.

A data mart shapes modelled data into a business-facing result. This first mart inherits every row accepted by int_orders.

  • In the file sidebar, select the models/marts folder.
  • Use the file creation control at the top of the file sidebar.
  • Enter mart_sales_summary.sql as the file name.
  • Press Enter.
  • Paste this SQL into models/marts/mart_sales_summary.sql:
select
    count(*) as order_count,
    sum(quantity) as units_sold,
    sum(revenue) as gross_revenue
from {{ ref('int_orders') }}

How does the summary work?

  • The count(*) expression counts every intermediate row.
  • The sum(quantity) expression totals the units sold.
  • The sum(revenue) expression totals the calculated revenue.
  • The reference to int_orders places the intermediate table before the mart in the dependency graph.
  • Save models/marts/mart_sales_summary.sql.
  • Build the intermediate model plus the mart by running this command:
dbt run

What happens during this run?

The dependency graph places int_orders before mart_sales_summary. Both models can finish even when their source data violates your business rules.

You now have a runnable report. You should see both models complete successfully in the terminal.

Did the summary fail to build?

  • Confirm that mart_sales_summary.sql is inside models/marts.
  • Check that its final reference uses int_orders.
  • Help me diagnose the summary build.
Reveal the broken assumptions

The SQL has completed successfully. The report can now show whether trusting every raw row produced a believable metric.

Before you preview the report, make a mental prediction about the revenue total from all six raw rows.

  • Preview the naive sales report by running this command:
dbt show --select "mart_sales_summary"

What does the preview show?

You should see an order count of 6 plus a units total of 11.

The gross revenue is 1890.

The wrong result is the lesson

The report counts the repeated O1002 sale twice. It also includes revenue from the ownerless O1004 sale.

The SQL succeeded while the business metric failed. A completed query cannot establish data trust by itself.

Do you see a different total?

  • Confirm that int_orders.sql selects directly from raw_orders.
  • Confirm that mart_sales_summary.sql aggregates int_orders.
  • Help me trace the unexpected total.

The inflated result shows the impact of trusting raw records. The tests can now identify the exact assumptions those records violate.

Before you run the tests, make a mental prediction about which identifier rules will find failing rows.

  • Test the intermediate model by running this command:
dbt test --select "int_orders"

What does the test run reveal?

The command executes the four tests attached to int_orders. Two tests pass.

The order identifier uniqueness test fails because O1002 is duplicated. The customer identifier test fails because O1004 has no owner.

These failures are evidence

The failed tests are intentional. They prove that the naive model permits duplicate revenue plus ownerless revenue.

Did every test pass?

  • Confirm that schema.yml is beside int_orders.sql inside models/intermediate.
  • Check that int_orders.sql still references raw_orders directly.
  • Help me restore the expected failures.

That is the evidence captured. Your report exposes inflated revenue while the tests pinpoint the assumptions that made it untrustworthy.

✔️ Awesome, I've got everything!

Your terminal now shows 1890 in gross revenue plus two intentional test failures. The quality gap is visible.

ⓧ I'd like to double check the full code

These references show the cumulative project files at this checkpoint.

Check dbt_project.yml
name: sales_pipeline
version: '1.0.0'
config-version: 2
profile: sales_pipeline

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

models:
  sales_pipeline:
    staging:
      +materialized: view
    intermediate:
      +materialized: table
    marts:
      +materialized: table

Project configuration check

The project configuration materializes intermediate models plus marts as tables. Staging models remain configured as views.

Check profiles.yml
sales_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './sales_pipeline.duckdb'
      schema: main
      threads: 4

Connection configuration check

The profile targets sales_pipeline.duckdb in the project folder. Relations are stored in the main schema.

Check raw_customers.csv
customer_id,customer_name,segment
C001,Aisha Khan,SMB
C002,Marco Silva,Enterprise
C003,Jamie Lee,SMB
C004,Sofia Chen,Mid-Market

Customer seed check

The customer seed contains the four valid customer records used by the relationship test.

Check raw_orders.csv
order_id,customer_id,order_date,product,quantity,unit_price
O1001,C001,2026-10-01,Starter Plan,1,120.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1002,C002,2026-10-02,Pro Plan,2,250.00
O1003,C003,2026-10-03,Starter Plan,3,120.00
O1004,,2026-10-04,Pro Plan,1,250.00
O1005,C004,2026-10-05,Analytics Add-on,2,80.00

Order seed check

The order seed retains six rows. It includes duplicate O1002 plus ownerless O1004.

Check int_orders.sql
select
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price,
    quantity * unit_price as revenue
from {{ ref('raw_orders') }}

Intermediate model check

The temporary model calculates row-level revenue directly from all six raw order rows.

Check schema.yml
version: 2

models:
  - name: int_orders
    description: Trusted order rows with one record per owned order.
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('raw_customers')
                field: customer_id

Test contract check

The schema declares four identifier checks. The naive model intentionally fails the order uniqueness check plus the customer identifier check.

Check mart_sales_summary.sql
select
    count(*) as order_count,
    sum(quantity) as units_sold,
    sum(revenue) as gross_revenue
from {{ ref('int_orders') }}

Sales mart check

The mart aggregates all six intermediate rows. Its result is 6 orders plus 11 units plus 1890 in gross revenue.

The naive report has made the quality gap visible. Next, you will create a staging boundary that protects every downstream metric.

Build a Trusted Staging Layer

Your failed tests exposed why the first sales summary cannot be trusted. One duplicated order inflated revenue while one ownerless order entered the dataset.

A staging layer in dbt creates the quality boundary that the raw data lacks. Trusted rows can then flow into every downstream report.

In this step, get ready to:
  • Create a staging view for trusted order rows.
  • Point the intermediate model at the staged orders.
  • Rebuild the pipeline against the corrected dependency graph.
Create the staging quality boundary

The seed tables preserve the source records exactly as they arrived. A staging model applies SQL rules before downstream models calculate revenue.

  • In the Explorer sidebar of Visual Studio Code, select the models/staging folder.
  • Click the New File icon beside the models/staging folder.
  • Enter stg_orders.sql as the file name.
  • Add the staging query by pasting this code into models/staging/stg_orders.sql:
select distinct
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price
from {{ ref('raw_orders') }}
where customer_id is not null

What does this staging model do?

  • The distinct keyword compares every selected value. The two identical O1002 rows collapse into one order.
  • The where customer_id is not null filter excludes O1004 because that order has no customer owner.
  • The ref('raw_orders') call records the raw seed as an upstream dependency.
  • Save models/staging/stg_orders.sql.

Before the preview, consider whether the duplicate or ownerless order should still appear.

  • Preview the staging model from the activated PowerShell terminal by running this command:
dbt show --select "stg_orders"

What does this preview check?

The preview runs the staged query against DuckDB. The raw seed table remains unchanged.

That first quality boundary is working. You should see four rows for O1001, O1002, O1003, plus O1005.

Still seeing six rows?

  • Confirm that select distinct appears at the top of models/staging/stg_orders.sql.
  • Confirm that the final line reads where customer_id is not null.
  • Save models/staging/stg_orders.sql before running the preview again.
  • Help me debug my staging model.

✔️ Awesome, I've got everything!

Your staging query is saved. Its preview contains four distinct owned orders.

ⓧ I'd like to double check the full code

  • Compare models/staging/stg_orders.sql with this complete file:
select distinct
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price
from {{ ref('raw_orders') }}
where customer_id is not null
Route revenue through staged orders

The staging model protects reporting only when the intermediate model depends on it. Updating that reference rewires the dependency graph so revenue uses the four trusted rows.

  • Switch back to models/intermediate/int_orders.sql in the Explorer sidebar.
  • Select the existing contents of models/intermediate/int_orders.sql.
  • Replace the temporary model by pasting this final query:
select
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price,
    quantity * unit_price as revenue
from {{ ref('stg_orders') }}

What changes in this model?

  • The ref('stg_orders') call makes the staging view the new upstream source.
  • The revenue formula remains quantity * unit_price because the calculation itself was correct.
  • Every downstream model that references int_orders now receives the trusted order rows.
  • Save models/intermediate/int_orders.sql.
  • Confirm that the final source line now contains ref('stg_orders').

Does the model still reference raw orders?

  • Check the final line of models/intermediate/int_orders.sql for ref('stg_orders').
  • Check that the revenue expression still reads quantity * unit_price as revenue.
  • Help me check the intermediate model.

✔️ Awesome, I've got everything!

Your intermediate model now reads from the trusted staging view. The revenue calculation remains in place.

ⓧ I'd like to double check the full code

  • Compare models/intermediate/int_orders.sql with this complete file:
select
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price,
    quantity * unit_price as revenue
from {{ ref('stg_orders') }}
Build and verify the corrected pipeline

The corrected dependency graph now places stg_orders before int_orders. A full build can recreate the models before running the configured data tests.

Before the build, consider whether the same two tests should still fail after the quality boundary is in place.

  • Build the complete pipeline from the activated PowerShell terminal by running this command:
dbt build

What does this build do?

  • The build loads the seed resources into the configured database.
  • The dependency graph places stg_orders before int_orders.
  • The configured tests examine the rebuilt intermediate table after its dependencies are ready.

You have closed the quality gap exposed by the first report. Every configured test should now pass against the four trusted orders.

Are the order tests still failing?

  • Confirm that models/staging/stg_orders.sql includes select distinct.
  • Confirm that the staging filter excludes null customer_id values.
  • Confirm that models/intermediate/int_orders.sql references stg_orders.
  • Help me resolve the failing tests.

Before the final preview, predict how removing the duplicate plus the ownerless order should change the totals.

  • Preview the rebuilt sales summary from the activated PowerShell terminal by running this command:
dbt show --select "mart_sales_summary"

What does this preview prove?

The summary reads from the rebuilt int_orders table. Its totals show whether the staging rules reached the reporting layer.

You now have a trusted result. You should see 4 orders with 8 units sold plus 1140 in gross revenue.

Does the summary still show 1890?

  • Confirm that the earlier dbt build completed successfully.
  • Confirm that mart_sales_summary still references int_orders.
  • Run the summary preview again after the successful build.
  • Help me trace the old summary result.

Your sales summary now rests on tested order data. Next up, you will shape those trusted rows into a customer-level sales report.

Deliver the Customer Sales Mart

Your trusted order layer now contains four valid sales records. The quality boundary from the previous step keeps messy raw rows away from reporting.

Clean intermediate data creates value when it answers a business question. In this step, you'll use dbt to build a customer sales data mart that ranks customers by trusted revenue.

In this step, get ready to:
  • Build a customer-level revenue model from trusted orders.
  • Protect customer identifiers with uniqueness and non-null checks.
  • Verify the completed pipeline through its ranked customer report.
Create the customer sales mart

The report needs customer details from raw_customers plus trusted revenue from int_orders. An inner join connects each valid order to its customer.

  • In the Explorer sidebar in Visual Studio Code, right-click models/marts.
  • Select New File.
  • Type mart_customer_sales.sql.
  • Press Enter.
  • Add the customer report query by copying this code into the new file:
select
    customers.customer_id,
    customers.customer_name,
    customers.segment,
    count(orders.order_id) as order_count,
    sum(orders.revenue) as total_revenue
from {{ ref('raw_customers') }} as customers
inner join {{ ref('int_orders') }} as orders
    on customers.customer_id = orders.customer_id
group by
    customers.customer_id,
    customers.customer_name,
    customers.segment
order by total_revenue desc

How Does This Model Answer the Question?

  • The ref('raw_customers') call supplies each customer's name plus segment.
  • The inner join keeps customers whose identifiers match trusted orders.
  • The count(orders.order_id) calculation reports each customer's order count.
  • The sum(orders.revenue) calculation produces trusted customer revenue.
  • The order by total_revenue desc clause places the highest-revenue customer first.
  • Save models/marts/mart_customer_sales.sql.
  • Build the new report model by running this command:
dbt run

What Does This Command Do?

The command runs the project models against the configured DuckDB target. The new mart becomes a persistent table in sales_pipeline.duckdb.

Good progress. The customer report now exists alongside your trusted sales summary.

Customer Mart Missing From the Run?

  • Confirm that mart_customer_sales.sql is saved inside models/marts.
  • Check that both ref calls match the existing resource names exactly.
  • Compare the closing lines with the reference file below if the terminal reports a SQL syntax problem.
  • Help me debug why my customer sales mart does not run.

✔️ Awesome, I've got everything!

Your customer mart model is saved. The model run confirms that dbt can build it in DuckDB.

ⓧ I'd like to double check the full code

Compare models/marts/mart_customer_sales.sql with this complete file:

select
    customers.customer_id,
    customers.customer_name,
    customers.segment,
    count(orders.order_id) as order_count,
    sum(orders.revenue) as total_revenue
from {{ ref('raw_customers') }} as customers
inner join {{ ref('int_orders') }} as orders
    on customers.customer_id = orders.customer_id
group by
    customers.customer_id,
    customers.customer_name,
    customers.segment
order by total_revenue desc
Test the customer identifiers

A customer-level report needs exactly one row for each customer. Data tests protect that reporting contract by rejecting duplicate or missing identifiers.

  • In the Explorer sidebar, right-click models/marts.
  • Select New File.
  • Type schema.yml.
  • Press Enter.
  • Add the customer identifier tests by copying this configuration into the new file:
version: 2

models:
  - name: mart_customer_sales
    description: Customer-level order and revenue report.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

What Do These Tests Protect?

  • The unique test checks that each customer appears once in the mart.
  • The not_null test checks that every report row belongs to an identified customer.
  • Each test passes when its query returns zero failing rows.
  • Save models/marts/schema.yml.

Before you run the full build, make a quick prediction about whether the customer identifier checks will pass.

  • Run the complete pipeline in the activated PowerShell terminal by running:
dbt build

What Does the Full Build Prove?

The command runs seeds plus models plus tests in dependency graph order. A successful build proves that the final mart still respects every upstream quality boundary.

You'll see the complete build finish successfully. Solid work. The customer mart now has passing identifier checks.

Build Not Passing?

  • Confirm that schema.yml is saved inside models/marts.
  • Check that the model name is exactly mart_customer_sales.
  • Check the indentation beneath columns plus data_tests.
  • Help me diagnose why my dbt build does not pass.

✔️ Awesome, I've got everything!

Your complete build passes. The customer sales mart now has its reporting contract enforced by tests.

ⓧ I'd like to double check the full code

Compare models/marts/schema.yml with this complete file:

version: 2

models:
  - name: mart_customer_sales
    description: Customer-level order and revenue report.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
Verify the customer ranking

The final preview turns the pipeline into a business result you can demonstrate. It shows which customers generated the most trusted revenue.

Before you preview the report, predict which customer leads the ranking.

  • Query the completed customer sales mart by running:
dbt show --select "mart_customer_sales"

What Does This Preview Show?

The command queries mart_customer_sales from the configured DuckDB target. The displayed rows preserve the descending revenue order defined by the model.

You'll see four customers in descending revenue order. Marco Silva leads with 500 in total_revenue.

Strong finish. Your base pipeline now turns tested orders into a customer-level business report.

Ranking Missing or Out of Order?

  • Confirm that the full build completed successfully before previewing the mart.
  • Check that the final model line uses order by total_revenue desc.
  • Check that the revenue aggregation uses sum(orders.revenue).
  • Help me debug an incorrect customer sales ranking.

Secret mission

Ingest Refunds and Calculate Net Revenue

Gross revenue leaves refunds out of the business story. Extend your pipeline with a tested refunds feed that produces 940 in trusted net revenue.

Clean Up Your Resources

Clean Up Your Resources

All resources in this project run locally. Keeping them creates no ongoing cloud or paid service costs.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The project-local .venv Python virtual environment.
  • The dbt_project.yml project configuration file.
  • The profiles.yml local connection configuration file.
  • The source CSV files inside seeds/.
  • The staging, intermediate, and mart files inside models/.
  • The persistent DuckDB database stored in sales_pipeline.duckdb.
  • The generated dbt build artifacts inside sales-pipeline.

Keep everything running

Keeping the project requires no action. Choose this option if you plan to extend the pipeline or query its trusted marts again.

  • Leave the sales-pipeline folder unchanged.
  • Reuse sales_pipeline.duckdb when you want to query the trusted sales data again.
  • Continue using the activated .venv terminal while you are actively building.

Pause - I'll come back to this later

Pausing exits the active environment while preserving every project file. No service continues running after the environment closes.

  • Leave the activated virtual environment by running:
deactivate

What Does This Command Do?

The deactivate command exits the active Python virtual environment. It leaves every file inside sales-pipeline untouched.

  • Confirm the PowerShell prompt no longer identifies the virtual environment as active.
  • Keep the sales-pipeline folder in its current location.

Delete - I don't want to use this again

Removing the folder is permanent. Every project resource listed above sits inside sales-pipeline.

  • Leave the activated .venv environment by running:
deactivate

Why Deactivate First?

The command exits the active Python virtual environment. Your project files remain available until you delete the folder.

  • Close Visual Studio Code.
  • Locate the sales-pipeline folder in File Explorer.
  • Delete the sales-pipeline folder.
  • Confirm the sales-pipeline folder no longer appears in File Explorer.

Nice Work!

Nice Work!

You did it! Your tested local sales analytics pipeline now turns messy sales feeds into trusted customer reporting with 940 in net revenue.

You've learned how to:

  • Build a repeatable local pipeline with dbt's built-in DuckDB adapter. Load CSV feeds into a persistent analytics database.
  • Make bad assumptions visible with a deliberately naive SQL model. Use automated data tests to catch duplicate orders. Reject ownerless orders with not-null tests. Enforce valid ownership with relationship tests.
  • Create a trusted staging boundary that reduces six raw order rows to four reportable orders. Produce a customer sales mart ranked by revenue. See Marco Silva lead the report with 500.
  • Secret Mission: Ingest a refunds feed through the same tested staging pattern. Calculate 940 in net revenue from 1140 in gross revenue after 200 in refunds.

Ready to quiz yourself?