Build a Star Schema with dbt and DuckDB

Transform raw e-commerce data into a Kimball star schema using dbt.

Introduction

30 Second Summary

Every company collects raw data, but raw data is messy. Orders reference customers that don't exist. Column names are inconsistent. Analysts waste hours cleaning data every time they write a query. Data engineers solve this by building pipelines that transform raw data into clean, reliable tables designed for analytics.

In this project, you will build a local analytics pipeline using dbt and DuckDB that transforms raw e-commerce CSV data into a star schema optimized for analytics queries, complete with automated tests that catch data quality issues before they reach dashboards.

What You'll Build

You run a single dbt build command and watch dbt compile your SQL models, resolve dependencies, materialize dimension and fact tables into DuckDB, and run automated tests that validate referential integrity across your entire star schema.

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

  • A Kimball-style star schema with dimension tables and a fact table that you can query with simple joins to answer business questions like "total sales by product category."
  • A layered dbt project using the industry-standard staging and marts pattern, where raw data flows through reusable cleaning models before reaching your analytics tables.
  • Automated data quality contracts that catch a real referential integrity violation hiding in your seed data, proving that bad rows never silently corrupt downstream analytics.
  • Secret Mission: Generate a date dimension using DuckDB's date functions and build a One Big Table that pre-joins everything for BI tools.

Are there any prerequisites?

You should be comfortable writing SQL (SELECT, JOIN, WHERE) and navigating your terminal. No prior dbt experience is needed.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters for your data engineering skills.

Set Up dbt and DuckDB

To build a star schema, you need a transformation tool and a database. In this project, that means dbt (data build tool) for writing SQL transformations, and DuckDB as a local analytics database that runs in-process with zero infrastructure.

In this step, you will install both tools inside a Python virtual environment, scaffold a new dbt project, and confirm that dbt can talk to DuckDB. Everything runs locally on your machine with instant feedback.

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

A virtual environment keeps your dbt packages isolated from other Python projects on your machine. You will create one, activate it, then install the dbt-duckdb adapter (which bundles dbt-core and DuckDB together).

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a virtual environment called dbt-env and activate it by running these commands:

macOS

python3 -m venv dbt-env
source dbt-env/bin/activate

Windows

python -m venv dbt-env
dbt-env\Scripts\activate

Your terminal prompt now shows (dbt-env) at the beginning, confirming the virtual environment is active.

  • Upgrade your packaging tools and install dbt-duckdb by running these commands:
python -m pip install --upgrade pip wheel setuptools
pip install dbt-duckdb

What does this install?

The dbt-duckdb package installs three things at once: dbt-core (the transformation framework), the DuckDB adapter, and DuckDB itself (an embedded analytics database). One command gives you the full stack.

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

You should see output showing dbt-core with a version starting with 1.11 and dbt-duckdb listed as an installed adapter.

Command not found?

If you see a "command not found" error, your virtual environment may not be active. Check that your terminal prompt starts with (dbt-env). If not, re-run the activation command for your OS.

On macOS, if python3 is not found, you may need to install Python 3.10 or higher from python.org.

Help me fix my dbt installation

Initialize the dbt project and configure DuckDB

Now that dbt is installed, you will scaffold a new project and tell dbt how to connect to DuckDB. The dbt init command creates the standard folder structure every dbt project uses.

  • Initialize a new dbt project by running:
dbt init ecommerce_analytics

What will dbt ask you?

The dbt init command is interactive. It will ask you which database adapter to use. When you see the prompt, select the number for duckdb and press Enter.

After that, dbt creates a folder called ecommerce_analytics on your Desktop with sample models, a dbt_project.yml config file, and the standard directory layout (models, seeds, tests, macros).

Next, you need to configure the connection profile so dbt knows where to create the DuckDB database file.

  • Open the profiles file at ~/.dbt/profiles.yml in VS Code (or any text editor). If the file already exists from the init command, replace its contents. If it does not exist, create it.
  • Paste the following configuration:
ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ecommerce_analytics.duckdb

What does this profile do?

The path field tells DuckDB to create a database file called ecommerce_analytics.duckdb in whatever directory you run dbt commands from. DuckDB is an embedded database, so there is no server to start. The file IS the database.

  • Save ~/.dbt/profiles.yml.

Now you will update the project configuration to define how your staging and marts layers will be materialized.

  • Move into the project directory by running:
cd ecommerce_analytics
  • Open dbt_project.yml in VS Code and replace its entire contents with:
name: 'ecommerce_analytics'
version: '1.0.0'

profile: 'ecommerce_analytics'

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

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

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

What does the models config do?

The models block at the bottom tells dbt how to materialize models in each folder. Staging models become views (lightweight SQL shortcuts that don't store data). Marts models become tables (physically stored for fast querying by analysts).

This is a dbt best practice: staging is cheap and ephemeral, marts are the polished consumption layer.

  • Save dbt_project.yml.

Not sure where ~/.dbt/ is?

On macOS, ~ means your home directory (e.g. /Users/yourname). The .dbt folder is hidden by default. You can open it in VS Code by pressing Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows), typing "Open File", and entering the full path ~/.dbt/profiles.yml.

Help me find my profiles.yml file

Verify the connection

Before you run this next command, what do you think will happen? Will dbt successfully connect to DuckDB, or will there be an error?

  • Test that dbt can find your project and connect to DuckDB by running:
dbt debug

You should see dbt check your profile, project, and connection. The final line reads All checks passed! confirming dbt can talk to DuckDB.

Seeing a connection error?

If dbt debug reports a profile error, double-check that the profile value in dbt_project.yml matches the top-level key in ~/.dbt/profiles.yml. Both must be ecommerce_analytics.

If it says the project is not found, confirm your terminal is inside the ecommerce_analytics directory. Run pwd (macOS) or cd (Windows) to check your current location.

Help me fix my dbt debug errors

✔️ Awesome, I've got everything!

Great. Double check you have saved both files.

ⓧ I'd like to double check the full code

Here is your ~/.dbt/profiles.yml:

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

Here is your dbt_project.yml:

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

Your local data stack is ready. dbt knows where to find your project, and DuckDB is waiting for data. Next up, you will load seed data into DuckDB and run your first model.

Load Seed Data and Run Your First Model

Your dbt project is connected and ready. Now you need actual data to transform.

In this step, you will load small CSV datasets directly into DuckDB using dbt seeds, then write a quick model to see how a naive approach falls short. This reveals exactly why a layered architecture matters.

In this step, get ready to:
  • Create four CSV seed files representing a small e-commerce dataset.
  • Load the seeds into DuckDB and confirm they exist.
  • Build a naive model and observe why it needs improvement.
Create the seed CSV files

Seeds are small CSV files that dbt loads directly into your database. They let you version-control reference data alongside your transformation code.

  • In VS Code, open the seeds/ folder inside your ecommerce_analytics project.
  • Create a new file called raw_customers.csv with this content:
customer_id,first_name,last_name,email,created_at
1,John,Smith,john.smith@email.com,2024-01-15
2,Sarah,Johnson,sarah.j@email.com,2024-02-20
3,Michael,Williams,m.williams@email.com,2024-03-10
4,Emily,Brown,emily.b@email.com,2024-04-05
5,David,Jones,d.jones@email.com,2024-05-12
  • Create a new file called raw_products.csv in the same seeds/ folder:
product_id,product_name,category,price,created_at
101,Wireless Mouse,Electronics,29.99,2024-01-01
102,Mechanical Keyboard,Electronics,89.99,2024-01-01
103,USB-C Hub,Electronics,49.99,2024-02-01
104,Monitor Stand,Accessories,39.99,2024-02-01
105,Desk Lamp,Accessories,24.99,2024-03-01
  • Create raw_orders.csv in seeds/:
order_id,customer_id,order_date,status
1001,1,2024-06-01,completed
1002,2,2024-06-05,completed
1003,3,2024-06-10,shipped
1004,1,2024-06-15,completed
1005,4,2024-06-20,pending
1006,99,2024-06-25,completed
  • Create raw_order_items.csv in seeds/:
order_item_id,order_id,product_id,quantity,unit_price
1,1001,101,2,29.99
2,1001,102,1,89.99
3,1002,103,1,49.99
4,1003,101,1,29.99
5,1003,104,2,39.99
6,1004,105,3,24.99
7,1005,102,1,89.99
8,1006,103,2,49.99

What are these files?

Together, these four CSVs represent a small e-commerce dataset. Customers buy products, orders track each purchase, and order items capture the individual line items within each order.

Notice that order 1006 references customer_id 99, which does not exist in the customers file. This is intentional. You will see why later.

  • Save all four files.
  • Confirm the files exist by running this in your terminal:
ls seeds/

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

Don't see all four files?

Make sure you saved each file inside the seeds/ folder, not the project root. The file names must match exactly (lowercase, underscores, .csv extension).

help me find where my seed files ended up

Load seeds into DuckDB

With the CSV files in place, dbt can load them directly into your DuckDB database as tables.

  • Load all four seed files into DuckDB by running:
dbt seed

You should see output showing each seed file loaded successfully, ending with PASS=4 in the summary line.

What does this command do?

The dbt seed command reads every CSV in your seeds/ directory and creates a corresponding table inside DuckDB. Each CSV becomes a queryable table with the same name as the file (without the extension).

  • Verify the data is actually in DuckDB by running this query:
python -c "import duckdb; conn = duckdb.connect('ecommerce_analytics.duckdb'); print(conn.sql('SELECT * FROM raw_customers'))"

You should see a table with 5 customer rows including John Smith, Sarah Johnson, and the others from your CSV.

Seeing a 'table not found' error?

Make sure dbt seed completed without errors first. If it did, check that you are running the Python command from inside the ecommerce_analytics/ directory where the .duckdb file lives.

help me troubleshoot why DuckDB cannot find my seed tables

Create and run a naive model

Now that you have data, try the simplest possible approach: a single model that joins orders with order items to get everything in one place.

  • Create a new file called orders_naive.sql inside the models/ folder with the following SQL:
select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.status,
    oi.order_item_id,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
from {{ ref('raw_orders') }} o
join {{ ref('raw_order_items') }} oi on o.order_id = oi.order_id

What does this model do?

This model joins the raw orders table with raw order items to produce a combined view of every order line. The {{ ref() }} function is how dbt models reference other models or seeds. It tells dbt about the dependency so it can build things in the right order.

The calculated line_total column multiplies quantity by unit price to get the dollar amount for each line item.

  • Save models/orders_naive.sql.

Before you run this, what do you think the output will show? How many models will dbt create?

  • Run the model by executing:
dbt run

You should see 1 model pass, with output showing dbt created a view called orders_naive.

  • Query the result to confirm it works:
python -c "import duckdb; conn = duckdb.connect('ecommerce_analytics.duckdb'); print(conn.sql('SELECT * FROM orders_naive'))"

You should see 8 rows combining order details with line item details, including the calculated line_total column.

Why is this approach a problem?

This single model works, but it jams everything into one place. Every future model that needs order data would have to repeat the same join logic, the same column selections, and the same type casting.

If the raw data format changes, you would need to update every model that touches it. This is the problem that a staging layer solves: you clean and standardize each source table exactly once, then every downstream model references the clean version.

dbt run showing an error?

Check that your file is named orders_naive.sql (not .csv) and lives directly inside models/, not inside a subfolder. Also confirm dbt seed ran successfully earlier, since the model depends on those seed tables.

help me debug why my dbt model is failing to run

✔️ Awesome, I've got everything!

Great. Double check you have saved all files, then move on.

ⓧ I'd like to double check the full code

Here is the complete models/orders_naive.sql file:

select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.status,
    oi.order_item_id,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
from {{ ref('raw_orders') }} o
join {{ ref('raw_order_items') }} oi on o.order_id = oi.order_id

Your seeds/ folder should contain four CSV files: raw_customers.csv, raw_products.csv, raw_orders.csv, and raw_order_items.csv. Scroll up to confirm each one matches the content shown earlier.

You have data flowing and a working model. Next up, you will replace this naive approach with a proper staging layer that cleans each source table exactly once.

Build the Staging Layer

In the last step, you loaded seed data and ran a naive model that joins orders with order items inline. That approach works once, but imagine every downstream model needing the same join and type-casting logic repeated.

Staging models solve this by creating reusable building blocks. Each staging model maps 1:1 to a source table, handling renaming, type-casting, and standardization exactly once.

In this step, get ready to:
  • Install the dbt-utils package for surrogate key generation in later steps.
  • Create four staging models that clean and standardize each source table.
  • Verify the staging layer builds as views and supports selective execution.
Install the dbt-utils package

Before building the staging models, you need dbt-utils, a utility package from dbt Labs. It provides macros like surrogate key generation that you will use in the next step when building dimension tables.

  • Create a file called packages.yml in the root of your ecommerce_analytics folder (the same level as dbt_project.yml) and paste the following:
packages:
  - package: dbt-labs/dbt_utils
    version: 1.4.1

What does this file do?

The packages.yml file declares external dbt packages your project depends on. When you run dbt deps, dbt reads this file and downloads the specified packages into a dbt_packages directory.

  • Save packages.yml.
  • Install the package by running this command:
dbt deps

You should see output confirming the package was installed, including a line like Installed from version 1.4.1.

✔️ Awesome, I've got everything!

Great. Double check you have saved packages.yml and that the dbt_packages folder appeared in your project directory.

ⓧ I'd like to double check the full code

packages:
  - package: dbt-labs/dbt_utils
    version: 1.4.1

Seeing a packages.yml parse error?

  • Make sure packages.yml is at the project root (next to dbt_project.yml), not inside models/ or seeds/.
  • YAML is indentation-sensitive. Use exactly 2 spaces for each level, not tabs.
  • Help me debug this
Create the staging models

Each staging model follows the same pattern: pull from the raw seed using {{ ref() }}, rename columns to a consistent convention, and cast types explicitly. You will create four models, one per source table.

  • Create the models/staging/ directory by running:
mkdir models/staging
  • Create a file called stg_customers.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_customers') }}
)

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

What does this model do?

  • The with source CTE pulls all columns from the raw seed table using {{ ref('raw_customers') }}. This tells dbt about the dependency so it builds models in the right order.
  • The full_name column concatenates first and last names, giving downstream models a ready-to-use display name.
  • The cast(created_at as date) ensures consistent typing. Raw CSVs often load dates as strings, which can cause silent comparison bugs.
  • Save stg_customers.sql.
  • Create a file called stg_products.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_products') }}
)

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

What does this model do?

Same pattern as stg_customers. The key addition is cast(price as decimal(10, 2)) which ensures the price column has consistent precision for arithmetic downstream.

  • Save stg_products.sql.
  • Verify your first two staging models compile by running:
dbt run

You should see 3 models complete successfully: the 2 new staging views plus the existing orders_naive model.

Seeing a compilation error?

  • Check that the file names end in .sql, not .txt or another extension.
  • Confirm both files are inside models/staging/, not in models/ directly.
  • Make sure the ref() argument matches the CSV filename exactly (e.g. raw_customers, not raw_customer).
  • Help me debug my staging models
  • Create a file called stg_orders.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_orders') }}
)

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

What does this model do?

This model passes through the order fields while casting order_date to a proper date type. Notice it does NOT filter out invalid customer_id values.

Staging models stay 1:1 with the source. Filtering happens later in the mart layer.

  • Save stg_orders.sql.
  • Create a file called stg_order_items.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_order_items') }}
)

select
    order_item_id,
    order_id,
    product_id,
    quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    quantity * cast(unit_price as decimal(10, 2)) as line_total
from source

What does this model do?

  • The unit_price cast to decimal(10, 2) ensures consistent precision for all money calculations.
  • The line_total computed column (quantity * unit_price) means every downstream model gets pre-calculated totals without repeating the multiplication.
  • Save stg_order_items.sql.

Why are staging models materialized as views?

Your dbt_project.yml already configures models under staging/ to materialize as views. Views avoid storing duplicate data.

Staging models are building blocks, not final artifacts. They compile to SQL that runs on-the-fly whenever a downstream model references them.

✔️ Awesome, I've got everything!

Great. Confirm you have all four files saved in models/staging/.

ⓧ I'd like to double check the full code

Here are all four staging models for comparison:

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

select
    customer_id,
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name,
    email,
    cast(created_at as date) as created_at
from source
with source as (
    select * from {{ ref('raw_products') }}
)

select
    product_id,
    product_name,
    category,
    cast(price as decimal(10, 2)) as price,
    cast(created_at as date) as created_at
from source
with source as (
    select * from {{ ref('raw_orders') }}
)

select
    order_id,
    customer_id,
    cast(order_date as date) as order_date,
    status
from source
with source as (
    select * from {{ ref('raw_order_items') }}
)

select
    order_item_id,
    order_id,
    product_id,
    quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    quantity * cast(unit_price as decimal(10, 2)) as line_total
from source
Delete the naive model and verify the staging layer

The naive model from Step 2 duplicated logic that your staging models now handle cleanly. Time to remove it and confirm your staging layer builds on its own.

  • Delete the naive model by running:
rm models/orders_naive.sql
  • Confirm the file is gone by running:
ls models/

You should see only the staging directory listed (no orders_naive.sql).

Before you run dbt run, how many models do you expect dbt to build now?

  • Run the full project build by running:
dbt run

You should see 4 models complete successfully, all as views. The naive model is gone and your staging layer is the only thing running.

  • Confirm selective execution works by running a single model:
dbt run --select stg_customers

You should see only 1 model built. Selective execution lets you iterate on a single model without rebuilding your entire project.

Why does selective execution matter?

In production projects with hundreds of models, rebuilding everything takes minutes or hours. The --select flag lets you rebuild only what you changed, making development fast even at scale.

Still seeing orders_naive in dbt output?

  • Confirm the file is actually deleted. Run ls models/ and check that only staging appears.
  • If you see a stale orders_naive table in DuckDB, that is expected. The table from the previous run still exists in the database file, but dbt will no longer manage or rebuild it.
  • Help me understand my dbt project state

Your staging layer is locked in. You have four clean, reusable building blocks that every downstream model can reference without repeating any cleaning logic. Next up, you will assemble these atoms into a star schema with dimension and fact tables.

Build the Star Schema

Your staging layer is in place. You now have four clean, standardized views that handle all the renaming and type-casting in one place.

The next challenge is assembling these building blocks into a structure optimized for analytics queries. Dimension tables provide the "who" and "what" context, while a fact table records measurable business events. Together they form a star schema that analysts can query with simple joins.

In this step, get ready to:
  • Create dimension tables with surrogate keys for customers and products.
  • Build a fact table that captures order line items with measurable values.
  • Query the star schema to answer a business question with simple joins.
Create the dimension models

Dimension tables describe the "nouns" of your business. Each one maps to a staging model, adds a surrogate key, and carries all the descriptive attributes an analyst might filter or group by.

  • Create the models/marts/ directory by running this command:
mkdir models/marts
  • Create a new file models/marts/dim_customer.sql and paste the following code:
with customers as (
    select * from {{ ref('stg_customers') }}
)

select
    -- Generate a hashed surrogate key from customer_id
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_key,
    customer_id,
    first_name,
    last_name,
    full_name,
    email,
    created_at
from customers

What does this code do?

This model pulls all customer attributes from your staging layer and adds one new column at the top.

  • generate_surrogate_key(['customer_id']) hashes the natural key (customer_id) into a consistent MD5 string. This surrogate key is what the fact table will reference instead of the raw integer.
  • ref('stg_customers') tells dbt this model depends on the staging layer. dbt uses these references to build its DAG and run models in the correct order.
  • Save dim_customer.sql.

✔️ Awesome, I've got everything!

Great. Make sure you saved the file before moving on.

ⓧ I'd like to double check the full code

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

select
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_key,
    customer_id,
    first_name,
    last_name,
    full_name,
    email,
    created_at
from customers
  • Create a new file models/marts/dim_product.sql and paste the following code:
with products as (
    select * from {{ ref('stg_products') }}
)

select
    -- Generate a hashed surrogate key from product_id
    {{ dbt_utils.generate_surrogate_key(['product_id']) }} as product_key,
    product_id,
    product_name,
    category,
    price,
    created_at
from products

What does this code do?

Same pattern as the customer dimension. This model adds a product_key surrogate key and carries all the product attributes that analysts use to slice their data (name, category, price).

  • Save dim_product.sql.

✔️ Awesome, I've got everything!

Great. Make sure you saved the file before moving on.

ⓧ I'd like to double check the full code

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

select
    {{ dbt_utils.generate_surrogate_key(['product_id']) }} as product_key,
    product_id,
    product_name,
    category,
    price,
    created_at
from products
  • Run only the two dimension models to confirm they materialize as tables:
dbt run --select "dim_customer dim_product"

You should see both models complete with OK status. dbt creates them as tables because your dbt_project.yml already configures the marts folder with +materialized: table.

Seeing a compilation error?

  • Make sure you saved both files before running the command.
  • Check that your files are inside models/marts/ and not models/ directly.
  • Confirm the Jinja syntax uses double curly braces: {{ dbt_utils.generate_surrogate_key(...) }}.

Help me debug my dbt dimension model compilation error.

Create the fact table

The fact table records measurable business events. In a Kimball star schema, the grain (one row = one what?) is the most important design decision. Your fact table will have one row per order line item.

  • Create a new file models/marts/fct_orders.sql and start by pasting the source CTEs:
with orders as (
    select * from {{ ref('stg_orders') }}
),

order_items as (
    select * from {{ ref('stg_order_items') }}
),

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

What do these CTEs do?

  • orders and order_items pull in the two staging models you need to join for the fact table.
  • customers pulls the list of valid customer IDs. You will use this to filter out any orders that reference a customer not in your system.
  • Directly below the code you just pasted (after the trailing comma on the customers CTE), add the final transformation and select:
final as (
    select
        -- Surrogate keys linking to dimension tables
        {{ dbt_utils.generate_surrogate_key(['order_items.order_item_id']) }} as order_item_key,
        {{ dbt_utils.generate_surrogate_key(['orders.customer_id']) }} as customer_key,
        {{ dbt_utils.generate_surrogate_key(['order_items.product_id']) }} as product_key,
        orders.order_id,
        order_items.order_item_id,
        orders.customer_id,
        order_items.product_id,
        orders.order_date,
        orders.status,
        -- Measures: the numeric values analysts aggregate
        order_items.quantity,
        order_items.unit_price,
        order_items.line_total
    from order_items
    inner join orders on order_items.order_id = orders.order_id
    where orders.customer_id in (select customer_id from customers)
)

select * from final

What does the final CTE do?

  • generate_surrogate_key creates hashed keys that match the dimension tables. When customer_key here uses orders.customer_id, it produces the same hash as dim_customer.customer_key. This is how star schema joins work.
  • The where clause filters out any orders whose customer ID does not exist in the customers table. This prevents orphan records from polluting your analytics.
  • The measures (quantity, unit_price, line_total) are the numeric values analysts will aggregate (SUM, AVG, COUNT) in their queries.
  • Save fct_orders.sql.

✔️ Awesome, I've got everything!

Great. Confirm you saved the file with both code chunks combined into one continuous SQL statement.

ⓧ I'd like to double check the full code

Your fct_orders.sql should contain both chunks in sequence. Scroll up and confirm you have:

  • The with keyword opening three CTEs (orders, order_items, customers).
  • A final CTE with three surrogate keys, column selections, a JOIN, and a WHERE filter.
  • select * from final as the last line.

Seeing a syntax error?

  • Make sure there is a blank line between the customers CTE (ending with ),) and final as (. The comma after customers is required because final is another CTE in the same with block.
  • Check you do not have a trailing comma after order_items.line_total (the last column before from).

Help me fix this dbt SQL syntax error.

Run and query the star schema

All three mart models are ready. Before you run them, think about what you expect to happen.

You have 4 staging views plus 3 new mart models. How many models total do you think dbt will process?

  • Run all models by executing:
dbt run

You should see 7 models processed. The 4 staging models appear as view and the 3 mart models appear as table, all with OK status.

Why does dbt re-run the staging views?

dbt always processes the full DAG when you run without a --select flag. Since the mart tables depend on staging views, dbt recreates the views first to ensure everything is consistent.

  • Query the star schema to answer "What are total sales by product category?" by running:
python -c "import duckdb; conn = duckdb.connect('ecommerce_analytics.duckdb'); print(conn.sql('SELECT dp.category, SUM(f.line_total) as total_sales FROM fct_orders f JOIN dim_product dp ON f.product_key = dp.product_key GROUP BY dp.category ORDER BY total_sales DESC').fetchdf())"

You should see two rows: Electronics with total sales of 319.94 and Accessories with 154.95.

What just happened?

You joined the fact table to a dimension table using the surrogate key (product_key). The analyst did not need to know about raw IDs, type casts, or data cleaning. They wrote a simple GROUP BY on category and got an instant answer.

This is the payoff of the star schema pattern. Clean dimensions plus a well-grained fact table make analytics queries trivial.

Getting a 'Table not found' error?

  • Confirm dbt run completed without errors before querying.
  • Check you are running the Python command from inside the ecommerce_analytics/ directory (where the .duckdb file lives).
  • Verify the table names in your query match exactly: fct_orders and dim_product.

Help me query my dbt star schema in DuckDB.

Your star schema is live. You can answer business questions with a single join. Next up, you will add automated tests that act as data quality contracts to protect this schema from bad data.

Add Tests and Catch a Data Quality Issue

You have a working star schema with dimension and fact tables. But how do you know the data flowing through it is actually correct?

Without automated tests, bad data silently flows into dashboards and reports. dbt tests act as data quality contracts that validate your models every time you run them. In this step, you will add tests and discover they catch a real referential integrity violation hiding in your seed data.

In this step, get ready to:
  • Define data tests for staging and mart models.
  • Run tests and see a relationships test fail on a bad data record.
  • Fix the referential integrity violation and confirm all tests pass.
Define data tests for your models

dbt ships with built-in generic tests: unique, not_null, accepted_values, and relationships. You define them in YAML schema files that live alongside your models.

  • Create a new file called _stg_models.yml inside your models/staging/ directory, and paste the following code:
models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
  - name: stg_products
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
  - name: stg_order_items
    columns:
      - name: order_item_id
        data_tests:
          - unique
          - not_null

What does this file do?

Each model entry declares which column is the primary key and what assertions dbt should check. The unique test ensures no duplicates exist. The not_null test ensures every row has a value in that column.

If either test fails, dbt tells you exactly which rows violated the rule. This catches data quality issues before they reach downstream models.

  • Save _stg_models.yml.

Now define tests for your mart models, including referential integrity checks between the fact and dimension tables.

  • Create a new file called _marts_models.yml inside your models/marts/ directory, and paste the following code:
models:
  - name: dim_customer
    description: "Customer dimension table with surrogate key"
    columns:
      - name: customer_key
        data_tests:
          - unique
          - not_null
  - name: dim_product
    description: "Product dimension table with surrogate key"
    columns:
      - name: product_key
        data_tests:
          - unique
          - not_null

What does this cover so far?

These entries test the dimension table surrogate keys. Every customer_key and product_key must be unique and non-null. If the generate_surrogate_key macro produced a collision or a null, these tests would catch it.

  • Add the following code directly below what you just pasted in the same _marts_models.yml file:
  - name: fct_orders
    description: "Order line items fact table at the grain of one row per order item"
    columns:
      - name: order_item_key
        data_tests:
          - unique
          - not_null
      - name: customer_key
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: product_key
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_product')
              field: product_key

What are relationships tests?

The relationships test enforces referential integrity. It checks that every value in fct_orders.customer_key exists as a matching row in dim_customer.customer_key. If any fact row points to a dimension record that does not exist, the test fails.

This is the same concept as a foreign key constraint in a traditional database. The difference is that dbt tests are enforced at build time, not by the database engine.

  • Save _marts_models.yml.

Getting a YAML parsing error?

YAML is sensitive to indentation. Make sure you are using spaces (not tabs) and that each nested level is indented by exactly 2 spaces. The fct_orders entry must align with the dim_customer entry above it.

help me fix my YAML indentation in _marts_models.yml

Run tests and hit the data quality issue

Your tests are defined. Time to run them against your DuckDB database and see what they find.

Before you run this, do you think all tests will pass? Think about what data is currently sitting in your fct_orders table. Hint: look at raw_orders.csv and count the customer IDs.

  • Rebuild your models and run all tests together by running this command:
dbt build

You will see most tests pass, but one test fails. The output shows a FAIL for the relationships test on customer_key. The summary line reports 1 failure.

Why did this test fail?

Look at your seed data in raw_orders.csv. Order 1006 references customer_id 99. But raw_customers.csv only contains customer IDs 1 through 5. Customer 99 does not exist.

When fct_orders was built, it generated a customer_key by hashing customer_id 99. That hashed key has no matching row in dim_customer. The relationships test caught this orphaned reference.

In production, this kind of data silently corrupts dashboards. Revenue gets attributed to a nonexistent customer. The test just saved you from that.

Fix the referential integrity violation

The fix is to filter out orders with invalid customer IDs in your fact table. Staging best practice is to keep models 1:1 with the source. The fact table is the appropriate place for this cross-source validation.

  • In models/marts/fct_orders.sql, find the end of the order_items CTE. It currently looks like this:
order_items as (
    select * from {{ ref('stg_order_items') }}
),

final as (
  • Add a new customers CTE between order_items and final, so the section now looks like this:
order_items as (
    select * from {{ ref('stg_order_items') }}
),

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

final as (
  • Now scroll down to the bottom of the final CTE. Find this line:
    from order_items
    inner join orders on order_items.order_id = orders.order_id
)
  • Add a WHERE clause between the join and the closing parenthesis, so the section now looks like this:
    from order_items
    inner join orders on order_items.order_id = orders.order_id
    where orders.customer_id in (select customer_id from customers)
)

What did these changes do?

The customers CTE selects all valid customer IDs from stg_customers. The WHERE clause filters the join so only orders with a valid customer_id make it into the fact table.

Order 1006 (customer_id 99) is now excluded because 99 is not in the customers CTE. Every remaining customer_key in fct_orders will have a matching row in dim_customer.

  • Save fct_orders.sql.

Now rebuild your models and re-run all tests together. Do you think the relationships test will pass this time?

  • Rebuild and test everything by running this command:
dbt build

This time you should see all tests pass. The summary shows zero failures across all your staging and mart tests.

Why dbt build instead of dbt test?

The dbt build command runs models, tests, seeds, and snapshots together in DAG order. It rebuilds fct_orders with the new WHERE clause first, then runs its tests against the freshly materialized table.

If you had only run dbt test (without rebuilding), it would test the old table that still contains the bad row. Using dbt build ensures the model is up to date before tests run.

Still seeing a test failure?

Double-check that the WHERE clause in fct_orders.sql references the customers CTE correctly. The line should read where orders.customer_id in (select customer_id from customers).

Also confirm you saved the file before running dbt build. Unsaved changes are not picked up by dbt.

help me debug my failing dbt relationships test on fct_orders

✔️ Awesome, I've got everything!

Great. Make sure you have saved both YAML files and fct_orders.sql.

ⓧ I'd like to double check the full code

Here is the complete _stg_models.yml:

models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
  - name: stg_products
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
  - name: stg_order_items
    columns:
      - name: order_item_id
        data_tests:
          - unique
          - not_null

For _marts_models.yml and fct_orders.sql, scroll up and confirm your file contains all the chunks you pasted above, in order. Your _marts_models.yml should have three model entries (dim_customer, dim_product, fct_orders). Your fct_orders.sql should have four CTEs (orders, order_items, customers, final) with the WHERE clause at the bottom of the final CTE.

You now have a data quality contract protecting your star schema. Every time you run dbt build, these tests automatically verify that no orphaned keys exist between your fact and dimension tables. Next up, you will extend this schema with a date dimension and build a One Big Table for BI consumption.

Secret mission

Add a Date Dimension and Build a One Big Table

Real star schemas always include a date dimension for time-based analysis. In this bonus challenge, you will generate a date spine using DuckDB's generate_series function, link it to your fact table, and build a One Big Table (OBT) that pre-joins everything for BI tools.

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 on DuckDB, so there are no ongoing cloud costs.

Resources you used:

  • Python virtual environment (dbt-env).
  • dbt project folder (ecommerce_analytics) containing all models, seeds, and your DuckDB database file.
  • dbt profile configuration (~/.dbt/profiles.yml).

Keep everything running

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

  • Your virtual environment stays activated and ready for dbt build whenever you return.
  • Your DuckDB database file persists inside the project folder with all your models materialized.
  • The dbt-duckdb package lives inside your virtual environment and does not affect anything else on your system.

Pause - I'll come back to this later

Deactivate the virtual environment to free up your terminal session. Your project files and database remain on disk for later.

  • Deactivate the virtual environment by running:
deactivate
  • When you come back, reactivate it with dbt-env\Scripts\activate (Windows) or source dbt-env/bin/activate (macOS/Linux), then cd ecommerce_analytics to resume.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes the virtual environment, the project folder (including the DuckDB database), and the dbt profile.

  • Deactivate the virtual environment first:
deactivate
  • Navigate up to the parent directory:
cd ~/Desktop
  • Delete the project folder and virtual environment.

On macOS/Linux:

rm -rf ecommerce_analytics
rm -rf dbt-env

On Windows:

rmdir /s /q ecommerce_analytics
rmdir /s /q dbt-env
  • Remove the dbt profile entry from ~/.dbt/profiles.yml. Open the file and delete the ecommerce_analytics block. If this was your only project, you can delete the entire file.

On macOS/Linux:

rm ~/.dbt/profiles.yml

On Windows:

del %USERPROFILE%\.dbt\profiles.yml
  • Confirm everything is removed by running ls on your Desktop. You should no longer see ecommerce_analytics or dbt-env.

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-ready analytics pipeline that transforms raw CSV data into a tested, documented star schema using dbt and DuckDB.

You've learned how to:

  • Built a dbt project from scratch with the industry-standard staging/marts layered architecture, running the full workflow from dbt seed to dbt build.
  • Transformed raw CSV data into a Kimball star schema with surrogate keys, dimension tables, and a fact table optimized for analytics queries.
  • Used dbt tests as data quality contracts to catch a real referential integrity violation in your data and fixed it, proving that automated testing protects your star schema from silent corruption.
  • Secret Mission: Generated a date dimension using DuckDB's date functions and built a One Big Table that pre-joins everything for BI consumption.

Ready to quiz yourself?