dbt Transformation Layer for Data Warehouse

Build tested, documented dbt models on a SQL Server star schema.

Introduction

30 Second Summary

Imagine your team builds a data warehouse with complex transformations, but a new analyst joins and has no way to tell which tables are trustworthy, what logic produced them, or whether the numbers are even correct. That gap between "data exists" and "data you can confidently report on" is where most data teams struggle.

In this project, you will build a dbt transformation layer on top of the star schema data warehouse you built in Project 1. You will turn raw tables into tested, documented, version-controlled reporting marts that any team member can understand and trust.

What You'll Build

You will open your browser to a locally served documentation site, click through an interactive lineage graph that traces data from your raw star schema tables through staging models into two reporting marts, and see every column described and every test listed alongside its pass/fail status.

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

  • A working dbt pipeline you can trigger with a single command, watching models materialize, tests validate, and any data quality issue surface immediately with a clear failure message.
  • Two reporting marts you can query in SSMS that produce the exact cross-border revenue and customer profitability numbers that took complex manual SQL in Project 1, now generated from reusable model files.
  • Automated data tests that catch a real SCD Type 2 uniqueness trap in your dimensional model, proving the tests actually protect against incorrect assumptions about your data.
  • An auto-generated documentation site with a lineage DAG showing how data flows from sources through staging into business-ready reports, shareable as a Git repository where any team member can read the YAML and contribute.
  • Secret Mission: Write a custom singular test that validates revenue consistency between your fact table and reporting mart, and implement a full dbt build workflow that runs models and tests in dependency order.

Are there any prerequisites?

Yes. This project assumes you have completed Project 1 (Build a Star Schema Data Warehouse) with the BalticDW fully populated on SQL Server 2025. You should already have Python 3.13, SQL Server 2025, SSMS 22, ODBC Driver 18, and VS Code installed from that project.

Before We Start

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

Your BalticDW star schema from Project 1 works, but right now the transformations live in raw Python scripts with no tests, no documentation, and no version control built in. This project changes that by adding a professional transformation layer on top.

Install dbt and Initialize the Project

Your Python ETL pipeline from Project 1 loads data into BalticDW, but it has no tests, no documentation, and no version control built in. If a new team member joins, they have to read raw Python to understand the transformations.

dbt solves this by making transformations declarative SQL files that are automatically tested and documented. In this step, you will install dbt, scaffold the project structure, and verify it can connect to your database.

In this step, get ready to:
  • Install dbt-core and the SQL Server adapter into a virtual environment.
  • Scaffold the project with dbt init and configure the database connection.
  • Verify the connection by running dbt debug.
Install dbt and scaffold the project

dbt-core is the transformation engine. The dbt-sqlserver adapter teaches dbt how to communicate with SQL Server specifically. You will install both into a Python virtual environment to keep your project dependencies isolated.

Why a version pin?

Since dbt-core 2.0.0 alpha was published to PyPI, a plain pip install dbt-sqlserver can accidentally pull in the unstable 2.0 alpha. Pinning "dbt-core<2.0" explicitly ensures you get the latest stable release (1.12.0).

  • Press the Windows key to open your search bar.
  • Type Command Prompt and press Enter to open it.
  • Move to your Desktop by running this command:
cd %USERPROFILE%\Desktop
  • Create a temporary virtual environment to bootstrap the dbt CLI by running:
python -m venv dbt_setup
dbt_setup\Scripts\activate
  • Install dbt-core and the SQL Server adapter by running:
pip install "dbt-core<2.0" dbt-sqlserver

The install takes about a minute. When it finishes, you should see a line reading "Successfully installed" followed by a list of packages including dbt-core-1.12.0 and dbt-sqlserver-1.10.1.

Seeing a resolution error?

If pip tries to install dbt-core 2.0.0 alpha, make sure you included the quotes around "dbt-core<2.0". The quotes prevent your shell from interpreting the < character as a redirect.

help me fix my dbt installation error

  • Scaffold the dbt project by running:
dbt init baltic_dw --skip-profile-setup

What does this command do?

dbt init baltic_dw creates a new folder called baltic_dw on your Desktop containing the standard dbt project structure: folders for models, tests, seeds, macros, snapshots, and a starter dbt_project.yml.

The --skip-profile-setup flag skips the interactive connection prompts. You will configure the connection manually in the next substep.

You should see output confirming your new dbt project was created in the baltic_dw folder.

  • Deactivate the temporary environment and clean it up by running:
deactivate
rmdir /S /Q dbt_setup
  • Move into the project folder and create the permanent virtual environment by running:
cd baltic_dw
python -m venv .venv
.venv\Scripts\activate
pip install "dbt-core<2.0" dbt-sqlserver

This .venv inside baltic_dw is your project's permanent environment. All future dbt commands will run from here.

  • Confirm dbt is available by running:
dbt --version

You should see output showing installed: 1.12.0 and the sqlserver adapter listed underneath.

dbt command not found?

Make sure your virtual environment is activated. Your terminal prompt should show (.venv) at the beginning. If not, run .venv\Scripts\activate again.

help me troubleshoot why dbt is not found after activating my venv

  • Delete the sample models that dbt init generated and create your own folder structure by running:
rmdir /S /Q models\example
mkdir models\staging
mkdir models\marts
  • Confirm the new folders exist by running:
dir models

You should see staging and marts listed as subdirectories inside models.

Configure the connection and project settings

dbt stores connection details in a file called profiles.yml located at ~/.dbt/profiles.yml. This is separate from your project so that credentials never get checked into version control.

  • Press the Windows key, type VS Code, and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the baltic_dw folder, and click Select Folder.

You should see the project structure in the Explorer sidebar: folders for models, tests, macros, and the dbt_project.yml file.

  • Open dbt_project.yml from the Explorer sidebar and replace its entire contents with the following:
name: 'baltic_dw'
version: '1.0.0'

profile: 'baltic_dw'

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

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

models:
  baltic_dw:
    staging:
      +materialized: view
      +schema: dbt_dev
    marts:
      +materialized: table
      +schema: dbt_dev

What does dbt_project.yml do?

This file is the heart of every dbt project. It tells dbt the project name, which profile to use for database connections, and where to find your SQL files.

  • profile: 'baltic_dw' links the project to a connection you will define next in profiles.yml.
  • The models: section tells dbt that anything in models/staging/ materializes as a view, and anything in models/marts/ materializes as a table.
  • +schema: dbt_dev places all dbt-generated objects in a separate schema from your raw dbo tables. This keeps sources and transformations cleanly separated.
  • Save dbt_project.yml with Ctrl+S.
  • Create the .dbt directory if it does not already exist by running this in your terminal:
if not exist "%USERPROFILE%\.dbt" mkdir "%USERPROFILE%\.dbt"
  • Create and open the profiles file by running:
code "%USERPROFILE%\.dbt\profiles.yml"
  • Paste the following connection configuration into the file:
baltic_dw:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server'
      server: localhost
      port: 1433
      database: BalticDW
      schema: dbo
      windows_login: true
      trust_cert: true

What do these settings mean?

  • type: sqlserver tells dbt to use the SQL Server adapter.
  • windows_login: true uses your Windows credentials to authenticate. No username or password needed.
  • trust_cert: true accepts the self-signed certificate on your local SQL Server instance.
  • database: BalticDW points dbt at the data warehouse you built in Project 1.
  • Save profiles.yml with Ctrl+S.
Verify the connection with dbt debug

The dbt debug command checks your installation, finds your project file, reads your profile, and attempts a real connection to the database. If anything is misconfigured, it tells you exactly which check failed.

  • Before you run this, predict: do you think all checks will pass on the first try, or will one fail?
  • Run the connection check by entering:
dbt debug

You should see a series of green checks ending with "All checks passed!" confirming dbt can find your project, read your profile, and connect to BalticDW.

Seeing a connection failure?

If the connection test fails, check these common causes:

  • Make sure SQL Server is running. Press the Windows key, type services.msc, and confirm SQL Server (MSSQLSERVER) shows Running.
  • Verify the database name in profiles.yml is exactly BalticDW. The name is case-sensitive.
  • Confirm the driver field matches what you installed in Project 1: ODBC Driver 18 for SQL Server.

help me debug my dbt connection failure

✔️ Awesome, I've got everything!

Great. Double check you have saved both dbt_project.yml and profiles.yml.

ⓧ I'd like to double check the full code

Your dbt_project.yml should contain exactly this:

name: 'baltic_dw'
version: '1.0.0'

profile: 'baltic_dw'

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

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

models:
  baltic_dw:
    staging:
      +materialized: view
      +schema: dbt_dev
    marts:
      +materialized: table
      +schema: dbt_dev

Your profiles.yml at ~/.dbt/profiles.yml should contain:

baltic_dw:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server'
      server: localhost
      port: 1433
      database: BalticDW
      schema: dbo
      windows_login: true
      trust_cert: true

Your dbt project is connected and ready. Next up, you will declare your existing star schema tables as dbt sources and build your first staging model that materializes as a queryable view.

Define Sources and Build Your First Model

Your dbt project is connected and ready to transform data. But right now, dbt knows nothing about the tables already sitting in your BalticDW database.

Before you can write transformation logic, you need to tell dbt which tables exist and where to find them. This is what sources do. Once declared, your models can reference them with source() instead of hardcoding table names, and dbt tracks the lineage automatically.

In this step, get ready to:
  • Declare all 5 BalticDW tables as dbt sources.
  • Write a staging model that joins facts to dimensions.
  • Run dbt and verify the view exists in SSMS with 156 rows.
Declare your BalticDW tables as dbt sources

A source in dbt is a YAML declaration that maps your raw database tables into dbt's dependency graph. Once declared, your models can reference them with source() instead of hardcoding table names.

  • Create the staging folder structure by running this command in your terminal:
mkdir models\staging
  • In VS Code, create a new file called __sources.yml inside the models/staging/ folder.
  • Add the source header and the FactSalesOrder table declaration by pasting this code into the file:
sources:
  - name: baltic_dw
    description: "Star schema data warehouse for Baltic FMCG distribution group. Built in Project 1 with Python ETL pipeline."
    database: BalticDW
    schema: dbo
    tables:
      - name: FactSalesOrder
        description: "Sales order fact table with surrogate keys to all dimensions. 156 rows across 3 Baltic countries."
        columns:
          - name: fact_key
            description: "Surrogate primary key for the fact table."
            data_tests:
              - unique
              - not_null
          - name: order_id
            description: "Natural order identifier from source ERP system."
            data_tests:
              - not_null
          - name: order_date_key
            description: "Integer date key in YYYYMMDD format. References DimDate."
            data_tests:
              - not_null
              - relationships:
                  to: source('baltic_dw', 'DimDate')
                  field: date_key

What does this YAML do?

The sources block tells dbt to look in the BalticDW database, dbo schema for these tables. The data_tests key defines assertions dbt will check when you run dbt test later.

The relationships test validates that every order_date_key value in FactSalesOrder actually exists in DimDate. This is dbt's way of enforcing referential integrity.

  • Add the remaining FactSalesOrder columns directly below the order_date_key section:
          - name: product_key
            description: "Surrogate key referencing DimProduct."
            data_tests:
              - not_null
              - relationships:
                  to: source('baltic_dw', 'DimProduct')
                  field: product_key
          - name: customer_key
            description: "Surrogate key referencing DimCustomer. Points to the version of the customer active at order time."
            data_tests:
              - not_null
              - relationships:
                  to: source('baltic_dw', 'DimCustomer')
                  field: customer_key
          - name: subsidiary_key
            description: "Surrogate key referencing DimSubsidiary."
            data_tests:
              - not_null
              - relationships:
                  to: source('baltic_dw', 'DimSubsidiary')
                  field: subsidiary_key
          - name: quantity
            description: "Number of units ordered."
            data_tests:
              - not_null
          - name: unit_price
            description: "Price per unit in EUR."
            data_tests:
              - not_null
          - name: line_total
            description: "Total line value (quantity * unit_price)."
            data_tests:
              - not_null

What are these relationship tests?

Each relationships test acts like a foreign key check. It verifies that every surrogate key in FactSalesOrder (product_key, customer_key, subsidiary_key) actually points to a valid row in the referenced dimension table.

  • Save __sources.yml and verify dbt can parse the FactSalesOrder source by running:
dbt compile

You should see output showing dbt found your source and compiled successfully with no errors.

Seeing a compilation error?

Check that your YAML indentation is consistent. YAML uses spaces, not tabs. Each nested level should be indented by exactly 2 spaces. Also confirm the file is named __sources.yml (two underscores) and lives in models/staging/.

Help me debug this YAML compilation error

  • Now add the DimCustomer table declaration below the FactSalesOrder section. This dimension uses accepted_values tests to validate enum-like columns:
      - name: DimCustomer
        description: "Customer dimension with SCD Type 2 history tracking. Contains both current and expired records."
        columns:
          - name: customer_key
            description: "Surrogate primary key. Unique across all versions of all customers."
            data_tests:
              - unique
              - not_null
          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - not_null
          - name: customer_name
            description: "Customer business name."
          - name: segment
            description: "Customer segment classification."
            data_tests:
              - accepted_values:
                  values: ['Retail', 'HORECA', 'Standard', 'Premium']
          - name: city
            description: "Customer city."
          - name: country_code
            description: "Two-letter country code."
            data_tests:
              - accepted_values:
                  values: ['EE', 'LV', 'LT']
  • Add the final DimCustomer column (is_current) and the DimProduct table directly below:
          - name: is_current
            description: "1 = current version, 0 = expired historical version."
            data_tests:
              - accepted_values:
                  values: [0, 1]
      - name: DimProduct
        description: "Product dimension for FMCG catalog items."
        columns:
          - name: product_key
            description: "Surrogate primary key."
            data_tests:
              - unique
              - not_null
          - name: product_id
            description: "Natural product identifier."
            data_tests:
              - unique
              - not_null
          - name: product_name
            description: "Full product name with size/variant."
          - name: category
            description: "Product category (Oral Care, Personal Care, Tissue, Hygiene, Beverages, Lubricants)."
          - name: brand
            description: "Parent brand name."
  • Save and confirm dbt can still parse all sources by running:
dbt ls --resource-type source

You should see a list of source names that includes FactSalesOrder, DimCustomer, and DimProduct printed to the terminal.

  • Add the DimSubsidiary table below DimProduct:
      - name: DimSubsidiary
        description: "Subsidiary dimension representing each Baltic country operation."
        columns:
          - name: subsidiary_key
            description: "Surrogate primary key."
            data_tests:
              - unique
              - not_null
          - name: country_code
            description: "Two-letter country code (EE, LV, LT)."
            data_tests:
              - unique
              - not_null
              - accepted_values:
                  values: ['EE', 'LV', 'LT']
          - name: country_name
            description: "Full country name."
          - name: subsidiary_name
            description: "Legal entity name of the subsidiary."
  • Add the final dimension table, DimDate, below DimSubsidiary:
      - name: DimDate
        description: "Date dimension covering 2024-2025 with pre-computed calendar attributes."
        columns:
          - name: date_key
            description: "Integer date key in YYYYMMDD format (e.g. 20240115)."
            data_tests:
              - unique
              - not_null
          - name: full_date
            description: "DATE type representation."
          - name: year
            description: "Calendar year."
          - name: quarter
            description: "Calendar quarter (1-4)."
          - name: month
            description: "Calendar month (1-12)."
          - name: month_name
            description: "Full month name (January, February, etc.)."
  • Save __sources.yml and confirm all 5 tables are registered by running:
dbt ls --resource-type source

You should now see all 5 source tables listed: FactSalesOrder, DimCustomer, DimProduct, DimSubsidiary, and DimDate.

Missing sources in the output?

Make sure __sources.yml is saved inside models/staging/ (not in the project root). Also check that each table entry starts with - name: at the correct indentation level (6 spaces from the left margin).

Help me figure out why sources are missing

✔️ Awesome, I've got everything!

Great. Make sure __sources.yml is saved. You now have all 5 BalticDW tables registered as dbt sources.

ⓧ I'd like to double check the full code

The full __sources.yml file is long (over 100 lines). Scroll through yours and confirm these sections appear in order:

  • File header (sources:, name: baltic_dw, database: BalticDW, schema: dbo).
  • FactSalesOrder with 9 columns (fact_key through line_total).
  • DimCustomer with 7 columns (customer_key through is_current).
  • DimProduct with 5 columns (product_key through brand).
  • DimSubsidiary with 4 columns (subsidiary_key through subsidiary_name).
  • DimDate with 6 columns (date_key through month_name).
Write the staging model

Now that dbt knows about your source tables, you can write a model that transforms them. A staging model denormalizes your star schema by joining the fact table to all dimensions into a single flat view.

  • Create a new file called stg_sales_orders.sql inside models/staging/.
  • Add the CTE declarations that pull from each source table:
with fact_orders as (
    select * from {{ source('baltic_dw', 'FactSalesOrder') }}
),

customers as (
    select * from {{ source('baltic_dw', 'DimCustomer') }}
),

products as (
    select * from {{ source('baltic_dw', 'DimProduct') }}
),

subsidiaries as (
    select * from {{ source('baltic_dw', 'DimSubsidiary') }}
),

dates as (
    select * from {{ source('baltic_dw', 'DimDate') }}
),

What does source() do?

The {{ source('baltic_dw', 'FactSalesOrder') }} macro generates the fully qualified table name (BalticDW.dbo.FactSalesOrder) at compile time. It also registers a lineage dependency so dbt knows this model depends on that source table.

Each CTE gives the source a short alias (fact_orders, customers, etc.) that you reference in the join below.

  • Add the joined CTE and final select below the dates CTE:
joined as (
    select
        f.fact_key,
        f.order_id,
        d.full_date as order_date,
        d.year as order_year,
        d.quarter as order_quarter,
        d.month as order_month,
        d.month_name as order_month_name,
        c.customer_id,
        c.customer_name,
        c.segment as customer_segment,
        c.city as customer_city,
        c.country_code as customer_country,
        p.product_id,
        p.product_name,
        p.category as product_category,
        p.brand as product_brand,
        sub.country_name as subsidiary_country,
        sub.subsidiary_name,
        f.quantity,
        f.unit_price,
        f.line_total
    from fact_orders f
    inner join customers c on f.customer_key = c.customer_key
    inner join products p on f.product_key = p.product_key
    inner join subsidiaries sub on f.subsidiary_key = sub.subsidiary_key
    inner join dates d on f.order_date_key = d.date_key
)

select * from joined

What does this joined CTE do?

The joined CTE resolves all surrogate keys into human-readable attributes. Instead of customer_key = 7, the output shows customer_name, customer_segment, and customer_country directly.

The final select * from joined outputs all 21 columns as a flat, queryable view. Downstream marts will use ref('stg_sales_orders') to build on top of this.

  • Save stg_sales_orders.sql.

Seeing a Jinja syntax error?

Make sure each source() call is wrapped in double curly braces: {{ source(...) }}. A missing brace or mismatched quote will cause a compilation failure.

Help me find this Jinja syntax issue

✔️ Awesome, I've got everything!

Great. Make sure stg_sales_orders.sql is saved and move on to run it.

ⓧ I'd like to double check the full code

Your stg_sales_orders.sql should have two main sections. Scroll through and confirm:

  • 5 CTEs at the top (fact_orders, customers, products, subsidiaries, dates) each using source().
  • A joined CTE with 21 columns in the SELECT and 4 INNER JOINs.
  • A final select * from joined at the bottom.
Run dbt and verify in SSMS

Your source declarations and staging model are in place. Time to materialize the view and confirm it contains the expected data.

  • Before you run this, what do you think dbt will create in the database: a table or a view? Think about what you configured in dbt_project.yml for the staging folder.
  • Run the dbt pipeline by executing:
dbt run

You should see output confirming 1 model was created successfully. Because your dbt_project.yml sets +materialized: view for the staging folder, dbt created a view (not a table) in the dbt_dev schema.

Why a view instead of a table?

Views are lightweight. They store no data on disk and always reflect the latest state of the underlying tables. This is ideal for staging models that just clean and join raw data.

Marts (which you build in the next step) will be materialized as tables for dashboard performance.

  • Switch to SSMS and connect to your local SQL Server instance.
  • Run this query to verify the staging view exists and has the correct row count:
SELECT COUNT(*) AS row_count FROM dbt_dev.stg_sales_orders;

You should see 156 returned. This matches the 156 rows in your FactSalesOrder table, confirming the join worked correctly.

Getting 0 rows or a missing object error?

If the view does not exist, check that dbt run completed without errors. Look for a red error line in the terminal output. Common causes: a misspelled table name in __sources.yml, or the schema: dbo line pointing to the wrong schema.

If you see more or fewer than 156 rows, check your join conditions in stg_sales_orders.sql. An incorrect join column (e.g., joining on customer_id instead of customer_key) can duplicate or drop rows.

Help me debug this row count issue

Your staging model is live. Next up, you will build reporting marts that aggregate this denormalized view into the business-ready answers your CEO can query directly.

Build Reporting Marts

In the last step, you built a staging model that joins raw star schema tables into a single denormalized view. Business users, however, don't want to write GROUP BY queries against that view every time they need monthly revenue numbers.

Reporting marts pre-compute those answers as materialized tables. In this step, you'll build two marts that answer the recurring business questions from Project 1: cross-border revenue by country-month, and customer segment profitability by product category.

In this step, get ready to:
  • Create a cross-border revenue mart that aggregates sales by country and month.
  • Create a customer segment profitability mart that aggregates revenue by segment and product category.
  • Run dbt run and verify both mart tables exist in SSMS with correct aggregated data.
Create the cross-border revenue mart

Your staging view already has every column you need. A reporting mart groups and aggregates that data into a shape that answers one specific business question without further SQL.

What is a reporting mart?

A mart is a pre-built summary table designed for a specific audience or report. Instead of forcing a dashboard to run a complex GROUP BY query on every refresh, the mart stores the pre-computed answer.

dbt materializes marts as physical tables so downstream tools query them instantly. Staging models stay as lightweight views.

  • Create a models/marts/ folder inside your dbt project by running:
mkdir models\marts
  • Create a new file called cross_border_revenue.sql inside models/marts/ and paste the following code:
with orders as (
    select * from {{ ref('stg_sales_orders') }}
)

select
    subsidiary_country as country_name,
    customer_country as country_code,
    order_year,
    order_month,
    order_month_name,
    count(*) as order_lines,
    sum(quantity) as total_units,
    sum(line_total) as total_revenue
from orders
group by
    subsidiary_country,
    customer_country,
    order_year,
    order_month,
    order_month_name

What does this code do?

  • ref('stg_sales_orders') tells dbt to pull from your staging model. This creates a dependency link in the lineage graph.
  • The SELECT groups orders by subsidiary country, customer country, year, and month, then counts lines and sums revenue.
  • This produces exactly the cross-border revenue breakdown you wrote manually in Project 1.

Why ref() instead of source()?

The source() macro points to raw database tables you declared in __sources.yml. The ref() macro points to other dbt models. Marts reference staging models (not raw tables) so dbt knows to build staging first.

  • Save cross_border_revenue.sql.
  • Now create the YAML schema file cross_border_revenue.yml in the same models/marts/ folder with the following content:
models:
  - name: cross_border_revenue
    description: "Monthly revenue aggregated by country. The single source of truth for cross-border sales reporting. Replaces manual SQL queries from Project 1."
    columns:
      - name: country_code
        description: "Two-letter country code (EE, LV, LT)."
        data_tests:
          - not_null
          - accepted_values:
              values: ['EE', 'LV', 'LT']
      - name: country_name
        description: "Full country name (Estonia, Latvia, Lithuania)."
        data_tests:
          - not_null
      - name: order_year
        description: "Calendar year of the orders."
        data_tests:
          - not_null
      - name: order_month
        description: "Calendar month number (1-12)."
        data_tests:
          - not_null
      - name: total_revenue
        description: "Sum of line_total for all orders in this country-month."
        data_tests:
          - not_null

What does this YAML do?

  • The description fields document what this mart represents. dbt uses these descriptions in its auto-generated documentation site.
  • The data_tests define assertions dbt checks when you run tests. These ensure no nulls slip into key columns and that country codes stay valid.
  • Save cross_border_revenue.yml.
  • Run dbt run to materialize the new mart by running:
dbt run

You should see dbt build both stg_sales_orders (as a view) and cross_border_revenue (as a table), both with OK status.

Seeing a compilation error?

  • Check that ref('stg_sales_orders') is spelled exactly as your staging file name (without the .sql extension).
  • Verify the file is saved inside models/marts/ and not inside models/staging/.
  • Make sure the double curly braces {{ }} are present around the ref() call.

Help me debug a dbt compilation error in my cross_border_revenue mart

  • Switch to SSMS and run this query to confirm the mart table exists with data:
SELECT TOP 5 * FROM dbt_dev.cross_border_revenue
ORDER BY order_year, order_month;

You should see rows with country names, country codes, year, month, and aggregated revenue figures.

Create the customer segment profitability mart

The second mart answers a different business question: which customer segments buy which product categories, and how much revenue does each combination generate?

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

select
    customer_segment,
    product_category,
    count(*) as order_lines,
    sum(quantity) as total_units,
    sum(line_total) as total_revenue,
    round(avg(line_total), 2) as avg_order_value
from orders
group by
    customer_segment,
    product_category

What does this code do?

  • Groups all orders by customer_segment (Retail, HORECA, Standard, Premium) and product_category (Oral Care, Beverages, etc.).
  • Calculates total revenue, total units, order line count, and average order value per combination.
  • This gives a profitability lens that shows which customer types drive the most revenue for which products.
  • Save customer_segment_profitability.sql.
  • Create the YAML schema file customer_segment_profitability.yml in models/marts/ with the following content:
models:
  - name: customer_segment_profitability
    description: "Revenue and volume breakdown by customer segment and product category. Shows which customer types buy which product types across all Baltic countries."
    columns:
      - name: customer_segment
        description: "Customer segment (Retail, HORECA, Standard, Premium)."
        data_tests:
          - not_null
          - accepted_values:
              values: ['Retail', 'HORECA', 'Standard', 'Premium']
      - name: product_category
        description: "Product category (Oral Care, Personal Care, Tissue, Hygiene, Beverages, Lubricants)."
        data_tests:
          - not_null
      - name: total_revenue
        description: "Sum of line_total for this segment-category combination."
        data_tests:
          - not_null

What does this YAML do?

  • Documents the mart's purpose and column meanings for the auto-generated docs site.
  • The accepted_values test on customer_segment ensures only your four known segments appear. If a bad value slips through the ETL pipeline, this test catches it.
  • Save customer_segment_profitability.yml.

How does dbt know to materialize marts as tables?

Your dbt_project.yml already has this configured from the previous step. The marts: section sets +materialized: table so any model file inside models/marts/ automatically becomes a physical table rather than a view.

Before you run the full pipeline, what do you think the output will show? How many models should dbt build?

  • Run dbt run to build the complete pipeline:
dbt run

You should see 3 models build successfully: stg_sales_orders as a view, then cross_border_revenue and customer_segment_profitability as tables.

One model failed while others succeeded?

  • Check the file name matches the model name exactly. A typo like customer_segment_profitabilty (missing an 'i') will cause a failure.
  • Ensure both .sql files start with the same {{ ref('stg_sales_orders') }} pattern. A mismatch in the model name will break the reference.
  • Confirm both files are inside models/marts/ and not accidentally placed elsewhere.

Help me debug a dbt run failure in my mart models

  • Switch to SSMS and run this query to verify the cross-border revenue mart:
SELECT * FROM dbt_dev.cross_border_revenue
ORDER BY order_year, order_month, country_code;

You should see monthly revenue totals per country. These are the same cross-border numbers you calculated manually in Project 1, now generated automatically from version-controlled model files.

  • Run this query to verify the customer segment profitability mart:
SELECT * FROM dbt_dev.customer_segment_profitability
ORDER BY total_revenue DESC;

You should see revenue broken down by customer segment and product category, sorted by highest revenue first.

✔️ Awesome, I've got everything!

Great. Make sure both cross_border_revenue.sql and customer_segment_profitability.sql are saved along with their YAML schema files.

ⓧ I'd like to double check the full code

Scroll back up and confirm each of these files is present in your models/marts/ folder, in order:

  • cross_border_revenue.sql (the SQL aggregation with ref('stg_sales_orders')).
  • cross_border_revenue.yml (the YAML schema with descriptions and data_tests).
  • customer_segment_profitability.sql (the SQL aggregation by segment and category).
  • customer_segment_profitability.yml (the YAML schema with accepted_values tests).

Both marts are live in your database. Next up, you'll add automated data tests that validate the integrity of your entire pipeline and catch a real failure hiding in your dimensional model.

Add Data Tests and Catch a Failure

Your mart queries return correct numbers today. But what happens if a bad data load corrupts a dimension table, or a schema change silently breaks a join?

Without automated tests, you would not know until a stakeholder reports wrong numbers. dbt's built-in data tests let you declare assertions directly in YAML. They run every time and surface failures immediately.

In this step, get ready to:
  • Add data tests to your staging model YAML.
  • Run dbt test and observe a real test failure caused by dimensional modeling.
  • Fix the failing test and confirm your entire test suite passes.
Add data tests to your staging model

Your sources already have tests defined from when you declared them. Now you need tests on your staging model too, so dbt validates the denormalized output after joins.

  • In VS Code, create a new file called stg_sales_orders.yml inside models/staging/.
  • Paste the following YAML to define tests for your staging view:
models:
  - name: stg_sales_orders
    description: "Denormalized view joining FactSalesOrder with all dimension tables. Each row is one order line with full customer, product, subsidiary, and date context resolved."
    columns:
      - name: fact_key
        description: "Unique identifier for each order line."
        data_tests:
          - unique
          - not_null
      - name: order_id
        description: "Natural order identifier from source ERP."
        data_tests:
          - not_null
      - name: line_total
        description: "Revenue for this order line (quantity * unit_price)."
        data_tests:
          - not_null
      - name: customer_country
        description: "Country code of the customer who placed the order."
        data_tests:
          - accepted_values:
              values: ['EE', 'LV', 'LT']

What do these tests assert?

  • unique on fact_key asserts every order line appears exactly once. If dbt finds duplicates, the test fails.
  • not_null asserts a column never contains NULL. Missing data in order_id or line_total would break downstream aggregations.
  • accepted_values on customer_country ensures only valid Baltic country codes appear. Any unexpected value signals dirty data.
  • Save stg_sales_orders.yml.
Add a uniqueness test on customer_id and run dbt test

Your DimCustomer table has a customer_id column that identifies each customer. Since it is the natural business key, you would expect each customer to appear exactly once.

  • In VS Code, open models/staging/__sources.yml.
  • Scroll down to the DimCustomer table and find the customer_id column section. It currently looks like this:
          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - not_null
  • Add a - unique line above - not_null so it now looks like this:
          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - unique
              - not_null
  • Save __sources.yml.

Before you run the tests, do you think all of them will pass? Think about what unique on customer_id actually checks and whether your SCD Type 2 data could violate it.

  • Run the full test suite by running this command in your terminal:
dbt test

You will see most tests pass, but one test fails. Look for a line containing FAIL in the output. The failing test is the unique test on DimCustomer.customer_id.

Why did the unique test on customer_id fail?

Your DimCustomer table uses SCD Type 2 history tracking, which you built in Project 1. When a customer changes segment (for example from Standard to Premium), SCD Type 2 keeps the old row and inserts a new one. Both rows share the same customer_id natural key.

The unique test found duplicate customer_id values because one customer has two versions in the table. The surrogate key customer_key IS unique across all versions. That is the correct column to test for uniqueness in a Type 2 dimension.

Fix the failing test and confirm all tests pass

Now that you understand the root cause, remove the incorrect unique assertion from customer_id. The customer_key column already has a unique test, which is the correct place for it.

  • In __sources.yml, find the customer_id section you just edited. It currently looks like this:
          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - unique
              - not_null
  • Remove the - unique line so it looks like this:
          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - not_null
  • Save __sources.yml.
  • Re-run the test suite to confirm everything passes:
dbt test

This time every test passes. Your summary line shows all assertions green with zero failures.

Still seeing a failure?

  • Make sure you removed the entire - unique line from customer_id, not from customer_key. The customer_key column should still have both unique and not_null.
  • Check your YAML indentation. The data_tests: key must be indented under the column name with consistent spacing.
  • Confirm you saved the file before re-running dbt test.

Why is my dbt test still failing after removing the unique assertion?

What did this teach you?

Blindly applying unique tests to natural keys is a common trap in dimensional modeling. SCD Type 2 dimensions deliberately duplicate natural keys to preserve history. The lesson is to think about what uniqueness means in context before applying tests mechanically.

✔️ Awesome, I've got everything!

Great. Double check you saved both stg_sales_orders.yml and __sources.yml.

ⓧ I'd like to double check the full code

Here is the complete stg_sales_orders.yml file you created in this step:

models:
  - name: stg_sales_orders
    description: "Denormalized view joining FactSalesOrder with all dimension tables. Each row is one order line with full customer, product, subsidiary, and date context resolved."
    columns:
      - name: fact_key
        description: "Unique identifier for each order line."
        data_tests:
          - unique
          - not_null
      - name: order_id
        description: "Natural order identifier from source ERP."
        data_tests:
          - not_null
      - name: line_total
        description: "Revenue for this order line (quantity * unit_price)."
        data_tests:
          - not_null
      - name: customer_country
        description: "Country code of the customer who placed the order."
        data_tests:
          - accepted_values:
              values: ['EE', 'LV', 'LT']

And confirm that customer_id in __sources.yml has only not_null (no unique):

          - name: customer_id
            description: "Natural key from source ERP. NOT unique due to SCD Type 2 versioning."
            data_tests:
              - not_null

Your data warehouse now has automated guardrails. Every time you run dbt test, dbt validates your data integrity across sources, staging, and marts. Next up, you will generate documentation and an interactive lineage DAG to make the whole pipeline visible.

Generate Documentation and Explore Lineage

In the previous step, you built automated tests that caught a real dimensional modeling issue and now guard your pipeline against silent data corruption. Your YAML files already contain descriptions for every source, model, and column.

In this step, you'll turn all of that metadata into a browsable documentation site. dbt auto-generates an interactive lineage DAG that traces how data flows from raw sources through your staging models into reporting marts.

In this step, get ready to:
  • Generate the documentation catalog from your YAML descriptions and model SQL.
  • Launch the documentation site locally and explore the interactive lineage DAG.
  • Verify all models, column descriptions, and test results appear in the documentation.
Generate the documentation catalog

The dbt docs generate command reads every YAML file, model SQL, and test definition in your project and compiles them into a documentation catalog. This produces two files in your target/ folder: catalog.json and manifest.json.

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

What does this command do?

It parses your entire project and queries your database to gather metadata (column types, row counts, test definitions).

The result is a static catalog that powers the documentation website. Every description field you wrote in your YAML files flows into this catalog.

  • Confirm the output shows a line that mentions catalog generation completing with no errors.
  • Verify the catalog file exists by running:
dir target\catalog.json

You should see catalog.json listed in the output.

Don't see catalog.json?

  • Make sure you ran the command from your project root folder (the one containing dbt_project.yml).
  • Check that your virtual environment is still activated. You should see (.venv) at the start of your terminal prompt.
  • If dbt reports a connection error, verify SQL Server is running via services.msc.

Help me troubleshoot why dbt docs generate failed

Launch the documentation site

With the catalog generated, you can now serve it as an interactive website. The dbt docs serve command starts a local web server and opens the documentation in your browser.

What happens when you run dbt docs serve?

This command starts a web server on port 8080 and opens your default browser to the documentation site. It will keep your terminal occupied until you press Ctrl+C to stop it.

Before you run this, what do you expect to see in the browser?

  • Launch the documentation site by running:
dbt docs serve

Your browser opens to the dbt documentation site. You should see a page listing your project name baltic_dw with sections for sources, models, and tests.

Browser didn't open?

  • If your browser did not open automatically, navigate to http://localhost:8080 manually.
  • If port 8080 is already in use, stop the server with Ctrl+C and re-run with a different port: dbt docs serve --port 8081.

Help me troubleshoot why my dbt docs site is not loading

Explore the lineage DAG and verify documentation

The documentation site includes an interactive lineage graph that shows how data flows through your project. This is the artifact you'd demo in an interview to prove you build documented, traceable pipelines.

  • In the bottom-right corner of the documentation page, click the blue circle icon to open the lineage DAG.
  • Look for the green source.baltic_dw.FactSalesOrder node on the left side of the graph.
  • Trace the arrow from FactSalesOrder through stg_sales_orders (your staging view) to both cross_border_revenue and customer_segment_profitability (your mart tables).
  • Click on the stg_sales_orders node to open its detail panel.
  • Confirm that the description reads: "Denormalized view joining FactSalesOrder with all dimension tables."
  • Scroll down in the detail panel to see the column list with descriptions and the data tests attached to each column.

What should I see in the lineage?

The DAG shows 5 green source nodes (one for each star schema table) on the left. Arrows flow from all 5 sources into stg_sales_orders in the middle.

From stg_sales_orders, two arrows branch out to cross_border_revenue and customer_segment_profitability on the right. This visualizes your entire transformation pipeline from raw data to business-ready marts.

  • Click on the cross_border_revenue node and verify the description says: "Monthly revenue aggregated by country."
  • Check that the country_code column shows its not_null and accepted_values tests listed.

You now have a fully documented, auto-generated reference for your entire transformation layer. Any team member can browse this site to understand the data flow without reading raw SQL.

Stop the docs server when you're done

Press Ctrl+C in your terminal to stop the documentation server and get your terminal back. You can re-launch it any time with dbt docs generate followed by dbt docs serve.

Secret mission

Custom Test and dbt Build Workflow

In production, teams don't run dbt run and dbt test separately. They use dbt build, which executes models and tests in dependency order as a single pipeline. Your challenge: write custom singular tests that validate business rules no generic test can express, then run the full production workflow.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • dbt-core and dbt-sqlserver Python packages in a virtual environment (no cost, disk space only).
  • BalticDW database on SQL Server 2025 Developer Edition (from Project 1, no cost).
  • dbt-generated views and tables in the dbt_dev schema of BalticDW (minimal disk space).

Keep everything running

No action needed. Choose this if you plan to continue into Project 3, which connects Power BI to these dbt-built marts.

  • Your dbt project files, virtual environment, and database all remain on disk ready for the next project.
  • The dbt_dev schema in BalticDW stays intact with your mart tables and staging view.

Pause - I'll come back to this later

Shut down the SQL Server service to free up memory. Your dbt project files remain intact on disk.

  • Press the Windows key, type services.msc, and press Enter.
  • Find SQL Server (MSSQLSERVER) in the list.
  • Right-click it and select Stop.
  • When you're ready to resume, return to services.msc and select Start on the same service.

Delete - I don't want to use this again

Remove all dbt project resources and the generated schema. This leaves your original BalticDW star schema from Project 1 untouched.

  • Delete the dbt project folder from your Desktop by running this in your terminal:
cd ~/Desktop
rm -rf baltic_dw
  • On Windows, use this command instead:
cd ~\Desktop
rmdir /s /q baltic_dw
  • Open SSMS and connect to your local SQL Server instance.
  • Run this query to drop the dbt_dev schema and all objects this project created:
DROP TABLE IF EXISTS dbt_dev.cross_border_revenue;
DROP TABLE IF EXISTS dbt_dev.customer_segment_profitability;
DROP VIEW IF EXISTS dbt_dev.stg_sales_orders;
DROP SCHEMA IF EXISTS dbt_dev;
  • Your original BalticDW tables in the dbo schema remain untouched for Project 1 or future use.

Nice Work!

Nice Work!

You just built a production-grade dbt transformation layer on top of your BalticDW star schema. You can now explain and demonstrate the full dbt workflow to an interviewer: from source declarations, through tested staging and mart models, into auto-generated documentation with lineage tracing.

You've learned how to:

  • Structure a dbt project with sources, staging models, and reporting marts following official best practices, with staging materialized as views and marts as tables.
  • Write and run data tests (unique, not_null, relationships, accepted_values) that catch real dimensional modeling issues like SCD Type 2 natural key duplication.
  • Generate auto-documentation with interactive lineage DAGs that visualize data flow from raw sources through transformations to business-ready marts.
  • Secret Mission: Wrote custom singular tests validating business rules (revenue consistency, no empty marts) and implemented the dbt build workflow that runs models and tests in dependency order as a production pipeline.

Ready to quiz yourself?