E-Commerce Dimensional Model with dbt

Introduction

30 Second Summary

Every time someone buys from an online store, their clicks, orders, and payments scatter across dozens of separate database tables. Analysts trying to answer simple questions like "who are our best customers?" end up writing complex queries that join five tables together just to get one number.

In this project, you will build a dimensional data warehouse using dbt and BigQuery that transforms messy raw e-commerce data into clean, analyst-ready tables. You will design a star schema, automate data quality tests, and generate documentation with a visual lineage graph.

What You'll Build

You run a single query against your fact table to answer "what's our revenue by product category this month?" and trust the answer because automated tests guard every model in the pipeline.

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

• A star schema in BigQuery where answering "who are our top customers by revenue?" takes one line of SQL instead of a four-table JOIN nightmare.
• Automated data quality tests that catch integrity issues (like null customer IDs) before they silently corrupt downstream dashboards. You will see a test fail on a real data issue and fix it at the source.
• A documentation site with a visual lineage graph where you can trace exactly how raw CSV data transforms through staging into business-ready tables.
• Secret Mission: Convert your fact table to an incremental materialization so it only processes new orders on each run.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOINs, GROUP BY, subqueries), command line basics, and Git basics. No prior experience with dbt or data warehousing 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 growth as an analytics engineer.

Set Up Your Toolchain

To transform raw data into a dimensional model, you need three tools talking to each other: a cloud data warehouse (BigQuery), the gcloud CLI for authentication, and dbt to run transformations locally.

In this step, you will wire all three together so dbt can reach BigQuery from your terminal. Once this connection works, every step after this is pure SQL and modeling logic.

In this step, get ready to:

• Create a Google Cloud account and project with the BigQuery API enabled.
• Install and configure the gcloud CLI for authentication.
• Install dbt-bigquery in a Python virtual environment and confirm the version.

Create a Google Cloud account and project

Google Cloud offers a free tier for BigQuery that includes 1 TiB of queries per month and 10 GiB of storage. That is far more than this project needs, so you will not be charged.

• Open https://console.cloud.google.com in your browser.
• If you do not already have a Google Cloud account, click Get started for free and follow the prompts to create one. No billing account or credit card is required for the BigQuery sandbox.
• Once signed in, click the project dropdown at the top of the page.
• Click New Project.
• Enter a project name (for example, dbt-ecommerce) and click Create.
• Wait a few seconds, then select your new project from the project dropdown so it becomes the active project.

What about the BigQuery API?

For new projects, the BigQuery API is automatically enabled. You do not need to turn it on manually. If you reuse an existing project, navigate to APIs & Services > Library, search for BigQuery API, and click Enable.

What is the BigQuery sandbox?

The BigQuery sandbox gives you the same free tier limits (1 TiB queries/month, 10 GiB storage) without requiring a credit card. Tables expire after 60 days and streaming inserts are disabled, but neither affects this project.

You should now see your new project name in the top navigation bar of the Google Cloud Console.

Install and configure the gcloud CLI

The gcloud CLI lets you authenticate from your terminal. dbt uses these credentials to connect to BigQuery without needing a service account key file.

Windows

• Download the Google Cloud CLI installer from https://cloud.google.com/sdk/docs/install.
• Run GoogleCloudSDKInstaller.exe and follow the prompts.
• When the installer asks whether to start the Google Cloud CLI shell, uncheck that option. You will initialize it yourself in the next step.
• After the installer finishes, close and reopen your terminal so the gcloud command is available on your PATH.

gcloud not recognized after install?

If your terminal says the command is not recognized, make sure you closed and reopened the terminal window after installation. The installer adds gcloud to your PATH, but existing terminals will not pick up the change.

help me troubleshoot why gcloud is not recognized in my terminal after installing the Google Cloud SDK on Windows

macOS

• Install the gcloud CLI using Homebrew by running this command:

brew update && brew install --cask gcloud-cli

• After the install finishes, close and reopen your terminal so the gcloud command is available on your PATH.

gcloud not found after install?

If your shell cannot find the command, restart your terminal. If that does not help, run brew info gcloud-cli and check whether the caveats section mentions a shell config line you need to add.

help me troubleshoot why gcloud is not found in my terminal after installing via Homebrew on macOS

• Verify the install by running:

gcloud --version

✔️ I see a version number

The gcloud CLI is ready. Continue to the next command below.

ⓧ Command not found

Your terminal cannot find the gcloud binary. Close your terminal completely and reopen it. If the problem persists, re-run the installer (Windows) or brew install --cask gcloud-cli (macOS).

help me fix gcloud command not found after installing the Google Cloud SDK

• Initialize the CLI and authenticate with your Google account by running:

gcloud init

What happens during gcloud init?

This command opens your browser to sign in with your Google account. After signing in, it asks you to select a project. Choose the project you just created (for example dbt-ecommerce). You can skip the region/zone prompt by pressing Enter.

• Set up Application Default Credentials (the credentials dbt uses to talk to BigQuery) by running:

gcloud auth application-default login

Your browser opens again for a short OAuth flow. After you approve, you should see a success message in your terminal confirming that credentials have been saved.

Why two logins?

The gcloud init login authenticates the CLI itself for admin commands. The gcloud auth application-default login command creates a separate credential file that libraries (like dbt) read automatically. Both are needed.

Create a virtual environment and install dbt

Installing dbt inside a virtual environment keeps its dependencies isolated from your system Python. This prevents version conflicts with other projects.

• Create a new virtual environment called dbt-env by running:

python -m venv dbt-env

• Activate the virtual environment:

Windows

dbt-env\Scripts\activate

macOS

source dbt-env/bin/activate

You should see (dbt-env) appear at the beginning of your terminal prompt. This confirms you are inside the virtual environment.

• Upgrade pip, wheel, and setuptools before installing dbt (this avoids common build issues):

python -m pip install --upgrade pip wheel setuptools

• Install dbt-bigquery by running:

python -m pip install dbt-bigquery

What does this install?

Installing dbt-bigquery automatically installs dbt-core as a dependency. You do not need to install them separately.

Before you run the next command, what version of dbt-core do you expect to see?

• Confirm dbt is installed correctly by running:

dbt --version

You should see output that includes dbt-core 1.12.x and dbt-bigquery 1.12.x. This confirms your toolchain is installed and ready.

Seeing command not found for dbt?

Make sure your virtual environment is active. Check for (dbt-env) at the start of your prompt. If it is missing, re-run the activation command above.

If you see a version lower than 1.12, run python -m pip install --upgrade dbt-bigquery to get the latest.

help me troubleshoot dbt --version not working after installing dbt-bigquery in a virtual environment

Your toolchain is wired up. dbt is installed, authenticated to Google Cloud, and ready to talk to BigQuery. Next up, you will load raw data into BigQuery and experience firsthand why it needs transformation.

Load Raw Data and Feel the Pain

Your dbt toolchain is installed and ready. Before building any transformation layer, you need data to transform and a reason to transform it.

In this step, you'll load raw e-commerce data into BigQuery and try to answer a business question using only raw tables. The experience will show exactly why unmodeled data is painful to work with.

In this step, get ready to:

• Initialize a dbt project and confirm the BigQuery connection.
• Load sample e-commerce data into BigQuery using dbt seeds.
• Attempt a business query against the raw tables and see the pain firsthand.

Initialize your dbt project and connect to BigQuery

A dbt project is a folder that holds all your models, tests, seeds, and configuration. You need one before you can run any transformations.

• Move to your Desktop by running this command:

cd ~/Desktop

• Create your dbt project by running this command:

dbt init ecommerce_analytics

What will dbt ask you?

The dbt init command launches an interactive wizard. It will ask you to select a database adapter. Choose the number for bigquery.

Next it asks for your authentication method. Select oauth. Then enter your GCP project ID, dbt_dev for the dataset, 4 for threads, a timeout value (the default is fine), and US for the location.

You should see a message confirming the project was created with sample files inside.

• Move into your new project folder by running:

cd ecommerce_analytics

• Delete the example models folder that dbt generated (you will write your own models from scratch) by running:

rm -rf models/example

• Verify the project structure by running:

ls

You should see folders like models, seeds, tests, and the dbt_project.yml file. The models/example folder should be gone.

Now confirm that dbt can talk to BigQuery.

• Open the profiles.yml file that dbt init created. On Windows it lives at C:\Users\USERNAME\.dbt\profiles.yml. On macOS/Linux it lives at ~/.dbt/profiles.yml.
• Confirm the file matches this configuration (update if needed):

ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: [[GCP_PROJECT_ID="your-gcp-project-id"]]
      dataset: dbt_dev
      threads: 4
      location: US

What does this configuration do?

• method: oauth tells dbt to use the credentials from gcloud auth application-default login (which you set up in Step 1).
• dataset: dbt_dev is the BigQuery dataset where dbt will create your models.
• location: US sets the data region for your BigQuery resources.

• Save profiles.yml and test the connection by running:

dbt debug

You should see a list of checks followed by All checks passed! at the bottom.

Seeing a connection error?

• Make sure your GCP project ID in profiles.yml exactly matches your project ID in the Google Cloud console (not the project name).
• Confirm you ran gcloud auth application-default login in Step 1. If unsure, run it again now.
• Check that the BigQuery API is enabled in your GCP project.

Why is dbt debug failing for me?

Create seed CSV files and load them into BigQuery

dbt seeds are CSV files that dbt loads directly into your data warehouse. They are perfect for small reference datasets or sample data you want to transform.

• In VS Code, open the ecommerce_analytics folder (the project you just created on your Desktop).
• Inside the seeds folder, create a new file called raw_customers.csv and paste the following:

id,first_name,last_name,email,created_at
1,Michael,Perez,michael.perez@example.com,2023-01-15
2,Shawn,Meyers,shawn.meyers@example.com,2023-01-20
3,Kathleen,Connor,kathleen.connor@example.com,2023-02-01
4,Jimmy,Wilkins,jimmy.wilkins@example.com,2023-02-12
5,Katherine,Hoffman,katherine.hoffman@example.com,2023-03-05
6,Sarah,Rodriguez,sarah.rodriguez@example.com,2023-03-19
7,Martin,Williams,martin.williams@example.com,2023-04-02
8,Frank,Miller,frank.miller@example.com,2023-04-18
9,Janet,Henderson,janet.henderson@example.com,2023-05-01
10,Amanda,Gonzalez,amanda.gonzalez@example.com,2023-05-15

• Create another file in the seeds folder called raw_orders.csv and paste:

id,customer_id,order_date,status,total_amount
1,1,2023-03-01,completed,52.00
2,2,2023-03-02,completed,34.50
3,3,2023-03-05,completed,89.99
4,1,2023-03-10,completed,45.00
5,4,2023-03-12,returned,29.99
6,5,2023-03-15,completed,120.00
7,2,2023-03-18,completed,67.50
8,6,2023-03-20,completed,42.00
9,7,2023-03-22,shipped,95.00
10,3,2023-03-25,completed,55.50
11,,2023-03-28,pending,0.00
12,8,2023-04-01,completed,78.00
13,9,2023-04-05,completed,110.00
14,1,2023-04-08,completed,63.00
15,10,2023-04-12,completed,44.99

Notice anything odd?

Look at row 11. The customer_id field is empty. This is an incomplete order record with no customer attached. In real data, issues like this are common and will cause problems downstream if not handled.

• Confirm your first two seed files exist by running this in your terminal:

ls seeds/

You should see raw_customers.csv and raw_orders.csv listed.

• Create raw_products.csv in the seeds folder and paste:

id,name,category,price,created_at
1,Wireless Headphones,Electronics,52.00,2023-01-01
2,Cotton T-Shirt,Clothing,34.50,2023-01-01
3,Running Shoes,Footwear,89.99,2023-01-01
4,Yoga Mat,Sports,45.00,2023-01-01
5,Novel - The Great Adventure,Books,29.99,2023-01-01
6,Bluetooth Speaker,Electronics,120.00,2023-01-01
7,Denim Jacket,Clothing,67.50,2023-01-01
8,Coffee Mug Set,Home,42.00,2023-01-01
9,Fitness Tracker,Electronics,95.00,2023-01-01
10,Backpack,Accessories,55.50,2023-01-01

• Create raw_payments.csv in the seeds folder and paste:

id,order_id,payment_method,amount,created_at
1,1,credit_card,52.00,2023-03-01
2,2,credit_card,34.50,2023-03-02
3,3,bank_transfer,89.99,2023-03-05
4,4,credit_card,45.00,2023-03-10
5,5,credit_card,29.99,2023-03-12
6,6,bank_transfer,120.00,2023-03-15
7,7,credit_card,67.50,2023-03-18
8,8,gift_card,42.00,2023-03-20
9,9,credit_card,95.00,2023-03-22
10,10,bank_transfer,55.50,2023-03-25
11,11,credit_card,0.00,2023-03-28
12,12,credit_card,78.00,2023-04-01
13,13,bank_transfer,110.00,2023-04-05
14,14,credit_card,63.00,2023-04-08
15,15,gift_card,44.99,2023-04-12

• Confirm all four seed files are in place by running:

ls seeds/

You should see raw_customers.csv, raw_orders.csv, raw_payments.csv, and raw_products.csv listed.

Now configure dbt to place seeds in a separate schema so raw data stays organized.

• 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"

seeds:
  ecommerce_analytics:
    +schema: raw

What does the seed schema config do?

The +schema: raw setting tells dbt to place your seed tables in a dataset called dbt_dev_raw (BigQuery appends the custom schema to your target dataset name). This mirrors production patterns where raw data lives in a separate schema from transformed models.

• Save dbt_project.yml.
• Before you run the seed command, what do you think will happen? Will dbt create tables in BigQuery from your CSV files?
• Load your seed data into BigQuery by running:

dbt seed

You should see output ending with Completed successfully and Done. PASS=4 ERROR=0 SKIP=0 TOTAL=4.

Seed command failing?

• Make sure your CSV files have no trailing spaces or blank lines at the end.
• Check that you saved dbt_project.yml before running the command.
• If you see a permissions error, confirm the BigQuery API is enabled and your OAuth credentials are valid.

Why is dbt seed failing for me?

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files.

ⓧ I'd like to double check the full code

Your dbt_project.yml should look exactly like this:

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"

seeds:
  ecommerce_analytics:
    +schema: raw

Your profiles.yml should look like this (with your own GCP project ID):

ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: your-gcp-project-id-here
      dataset: dbt_dev
      threads: 4
      location: US

Scroll up and confirm each of the 4 CSV files in your seeds/ folder matches the code blocks above (raw_customers.csv, raw_orders.csv, raw_products.csv, raw_payments.csv).

Query the raw data and feel the pain

Your data is now in BigQuery. Time to try answering a real business question with it.

• Open the BigQuery console in your browser at console.cloud.google.com/bigquery.
• Make sure your GCP project is selected in the top navigation bar.
• In the query editor, paste this SQL to answer "who are our top 3 customers by total spend?":

SELECT
    c.id,
    c.first_name,
    c.last_name,
    SUM(p.amount) as total_spend
FROM dbt_dev_raw.raw_customers c
JOIN dbt_dev_raw.raw_orders o ON c.id = o.customer_id
JOIN dbt_dev_raw.raw_payments p ON o.id = p.order_id
WHERE o.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spend DESC
LIMIT 3

• Click Run to execute the query.

You should see three rows returned with customer names and their total spend amounts.

What makes this query painful?

You needed a 3-table JOIN (customers, orders, payments) just to answer one business question. Column names are generic (id instead of customer_id), so you have to alias everything to keep track of which id belongs to which table.

You also had to manually filter for status = 'completed' to exclude returned orders from the revenue total. There is no pre-applied business logic telling you which orders count as revenue.

And remember row 11 in raw_orders with the empty customer_id? That dirty data lurks in your tables. Without a data quality layer, it can silently corrupt downstream analysis.

Why dimensional modeling solves this

A dimensional model (star schema) pre-computes these relationships. Instead of writing a 3-table JOIN every time, you query a single fact table for events and JOIN to dimension tables for context.

In the next steps, you will build exactly this. The question "top customers by lifetime value" will become a single SELECT with zero JOINs.

Your raw data is loaded and you have felt the pain of querying unmodeled tables. Next up, you will build a staging layer that cleans and renames this messy data into something predictable.

Build the Staging Layer

In the previous step, you loaded raw data into BigQuery and tried to answer a business question with it. That multi-JOIN query showed exactly why raw data is painful to work with: cryptic column names, inconsistent types, and no business logic applied.

Staging models are your first defense. They sit between your raw data and your business logic, renaming columns to business-friendly names and casting types consistently. Think of them as a clean contract: every model downstream can trust that staging data is well-named and well-typed.

In this step, get ready to:

• Write staging models that rename and type-cast your raw seed data.
• Configure staging models to materialize as views in BigQuery.
• Run your dbt project and verify the staging views appear in BigQuery.

Create the staging directory and write the customers model

Every dbt project organizes models into folders that reflect their purpose. The models/staging/ folder holds models that clean raw data without applying business logic.

• Create the staging directory by running this command in your terminal:

mkdir models/staging

• Create a new file called stg_customers.sql inside models/staging/ and paste the following code:

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

renamed as (
    select
        id as customer_id,
        first_name,
        last_name,
        email,
        cast(created_at as date) as created_at
    from source
)

select * from renamed

What does this code do?

This model uses a CTE (Common Table Expression) pattern that you will repeat for every staging model:

• The source CTE pulls all rows from the raw seed table using {{ ref('raw_customers') }}. The ref() function tells dbt how your models connect to each other.
• The renamed CTE selects columns with business-friendly names (id becomes customer_id) and casts created_at to a proper DATE type.
• The final select * from renamed outputs the cleaned result. This structure keeps each transformation step readable and testable.

• Save stg_customers.sql.
• Run your first staging model by running this command:

dbt run

You should see output showing 1 model created successfully, with a line like OK created sql view model confirming the view was built in BigQuery.

Seeing a compilation error?

• Check that your file is saved inside models/staging/ and not directly in models/. The folder structure matters.
• Make sure {{ ref('raw_customers') }} matches the seed file name exactly (without the .csv extension).
• Confirm your virtual environment is still activated. You should see (dbt-env) at the start of your terminal prompt.

Help me debug my dbt compilation error

Write the orders, products, and payments staging models

Now that you have the CTE pattern down, you will apply it to the remaining three raw tables. Each model follows the same structure: pull from the seed, rename columns, and cast types.

• Create a new file called stg_orders.sql inside models/staging/ and paste the following code:

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

renamed as (
    select
        id as order_id,
        customer_id,
        cast(order_date as date) as order_date,
        status as order_status,
        cast(total_amount as numeric) as order_total
    from source
)

select * from renamed

What does this code do?

This staging model renames id to order_id, renames status to order_status, and casts total_amount to a NUMERIC type for consistent arithmetic.

Notice we are not filtering out any rows here. The staging layer handles renaming and type-casting only. Data quality decisions will come later when we add tests.

• 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 {{ ref('raw_products') }}
),

renamed as (
    select
        id as product_id,
        name as product_name,
        category,
        cast(price as numeric) as price,
        cast(created_at as date) as created_at
    from source
)

select * from renamed

What does this code do?

This renames id to product_id and name to product_name (avoiding the reserved word name), and casts price to NUMERIC so downstream calculations are reliable.

• Save stg_products.sql.
• Run your models to verify the first three staging views are working:

dbt run

You should see 3 models run successfully, each showing OK created sql view model in the output.

• Create the last staging file called stg_payments.sql inside models/staging/ and paste the following code:

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

renamed as (
    select
        id as payment_id,
        order_id,
        payment_method,
        cast(amount as numeric) as amount,
        cast(created_at as date) as paid_at
    from source
)

select * from renamed

What does this code do?

This renames id to payment_id and created_at to paid_at (a more descriptive name for when the payment was processed). The amount is cast to NUMERIC for consistent arithmetic downstream.

• Save stg_payments.sql.

Configure materializations and verify the full staging layer

Your staging models ran as views by default. Making this explicit in your project configuration documents the decision and ensures it stays consistent as the project grows.

Why materialize staging models as views?

Views are cheap and always fresh. They do not store data themselves. Every time you query a view, it re-runs the underlying SQL against the latest source data.

For staging models that simply rename and cast, this is ideal. You avoid storing duplicate data and guarantee that any upstream change in the seeds is immediately reflected.

• In VS Code, open dbt_project.yml from the root of your project.
• Find the seeds: section at the bottom of the file. Add the following models: block directly below it:

models:
  ecommerce_analytics:
    staging:
      +materialized: view

What does this configuration do?

The +materialized: view setting tells dbt that every model inside the models/staging/ folder should be created as a BigQuery view. The + prefix means this config applies to all models in that directory (and subdirectories).

• Save dbt_project.yml.

Before you run this, how many models do you think dbt will create? What type will they be?

• Run all your models by running this command:

dbt run

You should see all 4 staging models run successfully. Each line shows OK created sql view model confirming they are materialized as views in your dbt_dev dataset.

• Open the BigQuery console in your browser.
• In the Explorer panel on the left, expand your project, then expand the dbt_dev dataset.
• Click on stg_orders and select the Preview tab to view the data.

You should see columns named order_id, customer_id, order_date, order_status, and order_total instead of the original raw column names. The data types are now consistent (DATE for dates, NUMERIC for amounts).

Don't see the staging views in BigQuery?

• Click the refresh icon next to your project name in the Explorer panel. BigQuery does not auto-refresh the dataset list.
• Confirm the views are in the dbt_dev dataset (not dbt_dev_raw). Seeds go to the raw schema. Models go to the default dataset.
• Check that dbt run completed with no errors in your terminal output.

Help me find my staging views in BigQuery

✔️ Awesome, I've got everything!

Great. Double check you have saved all four staging files and dbt_project.yml.

ⓧ I'd like to double check the full code

Here is each file you should have at this point. Compare yours against these:

models/staging/stg_customers.sql

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

renamed as (
    select
        id as customer_id,
        first_name,
        last_name,
        email,
        cast(created_at as date) as created_at
    from source
)

select * from renamed

models/staging/stg_orders.sql

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

renamed as (
    select
        id as order_id,
        customer_id,
        cast(order_date as date) as order_date,
        status as order_status,
        cast(total_amount as numeric) as order_total
    from source
)

select * from renamed

models/staging/stg_products.sql

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

renamed as (
    select
        id as product_id,
        name as product_name,
        category,
        cast(price as numeric) as price,
        cast(created_at as date) as created_at
    from source
)

select * from renamed

models/staging/stg_payments.sql

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

renamed as (
    select
        id as payment_id,
        order_id,
        payment_method,
        cast(amount as numeric) as amount,
        cast(created_at as date) as paid_at
    from source
)

select * from renamed

dbt_project.yml (full file):

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"

seeds:
  ecommerce_analytics:
    +schema: raw

models:
  ecommerce_analytics:
    staging:
      +materialized: view

Your staging layer is live. You have clean, consistently-typed data with business-friendly column names. Next up, you will build the star schema on top of this foundation, pre-computing the relationships so that business questions become single-JOIN queries instead of four-table monsters.

Build the Star Schema

Your staging layer gives you clean, consistently named data. But every time an analyst wants to answer a business question, they still need to JOIN three or four tables together manually.

A star schema solves this by pre-computing the relationships. You build one fact table (measurable business events) surrounded by dimension tables (descriptive context). After this step, "top customers by lifetime value" becomes a single query with zero JOINs.

In this step, get ready to:

• Build a customer dimension model that pre-computes lifetime metrics.
• Create a fact table that joins orders with payment details.
• Query the star schema and compare it to the raw data query from Step 2.

Create the customer dimension model

Dimension tables hold descriptive context about the entities in your business. The customer dimension pre-computes metrics like lifetime value so analysts never have to aggregate on the fly.

• Create the models/marts/ directory by running this command:

mkdir models/marts

• Confirm the directory exists by running:

ls models

You should see both staging and marts listed in the output.

• Create a new file called dim_customers.sql inside models/marts/ and paste the source and aggregation CTEs:

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

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

customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders,
        sum(order_total) as lifetime_value
    from orders
    group by customer_id
),

What does this code do?

• The customers CTE pulls cleaned customer data from your staging layer.
• The orders CTE pulls cleaned order data from staging.
• The customer_orders CTE aggregates each customer's orders into summary metrics: first order date, most recent order, total number of orders, and total lifetime spend.

• Directly below the customer_orders CTE (after the trailing comma), add the final assembly CTE and select statement:

final as (
    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customers.email,
        customers.created_at,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders,
        coalesce(customer_orders.lifetime_value, 0) as lifetime_value
    from customers
    left join customer_orders on customers.customer_id = customer_orders.customer_id
)

select * from final

What does this code do?

• The final CTE joins the customer details with their aggregated order metrics using a LEFT JOIN.
• The coalesce function ensures customers with no orders show 0 instead of NULL for their order count and lifetime value.
• The result is one row per customer with all their lifetime metrics pre-computed. Analysts can query this directly without any aggregation.

• Save dim_customers.sql.
• Verify the model builds by running:

dbt run --select dim_customers

You should see output showing the dim_customers model created successfully with a "Completed" status.

Seeing a compilation error?

• Check that the trailing comma after the customer_orders CTE closing parenthesis is present. Without it, the next CTE will not parse.
• Make sure {{ ref('stg_customers') }} and {{ ref('stg_orders') }} match your staging model filenames exactly.
• Confirm dim_customers.sql is saved inside the models/marts/ folder, not in models/staging/.

Why is my dim_customers model failing to compile?

✔️ Awesome, I've got everything!

Great. Double check you have saved dim_customers.sql and confirmed it built successfully.

ⓧ I'd like to double check the full code

Your dim_customers.sql file should contain both chunks in order. Scroll up and confirm:

• Chunk 1: Three CTEs (customers, orders, customer_orders) ending with a trailing comma after the closing parenthesis.
• Chunk 2: The final CTE with a LEFT JOIN and coalesce calls, followed by select * from final.

Write the product dimension and fact table

The product dimension is a simple catalog lookup. The fact table is where measurable events live. Each row in fct_orders represents one order joined with its payment details.

• Create a new file called dim_products.sql inside models/marts/ and paste this code:

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

final as (
    select
        product_id,
        product_name,
        category,
        price
    from products
)

select * from final

What does this code do?

This dimension table exposes the product catalog with clean column names. It selects only the columns analysts need (ID, name, category, price) and drops metadata like created_at that is not useful for reporting.

• Save dim_products.sql.
• Create a new file called fct_orders.sql inside models/marts/ and paste this code:

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

payments as (
    select * from {{ ref('stg_payments') }}
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.order_status,
        orders.order_total,
        payments.payment_method,
        payments.paid_at
    from orders
    left join payments on orders.order_id = payments.order_id
)

select * from final

What does this code do?

• The orders and payments CTEs pull from your staging models.
• The final CTE joins each order with its payment record. The grain is one row per order.
• This gives analysts a single table with order details and payment method in one place. No more multi-table JOINs for basic order questions.

• Save fct_orders.sql.
• Build the two new models by running:

dbt run --select dim_products fct_orders

You should see both models complete successfully in the output.

Models not found?

• Confirm both files are saved inside models/marts/ (not the project root or models/staging/).
• Check that the filenames are exactly dim_products.sql and fct_orders.sql with no extra spaces or typos.

Why can't dbt find my new mart models?

✔️ Awesome, I've got everything!

Great. Confirm both dim_products.sql and fct_orders.sql are saved.

ⓧ I'd like to double check the full code

Here is the complete dim_products.sql:

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

final as (
    select
        product_id,
        product_name,
        category,
        price
    from products
)

select * from final

Here is the complete fct_orders.sql:

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

payments as (
    select * from {{ ref('stg_payments') }}
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.order_status,
        orders.order_total,
        payments.payment_method,
        payments.paid_at
    from orders
    left join payments on orders.order_id = payments.order_id
)

select * from final

Configure table materialization and query the star schema

Right now your mart models built as views (the dbt default). For analytics tables that analysts query frequently, you want to materialize them as physical tables for better performance.

• In dbt_project.yml, find the models section. Locate this block:

models:
  ecommerce_analytics:
    staging:
      +materialized: view

• Add the marts configuration directly below the staging block, so the full models section looks like this:

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

Why tables instead of views?

Views re-execute their SQL every time they are queried. Tables store pre-computed results. For staging, views are fine because they are only read by downstream models during a dbt run.

Mart tables are queried directly by analysts and dashboards, often many times per day. Materializing them as tables means the heavy JOINs and aggregations run once during dbt run, and every subsequent query is fast.

• Save dbt_project.yml.
• Rebuild all models with the new materialization by running:

dbt run

You should see all 7 models complete. The staging models remain as views, and the 3 mart models now show as tables in the output.

Before you run the next query, think: how many JOINs do you think it takes to find your top 3 customers by lifetime value using the star schema?

• Switch back to the BigQuery console and run this query against your star schema:

SELECT
    customer_id,
    first_name,
    last_name,
    lifetime_value,
    number_of_orders
FROM `dbt_dev.dim_customers`
ORDER BY lifetime_value DESC
LIMIT 3

Zero JOINs. The answer comes back instantly from a single table. Compare this to the multi-table JOIN you wrestled with in Step 2.

What is a star schema?

A star schema is the Kimball approach to dimensional modeling. The fact table sits at the center (like the body of a star) holding measurable events. Dimension tables radiate outward (like the points of a star) holding descriptive attributes.

Analysts JOIN fact to dimension when they need context ("revenue by category"), but many questions are answerable from a single dimension or fact table alone. That is the power of pre-computation.

Query returning unexpected results?

• Make sure you ran dbt run after saving the dbt_project.yml changes. The tables need to be rebuilt with the latest config.
• Check that your dataset reference matches your profile. If your dataset is dbt_dev, the query should reference dbt_dev.dim_customers.
• Note that stg_orders still includes the null customer_id row at this point. You will fix this in the next step with tests.

Help me debug my BigQuery query against dim_customers.

Your star schema is built. You now have a fact table and two dimension tables that make business questions trivial to answer. Next up, you will add automated tests to catch data quality issues before they reach analysts.

Test Your Data and Generate Documentation

Your star schema is working, but there's a hidden problem lurking in the data. Order 11 in your raw data has a null customer_id, and right now that bad row flows silently through your staging views into every downstream model.

Models without tests silently degrade. In this step, you'll add automated tests that catch data quality issues before they reach analysts, fix the integrity problem your tests uncover, and generate documentation so the next engineer understands what you built.

In this step, get ready to:

• Add data quality tests to your staging and mart models.
• Fix a data integrity issue caught by your tests.
• Generate a documentation site with a visual lineage graph.

Define tests for your staging models

In dbt, tests are assertions you define in YAML schema files. They run against your models and fail loudly when data violates your expectations.

• In VS Code, create a new file called _staging.yml inside your models/staging/ folder.
• Paste the following code to define tests for your customers and orders models:

version: 2

models:
  - name: stg_customers
    description: Cleaned customer data from raw seed source. One row per customer.
    columns:
      - name: customer_id
        description: Primary key - unique customer identifier
        tests:
          - unique
          - not_null
      - name: email
        description: Customer email address
        tests:
          - unique
          - not_null

What do these tests do?

• unique asserts that no two rows share the same value in that column.
• not_null asserts that every row has a value (no NULLs allowed).
• Together they guarantee each primary key is present and unique, which is the foundation of data integrity.

• Below the stg_customers definition, add the stg_orders model definition with more advanced tests:

  - name: stg_orders
    description: Cleaned order data excluding incomplete records (null customer_id filtered out). One row per order.
    columns:
      - name: order_id
        description: Primary key - unique order identifier
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to stg_customers
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: order_status
        description: Current status of the order
        tests:
          - accepted_values:
              values: ['completed', 'shipped', 'returned', 'pending']

What are these advanced tests?

• relationships checks that every customer_id in orders actually exists in the customers table. This is referential integrity.
• accepted_values checks that order_status only contains values from a known list. Any unexpected value triggers a failure.

Before you run your tests, think about what you expect to happen. Do you think all tests will pass cleanly?

• Save _staging.yml and run your tests:

dbt test

You'll see a FAIL on the not_null test for stg_orders.customer_id. The output shows 1 failing record because order 11 in your raw data has an empty customer_id field.

This is exactly what tests are for. Without this test, that null row would silently corrupt downstream dashboards.

All tests passed instead of failing?

Double check that row 11 in seeds/raw_orders.csv has an empty customer_id field (the row should read 11,,2023-03-28,pending,0.00). If you accidentally added a value there, remove it and run dbt seed again before re-running dbt test.

help me debug why my dbt not_null test is passing when it should fail

Fix the failing test and add mart tests

The test told you exactly where the problem is. The fix belongs at the staging boundary, where you filter out incomplete records before they reach business logic.

• In models/staging/stg_orders.sql, find the renamed CTE. Locate the last line before the closing parenthesis:

    from source

• Add a WHERE clause directly below that line, so the block now ends like this:

    from source
    where customer_id is not null

Why filter here?

Filtering at the staging boundary means every downstream model (fact tables, dimensions) automatically gets clean data. You fix it once, and everything downstream benefits.

• Save stg_orders.sql and re-run your tests to confirm the fix works:

dbt test

All tests should now pass. The not_null test on customer_id succeeds because the null row is filtered out before testing.

Still seeing a failure?

Make sure the where customer_id is not null line is inside the renamed CTE, not after select * from renamed. The filter must come before the final SELECT to take effect.

help me fix my stg_orders.sql WHERE clause placement

• Now add test definitions for your remaining staging models. Add the following below your stg_orders definition in _staging.yml:

  - name: stg_products
    description: Cleaned product catalog data. One row per product.
    columns:
      - name: product_id
        description: Primary key - unique product identifier
        tests:
          - unique
          - not_null

  - name: stg_payments
    description: Cleaned payment transaction data. One row per payment.
    columns:
      - name: payment_id
        description: Primary key - unique payment identifier
        tests:
          - unique
          - not_null
      - name: order_id
        description: Foreign key to stg_orders
        tests:
          - not_null

What does this add?

These definitions ensure every product and payment has a valid primary key. The not_null test on stg_payments.order_id guarantees every payment links to an order.

• Save _staging.yml.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file and move on to the marts tests below.

ⓧ I'd like to double check the full code

Your _staging.yml has four model definitions in order: stg_customers, stg_orders, stg_products, and stg_payments. Scroll up and confirm each chunk is present in that order, starting with the version: 2 header at the top.

• Now create the marts schema file. Create a new file called _marts.yml inside your models/marts/ folder and paste this content:

version: 2

models:
  - name: fct_orders
    description: Order fact table - one row per order joined with payment information. Grain is one row per order_id.
    columns:
      - name: order_id
        description: Primary key - unique order identifier
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to dim_customers
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

• Add the dimension table definitions below the fct_orders definition:

  - name: dim_customers
    description: Customer dimension table with pre-computed lifetime metrics. One row per customer.
    columns:
      - name: customer_id
        description: Primary key - unique customer identifier
        tests:
          - unique
          - not_null
      - name: lifetime_value
        description: Total spend across all orders

  - name: dim_products
    description: Product dimension table with catalog information. One row per product.
    columns:
      - name: product_id
        description: Primary key - unique product identifier
        tests:
          - unique
          - not_null

What does the relationships test on fct_orders do?

The relationships test on fct_orders.customer_id checks that every customer referenced in the fact table actually exists in dim_customers. This catches orphaned records that would break JOINs in analyst queries.

• Save _marts.yml.
• Run dbt build to rebuild all models and run all tests together:

dbt build

Why dbt build instead of dbt test?

The dbt build command runs models and tests in dependency order. It rebuilds the staging views (now with the WHERE filter), then the mart tables, then runs all tests against the freshly-built models.

You should see a summary ending with all passes and zero errors. The output confirms that every primary key is unique, every foreign key resolves, and every status value is valid.

Seeing a relationships test failure on fct_orders?

This happens if dim_customers was not rebuilt after you fixed stg_orders.sql. The dbt build command should handle this automatically, but if you ran dbt test alone, try dbt build instead so models are refreshed first.

help me fix my dbt relationships test failure

Generate documentation and explore your lineage

Your schema files already include description fields for every model and key column. These descriptions power dbt's documentation site, giving analysts context about what each table contains and how it was built.

• Generate the documentation artifacts by running:

dbt docs generate

This produces a catalog.json and manifest.json in your target/ directory containing all model metadata.

• Serve the documentation site locally by running:

dbt docs serve

Your browser opens automatically to a documentation site on port 8080. Click the green circle icon in the bottom-right corner to open the lineage graph (DAG).

You should see the full data flow: seed tables on the left, staging views in the middle, and mart tables on the right. Click any node to see the descriptions you wrote in your YAML schema files.

What is this lineage graph?

The lineage graph (also called a DAG) shows how data flows through your project. Each node is a model, and each arrow shows a dependency.

This is invaluable for debugging. If a downstream dashboard breaks, you can trace the lineage backwards to find which source or transformation changed.

Browser didn't open automatically?

Navigate to http://localhost:8080 manually in your browser. If you see a blank page, make sure you ran dbt docs generate before dbt docs serve.

help me get my dbt docs site working

Your data warehouse now has a safety net. Every time you run dbt build, tests guard your models against bad data, and your documentation site gives anyone a clear map of how everything connects. Next up, you'll take on a bonus challenge to make your fact table production-ready with incremental processing.

Secret mission

Make Your Fact Table Incremental

In production, fact tables grow to billions of rows. Re-processing the entire table on every run is wasteful and expensive. Can you convert your fact table to only process new orders on each run?

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 on BigQuery's free tier (1 TiB queries/month, 10 GiB storage), so there are no ongoing costs for the data volumes used here.

Resources you used:

• BigQuery dbt_dev dataset (staging views and mart tables).
• BigQuery dbt_dev_raw dataset (seed data tables).
• Local ecommerce_analytics project folder.
• Local dbt-env Python virtual environment.
• The profiles.yml configuration file in your .dbt folder.

Keep everything running

No action needed. Choose this if you want to continue experimenting with new dbt models or practicing queries against your star schema.

• Your data stays in BigQuery at no cost within the free tier limits.
• You can continue adding new models, seeds, or tests at any time by reactivating your virtual environment.
• The gcloud CLI remains installed on your machine for future GCP work.

Pause - I'll come back to this later

BigQuery is serverless. You only pay for queries you run (and the first 1 TiB/month is free). There is nothing to pause.

• Your datasets stay in BigQuery at zero cost for this project's data volume.
• Deactivate your Python virtual environment by running:

deactivate

When you want to resume, reactivate the environment and pick up where you left off.

Delete - I don't want to use this again

Remove all project resources to start completely fresh. This deletes everything.

• Delete the entire GCP project to remove all BigQuery datasets at once by running:

gcloud projects delete [[GCP_PROJECT_ID="your-gcp-project-id"]]

What does this do?

This schedules your GCP project for deletion. Google holds it for 30 days before permanently removing it, so you can recover if you change your mind. You will be prompted to confirm before it proceeds.

• Deactivate your Python virtual environment by running:

deactivate

• Delete your local project folder and virtual environment.

On macOS/Linux:

cd ~/Desktop
rm -rf ecommerce_analytics
rm -rf dbt-env

On Windows:

cd ~\Desktop
rmdir /s /q ecommerce_analytics
rmdir /s /q dbt-env

• Delete your dbt profile configuration by removing the profiles.yml file.

On macOS/Linux:

rm ~/.dbt/profiles.yml

On Windows:

del %USERPROFILE%\.dbt\profiles.yml

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-style analytics data warehouse from scratch using dbt and BigQuery.

You've learned how to:

• Built an end-to-end dbt project that transforms raw CSV data through staging views into a dimensional star schema in BigQuery, and experienced the full development workflow: seed, run, test, docs.
• Designed a Kimball-style dimensional model with a fact table (fct_orders) and dimension tables (dim_customers, dim_products) that make business questions trivial to answer.
• Implemented automated data quality tests that caught a real integrity issue (null customer_id), fixed it at the staging boundary, and generated documentation with a visual lineage graph showing the complete data flow.
• Secret Mission: Converted the fact table to an incremental materialization, proving you understand how production dbt projects handle growing datasets efficiently.

Ready to quiz yourself?