Build an E-Commerce ELT Pipeline

Build a tested local e-commerce ELT pipeline with dbt and DuckDB.

Introduction

30 Second Summary

Sales reports often look trustworthy because the totals add up. A duplicated order or missing order number can quietly turn a clean-looking report into a bad business decision.

In this project, you will build a local e-commerce ELT pipeline with dbt and DuckDB. You will watch tests expose the flawed orders that inflated revenue before a repaired staging layer produces trusted metrics.

What You'll Build

You'll demo a local warehouse moving from visibly inflated revenue to tested daily totals, customer lifetime value, plus a browser-viewable lineage graph.

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

A local revenue pipeline you can run to load version-controlled customer and order fixtures into a persistent analytics database.
A data-quality test suite that catches duplicate order IDs, missing order IDs, invalid statuses, plus broken customer relationships before they reach business reports.
A trusted analytics layer showing corrected daily revenue, customer lifetime value, plus browser-viewable model lineage.
Secret Mission: Add a tested customer segmentation mart that labels every customer as high-value or standard.

Are there any prerequisites?

The project assumes basic SQL familiarity on a Mac with Visual Studio Code, Python 3, and Git. Step 1 covers dbt setup without requiring a cloud account.

Before We Start

This first checkpoint is where you commit to building a local e-commerce ELT pipeline that turns raw CSV fixtures into tested DuckDB analytics models. You will also pin down why duplicate or missing order_id values can inflate revenue and invalidate business metrics.

Set Up dbt and DuckDB

Your e-commerce ELT pipeline needs a repeatable environment before it can transform raw records. A local dbt project keeps each model with its tests.

DuckDB provides a warehouse-like database on your Mac. This local setup keeps your attention on data engineering without adding cloud accounts or credentials.

You'll prepare the project in Visual Studio Code. You'll use its integrated Terminal to install dbt v2 2.0.9.

In this step, get ready to:

Create the commerce_elt project folder in Visual Studio Code.
Install dbt v2 2.0.9 for the local pipeline.
Configure the project for a persistent local DuckDB connection.

Create your workspace and install dbt

The project folder gives your configuration files a fixed location. Opening that folder as the Visual Studio Code workspace also makes its integrated terminal start in the correct place.

Why use a local workflow?

A local dbt workflow lets you practise dependency management without setting up a cloud warehouse. DuckDB supplies the database engine without authentication.

This approach reserves your project time for models, tests, and business metrics. The finished pipeline also costs nothing to run locally.

Press Cmd+Space to open macOS search.
Type Terminal into the search field.
Press Enter to open Terminal.
Move to your Desktop by running this command:

cd ~/Desktop

Your terminal now uses the Desktop as its current location. The project folder you create next stays easy to find.

Create the commerce_elt folder by running this command:

mkdir commerce_elt

The command creates an empty commerce_elt folder on your Desktop.

Confirm the folder exists by running this command:

ls

You should see commerce_elt in the terminal output.

Press Cmd+Space to return to macOS search.
Type Visual Studio Code into the search field.
Press Enter to open Visual Studio Code.
Click File in the menu bar.
Click Open Folder.
Select the commerce_elt folder on your Desktop.
Click Open.

You should see commerce_elt at the top of the Explorer sidebar. Your workspace is ready for the project files.

Click Terminal in the Visual Studio Code menu bar.
Click New Terminal.

The integrated terminal opens inside commerce_elt. Commands now apply to this project folder.

The version check determines whether dbt is ready, outdated, or missing. Each result has a matching path below.

Check the installed dbt version by running this command:

dbt --version

Compare your terminal result with the tab labels below. A missing command is an expected result when dbt has not been installed.

✔️ I see version 2.0.9

Your dbt installation matches the project pin. You can continue to the project configuration.

ⓧ I see an older version

This project pins dbt v2 to 2.0.9. Updating the existing installation gives you the expected command behaviour.

Update dbt to the pinned version by running this command:

dbt system update --version 2.0.9

The updater replaces the active dbt v2 installation with version 2.0.9.

Verify the updated version by running this command:

dbt --version

You should now see dbt v2 2.0.9 in the output.

ⓧ Command not found

The missing command means dbt is not available in your current shell. The official installer adds the local dbt framework to your Mac.

Install dbt by running this command:

curl -fsSL https://public.cdn.getdbt.com/fs/install/install.sh | sh

The installation script downloads the self-hosted dbt framework. Keep the terminal open when the command finishes.

Pin the installation to version 2.0.9 by running this command:

dbt system update --version 2.0.9

The update command selects dbt v2 version 2.0.9 for this project.

Verify the installed version by running this command:

dbt --version

You should see dbt v2 2.0.9 in the output.

Still missing the dbt command?

Close the current integrated terminal after the installer finishes.
Click Terminal in the Visual Studio Code menu bar.
Click New Terminal to reload your shell environment.

If dbt still cannot be found, help me troubleshoot the local dbt installation.

Configure the dbt project

A dbt project uses YAML files to describe its folders, model settings, and database connection. You need one project file plus one local profile.

Create the model directories plus the seed directory by running these commands:

mkdir models
mkdir models/staging
mkdir models/marts
mkdir seeds

What do these folders hold?

The models/staging directory holds models that clean raw records.
The models/marts directory holds analytics-ready business models.
The seeds directory holds the small version-controlled CSV fixtures used in this tutorial.

Confirm the new directories by running these commands:

ls
ls models

The first listing should include models plus seeds. The second listing should include marts plus staging.

Use the new-file icon in the Explorer sidebar to create dbt_project.yml inside commerce_elt.
Paste this project configuration into dbt_project.yml.

name: commerce_elt
version: "1.0.0"
config-version: 2
profile: commerce_elt

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

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

seeds:
  commerce_elt:
    raw_customers:
      +column_types:
        customer_id: varchar
        customer_name: varchar
    raw_orders:
      +column_types:
        order_id: varchar
        customer_id: varchar
        order_date: date
        status: varchar
        amount: decimal(10,2)

What does this configuration do?

The project name matches the commerce_elt profile that dbt loads.
Staging models become views. Mart models become persistent tables.
The seed settings preserve identifiers as text. The order amount uses the exact decimal(10,2) type needed for monetary values.

Save dbt_project.yml.
Check the saved file for the top-level name, models, and seeds sections.

Seeing a YAML warning?

YAML uses spaces to show nesting. Check that each nested line matches the indentation shown in the code block.

For targeted help, help me fix my dbt project YAML.

The profile tells dbt where to create the local database. Its schema setting places project relations in main.

Use the new-file icon in the Explorer sidebar to create profiles.yml inside commerce_elt.
Paste this connection profile into profiles.yml.

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

How does the profile connect?

The dev target selects the local development output.
The type: duckdb setting uses the DuckDB adapter built into dbt v2.
The path: './commerce.duckdb' setting creates a persistent database file in commerce_elt.
The profile uses the main schema with 4 threads.

Save profiles.yml.
Check that profiles.yml appears beside dbt_project.yml in the Explorer sidebar.

Profile indentation look wrong?

Make sure dev is nested under outputs. Keep all four connection settings aligned beneath dev.

If the nesting is unclear, help me check my DuckDB profile YAML.

✔️ Everything matches

Great. Save both configuration files before validating the connection.

ⓧ I'd like to double check the full code

Compare your two configuration files with these complete versions.

name: commerce_elt
version: "1.0.0"
config-version: 2
profile: commerce_elt

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

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

seeds:
  commerce_elt:
    raw_customers:
      +column_types:
        customer_id: varchar
        customer_name: varchar
    raw_orders:
      +column_types:
        order_id: varchar
        customer_id: varchar
        order_date: date
        status: varchar
        amount: decimal(10,2)

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

Validate the local connection

The configuration files describe the intended project. A debug check now proves that dbt can read them and connect to the local database.

Before you run the checks, do you expect dbt to recognise both the project and its DuckDB connection?

Verify the pinned version plus the local connection by running these commands:

dbt --version
dbt debug

What do these checks prove?

The version command reports the active dbt v2 installation.
The debug command checks the project configuration plus the database connection.
A successful connection confirms that dbt can use the local DuckDB profile.

The version output should report dbt v2 2.0.9.

The debug output should confirm a valid project plus a successful local connection. You should also see commerce.duckdb in the Explorer sidebar.

That's the setup locked in. Your local warehouse is ready to accept its first e-commerce records.

Did dbt debug fail?

Confirm that the integrated terminal is running inside commerce_elt.
Confirm that dbt_project.yml uses profile: commerce_elt.
Confirm that profiles.yml starts with commerce_elt:.

For help with the output, help me diagnose my dbt debug failure.

Your dbt project now has a working local warehouse connection. Next up, you'll load flawed order data and watch the first revenue model run.

Build the First Revenue Pipeline

Your dbt project is open in Visual Studio Code. The validated profile reaches a local DuckDB database.

Small CSV seed files make this pipeline repeatable. A visible result lets you test whether a successful pipeline also produces trustworthy metrics.

In this step, get ready to:

Load the e-commerce fixtures into DuckDB.
Build the staging-to-mart revenue flow.
Preview the revenue table to inspect the effect of flawed source rows.

Add the raw fixture data

dbt seeds fit small version-controlled fixtures. This project uses them to create a repeatable customer dataset.

Right-click the seeds folder in Visual Studio Code's file sidebar.
Select the option for creating a new file.
Name the file raw_customers.csv.
Add the customer fixture rows by pasting this file content:

customer_id,customer_name
C001,Ada Lovelace
C002,Grace Hopper
C003,Katherine Johnson

What does this data do?

The customer_id column provides a stable key for each customer.
The customer_name column provides a readable name for later customer metrics.
The three rows create a small fixture that is easy to inspect.

Save raw_customers.csv with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm raw_customers.csv appears inside seeds/ in the file sidebar.

Customer file missing?

Check that the filename is exactly raw_customers.csv.
Move the file into seeds/ if it appears elsewhere in the sidebar.

Help me find why raw_customers.csv is missing from my dbt seeds folder.

The order fixture contains the records that feed the revenue calculation. Its repeated order plus blank key create the data-quality challenge for this pipeline.

Right-click the seeds folder in Visual Studio Code's file sidebar.
Select the option for creating a new file.
Name the file raw_orders.csv.
Add the order fixture rows by pasting this file content:

order_id,customer_id,order_date,status,amount
O1001,C001,2026-10-01,completed,120.00
O1002,C002,2026-10-01,completed,80.00
O1002,C002,2026-10-01,completed,80.00
,C003,2026-10-02,completed,50.00
O1003,C001,2026-10-02,completed,70.00
O1004,C003,2026-10-03,completed,200.00

What does this data do?

The repeated O1002 row gives the pipeline two identical order records.
The blank first value leaves one row without an order_id.
The amount column supplies the values used by the revenue calculation.

Save raw_orders.csv with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm raw_orders.csv appears inside seeds/ in the file sidebar.

Order file missing?

Check that the filename is exactly raw_orders.csv.
Move the file into seeds/ if it appears in another folder.

Help me fix the location or filename of raw_orders.csv.

Load both seed files into DuckDB by running this command in the integrated Terminal:

dbt seed

Check the integrated Terminal for successful loading of raw_customers plus raw_orders.

You'll see both seed names complete successfully.

Good progress. Both fixture tables now exist inside commerce.duckdb.

Seed load failed?

Check that both filenames end with .csv.
Check that every row has the same number of comma-separated fields as its header.

Help me troubleshoot my dbt seed failure.

Build the staging and revenue models

A staging model gives raw fields a consistent shape before a mart aggregates them. Here, SQL nullif(order_id, '') turns the blank key into NULL.

The minimal staging model preserves every source row. This gives the revenue mart an intentionally unprotected input.

Right-click models/staging in Visual Studio Code's file sidebar.
Select the option for creating a new file.
Name the file stg_orders.sql.
Create the minimal staging model by pasting this code:

select
    nullif(order_id, '') as order_id,
    customer_id,
    order_date,
    status,
    amount
from {{ ref('raw_orders') }}

What does this code do?

The ref('raw_orders') call creates a dependency on the seeded order table.
The nullif(order_id, '') expression normalizes the blank key to SQL NULL.
The remaining columns pass through without changing the source rows.

Save stg_orders.sql with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm stg_orders.sql appears inside models/staging/.

Staging model looks different?

Compare the nullif(order_id, '') expression with the snippet above.
Confirm the source reference is exactly raw_orders.

Help me compare my stg_orders.sql file with the expected minimal model.

The daily_revenue mart groups staging rows by date before calculating two metrics.

Right-click models/marts in Visual Studio Code's file sidebar.
Select the option for creating a new file.
Name the file daily_revenue.sql.
Create the daily revenue mart by pasting this code:

select
    order_date,
    count(*) as order_count,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by order_date
order by order_date

What does this code do?

The ref('stg_orders') call makes the mart depend on the staging model.
The count(*) aggregate counts every staging row for each date.
The sum(amount) aggregate adds every staging amount for each date.

Save daily_revenue.sql with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm daily_revenue.sql appears inside models/marts/.

Revenue model looks different?

Confirm the model reference uses stg_orders.
Confirm order_date appears in the grouping clause.

Help me compare my daily_revenue.sql file with the expected revenue model.

✔️ Awesome, I've got everything!

All project files for this stage are saved in the expected locations.

ⓧ I'd like to double check the full code

The complete project files at this point are:

name: commerce_elt
version: "1.0.0"
config-version: 2
profile: commerce_elt

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

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

seeds:
  commerce_elt:
    raw_customers:
      +column_types:
        customer_id: varchar
        customer_name: varchar
    raw_orders:
      +column_types:
        order_id: varchar
        customer_id: varchar
        order_date: date
        status: varchar
        amount: decimal(10,2)

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

customer_id,customer_name
C001,Ada Lovelace
C002,Grace Hopper
C003,Katherine Johnson

order_id,customer_id,order_date,status,amount
O1001,C001,2026-10-01,completed,120.00
O1002,C002,2026-10-01,completed,80.00
O1002,C002,2026-10-01,completed,80.00
,C003,2026-10-02,completed,50.00
O1003,C001,2026-10-02,completed,70.00
O1004,C003,2026-10-03,completed,200.00

select
    nullif(order_id, '') as order_id,
    customer_id,
    order_date,
    status,
    amount
from {{ ref('raw_orders') }}

select
    order_date,
    count(*) as order_count,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by order_date
order by order_date

Build both models in dependency order by running this command:

dbt run

Check the integrated Terminal for successful results from stg_orders plus daily_revenue.

You'll see stg_orders followed by daily_revenue in the successful model results. That is your first working dependency chain.

Model build failed?

Confirm both SQL files are saved in their expected directories.
Compare each model reference with the full-code tab.

Help me troubleshoot my dbt model build.

Preview the inflated revenue totals

Execution success proves only that the SQL ran. The preview reveals what the model actually counted.

Before you run the preview, which rows do you think will shape each date's total?

Preview the daily_revenue model by running this command:

dbt show --select "daily_revenue"

You'll see revenue of 280.00 for 2026-10-01 plus 120.00 for 2026-10-02. Both totals are intentionally wrong.

Why are the totals inflated?

The repeated O1002 row is counted twice on 2026-10-01.

The row with a NULL order key still contributes its 50.00 amount on 2026-10-02. A successful model build has silently produced bad metrics.

Don't see the inflated totals?

Confirm the earlier seed load completed successfully.
Confirm the model build completed successfully.
Confirm the preview selects daily_revenue.

Help me find why my revenue preview does not show the expected inflated totals.

Your first revenue pipeline runs despite the flawed source rows. Next up, you'll add data tests that turn silent data damage into a visible pipeline failure.

Expose Data Quality Failures

Your dbt pipeline already builds a revenue table. The inflated totals are stored in your local DuckDB database without any warning.

A successful model run only proves that the SQL executed. Data tests turn your assumptions about each order into checks that can fail visibly.

In this step, get ready to:

Define the quality rules for staged order keys.
Run the staging tests to expose the broken assumptions.
Query the failing records to identify the exact keys responsible.

Define the staging contract

A YAML model file records the rules that your staging data must satisfy. The current pass-through model remains unchanged while these rules test its output.

Use the new-file control in the Explorer sidebar to create staging.yml inside models/staging.

You'll see staging.yml beneath stg_orders.sql in the staging folder.

Paste the staging contract into models/staging/staging.yml using the code below:

version: 2

models:
  - name: stg_orders
    description: Clean orders with missing keys removed and duplicate order IDs reduced to one row.
    columns:
      - name: order_id
        description: Unique identifier for an order.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        description: Customer who placed the order.
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('raw_customers')
                field: customer_id
      - name: status
        description: Current order status.
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed']

What do these tests protect?

The data_tests entries turn column assumptions into automated checks.
The unique test rejects repeated order IDs.
The not_null test rejects missing keys.
The relationships test verifies that every staged customer_id exists in raw_customers.
The accepted_values test limits status to completed.

Save models/staging/staging.yml.
Confirm the Explorer sidebar lists staging.yml beneath models/staging.

Good progress. Your staging rules are now versioned beside the model they protect.

Is the staging contract missing?

Check that staging.yml sits inside models/staging.
Compare the indentation in your file with the complete reference below.

Help me check my staging test configuration.

✔️ Awesome, I've got everything!

Your saved staging.yml file now defines the complete staging contract.

ⓧ I'd like to double check the full code

Compare your saved file with this complete reference:

version: 2

models:
  - name: stg_orders
    description: Clean orders with missing keys removed and duplicate order IDs reduced to one row.
    columns:
      - name: order_id
        description: Unique identifier for an order.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        description: Customer who placed the order.
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('raw_customers')
                field: customer_id
      - name: status
        description: Current order status.
        data_tests:
          - accepted_values:
              arguments:
                values: ['completed']

How to use this reference

This reference shows the complete staging contract for this step. Matching its indentation ensures dbt associates each test with the intended column.

Run the expected test failure

Each data test searches for records that violate its rule. A test passes only when its query returns zero failing rows.

Before you run the checks, do you expect the current pass-through staging model to satisfy every rule?

Run the staging tests from the integrated Terminal using this command:

dbt test --select "stg_orders"

This failure is expected

You'll see the unique test fail on order_id. You'll also see the not_null test fail on order_id.

The nonzero result is intentional. It proves that the successful revenue model was hiding records that violate its key assumptions.

That is the guardrail working. Your project now reports the corruption that previously passed through silently.

Seeing a different test result?

Confirm that stg_orders.sql still contains the minimal pass-through model from the previous step.
Check that both unique and not_null appear beneath order_id in staging.yml.

Help me understand my dbt test result.

Find the violating keys

The failed test names identify which assumptions broke. A diagnostic SQL query reveals the exact key values behind those failures.

Before you run the diagnostic query, do you expect it to return one problematic group or more than one?

Inspect the violating keys from the integrated Terminal using this command:

dbt show --inline "select order_id, count(*) as row_count from {{ ref('stg_orders') }} group by order_id having count(*) > 1 or order_id is null"

How does the diagnostic query work?

The ref('stg_orders') call queries the staged order relation through dbt's dependency graph.
The group by order_id clause collects matching keys into groups.
The having count(*) > 1 or order_id is null clause keeps duplicate groups plus the missing-key group.

You'll see O1002 with a row_count of 2. You'll also see a NULL order ID with a row_count of 1.

Are the violating keys missing?

Check that seeds/raw_orders.csv still contains two rows with O1002.
Confirm that one row in seeds/raw_orders.csv has a blank order_id.
Verify that stg_orders.sql still applies nullif(order_id, '').

Help me diagnose my inline query output.

You have turned silent revenue corruption into a reproducible test failure with identifiable records. Next up, you'll repair the staging layer so every check passes with corrected revenue totals.

Repair the Staging Layer

Your dbt tests now pinpoint the records that inflated daily revenue. They found duplicate O1002 rows plus a NULL order_id.

The staging layer must protect each downstream DuckDB model from those rows. It will remove missing keys before retaining one row for each order ID.

In this step, get ready to:

Normalize order keys while excluding missing IDs.
Deduplicate repeated order IDs with row_number().
Validate corrected revenue after a full project build.

Build the normalization and ranking stages

A common table expression gives each cleanup stage a name. The first CTE normalizes blank keys.

A window function assigns each repeated key a position. The second CTE uses that position to mark one record as rank one.

Use Visual Studio Code's file sidebar to return to models/staging/stg_orders.sql.
Select all the existing SQL in models/staging/stg_orders.sql.
Replace the selected SQL with the first part of the repaired model by pasting this code:

with normalized_orders as (
    select
        nullif(order_id, '') as order_id,
        customer_id,
        order_date,
        status,
        amount
    from {{ ref('raw_orders') }}
),

ranked_orders as (
    select
        order_id,
        customer_id,
        order_date,
        status,
        amount,
        row_number() over (
            partition by order_id
            order by order_date, customer_id, amount
        ) as duplicate_rank
    from normalized_orders
    where order_id is not null
)

What do these CTEs do?

The normalized_orders CTE converts a blank order key into SQL NULL.
The where order_id is not null filter removes records that cannot identify an order.
The row_number() function assigns a position to each record inside its order group.
The partition by order_id clause restarts that ranking for each order ID.

Keep one row per order

The duplicate_rank value is a helper for choosing one record. The final query hides that helper from downstream models.

Place your cursor on a new line after the closing parenthesis for ranked_orders.
Append the final query by pasting this code:

select
    order_id,
    customer_id,
    order_date,
    status,
    amount
from ranked_orders
where duplicate_rank = 1

How does the final filter help?

The final query publishes only the original business columns.
The where duplicate_rank = 1 filter retains one record for each order ID.

Save models/staging/stg_orders.sql.
Switch back to the integrated Terminal from earlier.

Before you rebuild, do you expect the same two test failures or a clean run?

Rebuild the project resources plus their tests by running:

dbt build

You'll see every staging test pass. dbt also rebuilds daily_revenue from the repaired relation.

That closes the failure you exposed earlier: your staging layer now blocks missing keys plus repeated orders from reaching the marts.

Still seeing failed staging tests?

Check that where order_id is not null sits inside ranked_orders.
Check that the final line is exactly where duplicate_rank = 1.
Confirm that you saved models/staging/stg_orders.sql before rebuilding.

Help me diagnose my failing staging tests.

✔️ Awesome, I've got everything!

Your saved models/staging/stg_orders.sql now removes missing order IDs plus repeated order records.

ⓧ I'd like to double check the full code

Use this reference to compare the complete models/staging/stg_orders.sql file:

with normalized_orders as (
    select
        nullif(order_id, '') as order_id,
        customer_id,
        order_date,
        status,
        amount
    from {{ ref('raw_orders') }}
),

ranked_orders as (
    select
        order_id,
        customer_id,
        order_date,
        status,
        amount,
        row_number() over (
            partition by order_id
            order by order_date, customer_id, amount
        ) as duplicate_rank
    from normalized_orders
    where order_id is not null
)

select
    order_id,
    customer_id,
    order_date,
    status,
    amount
from ranked_orders
where duplicate_rank = 1

Check the corrected revenue

The daily_revenue model depends on stg_orders through ref(). A fresh preview proves that the repaired relation changed the business metric.

Before you preview it, which dates do you expect to lose revenue once the bad records disappear?

Preview the corrected daily revenue by running:

dbt show --select "daily_revenue"

You'll see revenue of 200.00 on 2026-10-01 plus 70.00 on 2026-10-02. The earlier totals were 280.00 plus 120.00.

Strong finish: your revenue mart now counts one valid row per order.

Revenue still looks inflated?

Confirm that the preceding build finished without failures.
Check that the final query reads from ranked_orders.
Check that duplicate_rank = 1 remains in the final filter.

Help me find why my revenue totals are still inflated.

Your staging layer now turns flawed source records into dependable order data. Next, you'll use those clean orders to build customer lifetime value metrics plus inspect their lineage.

Build and Document the Customer Mart

Your repaired staging layer now removes missing keys and duplicate orders before they reach daily_revenue. Its corrected totals give the warehouse a trusted foundation.

Clean orders become more valuable when they answer a customer question. In this step, you'll use dbt to calculate customer lifetime value inside your local DuckDB warehouse.

You'll also add descriptions plus data tests for both marts. The generated lineage site lets another engineer trace each metric back to its source.

In this step, get ready to:

Build a customer lifetime value mart from clean orders.
Document both marts with descriptions and data tests.
Generate a local documentation site with model lineage.

Build the customer lifetime value mart

A mart turns cleaned records into a metric that answers a business question. This SQL model measures each customer's valid order count plus total lifetime value.

The model joins raw_customers to the repaired stg_orders model. Its ref() calls also register both upstream dependencies in the project graph.

In the Visual Studio Code Explorer, select the models/marts/ folder.
Click New File... in the Explorer toolbar.
Enter customer_lifetime_value.sql.
Paste this model into customer_lifetime_value.sql:

select
    customers.customer_id,
    customers.customer_name,
    count(orders.order_id) as order_count,
    sum(orders.amount) as lifetime_value
from {{ ref('raw_customers') }} as customers
inner join {{ ref('stg_orders') }} as orders
    on customers.customer_id = orders.customer_id
group by
    customers.customer_id,
    customers.customer_name
order by lifetime_value desc

What does this model do?

The two ref() calls connect the customer seed and cleaned orders to this mart.
The inner join includes customers who have at least one valid order in stg_orders.
The count(orders.order_id) expression calculates each customer's valid order count.
The sum(orders.amount) expression calculates the customer's lifetime value.

Save customer_lifetime_value.sql.
Switch back to the integrated Terminal.
Preview the new mart by running this command:

dbt show --select "customer_lifetime_value"

What does this command do?

This command compiles the selected model against your configured DuckDB target. It previews the model's rows directly in the terminal.

You'll see Katherine Johnson with an order count of 1 plus a lifetime value of 200.00. Ada Lovelace follows with 2 orders plus 190.00 in lifetime value.

Grace Hopper appears with 1 order plus 80.00 in lifetime value. That is your customer mart working from the repaired staging data.

Can't preview the customer mart?

Confirm that customer_lifetime_value.sql sits inside models/marts/. Check that both model references match the code above.

If the query reports a syntax problem, compare the join condition plus the two grouped customer columns with the complete file below.

Help me troubleshoot my customer lifetime value dbt model.

✔️ Awesome, I've got everything!

Your customer lifetime value preview now shows three customers sorted by lifetime value. Double check that you saved customer_lifetime_value.sql.

ⓧ I'd like to double check the full code

Here is the complete customer_lifetime_value.sql file. Make sure yours matches exactly:

select
    customers.customer_id,
    customers.customer_name,
    count(orders.order_id) as order_count,
    sum(orders.amount) as lifetime_value
from {{ ref('raw_customers') }} as customers
inner join {{ ref('stg_orders') }} as orders
    on customers.customer_id = orders.customer_id
group by
    customers.customer_id,
    customers.customer_name
order by lifetime_value desc

This complete reference joins the two upstream relations before calculating one row of customer metrics per valid customer.

Document and test both marts

A YAML contract records what each mart represents. Its data tests also turn key metric assumptions into checks that run with the pipeline.

The unique plus not_null tests protect each mart's primary key. The lifetime value check prevents a customer metric from passing without a calculated value.

Select the models/marts/ folder in the Explorer.
Click New File... in the Explorer toolbar.
Enter marts.yml.
Paste this contract into marts.yml:

version: 2

models:
  - name: daily_revenue
    description: Corrected order count and revenue by order date.
    columns:
      - name: order_date
        description: Calendar date for the revenue metric.
        data_tests:
          - unique
          - not_null

  - name: customer_lifetime_value
    description: Completed order count and lifetime value for each customer with a valid order.
    columns:
      - name: customer_id
        description: Unique identifier for a customer in the mart.
        data_tests:
          - unique
          - not_null
      - name: lifetime_value
        description: Sum of valid order amounts for the customer.
        data_tests:
          - not_null

What does this contract protect?

The daily_revenue contract requires one non-null row for each order_date.
The customer_lifetime_value contract requires one non-null row for each customer_id.
The lifetime_value test confirms that every customer row contains a calculated metric.
The descriptions become model plus column documentation in the generated site.

Save marts.yml.

Before you run the build, predict whether any tests will fail now that the staging rows are clean.

Build the full project with its tests by running this command:

dbt build

What does this build check?

The build processes seeds plus models in dependency order. It also runs the staging and mart tests against the rebuilt relations.

Every seed, model, and data test should pass. Your cleaned orders now support both marts without breaking any documented assumption.

Preview the persisted customer metrics by running this command:

dbt show --select "customer_lifetime_value"

What does this preview confirm?

This preview reads the selected customer mart after the successful project build. It confirms that the tested model still returns its expected customer metrics.

You'll see Katherine Johnson at 200.00. Ada Lovelace follows at 190.00. Grace Hopper appears at 80.00.

Did a mart test fail?

Match each model name plus column name in marts.yml to the corresponding SQL output. YAML indentation must also match the complete file below.

Help me diagnose my failing mart tests.

✔️ Awesome, I've got everything!

Both marts are documented. The full build passes with primary-key checks plus a non-null lifetime value check.

ⓧ I'd like to double check the full code

Here is the complete marts.yml file. Make sure yours matches exactly:

version: 2

models:
  - name: daily_revenue
    description: Corrected order count and revenue by order date.
    columns:
      - name: order_date
        description: Calendar date for the revenue metric.
        data_tests:
          - unique
          - not_null

  - name: customer_lifetime_value
    description: Completed order count and lifetime value for each customer with a valid order.
    columns:
      - name: customer_id
        description: Unique identifier for a customer in the mart.
        data_tests:
          - unique
          - not_null
      - name: lifetime_value
        description: Sum of valid order amounts for the customer.
        data_tests:
          - not_null

This complete reference documents both marts plus the tests that protect their primary keys and customer metric.

Generate and inspect the lineage site

dbt can turn your model SQL plus YAML descriptions into a local documentation site. The site exposes each model's columns plus its position in the dependency graph.

Generate the local documentation artifacts by running this command:

dbt docs generate

What does this command generate?

This command compiles the project's model metadata and lineage information. It refreshes the local documentation artifacts used by the browser site.

The command returns you to the terminal prompt after generation completes. Your project documentation is ready to serve.

The next command starts a local server on port 8580. It opens the documentation site in your browser while the server keeps running in the integrated Terminal.

Before you start the site, predict which upstream nodes should feed customer_lifetime_value in the graph.

Serve the generated documentation site by running this command:

dbt docs serve

What should this command open?

This command serves the generated documentation locally. Your browser opens the site while the terminal keeps the server active.

You'll see the local dbt documentation site in your browser. The site contains the descriptions plus tests you added to marts.yml.

Can't see the documentation site?

Keep the server running in the integrated Terminal. If the browser stays closed, use the local address printed by the command to open the site manually.

Help me open my local dbt documentation site.

Open the documentation page for customer_lifetime_value.
Inspect its lineage graph.

You'll see raw_customers feeding the customer mart directly. You'll also see raw_orders feeding stg_orders before the clean orders reach both marts.

The graph proves that daily_revenue plus customer_lifetime_value share the same repaired order layer. Their descriptions plus tests are now visible beside that lineage.

Return to the integrated Terminal running the documentation server.
Press Control-C to stop the command.

You'll see the terminal prompt return. Your documentation remains generated locally while the server is safely stopped.

You now have tested customer metrics plus browser-viewable lineage. Another engineer can inspect what each mart means and trace every result back to its source.

Secret mission

Segment Customers by Value

Turn your customer lifetime value mart into a business-ready segmentation layer. Classify every customer as high_value or standard. Protect the labels with data tests.

Clean Up Your Resources

Choose whether to keep your local files, pause the documentation server, or delete the project entirely. Everything runs on your Mac through dbt and DuckDB, so there are no ongoing service costs.

Resources you used:

The commerce_elt local project folder containing your models, tests, configuration files, and seed data.
The target/ directory containing generated documentation and compiled project artifacts.
The logs/ directory containing local dbt logs.
The persistent commerce.duckdb database containing your seeds, staging models, and marts.

Keep everything running

No action is needed. Choose this if you want to keep developing the pipeline or inspecting its local data.

Keep the commerce_elt folder in its current location.
Leave target/, logs/, and commerce.duckdb inside the project folder.
Return to the tested marts when you want to extend the local warehouse.

Pause - I'll come back to this later

Pausing stops any local documentation server while every project file stays available. Your pipeline remains ready for another session.

Switch back to the integrated Terminal in Visual Studio Code.
Press Control+C if the documentation server is still running.
Confirm that the Terminal returns to its command prompt.
Keep the commerce_elt folder in its current location.

Delete - I don't want to use this again

Deleting the folder can feel final, but macOS keeps it recoverable until you empty the trash. The commerce_elt folder contains every project resource listed above.

Switch back to the integrated Terminal in Visual Studio Code.
Press Control+C if the documentation server is still running.
Press Cmd+Space to open macOS search.
Type Finder and press Enter to open it.
Search for commerce_elt using Finder's search field.
Select the folder whose contents include dbt_project.yml and commerce.duckdb.
Drag the selected commerce_elt folder to the trash icon in the Dock.
Review the trash for unrelated files you want to keep.
Empty the trash after those files are safe.
Confirm that Finder no longer lists the commerce_elt folder.

Nice Work!

You did it! You built a tested e-commerce ELT pipeline with dbt and DuckDB that catches bad order keys before they distort revenue.

You've learned how to:

Built a local ELT pipeline that loads version-controlled customer and order fixtures into layered staging and mart models.
Protected data quality with uniqueness, null, accepted-value, and relationship tests that expose unsafe records.
Repaired business metrics with key normalization plus window-function deduplication before producing corrected daily revenue, customer lifetime value, and documented lineage.
Completed the Secret Mission by building a tested customer segmentation mart that assigns every customer to the permitted high_value or standard segment.

Ready to quiz yourself?