Build a dbt Analytics Project with Claude Code

Build a local e-commerce analytics pipeline using dbt, DuckDB, and Claude Code.

Introduction

30 Second Summary

Think about the last time you ordered something online. That single purchase generated data across multiple systems: a customer record, an order entry, and a payment transaction.

In this project, you will use Claude Code to build a dbt analytics pipeline that transforms raw e-commerce data into clean, tested, analysis-ready models. Everything runs locally on DuckDB, so there's no cloud setup required.

What You'll Build

A local e-commerce analytics pipeline with staging and mart layers, built entirely through AI-assisted prompts in Claude Code and running on DuckDB.

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

  • A CLAUDE.md file that teaches Claude Code your project conventions for consistent, standards-compliant output.
  • A multi-layer dbt pipeline (seeds, staging, marts) that transforms raw CSV data into business-ready fact and dimension models.
  • An interactive documentation site with a DAG visualization of your entire pipeline lineage.
  • Secret Mission: A monthly revenue analysis model with window functions and a custom generic test.

Are there any prerequisites?

This project assumes basic familiarity with dbt concepts (models, refs, seeds) and intermediate SQL. You'll need Python (3.10+) and VS Code installed, plus a Claude Pro subscription.

Set Up dbt and Claude Code

Before you can build an analytics pipeline, you need two tools working together: dbt (the data transformation framework) and Claude Code (your AI pair programmer).

In this step, you will install dbt with a DuckDB adapter inside a Python virtual environment, install Claude Code on your machine, and confirm both tools can talk to each other.

In this step, get ready to:
  • Install dbt-duckdb in a Python virtual environment.
  • Install and verify Claude Code.
  • Verify both tools work together from the same terminal session.
Install dbt-duckdb in a virtual environment

A virtual environment keeps your project's Python packages isolated from the rest of your system. You will create one, activate it, then install dbt inside it.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) 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 dbt-claude-project
cd dbt-claude-project
  • Confirm the folder exists by running:
ls

You should see the dbt-claude-project folder listed in the output.

  • Create a Python virtual environment by running this command:

macOS

python3 -m venv .venv

Windows

python -m venv .venv

What is a virtual environment?

A virtual environment is an isolated Python installation inside your project folder. Packages you install here (like dbt) stay contained and do not conflict with other projects on your machine.

  • Activate the virtual environment by running this command:

macOS

source .venv/bin/activate

Windows

.venv\Scripts\activate

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

Don't see (.venv) in your prompt?

On Windows, you may need to allow script execution first. Run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned in PowerShell, then try activating again.

On macOS, make sure you used source before the path. Running the path alone will not activate the environment.

Help me activate my Python virtual environment.

Now you are ready to install dbt. A recent change to the dbt-core package means a clean pip install can accidentally pull in an incompatible alpha version. You will pin the version to avoid this.

  • Install dbt-core and the DuckDB adapter by running this command:
pip install "dbt-core<2.0" dbt-duckdb==1.10.1

Why pin dbt-core to less than 2.0?

Since June 2026, pip install dbt-duckdb can resolve dbt-core to 2.0.0-alpha.1, which uses a new Rust-based engine that is incompatible with Python adapters like dbt-duckdb.

Pinning "dbt-core<2.0" ensures you get the latest stable 1.x release, which works with all current adapters.

Seeing an install error?

Make sure your virtual environment is active. You should see (.venv) in your terminal prompt. If not, re-run the activation command above.

If you see a "Python version" error, confirm you have Python 3.10 or higher by running python --version (Windows) or python3 --version (macOS).

Help me fix my dbt-duckdb installation error.

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

✔️ I see dbt-core 1.x and the duckdb adapter listed

Your dbt installation is ready. You should see dbt-core with a 1.x version number and dbt-duckdb listed as an installed adapter.

ⓧ Command not found

This means your virtual environment is not active or the install did not complete. Try these fixes:

  • Make sure you see (.venv) in your terminal prompt. If not, re-run the activation command for your platform.
  • Re-run the install command: pip install "dbt-core<2.0" dbt-duckdb==1.10.1.
  • Run dbt --version again.
Install and verify Claude Code

Claude Code is Anthropic's AI coding assistant that runs directly in your terminal. It can read your entire project, run commands, and generate code across multiple files.

What account do I need for Claude Code?

Claude Code requires a Claude Pro, Max, Team, Enterprise, or Console account. The free Claude.ai plan does not include Claude Code access.

  • Install Claude Code by running this command in your terminal:

macOS

curl -fsSL https://claude.ai/install.sh | bash

Windows

Run this command in PowerShell (not inside the Python virtual environment terminal if it changed your shell):

irm https://claude.ai/install.ps1 | iex

Install command failing?

If you see a 403 error or a curl/network error, check your internet connection and try again. On Windows, make sure you are using PowerShell, not Command Prompt.

Help me install Claude Code on my machine.

  • After the installer finishes, open a new terminal window so the claude command is available.
  • Launch Claude Code by running:
claude

Claude Code opens an interactive prompt in your terminal. The first time you run it, it will ask you to authenticate through your browser.

  • Follow the authentication prompts. Your browser will open and ask you to sign in to your Anthropic account.
  • Once authenticated, return to your terminal. You should see the Claude Code prompt showing the version and current model.

✔️ I see the Claude Code prompt

Claude Code is ready. You should see a prompt with the Claude Code version and the current model name displayed.

  • Type /exit to close Claude Code for now. You will come back to it shortly.

ⓧ Command not found

The claude command is not in your system path. Try these fixes:

  • Close your current terminal and open a brand new one. The installer updates your path, but existing terminals do not pick up the change.
  • Run claude again in the new terminal.
  • If it still fails, re-run the install command for your platform from the step above.

Help me get the claude command working in my terminal.

Verify both tools work together

The final check is making sure Claude Code can run dbt commands from inside your project. This requires the virtual environment to be active in the same terminal session.

  • Open a new terminal window and navigate back to your project folder by running:
cd ~/Desktop/dbt-claude-project
  • Activate the virtual environment again:

macOS

source .venv/bin/activate

Windows

.venv\Scripts\activate
  • Confirm dbt is available by running:
dbt --version

You should see the same dbt-core 1.x version as before.

  • Now ask Claude Code to check the dbt installation by running this command:
claude -p "What version of dbt is installed? Run dbt --version to check."

What does the -p flag do?

The -p flag runs Claude Code in one-shot mode. It sends a single prompt, returns the result, and exits. This is useful for quick tasks without opening a full interactive session.

Claude Code runs the dbt --version command and reports the installed version back to you. This proves both tools are working together in your project environment.

Claude Code can't find dbt?

Make sure your virtual environment is active before running the claude -p command. Claude Code inherits your terminal's environment, so it needs (.venv) to be active to find dbt.

If Claude Code asks for permission to run the command, allow it. Claude Code needs shell access to execute dbt --version on your behalf.

Help me get Claude Code to run dbt commands.

Your development environment is ready. Next up, you will initialize the dbt project structure and write a CLAUDE.md file that teaches Claude Code your project conventions.

Initialize the Project and Write CLAUDE.md

Your development environment is set up with dbt and Claude Code ready to go. Now it's time to build the project structure they'll work with.

Every dbt project starts with directories for models, raw data to transform, and configuration that connects everything. You'll also create a CLAUDE.md file that teaches Claude Code your project conventions so it generates consistent, standards-compliant models.

In this step, get ready to:
  • Scaffold a dbt project with seed data and a clean directory structure.
  • Write a CLAUDE.md file encoding your project conventions.
  • Verify that Claude Code reads and follows your conventions.
Scaffold the dbt project and add seed data

The dbt init command scaffolds a project with the standard directory structure. You'll then clean up the defaults, add raw CSV seed data, and configure the DuckDB connection.

  • Scaffold the dbt project by running this command:
dbt init ecommerce_analytics

What happens during dbt init?

The command creates a new ecommerce_analytics directory with the standard dbt folder structure. It also asks you a series of questions to configure the project.

The first prompt asks which database adapter to use. It displays a numbered list of installed adapters. Since you installed dbt-duckdb, duckdb appears as an option. Enter its number and press Enter. Follow any additional prompts to complete the setup.

  • Move into the new project directory by running:
cd ecommerce_analytics
  • Delete the default example models and create the staging and marts directories by running these commands:
Remove-Item models/example -Recurse -Force
mkdir models/staging
mkdir models/marts

What are staging and marts?

The models/staging/ directory holds one-to-one transformations that clean and rename raw data. The models/marts/ directory holds business-logic models that combine staging models into analysis-ready tables.

This two-layer architecture keeps your project organized. Staging handles data cleaning. Marts handle business questions.

  • Confirm the directory structure by running:
ls models

You should see staging and marts listed, with no example directory remaining.

Don't see the right directories?

Make sure you ran cd ecommerce_analytics before the Remove-Item and mkdir commands. If models/example still appears, re-run the Remove-Item command.

If staging or marts is missing, re-run the mkdir command for the missing directory.

help me fix my dbt project directory structure

  • Open the project in VS Code by running:
code .

Command not recognized?

If code is not recognized, your terminal may need a restart for VS Code's PATH changes to take effect. Close and reopen your terminal, reactivate your virtual environment with .venv\Scripts\activate from the dbt-claude-project folder, then cd ecommerce_analytics and try code . again.

VS Code opens with the ecommerce_analytics project loaded. Now you'll create the raw seed data files.

  • In VS Code's Explorer sidebar, right-click the seeds folder and click New File.
  • Name the file customers.csv and paste the following 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.
  • Right-click the seeds folder again, click New File, name it orders.csv, and paste this content:
id,user_id,order_date,status
1,1,2018-01-01,returned
2,3,2018-01-02,completed
3,4,2018-01-04,completed
4,1,2018-01-05,completed
5,5,2018-01-05,completed
6,3,2018-01-06,completed
7,4,2018-01-07,completed
8,6,2018-01-08,completed
9,9,2018-01-09,returned
10,9,2018-01-10,completed
  • Create one more file in seeds called payments.csv and paste this content:
id,order_id,payment_method,amount
1,1,credit_card,1000
2,2,credit_card,2000
3,3,coupon,100
4,4,credit_card,2500
5,5,bank_transfer,1700
6,6,credit_card,500
7,7,credit_card,1800
8,8,bank_transfer,600
9,9,gift_card,2600
10,10,credit_card,300

What is this seed data?

These three CSV files represent a simplified e-commerce dataset. customers.csv has customer names. orders.csv tracks each order's date and status. payments.csv records how each order was paid.

Notice that orders.csv uses user_id instead of customer_id, and payments.csv stores amounts in cents (1000 = $10.00). Fixing these inconsistencies is exactly what the staging layer will do later.

  • Save all three files by pressing Ctrl+S in each tab.

Next, you'll update the project configuration to define how staging and mart models are materialized.

  • In VS Code's Explorer sidebar, click dbt_project.yml to open it.
  • Select all the existing content with Ctrl+A and replace it with the following:
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 this configuration do?

The models section at the bottom is the key change. It tells dbt to materialize staging models as views (lightweight, always reflecting the latest data) and mart models as tables (persisted to disk for faster queries).

The other keys (model-paths, seed-paths, etc.) tell dbt where to find each type of file in your project.

  • Save dbt_project.yml with Ctrl+S.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file.

ⓧ I'd like to double check the full code

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

Now you need to configure the database connection. The dbt profiles file lives outside your project, in your user home directory.

  • Ensure the .dbt folder exists in your home directory by running this in your terminal:
New-Item -ItemType Directory -Force "$env:USERPROFILE\.dbt"
  • Open a new file at the profiles location by running:
code "$env:USERPROFILE\.dbt\profiles.yml"
  • Paste the following content into the file:
ecommerce_analytics:
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 4
  target: dev

What does profiles.yml do?

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

The profile name ecommerce_analytics must match the profile value in your dbt_project.yml. This is how dbt knows which connection settings to use.

  • Save profiles.yml with Ctrl+S.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file.

ⓧ I'd like to double check the full code

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

Everything is configured. Time to load the seed data into DuckDB and confirm the full setup works.

  • Load the CSV seed data into DuckDB by running:
dbt seed

You should see output confirming that all three seeds loaded successfully. Look for lines showing customers, orders, and payments each marked as OK.

Seeing a connection error?

Check that the profile value in dbt_project.yml is ecommerce_analytics and matches the top-level key in your profiles.yml.

Verify that profiles.yml is saved at %USERPROFILE%\.dbt\profiles.yml and not inside your project directory. Run dbt debug to see exactly which profiles file dbt is reading and whether the connection succeeds.

help me fix my dbt seed connection error

Write the CLAUDE.md file

A CLAUDE.md file is like a README, but written for Claude Code instead of humans. Claude Code reads it automatically when you launch it from a directory.

By encoding your naming patterns, SQL style, and testing requirements in this file, Claude Code will follow your conventions every time it generates a model. This is the core technique that makes AI-assisted dbt development reliable.

Why does CLAUDE.md length matter?

Anthropic recommends keeping individual CLAUDE.md files under about 200 lines. Shorter files maintain better instruction adherence. Focus on the conventions that matter most for consistent output.

  • In VS Code's Explorer sidebar (the VS Code window open to ecommerce_analytics), right-click in the empty space below the file list and click New File.
  • Name the file CLAUDE.md and paste the following content:
# E-commerce Analytics dbt Project

## Project Overview
This is a dbt project using DuckDB for local analytics. It transforms raw e-commerce data (customers, orders, payments) into analysis-ready models.

## Architecture
- seeds/: Raw CSV data loaded via dbt seed
- models/staging/: One-to-one clean transformations from seed data, prefixed with stg_
- models/marts/: Business logic combining staging models, prefixed with fct_ (facts) or dim_ (dimensions)

## Conventions
- Use {{ ref() }} for all model references, including seeds
- SQL style: lowercase keywords, trailing commas, CTEs over subqueries
- CTE pattern: start with a "source" CTE, then a "renamed" CTE, then "final" for complex models
- Model naming: stg_<entity> for staging, fct_<entity> for facts, dim_<entity> for dimensions
- All models must have a primary key tested with unique + not_null
- Add column descriptions in YAML schema files (_staging.yml, _marts.yml)
- Amounts in seed data are in cents, convert to dollars in staging

## Commands
- dbt seed: Load CSV data into DuckDB
- dbt run: Build all models
- dbt test: Run all tests
- dbt build: Seed + run + test in dependency order
- dbt docs generate: Generate documentation
- dbt docs serve: Serve documentation site locally

What do these conventions do?

The Conventions section is the most important part. It tells Claude Code to use {{ ref() }} for all model references, follow a specific CTE pattern (source, renamed, final), and enforce testing on primary keys.

The cents-to-dollars convention ensures Claude Code converts payment amounts during staging. The naming convention (stg_, fct_, dim_) keeps models immediately identifiable by their role in the pipeline.

The Commands section gives Claude Code the ability to run dbt operations on your behalf when you ask it to build or test models.

  • Save CLAUDE.md with Ctrl+S.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file.

ⓧ I'd like to double check the full code

# E-commerce Analytics dbt Project

## Project Overview
This is a dbt project using DuckDB for local analytics. It transforms raw e-commerce data (customers, orders, payments) into analysis-ready models.

## Architecture
- seeds/: Raw CSV data loaded via dbt seed
- models/staging/: One-to-one clean transformations from seed data, prefixed with stg_
- models/marts/: Business logic combining staging models, prefixed with fct_ (facts) or dim_ (dimensions)

## Conventions
- Use {{ ref() }} for all model references, including seeds
- SQL style: lowercase keywords, trailing commas, CTEs over subqueries
- CTE pattern: start with a "source" CTE, then a "renamed" CTE, then "final" for complex models
- Model naming: stg_<entity> for staging, fct_<entity> for facts, dim_<entity> for dimensions
- All models must have a primary key tested with unique + not_null
- Add column descriptions in YAML schema files (_staging.yml, _marts.yml)
- Amounts in seed data are in cents, convert to dollars in staging

## Commands
- dbt seed: Load CSV data into DuckDB
- dbt run: Build all models
- dbt test: Run all tests
- dbt build: Seed + run + test in dependency order
- dbt docs generate: Generate documentation
- dbt docs serve: Serve documentation site locally
Verify Claude Code reads your conventions

The CLAUDE.md file is only useful if Claude Code actually reads it. Claude Code automatically discovers CLAUDE.md files by walking up the directory tree from wherever you launch it.

  • In your terminal, make sure you are inside the ecommerce_analytics directory, then launch Claude Code by running:
claude

Claude Code opens in interactive mode, showing the current model and version.

  • Ask Claude Code to summarize your conventions by typing this prompt and pressing Enter:
Summarize the project conventions from CLAUDE.md

What should I see?

Claude Code should accurately describe your naming patterns (stg_, fct_, dim_), SQL style rules (lowercase keywords, trailing commas, CTEs), and testing requirements (unique and not_null on primary keys). It should also mention the cents-to-dollars conversion rule.

If Claude Code does not mention these conventions, check that CLAUDE.md is saved in the root of the ecommerce_analytics directory and that you launched claude from that same directory.

Claude Code not reading your file?

Confirm the file is named exactly CLAUDE.md (all caps, with the .md extension). A lowercase claude.md will not be detected.

Make sure you launched Claude Code from inside the ecommerce_analytics folder, not from the parent dbt-claude-project folder. Run pwd to check your current directory.

help me get Claude Code to read my CLAUDE.md file

Your project structure is set up, seed data is loaded, and Claude Code knows your conventions. Next up, you'll put Claude Code to work generating your staging models.

Build Staging Models with Claude Code

Your CLAUDE.md conventions are locked in. Claude Code now knows exactly how you want your dbt project structured.

Staging models are the first transformation layer in a dbt pipeline. They take raw seed data and clean it up with consistent column names and proper data types, so every model downstream can rely on a predictable shape.

In this step, get ready to:
  • Prompt Claude Code to generate staging SQL models for all three seed tables.
  • Create a schema YAML file with data quality tests for every staging model.
  • Run and test the entire staging layer to verify zero failures.
Generate staging models from seed data

Each staging model follows a two-CTE pattern from your CLAUDE.md. A source CTE pulls raw data from a seed, and a renamed CTE applies clean column names.

  • In your Claude Code session, type this prompt to generate all three staging models:
Look at the CSV files in seeds/ and create staging models for each one in models/staging/. Follow the conventions in CLAUDE.md. Each staging model should use a CTE pattern: a 'source' CTE that refs the seed, and a 'renamed' CTE that applies column renames.

What will Claude Code do?

Claude Code reads your seed CSVs and your CLAUDE.md conventions, then generates three SQL files. It will ask for permission to create files in models/staging/. Allow it to proceed.

  • stg_customers.sql renames id to customer_id.
  • stg_orders.sql renames id to order_id and user_id to customer_id.
  • stg_payments.sql renames id to payment_id and converts amount from cents to dollars by dividing by 100.
  • Expand the models/staging/ folder in VS Code's Explorer sidebar.
  • You should see three new files: stg_customers.sql, stg_orders.sql, and stg_payments.sql.

Files missing or not following conventions?

  • If Claude Code created the files in the wrong directory, ask it to move them: "Move the staging models into models/staging/."
  • If the models use subqueries instead of CTEs, follow up with: "Refactor the staging models to use the source/renamed CTE pattern from CLAUDE.md."
  • If stg_payments.sql does not convert cents to dollars, ask: "Update stg_payments.sql to divide amount by 100.0 per the conventions in CLAUDE.md."

Help me fix my staging models so they follow the CTE pattern and naming conventions in CLAUDE.md.

Claude Code's output may look slightly different from the reference code below. The important things are the CTE pattern, {{ ref() }} usage, and correct column renames.

✔️ Awesome, I've got everything!

Great. Make sure all three files are saved in models/staging/ before moving on.

ⓧ I'd like to double check the full code

Compare your generated files against these references. If anything is off, ask Claude Code to fix it, or edit the files directly in VS Code.

with source as (
    select * from {{ ref('customers') }}
),

renamed as (
    select
        id as customer_id,
        first_name,
        last_name
    from source
)

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

renamed as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from source
)

select * from renamed
with source as (
    select * from {{ ref('payments') }}
),

renamed as (
    select
        id as payment_id,
        order_id,
        payment_method,
        amount / 100.0 as amount
    from source
)

select * from renamed
Generate the staging schema YAML

Your staging models exist, but nothing is checking their data quality yet. Schema YAML files define tests that dbt runs automatically every time you build.

  • Type this prompt into Claude Code to generate your schema file with tests:
Create a _staging.yml schema file in models/staging/ with unique and not_null tests on all primary keys. Add a relationships test from stg_orders.customer_id to stg_customers. Add accepted_values tests where appropriate.

What do these schema tests check?

  • unique and not_null on primary keys ensure every row has a distinct, non-empty identifier.
  • The relationships test on stg_orders.customer_id verifies every order references a valid customer in stg_customers.
  • accepted_values tests on status and payment_method catch unexpected values that could break downstream models.
  • Claude Code will ask for permission to create the file. Allow it to proceed.
  • Check VS Code's Explorer sidebar. You should see _staging.yml inside models/staging/.

Schema file missing or tests look wrong?

  • If Claude Code named the file something other than _staging.yml, ask it to rename: "Rename the schema file to _staging.yml."
  • If the relationships or accepted_values tests are missing, follow up: "Add a relationships test from stg_orders.customer_id to stg_customers and accepted_values tests on status and payment_method."

Help me fix my _staging.yml so it has the right tests for my staging models.

✔️ Awesome, I've got everything!

Great. Make sure the file is saved before you run the build in the next substep.

ⓧ I'd like to double check the full code

Compare your generated YAML against this reference. If anything differs, ask Claude Code to fix it, or edit the file directly in VS Code.

version: 2

models:
  - name: stg_customers
    description: Cleaned customer data from raw seed.
    columns:
      - name: customer_id
        description: Primary key for customers.
        tests:
          - unique
          - not_null

  - name: stg_orders
    description: Cleaned order data with renamed columns.
    columns:
      - name: order_id
        description: Primary key for orders.
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to stg_customers.
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_date
        description: Date the order was placed.
      - name: status
        description: Current status of the order.
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

  - name: stg_payments
    description: Cleaned payment data with amount converted from cents to dollars.
    columns:
      - name: payment_id
        description: Primary key for payments.
        tests:
          - unique
          - not_null
      - name: order_id
        description: Foreign key to stg_orders.
        tests:
          - not_null
      - name: payment_method
        description: Method of payment used.
        tests:
          - accepted_values:
              values: ['credit_card', 'coupon', 'bank_transfer', 'gift_card']
      - name: amount
        description: Payment amount in dollars.
Run and test the staging layer

Claude Code wrote the models and tests. Now you need to verify that everything actually compiles and passes against your DuckDB database.

  • Build and test all staging models by running this command in your terminal (with your virtual environment active):
dbt build --select staging

What does this command do?

dbt build --select staging compiles, runs, and tests only the models inside your models/staging/ directory. It materializes each staging model as a view in DuckDB, then runs all schema tests from _staging.yml.

You should see output showing 3 models completed and all tests passing with 0 failures.

Seeing test failures or compilation errors?

  • If you see a compilation error mentioning a missing model, check that your SQL files use {{ ref('customers') }} (the seed name), not {{ ref('stg_customers') }} (that would create a circular reference).
  • If an accepted_values test fails, the YAML might list values that do not match the actual data in your CSVs. Compare the values in _staging.yml against seeds/orders.csv and seeds/payments.csv.
  • If your virtual environment is not active, you may see a "command not found" error. Reactivate it with .venv\Scripts\activate and try again.

Help me debug my dbt build failures for the staging layer.

Your staging layer is solid. All three models compile, materialize as views, and pass every data quality test. Next up, you'll build mart models that combine these clean staging tables into business-ready analytics.

Build Mart Models with Claude Code

Your staging layer is built and tested. Each staging model takes raw seed data and outputs a clean, consistently named view in DuckDB.

Staging models are just the foundation. The real value comes from the marts layer, where you combine staging data into analysis-ready models that contain actual business logic. In this step, you'll prompt Claude Code to build a fact table and a dimension table.

In this step, get ready to:
  • Generate fact and dimension mart models with Claude Code.
  • Add schema tests and column descriptions for the mart layer.
  • Build and test the full pipeline end to end.
Generate fact and dimension models

Mart models sit on top of staging and add business logic. A fact table records measurable business events (like orders with payment totals). A dimension table describes business entities (like customers with lifetime metrics).

You'll create two models: fct_orders combines order data with payment totals, and dim_customers builds a customer profile with lifetime order metrics.

  • In your Claude Code session, enter this prompt to generate the orders fact model:
Create a fct_orders model in models/marts/ that joins stg_orders with aggregated payment amounts from stg_payments. Include order_id, customer_id, order_date, status, and total_amount.

What does fct_orders do?

A single order can have multiple payments (credit card plus a coupon, for example). The model aggregates all payments per order first, then joins those totals back to the order details.

It uses coalesce to default the total to 0 for any orders with no matching payments. This prevents null values from breaking downstream calculations.

  • Claude Code will create the file and show you the generated SQL. Review it to make sure it references stg_orders and stg_payments using {{ ref() }}.
  • Now enter this prompt to generate the customers dimension model:
Create a dim_customers model in models/marts/ that joins stg_customers with aggregated order metrics from fct_orders: first_order_date, last_order_date, total_orders, and lifetime_value.

What does dim_customers do?

This model builds a customer profile by joining stg_customers with aggregated order metrics. It calculates each customer's first order date, most recent order date, total number of orders, and lifetime spend.

Notice it references fct_orders, not stg_orders. This creates a dependency chain: seeds, then staging, then fct_orders, then dim_customers. dbt handles the build order automatically based on these {{ ref() }} calls.

  • Review Claude Code's output. Confirm that dim_customers references fct_orders (not stg_orders) and uses coalesce for customers who haven't placed any orders.
  • Verify that both models/marts/fct_orders.sql and models/marts/dim_customers.sql now exist in your project.

Claude Code generated different SQL?

Claude Code's output may differ slightly from what's shown in the double-check tabs below. The key things to verify are:

  • Both files use {{ ref() }} for all model references (no hardcoded table names).
  • The CTE pattern from your CLAUDE.md is followed (named CTEs, not subqueries).
  • The output columns match what you asked for in the prompt.

If the SQL is significantly different, you can ask Claude Code to revise it. Try a follow-up prompt like: "Update fct_orders to match the CTE pattern in CLAUDE.md and use coalesce for the total_amount."

Help me fix my Claude Code-generated dbt mart models

Compare Claude Code's generated SQL against the expected models below.

✔️ Awesome, I've got everything!

Your mart models are ready. Make sure both files are saved in models/marts/.

ⓧ I'd like to double check the full code

Here is what fct_orders.sql should look like:

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

payments as (
    select
        order_id,
        sum(amount) as total_amount
    from {{ ref('stg_payments') }}
    group by order_id
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        coalesce(payments.total_amount, 0) as total_amount
    from orders
    left join payments on orders.order_id = payments.order_id
)

select * from final

Here is what dim_customers.sql should look like:

with customers as (
    select * from {{ ref('stg_customers') }}
),

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

customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date,
        count(order_id) as total_orders,
        sum(total_amount) as lifetime_value
    from orders
    group by customer_id
),

final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.last_order_date,
        coalesce(customer_orders.total_orders, 0) as total_orders,
        coalesce(customer_orders.lifetime_value, 0) as lifetime_value
    from customers
    left join customer_orders on customers.customer_id = customer_orders.customer_id
)

select * from final
Generate the marts schema YAML with tests

Your staging layer already has a _staging.yml file with tests and descriptions. The mart models need the same treatment. Schema tests catch data quality issues before bad data reaches your analysts.

  • In Claude Code, enter this prompt to generate the schema file:
Create a _marts.yml schema file with unique and not_null tests on primary keys, and add descriptions for every column in both mart models.

What does the schema YAML do?

  • unique and not_null tests on primary keys guarantee that every row has an ID and that no two rows share the same one.
  • Column descriptions document what each field means, which feeds into the documentation site you'll generate in the next step.
  • Verify that models/marts/_marts.yml now exists and contains entries for both fct_orders and dim_customers.
  • Check that order_id and customer_id each have unique and not_null tests listed under them.

Missing descriptions or tests?

If Claude Code left out descriptions for some columns, send a follow-up prompt: "Add missing descriptions to _marts.yml for every column in fct_orders and dim_customers."

If the primary key tests are missing, check that your CLAUDE.md includes the convention about testing primary keys with unique and not_null. Claude Code relies on those instructions to decide what tests to generate.

Help me fix my Claude Code-generated _marts.yml schema file

Compare your schema file against the expected YAML below.

✔️ Awesome, I've got everything!

Your mart schema is ready. Make sure _marts.yml is saved in models/marts/.

ⓧ I'd like to double check the full code

Here is what _marts.yml should look like:

version: 2

models:
  - name: fct_orders
    description: Order fact table joining orders with aggregated payment totals.
    columns:
      - name: order_id
        description: Primary key for orders.
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to dim_customers.
      - name: order_date
        description: Date the order was placed.
      - name: status
        description: Current status of the order.
      - name: total_amount
        description: Total payment amount for the order in dollars.

  - name: dim_customers
    description: Customer dimension table with lifetime order metrics.
    columns:
      - name: customer_id
        description: Primary key for customers.
        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 first order.
      - name: last_order_date
        description: Date of the customer most recent order.
      - name: total_orders
        description: Total number of orders placed by the customer.
      - name: lifetime_value
        description: Total amount spent by the customer in dollars.
Build and test the full pipeline

All five models are in place: three staging views and two mart tables. Time to run the entire project end to end and confirm everything compiles, loads, and passes tests.

  • Exit your Claude Code session by pressing Ctrl+C or typing /exit to return to your regular terminal.
  • Run the full pipeline by running this command:
dbt build

What does dbt build do?

The dbt build command runs seed, run, and test in dependency order. It loads your CSVs, materializes all five models (staging views first, then mart tables), and executes every test defined in your schema YAML files.

Because dim_customers depends on fct_orders, dbt automatically builds fct_orders first. You never have to manage the build order yourself.

  • Check the terminal output. You should see all 5 models (3 staging + 2 marts) build successfully and all tests pass with zero failures.
  • Confirm that a dev.duckdb file exists in your project directory by running:
ls dev.duckdb

You should see dev.duckdb listed. This is the DuckDB database file containing all your seeds, staging views, and mart tables.

Seeing test failures or build errors?

  • If a test fails on unique or not_null, check that the SQL model's primary key column does not produce duplicates or null values. Open the SQL file and verify the column alias matches what is defined in the YAML.
  • If you see a compilation error about a missing model, check that the {{ ref() }} calls use the correct model names: stg_orders, stg_payments, stg_customers, and fct_orders.
  • If the column names in the YAML don't match the SQL output, update the YAML to use the exact column aliases from your SQL models.

Help me debug my dbt build failures

Your full analytics pipeline is running. Five models, all tested, all built in dependency order. Next up, you'll generate an interactive documentation site and explore the DAG of your entire pipeline.

Generate Documentation and Explore the DAG

Your five models are built and every test passes. The pipeline works, but right now only you know what each model does and how they connect.

In this step, you will use Claude Code to verify your documentation coverage. Then you will generate an interactive docs site with a DAG visualization that maps the full lineage of your pipeline.

In this step, get ready to:
  • Use Claude Code to verify and enrich your schema descriptions.
  • Generate and explore an interactive documentation site with a DAG visualization.
Use Claude Code to enrich documentation

Every model and column listed in your schema YAML files should have a clear description. These descriptions power the documentation site you will generate next.

  • Launch Claude Code from your project directory by running:
claude
  • Ask Claude Code to review your schema files and fill in any gaps by entering this prompt:
Review _staging.yml and _marts.yml. Make sure every model and every column has a description field. Add any that are missing.

What is Claude Code doing?

Claude Code reads both YAML schema files and checks every model and column entry for a description field.

If any description is missing, Claude Code adds one following the conventions in your CLAUDE.md file. This is the payoff of writing clear conventions earlier. Claude Code produces consistent, standards-compliant descriptions without you dictating each one.

  • Review Claude Code's response. It should confirm that all listed models and columns have descriptions, or show you the descriptions it added.
  • Type /exit to leave the Claude Code session and return to your terminal.

Claude Code didn't find your files?

Make sure you launched claude from inside the ecommerce_analytics directory. Claude Code needs to see your project files to read them.

Check that your CLAUDE.md file exists in the project root. Claude Code reads this file for your project conventions.

Help me get Claude Code to review my dbt YAML schema files.

Generate and serve the documentation site

dbt has a built-in documentation generator. It compiles your YAML descriptions, SQL models, and test definitions into an interactive website with a visual DAG.

  • Generate the documentation files by running:
dbt docs generate
  • You should see a confirmation message in your terminal indicating the documentation was generated successfully.

What did this create?

This command compiled your project into two files inside the target/ directory.

  • manifest.json contains the full project DAG and metadata about every model, test, and source.
  • catalog.json contains column-level details pulled directly from DuckDB.
  • Launch the documentation site locally by running:
dbt docs serve

A browser window opens automatically showing your dbt documentation site on port 8080. Keep your terminal running. The server stays active as long as the terminal is open.

  • Click on any model name (like fct_orders or dim_customers) to see its description, column definitions, and test coverage.
  • Open the DAG view using the graph icon in the bottom-right area of the page. You should see the full lineage: seeds (customers, orders, payments) flowing into staging models, which flow into mart models.

Why does the DAG matter?

The DAG (Directed Acyclic Graph) shows you how every model in your project depends on every other model. If you change stg_orders, you can instantly see that both fct_orders and dim_customers are downstream and may be affected.

On a real analytics team, this lineage map is how you confidently refactor models without breaking dashboards.

Browser didn't open?

Try opening http://localhost:8080 manually in your browser.

If port 8080 is already in use by another application, the command may fail. Close the other application using that port and try running dbt docs serve again.

The dbt docs site isn't loading or I see an error when running dbt docs serve. How do I fix this?

Secret mission

Add Revenue Analysis with Window Functions

Your pipeline covers orders and customers, but what about tracking revenue over time? In this secret mission, you'll use Claude Code to build a monthly revenue analysis model with window functions and create a custom data quality test to guard your numbers.

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 dbt-claude-project folder on your Desktop (containing the ecommerce_analytics dbt project, virtual environment, and dev.duckdb database).
  • The profiles.yml file in your .dbt directory.
  • The dbt documentation server (if still running from dbt docs serve).

Keep everything running

No action needed. Choose this if you are still actively building on this project or plan to extend it soon.

  • Your dbt-claude-project folder stays on your Desktop with all models, seeds, and the DuckDB database intact.
  • Your profiles.yml stays configured so you can run dbt build at any time.
  • Claude Code remains installed on your machine and available for future projects.

Pause - I'll come back to this later

Stop any running processes to free up system resources. Your project files stay on disk so you can pick up where you left off.

  • If the dbt documentation server is still running, switch to the terminal where you ran dbt docs serve and press Ctrl+C to stop it.
  • If your virtual environment is still active, deactivate it by running:
deactivate
  • Your project folder and all files remain on your Desktop. To resume, navigate back to dbt-claude-project, activate the virtual environment, and run dbt build.

Delete - I don't want to use this again

Remove all project files and configuration to start fresh. This cannot be undone.

  • If the dbt documentation server is still running, switch to the terminal where you ran dbt docs serve and press Ctrl+C to stop it.
  • If your virtual environment is still active, deactivate it by running:
deactivate
  • Delete the entire project folder from your Desktop. On macOS or Linux, run:
rm -rf ~/Desktop/dbt-claude-project
  • On Windows, run this in PowerShell:
Remove-Item -Recurse -Force ~\Desktop\dbt-claude-project
  • Delete the dbt profiles directory. On macOS or Linux, run:
rm -rf ~/.dbt
  • On Windows, run this in PowerShell:
Remove-Item -Recurse -Force ~\.dbt

Keep the .dbt folder if you use dbt elsewhere

If you use dbt for other projects, only delete the ecommerce_analytics profile entry from profiles.yml instead of removing the entire .dbt folder.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete e-commerce analytics pipeline using dbt and DuckDB, with Claude Code as your AI pair programmer handling the SQL generation, testing, and documentation.

You've learned how to:

  • Set up a local dbt + DuckDB analytics environment and crafted a CLAUDE.md file that taught Claude Code your project conventions, producing consistent and standards-compliant models.
  • Built a 3-layer analytics pipeline (seeds, staging, marts) entirely through Claude Code prompts, using CTE patterns and ref() references.
  • Added schema tests (unique, not_null, relationships, accepted_values) and column-level documentation, then generated an interactive DAG visualization of your full pipeline.
  • Secret Mission: Created a monthly revenue analysis model with window functions and a custom generic test.

Ready to quiz yourself?