Build a Banking Data Warehouse with dbt

Transform raw banking CSVs into a star schema using dbt and DuckDB.

Introduction

30 Second Summary

Every time you check your bank balance or review a transaction history, that data flows through systems that need to organize millions of records into something analysts can actually query. The tool that makes this transformation possible is called dbt.

In this project, you will build a local banking analytics data warehouse that transforms raw CSV data into a Kimball star schema using dbt Core and DuckDB.

What You'll Build

A complete dbt project that loads raw banking data (customers, accounts, branches, transactions) and transforms it through a three-layer architecture into a star schema ready for analytics queries.

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

  • A dbt project with source definitions, staging models, and mart models that transform raw banking CSVs into analytics-ready tables.
  • A Kimball star schema with 4 dimension tables and 1 fact table for banking transaction data.
  • Automated data quality tests validating unique keys, not-null constraints, and referential integrity across the warehouse.
  • Auto-generated documentation with a lineage DAG showing the full data pipeline from raw seeds to analytics-ready marts.
  • Secret Mission: Build an analytics mart that calculates monthly branch performance with running totals and month-over-month growth using advanced SQL window functions.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY) and have used a terminal before. Familiarity with Python basics is helpful for the initial setup, but you will not write Python code in this project.

Set Up dbt and DuckDB

Every data warehouse starts as raw, messy data sitting in files or databases. Your job as an analytics engineer is to transform that raw data into clean, structured tables that analysts can query for insights.

In this step, you will install dbt Core with the DuckDB adapter, initialize your project, and configure the database connection. This gives you a fully working local development environment with zero cloud costs.

In this step, get ready to:
  • Install dbt-duckdb in an isolated Python virtual environment.
  • Initialize the dbt project and configure its structure.
  • Verify the database connection with dbt debug.
Create a Python virtual environment and install dbt-duckdb

A virtual environment keeps your project's Python packages separate from your system-wide Python installation. This prevents version conflicts between projects.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir banking_warehouse
cd banking_warehouse
  • Create a Python virtual environment and activate it by running these commands:
python3 -m venv .venv
source .venv/bin/activate

What does activating a virtual environment do?

When you activate a virtual environment, your terminal starts using the Python interpreter and packages inside .venv/ instead of your system-wide Python. Your terminal prompt changes to show (.venv) at the beginning, confirming it is active.

macOS

The command above works on macOS. You should see (.venv) at the start of your terminal prompt.

Windows

On Windows, activate the virtual environment by running this command instead:

.venv\Scripts\activate

You should see (.venv) at the start of your terminal prompt.

  • Install the dbt-duckdb adapter by running this command:
pip install dbt-duckdb==1.10.1

What does this install?

Installing dbt-duckdb automatically installs dbt-core as well. The adapter is the bridge that lets dbt talk to a specific database engine. In this case, DuckDB runs entirely on your machine with no server or credentials needed.

The install takes about a minute. Once it finishes, you should see a success message.

  • Verify the installation by running:
dbt --version

You should see version information for dbt-core and the duckdb adapter printed in your terminal.

Command not found?

Make sure your virtual environment is active. You should see (.venv) at the start of your terminal prompt. If not, run source .venv/bin/activate again.

If you still see an error, try pip install dbt-duckdb==1.10.1 again and check for any error messages during installation.

help me troubleshoot why dbt --version is not working after installing dbt-duckdb in my virtual environment

Initialize the dbt project

The dbt init command scaffolds a project with all the folders and configuration files dbt needs to run transformations.

  • Initialize the project by running this command:
dbt init banking_warehouse --skip-profile-setup

What does dbt init create?

This command creates a banking_warehouse/ subdirectory with standard dbt folders: models/, seeds/, tests/, macros/, and a dbt_project.yml configuration file. The --skip-profile-setup flag tells dbt to skip the interactive database connection prompts because you will configure that manually next.

  • Move the project files into your current directory by running these commands:
mv banking_warehouse/* .
mv banking_warehouse/.gitignore . 2>/dev/null
rm -rf banking_warehouse

Why move the files up?

The dbt init command always creates a subdirectory. Since you already created banking_warehouse/ as your project root (with your virtual environment inside), you move the scaffolded files up one level so everything lives together in one directory.

  • Verify the project files exist by running:
ls

You should see dbt_project.yml, models/, seeds/, tests/, and other dbt folders in the output alongside your .venv/ directory.

Now you need to update the generated dbt_project.yml to configure how your models will be materialized (stored in the database).

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to Desktop, select the banking_warehouse folder, then click Open.
  • In the Explorer sidebar, click dbt_project.yml to open it.
  • Replace the entire contents of the file with the following:
name: 'banking_warehouse'
version: '1.0.0'
config-version: 2

profile: 'banking_warehouse'

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

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

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

What does the models section do?

The models: section tells dbt how to store the results of your SQL transformations. Staging models are materialized as views (lightweight, no storage cost, always-fresh reads). Mart models are materialized as tables (persisted for fast query performance).

This mirrors production patterns where staging is cheap and disposable, while the final star schema tables are optimized for analysts to query.

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

Next, you need to create a packages.yml file that tells dbt to install the dbt_utils package. This package provides helper macros you will use later to generate surrogate keys for your dimension tables.

  • In VS Code, right-click in the Explorer sidebar and select New File.
  • Name the file packages.yml and press Enter.
  • Paste the following content into the file:
packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.3.0"

What is dbt_utils?

The dbt_utils package is maintained by dbt Labs and provides widely-used macros like generate_surrogate_key(). You will use this macro in Step 4 to create unique keys for your dimension tables.

  • Save packages.yml.
  • Switch back to your terminal and install the dbt packages by running:
dbt deps

You should see output confirming that dbt-labs/dbt_utils was installed successfully.

Seeing a package install error?

Make sure you are in the banking_warehouse directory (where dbt_project.yml lives). Run pwd to check your current location. If you are somewhere else, run cd ~/Desktop/banking_warehouse to get back.

help me fix a dbt deps error when trying to install dbt_utils in my banking_warehouse project

Configure the DuckDB connection and verify with dbt debug

dbt needs a profiles.yml file to know how to connect to your database. For DuckDB, this is simple because DuckDB runs locally with no authentication required.

  • Create the dbt configuration directory and profiles file by running these commands:
mkdir -p ~/.dbt
cat > ~/.dbt/profiles.yml << 'EOF'
banking_warehouse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: banking_warehouse.duckdb
      threads: 4
EOF

What does profiles.yml configure?

The profiles.yml file lives at ~/.dbt/profiles.yml (outside your project directory) and tells dbt how to connect to your data warehouse.

The path: banking_warehouse.duckdb setting tells DuckDB to persist data to a local file. DuckDB creates this file automatically the first time you run a dbt command. No server, no credentials, no cloud account needed.

  • Verify everything is configured correctly by running:
dbt debug

You should see all checks pass with green OK messages, confirming that dbt can find your project and connect to DuckDB.

Seeing a connection error?

Check that your ~/.dbt/profiles.yml file matches the configuration above exactly. The profile name banking_warehouse must match the profile: value in your dbt_project.yml.

You can view your profiles file by running cat ~/.dbt/profiles.yml in your terminal to check for typos.

help me troubleshoot why dbt debug is failing with a connection error for my DuckDB banking_warehouse project

✔️ Awesome, I've got everything!

Great. Double check you have saved both dbt_project.yml and packages.yml in VS Code.

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml:

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

profile: 'banking_warehouse'

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

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

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

Here is the complete packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.3.0"

Here is the complete ~/.dbt/profiles.yml:

banking_warehouse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: banking_warehouse.duckdb
      threads: 4

Your dbt development environment is ready. Next up, you will load raw banking data into DuckDB using dbt seeds.

Load Raw Banking Data as Seeds

Your dbt project is configured and connected to DuckDB. Now it needs data to transform.

In a production environment, raw data lands in a warehouse from extraction tools. For local development, dbt offers a feature called seeds that lets you load CSV files directly into your database. In this step, you will create seed files representing raw banking data (customers, accounts, branches, and transactions), load them into DuckDB, and register them as tracked sources.

In this step, get ready to:
  • Create four CSV seed files representing raw banking source data.
  • Load the seeds into DuckDB with a single command.
  • Define a source configuration so dbt can track lineage from the raw layer.
Create the raw banking CSV seed files

Seeds are CSV files that dbt loads directly into your data warehouse. They are ideal for small, static reference data or sample datasets like the banking records you will use throughout this project.

What is a dbt seed?

A seed is a CSV file stored in your project's seeds/ directory. When you run dbt seed, dbt reads every CSV in that folder and creates a corresponding table in your database.

Once loaded, seeds can be referenced in downstream models using the ref function, just like any other model.

  • Create a file called raw_customers.csv inside the seeds/ directory with this content:
customer_id,first_name,last_name,email,date_of_birth,customer_since,status
C001,Alice,Johnson,alice.johnson@email.com,1985-03-15,2018-01-10,active
C002,Bob,Smith,bob.smith@email.com,1990-07-22,2019-06-05,active
C003,Carol,Williams,carol.w@email.com,1978-11-30,2017-03-20,active
C004,David,Brown,david.brown@email.com,1995-01-08,2021-09-15,inactive
C005,Eva,Martinez,eva.m@email.com,1982-05-19,2016-11-01,active

What does this data represent?

This file holds five bank customers with basic profile information. Notice the status column. It distinguishes active customers from inactive ones, which is useful for filtering in analytics later.

  • Create raw_accounts.csv in the seeds/ directory with this content:
account_id,customer_id,account_type,branch_id,opened_date,balance,currency
A001,C001,checking,B001,2018-01-10,5200.50,USD
A002,C001,savings,B001,2018-02-15,15000.00,USD
A003,C002,checking,B002,2019-06-05,3100.75,USD
A004,C003,savings,B001,2017-03-20,42000.00,USD
A005,C003,checking,B003,2017-04-01,8750.25,USD
A006,C004,checking,B002,2021-09-15,1200.00,USD
A007,C005,savings,B003,2016-11-01,28500.00,USD
A008,C005,checking,B003,2016-12-10,6300.00,USD

What does this data represent?

Eight accounts across five customers. Each account links to a customer via customer_id and to a branch via branch_id. These foreign key relationships are what your star schema will leverage later.

  • Create raw_branches.csv in the seeds/ directory with this content:
branch_id,branch_name,city,state,region
B001,Downtown Main,New York,NY,Northeast
B002,Westside Plaza,Los Angeles,CA,West
B003,Lakefront Center,Chicago,IL,Midwest

What does this data represent?

Three bank branches across different US regions. This small dimension table will let you slice transaction metrics by geography (city, state, or region).

  • Create raw_transactions.csv in the seeds/ directory with this content:
transaction_id,account_id,transaction_type,amount,transaction_date,description
T001,A001,deposit,2500.00,2024-01-05,Payroll deposit
T002,A001,withdrawal,150.00,2024-01-08,ATM withdrawal
T003,A002,deposit,1000.00,2024-01-10,Transfer from checking
T004,A003,deposit,3200.00,2024-01-12,Payroll deposit
T005,A004,deposit,5000.00,2024-01-15,Wire transfer
T006,A005,withdrawal,500.00,2024-01-18,Bill payment
T007,A001,withdrawal,75.00,2024-01-20,POS purchase
T008,A006,deposit,1800.00,2024-01-22,Payroll deposit
T009,A007,deposit,2000.00,2024-01-25,Interest credit
T010,A008,withdrawal,300.00,2024-01-28,ATM withdrawal
T011,A001,deposit,2500.00,2024-02-05,Payroll deposit
T012,A003,deposit,3200.00,2024-02-12,Payroll deposit
T013,A004,withdrawal,1500.00,2024-02-14,Wire transfer out
T014,A005,deposit,4200.00,2024-02-18,Client payment
T015,A007,deposit,2000.00,2024-02-25,Interest credit
T016,A002,deposit,500.00,2024-02-28,Savings contribution
T017,A001,deposit,2500.00,2024-03-05,Payroll deposit
T018,A003,withdrawal,800.00,2024-03-10,Rent payment
T019,A005,deposit,3800.00,2024-03-15,Client payment
T020,A008,deposit,1500.00,2024-03-20,Freelance income

What does this data represent?

Twenty transactions spanning January through March 2024, covering deposits, withdrawals, and transfers. This is your fact data. Each row records a single banking event tied to an account.

The transaction_type column (deposit, withdrawal, transfer) will drive your fact table metrics and the Secret Mission's branch performance analysis.

  • Confirm all four files exist by running:
ls seeds/

You should see raw_accounts.csv, raw_branches.csv, raw_customers.csv, and raw_transactions.csv in the output.

Don't see all four files?

Make sure you saved each file inside the seeds/ directory (not in the project root or models/). The filenames must match exactly, including the .csv extension.

Help me find my missing seed files in the banking_warehouse project.

Load the seeds into DuckDB

With the CSV files in place, a single dbt command will read them and create corresponding tables in your DuckDB database.

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

What does this command do?

The dbt seed command reads every CSV file in your seeds/ directory and loads it as a table in DuckDB. Each CSV becomes its own table with the same name as the file (minus the .csv extension).

You should see output confirming all four seeds completed successfully, with row counts for each table (5 customers, 8 accounts, 3 branches, 20 transactions).

Seeing a database error?

If you see a connection error, confirm that dbt debug still passes. If it does not, check that your virtual environment is activated (you should see (.venv) in your terminal prompt).

If you see a CSV parsing error, make sure there are no extra blank lines or trailing commas in your CSV files. The header row must be the very first line.

Help me fix my dbt seed error.

Define sources for lineage tracking

Your seeds are loaded, but dbt does not yet know they represent the raw layer of your pipeline. Defining a source tells dbt where the seed data lives so it can track lineage from raw data all the way through to your final mart models.

  • Create the models/staging/ directory by running:
mkdir -p models/staging
  • Create a file called _sources.yml inside models/staging/ with this content:
version: 2

sources:
  - name: banking_raw
    description: Raw banking data loaded from CSV seeds
    schema: main
    tables:
      - name: raw_customers
        description: Raw customer records
      - name: raw_accounts
        description: Raw account records
      - name: raw_branches
        description: Raw branch records
      - name: raw_transactions
        description: Raw transaction records

What does this file do?

This YAML file declares a source called banking_raw that points to the main schema in DuckDB. The four tables listed correspond to the four seed files you just loaded.

In the next step, your staging models will reference these sources using the {{ source() }} function. This creates a traceable lineage from raw data to every downstream transformation.

  • Save _sources.yml.
  • Verify the file is in the correct location by running:
ls models/staging/

You should see _sources.yml in the output.

Directory not found?

If you see a "No such file or directory" error, make sure you ran mkdir -p models/staging from the banking_warehouse project root (where dbt_project.yml lives). Run pwd to confirm your current location.

Help me fix my directory structure for dbt sources.

Your raw banking data is loaded and registered. Next up, you will build staging models that clean and standardize this data into a consistent format ready for dimensional modeling.

Build Staging Models

Your raw banking data is now loaded into DuckDB, but it still looks exactly like it did in the CSV files. Column names might be inconsistent, data types are generic strings, and there are no computed fields an analyst could use directly.

The staging layer is where you fix all of that. In this step, you will create four staging models that clean, rename, and cast your raw data into a consistent, reliable format that downstream models can trust.

In this step, get ready to:
  • Create four staging SQL models that standardize the raw banking data.
  • Confirm the staging materialization is set to views for lightweight processing.
  • Run the staging layer and verify all four models build successfully.
Create staging models to clean and standardize the raw data

Each staging model follows the same pattern: pull data from a source using a CTE, then select and transform columns in the final query. You will create one .sql file per source table inside models/staging/.

What is a CTE?

A CTE (Common Table Expression) is a named temporary result set defined with the with keyword. In dbt staging models, CTEs keep your SQL readable by separating the "where does the data come from" step from the "how do I transform it" step.

  • Create a new file called stg_customers.sql inside the models/staging/ directory.
  • Paste the following SQL into it:
with source as (
    select * from {{ source('banking_raw', 'raw_customers') }}
)

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

What does this code do?

  • The {{ source('banking_raw', 'raw_customers') }} Jinja function references the source you defined in _sources.yml. This gives dbt full lineage tracking from seed to staging.
  • The full_name column concatenates first and last names into a single field analysts can query directly.
  • The cast(... as date) calls convert string date values into proper date types so downstream models can use date math.
  • Save stg_customers.sql.
  • Create a new file called stg_accounts.sql inside models/staging/ and paste the following SQL:
with source as (
    select * from {{ source('banking_raw', 'raw_accounts') }}
)

select
    account_id,
    customer_id,
    account_type,
    branch_id,
    cast(opened_date as date) as opened_date,
    cast(balance as decimal(12, 2)) as current_balance,
    currency
from source

What does this code do?

  • The balance column is renamed to current_balance for clarity. Downstream models will immediately know this value reflects the account's present state.
  • The decimal(12, 2) cast ensures monetary values have exactly two decimal places, preventing floating-point rounding issues in financial calculations.
  • Save stg_accounts.sql.
  • Create a new file called stg_branches.sql inside models/staging/ and paste the following SQL:
with source as (
    select * from {{ source('banking_raw', 'raw_branches') }}
)

select
    branch_id,
    branch_name,
    city,
    state,
    region
from source

What does this code do?

This model selects columns without transformations because the branch data is already clean. Even when no casting is needed, creating a staging model establishes a single entry point that downstream models reference, rather than reaching directly into raw sources.

  • Save stg_branches.sql.
  • Create a new file called stg_transactions.sql inside models/staging/ and paste the following SQL:
with source as (
    select * from {{ source('banking_raw', 'raw_transactions') }}
)

select
    transaction_id,
    account_id,
    transaction_type,
    cast(amount as decimal(12, 2)) as transaction_amount,
    cast(transaction_date as date) as transaction_date,
    description
from source

What does this code do?

  • The amount column is renamed to transaction_amount. Descriptive names prevent ambiguity when this field joins with account balances later in the star schema.
  • The transaction_date is cast to a proper date type so it can join cleanly with the date dimension you will build in the next step.
  • Save stg_transactions.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved all four files in the models/staging/ directory.

ⓧ I'd like to double check the full code

Here are all four staging models for comparison.

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

select
    customer_id,
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name,
    email,
    cast(date_of_birth as date) as date_of_birth,
    cast(customer_since as date) as customer_since,
    status
from source
with source as (
    select * from {{ source('banking_raw', 'raw_accounts') }}
)

select
    account_id,
    customer_id,
    account_type,
    branch_id,
    cast(opened_date as date) as opened_date,
    cast(balance as decimal(12, 2)) as current_balance,
    currency
from source
with source as (
    select * from {{ source('banking_raw', 'raw_branches') }}
)

select
    branch_id,
    branch_name,
    city,
    state,
    region
from source
with source as (
    select * from {{ source('banking_raw', 'raw_transactions') }}
)

select
    transaction_id,
    account_id,
    transaction_type,
    cast(amount as decimal(12, 2)) as transaction_amount,
    cast(transaction_date as date) as transaction_date,
    description
from source
Confirm the staging materialization configuration

Your dbt_project.yml already contains the staging materialization setting from the previous step. This is the line that tells dbt how to build your staging models.

  • Open dbt_project.yml and confirm you see this block under the models: key:
models:
  banking_warehouse:
    staging:
      +materialized: view
    marts:
      +materialized: table

Why materialize staging as views?

A view does not store data on disk. It runs its SQL fresh every time something queries it.

Staging models are lightweight transformations (renaming, casting) that sit on top of already-loaded seed data. Making them views avoids duplicating data and keeps your warehouse storage lean. The heavier mart models downstream will be materialized as tables for query performance.

Run the staging layer and verify it builds

With all four models saved, you can now ask dbt to compile and execute only the staging layer.

  • Run the staging models by executing this command in your terminal:
dbt run --select staging

What does this command do?

The --select staging flag tells dbt to only build models inside the staging directory. This is faster than running the entire project and lets you verify one layer at a time.

You should see output showing all four models completed successfully, each marked as a view. Look for lines like OK created view model for each staging model.

Seeing a compilation error?

  • Check that each .sql file is saved inside models/staging/ (not inside models/ directly or another subdirectory).
  • Verify your source() calls match the source name banking_raw and table names (raw_customers, raw_accounts, etc.) exactly as defined in _sources.yml.
  • Make sure you ran dbt deps in the previous step to install the dbt_utils package. Missing packages can cause compilation failures.

Still stuck? Help me debug my dbt staging model compilation error.

Your staging layer is live. Every downstream model can now reference clean, consistently typed data through {{ ref('stg_customers') }} instead of reaching into messy raw sources. Next up, you will build the star schema with dimension and fact tables on top of this foundation.

Build the Star Schema

Your staging layer is live and serving clean, standardized data from all four banking tables. Now it's time to shape that data into something an analyst can actually query for insights.

In this step, you will build a Kimball star schema on top of your staging models. A star schema organizes data into dimension tables (the "who, what, where, when") and a central fact table (the "what happened"). This is the industry-standard pattern for analytics warehouses.

In this step, get ready to:
  • Create four dimension models (customer, account, branch, date) with surrogate keys.
  • Create a fact table that joins transactions to all dimension keys.
  • Run the full pipeline and query the star schema to verify it works.
Create the dimension models

Dimension tables describe the entities in your business. Each dimension gets a surrogate key (a hashed identifier) that the fact table will reference.

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

The first dimension is the customer dimension. It enriches customer data with aggregated account information like the number of accounts each customer holds.

  • Create models/marts/dim_customer.sql and paste the following code:
with customers as (
    select * from {{ ref('stg_customers') }}
),

accounts as (
    select * from {{ ref('stg_accounts') }}
),

customer_accounts as (
    select
        customer_id,
        count(*) as number_of_accounts,
        min(opened_date) as first_account_date
    from accounts
    group by customer_id
)

select
    {{ dbt_utils.generate_surrogate_key(['c.customer_id']) }} as customer_key,
    c.customer_id,
    c.full_name,
    c.email,
    c.date_of_birth,
    c.customer_since,
    c.status,
    coalesce(ca.number_of_accounts, 0) as number_of_accounts,
    ca.first_account_date
from customers c
left join customer_accounts ca on c.customer_id = ca.customer_id

What does this code do?

This model pulls cleaned customer data from the staging layer using {{ ref('stg_customers') }}, which tells dbt about the dependency.

The customer_accounts CTE aggregates how many accounts each customer has and when their first account was opened. The final SELECT joins these together and generates a customer_key surrogate key using dbt_utils.generate_surrogate_key().

Surrogate keys are hashed identifiers that remain stable even if the source system's IDs change. They are the standard way to connect dimension tables to fact tables.

  • Confirm the file exists by running:
ls models/marts/

You should see dim_customer.sql in the output.

Next, create the account dimension. This is a simpler pass-through dimension that adds a surrogate key to the account data.

  • Create models/marts/dim_account.sql and paste the following code:
with accounts as (
    select * from {{ ref('stg_accounts') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['account_id']) }} as account_key,
    account_id,
    customer_id,
    account_type,
    branch_id,
    opened_date,
    current_balance,
    currency
from accounts

What does this code do?

This model reads from stg_accounts and adds a hashed account_key surrogate key. The rest of the columns pass through unchanged because the staging layer already handled the cleaning.

Now create the branch dimension using the same pattern.

  • Create models/marts/dim_branch.sql and paste the following code:
with branches as (
    select * from {{ ref('stg_branches') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['branch_id']) }} as branch_key,
    branch_id,
    branch_name,
    city,
    state,
    region
from branches

What does this code do?

Same pattern as the account dimension. It adds a branch_key surrogate key and passes through all branch attributes (name, city, state, region).

  • Confirm all three dimension files exist by running:
ls models/marts/

You should see dim_account.sql, dim_branch.sql, and dim_customer.sql listed.

The final dimension is the date dimension. Unlike the others, this one doesn't pull from a source table. It generates a complete calendar for 2024 using a date spine.

  • Create models/marts/dim_date.sql and paste the following code:
with date_spine as (
    select
        unnest(
            generate_series(
                date '2024-01-01',
                date '2024-12-31',
                interval '1 day'
            )
        )::date as date_day
)

select
    {{ dbt_utils.generate_surrogate_key(['date_day']) }} as date_key,
    date_day,
    extract(year from date_day) as year,
    extract(month from date_day) as month,
    extract(day from date_day) as day_of_month,
    extract(dow from date_day) as day_of_week,
    case extract(dow from date_day)
        when 0 then 'Sunday'
        when 1 then 'Monday'
        when 2 then 'Tuesday'
        when 3 then 'Wednesday'
        when 4 then 'Thursday'
        when 5 then 'Friday'
        when 6 then 'Saturday'
    end as day_name,
    case
        when extract(month from date_day) in (1, 2, 3) then 'Q1'
        when extract(month from date_day) in (4, 5, 6) then 'Q2'
        when extract(month from date_day) in (7, 8, 9) then 'Q3'
        else 'Q4'
    end as quarter,
    case
        when extract(dow from date_day) in (0, 6) then false
        else true
    end as is_weekday
from date_spine

What does this code do?

The date_spine CTE uses DuckDB's generate_series function to create one row for every day in 2024 (365 rows total).

The final SELECT enriches each date with useful attributes: year, month, day of week, quarter, and a weekday flag. Analysts use these columns to filter and group transactions by time periods without doing date math in every query.

Date dimensions are standard practice

Every production data warehouse has a date dimension. It lets analysts write WHERE quarter = 'Q1' instead of calculating date ranges manually.

Create the fact table

The fact table is the center of the star schema. It records each individual banking transaction and connects it to all four dimensions through their surrogate keys.

  • Create models/marts/fct_transactions.sql and paste the following code:
with transactions as (
    select * from {{ ref('stg_transactions') }}
),

accounts as (
    select * from {{ ref('stg_accounts') }}
),

dim_customer as (
    select * from {{ ref('dim_customer') }}
),

dim_account as (
    select * from {{ ref('dim_account') }}
),

dim_branch as (
    select * from {{ ref('dim_branch') }}
),

dim_date as (
    select * from {{ ref('dim_date') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['t.transaction_id']) }} as transaction_key,
    dc.customer_key,
    da.account_key,
    db.branch_key,
    dd.date_key,
    t.transaction_id,
    t.transaction_type,
    t.transaction_amount,
    t.transaction_date,
    t.description
from transactions t
left join accounts a on t.account_id = a.account_id
left join dim_customer dc on a.customer_id = dc.customer_id
left join dim_account da on t.account_id = da.account_id
left join dim_branch db on a.branch_id = db.branch_id
left join dim_date dd on t.transaction_date = dd.date_day

What does this code do?

The grain of this fact table is one row per transaction. Every row captures what happened (type, amount, description) plus foreign keys to every dimension (customer, account, branch, date).

The joins work in two stages. First, stg_accounts bridges between a transaction's account_id and the customer/branch that own that account. Then each dimension table contributes its surrogate key to the final output.

Using {{ ref('dim_customer') }} instead of {{ ref('stg_customers') }} tells dbt that the fact table depends on the dimension models, not the staging models directly. This is what creates the star shape in the lineage DAG.

  • Save fct_transactions.sql.
  • Confirm all five mart files exist by running:
ls models/marts/

You should see dim_account.sql, dim_branch.sql, dim_customer.sql, dim_date.sql, and fct_transactions.sql listed.

Missing a file?

Make sure you created each file inside models/marts/ and not in the project root or the models/staging/ directory. Double-check with ls models/marts/ to confirm the path.

help me find where my mart files ended up

Run the full pipeline and verify the star schema

Your dbt_project.yml already configures the marts layer to materialize as tables, which is what you want for dimension and fact tables. Tables persist the data in DuckDB for fast query performance.

Why tables instead of views?

Staging models use views because they are lightweight reads that always reflect the latest source data. Mart models use tables because they store pre-computed results that analysts query repeatedly. This mirrors how production data warehouses are configured.

Before running the pipeline, you need to install the dbt_utils package that your dimension models reference.

  • Install the dbt packages by running:
dbt deps

What does this command do?

The dbt deps command reads your packages.yml file and downloads the listed packages into the dbt_packages directory. This gives your models access to macros like generate_surrogate_key().

  • Build the entire pipeline (staging views and mart tables) by running:
dbt run
  • You should see output showing all nine models completed successfully (4 staging views + 5 mart tables).

Seeing a compilation error?

If you see an error mentioning a missing macro or undefined dbt_utils, make sure you ran dbt deps first. The packages must be installed before dbt can compile models that use them.

If you see a SQL syntax error, double-check that your SQL files match the code above exactly. A missing comma or mismatched parenthesis is the most common cause.

help me debug my dbt run error

Now verify the star schema works by querying the fact table directly in DuckDB.

  • Query the fact table to see your star schema in action by running:
python3 -c "
import duckdb
con = duckdb.connect('banking_warehouse.duckdb')
result = con.execute('''
    SELECT
        dc.full_name,
        db.branch_name,
        ft.transaction_type,
        ft.transaction_amount,
        ft.transaction_date
    FROM fct_transactions ft
    JOIN dim_customer dc ON ft.customer_key = dc.customer_key
    JOIN dim_branch db ON ft.branch_key = db.branch_key
    ORDER BY ft.transaction_date
    LIMIT 5
''').fetchall()
for row in result:
    print(row)
con.close()
"

What does this query do?

This query joins the fact table to the customer and branch dimensions using surrogate keys. It returns the first five transactions with human-readable names instead of raw IDs. This is exactly how an analyst would query a star schema.

You should see five rows of output showing customer names, branch names, transaction types, amounts, and dates.

✔️ Awesome, I've got everything!

Great. Double check that all five files are saved in models/marts/.

ⓧ I'd like to double check the full code

Here is models/marts/dim_customer.sql:

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

accounts as (
    select * from {{ ref('stg_accounts') }}
),

customer_accounts as (
    select
        customer_id,
        count(*) as number_of_accounts,
        min(opened_date) as first_account_date
    from accounts
    group by customer_id
)

select
    {{ dbt_utils.generate_surrogate_key(['c.customer_id']) }} as customer_key,
    c.customer_id,
    c.full_name,
    c.email,
    c.date_of_birth,
    c.customer_since,
    c.status,
    coalesce(ca.number_of_accounts, 0) as number_of_accounts,
    ca.first_account_date
from customers c
left join customer_accounts ca on c.customer_id = ca.customer_id

Here is models/marts/dim_account.sql:

with accounts as (
    select * from {{ ref('stg_accounts') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['account_id']) }} as account_key,
    account_id,
    customer_id,
    account_type,
    branch_id,
    opened_date,
    current_balance,
    currency
from accounts

Here is models/marts/dim_branch.sql:

with branches as (
    select * from {{ ref('stg_branches') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['branch_id']) }} as branch_key,
    branch_id,
    branch_name,
    city,
    state,
    region
from branches

Here is models/marts/dim_date.sql:

with date_spine as (
    select
        unnest(
            generate_series(
                date '2024-01-01',
                date '2024-12-31',
                interval '1 day'
            )
        )::date as date_day
)

select
    {{ dbt_utils.generate_surrogate_key(['date_day']) }} as date_key,
    date_day,
    extract(year from date_day) as year,
    extract(month from date_day) as month,
    extract(day from date_day) as day_of_month,
    extract(dow from date_day) as day_of_week,
    case extract(dow from date_day)
        when 0 then 'Sunday'
        when 1 then 'Monday'
        when 2 then 'Tuesday'
        when 3 then 'Wednesday'
        when 4 then 'Thursday'
        when 5 then 'Friday'
        when 6 then 'Saturday'
    end as day_name,
    case
        when extract(month from date_day) in (1, 2, 3) then 'Q1'
        when extract(month from date_day) in (4, 5, 6) then 'Q2'
        when extract(month from date_day) in (7, 8, 9) then 'Q3'
        else 'Q4'
    end as quarter,
    case
        when extract(dow from date_day) in (0, 6) then false
        else true
    end as is_weekday
from date_spine

Here is models/marts/fct_transactions.sql:

with transactions as (
    select * from {{ ref('stg_transactions') }}
),

accounts as (
    select * from {{ ref('stg_accounts') }}
),

dim_customer as (
    select * from {{ ref('dim_customer') }}
),

dim_account as (
    select * from {{ ref('dim_account') }}
),

dim_branch as (
    select * from {{ ref('dim_branch') }}
),

dim_date as (
    select * from {{ ref('dim_date') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['t.transaction_id']) }} as transaction_key,
    dc.customer_key,
    da.account_key,
    db.branch_key,
    dd.date_key,
    t.transaction_id,
    t.transaction_type,
    t.transaction_amount,
    t.transaction_date,
    t.description
from transactions t
left join accounts a on t.account_id = a.account_id
left join dim_customer dc on a.customer_id = dc.customer_id
left join dim_account da on t.account_id = da.account_id
left join dim_branch db on a.branch_id = db.branch_id
left join dim_date dd on t.transaction_date = dd.date_day

Your star schema is built and verified. Next up, you will add data quality tests and auto-generated documentation to make this warehouse production-ready.

Add Data Quality Tests and Documentation

Your star schema is built and queryable. But how do you know the data flowing through it is trustworthy? In production, bad data silently breaks dashboards and misleads decisions.

In this step, you will add automated data quality tests that validate your warehouse every time it runs. You will also generate documentation with a visual lineage DAG so anyone on your team can see how data flows from raw CSVs to analytics-ready tables.

In this step, get ready to:
  • Define schema tests for unique keys, not-null constraints, and referential integrity.
  • Add model and column descriptions for auto-generated documentation.
  • Generate and serve a documentation site with a visual lineage DAG.
Add schema tests for the staging layer

In dbt, tests are assertions you define in YAML files alongside your models. When you run dbt test, dbt checks every assertion and reports failures. This catches data quality issues before they reach your analysts.

  • Create a new file called _staging.yml inside your models/staging/ directory.
  • Paste the following YAML into the file:
version: 2

models:
  - name: stg_customers
    description: Cleaned and standardized customer data
    columns:
      - name: customer_id
        description: Unique identifier for each customer
        tests:
          - not_null
          - unique
      - name: email
        tests:
          - not_null

  - name: stg_accounts
    description: Cleaned and standardized account data
    columns:
      - name: account_id
        description: Unique identifier for each account
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null

  - name: stg_branches
    description: Cleaned and standardized branch data
    columns:
      - name: branch_id
        description: Unique identifier for each branch
        tests:
          - not_null
          - unique

  - name: stg_transactions
    description: Cleaned and standardized transaction data
    columns:
      - name: transaction_id
        description: Unique identifier for each transaction
        tests:
          - not_null
          - unique
      - name: account_id
        tests:
          - not_null
      - name: transaction_amount
        tests:
          - not_null

What does this YAML do?

Each model entry maps to a staging SQL file you created earlier. Under columns, you declare which columns to test and what to test them for.

  • not_null ensures no row has a missing value in that column.
  • unique ensures no two rows share the same value. Together they guarantee a valid primary key.
  • The description fields feed into auto-generated documentation. They describe what each model and column represents.
  • Save _staging.yml.

YAML indentation errors?

YAML is whitespace-sensitive. Make sure you use spaces (not tabs) and that each level is indented by exactly 2 spaces. The tests: key must be indented under its parent column name.

Help me fix a YAML indentation error in my dbt schema file.

Add schema tests for the marts layer

The marts layer needs stronger tests because it is the layer analysts query directly. Beyond unique and not-null checks, you will add relationship tests that verify every foreign key in the fact table points to a real dimension record.

  • Create a new file called _marts.yml inside your models/marts/ directory.
  • Paste the following YAML into the file:
version: 2

models:
  - name: dim_customer
    description: Customer dimension table with aggregated account info
    columns:
      - name: customer_key
        description: Surrogate key for customer dimension
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
          - unique

  - name: dim_account
    description: Account dimension table
    columns:
      - name: account_key
        description: Surrogate key for account dimension
        tests:
          - not_null
          - unique
      - name: account_id
        tests:
          - not_null
          - unique

  - name: dim_branch
    description: Branch dimension table
    columns:
      - name: branch_key
        description: Surrogate key for branch dimension
        tests:
          - not_null
          - unique
      - name: branch_id
        tests:
          - not_null
          - unique

  - name: dim_date
    description: Date dimension table spanning 2024
    columns:
      - name: date_key
        description: Surrogate key for date dimension
        tests:
          - not_null
          - unique
      - name: date_day
        tests:
          - not_null
          - unique

  - name: fct_transactions
    description: Fact table recording all banking transactions at the individual transaction grain
    columns:
      - name: transaction_key
        description: Surrogate key for transaction fact
        tests:
          - not_null
          - unique
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: account_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_account')
              field: account_key
      - name: branch_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_branch')
              field: branch_key
      - name: date_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_date')
              field: date_key
      - name: transaction_amount
        tests:
          - not_null

What are relationship tests?

A relationships test checks referential integrity. For example, the test on customer_key in fct_transactions verifies that every value exists in dim_customer.customer_key.

If a transaction references a customer that does not exist in the dimension table, the test fails. This catches broken joins before analysts ever query the data.

  • Save _marts.yml.

Seeing a parser error?

The relationships block has nested keys (to: and field:) that must be indented one level deeper than - relationships:. Double check that to: aligns with field: directly below it.

Help me fix the relationships test indentation in my _marts.yml file.

Run the test suite
  • Run all tests across your project by running this command:
dbt test
  • You should see output confirming all tests passed. Each test shows a green PASS status with the model and column it validated.

What is dbt testing under the hood?

Each test compiles to a SQL query. A unique test runs a query that counts duplicates. If the count is greater than zero, the test fails. A relationships test finds foreign key values that have no match in the parent table.

In production, you would run dbt test on a schedule (or after every dbt run) to catch data quality regressions automatically.

Tests failing?

If a relationship test fails, it usually means the mart models need to be rebuilt with the latest staging data. Run dbt run first to refresh all models, then run dbt test again.

Help me debug failing dbt tests in my banking warehouse project.

Understand the descriptions you added

Notice the description fields in both YAML files you just created. These are not just comments. dbt uses them to generate a searchable documentation website for your data warehouse.

Why do descriptions matter?

In a real analytics team, dozens of models exist and new team members need to understand what each table and column represents. Descriptions turn your YAML schema files into a living data dictionary.

Model-level descriptions (like "Customer dimension table with aggregated account info") explain the table's purpose. Column-level descriptions (like "Surrogate key for customer dimension") explain what each field holds. Both appear in the auto-generated documentation site you will serve next.

Generate and serve the documentation site

dbt can compile all your model definitions, descriptions, and dependencies into a browsable documentation website. The site includes a visual lineage DAG that shows how data flows from seeds through staging to marts.

  • Generate the documentation catalog by running this command:
dbt docs generate
  • You should see output confirming the catalog was generated successfully.
  • Serve the documentation site locally by running this command:
dbt docs serve
  • Your default browser should open automatically to localhost:8080.
  • Click the blue circular icon in the bottom-right corner of the page to open the lineage DAG.
  • You should see your full pipeline visualized: seed files on the left, staging models in the middle, and mart tables on the right, with arrows showing how data flows between them.

What is a lineage DAG?

DAG stands for Directed Acyclic Graph. It is a visual map of every dependency in your dbt project. Each node is a model or seed, and each arrow shows which models depend on which.

This is one of dbt's most powerful features for analytics teams. If something breaks, you can trace exactly which downstream models are affected.

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

Browser did not open?

If your browser did not open automatically, manually navigate to http://localhost:8080. If port 8080 is already in use, the terminal output will show which port dbt chose instead.

Help me access the dbt docs site that did not open in my browser.

✔️ Awesome, I've got everything!

Great. Double check you have saved both _staging.yml and _marts.yml before moving on.

ⓧ I'd like to double check the full code

Here is the full _staging.yml file:

version: 2

models:
  - name: stg_customers
    description: Cleaned and standardized customer data
    columns:
      - name: customer_id
        description: Unique identifier for each customer
        tests:
          - not_null
          - unique
      - name: email
        tests:
          - not_null

  - name: stg_accounts
    description: Cleaned and standardized account data
    columns:
      - name: account_id
        description: Unique identifier for each account
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null

  - name: stg_branches
    description: Cleaned and standardized branch data
    columns:
      - name: branch_id
        description: Unique identifier for each branch
        tests:
          - not_null
          - unique

  - name: stg_transactions
    description: Cleaned and standardized transaction data
    columns:
      - name: transaction_id
        description: Unique identifier for each transaction
        tests:
          - not_null
          - unique
      - name: account_id
        tests:
          - not_null
      - name: transaction_amount
        tests:
          - not_null

Here is the full _marts.yml file:

version: 2

models:
  - name: dim_customer
    description: Customer dimension table with aggregated account info
    columns:
      - name: customer_key
        description: Surrogate key for customer dimension
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
          - unique

  - name: dim_account
    description: Account dimension table
    columns:
      - name: account_key
        description: Surrogate key for account dimension
        tests:
          - not_null
          - unique
      - name: account_id
        tests:
          - not_null
          - unique

  - name: dim_branch
    description: Branch dimension table
    columns:
      - name: branch_key
        description: Surrogate key for branch dimension
        tests:
          - not_null
          - unique
      - name: branch_id
        tests:
          - not_null
          - unique

  - name: dim_date
    description: Date dimension table spanning 2024
    columns:
      - name: date_key
        description: Surrogate key for date dimension
        tests:
          - not_null
          - unique
      - name: date_day
        tests:
          - not_null
          - unique

  - name: fct_transactions
    description: Fact table recording all banking transactions at the individual transaction grain
    columns:
      - name: transaction_key
        description: Surrogate key for transaction fact
        tests:
          - not_null
          - unique
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: account_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_account')
              field: account_key
      - name: branch_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_branch')
              field: branch_key
      - name: date_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_date')
              field: date_key
      - name: transaction_amount
        tests:
          - not_null

Your data warehouse now has automated quality checks and browsable documentation. In the Secret Mission, you will push this further by building an analytics mart with advanced SQL window functions.

Secret mission

Build an Analytics Mart with Window Functions

Your star schema is powerful, but a real Analytics Engineer goes further. Can you build a branch performance mart that calculates running totals and month-over-month deposit growth using SQL window functions?

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • The banking_warehouse dbt project directory (all models, seeds, YAML configs, and the banking_warehouse.duckdb database file).
  • The Python virtual environment (.venv directory).
  • The dbt connection profile at ~/.dbt/profiles.yml.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep the project on your machine for reference.

  • Your banking_warehouse.duckdb file is only a few MB. It takes up negligible disk space.
  • The virtual environment (.venv) stays inactive unless you reactivate it.
  • To pick up where you left off later, navigate to your project directory, activate the virtual environment, and run dbt build to rebuild everything.

Pause - I'll come back to this later

There are no running processes or cloud services to pause. Your project is already idle on disk.

  • Deactivate the virtual environment if it is still active by running:
  • This frees your terminal session back to the system Python.
deactivate

Everything else stays on disk ready for you to return.

Delete - I don't want to use this again

Remove all project files, the database, and the dbt profile. Nothing persists after these steps.

  • Deactivate the virtual environment if it is still active:
deactivate
  • Navigate to your Desktop and delete the project directory by running these commands:
cd ~/Desktop
rm -rf banking_warehouse
  • If you created a parent directory that also contains your .venv, delete that parent directory instead of just banking_warehouse.
  • Remove the banking_warehouse profile from your dbt profiles file. Open ~/.dbt/profiles.yml and delete the banking_warehouse entry. If this was your only dbt project, you can delete the entire file:
rm ~/.dbt/profiles.yml

On Windows, use these commands instead:

cd ~\Desktop
Remove-Item -Recurse -Force banking_warehouse
Remove-Item ~\.dbt\profiles.yml

Your machine is now clean. No dbt artifacts remain.

Nice Work!

Nice Work!

You just built a production-grade banking analytics data warehouse from scratch using dbt and DuckDB. From raw CSV files to a fully tested, documented star schema, you now have the core skills an Analytics Engineer uses daily.

You've learned how to:

  • Build a complete dbt project with source definitions, staging models, and mart layers that transform raw banking CSVs into analytics-ready tables.
  • Design a Kimball star schema with 4 dimension tables and a fact table for banking transaction data.
  • Implement automated data quality tests and generate documentation with a visual lineage DAG showing the full data pipeline.
  • Secret Mission: Built an analytics mart using advanced SQL window functions (running totals, LAG) to calculate month-over-month branch performance.

Ready to quiz yourself?