Fintech Payments Warehouse with dbt

Model raw payment data into a star schema using dbt and DuckDB.

Introduction

30 Second Summary

Every time you tap your card or click "pay now" online, a payment processor fires off a chain of events behind the scenes. Authorizations, captures, refunds, chargebacks. Someone needs to transform all of that raw event data into insights that power business decisions.

In this project, you will build a payments analytics data warehouse using dbt and DuckDB. You will transform raw payment transaction events through a three-layer architecture into a star schema that analysts can query for revenue reporting and fraud detection.

What You'll Build

A dbt project that models Adyen-style payment processor data (authorizations, captures, refunds, chargebacks) through staging, intermediate, and mart layers into a dimensional star schema with automated data quality tests and interactive documentation.

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

  • A dbt project with 10+ SQL models organized across a three-layer medallion architecture (staging, intermediate, and marts).
  • A Kimball star schema with a fact table and three dimension tables modeling real-world payment flows.
  • A data quality test suite validating primary keys, referential integrity, and business rules, plus auto-generated documentation with an interactive lineage graph.
  • Secret Mission: Build a fraud risk analysis model using SQL window functions to flag suspicious transaction patterns.

Are there any prerequisites?

You should have basic SQL knowledge (JOINs, GROUP BY, CASE statements). You also need Python (3.10+) and VS Code installed on your machine.

Set Up dbt with DuckDB

Before you can model payment data, you need an analytics engineering toolkit. dbt (data build tool) transforms raw data into analytics-ready tables using plain SQL.

In this step, you will install dbt with the DuckDB adapter so everything runs locally on your machine. You will also scaffold the project structure and confirm the connection works.

In this step, get ready to:
  • Install dbt-duckdb in a Python virtual environment.
  • Initialize and configure the dbt project with a DuckDB connection.
  • Install the VS Code dbt extension for syntax highlighting.
Create a Python virtual environment and install dbt-duckdb

A virtual environment keeps your project dependencies isolated from other Python projects on your machine. You will create one and install dbt inside it.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • Open a terminal inside VS Code by pressing Ctrl+` (the backtick key, usually above Tab).

Which terminal will open?

On Windows, VS Code opens PowerShell by default. On macOS, it opens your default shell (usually zsh). Both work for this project.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a project folder and set up a virtual environment by running these commands:
mkdir payments_warehouse
cd payments_warehouse
python -m venv venv
  • Activate the virtual environment by running the command for your operating system:

macOS

source venv/bin/activate

Windows

venv\Scripts\Activate.ps1

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

Windows: seeing a script execution error?

PowerShell may block scripts by default. Run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned in your terminal, then try the activation command again.

Help me fix the PowerShell execution policy error.

  • Install dbt with the DuckDB adapter by running:
pip install "dbt-core<2.0" dbt-duckdb

Why the version pin on dbt-core?

Since June 2026, a dbt-core 2.0.0-alpha.1 release exists on PyPI. Without the pin, pip may resolve to the unstable alpha version.

Adding "dbt-core<2.0" to the install command ensures you get the latest stable 1.x release instead.

  • Verify the installation by running:
dbt --version

You should see version information for both dbt-core and the dbt-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 the activation command again.

Help me troubleshoot why dbt --version is not working.

Initialize the dbt project and configure the DuckDB connection

Now that dbt is installed, you need to scaffold the project file structure. The dbt init command creates all the folders and configuration files a dbt project needs.

  • Scaffold the dbt project by running:
dbt init payments_warehouse

What will dbt init ask you?

The command will prompt you to choose a database adapter. Type the number next to duckdb and press Enter.

This creates a payments_warehouse/ subdirectory containing the project structure (models, tests, seeds, macros folders and a starter dbt_project.yml).

  • Move into the new project directory by running:
cd payments_warehouse
  • Open this folder as a workspace in VS Code by running:
code .

A new VS Code window opens with your dbt project loaded. Open a new terminal in this window (Ctrl+`) and reactivate your virtual environment.

macOS

source ../venv/bin/activate

Windows

..\venv\Scripts\Activate.ps1

Why ../venv?

Your virtual environment lives one folder above the dbt project (in the payments_warehouse/ parent directory on your Desktop). The .. navigates up to that parent so the activation script can be found.

Now you will replace the generated dbt_project.yml with the correct configuration for this project.

  • In the VS Code Explorer sidebar, click on dbt_project.yml to open it.
  • Select all the content (Cmd+A on macOS or Ctrl+A on Windows) and replace it with the following:
name: 'payments_warehouse'
version: '1.0.0'

profile: 'payments_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:
  payments_warehouse:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

What does this configuration do?

The profile field tells dbt which connection profile to use (you will create this next). The path fields define where dbt looks for models, tests, seeds, and macros.

The models: section sets materialization strategies. Staging and intermediate models become views (lightweight, always fresh). Mart models become tables (faster for analyst queries).

  • Save dbt_project.yml.

Next, you need to tell dbt how to connect to DuckDB. This goes in a profiles.yml file in your project root.

  • Create a new file called profiles.yml by right-clicking in the Explorer sidebar and selecting New File.
  • Name the file profiles.yml and paste the following content:
payments_warehouse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './payments_warehouse.duckdb'
      threads: 4

What does this profile do?

The type: duckdb tells dbt to use the DuckDB adapter. The path field points to a local file where DuckDB stores all your data. DuckDB creates this file automatically on the first run.

  • Save profiles.yml.

Your project will use a helper package called dbt_utils for generating surrogate keys later. You declare package dependencies in a packages.yml file.

  • Create another new file in the project root called packages.yml and paste the following content:
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
  • Save packages.yml.
  • Install the declared 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 your virtual environment is still active (you should see (venv) in your prompt). Also check that packages.yml is saved in the same folder as dbt_project.yml.

Help me fix my dbt deps error.

  • Confirm that dbt can connect to DuckDB by running:
dbt debug

You should see a list of checks ending with a green "All checks passed!" message at the bottom of the output.

Seeing a profile not found error?

Check that the profile value in dbt_project.yml matches the top-level key in profiles.yml. Both must be payments_warehouse.

Also confirm that profiles.yml is in the same folder as dbt_project.yml (the project root). YAML indentation matters too. Make sure the spacing matches exactly.

Help me debug my dbt connection error.

✔️ Awesome, I've got everything!

Great. Make sure all three files are saved before moving on.

ⓧ I'd like to double check the full code

Here is the complete content for each configuration file:

name: 'payments_warehouse'
version: '1.0.0'

profile: 'payments_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:
  payments_warehouse:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table
payments_warehouse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: './payments_warehouse.duckdb'
      threads: 4
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]
Install the VS Code dbt extension

A VS Code extension gives you syntax highlighting and navigation helpers for dbt's Jinja-SQL files. This makes editing .sql models much easier as your project grows.

  • Open the Extensions panel by pressing Cmd+Shift+X (macOS) or Ctrl+Shift+X (Windows).
  • Type Power User for dbt into the search bar.
  • Find the extension published by Altimate Inc. and click Install.

What does this extension provide?

Power User for dbt adds Jinja-SQL syntax highlighting, autocompletion for ref() and source() functions, and model navigation. These features become essential once you have multiple SQL models referencing each other.

  • Confirm the extension is working by opening any .sql file in the models/ folder (dbt init created example models there).
  • Check the bottom-right corner of VS Code. The language mode should show Jinja SQL instead of plain SQL.

Still seeing plain SQL as the language mode?

Click the language indicator in the bottom-right of VS Code and select Jinja SQL from the dropdown list. The extension may take a moment to activate after installation.

Help me set up the dbt extension language mode.

Your analytics engineering toolkit is ready. Next up, you will create the raw payment data that simulates what a real payment processor like Adyen sends to its data warehouse.

Create Seed Data and Source Definitions

Your dbt project is scaffolded and connected to DuckDB. Now you need data to model.

In a real analytics engineering role, raw data arrives from a payment processor's API or event stream. For this project, you will simulate that raw data using dbt seeds. Seeds are CSV files that dbt loads directly into your warehouse, giving you a controlled dataset to build transformations on top of.

In this step, get ready to:
  • Create four CSV seed files with realistic Adyen-style payment data.
  • Load seed data into DuckDB using dbt seed.
  • Define dbt sources that point to the loaded seed tables.
Create CSV seed files simulating raw payment data

Your seed files simulate the raw data that a payment service provider like Adyen would produce. You will create four files: one for payment lifecycle events and three for reference data (merchants, customers, payment methods).

  • In the VS Code Explorer sidebar, right-click the seeds folder.
  • Click New File.
  • Name it raw_payment_events.csv and press Enter.
  • Paste the following data into the file:
psp_reference,merchant_reference,event_type,amount_minor_units,currency_code,status,created_at,merchant_id,customer_id,payment_method_id,issuer_country,risk_score
PSP001,MR-1001,AUTHORISATION,15000,EUR,success,2024-03-15 10:23:45,M001,C001,PM01,NL,12
PSP002,MR-1002,AUTHORISATION,8500,EUR,success,2024-03-15 10:45:12,M002,C002,PM02,DE,25
PSP003,MR-1003,AUTHORISATION,120000,EUR,success,2024-03-15 11:02:33,M001,C003,PM01,NL,45
PSP004,MR-1004,AUTHORISATION,3200,GBP,success,2024-03-15 11:15:00,M003,C004,PM03,GB,8
PSP005,MR-1005,AUTHORISATION,95000,EUR,failed,2024-03-15 11:30:22,M002,C005,PM02,FR,88
PSP006,MR-1001,CAPTURE,15000,EUR,success,2024-03-15 12:00:00,M001,C001,PM01,NL,12
PSP007,MR-1002,CAPTURE,8500,EUR,success,2024-03-15 12:30:00,M002,C002,PM02,DE,25
PSP008,MR-1003,CAPTURE,120000,EUR,success,2024-03-15 13:00:00,M001,C003,PM01,NL,45
PSP009,MR-1004,CAPTURE,3200,GBP,success,2024-03-15 13:15:00,M003,C004,PM03,GB,8
PSP010,MR-1006,AUTHORISATION,5500,EUR,success,2024-03-15 14:00:10,M001,C001,PM01,NL,15
PSP011,MR-1006,CAPTURE,5500,EUR,success,2024-03-15 14:30:00,M001,C001,PM01,NL,15
PSP012,MR-1007,AUTHORISATION,200000,EUR,success,2024-03-15 14:45:00,M002,C006,PM04,BE,72
PSP013,MR-1007,CAPTURE,200000,EUR,success,2024-03-15 15:00:00,M002,C006,PM04,BE,72
PSP014,MR-1008,AUTHORISATION,4200,EUR,success,2024-03-15 15:10:00,M003,C002,PM02,DE,18
PSP015,MR-1008,CAPTURE,4200,EUR,success,2024-03-15 15:30:00,M003,C002,PM02,DE,18
PSP016,MR-1002,REFUND,8500,EUR,success,2024-03-16 09:00:00,M002,C002,PM02,DE,25
PSP017,MR-1009,AUTHORISATION,67000,EUR,success,2024-03-16 10:00:00,M001,C007,PM01,NL,35
PSP018,MR-1009,CAPTURE,67000,EUR,success,2024-03-16 10:30:00,M001,C007,PM01,NL,35
PSP019,MR-1010,AUTHORISATION,15000,EUR,success,2024-03-16 11:00:00,M002,C001,PM02,NL,55
PSP020,MR-1010,CAPTURE,15000,EUR,success,2024-03-16 11:30:00,M002,C001,PM02,NL,55
PSP021,MR-1003,CHARGEBACK,120000,EUR,success,2024-03-17 08:00:00,M001,C003,PM01,NL,45
PSP022,MR-1011,AUTHORISATION,9900,EUR,success,2024-03-17 09:00:00,M003,C008,PM03,IE,20
PSP023,MR-1011,CAPTURE,9900,EUR,success,2024-03-17 09:30:00,M003,C008,PM03,IE,20
PSP024,MR-1012,AUTHORISATION,340000,EUR,success,2024-03-17 10:00:05,M001,C003,PM01,NL,92
PSP025,MR-1012,AUTHORISATION,330000,EUR,success,2024-03-17 10:00:45,M001,C003,PM01,NL,91

What does this data represent?

This file captures the full lifecycle of a payment. Each row is an event with a psp_reference (unique event ID) and a merchant_reference (shared across events for the same order).

The event_type column tracks where a payment is in its lifecycle: AUTHORISATION (approval), CAPTURE (money collected), REFUND (money returned), or CHARGEBACK (disputed). The amount_minor_units stores amounts in cents to avoid floating-point rounding issues.

  • Save raw_payment_events.csv.

Now create the three reference data files. These hold the dimension data that enriches each payment event.

  • Right-click the seeds folder again and create a new file named raw_merchants.csv.
  • Paste this data:
merchant_id,merchant_name,merchant_country,merchant_category,onboarded_at
M001,TechFlow BV,NL,electronics,2022-01-15
M002,EuroShop GmbH,DE,retail,2022-03-20
M003,GlobalPay Ltd,GB,services,2022-06-01

What does this data represent?

These are the merchant accounts registered on the payment platform. Each merchant has a country, category, and onboarding date. The merchant_id values (M001, M002, M003) match the foreign keys in the payment events file.

  • Save raw_merchants.csv.
  • Create another new file in seeds named raw_customers.csv.
  • Paste this data:
customer_id,email_hash,customer_country,customer_segment,first_seen_at
C001,a1b2c3d4,NL,premium,2023-01-10
C002,e5f6g7h8,DE,standard,2023-02-15
C003,i9j0k1l2,NL,premium,2023-03-01
C004,m3n4o5p6,GB,standard,2023-04-12
C005,q7r8s9t0,FR,standard,2023-05-20
C006,u1v2w3x4,BE,enterprise,2023-06-01
C007,y5z6a7b8,NL,premium,2023-07-15
C008,c9d0e1f2,IE,standard,2023-08-01

What does this data represent?

Customer records use a hashed email (email_hash) instead of real PII. Each customer belongs to a segment (premium, standard, enterprise) and a country. Payment processors store anonymized shopper data like this for analytics without exposing personal details.

  • Save raw_customers.csv.
  • Create one more file in seeds named raw_payment_methods.csv.
  • Paste this data:
payment_method_id,method_type,method_subtype,card_network
PM01,card,credit,visa
PM02,card,debit,mastercard
PM03,card,credit,mastercard
PM04,ideal,bank_transfer,

What does this data represent?

This file lists the payment methods available on the platform. Notice PM04 is iDEAL, a Dutch bank transfer method. The empty card_network value for iDEAL is intentional because bank transfers do not go through a card network.

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

You should see raw_payment_events.csv, raw_merchants.csv, raw_customers.csv, and raw_payment_methods.csv listed in the output.

Don't see all four files?

Make sure each file was created inside the seeds/ folder, not in the project root. If a file ended up in the wrong location, drag it into seeds/ using the Explorer sidebar.

Also check that each filename starts with raw_ and ends with .csv (no extra spaces or capital letters).

Help me troubleshoot my seed files

✔️ Awesome, I've got everything!

Great. Make sure all four files are saved before moving on.

ⓧ I'd like to double check the full code

Here are the complete contents of each seed file for comparison.

psp_reference,merchant_reference,event_type,amount_minor_units,currency_code,status,created_at,merchant_id,customer_id,payment_method_id,issuer_country,risk_score
PSP001,MR-1001,AUTHORISATION,15000,EUR,success,2024-03-15 10:23:45,M001,C001,PM01,NL,12
PSP002,MR-1002,AUTHORISATION,8500,EUR,success,2024-03-15 10:45:12,M002,C002,PM02,DE,25
PSP003,MR-1003,AUTHORISATION,120000,EUR,success,2024-03-15 11:02:33,M001,C003,PM01,NL,45
PSP004,MR-1004,AUTHORISATION,3200,GBP,success,2024-03-15 11:15:00,M003,C004,PM03,GB,8
PSP005,MR-1005,AUTHORISATION,95000,EUR,failed,2024-03-15 11:30:22,M002,C005,PM02,FR,88
PSP006,MR-1001,CAPTURE,15000,EUR,success,2024-03-15 12:00:00,M001,C001,PM01,NL,12
PSP007,MR-1002,CAPTURE,8500,EUR,success,2024-03-15 12:30:00,M002,C002,PM02,DE,25
PSP008,MR-1003,CAPTURE,120000,EUR,success,2024-03-15 13:00:00,M001,C003,PM01,NL,45
PSP009,MR-1004,CAPTURE,3200,GBP,success,2024-03-15 13:15:00,M003,C004,PM03,GB,8
PSP010,MR-1006,AUTHORISATION,5500,EUR,success,2024-03-15 14:00:10,M001,C001,PM01,NL,15
PSP011,MR-1006,CAPTURE,5500,EUR,success,2024-03-15 14:30:00,M001,C001,PM01,NL,15
PSP012,MR-1007,AUTHORISATION,200000,EUR,success,2024-03-15 14:45:00,M002,C006,PM04,BE,72
PSP013,MR-1007,CAPTURE,200000,EUR,success,2024-03-15 15:00:00,M002,C006,PM04,BE,72
PSP014,MR-1008,AUTHORISATION,4200,EUR,success,2024-03-15 15:10:00,M003,C002,PM02,DE,18
PSP015,MR-1008,CAPTURE,4200,EUR,success,2024-03-15 15:30:00,M003,C002,PM02,DE,18
PSP016,MR-1002,REFUND,8500,EUR,success,2024-03-16 09:00:00,M002,C002,PM02,DE,25
PSP017,MR-1009,AUTHORISATION,67000,EUR,success,2024-03-16 10:00:00,M001,C007,PM01,NL,35
PSP018,MR-1009,CAPTURE,67000,EUR,success,2024-03-16 10:30:00,M001,C007,PM01,NL,35
PSP019,MR-1010,AUTHORISATION,15000,EUR,success,2024-03-16 11:00:00,M002,C001,PM02,NL,55
PSP020,MR-1010,CAPTURE,15000,EUR,success,2024-03-16 11:30:00,M002,C001,PM02,NL,55
PSP021,MR-1003,CHARGEBACK,120000,EUR,success,2024-03-17 08:00:00,M001,C003,PM01,NL,45
PSP022,MR-1011,AUTHORISATION,9900,EUR,success,2024-03-17 09:00:00,M003,C008,PM03,IE,20
PSP023,MR-1011,CAPTURE,9900,EUR,success,2024-03-17 09:30:00,M003,C008,PM03,IE,20
PSP024,MR-1012,AUTHORISATION,340000,EUR,success,2024-03-17 10:00:05,M001,C003,PM01,NL,92
PSP025,MR-1012,AUTHORISATION,330000,EUR,success,2024-03-17 10:00:45,M001,C003,PM01,NL,91
merchant_id,merchant_name,merchant_country,merchant_category,onboarded_at
M001,TechFlow BV,NL,electronics,2022-01-15
M002,EuroShop GmbH,DE,retail,2022-03-20
M003,GlobalPay Ltd,GB,services,2022-06-01
customer_id,email_hash,customer_country,customer_segment,first_seen_at
C001,a1b2c3d4,NL,premium,2023-01-10
C002,e5f6g7h8,DE,standard,2023-02-15
C003,i9j0k1l2,NL,premium,2023-03-01
C004,m3n4o5p6,GB,standard,2023-04-12
C005,q7r8s9t0,FR,standard,2023-05-20
C006,u1v2w3x4,BE,enterprise,2023-06-01
C007,y5z6a7b8,NL,premium,2023-07-15
C008,c9d0e1f2,IE,standard,2023-08-01
payment_method_id,method_type,method_subtype,card_network
PM01,card,credit,visa
PM02,card,debit,mastercard
PM03,card,credit,mastercard
PM04,ideal,bank_transfer,
Load seeds and define sources

With the CSV files in place, you can load them into DuckDB as tables. Then you will tell dbt where to find those tables by creating a source definition file.

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

You should see output showing each seed loaded successfully with a row count and a final summary line reading PASS=4.

What does this command do?

The dbt seed command reads every CSV file in the seeds/ directory and loads it as a table in your data warehouse. Because you configured DuckDB with schema: main (the default), each CSV becomes a table in the main schema.

Seeing an error during seed?

If you see a parsing error, open the CSV file mentioned in the error message. Check for trailing spaces after commas, missing commas between fields, or extra blank lines at the end of the file.

If the command says it cannot find the seeds directory, make sure your terminal is inside the payments_warehouse project folder (the one containing dbt_project.yml).

Help me fix my dbt seed error

Now you need to tell dbt that these tables exist as sources. Sources let your models reference raw data using the {{ source() }} function instead of hard-coding table names.

  • In the Explorer sidebar, expand the models folder.
  • Right-click models and create a new folder named staging.
  • Right-click the new staging folder and create a file named _sources.yml.
  • Paste the following source definition:
version: 2

sources:
  - name: raw
    description: Raw payment data from the payment service provider (Adyen-style)
    schema: main
    tables:
      - name: raw_payment_events
        description: All payment lifecycle events (authorisations, captures, refunds, chargebacks)
      - name: raw_merchants
        description: Merchant accounts registered on the payment platform
      - name: raw_customers
        description: Customer (shopper) records identified by hashed email
      - name: raw_payment_methods
        description: Payment method types supported by the platform

What does this file do?

This YAML file declares a source called raw that lives in the main schema. Each table entry maps to one of the seed tables you just loaded.

With this definition, your SQL models can reference raw data using {{ source('raw', 'raw_payment_events') }} instead of hard-coding main.raw_payment_events. This makes your project portable across different environments and databases.

  • Save _sources.yml.
  • Validate that dbt can read your source definition by running:
dbt compile

You should see output listing the registered adapter and source information with no errors.

Seeing a YAML parsing error?

YAML is sensitive to indentation. Make sure you used spaces (not tabs) and that each level is indented by exactly 2 spaces. The tables: key should be indented 4 spaces from the left margin.

Also confirm the file is named exactly _sources.yml (with a leading underscore) and lives inside models/staging/.

Help me fix my dbt compile error

✔️ Awesome, I've got everything!

Great. Double check you have saved _sources.yml before moving on.

ⓧ I'd like to double check the full code

Here is the complete source definition file:

version: 2

sources:
  - name: raw
    description: Raw payment data from the payment service provider (Adyen-style)
    schema: main
    tables:
      - name: raw_payment_events
        description: All payment lifecycle events (authorisations, captures, refunds, chargebacks)
      - name: raw_merchants
        description: Merchant accounts registered on the payment platform
      - name: raw_customers
        description: Customer (shopper) records identified by hashed email
      - name: raw_payment_methods
        description: Payment method types supported by the platform

Your raw data is loaded and your sources are defined. Next up, you will build staging models that clean and standardize this raw data into a consistent format for downstream transformations.

Build the Staging Layer

Your seed data is loaded into DuckDB, but it is still in its raw form. Column names might have inconsistent casing, data types are inferred rather than explicit, and there is no standard convention across the four tables.

The staging layer fixes this. Each staging model selects from exactly one source, renames fields to consistent conventions, and casts data types explicitly. Every other model in the project will reference staging models instead of raw sources.

In this step, get ready to:
  • Create four staging SQL models that clean and standardize the raw data.
  • Run the staging layer and verify the transformations in DuckDB.
Create staging models for each source

In dbt, staging models are the first transformation layer. They follow a strict rule: each staging model reads from exactly one source, applies light cleaning (renaming, casting, casing), and outputs a standardized view that downstream models can trust.

You will create four staging models inside the models/staging/ directory that already contains your _sources.yml file.

  • Create a new file called stg_payment_events.sql inside the models/staging/ directory.
  • Paste the following code into stg_payment_events.sql:
with source as (
    select * from {{ source('raw', 'raw_payment_events') }}
),

renamed as (
    select
        psp_reference,
        merchant_reference,
        upper(event_type) as event_type,
        cast(amount_minor_units as integer) as amount_minor_units,
        upper(currency_code) as currency_code,
        lower(status) as status,
        cast(created_at as timestamp) as created_at,
        merchant_id,
        customer_id,
        payment_method_id,
        upper(issuer_country) as issuer_country,
        cast(risk_score as integer) as risk_score
    from source
)

select * from renamed

What does this code do?

This model follows the standard dbt staging pattern: a source CTE pulls from the raw table, and a renamed CTE applies all the cleaning transformations.

  • upper() standardizes fields like event_type and currency_code to uppercase so downstream filters are case-safe.
  • lower() on status makes comparison values predictable.
  • cast() explicitly sets numeric and timestamp types instead of relying on DuckDB inference.
  • Save stg_payment_events.sql.

The stg_ naming convention

Every staging model starts with stg_ followed by the source table name without the raw_ prefix. This is a dbt community convention that makes model lineage obvious at a glance.

  • Create a new file called stg_merchants.sql inside models/staging/.
  • Paste the following code into stg_merchants.sql:
with source as (
    select * from {{ source('raw', 'raw_merchants') }}
),

renamed as (
    select
        merchant_id,
        merchant_name,
        upper(merchant_country) as merchant_country,
        lower(merchant_category) as merchant_category,
        cast(onboarded_at as date) as onboarded_at
    from source
)

select * from renamed

What does this code do?

This model standardizes merchant data. Country codes go uppercase (NL, DE) for consistent filtering. Categories go lowercase so Electronics and electronics are never treated as different values.

  • Save stg_merchants.sql.
  • Create a new file called stg_customers.sql inside models/staging/.
  • Paste the following code into stg_customers.sql:
with source as (
    select * from {{ source('raw', 'raw_customers') }}
),

renamed as (
    select
        customer_id,
        email_hash,
        upper(customer_country) as customer_country,
        lower(customer_segment) as customer_segment,
        cast(first_seen_at as date) as first_seen_at
    from source
)

select * from renamed

What does this code do?

Same pattern as merchants. The customer_country is uppercased for ISO consistency, customer_segment is lowercased, and first_seen_at is explicitly cast to a date type.

  • Save stg_customers.sql.
  • Create a new file called stg_payment_methods.sql inside models/staging/.
  • Paste the following code into stg_payment_methods.sql:
with source as (
    select * from {{ source('raw', 'raw_payment_methods') }}
),

renamed as (
    select
        payment_method_id,
        lower(method_type) as method_type,
        lower(method_subtype) as method_subtype,
        lower(card_network) as card_network
    from source
)

select * from renamed

What does this code do?

Payment methods only need lowercase normalization. Fields like method_type and card_network become consistently lowercased so that Visa and visa match in downstream joins.

  • Save stg_payment_methods.sql.

Now you need a schema file to document what each staging model does. This is separate from _sources.yml (which documents the raw sources) and describes the staging models themselves.

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

models:
  - name: stg_payment_events
    description: Cleaned and standardized payment events
  - name: stg_merchants
    description: Cleaned and standardized merchant data
  - name: stg_customers
    description: Cleaned and standardized customer data
  - name: stg_payment_methods
    description: Cleaned and standardized payment method data

Why a separate schema file?

The _staging.yml file registers your models with dbt's metadata system. These descriptions appear in auto-generated documentation and help other analysts understand what each model provides without reading the SQL.

  • Save _staging.yml.

Seeing YAML errors?

YAML is whitespace-sensitive. Make sure each - name: line is indented with exactly 4 spaces under models:. Tabs will cause parsing failures.

help me fix YAML indentation errors in my dbt schema file

✔️ Awesome, I've got everything!

Great. Make sure all five files are saved in models/staging/.

ⓧ I'd like to double check the full code

Here are all five staging files for comparison.

with source as (
    select * from {{ source('raw', 'raw_payment_events') }}
),

renamed as (
    select
        psp_reference,
        merchant_reference,
        upper(event_type) as event_type,
        cast(amount_minor_units as integer) as amount_minor_units,
        upper(currency_code) as currency_code,
        lower(status) as status,
        cast(created_at as timestamp) as created_at,
        merchant_id,
        customer_id,
        payment_method_id,
        upper(issuer_country) as issuer_country,
        cast(risk_score as integer) as risk_score
    from source
)

select * from renamed
with source as (
    select * from {{ source('raw', 'raw_merchants') }}
),

renamed as (
    select
        merchant_id,
        merchant_name,
        upper(merchant_country) as merchant_country,
        lower(merchant_category) as merchant_category,
        cast(onboarded_at as date) as onboarded_at
    from source
)

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

renamed as (
    select
        customer_id,
        email_hash,
        upper(customer_country) as customer_country,
        lower(customer_segment) as customer_segment,
        cast(first_seen_at as date) as first_seen_at
    from source
)

select * from renamed
with source as (
    select * from {{ source('raw', 'raw_payment_methods') }}
),

renamed as (
    select
        payment_method_id,
        lower(method_type) as method_type,
        lower(method_subtype) as method_subtype,
        lower(card_network) as card_network
    from source
)

select * from renamed
version: 2

models:
  - name: stg_payment_events
    description: Cleaned and standardized payment events
  - name: stg_merchants
    description: Cleaned and standardized merchant data
  - name: stg_customers
    description: Cleaned and standardized customer data
  - name: stg_payment_methods
    description: Cleaned and standardized payment method data
Run the staging layer and verify the output

Your dbt_project.yml already configures staging models as materialized: view. This means dbt will create SQL views (not tables) for each staging model. Views are lightweight because they do not store data. They re-run the transformation query every time they are read.

  • Run only 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 run models inside the models/staging/ directory. This is faster than running the entire project and lets you verify one layer at a time.

  • Confirm you see output showing all four models completed successfully. You should see lines showing each view was created, ending with a summary like Finished running 4 view models.

Seeing a compilation error?

If dbt reports a source not found error, check that the name values inside {{ source('raw', '...') }} in your SQL match the table names declared in _sources.yml exactly.

help me debug why dbt cannot find my source tables

To confirm the transformations are working, query one of the new views directly.

  • Open a DuckDB query against your staging view by running:
python -c "import duckdb; con = duckdb.connect('payments_warehouse.duckdb'); print(con.sql('SELECT * FROM stg_payment_events LIMIT 5').df())"
  • Confirm that event_type values appear in uppercase, status appears in lowercase, and created_at shows as a timestamp.

Why query directly?

Querying the view proves that your transformations actually produce results. If a cast() fails on real data or a source reference is wrong, you will see the error here rather than in a later step where the root cause is harder to trace.

Your staging layer is live. Every raw source now has a clean, typed, consistently cased view sitting on top of it. Next, you will join these staging models together to build enriched intermediate models and your star schema marts.

Build the Intermediate and Mart Layers

Your staging layer is clean and standardized, but it still mirrors the shape of the raw source tables. An analyst asking "how much revenue did we process last week?" would need to write complex joins across four tables every time.

In this step, you will build the intermediate and mart layers that reshape the data into an analytics-friendly star schema. The intermediate layer enriches payment events with merchant, customer, and payment method details. The mart layer produces a central fact table surrounded by dimension tables that analysts can query directly.

In this step, get ready to:
  • Build an intermediate model that joins payment events with related entities.
  • Create three dimension tables with surrogate keys.
  • Build a fact table that forms the center of your star schema.
Build the intermediate enriched payments model

The intermediate layer sits between staging and marts. Its job is to join related staging models together and apply light business logic before the data reaches the final analytical tables.

  • Create a new folder called intermediate inside your models/ directory.
  • Create a new file called int_payments_enriched.sql inside models/intermediate/ and paste the following code:
with payment_events as (
    select * from {{ ref('stg_payment_events') }}
),

merchants as (
    select * from {{ ref('stg_merchants') }}
),

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

payment_methods as (
    select * from {{ ref('stg_payment_methods') }}
),

authorisations as (
    select
        pe.psp_reference,
        pe.merchant_reference,
        pe.event_type,
        pe.amount_minor_units,
        pe.currency_code,
        pe.status,
        pe.created_at,
        pe.issuer_country,
        pe.risk_score,
        pe.merchant_id,
        pe.customer_id,
        pe.payment_method_id,
        m.merchant_name,
        m.merchant_country,
        m.merchant_category,
        c.customer_country,
        c.customer_segment,
        pm.method_type,
        pm.method_subtype,
        pm.card_network
    from payment_events pe
    left join merchants m on pe.merchant_id = m.merchant_id
    left join customers c on pe.customer_id = c.customer_id
    left join payment_methods pm on pe.payment_method_id = pm.payment_method_id
    where pe.status = 'success'
)

select * from authorisations

What does this model do?

This model pulls from all four staging models using {{ ref() }} to declare dependencies. dbt uses these references to determine the correct build order automatically.

  • The left join clauses enrich each payment event with merchant name, customer segment, and payment method details.
  • The where pe.status = 'success' filter removes failed transactions so downstream models only work with confirmed events.
  • Create a second file called _schema.yml inside models/intermediate/ and paste this documentation:
version: 2

models:
  - name: int_payments_enriched
    description: Payment events enriched with merchant, customer, and payment method details. Filtered to successful events only.
  • Save both files.
  • Verify the intermediate model builds by running this command:
dbt run --select int_payments_enriched

You should see output confirming the model was created successfully with no errors.

Seeing a compilation error?

  • Check that your ref() names match exactly: stg_payment_events, stg_merchants, stg_customers, stg_payment_methods.
  • Make sure the file is saved inside models/intermediate/ and not directly in models/.
  • Confirm you ran dbt deps earlier to install packages.

Why is my intermediate model failing to compile?

✔️ Awesome, I've got everything!

Great. Make sure both files are saved before moving on.

ⓧ I'd like to double check the full code

with payment_events as (
    select * from {{ ref('stg_payment_events') }}
),

merchants as (
    select * from {{ ref('stg_merchants') }}
),

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

payment_methods as (
    select * from {{ ref('stg_payment_methods') }}
),

authorisations as (
    select
        pe.psp_reference,
        pe.merchant_reference,
        pe.event_type,
        pe.amount_minor_units,
        pe.currency_code,
        pe.status,
        pe.created_at,
        pe.issuer_country,
        pe.risk_score,
        pe.merchant_id,
        pe.customer_id,
        pe.payment_method_id,
        m.merchant_name,
        m.merchant_country,
        m.merchant_category,
        c.customer_country,
        c.customer_segment,
        pm.method_type,
        pm.method_subtype,
        pm.card_network
    from payment_events pe
    left join merchants m on pe.merchant_id = m.merchant_id
    left join customers c on pe.customer_id = c.customer_id
    left join payment_methods pm on pe.payment_method_id = pm.payment_method_id
    where pe.status = 'success'
)

select * from authorisations
version: 2

models:
  - name: int_payments_enriched
    description: Payment events enriched with merchant, customer, and payment method details. Filtered to successful events only.
Build dimension tables

Dimension tables describe the "who" and "what" of your data. Each dimension gets a surrogate key generated by hashing its natural key. This decouples your analytics layer from source system IDs.

  • Create a new folder called marts inside your models/ directory.
  • Create a file called dim_merchant.sql inside models/marts/ and paste the following code:
with merchants as (
    select * from {{ ref('stg_merchants') }}
),

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['merchant_id']) }} as merchant_key,
        merchant_id,
        merchant_name,
        merchant_country,
        merchant_category,
        onboarded_at
    from merchants
)

select * from final

What is a surrogate key?

The dbt_utils.generate_surrogate_key macro hashes the column(s) you pass to it (here, merchant_id) to produce a deterministic string key.

This means your fact table references a stable hash instead of the raw source ID. If the source system ever changes how it assigns IDs, your analytics layer stays consistent.

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

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_key,
        customer_id,
        email_hash,
        customer_country,
        customer_segment,
        first_seen_at
    from customers
)

select * from final

What does this model do?

This follows the same pattern as dim_merchant. It generates a customer_key surrogate by hashing customer_id, then exposes all customer attributes for analysts to filter and group by.

  • Create a file called dim_payment_method.sql inside models/marts/ and paste the following code:
with payment_methods as (
    select * from {{ ref('stg_payment_methods') }}
),

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['payment_method_id']) }} as payment_method_key,
        payment_method_id,
        method_type,
        method_subtype,
        card_network
    from payment_methods
)

select * from final

What does this model do?

Same pattern again. The payment_method_key surrogate hashes payment_method_id and exposes the method type and card network for downstream analysis.

  • Save all three dimension files.

You will not see a change yet. The fact table in the next substep references these dimension keys and triggers the full build.

✔️ Awesome, I've got everything!

Great. Confirm all three files are saved in models/marts/ before continuing.

ⓧ I'd like to double check the full code

with merchants as (
    select * from {{ ref('stg_merchants') }}
),

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['merchant_id']) }} as merchant_key,
        merchant_id,
        merchant_name,
        merchant_country,
        merchant_category,
        onboarded_at
    from merchants
)

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

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_key,
        customer_id,
        email_hash,
        customer_country,
        customer_segment,
        first_seen_at
    from customers
)

select * from final
with payment_methods as (
    select * from {{ ref('stg_payment_methods') }}
),

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['payment_method_id']) }} as payment_method_key,
        payment_method_id,
        method_type,
        method_subtype,
        card_network
    from payment_methods
)

select * from final
Build the fact table

The fact table is the heart of your star schema. It records one row per authorized payment and links to your dimension tables via their surrogate keys.

  • Create a file called fct_payments.sql inside models/marts/ and paste the following code:
with enriched_payments as (
    select * from {{ ref('int_payments_enriched') }}
),

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

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

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

captures as (
    select merchant_reference
    from enriched_payments
    where event_type = 'CAPTURE'
),

refunds as (
    select merchant_reference
    from enriched_payments
    where event_type = 'REFUND'
),

chargebacks as (
    select merchant_reference
    from enriched_payments
    where event_type = 'CHARGEBACK'
),

authorisations as (
    select * from enriched_payments
    where event_type = 'AUTHORISATION'
),

final as (
    select
        a.psp_reference as payment_id,
        a.merchant_reference,
        dm.merchant_key,
        dc.customer_key,
        dpm.payment_method_key,
        a.amount_minor_units / 100.0 as amount,
        a.currency_code,
        a.issuer_country,
        a.risk_score,
        a.created_at as authorized_at,
        case when cap.merchant_reference is not null then true else false end as is_captured,
        case when ref.merchant_reference is not null then true else false end as is_refunded,
        case when cb.merchant_reference is not null then true else false end as is_chargedback
    from authorisations a
    left join dim_merchant dm on a.merchant_id = dm.merchant_id
    left join dim_customer dc on a.customer_id = dc.customer_id
    left join dim_payment_method dpm on a.payment_method_id = dpm.payment_method_id
    left join captures cap on a.merchant_reference = cap.merchant_reference
    left join refunds ref on a.merchant_reference = ref.merchant_reference
    left join chargebacks cb on a.merchant_reference = cb.merchant_reference
)

select * from final

What does this model do?

  • It filters int_payments_enriched to only AUTHORISATION events. Each authorisation becomes one row in the fact table.
  • Separate CTEs extract CAPTURE, REFUND, and CHARGEBACK events by merchant_reference. Left joins against these produce the boolean flags is_captured, is_refunded, and is_chargedback.
  • The amount_minor_units / 100.0 conversion turns cents into a decimal amount (e.g., 15000 becomes 150.00).
  • Dimension surrogate keys (merchant_key, customer_key, payment_method_key) replace raw IDs so analysts can join to dimensions cleanly.
  • Save fct_payments.sql.
  • Build the entire pipeline by running this command:
dbt run

You should see output confirming that all models were created successfully. The intermediate model builds as a view and the mart models build as tables, matching your dbt_project.yml configuration.

Seeing a relationship or compilation error?

  • If you see a "Compilation Error" referencing a model that does not exist, check that all files are saved inside models/marts/ and not accidentally in models/ or another folder.
  • If you see a database error about dbt_utils, run dbt deps to install the package, then run dbt run again.
  • Make sure column names in your joins match exactly (e.g., merchant_id, customer_id, payment_method_id).

Help me debug my dbt run errors across the intermediate and mart layers.

✔️ Awesome, I've got everything!

Great. Double check that dbt run completed with no errors before moving on.

ⓧ I'd like to double check the full code

with enriched_payments as (
    select * from {{ ref('int_payments_enriched') }}
),

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

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

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

captures as (
    select merchant_reference
    from enriched_payments
    where event_type = 'CAPTURE'
),

refunds as (
    select merchant_reference
    from enriched_payments
    where event_type = 'REFUND'
),

chargebacks as (
    select merchant_reference
    from enriched_payments
    where event_type = 'CHARGEBACK'
),

authorisations as (
    select * from enriched_payments
    where event_type = 'AUTHORISATION'
),

final as (
    select
        a.psp_reference as payment_id,
        a.merchant_reference,
        dm.merchant_key,
        dc.customer_key,
        dpm.payment_method_key,
        a.amount_minor_units / 100.0 as amount,
        a.currency_code,
        a.issuer_country,
        a.risk_score,
        a.created_at as authorized_at,
        case when cap.merchant_reference is not null then true else false end as is_captured,
        case when ref.merchant_reference is not null then true else false end as is_refunded,
        case when cb.merchant_reference is not null then true else false end as is_chargedback
    from authorisations a
    left join dim_merchant dm on a.merchant_id = dm.merchant_id
    left join dim_customer dc on a.customer_id = dc.customer_id
    left join dim_payment_method dpm on a.payment_method_id = dpm.payment_method_id
    left join captures cap on a.merchant_reference = cap.merchant_reference
    left join refunds ref on a.merchant_reference = ref.merchant_reference
    left join chargebacks cb on a.merchant_reference = cb.merchant_reference
)

select * from final

Your star schema is live. Next up, you will add data quality tests and generate interactive documentation to prove the pipeline is trustworthy.

Add Data Quality Tests and Documentation

Your star schema is built and your mart tables are ready for analysts to query. But how do you know the data inside them is actually correct?

In this step, you'll add automated data quality tests that validate your models every time they run. You'll also generate interactive documentation with a visual lineage graph showing exactly how data flows through your pipeline.

In this step, get ready to:
  • Add data quality tests to your mart models.
  • Run the test suite and verify all tests pass.
  • Generate interactive documentation with a lineage DAG.
Add generic tests to the mart models

dbt ships with four built-in generic tests: unique, not_null, accepted_values, and relationships. You declare these in a YAML schema file alongside your models, and dbt executes them as SQL queries against your warehouse.

What are generic tests?

Each generic test compiles to a SQL query that returns rows violating the rule. If zero rows come back, the test passes. If any rows return, the test fails.

For example, a unique test on payment_id becomes a query that counts duplicate values. A relationships test checks that every foreign key in your fact table exists in the referenced dimension.

  • Create a new file called _schema.yml inside your models/marts/ directory by pasting the following code:
version: 2

models:
  - name: fct_payments
    description: Fact table containing one row per authorized payment with flags for capture, refund, and chargeback status.
    columns:
      - name: payment_id
        description: The PSP reference serving as the unique payment identifier
        tests:
          - unique
          - not_null
      - name: merchant_key
        description: Surrogate key referencing dim_merchant
        tests:
          - not_null
          - relationships:
              to: ref('dim_merchant')
              field: merchant_key
      - name: customer_key
        description: Surrogate key referencing dim_customer
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: payment_method_key
        description: Surrogate key referencing dim_payment_method
        tests:
          - not_null
          - relationships:
              to: ref('dim_payment_method')
              field: payment_method_key
      - name: currency_code
        description: ISO currency code
        tests:
          - accepted_values:
              values: ['EUR', 'GBP', 'USD']

  - name: dim_merchant
    description: Dimension table for merchant accounts
    columns:
      - name: merchant_key
        description: Surrogate key for the merchant
        tests:
          - unique
          - not_null
      - name: merchant_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null

  - name: dim_customer
    description: Dimension table for customers (shoppers)
    columns:
      - name: customer_key
        description: Surrogate key for the customer
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null

  - name: dim_payment_method
    description: Dimension table for payment method types
    columns:
      - name: payment_method_key
        description: Surrogate key for the payment method
        tests:
          - unique
          - not_null
      - name: payment_method_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null

What does this schema define?

  • unique and not_null on payment_id ensures every payment has exactly one row with no gaps.
  • relationships tests on merchant_key, customer_key, and payment_method_key confirm every foreign key in the fact table has a matching record in its dimension.
  • accepted_values on currency_code catches any unexpected currency sneaking into your data (only EUR, GBP, and USD are valid).
  • Each dimension table gets unique and not_null on both its surrogate key and natural key, ensuring no duplicates exist.
  • Save models/marts/_schema.yml.

Seeing YAML indentation errors?

YAML is whitespace-sensitive. Make sure you use spaces (not tabs) and that each level is indented by exactly two spaces. The tests: key must align under its parent columns: entry.

help me fix YAML indentation errors in my dbt schema file

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

version: 2

models:
  - name: fct_payments
    description: Fact table containing one row per authorized payment with flags for capture, refund, and chargeback status.
    columns:
      - name: payment_id
        description: The PSP reference serving as the unique payment identifier
        tests:
          - unique
          - not_null
      - name: merchant_key
        description: Surrogate key referencing dim_merchant
        tests:
          - not_null
          - relationships:
              to: ref('dim_merchant')
              field: merchant_key
      - name: customer_key
        description: Surrogate key referencing dim_customer
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: payment_method_key
        description: Surrogate key referencing dim_payment_method
        tests:
          - not_null
          - relationships:
              to: ref('dim_payment_method')
              field: payment_method_key
      - name: currency_code
        description: ISO currency code
        tests:
          - accepted_values:
              values: ['EUR', 'GBP', 'USD']

  - name: dim_merchant
    description: Dimension table for merchant accounts
    columns:
      - name: merchant_key
        description: Surrogate key for the merchant
        tests:
          - unique
          - not_null
      - name: merchant_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null

  - name: dim_customer
    description: Dimension table for customers (shoppers)
    columns:
      - name: customer_key
        description: Surrogate key for the customer
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null

  - name: dim_payment_method
    description: Dimension table for payment method types
    columns:
      - name: payment_method_key
        description: Surrogate key for the payment method
        tests:
          - unique
          - not_null
      - name: payment_method_id
        description: Natural key from the source system
        tests:
          - unique
          - not_null
Run tests and generate documentation

With your test definitions saved, dbt can now execute each test as a SQL query against your DuckDB warehouse. If any test returns rows (violations), it fails.

  • Run your full test suite by running this command:
dbt test
  • You should see each test execute and pass. The final line shows a summary like PASS=17 WARN=0 ERROR=0 SKIP=0 (the exact count depends on the number of columns with tests defined).

What does dbt test do?

For each test in your _schema.yml, dbt compiles a SQL query and runs it against the warehouse. A unique test looks for duplicate values. A relationships test checks that every foreign key has a matching primary key in the referenced table.

If all tests pass, your data meets the quality contracts you defined. If any fail, dbt tells you exactly which test and column had violations.

Tests failing unexpectedly?

If you see relationship test failures, make sure you ran dbt run before dbt test. The dimension tables need to exist in DuckDB before the fact table's foreign key tests can look them up.

help me debug failing dbt tests in my payments warehouse project

  • Generate the documentation catalog by running this command:
dbt docs generate
  • You should see dbt create manifest.json and catalog.json in your target/ folder.
  • Launch the interactive documentation site by running this command:
dbt docs serve
  • Your browser opens to a local documentation site on port 8080. Click the blue icon in the bottom-right corner to open the lineage graph.
  • You should see a full DAG showing your seed CSVs flowing through staging views, into the intermediate model, and finally into your mart tables.

What is the lineage graph?

The lineage graph (also called a DAG) visualizes every model in your project and how they connect through ref() and source() calls. It shows the full data flow from raw sources through your three-layer architecture to the final star schema.

This is what you would show a stakeholder or a new team member to explain how the pipeline works.

Stop the docs server

Press Ctrl+C in your terminal to stop the documentation server when you are done exploring.

Secret mission

Build a Fraud Risk Analysis Model

Your star schema is built and tested. But can you use it to detect suspicious payment patterns? In this challenge, you will build a fraud risk model that uses SQL window functions to flag transactions based on velocity, frequency, and risk score thresholds.

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 payments_warehouse project folder on your Desktop (contains all dbt models, seeds, configuration, the virtual environment, and the DuckDB database file).
  • The payments_warehouse.duckdb file inside the project folder (your local analytical database).
  • The VS Code dbt extension installed in your editor.

Keep everything running

No action needed. Choose this if you're still actively building or want to explore the data further.

  • Your payments_warehouse.duckdb file persists between sessions. It costs nothing and lives on your machine.
  • To resume work later, open a terminal in your project folder, re-activate your virtual environment, and run dbt run anytime to rebuild your models.
  • The VS Code dbt extension remains installed and will activate automatically when you open .sql files in a dbt project.

Pause - I'll come back to this later

Shut down any running processes to free up memory. Your files stay on disk for when you return.

  • If dbt docs serve is still running, press Ctrl+C in the terminal to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Close VS Code. Your project folder and database file will be waiting on your Desktop when you come back.

Delete - I don't want to use this again

Remove all project files from your machine. There are no cloud resources to clean up.

  • If dbt docs serve is still running, press Ctrl+C in the terminal to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Navigate to your Desktop and delete the entire project folder by running:
cd ~/Desktop
rm -rf payments_warehouse

On Windows, use this command instead:

cd ~\Desktop
Remove-Item -Recurse -Force payments_warehouse
  • This removes all models, seeds, configuration files, the virtual environment, and the payments_warehouse.duckdb database.
  • To uninstall the VS Code dbt extension, open VS Code, click the Extensions icon in the left sidebar, find the dbt extension, and click Uninstall.

Nice Work!

Nice Work!

Nice work! You just built a production-grade payments analytics warehouse using dbt and DuckDB, transforming raw fintech transaction data into a governed, tested star schema.

You've learned how to:

  • Build a dbt project from scratch with a three-layer medallion architecture (staging, intermediate, marts) modelling Adyen-style payment data flows.
  • Implement a Kimball star schema with a fact table (fct_payments) and three dimension tables, connected by surrogate keys.
  • Write a YAML-based data quality test suite enforcing primary key uniqueness, referential integrity, and business rule validation, plus auto-generated documentation with a full lineage DAG.
  • Secret Mission: Built a fraud risk detection model using SQL window functions to flag suspicious payment patterns based on velocity, frequency, and risk score.

Ready to quiz yourself?