Transform E-Commerce Data with dbt

Build a tested dbt pipeline that transforms raw data into analytics-ready tables.

Introduction

30 Second Summary

Every online store collects heaps of raw data about customers, orders, and payments. But raw data is messy, inconsistent, and impossible to analyze until someone transforms it into something useful.

In this project, you will build a complete data transformation pipeline using dbt and DuckDB that turns raw e-commerce CSV files into clean, tested, and documented analytics-ready tables.

What You'll Build

A local dbt project that transforms raw seed data (customers, orders, payments) through staging views and mart tables, with automated data tests and a visual lineage DAG.

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

  • A working dbt project with staging models that clean raw data and mart models that join it into business-ready tables.
  • Automated data tests that validate unique keys, null checks, accepted values, and referential integrity across your pipeline.
  • Auto-generated documentation with a visual data lineage DAG served locally in your browser.
  • Secret Mission: Build a monthly revenue metrics model and write a custom singular data test.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY) and using the terminal. No prior dbt experience is needed.

Set Up dbt Core and DuckDB

Before you can transform data, you need a working environment. This step gets dbt Core and DuckDB running on your machine.

dbt Core is the open-source tool that analytics engineers use to transform raw data into clean, tested models. DuckDB is an embedded analytical database that stores everything in a single local file. Together, they give you a full data transformation pipeline with zero cloud accounts and zero cost.

In this step, get ready to:
  • Clone the Jaffle Shop repository and navigate into the project.
  • Create a Python virtual environment and install dbt with the DuckDB adapter.
  • Load seed CSV data into DuckDB and explore the raw tables.
Clone the Jaffle Shop repository

The Jaffle Shop is a fictional e-commerce cafe created by dbt Labs specifically for learning dbt. The repository includes pre-configured project settings and CSV seed data you will transform throughout this project.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Clone the repository and move into the project folder by running these commands:
git clone https://github.com/dbt-labs/jaffle_shop_duckdb.git
cd jaffle_shop_duckdb
  • Confirm you are inside the project by running:
ls

You should see files like dbt_project.yml, requirements.txt, and a seeds/ folder in the output.

Don't see those files?

Make sure the git clone command completed without errors. If you see "fatal: repository not found", check that you have an internet connection and typed the URL correctly.

If you see a different folder listing, you may not have run cd jaffle_shop_duckdb after cloning.

help me clone the jaffle_shop_duckdb repository

Create a virtual environment and install dbt

A Python virtual environment keeps your dbt installation isolated from other Python projects on your machine. This project requires Python 3.13 or newer.

  • Check your Python version by running:
python3 --version

✔️ I see Python 3.13 or higher

You are good to go. Continue to the next instruction below.

ⓧ I see a lower version or command not found

This project requires Python 3.13 or newer. Visit python.org/downloads and install the latest version for your operating system.

After installing, close and reopen your terminal, then run python3 --version again to confirm.

Now create the virtual environment and install dbt.

macOS

  • Create and activate a virtual environment by running these commands:
python3 -m venv venv
source venv/bin/activate
  • Upgrade pip and install the project dependencies by running:
python3 -m pip install --upgrade pip
python3 -m pip install -r requirements.txt
  • Re-activate the virtual environment by running:
source venv/bin/activate

Windows

  • Create and activate a virtual environment by running these commands:
python -m venv venv
venv\Scripts\activate.bat
  • Upgrade pip and install the project dependencies by running:
python -m pip install --upgrade pip
python -m pip install -r requirements.txt
  • Re-activate the virtual environment by running:
venv\Scripts\activate.bat

Why activate the virtual environment twice?

Some shell environments (especially those using pyenv) cache binary paths. The second activation forces your shell to pick up the newly installed dbt binary from inside the virtual environment instead of a stale system path.

  • Verify dbt is installed correctly by running:
dbt --version

You should see dbt Core 1.11.x and the duckdb plugin listed in the output.

  • Run the debug command to confirm dbt can connect to DuckDB:
dbt debug

You should see "All checks passed!" at the bottom of the output.

Seeing "command not found" for dbt?

Make sure your virtual environment is active. You should see (venv) at the start of your terminal prompt. If not, run the activation command again.

If dbt debug shows a connection error, make sure you are inside the jaffle_shop_duckdb folder. The profiles.yml file in that folder tells dbt where to find the DuckDB database.

help me fix my dbt installation

Load seed data and explore the raw tables

dbt seeds are CSV files that dbt loads directly into your database as tables. The Jaffle Shop comes with three seed files representing raw data from a fictional e-commerce app.

  • Load the seed CSVs into DuckDB by running:
dbt seed

What does dbt seed do?

This command reads the CSV files in the seeds/ folder and creates real database tables from them. After this command, your jaffle_shop.duckdb file contains three tables: raw_customers, raw_orders, and raw_payments.

You should see output showing that three tables were loaded successfully.

  • Explore the raw_customers table by running:
duckdb jaffle_shop.duckdb -c "select * from raw_customers limit 5"

You should see a table with columns id, first_name, and last_name.

  • Explore the raw_orders table by running:
duckdb jaffle_shop.duckdb -c "select * from raw_orders limit 5"

You should see columns id, user_id, order_date, and status.

  • Explore the raw_payments table by running:
duckdb jaffle_shop.duckdb -c "select * from raw_payments limit 5"

You should see columns id, order_id, payment_method, and amount. Notice that the amount values are in cents (e.g. 1000 means $10.00).

What are you looking at?

These three tables represent the raw data layer of a typical analytics pipeline. Customers have names. Orders link customers to purchase events. Payments record how each order was paid.

The column names are inconsistent (id means different things in each table) and the payment amounts are stored in cents. In the next steps, you will write dbt models that clean up these issues.

Seeing "Could not set lock on file"?

DuckDB only allows one connection at a time. If you have another terminal session or database tool connected to jaffle_shop.duckdb, close that connection and try again.

help me troubleshoot my DuckDB connection

Your environment is ready and your raw data is loaded. Next up, you will write staging models that clean and rename this raw data into a consistent format.

Build Staging Models

Your raw data is loaded and ready. Now you need to clean it up before anyone can use it for analysis.

In dbt, the first transformation layer is called staging. Staging models map 1:1 with your raw tables and do simple clean-up work like renaming columns and converting units. In this step, you will write three staging models that prepare the raw Jaffle Shop data for downstream analysis.

In this step, get ready to:
  • Clear out the pre-built model files from the cloned repo.
  • Write three staging SQL models that clean and rename raw columns.
  • Run dbt and verify the staging views exist in DuckDB.
Clear out the pre-built models

The cloned repo comes with pre-built model files. You will delete them and write your own from scratch to practice building models hands-on.

  • Remove all existing model files and recreate an empty staging directory by running these commands:
rm -rf models/
mkdir -p models/staging

What does this do?

The first command deletes the entire models/ directory and all files inside it. The second command recreates the directory structure you need, with an empty models/staging/ folder ready for your new models.

  • Confirm the staging directory was created by running:
ls models/

You should see staging listed as the only item.

Write the staging models

What is a staging model?

A staging model is a SQL file that maps 1:1 with a raw source table. Its job is to rename columns to consistent conventions and do light transformations like unit conversions.

Staging models keep downstream models clean because all the messy raw-data quirks are handled in one place.

  • In your code editor, create a new file at models/staging/stg_customers.sql and paste the following code:
select
    id as customer_id,
    first_name,
    last_name
from {{ ref('raw_customers') }}

What does this model do?

  • id as customer_id renames the generic id column to a descriptive name that makes joins easier to read downstream.
  • {{ ref('raw_customers') }} is dbt's ref() function. It tells dbt which table to read from and builds a dependency link so dbt knows the correct run order.
  • Save stg_customers.sql.
  • Create a second file at models/staging/stg_orders.sql and paste the following code:
select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
from {{ ref('raw_orders') }}

What does this model do?

  • id as order_id gives the primary key a descriptive name.
  • user_id as customer_id renames the foreign key to match the naming convention from stg_customers. Consistent column names make joins obvious.
  • Save stg_orders.sql.
  • Create a third file at models/staging/stg_payments.sql and paste the following code:
select
    id as payment_id,
    order_id,
    payment_method,
    amount / 100.0 as amount
from {{ ref('raw_payments') }}

What does this model do?

  • amount / 100.0 as amount converts the raw payment amount from cents (stored as an integer) to dollars. This prevents every downstream model from having to remember to divide by 100.
  • order_id and payment_method are passed through unchanged because their names are already clear.
  • Save stg_payments.sql.
  • Confirm all three files are in place by running:
ls models/staging/

You should see stg_customers.sql, stg_orders.sql, and stg_payments.sql listed.

Missing a file?

Make sure you created the files inside models/staging/ and not in the project root. Double-check the file names end with .sql (not .txt or another extension).

Help me find my staging model files.

✔️ 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

Here are all three staging models for comparison.

select
    id as customer_id,
    first_name,
    last_name
from {{ ref('raw_customers') }}
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 dbt and verify your staging views

Your SQL files are just templates until dbt compiles and executes them against the database. Running dbt run turns each model file into a real database object.

  • Execute all your models by running this command:
dbt run

What does dbt run do?

dbt reads every .sql file in your models/ directory, resolves the ref() calls to build a dependency graph, and executes each model in the correct order. Since the default materialization is view, your staging models are created as views in DuckDB.

You should see output showing 3 models created successfully, each marked as a view.

  • Query the customers staging view to confirm it works by running:
duckdb jaffle_shop.duckdb -c "select * from stg_customers limit 5"

You should see a table with three columns: customer_id, first_name, and last_name. Notice that the original id column is now named customer_id.

  • Also verify the payments model converted cents to dollars by running:
duckdb jaffle_shop.duckdb -c "select * from stg_payments limit 5"

The amount column should show decimal values (like 10.0) instead of the raw integer cents (like 1000).

Seeing a compilation error?

  • If dbt says it cannot find raw_customers, make sure you ran dbt seed in Step 1. The seed tables must exist before your models can reference them.
  • If you see a SQL syntax error, check that you included the Jinja curly braces around ref(): it should be {{ ref('raw_customers') }} with double curly braces on both sides.
  • If dbt says no models were found, confirm your files are in models/staging/ and end with .sql.

Help me debug my dbt run error.

Your staging layer is complete. You now have three clean views that rename columns and convert units. Next up, you will build mart models that join these staging views together into analytics-ready tables.

Build Mart Models

Your staging models now provide clean, consistently named data. The next layer in a dbt project is the mart layer, where you join and aggregate staging models into business-ready tables.

In this step, you will build two mart models. The first summarizes each customer's order history and lifetime spending. The second enriches each order with a breakdown of payment methods.

In this step, get ready to:
  • Create a customers summary model using CTEs and ref().
  • Create an orders model with per-payment-method columns using Jinja.
  • Configure table materializations and run your models.
Create the customers model

The customers model answers the question: for each customer, when did they first order, when did they last order, how many orders have they placed, and how much have they spent in total?

You will build this by writing a series of CTEs (Common Table Expressions) that pull from your staging models using ref(), then join and aggregate the results.

  • Create a new file called models/customers.sql in your code editor 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 lifetime_value
    from payments
    left join orders on payments.order_id = orders.order_id
    group by orders.customer_id
),

final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders,
        coalesce(customer_payments.lifetime_value, 0) as 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 first three CTEs (customers, orders, payments) import all rows from each staging model using ref(). This tells dbt how the models depend on each other.
  • customer_orders groups orders by customer to calculate first order date, most recent order date, and total order count.
  • customer_payments joins payments back to orders (to get the customer_id), then sums the total amount spent per customer.
  • final left joins everything together. coalesce() ensures customers with no orders show 0 instead of NULL.
  • Save models/customers.sql.

You will not see a change in the database yet. The next substep adds the orders model, and then you will run everything together.

Create the orders model

The orders model enriches each order with a column for every payment method (credit card, coupon, bank transfer, gift card) plus a total amount. Instead of writing repetitive SQL for each method, you will use a Jinja for loop to generate the columns automatically.

  • Create a new file called models/orders.sql and paste the following code:
{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}

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

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

order_payments as (
    select
        order_id,
        {% for payment_method in payment_methods -%}
        sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount,
        {% endfor -%}
        sum(amount) as amount
    from payments
    group by order_id
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        {% for payment_method in payment_methods -%}
        coalesce(order_payments.{{ payment_method }}_amount, 0) as {{ payment_method }}_amount,
        {% endfor -%}
        coalesce(order_payments.amount, 0) as amount
    from orders
    left join order_payments on orders.order_id = order_payments.order_id
)

select * from final

What does the Jinja for loop do?

The {% set payment_methods = [...] %} line defines a list of payment method names. The {% for payment_method in payment_methods %} loop iterates over that list and generates a separate column for each one.

When dbt compiles this model, the loop expands into four sum(case when ...) expressions. This is a real-world dbt pattern that avoids writing the same logic four times.

  • Save models/orders.sql.

You will not see a change in the database yet. The next substep configures how these models are stored and runs everything.

Configure materializations and run your models

By default, dbt creates models as views. Views are lightweight because they do not store data. They re-run the SQL every time you query them.

Mart models serve downstream analytics, so you want them materialized as tables. Tables store the query results physically, making reads fast.

  • Open models/customers.sql and add this line at the very top of the file (above the with keyword):
{{ config(materialized='table') }}
  • Open models/orders.sql and add the same line at the very top of the file (above the {% set line):
{{ config(materialized='table') }}

What is a materialization?

A materialization tells dbt how to persist a model in the database. The two most common types are view (the default) and table.

Your staging models stay as views because they are simple renames. Your mart models use table because they aggregate and join data that downstream queries will read frequently.

  • Save both files.
  • Run all your models by executing this command in your terminal:
dbt run

You should see output showing 5 models passing. The staging models appear as view and the mart models appear as table.

  • Verify the customers table by querying it with DuckDB:
duckdb jaffle_shop.duckdb -c "select * from customers limit 5"

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

Seeing a compilation or database error?

  • Check that {{ config(materialized='table') }} is on its own line at the very top of each file, with no text above it.
  • Make sure your ref() calls reference the exact model names: stg_customers, stg_orders, stg_payments. These must match your staging file names without the .sql extension.
  • Confirm there are no extra commas after the last column in each CTE.

Still stuck? Ask for help.

✔️ Awesome, I've got everything!

Great. Double check you have saved both models/customers.sql and models/orders.sql.

ⓧ I'd like to double check the full code

Here is the complete models/customers.sql:

{{ config(materialized='table') }}

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 lifetime_value
    from payments
    left join orders on payments.order_id = orders.order_id
    group by orders.customer_id
),

final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders,
        coalesce(customer_payments.lifetime_value, 0) as 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

Here is the complete models/orders.sql:

{{ config(materialized='table') }}

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}

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

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

order_payments as (
    select
        order_id,
        {% for payment_method in payment_methods -%}
        sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount,
        {% endfor -%}
        sum(amount) as amount
    from payments
    group by order_id
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        {% for payment_method in payment_methods -%}
        coalesce(order_payments.{{ payment_method }}_amount, 0) as {{ payment_method }}_amount,
        {% endfor -%}
        coalesce(order_payments.amount, 0) as amount
    from orders
    left join order_payments on orders.order_id = order_payments.order_id
)

select * from final

Your mart models are live in the database. You now have a complete transformation layer: raw CSVs flow through staging views into analytics-ready tables. Next up, you will add automated data tests to catch quality issues before they reach your analysts.

Add Data Tests and Documentation

Your models are building clean, analytics-ready tables. But how do you know the data inside them is actually correct?

In production, data pipelines break silently. A source system changes a column name, a new status value appears, or a join accidentally duplicates rows. dbt solves this with data tests that validate your models every time they run. In this step, you will add automated tests and generate a documentation site with a visual lineage DAG.

In this step, get ready to:
  • Define data tests for all your models in a YAML schema file.
  • Run the tests and observe what happens when they fail.
  • Generate documentation and explore the lineage DAG in your browser.
Create a schema file with data tests

dbt's generic data tests let you assert properties about your columns without writing any SQL. You declare them in a YAML file alongside your models, and dbt generates the test queries for you.

  • Create a new file called models/schema.yml in your code editor.
  • Paste the following YAML into the file:
version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

  - name: stg_payments
    columns:
      - name: payment_id
        data_tests:
          - unique
          - not_null

  - name: customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id

What does this file do?

This schema file declares four types of generic data tests across your models:

  • unique ensures no duplicate values exist in a column.
  • not_null ensures every row has a value in that column.
  • accepted_values checks that a column only contains values from a specified list.
  • relationships verifies referential integrity. Every customer_id in orders must exist in the customers model.

Why data_tests instead of tests?

Since dbt v1.8, the recommended YAML key is data_tests: (renamed from tests:). The old key still works for backward compatibility, but data_tests: is the current standard.

  • Save models/schema.yml.

Seeing YAML indentation errors?

YAML is whitespace-sensitive. Make sure you use spaces (not tabs) and that nested items are indented consistently by 2 or 4 spaces. The values: key under accepted_values: must be indented one level deeper than accepted_values: itself.

Help me fix a YAML indentation error in my dbt schema.yml file.

Run your data tests

With the schema file saved, dbt can now run all the tests you defined. Each test generates a SQL query behind the scenes. If the query returns zero rows, the test passes.

  • Run all data tests by running this command:
dbt test
  • You should see output showing each test passing with a green PASS status. All tests should pass.

What should I see?

dbt will list each test it runs (like unique_stg_customers_customer_id, not_null_orders_order_id) followed by a pass/fail status. At the bottom, you will see a summary like 11 of 11 PASS.

Now let's see what a failing test looks like. This helps you understand how dbt reports data quality issues.

  • Open models/schema.yml in your editor.
  • Find the accepted_values test under stg_orders. Find this line:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
  • Remove 'shipped' from the list so the line looks like this:
              values: ['placed', 'completed', 'return_pending', 'returned']
  • Save the file and run the tests again:
dbt test
  • You should see the accepted_values test fail with a red FAIL status. dbt tells you exactly which test failed and how many rows violated the rule.
  • Now revert the change. Add 'shipped' back into the values list so it matches the original:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
  • Save models/schema.yml.

Tests still failing after reverting?

Double check that you added 'shipped' back in the correct position with the correct punctuation. The list should have exactly five values separated by commas inside square brackets.

Help me fix my accepted_values test in schema.yml.

Generate documentation and explore the lineage DAG

dbt can auto-generate a documentation website from your project. This includes every model's SQL, column definitions, test details, and a visual lineage DAG showing how data flows from raw sources through your transformations.

  • Generate the documentation catalog by running this command:
dbt docs generate

What does this command do?

This command scans your project and creates a catalog.json and manifest.json in your target/ directory. These files contain all the metadata dbt needs to render the documentation site.

  • Launch the documentation site locally by running:
dbt docs serve
  • Your browser should open automatically to the documentation site on port 8080. If it does not, open your browser and navigate to http://localhost:8080.
  • Click the blue circular icon in the bottom-right corner of the page to open the lineage DAG.
  • You should see a graph showing your raw seeds (raw_customers, raw_orders, raw_payments) flowing into staging views, which then flow into your mart tables.
  • Click on any model in the DAG to see its SQL, columns, and tests.

What is a lineage DAG?

A DAG (Directed Acyclic Graph) shows the dependency relationships between your models. Each node is a model or seed, and arrows show which models depend on others via the ref() function. This makes it easy to see the full data pipeline at a glance.

  • When you are done exploring, press Ctrl+C in your terminal to stop the documentation server.

Port 8080 already in use?

If another process is using port 8080, dbt will show an error. You can specify a different port by running dbt docs serve --port 8081 instead.

Help me resolve a port conflict with dbt docs serve.

✔️ Awesome, I've got everything!

Great. Double check you have saved models/schema.yml and that all tests pass when you run dbt test.

ⓧ I'd like to double check the full code

Here is the complete models/schema.yml file for comparison:

version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

  - name: stg_payments
    columns:
      - name: payment_id
        data_tests:
          - unique
          - not_null

  - name: customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id

Your dbt project is now fully tested and documented. You have automated validations catching data quality issues and a visual map of your entire pipeline. Next up, you will tackle a Secret Mission to extend your project even further.

Secret mission

Monthly Revenue Model and Custom Test

Your dbt pipeline transforms raw data into clean customer and order tables. But what if stakeholders need monthly revenue trends? Build an aggregated metrics model and write a custom singular data test to validate the numbers.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally using DuckDB, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Python virtual environment (venv folder inside the project directory).
  • DuckDB database file (jaffle_shop.duckdb in the project root).
  • Cloned Git repository (jaffle_shop_duckdb folder on your Desktop).
  • dbt-generated documentation site (runs on port 8080 when active).

Keep everything running

No action needed. Choose this if you are still actively building or want to experiment with additional dbt models.

  • Your jaffle_shop_duckdb folder stays on your Desktop with all your models and the DuckDB database intact.
  • To resume working, open your terminal, navigate to the project folder, and activate the virtual environment with source venv/bin/activate.
  • The documentation server is not running unless you start it again with dbt docs serve.

Pause - I'll come back to this later

Shut down any running processes to free up system resources. Your files stay safe on disk.

  • If the documentation server is still running, press Ctrl+C in the terminal to stop it.
  • Deactivate the virtual environment by running deactivate in your terminal.
  • Your project folder, DuckDB database, and all model files remain on your Desktop for next time.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes everything you built in this project.

  • If the documentation server is still running, press Ctrl+C in the terminal to stop it.
  • Deactivate the virtual environment by running deactivate.
  • Navigate to your Desktop and delete the entire jaffle_shop_duckdb folder.
  • This removes the virtual environment, DuckDB database file, seed data, models, and all generated documentation.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete analytics engineering pipeline with dbt and DuckDB. From raw CSV seed data to tested, documented, analytics-ready tables, this is the same workflow professional analytics engineers use every day.

You've learned how to:

  • Built staging models that clean and rename raw data, and mart models that join and aggregate staging layers into business-ready tables with configurable materializations.
  • Added automated data tests (unique, not_null, accepted_values, relationships) that catch data quality issues before they reach your stakeholders.
  • Generated documentation with a visual data lineage DAG showing how raw seeds flow through staging models to mart models.
  • Built a monthly revenue metrics model and wrote a custom singular data test to validate business logic.

Ready to quiz yourself?