ELT Pipeline with Python, dbt, and DuckDB

Build an e-commerce ELT pipeline with a star schema and data quality tests.

Introduction

30 Second Summary

Every online store you have ever used relies on clean, organized data to track orders, recommend products, and generate sales reports. Turning scattered raw records into those clean tables is the job of a data pipeline.

In this project, you will build a complete ELT pipeline on your local machine. You will extract raw e-commerce data from CSV files, load it into DuckDB with Python, and use dbt to transform that data into a clean star schema ready for analytics.

What You'll Build

Picture running a single command and watching dbt compile your SQL models, build a star schema, and run data quality tests that catch bad data before it ever reaches a dashboard.

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

  • A Python extraction script that loads raw CSV data into DuckDB and prints row counts confirming every record landed.
  • A star schema with a central fact table and dimension tables, built with dbt, that you can query to see every order enriched with customer names, product details, and calculated totals.
  • Data quality tests that automatically catch silent data corruption like orphaned foreign keys before bad data reaches your analytics layer.
  • Secret Mission: Build a monthly revenue report with cumulative totals and window functions.

Are there any prerequisites?

This project assumes basic SQL knowledge and basic Python knowledge. You should also have VS Code and Python 3.10+ already installed on your machine.

Before We Start

Before writing any code, take a moment to commit to what you are about to build and why it matters. This is the kind of data pipeline that data engineering teams rely on every day.

Set Up Your Data Engineering Environment

You need a properly configured dbt project connected to DuckDB before you can build any pipeline. This step gets all your tools installed and verified in one pass.

You will create a Python virtual environment, install dbt-duckdb, scaffold the dbt project structure, and confirm the database connection works.

In this step, get ready to:
  • Create a virtual environment and install dbt-duckdb.
  • Initialize and configure the dbt project with a DuckDB connection.
  • Verify the setup by running dbt debug.
Create a virtual environment and install dbt-duckdb

The dbt-duckdb adapter bundles dbt-core (the transformation framework) and DuckDB (an analytical database) in one package. A virtual environment keeps these dependencies isolated from other projects on your machine.

  • 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 ecommerce_pipeline
cd ecommerce_pipeline
  • Set up a Python virtual environment by running:
python -m venv venv
  • Confirm the virtual environment was created by running:
ls

You should see a venv folder in the output.

Seeing an error with python -m venv?

On macOS, the python command may point to an older system Python. Try python3 -m venv venv instead.

On Windows, make sure Python was added to your PATH during installation. Reinstall Python and check the Add Python to PATH box if you missed it.

On Windows, if ls does not work, use dir instead to list files.

Help me fix my Python virtual environment setup.

  • Activate the virtual environment by running this command:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt. This confirms you are working inside the virtual environment.

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

This installs dbt-core, DuckDB, and the adapter that connects them. The install may take a minute or two.

Seeing an error during install?

If you see a permissions error, make sure you activated the virtual environment first. Your prompt should start with (venv).

If the install hangs or fails with a network error, check your internet connection and try running the command again.

Help me fix my dbt-duckdb installation error.

Initialize the dbt project and configure the DuckDB connection

dbt needs a specific folder structure to find your models, tests, and configuration. The dbt init command scaffolds this structure for you.

  • Scaffold a new dbt project by running:
dbt init dbt_project --skip-profile-setup

What does this command do?

dbt init creates the standard dbt project structure: folders for models, tests, seeds, macros, and snapshots, plus a dbt_project.yml configuration file.

The --skip-profile-setup flag skips the interactive database prompt because you will create the connection profile manually in the next step.

The scaffolded files live inside a dbt_project subfolder. You need to move everything up into your main ecommerce_pipeline folder, then remove the empty subfolder and the default example models.

  • Move the project files up and clean up by running these commands:

macOS

mv dbt_project/* .
mv dbt_project/.gitignore .
rm -rf dbt_project
rm -rf models/example

Windows

Get-ChildItem dbt_project -Force | Move-Item -Destination .
Remove-Item dbt_project -Recurse -Force
Remove-Item models\example -Recurse -Force
  • Confirm the project structure by running:

macOS

ls

Windows

dir

You should see dbt_project.yml, models, tests, seeds, and other dbt folders alongside your venv folder.

Now open VS Code to create and edit your configuration files.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the ecommerce_pipeline folder, then click Open.

VS Code opens with your project files visible in the Explorer sidebar on the left.

  • Right-click in the empty space below the file list in the Explorer sidebar.
  • Select New File.
  • Type profiles.yml and press Enter.
  • Paste the following configuration into profiles.yml:
ecommerce_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: 'ecommerce.duckdb'
      schema: main
      threads: 4

What does profiles.yml do?

This file tells dbt how to connect to your database. The type: duckdb line selects the DuckDB adapter. The path points to a local ecommerce.duckdb file that dbt creates automatically when it first connects.

The profile name ecommerce_pipeline must match the profile field in dbt_project.yml. You will set that up next.

  • Save profiles.yml by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Double check that you have saved your profiles.yml file.

ⓧ I'd like to double check the full code

Here is the complete profiles.yml file:

ecommerce_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: 'ecommerce.duckdb'
      schema: main
      threads: 4
  • Click on dbt_project.yml in the Explorer sidebar to open it.
  • Select all the existing content by pressing Cmd+A (macOS) or Ctrl+A (Windows).
  • Replace the content with the following configuration:
name: 'ecommerce_pipeline'
version: '1.0.0'

profile: 'ecommerce_pipeline'

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_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

What is the models section doing?

The models section at the bottom tells dbt how to materialize the SQL models you will write later. Models in the staging folder will be created as views (lightweight queries that always reflect the latest data).

Models in the marts folder will be created as tables (pre-computed results that are faster to query). This is a common pattern in production dbt projects.

  • Save dbt_project.yml by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Double check that you have saved your dbt_project.yml file.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml file:

name: 'ecommerce_pipeline'
version: '1.0.0'

profile: 'ecommerce_pipeline'

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_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

Switch back to your terminal window. You need to tell dbt where to find the profiles.yml file you just created.

Why set this variable?

By default, dbt looks for profiles.yml in a ~/.dbt/ folder in your home directory. Setting DBT_PROFILES_DIR to . tells dbt to look in the current project folder instead. This keeps everything in one place.

  • Set the profiles directory by running this command:

macOS

export DBT_PROFILES_DIR=.

Windows

set DBT_PROFILES_DIR=.

This only lasts for your current session

This environment variable resets when you close the terminal. If you come back to this project later, you will need to activate the virtual environment and set this variable again.

Verify the connection

Everything is configured. Before you move on, verify that dbt can find your profile, connect to DuckDB, and validate your project structure.

Before you run the next command, what do you think the output will show if all your configuration is correct?

  • Verify your setup by running:
dbt debug

You should see a series of checks, ending with All checks passed! at the bottom. This confirms dbt found your profiles.yml, connected to DuckDB, and validated the project configuration.

Not seeing All checks passed?

If you see a profile-not-found error, make sure you set the DBT_PROFILES_DIR environment variable in the same terminal session where you ran dbt debug.

If the profile name does not match, check that the top-level key in profiles.yml is ecommerce_pipeline and that dbt_project.yml has profile: 'ecommerce_pipeline'. These names must match exactly.

If you see a DuckDB connection error, confirm your profiles.yml has type: duckdb and path: 'ecommerce.duckdb'.

Help me debug why dbt debug is failing.

Your environment is locked and loaded. dbt is installed, the project is scaffolded, and the DuckDB connection is verified. Next up, you will create raw data files and load them into DuckDB with a Python script.

Extract and Load Raw Data into DuckDB

In the previous step, you connected dbt to DuckDB and confirmed the connection works. But the database is completely empty right now. Before dbt can transform anything, data needs to exist in the warehouse.

This step simulates the "EL" half of an ELT pipeline. You will create raw e-commerce data as CSV files, then write a Python script that uses the DuckDB Python API to load those files into database tables.

In this step, get ready to:
  • Create raw CSV data files with customer, order, and product data.
  • Write a Python script that loads CSV data into DuckDB tables.
Create the raw data CSV files

Every pipeline starts with raw data. You will create three CSV files representing a small e-commerce dataset with customers, orders, and products.

  • Create a data/ folder inside your ecommerce_pipeline directory by running this command:
mkdir data
  • In VS Code, right-click the data folder in the Explorer sidebar and select New File.
  • Name the file raw_customers.csv.
  • Paste the following data into the file:
customer_id,first_name,last_name,email,created_at
1,Alice,Johnson,alice@example.com,2024-01-15
2,Bob,Smith,bob@example.com,2024-02-20
3,Carol,Williams,,2024-03-10
4,Dave,Brown,dave@example.com,2024-01-25
5,Eve,Davis,eve@example.com,2024-04-01
6,Frank,Miller,frank@example.com,2024-02-14
7,Grace,Wilson,grace@example.com,2024-03-22
8,Hank,Moore,hank@example.com,2024-05-10
9,Ivy,Taylor,,2024-04-18
10,Jack,Anderson,jack@example.com,2024-06-01

What is this data?

This file holds 10 customers. Notice that rows 3 (Carol) and 9 (Ivy) have empty email fields. These missing values are intentional. You will handle them in a later step.

  • Save data/raw_customers.csv.
  • Confirm the file exists by running:
ls data/

You should see raw_customers.csv listed in the output.

  • Create another file in the data/ folder called raw_orders.csv using the same method (right-click data, select New File).
  • Paste the following data into the file:
order_id,customer_id,product_id,quantity,order_date,status
1001,1,101,1,2024-03-01,completed
1002,2,103,3,2024-03-05,completed
1003,1,105,1,2024-03-10,completed
1004,3,102,2,2024-03-15,shipped
1005,5,104,1,2024-03-20,completed
1006,4,101,1,2024-04-01,completed
1007,2,107,1,2024-04-05,returned
1008,6,106,1,2024-04-10,completed
1009,99,103,2,2024-04-15,completed
1010,7,108,5,2024-04-20,completed
1011,8,105,1,2024-05-01,shipped
1012,3,101,1,2024-05-05,completed
1013,10,999,1,2024-05-10,completed
1014,5,102,3,2024-05-15,completed
1015,1,104,1,2024-05-20,completed
1016,9,107,1,2024-06-01,completed
1017,4,103,2,2024-06-05,completed
1018,6,105,1,2024-06-10,shipped
1019,2,108,4,2024-06-15,completed
1020,8,106,1,2024-06-20,completed

Spot the hidden problems

This file holds 20 orders. Two of them contain intentional data quality problems that you will catch later in the project.

  • Order 1009 references customer_id=99, but no customer with ID 99 exists in the customers file.
  • Order 1013 references product_id=999, but no product with ID 999 exists in the products file.

These orphaned foreign keys are the kind of silent data corruption that costs companies real money. Keep them in mind.

  • Save data/raw_orders.csv.
  • Create one more file in the data/ folder called raw_products.csv.
  • Paste the following data into the file:
product_id,product_name,category,price
101,Wireless Mouse,Electronics,29.99
102,USB-C Cable,Electronics,12.99
103,Notebook,Office Supplies,4.99
104,Desk Lamp,Office Supplies,34.99
105,Mechanical Keyboard,Electronics,89.99
106,Monitor Stand,Office Supplies,49.99
107,Webcam HD,Electronics,59.99
108,Sticky Notes,Office Supplies,3.49

What does this data cover?

This file holds 8 products split across two categories: Electronics and Office Supplies. Each product has a unique ID, a name, and a price.

  • Save data/raw_products.csv.
  • Confirm all three files exist by running:
ls data/

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

Don't see all three files?

  • Make sure you created the files inside the data/ folder, not in the project root.
  • Check that the file names match exactly, including the .csv extension.
  • Confirm you saved each file after pasting the data.

Help me figure out why my CSV files aren't appearing in the data/ folder.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Scroll up and confirm each CSV file matches the content shown above. Pay special attention to:

  • raw_customers.csv has 10 data rows (plus the header). Rows 3 and 9 have empty email fields.
  • raw_orders.csv has 20 data rows. Order 1009 has customer_id 99 and order 1013 has product_id 999.
  • raw_products.csv has 8 data rows with prices as decimal values.
Write and run the Python extraction script

The CSV files exist on disk, but dbt cannot read them directly. You need to load them into DuckDB tables first. This is the "Load" step of the ELT process.

  • Create a new file called extract_load.py in the root of your ecommerce_pipeline folder (not inside data/).
  • Paste the following Python script into the file:
import duckdb


def main():
    # Connect to the same DuckDB file that dbt uses
    con = duckdb.connect("ecommerce.duckdb")

    # Create a 'raw' schema to keep raw data separate from dbt models
    con.execute("CREATE SCHEMA IF NOT EXISTS raw")

    # Load each CSV into a table in the raw schema
    con.execute("""
        CREATE OR REPLACE TABLE raw.customers AS
        SELECT * FROM read_csv_auto('data/raw_customers.csv')
    """)

    con.execute("""
        CREATE OR REPLACE TABLE raw.orders AS
        SELECT * FROM read_csv_auto('data/raw_orders.csv')
    """)

    con.execute("""
        CREATE OR REPLACE TABLE raw.products AS
        SELECT * FROM read_csv_auto('data/raw_products.csv')
    """)

    # Print row counts to confirm each table loaded
    tables = ["raw.customers", "raw.orders", "raw.products"]
    for table in tables:
        count = con.execute(f"SELECT count(*) FROM {table}").fetchone()[0]
        print(f"Loaded {table}: {count} rows")

    con.close()


if __name__ == "__main__":
    main()

What does this script do?

  • duckdb.connect("ecommerce.duckdb") opens the same database file that dbt will use (configured in profiles.yml).
  • CREATE SCHEMA IF NOT EXISTS raw creates a separate schema so raw data stays isolated from dbt's transformed models.
  • read_csv_auto is a DuckDB function that reads a CSV file and automatically detects column types, headers, and delimiters.
  • The loop at the end queries each table's row count and prints it, giving you instant confirmation that the load worked.
  • Save extract_load.py.

✔️ Awesome, I've got everything!

Great. Double check you saved the file before running it.

ⓧ I'd like to double check the full code

import duckdb


def main():
    con = duckdb.connect("ecommerce.duckdb")

    con.execute("CREATE SCHEMA IF NOT EXISTS raw")

    con.execute("""
        CREATE OR REPLACE TABLE raw.customers AS
        SELECT * FROM read_csv_auto('data/raw_customers.csv')
    """)

    con.execute("""
        CREATE OR REPLACE TABLE raw.orders AS
        SELECT * FROM read_csv_auto('data/raw_orders.csv')
    """)

    con.execute("""
        CREATE OR REPLACE TABLE raw.products AS
        SELECT * FROM read_csv_auto('data/raw_products.csv')
    """)

    tables = ["raw.customers", "raw.orders", "raw.products"]
    for table in tables:
        count = con.execute(f"SELECT count(*) FROM {table}").fetchone()[0]
        print(f"Loaded {table}: {count} rows")

    con.close()


if __name__ == "__main__":
    main()

Before you run this script, think about what you expect to see. How many rows should each table have, based on the CSV files you just created?

  • Run the extraction script by running this command:
python extract_load.py

You should see this output:

Loaded raw.customers: 10 rows
Loaded raw.orders: 20 rows
Loaded raw.products: 8 rows

Each row count matches the number of data rows in the CSV files. Your raw data is now inside DuckDB, ready for dbt to transform.

Seeing a FileNotFoundError?

  • Make sure you are running the command from inside the ecommerce_pipeline folder, not from inside data/ or any other subdirectory.
  • Check that the data/ folder contains all three CSV files. Run ls data/ to verify.
  • Confirm the filenames match exactly: raw_customers.csv, raw_orders.csv, and raw_products.csv.

Help me fix a FileNotFoundError in my DuckDB extraction script.

Why Python instead of dbt seeds?

dbt has a built-in feature called seeds that can load CSV files. However, seeds are designed for small reference data like country codes, not for pipeline ingestion.

Using a Python script teaches you the real-world pattern: data teams write extraction scripts in Python to pull data from APIs, databases, and file systems. The dbt layer then focuses purely on transformation.

Your warehouse now has raw data sitting in DuckDB tables. Next up, you will use dbt to transform that messy raw data into clean, standardized staging models.

Build Staging Models with dbt

Your raw data is sitting in DuckDB, but it is far from ready for analysis.

Column types are inconsistent, some emails are missing, and field names do not follow a standard convention. Staging models are where you fix all of that. They sit between your raw tables and your analytics layer, creating a clean contract that every downstream model can rely on. This step gives you your first visible dbt result.

In this step, get ready to:
  • Define dbt sources that map to your raw tables.
  • Create staging models that clean and standardize the data.
  • Run dbt and query the results to see clean output.
Define dbt sources pointing to the raw tables

Before dbt can read your raw tables, it needs a map telling it where they live. A source definition in dbt connects a friendly name (like raw) to an actual database schema and its tables.

  • Create a models/staging/ directory inside your ecommerce_pipeline folder by running this command:
mkdir -p models/staging
  • Confirm the directory exists by running:
ls models/

You should see staging listed alongside any other folders dbt created.

  • Create a new file called _sources.yml inside models/staging/ and paste the following code:
version: 2

sources:
  - name: raw
    schema: raw
    tables:
      - name: customers
      - name: orders
      - name: products

What does this file do?

This YAML file registers your three raw tables as dbt sources. The name: raw label matches the raw schema your Python script created in DuckDB.

With this in place, your staging models can reference these tables using the {{ source('raw', 'customers') }} syntax instead of hardcoding table names.

  • Save _sources.yml.

File not saving in the right place?

Make sure _sources.yml is inside models/staging/, not in models/ or the project root. The underscore at the start of the filename is intentional.

Help me find where to save my _sources.yml file

✔️ Awesome, I've got everything!

Great. Double check you have saved _sources.yml before moving on.

ⓧ I'd like to double check the full code

version: 2

sources:
  - name: raw
    schema: raw
    tables:
      - name: customers
      - name: orders
      - name: products
Create three staging models

Each staging model selects from one raw source, casts data types, renames columns for consistency, and handles missing values. Think of staging as a filter that every row must pass through before reaching your analytics layer.

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

cleaned as (
    select
        customer_id,
        first_name,
        last_name,
        first_name || ' ' || last_name as full_name,
        coalesce(email, 'unknown') as email,
        cast(created_at as date) as created_at
    from source
)

select * from cleaned

What does this model do?

  • source('raw', 'customers') pulls data from the raw table you defined in _sources.yml.
  • first_name || ' ' || last_name as full_name concatenates the two name columns into a single full_name field that is easier to work with downstream.
  • coalesce(email, 'unknown') replaces NULL emails with the string unknown so no row has a blank email field.
  • cast(created_at as date) ensures the date column is stored as a proper date type, not a string.
  • Save stg_customers.sql.
  • Run your first dbt build to see this model in action:
dbt run

You should see output ending with a summary like Completed successfully and PASS=1. dbt built stg_customers as a view in DuckDB.

Seeing a compilation error?

Check that _sources.yml is saved inside models/staging/ and that the source name raw matches exactly. Also confirm the DBT_PROFILES_DIR environment variable is still set to . from Step 1.

Help me debug my dbt run error

  • Create a new file called stg_orders.sql inside models/staging/ and paste the following code:
with source as (
    select * from {{ source('raw', 'orders') }}
),

cleaned as (
    select
        order_id,
        customer_id,
        product_id,
        quantity,
        cast(order_date as date) as order_date,
        status
    from source
)

select * from cleaned

What does this model do?

This model selects every column from the raw orders table and casts order_date to a proper date type. For now, all 20 order rows pass through.

  • Save stg_orders.sql.
  • Create a new file called stg_products.sql inside models/staging/ and paste the following code:
with source as (
    select * from {{ source('raw', 'products') }}
),

cleaned as (
    select
        product_id,
        product_name,
        category,
        cast(price as decimal(10, 2)) as price
    from source
)

select * from cleaned

What does this model do?

This model casts the price column to decimal(10, 2), which ensures prices are stored with exactly two decimal places. Without the cast, DuckDB might infer a float type, which can cause rounding issues in financial calculations.

  • Save stg_products.sql.

✔️ Awesome, I've got everything!

Great. Make sure all three staging SQL files are saved inside models/staging/.

ⓧ I'd like to double check the full code

Here is each staging model for comparison.

stg_customers.sql:

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

cleaned as (
    select
        customer_id,
        first_name,
        last_name,
        first_name || ' ' || last_name as full_name,
        coalesce(email, 'unknown') as email,
        cast(created_at as date) as created_at
    from source
)

select * from cleaned

stg_orders.sql:

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

cleaned as (
    select
        order_id,
        customer_id,
        product_id,
        quantity,
        cast(order_date as date) as order_date,
        status
    from source
)

select * from cleaned

stg_products.sql:

with source as (
    select * from {{ source('raw', 'products') }}
),

cleaned as (
    select
        product_id,
        product_name,
        category,
        cast(price as decimal(10, 2)) as price
    from source
)

select * from cleaned
Run dbt and query the results

All three staging models are saved. Time to build them all and inspect the output.

  • Before you run this, how many models do you think dbt will build? Think about the staging models you just created.
  • Build all models by running:
dbt run

You should see three models listed in the output, each marked OK, with a final summary of PASS=3. dbt built each staging model as a view because of the +materialized: view setting in your dbt_project.yml.

Why views instead of tables?

A view is a saved SQL query that runs on the fly every time you read from it. Staging models are intermediate layers, so views keep them lightweight. Your final analytics tables (built later in the marts layer) will use +materialized: table to store results physically for faster reads.

  • Query the cleaned customer data to confirm the transformations worked:
dbt show --inline "select * from stg_customers limit 5"

You should see a table with columns like customer_id, first_name, last_name, full_name, email, and created_at. Notice that the full_name column combines first and last names into one field. Any customer who had a missing email now shows unknown instead of a blank.

Seeing a relation not found error?

Make sure you ran dbt run before the dbt show command. The dbt show --inline command queries the actual database, so the staging views must exist first.

Also confirm that extract_load.py was run in Step 2. The staging models depend on the raw tables it created.

Help me fix my dbt show query error

Your staging layer is live. Raw data now flows through a clean, consistent interface before anything else touches it. Next up, you will build a star schema on top of these staging models to answer real analytics questions.

Build a Star Schema for Analytics

Your staging models have cleaned and standardized the raw data. But an analyst asking "what is our revenue by product category?" still needs to join three separate tables manually to get an answer.

A star schema solves this by organizing data around a central fact table (the transactions) surrounded by dimension tables (the context: who bought what). In this step, you'll build dimension tables for customers and products, then a fact table that joins everything into one analytics-ready view.

In this step, get ready to:
  • Create dimension models for customers and products.
  • Build a fact table that joins orders with customer and product details.
  • Query the fact table and discover a data quality issue hiding in the raw data.
Create the dimension and fact models

Dimension tables hold the descriptive context that gives meaning to your transactions. A customer's name, an email address, a product category. These are the "who" and "what" behind every order.

  • Create a models/marts/ folder inside your ecommerce_pipeline project by running this command:
mkdir models/marts
  • Confirm the folder was created by running:
ls models/

You should see both marts and staging listed.

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

select
    customer_id,
    full_name,
    email,
    created_at
from customers

What does this model do?

This dimension model pulls cleaned customer data from your stg_customers staging model using dbt's ref() function. It selects only the columns analysts need: the customer's ID, full name, email, and signup date.

  • Save dim_customers.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

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

select
    customer_id,
    full_name,
    email,
    created_at
from customers
  • Create another new file called dim_products.sql inside models/marts/ and paste the following code:
with products as (
    select * from {{ ref('stg_products') }}
)

select
    product_id,
    product_name,
    category,
    price
from products

What does this model do?

This dimension model surfaces product details (name, category, price) from stg_products. Together with dim_customers, these two dimension tables provide all the descriptive context your fact table will need.

  • Save dim_products.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

with products as (
    select * from {{ ref('stg_products') }}
)

select
    product_id,
    product_name,
    category,
    price
from products

Your two dimension models are ready. Before creating the fact table, let's make sure dbt can build them.

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

You should see 5 models build successfully: your 3 staging views plus the 2 new dimension tables.

Seeing a compilation error?

  • Check that both .sql files are saved inside models/marts/, not in models/staging/ or the project root.
  • Verify the ref() names match exactly: stg_customers and stg_products. These must match the filenames of your staging models (without the .sql extension).
  • Why won't my dbt dimension models build?

Now for the centerpiece of the star schema: the fact table. This model joins orders with both dimension tables so every row has the full picture.

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

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

products as (
    select * from {{ ref('stg_products') }}
)

select
    o.order_id,
    o.order_date,
    o.status,
    o.quantity,
    c.customer_id,
    c.full_name as customer_name,
    p.product_id,
    p.product_name,
    p.category as product_category,
    p.price as unit_price,
    o.quantity * p.price as order_total
from orders o
left join customers c on o.customer_id = c.customer_id
left join products p on o.product_id = p.product_id

What does this model do?

  • Three CTEs pull in data from stg_orders, stg_customers, and stg_products using ref() so dbt knows the dependency order.
  • LEFT JOINs connect each order to its customer and product. LEFT (not INNER) means orders are kept even if a matching customer or product is missing.
  • The calculated column order_total multiplies quantity * price so analysts get a dollar amount per order without recalculating it every time.

Why LEFT JOINs instead of INNER JOINs?

An INNER JOIN silently drops any order that cannot find a matching customer or product. A LEFT JOIN keeps every order and fills in NULLs where a match is missing. This makes data quality problems visible instead of hiding them.

  • Save fct_orders.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

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

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

products as (
    select * from {{ ref('stg_products') }}
)

select
    o.order_id,
    o.order_date,
    o.status,
    o.quantity,
    c.customer_id,
    c.full_name as customer_name,
    p.product_id,
    p.product_name,
    p.category as product_category,
    p.price as unit_price,
    o.quantity * p.price as order_total
from orders o
left join customers c on o.customer_id = c.customer_id
left join products p on o.product_id = p.product_id
Run and inspect the results
  • Rebuild all your models (staging + marts) by running this command:
dbt run

You should see 6 models build successfully: 3 staging views and 3 mart tables (dim_customers, dim_products, and fct_orders).

Seeing a compilation or runtime error?

  • Check that all three .sql files are in models/marts/. Files in the wrong directory will not be found by dbt.
  • Verify that your ref() calls use single quotes: {{ ref('stg_orders') }}, not double quotes.
  • Why is my dbt run failing on the fact table?

The fact table built successfully, which means every order now carries customer names, product details, and a calculated total. Before you move on, think about this: do you expect every single row in fct_orders to have clean, complete data?

  • Search for rows with missing data by running this command:
dbt show --inline "select * from fct_orders where customer_name is null or product_name is null"

Two rows appear with NULL values. Order 1009 has customer_id=99, which does not exist in the customers table. Order 1013 has product_id=999, which does not exist in the products table. The LEFT JOINs kept these orphaned orders, filling in NULLs where a match was missing.

Why does this matter?

This is exactly the kind of silent data corruption that costs companies real money. A dashboard summing order_total would quietly undercount revenue because the NULL totals are excluded from the sum. Nobody gets an error. The numbers just look slightly wrong.

You discovered this by manually querying. In the next step, you'll add automated dbt tests that catch these issues every time the pipeline runs.

Your star schema is built and you've found bad data hiding in plain sight. Next up, you'll add dbt tests that catch these issues automatically so they never reach a dashboard.

Add Data Quality Tests

In the last step, you queried fct_orders and spotted rows with NULL values for customer_name and product_name. Those NULLs are the kind of silent data corruption that costs companies real money.

If a dashboard pulls revenue by customer and two orders have no customer name, the numbers are quietly wrong. dbt tests catch these issues automatically before bad data reaches reports. In this step, you will turn your pipeline from "it runs" into "it runs correctly."

In this step, get ready to:
  • Add test definitions for your staging and mart models.
  • Run dbt test and watch it catch the data quality issues you found manually.
  • Fix the staging layer so all tests pass on every future run.
Add schema tests for staging and mart models

dbt lets you declare tests in YAML files that live alongside your models. Each test is a simple assertion: "this column must be unique" or "this column must never be NULL."

  • Create a new file called _staging.yml inside your models/staging/ folder and paste in this code:
version: 2

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

  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

  - name: stg_products
    columns:
      - name: product_id
        tests:
          - unique
          - not_null

What does this file do?

Each model gets tests on its primary key column. The unique test checks that no two rows share the same ID. The not_null test checks that every row has an ID value.

If either test fails, dbt reports an error instead of silently building on bad data. These are the most fundamental data quality checks in any pipeline.

  • Save _staging.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved models/staging/_staging.yml before moving on.

ⓧ I'd like to double check the full code

version: 2

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

  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

  - name: stg_products
    columns:
      - name: product_id
        tests:
          - unique
          - not_null

Now your staging models have basic guardrails. The mart layer needs stricter tests because your fact table is where bad data has the most impact.

  • Create a new file called _marts.yml inside your models/marts/ folder and paste in this code:
version: 2

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_name
        tests:
          - not_null
      - name: product_name
        tests:
          - not_null
      - name: order_total
        tests:
          - not_null

  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: dim_products
    columns:
      - name: product_id
        tests:
          - unique
          - not_null

Why test customer_name and product_name?

These are the exact columns where you found NULL values in Step 4. The not_null tests on customer_name, product_name, and order_total will catch any orphaned foreign keys that produce NULLs after the LEFT JOIN.

The dimension tables (dim_customers and dim_products) get unique and not_null tests on their primary keys to prevent duplicate IDs from causing double-counted joins in the fact table.

  • Save _marts.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved models/marts/_marts.yml before moving on.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_name
        tests:
          - not_null
      - name: product_name
        tests:
          - not_null
      - name: order_total
        tests:
          - not_null

  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: dim_products
    columns:
      - name: product_id
        tests:
          - unique
          - not_null

Seeing a YAML parsing error?

YAML is sensitive to indentation. Make sure each - name: line is indented with exactly 2 spaces under models:. Mixing tabs and spaces will cause dbt to throw a parsing error.

Help me fix my YAML parsing error in dbt.

Run dbt test and see the failures

Your tests are defined. Time to run them against the data that is already in your pipeline.

Before you run this, think back to what you saw in Step 4. Do you expect all the tests to pass, or will some fail? Which ones?

  • Run all your dbt tests by running this command:
dbt test

You will see two test failures in the output: not_null_fct_orders_customer_name and not_null_fct_orders_product_name.

Why did these tests fail?

Order 1009 references customer_id=99 and order 1013 references product_id=999. Neither ID exists in the customers or products tables. The LEFT JOINs in fct_orders produced NULLs for those rows, and the not_null tests caught them.

This is exactly what the tests are designed to do. Without them, this bad data would silently reach your dashboards and corrupt your analytics.

Fix the staging layer and verify

The tests told you where the problem is. The right place to filter out bad data is in the staging layer, before it ever reaches your mart models.

  • In VS Code, open models/staging/stg_orders.sql.
  • Find the current contents of the file, which look like this:
with source as (
    select * from {{ source('raw', 'orders') }}
),

cleaned as (
    select
        order_id,
        customer_id,
        product_id,
        quantity,
        cast(order_date as date) as order_date,
        status
    from source
)

select * from cleaned
  • Replace the entire contents of stg_orders.sql with this updated version that filters out invalid references:
with source as (
    select * from {{ source('raw', 'orders') }}
),

-- Look up which customer IDs actually exist
valid_customers as (
    select customer_id from {{ source('raw', 'customers') }}
),

-- Look up which product IDs actually exist
valid_products as (
    select product_id from {{ source('raw', 'products') }}
),

cleaned as (
    select
        order_id,
        customer_id,
        product_id,
        quantity,
        cast(order_date as date) as order_date,
        status
    from source
    -- Only keep orders with valid references
    where customer_id in (select customer_id from valid_customers)
      and product_id in (select product_id from valid_products)
)

select * from cleaned

What changed?

Two new CTEs (valid_customers and valid_products) query the raw source tables for IDs that actually exist. The WHERE clause in cleaned filters out any order whose customer_id or product_id does not match a real record.

This is referential integrity validation at the staging layer. Bad data gets caught early so downstream models like fct_orders never see it.

  • Save stg_orders.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved models/staging/stg_orders.sql before moving on.

ⓧ I'd like to double check the full code

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

valid_customers as (
    select customer_id from {{ source('raw', 'customers') }}
),

valid_products as (
    select product_id from {{ source('raw', 'products') }}
),

cleaned as (
    select
        order_id,
        customer_id,
        product_id,
        quantity,
        cast(order_date as date) as order_date,
        status
    from source
    where customer_id in (select customer_id from valid_customers)
      and product_id in (select product_id from valid_products)
)

select * from cleaned

Not sure how to replace the file contents?

Select all the existing text in stg_orders.sql with Cmd+A (macOS) or Ctrl+A (Windows), then paste the new code over it. The entire file should be replaced.

Help me replace the contents of stg_orders.sql.

Now the staging layer filters bad data and you have tests to verify it. Before you run this next command, do you think all the tests will pass now? What changed that should fix the two failures?

  • Rebuild all models and run all tests in one shot by running this command:
dbt build

You should see all models rebuild successfully and all tests pass. The summary line will show zero errors and zero failures.

  • Verify the bad data is gone by running this command:
dbt show --inline "select * from fct_orders where customer_name is null or product_name is null"

The query returns zero rows. Orders 1009 and 1013 were filtered out at the staging layer, so fct_orders now contains 18 clean rows with no NULL values.

Still seeing test failures?

Make sure you saved stg_orders.sql after pasting the new code. If the WHERE clause is missing or mistyped, the invalid orders will still flow through.

Also confirm that the two new CTEs (valid_customers and valid_products) reference {{ source('raw', 'customers') }} and {{ source('raw', 'products') }} respectively.

Help me debug why dbt build is still showing test failures after I updated stg_orders.sql.

Your pipeline now has automated data quality gates. Every time you run dbt build, dbt compiles your models, builds them, and runs every test in one pass. Bad data gets caught before it ever reaches a dashboard.

Secret mission

Build a Monthly Revenue Report

Your pipeline builds clean analytics tables and catches bad data. But stakeholders need reports, not raw tables. Can you build a monthly revenue report that tracks cumulative revenue over time using SQL window functions?

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • ecommerce_pipeline project folder on your Desktop (contains all project files, the Python virtual environment, the ecommerce.duckdb database, and dbt artifacts).

Keep everything running

No action needed. Choose this if you are still actively building or want to revisit your pipeline later.

  • Your entire project lives inside the ecommerce_pipeline folder on your Desktop. Everything stays in place.
  • If the dbt docs server is still running in your terminal, you can stop it anytime by pressing Ctrl+C.
  • To resume work later, open a terminal in the ecommerce_pipeline folder, activate the virtual environment, and pick up where you left off.

Pause - I'll come back to this later

Shut down running processes to free up system resources. Your project files stay on disk so you can resume later.

  • If the dbt docs server is still running, press Ctrl+C in that terminal to stop it.
  • Deactivate the Python virtual environment by running:
deactivate
  • To resume later, open a terminal in the ecommerce_pipeline folder on your Desktop and reactivate the virtual environment with venv\Scripts\activate (Windows) or source venv/bin/activate (macOS/Linux).

Delete - I don't want to use this again

Remove all project files and start fresh. This deletes the project folder, virtual environment, DuckDB database, and all dbt models.

  • If the dbt docs server is still running, press Ctrl+C in that terminal to stop it.
  • Deactivate the virtual environment if it is still active by running:
deactivate
  • Navigate to your Desktop and delete the entire project folder.

On macOS/Linux:

cd ~/Desktop
rm -rf ecommerce_pipeline

On Windows:

cd ~\Desktop
rmdir /s /q ecommerce_pipeline
  • Confirm the folder is gone by running:
ls

You should no longer see ecommerce_pipeline in the output.

Nice Work!

Nice Work!

You just built a complete e-commerce ELT pipeline from scratch. Raw CSV data flows into DuckDB through a Python extraction script, gets cleaned by dbt staging models, and lands in a star schema ready for analytics.

You've learned how to:

  • Built a Python extraction script that loads raw CSV data into DuckDB and created dbt staging models that clean, cast, and standardize raw data into a consistent contract.
  • Designed a star schema with a central fact table (fct_orders) and two dimension tables (dim_customers, dim_products) following real-world dimensional modeling patterns.
  • Discovered silent data corruption from orphaned foreign keys, added dbt tests that catch these issues automatically, and fixed the staging layer so all tests pass.
  • Completed an optional Secret Mission to push your data engineering skills further.

Ready to quiz yourself?