Analytics Platform with dbt and BigQuery

Build a production-grade star schema using dbt layered architecture on BigQuery.

Introduction

30 Second Summary

Every company collects raw data from orders, customers, and payments. But raw data is messy and disorganized. Turning it into something a business can actually use requires a structured, repeatable pipeline.

In this project, you will build a production-grade analytics platform that transforms raw e-commerce data into a business-ready star schema using dbt and Google BigQuery.

What You'll Build

A complete analytics engineering pipeline with layered transformations, a star schema, automated data quality tests, and auto-generated documentation with lineage visualization.

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

  • A dbt project connected to BigQuery with a layered architecture (staging, intermediate, marts) following industry best practices.
  • A star schema with tested dimension and fact tables, complete with referential integrity checks and automated data quality gates.
  • An incremental model optimized for large-scale append-heavy workloads, with auto-generated documentation and full lineage visualization.
  • Secret Mission: implement a Slowly Changing Dimension (SCD Type 2) using dbt snapshots to track customer segment changes over time.

Are there any prerequisites?

You should have basic SQL knowledge (SELECT, JOIN, GROUP BY, window functions) and familiarity with the command line.

No prior experience with dbt or BigQuery is required. This project covers full setup from scratch.

Set Up Your Environment

Before you can build any dbt models, you need a working local environment connected to a cloud data warehouse. This step gets Python, dbt, and Google BigQuery talking to each other on your machine.

You will install dbt via the dbt-bigquery package, which bundles dbt-core automatically. Then you will install the Google Cloud SDK and authenticate so that dbt can run queries against BigQuery.

In this step, get ready to:
  • Install Python 3.12+ and create a virtual environment with dbt-bigquery.
  • Install Google Cloud SDK, authenticate, and create a GCP project with BigQuery enabled.
  • Verify the full installation with dbt --version.
Install Python and dbt-bigquery

The dbt-bigquery adapter requires Python 3.10 or higher (up to 3.13). This project uses Python 3.12+ for full compatibility.

  • Check your Python version by running this command:
python3 --version

✔️ I see version 3.12 or higher

Your Python version is compatible. Move on to creating your virtual environment below.

ⓧ I see a version lower than 3.12

Your system has an older version of Python installed. macOS often ships with Python 3.9 by default, which will not work for this project.

  • Download and install Python 3.12 or 3.13 from the official site:

python.org/downloads

  • After installing, run python3.12 --version or python3.13 --version to confirm the new version is available.
  • Use the full versioned binary (e.g. python3.12) for the virtual environment step below if your default python3 still points to the older version.

ⓧ Command not found

Python is not installed on your system. Download and install Python 3.12 or 3.13 from the official site:

python.org/downloads

  • Run through the installer, making sure to check the option to add Python to your PATH (Windows) or follow the default prompts (macOS).
  • After installing, close and reopen your terminal.
  • Run python3 --version again to confirm you see 3.12 or higher.

A virtual environment keeps your project dependencies isolated from the rest of your system. This prevents version conflicts between projects.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder, move into it, and set up a virtual environment by running these commands:
mkdir dbt-analytics
cd dbt-analytics
python3 -m venv venv

What is a virtual environment?

A virtual environment is a self-contained directory that holds its own copy of Python and any packages you install. When you activate it, pip install puts packages only inside that directory instead of globally.

This means you can have different versions of dbt for different projects without them clashing.

  • Activate your virtual environment by running this command:

macOS

source venv/bin/activate

Windows

.\venv\Scripts\activate

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

  • Install dbt-bigquery (which installs dbt-core automatically) by running:
pip install dbt-bigquery

What does this install?

The dbt-bigquery package is an adapter that lets dbt talk to Google BigQuery. It automatically pulls in dbt-core as a dependency, so one install gives you both.

This install may take a minute or two. When it finishes, you should see a success message with no errors.

Seeing a permissions error or build failure?

  • Make sure you see (venv) in your prompt. If you forgot to activate, packages install globally and may hit permission issues.
  • If you see compilation errors, confirm your Python version is 3.12 or 3.13. Python 3.14 is not supported by dbt-bigquery.

Help me troubleshoot my dbt-bigquery installation error.

Install Google Cloud SDK and authenticate

dbt needs credentials to run queries against BigQuery. The Google Cloud SDK provides the gcloud CLI, which handles authentication so dbt can connect using your Google account.

  • Check whether the Google Cloud CLI is already installed by running:
gcloud --version

✔️ I see a version number

The Google Cloud CLI is already installed. Move on to the authentication step below.

ⓧ Command not found

You need to install the Google Cloud CLI. Follow the official installation guide for your operating system:

Install the Google Cloud CLI

  • On macOS, download the archive, extract it, and run the install.sh script.
  • On Windows, download and run the installer (.exe).
  • After installing, close and reopen your terminal so the gcloud command is available on your PATH.
  • Run gcloud --version again to confirm it works.

Now you need a GCP project with BigQuery enabled. If you already have one, you can reuse it. Otherwise, follow these steps to create one.

  • Open the Google Cloud Console in your browser and sign in with your Google account.
  • Click the project dropdown at the top of the page.
  • Click New Project.
  • Enter a project name (e.g. dbt-analytics-project) and click Create.
  • Once the project is created, select it from the project dropdown.
  • Navigate to APIs & Services in the left sidebar.
  • Click Enable APIs and Services.
  • Search for BigQuery API and click Enable.

Do I need a billing account?

Yes. BigQuery's Free Tier (1 TB queries/month and 10 GB storage) requires a billing account, but you will not be charged as long as you stay within those limits. The project's data is tiny and will not come close to these thresholds.

If prompted to enable billing, add a payment method. Google will not charge you within Free Tier limits.

Now authenticate your local machine so that dbt can access BigQuery using your Google account.

  • Authenticate with Application Default Credentials by running this command:
gcloud auth application-default login

What happens when you run this?

This command opens your browser and asks you to sign in with your Google account. After you grant permission, it saves credentials locally that dbt (and other tools) use to authenticate with BigQuery.

  • Sign in with the same Google account that owns your GCP project.
  • Click Allow when prompted to grant access.

You should see a confirmation message in your terminal indicating that credentials have been saved.

Browser didn't open?

  • If you are on a remote machine or a browser does not open, the terminal will print a URL. Copy and paste it into your browser manually to complete authentication.
  • Make sure you allow the requested permissions. If you click deny, the credentials will not be saved.

Help me troubleshoot gcloud authentication.

Verify the installation

With Python, dbt, and gcloud all installed, run a quick check to confirm everything is working together.

  • Verify your dbt installation by running this command:
dbt --version

✔️ I see dbt-core and bigquery listed

Your output should show a Core section with an installed version and a Plugins section listing bigquery. Everything is ready.

ⓧ Command not found or missing plugin

  • If you see "command not found", make sure your virtual environment is still active. Look for (venv) in your terminal prompt.
  • If you see dbt-core but no bigquery plugin, run pip install dbt-bigquery again inside your activated virtual environment.
  • If you closed your terminal since activating, navigate back to your project folder and re-activate the virtual environment before running dbt --version.

Help me troubleshoot dbt --version not working.

What should I see?

The output has two sections. The Core section shows your installed dbt-core version. The Plugins section lists each adapter you have installed. You should see bigquery listed there with its version number.

Your development environment is fully set up. Next, you will initialize the dbt project and load your raw e-commerce data into BigQuery.

Initialize Your dbt Project and Load Raw Data

Your environment is ready with dbt and BigQuery connected. Now you need a project structure to organize your transformations and raw data to transform.

In this step, you will scaffold a dbt project, connect it to your BigQuery dataset, and load sample e-commerce data that will serve as the raw source for the rest of the project.

In this step, get ready to:
  • Scaffold the dbt project and configure the BigQuery connection.
  • Create seed CSV files with sample e-commerce data.
  • Load the seed data into BigQuery tables.
Scaffold the project and configure the BigQuery connection

The dbt init command creates a new project folder with all the directories and starter files you need. It also walks you through setting up a connection profile so dbt knows how to reach your data warehouse.

  • Make sure your virtual environment is activated, then scaffold the project by running this command:
dbt init ecommerce_analytics

What will this command ask?

The dbt init command launches an interactive setup. It will ask you several questions. Answer them as follows:

  • Which database would you like to use? Select bigquery (enter the number next to it).
  • Desired authentication method: Select oauth.
  • project (GCP project id): Enter your GCP project ID.
  • dataset (the name of your dbt dataset): Enter dbt_dev.
  • threads (1 or more): Enter 4.
  • job_execution_timeout_seconds: Press Enter to accept the default (300).
  • Desired location: Select US.
  • After the prompts complete, move into the new project directory by running:
cd ecommerce_analytics
  • Open the ecommerce_analytics folder in VS Code so you can edit files throughout the rest of this project.

Now confirm the connection profile is correct. dbt stores connection details in ~/.dbt/profiles.yml. The init wizard wrote your answers there automatically.

  • Open ~/.dbt/profiles.yml in your editor and confirm it looks like this:
ecommerce_analytics:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: [[GCP_PROJECT_ID="your-gcp-project-id"]]
      dataset: dbt_dev
      threads: 4
      job_execution_timeout_seconds: 300
      location: US

What is profiles.yml?

This file tells dbt how to connect to your data warehouse. The profile name (ecommerce_analytics) must match the profile key in your dbt_project.yml file. The method: oauth setting uses the credentials you set up with gcloud auth application-default login in Step 1.

The dataset field is what BigQuery calls a schema. All the tables dbt creates will land in a BigQuery dataset named dbt_dev.

  • Verify the connection works by running:
dbt debug
  • You should see all checks passing, ending with Connection test: OK connection ok.

Seeing a connection error?

  • Make sure you ran gcloud auth application-default login in Step 1 and completed the browser-based OAuth flow.
  • Check that the project value in profiles.yml matches your actual GCP project ID (visible in the Google Cloud Console header).
  • Confirm the BigQuery API is enabled for your project in the GCP console under APIs & Services.

Help me debug my dbt debug connection error with BigQuery OAuth.

Next, update dbt_project.yml to configure how each layer of models will be materialized. This sets up the layered architecture pattern you will use throughout the project.

  • Open dbt_project.yml in your editor and replace its entire contents with the following:
name: 'ecommerce_analytics'
version: '1.0.0'
config-version: 2

profile: 'ecommerce_analytics'

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

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

What does this configuration do?

The models section defines default materializations for each folder inside models/. Staging and intermediate models become views (cheap, always fresh). Mart models become tables (fast reads for BI tools).

This is the dbt layered architecture pattern. You will create these folders and populate them in the following steps.

  • Save dbt_project.yml.
Create the seed data files

Seeds are CSV files that dbt loads directly into your warehouse as tables. You will use four seed files to simulate raw e-commerce data: customers, products, orders, and payments.

  • In the seeds/ directory inside your project, create a file named raw_customers.csv with the following content:
customer_id,first_name,last_name,email,country,created_at
1,Alice,Johnson,alice.johnson@email.com,United States,2023-01-15
2,Bob,Smith,bob.smith@email.com,United Kingdom,2023-02-20
3,Carol,Williams,carol.w@email.com,Canada,2023-03-10
4,David,Brown,david.b@email.com,Australia,2023-04-05
5,Eva,Martinez,eva.m@email.com,Germany,2023-05-12
6,Frank,Lee,frank.lee@email.com,United States,2023-06-18
7,Grace,Chen,grace.chen@email.com,Singapore,2023-07-22
8,Henry,Wilson,henry.w@email.com,United Kingdom,2023-08-30
9,Iris,Taylor,iris.t@email.com,Canada,2023-09-14
10,Jack,Anderson,jack.a@email.com,United States,2023-10-01

What does this data represent?

This is a customer registry with 10 records spanning different countries. The customer_id column serves as the primary key. Later, you will build a customer dimension that enriches this data with order history and value segments.

  • Create seeds/raw_products.csv with the following content:
product_id,product_name,category,price,supplier
101,Wireless Mouse,Electronics,29.99,TechCorp
102,USB-C Hub,Electronics,49.99,TechCorp
103,Standing Desk,Furniture,399.99,OfficePro
104,Ergonomic Chair,Furniture,299.99,OfficePro
105,Noise Cancelling Headphones,Electronics,199.99,AudioMax
106,Mechanical Keyboard,Electronics,89.99,TechCorp
107,Monitor Arm,Furniture,79.99,OfficePro
108,Webcam HD,Electronics,69.99,AudioMax
109,Desk Lamp,Furniture,45.99,OfficePro
110,Cable Management Kit,Accessories,19.99,TechCorp
  • Create seeds/raw_orders.csv with the following content:
order_id,customer_id,product_id,order_date,quantity,status,amount
1001,1,101,2024-01-10,2,completed,59.98
1002,2,103,2024-01-15,1,completed,399.99
1003,1,105,2024-01-20,1,completed,199.99
1004,3,102,2024-02-01,3,completed,149.97
1005,4,104,2024-02-10,1,shipped,299.99
1006,5,106,2024-02-15,2,completed,179.98
1007,2,108,2024-03-01,1,completed,69.99
1008,6,103,2024-03-10,1,returned,399.99
1009,7,101,2024-03-15,1,completed,29.99
1010,8,105,2024-03-20,1,shipped,199.99
1011,1,110,2024-04-01,5,completed,99.95
1012,9,107,2024-04-10,2,completed,159.98
1013,3,106,2024-04-15,1,completed,89.99
1014,10,104,2024-04-20,1,completed,299.99
1015,5,109,2024-05-01,1,cancelled,45.99
1016,4,102,2024-05-10,2,completed,99.98
1017,6,105,2024-05-15,1,completed,199.99
1018,7,103,2024-05-20,1,completed,399.99
1019,2,110,2024-06-01,3,completed,59.97
1020,8,106,2024-06-10,1,shipped,89.99
  • Create seeds/raw_payments.csv with the following content:
payment_id,order_id,payment_method,amount,payment_date,status
2001,1001,credit_card,59.98,2024-01-10,success
2002,1002,paypal,399.99,2024-01-15,success
2003,1003,credit_card,199.99,2024-01-20,success
2004,1004,debit_card,149.97,2024-02-01,success
2005,1005,credit_card,299.99,2024-02-10,success
2006,1006,paypal,179.98,2024-02-15,success
2007,1007,credit_card,69.99,2024-03-01,success
2008,1008,credit_card,399.99,2024-03-10,refunded
2009,1009,debit_card,29.99,2024-03-15,success
2010,1010,paypal,199.99,2024-03-20,success
2011,1011,credit_card,99.95,2024-04-01,success
2012,1012,debit_card,159.98,2024-04-10,success
2013,1013,credit_card,89.99,2024-04-15,success
2014,1014,paypal,299.99,2024-04-20,success
2015,1015,credit_card,45.99,2024-05-01,failed
2016,1016,credit_card,99.98,2024-05-10,success
2017,1017,debit_card,199.99,2024-05-15,success
2018,1018,paypal,399.99,2024-05-20,success
2019,1019,credit_card,59.97,2024-06-01,success
2020,1020,debit_card,89.99,2024-06-10,success

Why multiple statuses in orders and payments?

The seed data deliberately includes different statuses (completed, shipped, returned, cancelled for orders and success, failed, refunded for payments). This variety lets you write meaningful data quality tests in later steps, such as accepted_values checks that ensure no unexpected statuses appear.

  • Save all four CSV files.
  • Confirm they exist by running:
ls seeds/
  • You should see raw_customers.csv, raw_orders.csv, raw_payments.csv, and raw_products.csv listed in the output.
Load seed data into BigQuery

With the CSV files in place, you can now load them into BigQuery as actual tables. The dbt seed command reads every CSV in the seeds/ directory and creates a corresponding table in your configured dataset.

  • Load all seed data into BigQuery by running:
dbt seed
  • You should see output confirming each seed was loaded, with 4 tables created in your dbt_dev dataset.

What does dbt seed do?

The dbt seed command creates a table in BigQuery for each CSV file. It infers column types automatically and uploads the rows. Seeds are designed for small, static reference data. They are not meant for large production datasets.

  • Confirm the tables exist in BigQuery by opening the Google Cloud Console.
  • Navigate to BigQuery in the left sidebar.
  • Expand your project, then expand the dbt_dev dataset.
  • You should see four tables: raw_customers, raw_orders, raw_payments, and raw_products.

Don't see the tables in BigQuery?

  • Check the terminal output from dbt seed for errors. If you see a permissions error, ensure the BigQuery API is enabled and your OAuth credentials have access to the project.
  • Make sure dataset in your profiles.yml is set to dbt_dev. If you used a different name, dbt created the tables there instead.
  • Try refreshing the BigQuery console. New datasets can take a few seconds to appear in the UI.

Help me troubleshoot why dbt seed did not create tables in my BigQuery dataset.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml you should have at this point:

name: 'ecommerce_analytics'
version: '1.0.0'
config-version: 2

profile: 'ecommerce_analytics'

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

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

And here are the four seed files:

customer_id,first_name,last_name,email,country,created_at
1,Alice,Johnson,alice.johnson@email.com,United States,2023-01-15
2,Bob,Smith,bob.smith@email.com,United Kingdom,2023-02-20
3,Carol,Williams,carol.w@email.com,Canada,2023-03-10
4,David,Brown,david.b@email.com,Australia,2023-04-05
5,Eva,Martinez,eva.m@email.com,Germany,2023-05-12
6,Frank,Lee,frank.lee@email.com,United States,2023-06-18
7,Grace,Chen,grace.chen@email.com,Singapore,2023-07-22
8,Henry,Wilson,henry.w@email.com,United Kingdom,2023-08-30
9,Iris,Taylor,iris.t@email.com,Canada,2023-09-14
10,Jack,Anderson,jack.a@email.com,United States,2023-10-01
product_id,product_name,category,price,supplier
101,Wireless Mouse,Electronics,29.99,TechCorp
102,USB-C Hub,Electronics,49.99,TechCorp
103,Standing Desk,Furniture,399.99,OfficePro
104,Ergonomic Chair,Furniture,299.99,OfficePro
105,Noise Cancelling Headphones,Electronics,199.99,AudioMax
106,Mechanical Keyboard,Electronics,89.99,TechCorp
107,Monitor Arm,Furniture,79.99,OfficePro
108,Webcam HD,Electronics,69.99,AudioMax
109,Desk Lamp,Furniture,45.99,OfficePro
110,Cable Management Kit,Accessories,19.99,TechCorp
order_id,customer_id,product_id,order_date,quantity,status,amount
1001,1,101,2024-01-10,2,completed,59.98
1002,2,103,2024-01-15,1,completed,399.99
1003,1,105,2024-01-20,1,completed,199.99
1004,3,102,2024-02-01,3,completed,149.97
1005,4,104,2024-02-10,1,shipped,299.99
1006,5,106,2024-02-15,2,completed,179.98
1007,2,108,2024-03-01,1,completed,69.99
1008,6,103,2024-03-10,1,returned,399.99
1009,7,101,2024-03-15,1,completed,29.99
1010,8,105,2024-03-20,1,shipped,199.99
1011,1,110,2024-04-01,5,completed,99.95
1012,9,107,2024-04-10,2,completed,159.98
1013,3,106,2024-04-15,1,completed,89.99
1014,10,104,2024-04-20,1,completed,299.99
1015,5,109,2024-05-01,1,cancelled,45.99
1016,4,102,2024-05-10,2,completed,99.98
1017,6,105,2024-05-15,1,completed,199.99
1018,7,103,2024-05-20,1,completed,399.99
1019,2,110,2024-06-01,3,completed,59.97
1020,8,106,2024-06-10,1,shipped,89.99
payment_id,order_id,payment_method,amount,payment_date,status
2001,1001,credit_card,59.98,2024-01-10,success
2002,1002,paypal,399.99,2024-01-15,success
2003,1003,credit_card,199.99,2024-01-20,success
2004,1004,debit_card,149.97,2024-02-01,success
2005,1005,credit_card,299.99,2024-02-10,success
2006,1006,paypal,179.98,2024-02-15,success
2007,1007,credit_card,69.99,2024-03-01,success
2008,1008,credit_card,399.99,2024-03-10,refunded
2009,1009,debit_card,29.99,2024-03-15,success
2010,1010,paypal,199.99,2024-03-20,success
2011,1011,credit_card,99.95,2024-04-01,success
2012,1012,debit_card,159.98,2024-04-10,success
2013,1013,credit_card,89.99,2024-04-15,success
2014,1014,paypal,299.99,2024-04-20,success
2015,1015,credit_card,45.99,2024-05-01,failed
2016,1016,credit_card,99.98,2024-05-10,success
2017,1017,debit_card,199.99,2024-05-15,success
2018,1018,paypal,399.99,2024-05-20,success
2019,1019,credit_card,59.97,2024-06-01,success
2020,1020,debit_card,89.99,2024-06-10,success

Your dbt project is scaffolded, connected to BigQuery, and loaded with raw data. Next up, you will build the staging layer to clean and standardize this raw data into consistent, transformation-ready models.

Build the Staging Layer

Your raw data is sitting in BigQuery, but it is messy. Column names are inconsistent, types are uncast, and there is no documentation telling dbt where the data lives.

The staging layer solves this by creating one model per source table that cleans, renames, and standardizes the raw data. Think of it as the single point of entry for all downstream transformations.

In this step, get ready to:
  • Define your raw data sources with primary key tests.
  • Create four staging models that clean and standardize the raw tables.
  • Run and validate the staging layer in BigQuery.
Define your data sources

Before dbt can read from your raw tables, you need to tell it where they live. A source definition file maps your BigQuery tables to names that dbt models can reference with the {{ source() }} function.

  • Create the models/staging/ directory inside your ecommerce_analytics project by running this command:
mkdir -p models/staging
  • Create a new file called _sources.yml inside models/staging/ and paste the following code:
version: 2

sources:
  - name: ecommerce
    description: Raw e-commerce data loaded via dbt seeds
    schema: "{{ target.schema }}"
    tables:
      - name: raw_orders
        description: One record per order placed by a customer
        columns:
          - name: order_id
            description: Primary key of the orders table
            data_tests:
              - unique
              - not_null
      - name: raw_customers
        description: One record per registered customer
        columns:
          - name: customer_id
            description: Primary key of the customers table
            data_tests:
              - unique
              - not_null
      - name: raw_products
        description: One record per product in the catalog
        columns:
          - name: product_id
            description: Primary key of the products table
            data_tests:
              - unique
              - not_null
      - name: raw_payments
        description: One record per payment transaction
        columns:
          - name: payment_id
            description: Primary key of the payments table
            data_tests:
              - unique
              - not_null

What does this file do?

This YAML file registers your four raw tables as a source called ecommerce. The schema: "{{ target.schema }}" line dynamically points to whatever dataset your profiles.yml targets (in your case, dbt_dev).

The data_tests on each primary key column ensure that values are always unique and never null. If your raw data ever violates these rules, dbt will catch it before bad data flows downstream.

  • Save _sources.yml.

YAML indentation errors?

YAML is whitespace-sensitive. Make sure each nested level uses exactly two spaces (not tabs). If you see parsing errors later, check that data_tests is indented under columns, not at the same level.

help me fix a YAML indentation error in my dbt sources file

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before continuing.

ⓧ I'd like to double check the full code

version: 2

sources:
  - name: ecommerce
    description: Raw e-commerce data loaded via dbt seeds
    schema: "{{ target.schema }}"
    tables:
      - name: raw_orders
        description: One record per order placed by a customer
        columns:
          - name: order_id
            description: Primary key of the orders table
            data_tests:
              - unique
              - not_null
      - name: raw_customers
        description: One record per registered customer
        columns:
          - name: customer_id
            description: Primary key of the customers table
            data_tests:
              - unique
              - not_null
      - name: raw_products
        description: One record per product in the catalog
        columns:
          - name: product_id
            description: Primary key of the products table
            data_tests:
              - unique
              - not_null
      - name: raw_payments
        description: One record per payment transaction
        columns:
          - name: payment_id
            description: Primary key of the payments table
            data_tests:
              - unique
              - not_null
Create the staging models

Each staging model reads from exactly one source table and applies light transformations: renaming columns, casting types, and standardizing values with lower(). No business logic belongs here.

  • Create a new file called _stg_models.yml inside models/staging/ and paste the following schema definition:
version: 2

models:
  - name: stg_orders
    description: Cleaned orders with standardized column names and types
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: order_status
        description: Current order status (lowercased)
        data_tests:
          - accepted_values:
              values: ['completed', 'shipped', 'returned', 'cancelled']

  - name: stg_customers
    description: Cleaned customers with derived full_name field
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: stg_products
    description: Cleaned products with standardized category names
    columns:
      - name: product_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: stg_payments
    description: Cleaned payments with standardized status values
    columns:
      - name: payment_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: payment_status
        description: Payment processing status
        data_tests:
          - accepted_values:
              values: ['success', 'failed', 'refunded']

What does this schema file do?

This file defines metadata and tests for your staging models. The accepted_values tests on order_status and payment_status catch unexpected values automatically.

If a new status like pending appears in your raw data, dbt will flag it as a test failure so you can decide how to handle it.

  • Save _stg_models.yml.

✔️ Awesome, I've got everything!

Great. Save the file and continue to your first SQL model.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_orders
    description: Cleaned orders with standardized column names and types
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: order_status
        description: Current order status (lowercased)
        data_tests:
          - accepted_values:
              values: ['completed', 'shipped', 'returned', 'cancelled']

  - name: stg_customers
    description: Cleaned customers with derived full_name field
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: stg_products
    description: Cleaned products with standardized category names
    columns:
      - name: product_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: stg_payments
    description: Cleaned payments with standardized status values
    columns:
      - name: payment_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: payment_status
        description: Payment processing status
        data_tests:
          - accepted_values:
              values: ['success', 'failed', 'refunded']

Now you will create the four SQL models. Each follows the same pattern: pull from the source, rename columns, cast types, and output clean data.

  • Create stg_orders.sql inside models/staging/ and paste this code:
with source as (

    select * from {{ source('ecommerce', 'raw_orders') }}

),

renamed as (

    select
        order_id,
        customer_id,
        product_id,
        cast(order_date as date) as order_date,
        quantity,
        lower(status) as order_status,
        cast(amount as numeric) as order_amount
    from source

)

select * from renamed

What does this model do?

The source CTE pulls from raw_orders using the source you just defined. The renamed CTE does three things: casts order_date to a proper date type, lowercases status into order_status, and casts amount to numeric as order_amount.

This pattern of renaming status to order_status avoids ambiguity when you join multiple tables later. Each staging model prefixes generic column names with the entity name.

  • Create stg_customers.sql inside models/staging/ and paste this code:
with source as (

    select * from {{ source('ecommerce', 'raw_customers') }}

),

renamed as (

    select
        customer_id,
        first_name,
        last_name,
        concat(first_name, ' ', last_name) as full_name,
        lower(email) as email,
        country,
        cast(created_at as date) as customer_created_at
    from source

)

select * from renamed

What does this model do?

This model derives a full_name field by concatenating first_name and last_name. It also lowercases emails for consistency and renames created_at to customer_created_at to avoid column name collisions in joins.

  • Create stg_products.sql inside models/staging/ and paste this code:
with source as (

    select * from {{ source('ecommerce', 'raw_products') }}

),

renamed as (

    select
        product_id,
        product_name,
        lower(category) as category,
        cast(price as numeric) as unit_price,
        supplier
    from source

)

select * from renamed

What does this model do?

This model lowercases category for consistent grouping and renames price to unit_price. The rename clarifies that this is the per-unit price, not an order total.

  • Create stg_payments.sql inside models/staging/ and paste this code:
with source as (

    select * from {{ source('ecommerce', 'raw_payments') }}

),

renamed as (

    select
        payment_id,
        order_id,
        lower(payment_method) as payment_method,
        cast(amount as numeric) as payment_amount,
        cast(payment_date as date) as payment_date,
        lower(status) as payment_status
    from source

)

select * from renamed

What does this model do?

This model lowercases both payment_method and status (renamed to payment_status). It also renames amount to payment_amount to distinguish it from order amounts downstream.

  • Save all four SQL files.

Why this CTE pattern?

Every staging model uses the same two-CTE structure: source (pulls raw data) and renamed (applies transformations). This consistency makes models predictable and easy to review.

The final select * from renamed outputs the clean version. If you ever need to add a filter or deduplication step, you insert it as a new CTE between source and renamed.

✔️ Awesome, I've got everything!

Great. Double check you have saved all four SQL files before running dbt.

ⓧ I'd like to double check the full code

with source as (

    select * from {{ source('ecommerce', 'raw_orders') }}

),

renamed as (

    select
        order_id,
        customer_id,
        product_id,
        cast(order_date as date) as order_date,
        quantity,
        lower(status) as order_status,
        cast(amount as numeric) as order_amount
    from source

)

select * from renamed
with source as (

    select * from {{ source('ecommerce', 'raw_customers') }}

),

renamed as (

    select
        customer_id,
        first_name,
        last_name,
        concat(first_name, ' ', last_name) as full_name,
        lower(email) as email,
        country,
        cast(created_at as date) as customer_created_at
    from source

)

select * from renamed
with source as (

    select * from {{ source('ecommerce', 'raw_products') }}

),

renamed as (

    select
        product_id,
        product_name,
        lower(category) as category,
        cast(price as numeric) as unit_price,
        supplier
    from source

)

select * from renamed
with source as (

    select * from {{ source('ecommerce', 'raw_payments') }}

),

renamed as (

    select
        payment_id,
        order_id,
        lower(payment_method) as payment_method,
        cast(amount as numeric) as payment_amount,
        cast(payment_date as date) as payment_date,
        lower(status) as payment_status
    from source

)

select * from renamed
Run and validate the staging layer

Your staging models are defined. Now you will materialize them as views in BigQuery and run the source tests to confirm your raw data passes quality checks.

  • Materialize all staging models by running this command:
dbt run --select staging

What does this command do?

The --select staging flag tells dbt to only run models inside the staging folder. Because your dbt_project.yml configures staging models as +materialized: view, dbt creates BigQuery views instead of tables. Views are cheap and always return fresh data.

  • You should see output showing 4 models created successfully, each with OK status and created sql view model in the log line.
  • Now validate the source data quality by running this command:
dbt test --select source:ecommerce

What does this command do?

The source:ecommerce selector targets only the tests defined in your _sources.yml file. This runs the unique and not_null checks on all four primary key columns.

  • You should see all tests pass with a summary line showing PASS=8 (two tests per source table, four tables).

Seeing test failures or compilation errors?

If dbt cannot find your sources, check that the schema value in _sources.yml matches your profiles.yml dataset name (dbt_dev). Also confirm you ran dbt seed successfully in the previous step.

If you see a unique test failure, your seed data may have been loaded twice. Run dbt seed --full-refresh to reload from scratch.

help me debug why my dbt source tests are failing on BigQuery

Your staging layer is live. You now have four clean, standardized views that any downstream model can safely reference. Next up, you will join these staging models together and add business logic in the intermediate layer.

Build the Intermediate Layer

Your staging layer is working. You have four clean views in BigQuery that standardize raw data into a consistent format.

The problem is that your staging models exist in isolation. An order on its own tells you nothing about how the customer paid or who placed it. The intermediate layer solves this by joining related staging models together and applying business logic before data reaches the final marts.

In this step, get ready to:
  • Create an enriched orders model that joins orders with payments and customers.
  • Add a schema file with tests for the intermediate model.
  • Run and verify the intermediate layer in BigQuery.
Create the enriched orders model

The intermediate layer sits between staging and marts. Its job is to combine cleaned data from multiple staging models and add calculations that don't belong in a final business entity yet.

  • Create a new folder called intermediate inside your models/ directory by running:
mkdir models/intermediate
  • Create a new file called int_orders_enriched.sql inside models/intermediate/ and add this code:
with orders as (

    select * from {{ ref('stg_orders') }}

),

payments as (

    select * from {{ ref('stg_payments') }}

),

customers as (

    select * from {{ ref('stg_customers') }}

),

orders_with_context as (

    select
        orders.order_id,
        orders.customer_id,
        orders.product_id,
        orders.order_date,
        orders.quantity,
        orders.order_status,
        orders.order_amount,
        payments.payment_method,
        payments.payment_status,
        customers.full_name as customer_name,
        customers.country as customer_country,
        row_number() over (
            partition by orders.customer_id
            order by orders.order_date
        ) as customer_order_number
    from orders
    left join payments on orders.order_id = payments.order_id
    left join customers on orders.customer_id = customers.customer_id

)

select * from orders_with_context

What does this code do?

This model pulls data from three staging models using {{ ref() }}, which tells dbt about the dependency between models.

  • The left join to payments adds payment_method and payment_status to every order.
  • The left join to customers brings in customer_name and customer_country.
  • The row_number() window function computes customer_order_number, which tracks whether this is a customer's 1st, 2nd, or 3rd order. The marts layer uses this to flag first-time buyers.

Why use an intermediate layer instead of joining directly in the marts?

Intermediate models keep your marts clean and focused. Without them, every mart that needs payment or customer data would duplicate the same join logic.

By centralizing the join here, you write it once and reference it anywhere. If the join logic ever changes, you update one file instead of hunting through every mart.

  • Save int_orders_enriched.sql.
Add a schema file with tests

Every layer in your dbt project needs a YAML schema file that documents the models and declares tests. This ensures data quality is validated automatically whenever you run the test suite.

  • Create a new file called _int_models.yml inside models/intermediate/ and add this configuration:
version: 2

models:
  - name: int_orders_enriched
    description: Orders enriched with payment details, customer context, and order sequence number per customer
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null

What does this file do?

  • The description field documents the model's purpose. This surfaces in dbt's auto-generated documentation site.
  • The unique and not_null tests on order_id guarantee that the join did not accidentally duplicate or lose rows.
  • Save _int_models.yml.

✔️ Awesome, I've got everything!

Great. Double check you've saved both files before moving on.

ⓧ I'd like to double check the full code

Here is the full code for both intermediate files:

with orders as (

    select * from {{ ref('stg_orders') }}

),

payments as (

    select * from {{ ref('stg_payments') }}

),

customers as (

    select * from {{ ref('stg_customers') }}

),

orders_with_context as (

    select
        orders.order_id,
        orders.customer_id,
        orders.product_id,
        orders.order_date,
        orders.quantity,
        orders.order_status,
        orders.order_amount,
        payments.payment_method,
        payments.payment_status,
        customers.full_name as customer_name,
        customers.country as customer_country,
        row_number() over (
            partition by orders.customer_id
            order by orders.order_date
        ) as customer_order_number
    from orders
    left join payments on orders.order_id = payments.order_id
    left join customers on orders.customer_id = customers.customer_id

)

select * from orders_with_context
version: 2

models:
  - name: int_orders_enriched
    description: Orders enriched with payment details, customer context, and order sequence number per customer
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
Run and verify the intermediate layer
  • Materialize the intermediate model by running:
dbt run --select intermediate
  • You should see output confirming that int_orders_enriched was created as a view.

Why is this a view?

Your dbt_project.yml sets +materialized: view for the intermediate layer. Views are ideal here because intermediate models are internal building blocks, not queried directly by analysts. They stay fresh without occupying storage.

  • Open the BigQuery console in your browser.
  • In the Explorer panel on the left, expand your project and then your dbt_dev dataset.
  • Click on int_orders_enriched to open it, then click the Preview tab.
  • You should see 20 rows with columns including payment_method, customer_name, and customer_order_number.

Seeing a compilation error?

  • Check that your {{ ref() }} calls use the exact staging model names: stg_orders, stg_payments, stg_customers.
  • Make sure the file is saved in models/intermediate/ and not in the staging/ folder.
  • Confirm that your staging models ran successfully in the previous step. Run dbt run --select staging again if you're unsure.

Help me debug my dbt intermediate model compilation error.

Your intermediate layer is live. Every order now carries its payment context, customer details, and sequence number. Next up, you'll use this enriched data to build the star schema with dimension and fact tables in the marts layer.

Build the Star Schema Marts Layer

Your intermediate layer now holds enriched order data ready for consumption. The final layer in a dbt project is the marts layer, where you build business-ready entities that analysts and BI tools query directly.

In this step, you will build a star schema consisting of two dimension tables (dim_customers and dim_products) and one fact table (fct_orders). Dimension tables describe the "who" and "what" of your business. The fact table records the measurable events that connect them.

In this step, get ready to:
  • Build a customer dimension with lifetime metrics and value segmentation.
  • Build a product dimension with aggregated sales performance metrics.
  • Build a fact table that connects orders to both dimensions with an incremental strategy.
Create the customer dimension model

A customer dimension enriches raw customer data with behavioral metrics. By aggregating order history into the dimension, downstream analysts can segment customers without writing complex joins every time they query.

  • Create a new folder called marts inside your models/ directory.
  • Create a new file called dim_customers.sql inside models/marts/ and paste the following code:
with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('int_orders_enriched') }}

),

-- Aggregate order metrics per customer
customer_metrics as (

    select
        customer_id,
        count(order_id) as total_orders,
        sum(order_amount) as lifetime_value,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date
    from orders
    where order_status != 'cancelled'
    group by customer_id

),

-- Join customer attributes with their aggregated metrics
final as (

    select
        customers.customer_id,
        customers.full_name,
        customers.email,
        customers.country,
        customers.customer_created_at,
        coalesce(customer_metrics.total_orders, 0) as total_orders,
        coalesce(customer_metrics.lifetime_value, 0) as lifetime_value,
        customer_metrics.first_order_date,
        customer_metrics.last_order_date,
        case
            when customer_metrics.lifetime_value >= 500 then 'high_value'
            when customer_metrics.lifetime_value >= 200 then 'medium_value'
            else 'low_value'
        end as customer_segment
    from customers
    left join customer_metrics
        on customers.customer_id = customer_metrics.customer_id

)

select * from final

What does this code do?

This model pulls from two upstream sources and combines them into a single customer entity.

  • The customer_metrics CTE aggregates each customer's order history into lifetime totals, filtering out cancelled orders.
  • coalesce(..., 0) ensures customers with no orders get a zero instead of null, which makes downstream filtering reliable.
  • The customer_segment CASE statement classifies customers as high_value (>= $500 lifetime), medium_value (>= $200), or low_value.
  • Save dim_customers.sql.

What is a star schema?

A star schema organizes your data warehouse into two types of tables. Dimension tables (prefixed dim_) describe entities like customers and products. Fact tables (prefixed fct_) record measurable events like orders.

The fact table sits at the center with foreign keys pointing outward to each dimension. When you diagram it, the shape looks like a star.

Create the product dimension model

The product dimension enriches the product catalog with sales performance metrics. This lets analysts answer questions like "which products drive the most revenue" without joining raw tables.

  • Create a new file called dim_products.sql inside models/marts/ and paste the following code:
with products as (

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

),

-- Aggregate sales metrics per product
product_orders as (

    select
        product_id,
        count(order_id) as times_ordered,
        sum(quantity) as total_units_sold,
        sum(order_amount) as total_revenue
    from {{ ref('int_orders_enriched') }}
    where order_status != 'cancelled'
    group by product_id

),

-- Join product attributes with their sales metrics
final as (

    select
        products.product_id,
        products.product_name,
        products.category,
        products.unit_price,
        products.supplier,
        coalesce(product_orders.times_ordered, 0) as times_ordered,
        coalesce(product_orders.total_units_sold, 0) as total_units_sold,
        coalesce(product_orders.total_revenue, 0) as total_revenue
    from products
    left join product_orders
        on products.product_id = product_orders.product_id

)

select * from final

What does this code do?

  • The product_orders CTE counts how many times each product was ordered, how many total units were sold, and total revenue generated.
  • Cancelled orders are excluded so the metrics reflect actual completed business.
  • The left join preserves products that have never been ordered, giving them zeros via coalesce.
  • Save dim_products.sql.
Create the fact table and materialize the marts

The fact table is the center of your star schema. It records every order event at the most granular level, with foreign keys that point to each dimension table.

  • Create a new file called fct_orders.sql inside models/marts/ and paste the following code:
-- Configure this model for incremental loading
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

with enriched_orders as (

    select * from {{ ref('int_orders_enriched') }}

)

select
    order_id,
    customer_id,
    product_id,
    order_date,
    quantity,
    order_status,
    order_amount,
    payment_method,
    payment_status,
    customer_name,
    customer_country,
    customer_order_number,
    case
        when customer_order_number = 1 then true
        else false
    end as is_first_order
from enriched_orders

{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}

What does this code do?

  • The config block sets this model to incremental materialization with a merge strategy. On subsequent runs, only new rows (based on order_date) get processed instead of rebuilding the entire table.
  • The is_first_order flag marks whether this was the customer's first-ever purchase, derived from the customer_order_number field you built in the intermediate layer.
  • The {% if is_incremental() %} block only activates on subsequent runs. The first run always processes all rows.
  • Save fct_orders.sql.

Now you need a schema file to document these models and define their tests. This file will include relationship tests that validate your star schema's foreign keys.

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

models:
  - name: dim_customers
    description: Customer dimension with lifetime metrics and value segmentation
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_segment
        description: Value segment based on lifetime spend
        data_tests:
          - accepted_values:
              values: ['high_value', 'medium_value', 'low_value']

  - name: dim_products
    description: Product dimension with aggregated sales performance metrics
    columns:
      - name: product_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: fct_orders
    description: Order fact table at order-level grain with foreign keys to dimensions
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to dim_customers
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: product_id
        description: Foreign key to dim_products
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_products')
              field: product_id
      - name: order_amount
        description: Total order amount in USD
        data_tests:
          - not_null

What do the relationships tests do?

The relationships tests validate referential integrity. They check that every customer_id in fct_orders actually exists in dim_customers, and the same for product_id and dim_products.

This catches broken joins early. If an order references a customer that does not exist in the dimension, the test fails before bad data reaches your dashboards.

  • Save _marts_models.yml.
  • Materialize all three mart models by running this command in your terminal:
dbt run --select marts
  • You should see output confirming all three models completed successfully: dim_customers, dim_products, and fct_orders created as tables in BigQuery.
  • Open the BigQuery console and navigate to your dbt_dev dataset. You should see dim_customers, dim_products, and fct_orders listed as tables.

Seeing a compilation error?

  • Check that all four files (dim_customers.sql, dim_products.sql, fct_orders.sql, _marts_models.yml) are inside the models/marts/ directory, not in models/ directly.
  • Ensure ref('int_orders_enriched') and ref('stg_customers') match the model names from your staging and intermediate layers exactly.
  • If you see a YAML parsing error, check indentation in _marts_models.yml. YAML is whitespace-sensitive and requires consistent spacing.

Help me debug my dbt marts compilation error

✔️ Awesome, I've got everything!

Great. Double check you have saved all four files in models/marts/.

ⓧ I'd like to double check the full code

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('int_orders_enriched') }}

),

customer_metrics as (

    select
        customer_id,
        count(order_id) as total_orders,
        sum(order_amount) as lifetime_value,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date
    from orders
    where order_status != 'cancelled'
    group by customer_id

),

final as (

    select
        customers.customer_id,
        customers.full_name,
        customers.email,
        customers.country,
        customers.customer_created_at,
        coalesce(customer_metrics.total_orders, 0) as total_orders,
        coalesce(customer_metrics.lifetime_value, 0) as lifetime_value,
        customer_metrics.first_order_date,
        customer_metrics.last_order_date,
        case
            when customer_metrics.lifetime_value >= 500 then 'high_value'
            when customer_metrics.lifetime_value >= 200 then 'medium_value'
            else 'low_value'
        end as customer_segment
    from customers
    left join customer_metrics
        on customers.customer_id = customer_metrics.customer_id

)

select * from final
with products as (

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

),

product_orders as (

    select
        product_id,
        count(order_id) as times_ordered,
        sum(quantity) as total_units_sold,
        sum(order_amount) as total_revenue
    from {{ ref('int_orders_enriched') }}
    where order_status != 'cancelled'
    group by product_id

),

final as (

    select
        products.product_id,
        products.product_name,
        products.category,
        products.unit_price,
        products.supplier,
        coalesce(product_orders.times_ordered, 0) as times_ordered,
        coalesce(product_orders.total_units_sold, 0) as total_units_sold,
        coalesce(product_orders.total_revenue, 0) as total_revenue
    from products
    left join product_orders
        on products.product_id = product_orders.product_id

)

select * from final
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

with enriched_orders as (

    select * from {{ ref('int_orders_enriched') }}

)

select
    order_id,
    customer_id,
    product_id,
    order_date,
    quantity,
    order_status,
    order_amount,
    payment_method,
    payment_status,
    customer_name,
    customer_country,
    customer_order_number,
    case
        when customer_order_number = 1 then true
        else false
    end as is_first_order
from enriched_orders

{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}
version: 2

models:
  - name: dim_customers
    description: Customer dimension with lifetime metrics and value segmentation
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_segment
        description: Value segment based on lifetime spend
        data_tests:
          - accepted_values:
              values: ['high_value', 'medium_value', 'low_value']

  - name: dim_products
    description: Product dimension with aggregated sales performance metrics
    columns:
      - name: product_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: fct_orders
    description: Order fact table at order-level grain with foreign keys to dimensions
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to dim_customers
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: product_id
        description: Foreign key to dim_products
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_products')
              field: product_id
      - name: order_amount
        description: Total order amount in USD
        data_tests:
          - not_null

Your star schema is live in BigQuery. Next up, you will add comprehensive data quality tests that validate your entire pipeline before any data reaches a dashboard.

Add Data Quality Tests

Your star schema is built. You have dimension tables, a fact table, and a full pipeline from raw data to business-ready models.

But how do you know the data is correct? In production, bad data slips in silently. A missing customer ID breaks a join. A negative order amount skews revenue metrics. Data quality tests catch these issues before they reach your BI tools.

In this step, get ready to:
  • Review the generic tests already defined in your YAML schema files.
  • Create a singular test that validates business logic across the project.
  • Run the full test suite and confirm a 100% pass rate.
Review your generic tests

dbt has four built-in generic tests that you declare directly in YAML schema files. You already added these tests throughout your project as you built each layer.

  • Open models/marts/_marts_models.yml in VS Code.
  • Review the tests defined under each column.

What do these generic tests check?

  • unique ensures no duplicate values exist in a column (critical for primary keys).
  • not_null ensures every row has a value in the column (no gaps in required fields).
  • accepted_values ensures a column only contains values from a specific list (catches typos and invalid statuses).
  • relationships ensures every foreign key value exists in the referenced dimension table (validates referential integrity).

The relationships tests on fct_orders.customer_id and fct_orders.product_id are particularly important. They guarantee that every order points to a real customer and a real product in your dimension tables.

Where else are generic tests defined?

Your project has generic tests scattered across three YAML files. Source-level tests live in models/staging/_sources.yml (unique and not_null on all raw primary keys). Staging-level tests live in models/staging/_stg_models.yml (plus accepted_values on order_status and payment_status).

Intermediate-level tests live in models/intermediate/_int_models.yml (unique and not_null on order_id). Together these form a comprehensive safety net across every layer of your pipeline.

Create a singular test for business logic

Generic tests handle structural checks. But what about business rules that span multiple columns or require custom SQL? That is where singular tests come in.

A singular test is a standalone .sql file that returns rows which violate a rule. If the query returns zero rows, the test passes. If it returns any rows, those are the failing records.

  • Create a new file called assert_positive_order_amount.sql inside your tests/ directory (at the project root, next to models/ and seeds/).
  • Add the following SQL to the file:
select
    order_id,
    order_amount
from {{ ref('fct_orders') }}
where order_amount <= 0

What does this test do?

This query finds any orders where the amount is zero or negative. In a real e-commerce system, every completed order should have a positive dollar value. If this query returns rows, something is wrong with the source data or transformation logic.

The {{ ref('fct_orders') }} function ensures dbt knows this test depends on the fct_orders model. It will appear in your lineage graph and run in the correct order.

  • Save tests/assert_positive_order_amount.sql.

Not sure where the tests folder is?

The tests/ directory sits at the same level as models/, seeds/, and dbt_project.yml. If it does not exist yet, create it. Check that dbt_project.yml has test-paths: ["tests"] listed.

Why can't dbt find my singular test?

Run the full test suite

Now you will run every test in the project at once. This includes source tests, staging tests, intermediate tests, marts tests, and your new singular test.

  • Execute all tests across the project by running:
dbt test

What does this command do?

The dbt test command runs every data test defined in your project. It executes the generic tests from your YAML files and the singular tests from your tests/ directory.

Each test compiles to a SQL query that BigQuery runs against your actual data. A test passes when it returns zero rows (no violations found). A test fails when it returns one or more rows.

  • Confirm you see all tests pass with zero errors. Your output summary should show PASS for every test and ERROR=0 in the final line.

Seeing test failures?

If a relationships test fails, it means fct_orders contains a customer_id or product_id that does not exist in the corresponding dimension table. Re-run dbt run --select marts to rebuild all mart tables, then try dbt test again.

If an accepted_values test fails, check that your staging models are using lower() to standardize values. Case mismatches are the most common cause.

Help me debug my failing dbt tests

✔️ Awesome, I've got everything!

Great. Double check you have saved tests/assert_positive_order_amount.sql and that all tests pass.

ⓧ I'd like to double check the full code

Here is the singular test file you created in this step:

select
    order_id,
    order_amount
from {{ ref('fct_orders') }}
where order_amount <= 0

The _marts_models.yml file should still look exactly like this (unchanged from the previous step):

version: 2

models:
  - name: dim_customers
    description: Customer dimension with lifetime metrics and value segmentation
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_segment
        description: Value segment based on lifetime spend
        data_tests:
          - accepted_values:
              values: ['high_value', 'medium_value', 'low_value']

  - name: dim_products
    description: Product dimension with aggregated sales performance metrics
    columns:
      - name: product_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: fct_orders
    description: Order fact table at order-level grain with foreign keys to dimensions
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to dim_customers
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: product_id
        description: Foreign key to dim_products
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_products')
              field: product_id
      - name: order_amount
        description: Total order amount in USD
        data_tests:
          - not_null

Your data quality safety net is in place. Every time you run dbt test, dbt validates your entire pipeline from sources through to the final mart tables. Next up, you will generate documentation and explore the full lineage of your project.

Generate Documentation and Explore Lineage

Your project now has comprehensive data quality tests guarding every layer. A teammate could run dbt test and instantly know whether the pipeline is healthy.

Testing tells you if the data is correct. Documentation tells you what the data means. In this step, you will generate a browsable documentation site from your YAML descriptions and explore the lineage graph that visualizes how data flows through your project.

In this step, get ready to:
  • Review the descriptions already defined in your YAML schema files.
  • Generate a documentation catalog and launch the docs site locally.
  • Explore the lineage graph to trace data flow end-to-end.
Review your model descriptions

Throughout this project, you have been adding description fields to your YAML schema files. These descriptions are what powers dbt's auto-generated documentation site.

  • Open models/staging/_sources.yml in VS Code and confirm that each source table and primary key column has a description field.
  • Open models/staging/_stg_models.yml and confirm each staging model has a description.
  • Open models/marts/_marts_models.yml and confirm the mart models have descriptions on both the model and key columns.

Why do descriptions matter?

When you run dbt docs generate, dbt reads every description field from your YAML files and compiles them into a searchable documentation catalog. This means anyone on your team can look up what a column means without reading the SQL.

In production teams, model descriptions are treated like code. They get reviewed in pull requests and kept up to date as logic changes.

Generate the documentation site

dbt compiles all of your model descriptions, column metadata, and dependency relationships into a static documentation site you can browse locally.

  • Generate the documentation catalog by running this command:
dbt docs generate

What does this command do?

This reads all your YAML schema files, model SQL files, and project configuration. It produces a catalog.json and manifest.json in the target/ directory. These JSON files contain every piece of metadata about your project.

  • Launch the local documentation site by running:
dbt docs serve

What does this command do?

This starts a local web server on port 8080 and opens your browser automatically. The site displays all of your model descriptions, column documentation, and a visual lineage graph.

  • Your browser should open to http://localhost:8080. You should see a documentation homepage listing all of your project's models.

Browser didn't open?

If your browser did not open automatically, manually navigate to http://localhost:8080 in your browser.

If the port is already in use, stop any other process on that port or specify a different one with dbt docs serve --port 8081.

Help me troubleshoot dbt docs serve not loading in my browser.

Explore the lineage graph

The lineage graph is the most powerful feature of dbt's documentation. It visualizes every dependency in your project as a directed acyclic graph (DAG).

  • In the documentation site, click the blue icon in the bottom-right corner of the page to open the lineage graph.
  • You should see your full pipeline visualized: the four source tables on the left, four staging views, the intermediate model in the middle, and the three mart tables on the right.
  • Click on any model node (try fct_orders) to see its description, columns, and the models it depends on.
  • Use the --select filter at the top of the lineage view to type +fct_orders and see only the upstream dependencies of the fact table.

What is the lineage graph showing?

Each node represents a model or source. Each arrow represents a ref() or source() call in your SQL. The graph proves that your pipeline has no circular dependencies and shows exactly which models are affected when upstream data changes.

This is the same lineage visualization that production data teams use to do impact analysis before making changes to shared models.

  • When you are done exploring, go back to your terminal and press Ctrl+C to stop the documentation server.

Your project now has living documentation that stays in sync with your code. Next up, you will convert your fact table to an incremental model for production-scale performance.

Implement Incremental Modeling

Your star schema is tested, documented, and ready for production queries. But there is a scalability problem lurking beneath the surface.

Every time you run dbt run, your fct_orders table rebuilds from scratch. With 20 rows that takes seconds, but in production fact tables grow to millions or billions of rows. dbt's incremental materialization solves this by processing only new data on each run.

In this step, get ready to:
  • Understand how the incremental configuration in your fact table works.
  • Run a full-refresh build followed by an incremental run.
  • Verify the MERGE statement in dbt's compiled SQL output.
Review the incremental configuration

Your fct_orders.sql file already contains the incremental configuration you added when building the marts layer. Let's walk through what each piece does.

  • Open models/marts/fct_orders.sql in your editor.
  • Look at the config block at the top of the file:
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

What does this config do?

  • materialized='incremental' tells dbt to build this model as a table on the first run, then only process new rows on subsequent runs.
  • unique_key='order_id' defines the row identity. If a new row arrives with an existing order_id, dbt updates the existing row instead of creating a duplicate.
  • incremental_strategy='merge' tells BigQuery to use a MERGE statement. This atomically handles both inserts (new rows) and updates (changed rows) in a single operation.
  • Now scroll to the bottom of the file and find the incremental filter:
{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}

How does the incremental filter work?

The is_incremental() macro returns True only when three conditions are met: the table already exists in the database, the model is configured as incremental, and the --full-refresh flag is not passed.

When True, the where clause filters to only rows with an order_date newer than the latest date already in the table. The {{ this }} variable refers to the existing target table in BigQuery.

Why incremental instead of table?

dbt recommends starting with views, moving to tables when queries become too slow, then switching to incremental when the table takes too long to rebuild. For append-heavy fact tables that grow daily, incremental avoids reprocessing historical data that has not changed.

The merge strategy is ideal here because it handles both new inserts and potential updates to existing orders in a single atomic operation.

Run the full-refresh and incremental builds

To demonstrate the incremental behavior, you need to run the model twice. The first run builds the full table from scratch. The second run exercises the incremental path.

  • Build the initial full table by running this command:
dbt run --select fct_orders --full-refresh

What does --full-refresh do?

The --full-refresh flag forces dbt to drop the existing table and rebuild it from scratch. During this run, is_incremental() returns False, so the where filter is skipped and all rows are processed.

  • Confirm you see a success message indicating the model was created.
  • Now run the same model without the --full-refresh flag to trigger an incremental run:
dbt run --select fct_orders
  • Confirm you see a success message indicating the model completed.

What happened on the second run?

This time is_incremental() returned True because the table already exists and no --full-refresh flag was passed. dbt applied the where order_date > (select max(order_date) from ...) filter. Since no new rows exist beyond the current max date, the MERGE processed zero new rows.

Seeing a database error on the second run?

Make sure the first run with --full-refresh completed successfully before running the incremental command. The table must exist in BigQuery for the incremental path to work.

If you see a permissions error, confirm your gcloud authentication is still active by running gcloud auth application-default login again.

help me debug a database error when running my dbt incremental model on BigQuery

Verify the incremental behavior in compiled SQL

dbt writes the actual SQL it sends to BigQuery into the target/run/ directory. Inspecting this file proves that the MERGE statement is being generated correctly.

  • Open the compiled run file at this path in your editor:
target/run/ecommerce_analytics/models/marts/fct_orders.sql
  • Look for a merge statement near the top of the file. You should see BigQuery-specific SQL that merges incoming rows into the existing fct_orders table using order_id as the join key.

What should I see in the compiled SQL?

The file contains the fully resolved SQL that dbt sent to BigQuery on the last run. For an incremental run, this includes a MERGE statement with a WHEN MATCHED THEN UPDATE clause and a WHEN NOT MATCHED THEN INSERT clause.

You will also see that {{ ref('int_orders_enriched') }} has been compiled into the actual BigQuery table reference, and {{ this }} has been replaced with the full path to fct_orders in your dataset.

  • You can also check the target/compiled/ directory for the SELECT-only version of the query (without the MERGE wrapper). This is useful for debugging the select logic independently.

Don't edit files in target/

The target/ directory is auto-generated by dbt on every run. Any edits you make there will be overwritten. Always edit your source files in models/ instead.

Your analytics platform now handles production-scale workloads efficiently. Instead of rebuilding the entire fact table on every run, dbt processes only the newest orders. Next up, you will tackle a Secret Mission that tracks how customer segments change over time.

Secret mission

Implement SCD Type 2 with dbt Snapshots

In production data warehouses, dimensions change over time. A customer might shift from "low_value" to "high_value" as they buy more. Can you track these changes historically using dbt's snapshot feature?

Clean Up Your Resources

Clean Up Your Resources

This project uses Google BigQuery's Free Tier, which includes 1 TB of processed query data and 10 GB of storage per month at no cost. As long as you stay within these limits, there are no ongoing charges.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • BigQuery dataset (dbt_dev) with all tables, views, and snapshot tables.
  • GCP project with billing account enabled.
  • Local project files (ecommerce_analytics folder and Python virtual environment).

Keep everything running

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

  • Your BigQuery dataset stays within the Free Tier (1 TB queries/month, 10 GB storage). The seed data and models from this project use a negligible amount of storage.
  • Your local files remain on your machine.
  • Your GCP billing account is attached but will not incur charges while you stay within Free Tier limits.

Pause - I'll come back to this later

Shut down running processes to free up local memory. Your BigQuery resources cost nothing within Free Tier limits, so they can stay.

  • Deactivate your Python virtual environment by running:
deactivate
  • Close the dbt docs server if it is still running by pressing Ctrl+C in the terminal where you launched it.
  • Your BigQuery dataset and GCP project remain available for when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each item below to fully clean up.

Delete the BigQuery dataset
  • Delete the dbt_dev dataset and all its tables by running this command in your terminal:
bq rm -r -f [[GCP_PROJECT_ID="your-gcp-project-id"]]:dbt_dev

What does this command do?

  • bq rm is the BigQuery CLI command to remove a resource.
  • -r recursively deletes all tables, views, and snapshots inside the dataset.
  • -f skips the confirmation prompt.
Delete the GCP project (optional)
  • If you no longer need the GCP project for any other work, you can shut it down entirely.
  • Open the Google Cloud Console at console.cloud.google.com.
  • Navigate to IAM & Admin in the left sidebar, then click Settings.
  • Scroll to the bottom and click Shut down.
  • Enter your project ID to confirm, then click Shut down anyway.

What happens when you shut down a project?

Google Cloud gives you a 30-day grace period. During that time you can restore the project if you change your mind. After 30 days, the project and all its resources are permanently deleted.

Delete local project files
  • Remove the entire project folder from your Desktop by running:

macOS

rm -rf ~/Desktop/ecommerce_analytics

Windows

rmdir /s /q %USERPROFILE%\Desktop\ecommerce_analytics

This removes the dbt project files, virtual environment, and all generated artifacts.

That's a Wrap!

That's a Wrap!

Nice work! You just built a production-grade analytics platform using dbt and Google BigQuery. This is the exact architecture that senior data engineers implement at companies running real analytics workloads.

You've learned how to:

  • Designed a layered dbt architecture (staging, intermediate, marts) that transforms raw e-commerce data into a business-ready star schema with dim_customers, dim_products, and fct_orders.
  • Added comprehensive data quality testing with generic tests, referential integrity checks, and singular tests to ensure 100% data reliability.
  • Implemented incremental modeling with a merge strategy for production-scale performance, and generated auto-documentation with a full lineage graph showing end-to-end data flow.
  • Tracked dimension changes over time using SCD Type 2 snapshots to preserve historical customer segment data.

Ready to quiz yourself?