Analytics Pipeline with dbt and DuckDB

Build a layered ELT pipeline with dbt, DuckDB, and automated data quality tests.

Introduction

30 Second Summary

Every company collects mountains of raw data from orders, customers, and payments. Turning that mess into clean, trustworthy insights is the job of a data engineer.

In this project, you will build a complete analytics pipeline that transforms raw CSV data into tested, documented models using dbt and a local DuckDB database.

What You'll Build

You'll create a layered dbt project with staging models, a customers mart, automated data quality tests, and browsable documentation with a visual dependency graph.

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

  • A dbt project with staging and mart layers that transform raw e-commerce data into analytics-ready tables.
  • A local DuckDB database file containing your transformed data, queryable directly from the terminal.
  • Automated data quality tests that validate your pipeline output before it reaches downstream consumers.
  • Auto-generated documentation with a visual DAG you can browse in a local web UI.
  • Secret Mission: Create a reusable Jinja macro and a revenue-by-month mart with date-based aggregation.

Are there any prerequisites?

You should have basic SQL knowledge (SELECT, JOIN, GROUP BY) and basic Python knowledge (enough to run commands in a terminal). Familiarity with the command line is helpful but not required.

Set Up Your dbt Environment

Every analytics team needs a way to transform raw data into clean, reliable tables that analysts can query. That's what dbt (data build tool) does.

In this step, you'll create a Python virtual environment, install dbt with the DuckDB adapter, scaffold a new project, and configure the folder structure that production dbt projects use.

In this step, get ready to:
  • Create a virtual environment and install dbt-duckdb.
  • Initialize and configure a dbt project for DuckDB.
  • Set up the folder structure for staging and mart models.
Create a Python virtual environment and install dbt-duckdb

A virtual environment keeps your project's Python packages isolated from everything else on your machine. This prevents version conflicts between projects.

  • 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
  • Create your project folder and move into it by running these commands:
mkdir ecommerce_analytics
cd ecommerce_analytics
  • Create and activate a Python virtual environment by running these commands:
python3 -m venv venv
source venv/bin/activate

What does activating a virtual environment do?

When you activate a virtual environment, your terminal uses the Python and pip inside the venv/ folder instead of your system-wide Python. You'll see (venv) appear at the start of your terminal prompt to confirm it's active.

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

What does this install?

The dbt-duckdb package installs both dbt Core and the DuckDB adapter in one go. dbt Core is the transformation engine, and the adapter tells dbt how to talk to DuckDB specifically.

This install takes a minute or two. Once it finishes, you should see a success message.

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

You should see version numbers for dbt-core and dbt-duckdb printed in your terminal.

Command not found?

Make sure your virtual environment is active. You should see (venv) at the start of your terminal prompt. If not, run source venv/bin/activate again from inside the ecommerce_analytics folder.

help me fix a dbt command not found error after installing dbt-duckdb in a virtual environment

Initialize a new dbt project and configure it for DuckDB

Now that dbt is installed, you'll scaffold a project structure. The dbt init command generates all the folders and config files a dbt project needs.

  • Scaffold your dbt project by running:
dbt init ecommerce_analytics --skip-profile-setup

What does this command do?

This creates a subfolder called ecommerce_analytics with sample models, a dbt_project.yml config file, and folder stubs for models, tests, macros, and more.

The --skip-profile-setup flag tells dbt not to launch the interactive connection wizard. You'll configure the connection manually in the next step for full control over the settings.

  • Move the scaffolded files up into your current directory by running:
mv ecommerce_analytics/* .
mv ecommerce_analytics/.gitignore .
rm -rf ecommerce_analytics

Why move files up?

The dbt init command always creates a new subdirectory. Since you already created the ecommerce_analytics folder (with your virtual environment inside), you need to move the scaffold files one level up so everything lives in the same project root.

  • Confirm your project files are in place by running:
ls

You should see dbt_project.yml, models/, macros/, tests/, seeds/, and venv/ listed in the output.

Next, you need to tell dbt how to connect to DuckDB. This is done through a profiles.yml file stored in the ~/.dbt/ directory on your machine.

  • Create the ~/.dbt/ directory (if it doesn't already exist) by running:
mkdir -p ~/.dbt
  • Create and open the profiles file by running:
open ~/.dbt/profiles.yml 2>/dev/null || touch ~/.dbt/profiles.yml && open ~/.dbt/profiles.yml

Use your code editor instead

If the above command doesn't open a text editor, you can open the file in VS Code instead. In VS Code, press Cmd+O (macOS) or Ctrl+O (Windows), then navigate to your home folder, open the .dbt folder, and open profiles.yml. If the file doesn't exist, create it there.

  • Paste the following configuration into profiles.yml:
ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './ecommerce_analytics.duckdb'
      schema: main
      threads: 4

What does this configuration do?

  • type: duckdb tells dbt to use the DuckDB adapter.
  • path: './ecommerce_analytics.duckdb' creates a local database file in your project folder when dbt runs.
  • schema: main sets the default schema where your models will be created.
  • threads: 4 lets dbt run up to 4 models in parallel for faster builds.
  • Save profiles.yml and close the file.
  • Verify your connection works by running:
dbt debug

You should see a series of green checkmarks and All checks passed! at the bottom of the output.

Seeing a connection error?

Make sure you're running dbt debug from inside the ecommerce_analytics folder (the one containing dbt_project.yml). Also check that the profile name in dbt_project.yml matches the top-level key in ~/.dbt/profiles.yml. Both should say ecommerce_analytics.

help me debug a dbt debug connection failure with DuckDB

Clean up the scaffolded project

The dbt init command created sample model files you don't need. You'll remove those and set up the folder structure that production dbt projects use: a staging layer and a marts layer.

  • Remove the sample models that dbt init created by running:
rm -rf models/example
  • Create the staging and marts folders by running:
mkdir -p models/staging models/marts

What are staging and marts?

This two-layer structure is a dbt best practice. The staging layer cleans and renames raw source data without changing its grain. The marts layer combines staged data into business-friendly tables that analysts query directly.

Staging models are materialized as views (lightweight, always up-to-date). Marts are materialized as tables (faster to query, stored on disk).

Now you need to tell dbt how to materialize models in each folder. You'll update dbt_project.yml to configure this.

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

profile: 'ecommerce_analytics'

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

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

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

What does the models section do?

  • +materialized: view means every model inside models/staging/ will be created as a database view.
  • +materialized: table means every model inside models/marts/ will be stored as a physical table.
  • The + prefix means this config applies to all models in that folder, not just one specific model.
  • Save dbt_project.yml.
  • Confirm everything still connects by running:
dbt debug

You should see All checks passed! again, confirming your updated config is valid.

Seeing a YAML parsing error?

YAML is whitespace-sensitive. Make sure you're using spaces (not tabs) for indentation, and that nested keys are indented by exactly 2 spaces. Double-check that the models: section uses the exact structure shown above.

help me fix a YAML parsing error in my dbt_project.yml file

✔️ Awesome, I've got everything!

Great. Double check you've saved both dbt_project.yml and ~/.dbt/profiles.yml.

ⓧ I'd like to double check the full code

Here's what dbt_project.yml should look like:

name: 'ecommerce_analytics'
version: '1.0.0'

profile: 'ecommerce_analytics'

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

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

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

And here's what ~/.dbt/profiles.yml should look like:

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

Your dbt environment is ready. You have a working connection to DuckDB, a clean project structure, and materialization rules in place. Next up, you'll load raw data into DuckDB using dbt seeds.

Load Raw Data with Seeds

Your dbt project is scaffolded, configured, and connected to a local DuckDB database. Now you need data to transform.

In a production environment, raw data lives in a warehouse loaded by an ingestion tool. For this project, you will use dbt seeds to load CSV files directly into your database. This keeps the project zero-dependency and lets you focus on the transformation layer.

In this step, get ready to:
  • Create three CSV seed files representing raw e-commerce data.
  • Load the seeds into your DuckDB database.
  • Verify the data is queryable.
Create CSV seed files for raw data

Seeds are CSV files that dbt loads directly into your database as tables. You will create three files representing the raw data for a fictional e-commerce company: customers, orders, and payments.

What are seeds in dbt?

Seeds are small, version-controlled CSV files stored in your seeds/ directory. When you run dbt seed, dbt reads these files and creates corresponding tables in your database.

Seeds work best for small, static reference data like lookup tables, test fixtures, or sample datasets. They are not designed for large production data loads.

  • Create a new file called raw_customers.csv inside the seeds/ folder of your project.
  • Paste in the following customer data:
customer_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.

What does this data represent?

This file defines 10 customers with unique IDs and names. The customer_id column will be the primary key used to join with orders later in the pipeline.

  • Save raw_customers.csv.
  • Create a second file called raw_orders.csv inside the seeds/ folder.
  • Paste in the following order data:
order_id,customer_id,order_date,status
1,1,2024-01-01,completed
2,3,2024-01-02,completed
3,5,2024-01-04,completed
4,1,2024-01-05,completed
5,2,2024-01-08,completed
6,3,2024-01-10,returned
7,7,2024-01-11,completed
8,1,2024-01-12,return_pending
9,4,2024-01-15,completed
10,8,2024-01-17,completed

What does this data represent?

Each row is an order tied to a customer via customer_id. Notice the status column has three possible values: completed, returned, and return_pending. You will test these accepted values later in Step 5.

  • Save raw_orders.csv.
  • Create a third file called raw_payments.csv inside the seeds/ folder.
  • Paste in the following payment data:
payment_id,order_id,payment_method,amount
1,1,credit_card,1000
2,2,credit_card,2000
3,3,coupon,500
4,4,credit_card,2500
5,5,bank_transfer,1700
6,6,credit_card,1200
7,7,credit_card,3000
8,8,credit_card,800
9,9,bank_transfer,2200
10,10,coupon,1500

Why is the amount in cents?

Storing monetary values in cents (integers) avoids floating-point rounding errors. A value of 1000 means $10.00. You will convert cents to dollars in your staging model later.

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

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

Don't see the files?

Make sure you created the files inside the seeds/ directory, not in the project root. The seeds/ folder was created automatically when you ran dbt init in Step 1.

Also check that each file uses the exact .csv extension, not .txt or any other format.

Still stuck? Help me find my seed files in the dbt project structure.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files.

ⓧ I'd like to double check the full code

Here are the three seed files for comparison:

customer_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.
order_id,customer_id,order_date,status
1,1,2024-01-01,completed
2,3,2024-01-02,completed
3,5,2024-01-04,completed
4,1,2024-01-05,completed
5,2,2024-01-08,completed
6,3,2024-01-10,returned
7,7,2024-01-11,completed
8,1,2024-01-12,return_pending
9,4,2024-01-15,completed
10,8,2024-01-17,completed
payment_id,order_id,payment_method,amount
1,1,credit_card,1000
2,2,credit_card,2000
3,3,coupon,500
4,4,credit_card,2500
5,5,bank_transfer,1700
6,6,credit_card,1200
7,7,credit_card,3000
8,8,credit_card,800
9,9,bank_transfer,2200
10,10,coupon,1500
Load the seeds into DuckDB

With your CSV files in place, you can now tell dbt to read them and create corresponding tables in your DuckDB database.

  • Load all seed files into your database by running:
dbt seed

What does this command do?

The dbt seed command reads every CSV file in your seeds/ directory and loads it into the database as a table. Each file becomes its own table named after the filename.

In this case, raw_customers.csv becomes the raw_customers table, raw_orders.csv becomes raw_orders, and raw_payments.csv becomes raw_payments.

You should see output confirming all three seeds were loaded successfully, with each showing OK and a row count of 10.

Seeing a connection error?

If dbt reports a connection failure, run dbt debug to check your profile configuration. Confirm that ~/.dbt/profiles.yml has the correct project name ecommerce_analytics and that type: duckdb is set.

If you see a file-parsing error, check that your CSV files have no trailing commas or extra blank lines at the end.

Need more help? Help me debug my dbt seed command.

  • Verify the data is queryable by previewing one of the tables:
dbt show --select "raw_customers"

You should see a formatted table in your terminal showing all 10 customer rows with their IDs, first names, and last names.

What is dbt show?

The dbt show command previews query results directly in your terminal. It is useful for quick checks without needing a separate database client.

Not seeing any output?

Make sure you ran dbt seed successfully before running dbt show. The show command queries data that must already exist in the database.

Also check that the select argument matches the seed filename exactly: raw_customers (no .csv extension).

Still stuck? Help me query my seeded data in dbt.

Your raw data is loaded and ready for transformation. Next up, you will build staging models that clean and standardize this data into a consistent format.

Build Staging Models

Your raw data is loaded into DuckDB as three tables. Right now, the column names and data types are exactly as they appeared in the CSV files. Before you can build analytics on top of this data, you need a clean, consistent layer that standardizes naming and fixes types.

In dbt, this clean layer is called the staging layer. Staging models sit between your raw sources and your business-logic models. Each one maps 1:1 to a source table and handles light transformations like renaming columns or converting data types.

In this step, get ready to:
  • Create a sources file that tells dbt where your raw data lives.
  • Build three staging models that clean and rename your raw data.
  • Run the staging layer and verify the output in your terminal.
Create a sources YAML file

Before your staging models can reference the raw tables, dbt needs to know where those tables live. A sources file is a YAML configuration that maps friendly names to actual database tables.

  • Create a new file called _sources.yml inside your models/staging/ folder.
  • Paste the following into the file:
version: 2

sources:
  - name: ecommerce
    description: "Raw e-commerce data loaded from CSV seeds"
    schema: main
    tables:
      - name: raw_customers
        description: "Raw customer data"
      - name: raw_orders
        description: "Raw order data"
      - name: raw_payments
        description: "Raw payment data with amounts in cents"

What does this file do?

This file defines a source called ecommerce that points to the main schema in your DuckDB database.

Each table entry maps to one of the seed tables you loaded in the previous step. Your staging models will use {{ source('ecommerce', 'raw_customers') }} instead of hardcoding table names. This means if you ever move your raw data to a different schema, you only update it in one place.

  • Save _sources.yml.
  • Confirm the file was created by running:
ls models/staging/

You should see _sources.yml listed in the output.

Don't see the file?

Make sure you created the file inside models/staging/ and not in the project root or another folder. Double check that the filename starts with an underscore: _sources.yml.

Help me find my file

Why does the filename start with an underscore?

In dbt projects, YAML config files that describe models or sources are prefixed with an underscore by convention. This visually separates them from the .sql model files in the same folder.

Build staging models for each source

Each staging model is a SQL file that selects from one source table and applies light transformations. The pattern is the same for each: pull from the source, rename or cast columns, and output a clean result.

  • Create a new file called stg_customers.sql inside models/staging/ and paste in the following:
with source as (

    select * from {{ source('ecommerce', 'raw_customers') }}

),

renamed as (

    select
        customer_id,
        first_name,
        last_name

    from source

)

select * from renamed

What does this model do?

This model pulls all rows from the raw_customers source table. It uses a CTE (Common Table Expression) pattern to keep the SQL readable.

For this table, the columns are already clean so the model just passes them through. In a real project, you might rename cust_id to customer_id or drop unused columns here.

  • Save stg_customers.sql.
  • You won't see a visible change yet. The next two models follow the same pattern, and you will run all three together at the end of this substep.

Seeing a syntax highlight error in VS Code?

The {{ source(...) }} syntax is Jinja templating, not plain SQL. VS Code may underline it as invalid. This is safe to ignore. dbt will compile the Jinja into valid SQL at runtime.

Help me with this syntax error

  • Create a new file called stg_orders.sql inside models/staging/ and paste in the following:
with source as (

    select * from {{ source('ecommerce', 'raw_orders') }}

),

renamed as (

    select
        order_id,
        customer_id,
        cast(order_date as date) as order_date,
        status

    from source

)

select * from renamed

What does this model do?

This model pulls from raw_orders and explicitly casts order_date from a string to a proper date type. This ensures downstream models can reliably use date functions like min() or max() without type errors.

  • Save stg_orders.sql.
  • One more model to go. This last one includes a numeric transformation.
  • Create a new file called stg_payments.sql inside models/staging/ and paste in the following:
with source as (

    select * from {{ source('ecommerce', 'raw_payments') }}

),

renamed as (

    select
        payment_id,
        order_id,
        payment_method,
        amount / 100.0 as amount

    from source

)

select * from renamed

What does this model do?

The raw payment amounts are stored in cents (e.g. 1000 means $10.00). Dividing by 100.0 converts them to dollars. Using 100.0 (with the decimal) ensures the result is a float, not integer division.

  • Save stg_payments.sql.
  • Confirm all your staging files exist by running:
ls models/staging/

You should see four files: _sources.yml, stg_customers.sql, stg_orders.sql, and stg_payments.sql.

Missing a file?

Make sure all four files are saved inside models/staging/ specifically. If a file ended up in models/ or the project root, move it into the staging subfolder.

Help me find my missing file

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Here are all four staging files for comparison.

version: 2

sources:
  - name: ecommerce
    description: "Raw e-commerce data loaded from CSV seeds"
    schema: main
    tables:
      - name: raw_customers
        description: "Raw customer data"
      - name: raw_orders
        description: "Raw order data"
      - name: raw_payments
        description: "Raw payment data with amounts in cents"
with source as (

    select * from {{ source('ecommerce', 'raw_customers') }}

),

renamed as (

    select
        customer_id,
        first_name,
        last_name

    from source

)

select * from renamed
with source as (

    select * from {{ source('ecommerce', 'raw_orders') }}

),

renamed as (

    select
        order_id,
        customer_id,
        cast(order_date as date) as order_date,
        status

    from source

)

select * from renamed
with source as (

    select * from {{ source('ecommerce', 'raw_payments') }}

),

renamed as (

    select
        payment_id,
        order_id,
        payment_method,
        amount / 100.0 as amount

    from source

)

select * from renamed
Run and verify the staging layer

Your staging models are defined but haven't been executed yet. Running them tells dbt to compile the Jinja, execute the SQL against DuckDB, and create views for each model.

  • Build all three staging models by running:
dbt run --select staging

You should see output confirming that three models were created successfully. Each line shows OK next to the model name.

What does this command do?

The --select staging flag tells dbt to only run models inside the models/staging/ folder. Because you configured +materialized: view for staging in dbt_project.yml, dbt creates these as database views rather than physical tables.

Views are lightweight since they store the query definition, not the data itself.

Seeing a compilation error?

Check that your _sources.yml file uses correct YAML indentation (spaces, not tabs). Also verify that the source name ecommerce in your YAML matches exactly what your SQL files reference in {{ source('ecommerce', ...) }}.

Help me debug this error

  • Verify the cents-to-dollars conversion worked by previewing the payments model:
dbt show --select "stg_payments"

You should see a table of results where the amount column shows dollar values like 10.0, 20.0, and 5.0 instead of the original cent values (1000, 2000, 500).

What is dbt show?

The dbt show command lets you preview query results directly in your terminal without needing a separate database client. It compiles and runs the model's SQL, then displays the first rows of output.

Your staging layer is built and verified. Next up, you will combine these three clean models into a single analytics-ready customers mart.

Build a Customers Mart

Your staging models are live and serving clean data from the raw seed tables. Now it is time to combine that data into something an analyst would actually query.

A mart is a model built for a specific business audience. In this step, you will create a customers mart that joins order, payment, and customer data into a single analytics-ready table.

In this step, get ready to:
  • Create a customers mart model that aggregates order and payment data per customer.
  • Run the full pipeline and verify the mart output in your terminal.
Create the customers mart model

The customers mart pulls from all three staging models using ref() calls. Each ref() tells dbt that one model depends on another, which builds the DAG (dependency graph) automatically.

  • Create a new file called customers.sql inside the models/marts/ folder.
  • Paste the following SQL into customers.sql:
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?

This model uses CTEs (Common Table Expressions) to break the transformation into logical steps:

  • customers, orders, and payments pull cleaned data from the staging layer using ref().
  • customer_orders calculates each customer's first order date, most recent order date, and total number of orders.
  • customer_payments joins payments to orders so it can sum up each customer's total spend (lifetime value).
  • final joins everything together and uses coalesce() to default missing values to 0 (for customers who have never ordered).
  • Save customers.sql.

You will not see a change yet. The next substep runs dbt build to execute this model against your database.

✔️ Awesome, I've got everything!

Great. Make sure you have saved models/marts/customers.sql before moving on.

ⓧ I'd like to double check the full 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
Run the full pipeline

The dbt build command executes seeds, models, and tests in dependency order. It is the single command you will use most often to run your entire project end-to-end.

  • Run the full pipeline by running this command:
dbt build

What does dbt build do?

dbt build runs everything in your project in the correct order: seeds first, then staging views, then the customers mart table. It respects the ref() calls you wrote to figure out what depends on what.

Because your dbt_project.yml sets marts: +materialized: table, the customers model is written as a physical table in DuckDB. Staging models remain as lightweight views.

  • You should see terminal output confirming that each seed, model, and test completed with an OK status.
  • Verify the mart output by running this command:
dbt show --select "customers"

You should see a table in your terminal with columns for customer_id, first_name, last_name, first_order_date, most_recent_order_date, number_of_orders, and lifetime_value. Each row represents a single customer with their aggregated order and payment data.

Seeing a compilation error?

  • Check that your file is saved at models/marts/customers.sql (not inside models/staging/).
  • Verify each ref() matches the exact staging model name: stg_customers, stg_orders, stg_payments.
  • Make sure there are no missing commas between CTEs. Each CTE block ends with a closing parenthesis followed by a comma, except the last one (final).

Help me debug a dbt compilation error in my customers mart model.

Your customers mart is live and queryable. Next up, you will add automated data quality tests to catch bad data before it reaches downstream consumers.

Add Data Quality Tests and Documentation

Your pipeline is producing data, but how do you know the data is correct? In production, bad data slips in all the time. Duplicate IDs, null values, and unexpected statuses can break downstream dashboards without anyone noticing.

In this step, you will add automated data quality tests that catch problems before they reach consumers. You will also generate browsable documentation that shows every model, column, and dependency in your project.

In this step, get ready to:
  • Add schema tests to your staging and mart models.
  • Run the test suite and verify all assertions pass.
  • Generate and browse auto-generated documentation with a visual DAG.
Add schema tests to your staging models

dbt ships with four built-in generic tests: unique, not_null, accepted_values, and relationships. You declare them in YAML files alongside your models, and dbt generates the SQL assertions automatically.

  • Create a new file called _staging__models.yml inside your models/staging/ folder.
  • Paste the following YAML into the file:
version: 2

models:
  - name: stg_customers
    description: "Cleaned customer data"
    columns:
      - name: customer_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    description: "Cleaned order data"
    columns:
      - name: order_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null
      - name: status
        description: "Order status"
        data_tests:
          - accepted_values:
              values: ['completed', 'returned', 'return_pending', 'placed']

  - name: stg_payments
    description: "Cleaned payment data with amount in dollars"
    columns:
      - name: payment_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null

What does this YAML do?

Each model entry lists the columns you want to assert properties on. The data_tests key tells dbt which generic tests to run against that column.

  • unique checks that no two rows share the same value in that column.
  • not_null checks that every row has a value (no NULLs).
  • accepted_values checks that every value in the column matches one of the values you list. If a row contains a status like cancelled that is not in your list, the test fails.
  • Save _staging__models.yml.

Getting a YAML parsing error?

  • YAML is whitespace-sensitive. Make sure you use spaces (not tabs) and that each indentation level is exactly two spaces.
  • Check that data_tests is spelled exactly as shown. Older dbt versions used tests but the current key is data_tests.

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

Add schema tests to your customers mart

The mart layer is what downstream consumers query directly. Tests here guarantee that the final output is trustworthy.

  • Create a new file called _marts__models.yml inside your models/marts/ folder.
  • Paste the following YAML into the file:
version: 2

models:
  - name: customers
    description: "Customer mart with aggregated order and payment data"
    columns:
      - name: customer_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null
      - name: first_name
        data_tests:
          - not_null
      - name: last_name
        data_tests:
          - not_null

What does this test?

This ensures that every customer has a unique, non-null ID, and that no customer is missing a first or last name. If a join in your mart accidentally duplicates rows or drops names, these tests will catch it.

  • Save _marts__models.yml.

✔️ Awesome, I've got everything!

Great. Make sure both YAML files are saved before moving on.

ⓧ I'd like to double check the full code

Here is the full _staging__models.yml:

version: 2

models:
  - name: stg_customers
    description: "Cleaned customer data"
    columns:
      - name: customer_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    description: "Cleaned order data"
    columns:
      - name: order_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null
      - name: status
        description: "Order status"
        data_tests:
          - accepted_values:
              values: ['completed', 'returned', 'return_pending', 'placed']

  - name: stg_payments
    description: "Cleaned payment data with amount in dollars"
    columns:
      - name: payment_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null

And here is the full _marts__models.yml:

version: 2

models:
  - name: customers
    description: "Customer mart with aggregated order and payment data"
    columns:
      - name: customer_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null
      - name: first_name
        data_tests:
          - not_null
      - name: last_name
        data_tests:
          - not_null
Run your test suite

Now that your tests are defined, dbt can execute them. Each test becomes a SQL query that looks for failing rows. Zero failures means the test passes.

  • Run all your data quality tests by running this command:
dbt test
  • You should see each test listed in the terminal output with a pass status next to each test name.
  • At the bottom, you should see a summary showing all tests passed with zero errors.

What is each test doing?

Behind the scenes, dbt constructs a SELECT query for each test. For a unique test, it groups by the column and filters for duplicates. For not_null, it selects rows where the value IS NULL.

If the query returns zero rows, the assertion holds and the test passes. If it returns any rows, those are the failing records and the test fails.

Seeing test failures?

  • If an accepted_values test fails, check that the values list in your YAML matches the actual values in your seed data. Look at raw_orders.csv to confirm the status values.
  • If a unique test fails, you may have duplicate rows in your seed CSV. Check that each ID column has distinct values.

Help me debug a failing dbt test.

Generate and browse your project documentation

dbt can auto-generate a browsable documentation site from your model descriptions, column definitions, and the dependency graph. This gives you a visual map of how data flows through your project.

  • Generate the documentation artifacts by running this command:
dbt docs generate
  • This creates HTML and JSON files inside the target/ directory.
  • Launch the documentation site by running this command:
dbt docs serve
  • Your default browser should open automatically to http://localhost:8080.
  • Click the blue DAG icon in the bottom-right corner to open the dependency graph.
  • Explore the graph to see how your seeds flow into staging models, which then feed into the customers mart.
  • Click on any model node in the graph to see its description, columns, and test definitions.

What is a DAG?

A DAG (Directed Acyclic Graph) is a visual representation of dependencies. Each node is a model, source, or test. Arrows show which models depend on which.

"Acyclic" means there are no circular dependencies. Data flows in one direction only, from sources through staging to marts.

Stop the docs server

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

Browser didn't open automatically?

  • Open your browser manually and navigate to http://localhost:8080.
  • If you see a port-in-use error, another process is using port 8080. Stop that process or close and reopen your terminal before running the command again.

Help me troubleshoot dbt docs serve not opening.

Your analytics pipeline is now tested, documented, and browsable. This is the exact workflow data engineers use in production: define models, test assumptions, and ship documentation alongside the code.

Secret mission

Add a Revenue-by-Month Mart with a Custom Macro

Your pipeline works, but it has a hidden weakness. The cents-to-dollars conversion in stg_payments is hardcoded inline. What happens when three more models need that same conversion? You copy-paste it everywhere, and one day someone changes the formula in one place but not the others. In this secret mission, you'll extract that logic into a reusable Jinja macro, then use it to build a brand new revenue-by-month mart that stakeholders actually ask for in real analytics teams.

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 running, pause them to come back later, or delete them entirely.

Resources you used:

  • The ecommerce_analytics project folder on your Desktop (contains your dbt project, Python virtual environment, and DuckDB database file).
  • The ~/.dbt/profiles.yml configuration file.
  • The dbt docs serve process (if still running in your terminal).

Keep everything running

No action needed. Choose this if you're still actively building or want to experiment with your dbt project further.

  • Your project folder, virtual environment, and DuckDB database remain on your Desktop.
  • Your ~/.dbt/profiles.yml stays configured so you can run dbt build anytime.
  • If dbt docs serve is still running, you can keep browsing the documentation at http://localhost:8080.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your project files stay intact for when you return.

  • If dbt docs serve is still running, press Ctrl+C in that terminal to stop it.
  • Deactivate your virtual environment by running:
deactivate

When you're ready to come back, navigate to your project folder, reactivate the virtual environment, and pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes your dbt project, virtual environment, DuckDB database, and profile configuration.

  • If dbt docs serve is still running, press Ctrl+C in that terminal to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Delete the entire project folder by running:

macOS

rm -rf ~/Desktop/ecommerce_analytics

Windows

rmdir /s /q %USERPROFILE%\Desktop\ecommerce_analytics
  • Delete the dbt profiles configuration by running:

macOS

rm ~/.dbt/profiles.yml

Windows

del %USERPROFILE%\.dbt\profiles.yml

If profiles.yml was the only file in your ~/.dbt/ folder, you can remove the folder too.

Nice Work!

Nice Work!

Nice work! You just built a complete analytics pipeline from scratch using dbt and DuckDB.

You've learned how to:

  • Build a layered dbt project with staging models (views) and mart models (tables) that transform raw CSV data into analytics-ready outputs in a local DuckDB database.
  • Write SQL transformations following production best practices, including refs, sources, and naming conventions that transfer directly to Snowflake, BigQuery, or Redshift.
  • Add automated data quality tests and generate browsable documentation with a visual DAG that maps your entire pipeline's dependency graph.
  • Secret Mission: Created a reusable Jinja macro for currency conversion and built a date-aggregated monthly_revenue mart.

Ready to quiz yourself?