Data Transformation Pipeline with dbt

Build a staging-to-marts dbt pipeline on DuckDB with tests and docs.

Introduction

30 Second Summary

Every company has raw data scattered across systems. Turning that raw data into reliable answers (who are our best customers? which products are trending?) is the job of a data transformation pipeline.

In this project, you will build a complete data pipeline using dbt and DuckDB that transforms raw e-commerce CSV files into tested, documented analytics tables on your local machine.

What You'll Build

Run dbt build and watch raw e-commerce CSVs transform into a tested, documented customer lifetime value table in seconds, all on your local machine.

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

  • A dbt pipeline that loads raw e-commerce CSVs through staging models that clean the data, then joins everything into a single analytics-ready customer lifetime value table with one command.
  • Automated data tests that validate every table on every run, catching nulls, duplicates, and invalid values before they reach downstream consumers.
  • A visual lineage graph you can browse in your browser, showing exactly how data flows from raw sources to final output as a DAG.
  • Secret Mission: Add a revenue-by-month mart model with an incremental materialization that processes only new data on subsequent runs.

Are there any prerequisites?

This project assumes intermediate SQL knowledge (SELECT, JOIN, GROUP BY, subqueries) and basic comfort with the terminal. You do not need prior experience with dbt or DuckDB.

Before We Start

Before we dive into any setup or code, let's lock in what you're about to build and why it matters.

Set Up dbt with DuckDB

To build your data transformation pipeline, you need a working dbt environment connected to a database.

Most dbt tutorials require a cloud data warehouse with accounts and billing. In this project, you'll use DuckDB instead. DuckDB is a local analytics database that runs as a single file on your machine. Zero cloud setup, zero cost, same dbt skills.

In this step, get ready to:
  • Check your Python version and create a virtual environment.
  • Install dbt-duckdb and verify the installation.
  • Initialize a new dbt project and configure the DuckDB connection.
Check your Python version and create a virtual environment

dbt Core requires Python 3.9 or higher. Before installing anything, confirm your Python version is compatible.

  • Open your terminal in VS Code by pressing Ctrl+` (backtick key, top-left of keyboard).
  • Check your Python version by running this command:
python --version

✔️ I see version 3.9 or higher

Your Python version is compatible. Move on to creating your virtual environment below.

ⓧ I see an older version

You need Python 3.9+ for dbt Core. Download the latest version from python.org/downloads and run the installer.

  • During installation, check the box that says Add Python to PATH.
  • After installing, close and reopen your terminal, then run python --version again to confirm.

ⓧ Command not found

Python is not installed or not on your PATH. Download it from python.org/downloads and run the installer.

  • During installation, check the box that says Add Python to PATH.
  • After installing, close and reopen your terminal, then run python --version again to confirm you see a version number.

A virtual environment isolates your project's packages from your system Python. This prevents version conflicts between projects.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a new project folder and move into it by running these commands:
mkdir byteshop-dbt
cd byteshop-dbt
  • Create a virtual environment by running:
python -m venv venv
  • Activate the virtual environment by running:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate.bat

You should see (venv) appear at the start of your terminal prompt. This confirms the virtual environment is active.

Don't see (venv) in your prompt?

  • On Windows PowerShell, you may need to run venv\Scripts\Activate.ps1 instead.
  • If you get a permissions error on Windows PowerShell, run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned first, then retry activation.
  • Make sure you ran the python -m venv venv command from inside the byteshop-dbt folder.

Need more help? Why can't I activate my virtual environment?

Install dbt-duckdb and verify the installation

The dbt-duckdb package installs both dbt Core and the DuckDB adapter in one command.

  • Install dbt-duckdb by running:
pip3 install dbt-duckdb

What does this install?

This single command installs dbt-core (the transformation engine) and dbt-duckdb (the adapter that connects dbt to a DuckDB database). You do not need to install DuckDB separately.

The install takes a minute or two. Once it finishes, confirm everything is working.

  • Verify your installation by running:
dbt --version

You should see output listing the dbt Core version under Core and duckdb listed under Plugins.

Seeing 'command not found'?

  • Make sure your virtual environment is still active. You should see (venv) in your prompt.
  • If you accidentally closed and reopened your terminal, navigate back to byteshop-dbt and reactivate with the activate command from above.

Still stuck? Help me fix my dbt installation

Initialize a new dbt project and configure the DuckDB connection

The dbt init command scaffolds a complete project structure with all the folders and config files dbt needs.

  • Scaffold your dbt project by running:
dbt init byteshop

What will dbt init ask me?

The command will prompt you to choose a database and configure connection settings. Here is what to enter:

  • When asked which database to use, enter the number next to duckdb (usually 1).
  • When asked for the path, enter byteshop.duckdb.
  • When asked for threads, enter 4.

Once complete, dbt creates a byteshop folder inside your current directory with the project scaffolding.

  • Move into the new project folder by running:
cd byteshop
  • Confirm the project files were created by running:
ls

You should see dbt_project.yml, models, seeds, tests, and other folders in the output.

Now open this project in VS Code so you can edit the configuration files.

  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to Desktop > byteshop-dbt > byteshop and click Select Folder.

Next, clean up the generated dbt_project.yml so it matches the structure you need for this project.

  • In the VS Code Explorer sidebar, click on dbt_project.yml to open it.
  • Replace the entire file contents with the following:
name: 'byteshop'
version: '1.0.0'

profile: 'byteshop'

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

models:
  byteshop:

What does this file do?

  • The name and profile fields link this project to the connection profile in profiles.yml.
  • The path fields (model-paths, seed-paths, etc.) tell dbt where to find your SQL models, CSV seeds, and tests.
  • The models: byteshop: section is where you will later configure materializations for different model directories.
  • Save the file by pressing Ctrl+S (Windows) or Cmd+S (macOS).

Now verify that your profiles.yml is correctly configured. The dbt init command created this file in your home directory.

  • Open your profiles.yml by running this command in the VS Code terminal:

macOS

cat ~/.dbt/profiles.yml

Windows

type %USERPROFILE%\.dbt\profiles.yml

Verify that the output matches this configuration:

byteshop:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: byteshop.duckdb
      threads: 4

What does profiles.yml do?

This file tells dbt how to connect to your database. The type: duckdb setting uses the DuckDB adapter. The path: byteshop.duckdb creates a local database file in your project directory when you first run dbt.

DuckDB creates this file automatically. No server setup required.

Edit profiles.yml if it doesn't match

If the output shows a different path or thread count, open the file directly and edit it. On macOS, it lives at ~/.dbt/profiles.yml. On Windows, it lives at C:\Users\<username>\.dbt\profiles.yml.

Before you run this final check, what do you expect to see?

  • Confirm everything is wired up correctly by running:
dbt --version

You should see the dbt Core version number under Core and duckdb listed under Plugins. This confirms dbt is installed and knows which adapter to use.

✔️ Awesome, I've got everything!

Great. Double check that you saved dbt_project.yml and you are ready to move on.

ⓧ I'd like to double check the full code

Here is what your dbt_project.yml should look like:

name: 'byteshop'
version: '1.0.0'

profile: 'byteshop'

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

models:
  byteshop:

And your profiles.yml (in ~/.dbt/profiles.yml) should look like:

byteshop:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: byteshop.duckdb
      threads: 4

Your dbt environment is ready to go. Next up, you will load raw data into DuckDB and run your first model.

Load Raw Data and Run Your First Model

Your dbt environment is wired up and ready. DuckDB is connected, and you have a scaffolded project structure waiting for data.

Before you can build transformation models, you need raw data in your database. In this step, you'll load CSV seed files into DuckDB and prove the pipeline runs end to end.

In this step, get ready to:
  • Load raw customer, order, and payment data into DuckDB using dbt seeds.
  • Run the default example models to confirm the full pipeline works.
  • Clean up the project for your own custom models.
Create seed CSV files and load them into DuckDB

dbt seeds let you load small CSV files directly into your database as tables. This is perfect for reference data or small datasets like the raw e-commerce data you'll transform in later steps.

  • In VS Code's Explorer sidebar, right-click the seeds/ folder.
  • Click New File.
  • Name the file raw_customers.csv and paste this content:
id,first_name,last_name
1,Michael,P.
2,Shawn,M.
3,Kathleen,P.
4,Jimmy,C.
5,Katherine,R.
6,Sarah,R.
7,Martin,M.
8,Frank,R.
9,Jennifer,F.
10,Henry,W.
  • Create another file in seeds/ called raw_orders.csv with this content:
id,user_id,order_date,status
1,1,2024-01-01,completed
2,3,2024-01-02,completed
3,4,2024-01-04,completed
4,1,2024-01-05,completed
5,5,2024-01-08,completed
6,3,2024-01-10,returned
7,4,2024-01-11,completed
8,6,2024-01-12,completed
9,9,2024-01-14,returned
10,9,2024-01-15,completed
  • Create one more file in seeds/ called raw_payments.csv with this content:
id,order_id,payment_method,amount
1,1,credit_card,1000
2,2,credit_card,2000
3,3,coupon,100
4,4,coupon,2500
5,5,bank_transfer,1700
6,6,credit_card,600
7,7,credit_card,1600
8,8,credit_card,2300
9,9,bank_transfer,2300
10,10,credit_card,0

What is this data?

This is the raw data for ByteShop, your fictional e-commerce store. You have 10 customers, 10 orders, and 10 payments.

Notice the amount column in payments is stored in cents (1000 = $10.00). Your staging models will convert this to dollars later.

  • Save all three files.
  • Confirm the files exist by running:
ls seeds/

You should see raw_customers.csv, raw_orders.csv, and raw_payments.csv listed in the output.

  • Load all three CSV files into DuckDB by running:
dbt seed

What does this command do?

The dbt seed command reads every CSV file in your seeds/ directory and creates a corresponding table in DuckDB. Each CSV becomes a table with the same name as the file (without the .csv extension).

You should see output ending with "Completed successfully" and three seed tables loaded (raw_customers, raw_orders, raw_payments).

Seeing an error about profiles or connection?

Make sure you're running the command from inside the byteshop project folder (the one containing dbt_project.yml). dbt looks for the project file in your current directory.

If your virtual environment is no longer active, reactivate it first with venv\Scripts\activate.bat (CMD) before running dbt commands.

help me debug a dbt seed error

Run the default example models

Your scaffolded project came with example SQL models in a models/example/ directory. Running them proves the full dbt-to-DuckDB pipeline works end to end.

Before you run this next command, what do you think will happen? You have seed data in the database and example models in the project.

  • Execute the example models by running:
dbt run

You should see output ending with "Completed successfully" and two models created. dbt compiled the example SQL files and ran them against DuckDB.

What just happened?

The dbt run command compiled every .sql file in your models/ directory into raw SQL, then executed each query against DuckDB. The results are stored as views or tables inside the byteshop.duckdb file.

This confirms your connection works and dbt can transform data. Now you'll remove these examples and build your own models.

Seeing a compilation or runtime error?

If dbt run fails, first check that dbt seed completed successfully in the previous substep. The example models depend on the seed data being present.

help me debug a dbt run error

Delete the example models and configure your project

The example models served their purpose. Now you'll remove them and set up the folder structure for your own staging and mart models.

  • Delete the example models directory by running:
rm -rf models/example/
  • Open dbt_project.yml in VS Code.
  • Scroll to the bottom and find the models: section. It currently looks like this:
models:
  byteshop:
  • Replace that section with the following configuration that defines materialization defaults for your staging and marts directories:
models:
  byteshop:
    staging:
      +materialized: view
    marts:
      +materialized: table

What are materializations?

A materialization controls how dbt persists a model's results in the database. Views are lightweight references to a query (rebuilt on each read). Tables store the results physically (faster to query, takes more space).

Staging models use views because they're intermediate building blocks. Mart models use tables because they're the final analytics-ready output that people query directly.

  • Save dbt_project.yml.
  • Verify the cleanup worked by running:
dbt run

With no model files left in the models/ directory, dbt has nothing to execute. You should see a warning that says nothing was found to run.

✔️ Awesome, I've got everything!

Great. Double check you've saved dbt_project.yml and confirmed the example models are gone.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml at this point:

name: 'byteshop'
version: '1.0.0'

profile: 'byteshop'

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:
  - "target"
  - "dbt_packages"

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

Your raw data is loaded and the project is clean. Next up, you'll build staging models that standardize this raw data into a consistent format.

Build Staging Models

Your raw CSV data is sitting in DuckDB, but the column names aren't consistent.

The orders table calls the customer column user_id, while the customers table calls it id. Payments store amounts in cents rather than dollars. If every downstream model has to deal with these quirks, you'll repeat the same cleanup logic everywhere. Staging models fix this at the source by creating a clean, standardized layer that the rest of your pipeline can trust.

In this step, get ready to:
  • Create staging SQL models that rename and standardize raw columns.
  • Use the ref() function to connect your models into a DAG.
  • Run your staging pipeline and confirm all three models build as views.
Create the staging directory and your first model

The staging layer lives in its own directory inside models/. Each staging model is a .sql file containing a SELECT statement that reads from a raw source and outputs clean columns.

  • Create the models/staging/ directory by running this command in your terminal:
mkdir models/staging
  • Confirm the directory exists by running:
ls models

You should see staging listed in the output.

  • Create a new file called stg_customers.sql inside the models/staging/ directory. In VS Code, right-click the staging folder in the Explorer sidebar and select New File.
  • Paste the following code into stg_customers.sql:
select
    id as customer_id,
    first_name,
    last_name
from {{ ref('raw_customers') }}

What does this code do?

This model reads from the raw_customers seed table and renames the generic id column to customer_id. Now every model that references customers will use the same clear column name.

The {{ ref('raw_customers') }} function tells dbt that this model depends on the raw_customers seed. dbt uses these references to build a DAG (directed acyclic graph) so it always runs models in the correct order.

  • Save stg_customers.sql.
  • Run your first staging model by executing this command:
dbt run

You should see output showing 1 model created successfully, with stg_customers created as a SQL view.

Seeing an error instead?

  • Check that your file is saved in models/staging/stg_customers.sql and not directly in models/.
  • Make sure ref('raw_customers') matches your seed file name exactly (without the .csv extension).
  • Verify you have double curly braces {{ }} around the ref function, not single braces.

Help me debug my dbt staging model error.

✔️ Awesome, I've got everything!

Great. Make sure you have saved stg_customers.sql before moving on.

ⓧ I'd like to double check the full code

select
    id as customer_id,
    first_name,
    last_name
from {{ ref('raw_customers') }}
Write the orders and payments staging models

The next two models follow the same pattern. Each reads from a raw seed, renames columns for clarity, and uses ref() to register the dependency.

  • Create a new file called stg_orders.sql in models/staging/ and paste this code:
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
from {{ ref('raw_orders') }}

What does this code do?

This model renames the inconsistent user_id column to customer_id, matching the name used in stg_customers. The generic id becomes order_id so joins downstream are always explicit.

  • Save stg_orders.sql.
  • Create one more file called stg_payments.sql in models/staging/ and paste this code:
select
    id as payment_id,
    order_id,
    payment_method,
    amount / 100.0 as amount
from {{ ref('raw_payments') }}

What does this code do?

This model converts the amount column from cents to dollars by dividing by 100. Raw payment amounts like 1000 become 10.00, making downstream calculations immediately readable.

The {{ ref('raw_payments') }} reference adds this model to the DAG, so dbt knows to load the raw_payments seed before building this view.

  • Save stg_payments.sql.

Not sure about the file structure?

Your models/staging/ folder should now contain exactly three files: stg_customers.sql, stg_orders.sql, and stg_payments.sql. If you accidentally placed a file in the wrong directory, drag it into models/staging/ using the Explorer sidebar.

Help me check my staging model file structure.

✔️ Awesome, I've got everything!

Great. Make sure all three files are saved before moving on.

ⓧ I'd like to double check the full code

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
from {{ ref('raw_orders') }}
select
    id as payment_id,
    order_id,
    payment_method,
    amount / 100.0 as amount
from {{ ref('raw_payments') }}
Run the full staging pipeline

Your dbt_project.yml already configures everything in the staging/ directory to materialize as views. That means dbt creates these models as database views rather than physical tables, keeping them lightweight and always up to date.

What is a view?

A view is a saved SQL query that runs fresh every time you read from it. Unlike a table, it doesn't store data on disk. Views are ideal for staging models because they always reflect the latest raw data without taking up extra space.

Before you run the next command, how many models do you expect dbt to create this time?

  • Run the full staging pipeline by executing:
dbt run

You should see all three staging models (stg_customers, stg_orders, stg_payments) created successfully as views. The output ends with a summary line showing 3 passes and 0 errors.

Seeing fewer than 3 models pass?

  • Read the error message carefully. It usually names the file and line number where the problem is.
  • Check that each ref() argument matches the seed name exactly: raw_customers, raw_orders, raw_payments.
  • Verify you ran dbt seed in the previous step. The staging models can only reference seeds that have already been loaded.

Help me fix my dbt run errors.

Your staging layer is locked in. All three models clean and standardize raw data, and dbt knows exactly how they connect. Next up, you'll join these staging models together into a single analytics-ready mart table.

Build a Mart Model

Your staging models are clean and consistent. Each one handles a single source table. But answering a business question like "how much has each customer spent in total?" requires manually joining data from three separate models.

A mart model solves this by joining staging models together into one analytics-ready table. In this step, you'll build a customers mart that calculates customer lifetime value by combining data from all three staging sources.

In this step, get ready to:
  • Create a models/marts/ directory for your mart model.
  • Write a customers.sql model that joins all staging data and calculates per-customer metrics.
  • Run dbt run and preview the output to confirm the mart works.
Create the marts directory and write the source CTEs

The customers mart will pull data from all three staging models using CTEs (Common Table Expressions). CTEs let you break a complex query into named, readable blocks that build on each other.

  • Create the marts directory by running this command in your terminal:
mkdir models/marts
  • Confirm the directory was created by running:
ls models

You should see both marts and staging listed in the output.

  • Create a new file called customers.sql inside models/marts/ and paste the following code:
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('stg_orders') }}
),

payments as (
    select * from {{ ref('stg_payments') }}
),

customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders
    from orders
    group by customer_id
),

customer_payments as (
    select
        orders.customer_id,
        sum(payments.amount) as total_amount
    from payments
    left join orders on payments.order_id = orders.order_id
    group by orders.customer_id
),

What does this code do?

  • The first three CTEs (customers, orders, payments) import data from your staging models using {{ ref() }}. This tells dbt these models depend on your staging layer.
  • The customer_orders CTE aggregates each customer's order history: their first order, most recent order, and total number of orders.
  • The customer_payments CTE calculates the total amount each customer has spent by joining payments back to orders (since payments link to orders, not directly to customers).
Add the final join with customer lifetime value

The aggregation CTEs calculated metrics per customer. Now you need a final CTE that joins everything together into one row per customer.

  • In customers.sql, add the following code directly below the customer_payments CTE (after the trailing comma on line 30):

final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        customer_orders.number_of_orders,
        coalesce(customer_payments.total_amount, 0) as customer_lifetime_value
    from customers
    left join customer_orders on customers.customer_id = customer_orders.customer_id
    left join customer_payments on customers.customer_id = customer_payments.customer_id
)

select * from final

What does this code do?

  • The final CTE joins the customer info with their aggregated order and payment data using left joins. This ensures every customer appears even if they have no orders yet.
  • The coalesce(customer_payments.total_amount, 0) handles customers with no payments by defaulting their lifetime value to 0 instead of NULL.
  • The final select * from final outputs the result. dbt uses whatever the last SELECT returns as the model's output.
  • Save customers.sql.

✔️ Awesome, I've got everything!

Great. Make sure you've saved the file before moving on.

ⓧ I'd like to double check the full code

Your models/marts/customers.sql should have these sections in order:

  • The with keyword followed by three import CTEs (customers, orders, payments) each using {{ ref() }}.
  • The customer_orders CTE with min, max, and count aggregations grouped by customer_id.
  • The customer_payments CTE with a left join from payments to orders, summing the amount.
  • The final CTE joining all three previous CTEs together with coalesce on the total amount.
  • A closing select * from final at the very end.

File in the wrong place?

Make sure customers.sql is inside models/marts/ (not models/staging/ or the project root). dbt uses the folder path to apply the correct materialization config.

Also check that the entire file is one continuous query starting with with and ending with select * from final. There should be no stray semicolons between CTEs.

Help me debug my customers.sql mart model.

Run the pipeline and preview customer data

Your dbt_project.yml already has marts configured as tables from the previous step. That means dbt will materialize this model as a persistent table in DuckDB rather than a view.

Why tables for marts?

Views re-run their query every time someone reads from them. Tables store pre-computed results on disk.

Staging models are lightweight transformations (just renaming columns), so views are fine. Marts involve expensive joins and aggregations, so persisting the result as a table avoids re-computing it on every query.

Before you run this, what do you think will happen? Will dbt create the customers model as a view or a table?

  • Run the pipeline by executing:
dbt run

You should see output showing all four models completed successfully. The three staging models appear as views, and the customers mart appears as a table.

Now preview the actual data your mart produces. What columns do you expect to see in the output?

  • Preview the customers mart output by running:
dbt show --select customers

You should see a table with columns for customer_id, first_name, last_name, first_order_date, most_recent_order_date, number_of_orders, and customer_lifetime_value. Each row represents one customer with their full order history summarized.

Seeing a compilation error?

  • Check that each {{ ref() }} call uses the exact staging model name: stg_customers, stg_orders, stg_payments.
  • Verify there are commas between every CTE (after each closing parenthesis) except the last one (final).
  • Make sure the file does not end with a semicolon. dbt adds its own wrapping around your SQL.

Help me fix my dbt compilation error.

Your mart model is working. A single dbt run now transforms raw CSVs through staging views into a fully joined analytics table. Next up, you'll add automated tests and documentation to make this pipeline production-ready.

Add Tests and Documentation

Your pipeline now transforms raw data into a clean mart table. But nothing currently guards against bad data slipping through unnoticed.

Without tests, a corrupted CSV or a new unexpected payment method silently poisons your downstream analytics. Without documentation, the next person (or future you) has no idea what each model does. In this step, you'll add both to make your pipeline production-ready.

In this step, get ready to:
  • Add data quality tests to all staging models.
  • Add tests and descriptions to the customers mart.
  • Run dbt build to validate everything and generate a documentation site with your DAG.
Define tests for your staging models

dbt lets you declare data quality rules in YAML files that sit alongside your models. These generic tests run automatically and catch problems like null primary keys, duplicate rows, or unexpected values before they reach downstream consumers.

  • In VS Code, create a new file called _staging.yml inside your models/staging/ directory.
  • Add the following YAML to define tests for the stg_customers model:
models:
  - name: stg_customers
    description: Cleaned customer data with standardized column names.
    columns:
      - name: customer_id
        description: Primary key.
        data_tests:
          - unique
          - not_null

What do these tests do?

  • unique asserts that no two rows share the same customer_id value.
  • not_null asserts that every row has a value in customer_id. No blanks allowed.
  • Together they guarantee your primary key is reliable for downstream joins.
  • Below the stg_customers block (still inside the same models: list), add the stg_orders model definition:
  - name: stg_orders
    description: Cleaned order data with standardized column names.
    columns:
      - name: order_id
        description: Primary key.
        data_tests:
          - unique
          - not_null
      - name: status
        description: Order status.
        data_tests:
          - accepted_values:
              values: ['completed', 'returned']
      - name: customer_id
        description: Foreign key to stg_customers.
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

What are accepted_values and relationships?

accepted_values checks that every value in the status column is one of the values you list. If a new row contains cancelled (which you haven't accounted for), the test fails and alerts you.

relationships checks referential integrity. It ensures every customer_id in stg_orders actually exists in stg_customers. This catches orphaned records that would produce NULLs in your joins.

  • Save _staging.yml and run your tests on the first two models to confirm the YAML is valid:
dbt test

You should see each test listed with a PASS status, ending with a summary line like PASS=4 WARN=0 ERROR=0.

Seeing a YAML parsing error?

  • YAML is indent-sensitive. Each nested level uses exactly 2 spaces (not tabs). Check that columns: is indented 6 spaces from the left edge and data_tests: is indented 8 spaces.
  • Make sure the file is named exactly _staging.yml (with the leading underscore) and lives inside models/staging/.
  • Help me debug my _staging.yml YAML parsing error in dbt.
  • Return to _staging.yml and add the stg_payments model definition below stg_orders:
  - name: stg_payments
    description: Cleaned payment data with amount converted from cents to dollars.
    columns:
      - name: payment_id
        description: Primary key.
        data_tests:
          - unique
          - not_null
      - name: order_id
        description: Foreign key to stg_orders.
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_orders')
              field: order_id
      - name: payment_method
        description: Method of payment.
        data_tests:
          - accepted_values:
              values: ['credit_card', 'coupon', 'bank_transfer']

What does this add?

This mirrors the patterns from stg_orders. The relationships test on order_id verifies every payment links to a real order. The accepted_values test on payment_method catches any unexpected payment types.

  • Save _staging.yml.

✔️ Awesome, I've got everything!

Great. Make sure you've saved _staging.yml with all three model definitions before moving on.

ⓧ I'd like to double check the full code

Your _staging.yml should contain three model blocks in this order. Scroll up and confirm each one is present:

  • The models: key at the top, followed by stg_customers with unique and not_null on customer_id.
  • stg_orders with unique/not_null on order_id, accepted_values on status, and relationships on customer_id.
  • stg_payments with unique/not_null on payment_id, relationships on order_id, and accepted_values on payment_method.
Define tests and descriptions for your mart model

Your customers mart is the table that business users query. Adding descriptions here means dbt can auto-generate documentation that explains every column to anyone who opens the docs site.

  • Create a new file called _marts.yml inside your models/marts/ directory.
  • Add the following YAML to define tests and column descriptions for the customers model:
models:
  - name: customers
    description: >
      One record per customer with lifetime order metrics.
      Joins customer info with aggregated order and payment data.
    columns:
      - name: customer_id
        description: Primary key.
        data_tests:
          - unique
          - not_null
      - name: first_name
        description: Customer first name.
      - name: last_name
        description: Customer last name.
      - name: first_order_date
        description: Date of the customer's first order.
      - name: most_recent_order_date
        description: Date of the customer's most recent order.
      - name: number_of_orders
        description: Total number of orders placed by this customer.
      - name: customer_lifetime_value
        description: Total amount spent by this customer across all orders, in dollars.
        data_tests:
          - not_null

What does this file do?

The description fields document what the model and each column represent. These appear in the auto-generated documentation site you'll launch shortly.

The not_null test on customer_lifetime_value ensures every customer has a calculated spend value. The coalesce(..., 0) in your SQL handles this, but the test confirms it never breaks.

  • Save _marts.yml.

✔️ Awesome, I've got everything!

Great. Double check you've saved _marts.yml before continuing.

ⓧ I'd like to double check the full code

models:
  - name: customers
    description: >
      One record per customer with lifetime order metrics.
      Joins customer info with aggregated order and payment data.
    columns:
      - name: customer_id
        description: Primary key.
        data_tests:
          - unique
          - not_null
      - name: first_name
        description: Customer first name.
      - name: last_name
        description: Customer last name.
      - name: first_order_date
        description: Date of the customer's first order.
      - name: most_recent_order_date
        description: Date of the customer's most recent order.
      - name: number_of_orders
        description: Total number of orders placed by this customer.
      - name: customer_lifetime_value
        description: Total amount spent by this customer across all orders, in dollars.
        data_tests:
          - not_null
Run the full pipeline and generate documentation

The dbt build command runs seeds, models, and tests in dependency order. One command validates your entire pipeline end to end.

Before you run it, what do you expect to happen? Will all tests pass, or do you think any might fail?

  • Run dbt build to execute everything:
dbt build

You should see each seed, model, and test listed with a PASS status. The final summary line reads something like PASS=17 WARN=0 ERROR=0 (the exact count depends on your seeds, models, and tests combined).

Seeing ERROR in the summary?

  • Read the failing test name in the output. It tells you exactly which model and column failed (e.g. accepted_values_stg_orders_status).
  • If a relationships test fails, check that the to: and field: values in your YAML match the actual model and column names exactly.
  • If you see a compilation error, check your YAML indentation. Every level is exactly 2 spaces.
  • Help me debug my failing dbt test.

Now generate the documentation site so you can see your entire pipeline as a visual graph.

  • Generate the documentation catalog by running:
dbt docs generate
  • Launch the documentation site in your browser by running:
dbt docs serve

What does dbt docs serve do?

This starts a local web server on port 8080 and opens your browser to the documentation site. You can browse model descriptions, column details, and a visual lineage graph (the DAG) showing how data flows from seeds through staging into your mart.

Press Ctrl+C in your terminal when you're done browsing to stop the server.

  • In the documentation site, click the lineage graph icon (bottom-right corner) to see your DAG.
  • Confirm you can see arrows flowing from your seed files through staging models into the customers mart.

Browser didn't open automatically?

  • Open your browser manually and navigate to http://localhost:8080.
  • If port 8080 is already in use, the terminal output shows which port dbt selected instead.
  • Help me access my dbt documentation site.

Your pipeline is now tested and documented. Every time you run dbt build, those tests execute automatically and catch problems before bad data reaches your analytics tables. Next up, you'll take on the Secret Mission and build an incremental model that handles real-world data growth.

Secret mission

Add an Incremental Revenue Model

In production, tables with millions of rows can't be rebuilt from scratch on every run. Can you build a model that processes only new data, the way real dbt projects handle scale?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources in place, or delete them entirely.

Resources you used:

  • The byteshop project folder on your Desktop (contains models, seeds, virtual environment, and the .duckdb database file).
  • The profiles.yml file in your ~/.dbt/ directory (stores your DuckDB connection config).

Keep everything running

No action needed. Choose this if you want to keep building on this project or reference it later.

  • The .duckdb file is under 1 MB for this dataset, so it takes up almost no disk space.
  • The .gitignore already excludes target/, logs/, and dbt_packages/, so the project is ready to push to a public GitHub repository for your portfolio.
  • Your Python virtual environment and all installed packages stay inside the project folder.

Pause - I'll come back to this later

Stop any running processes to free up your terminal. Your files stay in place for when you return.

  • If dbt docs serve is still running, press Ctrl+C in that terminal to stop the documentation server.
  • Deactivate your virtual environment by running deactivate in your terminal.
  • Close VS Code. When you come back, reactivate the virtual environment and pick up where you left off.

Delete - I don't want to use this again

Remove all project files and configuration to start fresh.

  • Deactivate your virtual environment first by running deactivate in your terminal.
  • Delete the project folder by running the appropriate command for your operating system.

macOS

cd ~/Desktop
rm -rf byteshop

Windows

cd ~\Desktop
rmdir /s /q byteshop
  • Delete the dbt configuration directory that stores your connection profile.

macOS

rm -rf ~/.dbt

Windows

rmdir /s /q %USERPROFILE%\.dbt
  • Confirm the files are gone by running ls ~/Desktop (macOS) or dir ~\Desktop (Windows). The byteshop folder should no longer appear.

Nice Work!

Nice Work!

Nice work! You just built a production-style data transformation pipeline using dbt and DuckDB, entirely on your local machine.

You've learned how to:

  • Build a complete dbt project with the industry-standard staging-to-marts pattern, transforming raw CSV data into analytics-ready tables using DuckDB as your local warehouse.
  • Used the ref() function to wire up a DAG that dbt executes in dependency order, with materializations configured per layer (views for staging, tables for marts).
  • Added generic data tests to catch quality issues automatically and generated documentation with a visual lineage graph you can browse in your browser.
  • Secret Mission: Implemented an incremental model that processes only new data on subsequent runs, matching how production dbt projects handle large datasets.

Ready to quiz yourself?