Payments Analytics Layer with dbt

Build a tested dbt pipeline that transforms raw payment data into KPIs.

Introduction

30 Second Summary

Every time you tap your phone to pay for coffee or split a bill with a friend, a payment transaction is created. Behind the scenes, companies need to know which payments succeed, how long they take to settle, and whether chargebacks are eating into revenue.

In this project, you will build a production-style analytics layer using dbt and Google BigQuery that transforms raw payment transaction data into trusted KPI models. These are the kind of tables a product manager or finance team would query daily to monitor payment health.

What You'll Build

You will run a single dbt build command and watch your entire transformation pipeline execute end-to-end, from loading raw CSV seed data through to producing queryable payment performance metrics in BigQuery.

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

  • A dbt pipeline that loads payment seed data into BigQuery and transforms it through three layers: staging (clean and type), intermediate (deduplicate and enrich), and marts (calculate KPIs).
  • A payment KPIs mart you can query in BigQuery to see transaction success rate, average settlement time in hours, and chargeback ratio segmented by payment channel and currency.
  • A dbt documentation site running locally that visualizes your project's full data lineage from raw seeds through staging, intermediate, and mart layers.
  • Secret Mission: Build a monthly trend analysis mart using SQL window functions to show month-over-month changes in payment volume and success rate.

Are there any prerequisites?

None. This guide includes full setup instructions for every tool you need, including Python, dbt, BigQuery, and VS Code. The BigQuery sandbox is completely free and does not require a credit card.

Before We Start

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

A payments company generates thousands of transaction events every day. Your job is to turn that raw data into trustworthy KPIs that stakeholders can act on.

Set Up Your Environment

Before you can transform payment data, you need the tools that make it possible. dbt needs a working Python environment and a live connection to BigQuery to run SQL transformations in the cloud.

In this step, you will install Python and dbt, create a free BigQuery sandbox, and wire the two together so dbt can talk to your warehouse.

In this step, get ready to:
  • Install Python 3.13 and dbt-bigquery inside a virtual environment.
  • Set up a BigQuery sandbox with a service account.
  • Initialize a dbt project and confirm the connection works.
Install Python 3.13 and dbt-bigquery

dbt-bigquery requires Python 3.10 or higher, and Python 3.14 is not yet supported. You will use Python 3.13 for this project.

  • Open PowerShell by pressing the Windows key, typing PowerShell, and pressing Enter.
  • Check if Python is already installed by running this command:
python --version

✔️ I see version 3.13 or higher

You are ready to move on to creating your virtual environment.

ⓧ I see an older version

You have Python installed but it is too old for dbt-bigquery. Download Python 3.13 from the official website:

  • Go to https://www.python.org/downloads/ and download the Python 3.13 installer for Windows.
  • Run the installer.
  • On the first screen, check the box that says Add python.exe to PATH, then click Install Now.
  • Close and reopen PowerShell after installation.
  • Run python --version again to confirm it now shows 3.13.

Older version still showing?

Windows sometimes keeps the old version on your PATH. Try running py -3.13 --version to target the new install directly. If that works, use py -3.13 instead of python for the virtual environment step below.

ⓧ Command not found

Python is not installed yet. Download and install it:

  • Go to https://www.python.org/downloads/ and download the Python 3.13 installer for Windows.
  • Run the installer.
  • On the first screen, check the box that says Add python.exe to PATH, then click Install Now.
  • Close and reopen PowerShell after installation.
  • Run python --version to confirm it shows 3.13.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a project folder and move into it by running these commands:
mkdir bluecode-payments-project
cd bluecode-payments-project
  • Create a virtual environment by running this command:
python -m venv venv

What is a virtual environment?

A virtual environment is an isolated Python installation just for this project. It keeps your dbt packages separate from other Python projects on your machine, preventing version conflicts.

  • Activate the virtual environment by running:
venv\Scripts\activate

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

  • Install the dbt-bigquery adapter (which includes dbt Core) by running:
pip install dbt-bigquery==1.12.0

What does this install?

The dbt-bigquery package installs both dbt Core and the BigQuery adapter together. You only need this one install command to get everything dbt needs.

This may take a minute or two as pip downloads all the dependencies.

  • Verify dbt is installed by running:
dbt --version

You should see output showing dbt Core version 1.12.0 and the bigquery plugin listed.

Seeing 'dbt not found'?

Make sure your virtual environment is still active. Look for (venv) at the start of your prompt. If it disappeared, run venv\Scripts\activate again.

Help me troubleshoot this

Set up BigQuery sandbox

BigQuery sandbox gives you a free BigQuery instance with no credit card required. It includes 10 GB of storage and 1 TB of query processing per month.

  • Open your browser and go to https://console.cloud.google.com/bigquery.
  • Sign in with your Google account (or create one if needed).
  • If prompted to select or create a project, click Create Project.
  • Enter a project name like dbt-payments-project and click Create.
  • If asked about a billing account, select No organization and skip billing. The BigQuery sandbox does not require a billing account.

What is the BigQuery sandbox?

The BigQuery sandbox lets you use BigQuery without a credit card or billing account. You get 10 GB of active storage and 1 TB of processed query data per month for free.

Tables automatically expire after 60 days in sandbox mode, but that is plenty of time for this project.

  • Enable the BigQuery API by navigating to APIs & Services in the left sidebar, then clicking Enable APIs and Services.
  • Search for BigQuery API and click Enable if it is not already enabled.

Now you need a service account so dbt can authenticate with BigQuery.

  • In the left sidebar, click IAM & Admin, then click Service Accounts.
  • Click Create Service Account at the top.
  • Enter dbt-service-account as the service account name.
  • Click Create and Continue.
  • In the Grant this service account access to project section, add two roles:
  • Search for and select BigQuery Job User (roles/bigquery.jobUser).
  • Click Add Another Role, then search for and select BigQuery Data Editor (roles/bigquery.dataEditor).
  • Click Continue, then click Done.

Why these two roles?

BigQuery Job User lets dbt run queries (jobs) against your project. BigQuery Data Editor lets dbt create tables and views to store your transformation results. Together, they give dbt exactly the permissions it needs without granting full admin access.

  • Click on your new service account in the list.
  • Go to the Keys tab.
  • Click Add Key, then select Create New Key.
  • Select JSON as the key type and click Create.
  • A JSON file downloads to your computer. Move this file to your bluecode-payments-project folder on the Desktop for easy reference.

Keep your keyfile safe

This JSON keyfile contains credentials that can access your BigQuery project. Do not commit it to Git or share it publicly. You will add it to .gitignore later.

Initialize dbt project and connect to BigQuery

With Python, dbt, and BigQuery all ready, you can now scaffold your dbt project and point it at your warehouse.

  • Back in your PowerShell terminal, make sure you are in the bluecode-payments-project folder with your virtual environment active.
  • Initialize a new dbt project by running:
dbt init bluecode_payments

What prompts will you see?

dbt init will ask which database adapter to use. Type the number next to bigquery and press Enter.

It will then ask for connection details (authentication method, project ID, dataset, etc.). For now, choose the service_account authentication method and fill in the prompts. You can update these values in profiles.yml afterwards if needed.

  • When prompted for the authentication method, enter the number for service_account.
  • For keyfile, enter the full path to your JSON keyfile (for example, C:\Users\YourName\Desktop\bluecode-payments-project\your-keyfile.json).
  • For project, enter your Google Cloud project ID (visible in the Google Cloud Console header).
  • For dataset, enter dbt_payments.
  • For threads, enter 1.
  • For location, enter US (or your preferred BigQuery region).

dbt creates a new bluecode_payments folder with sample project files and writes your connection profile to ~/.dbt/profiles.yml.

  • Move into the new project directory by running:
cd bluecode_payments
  • Open this folder in VS Code by running:
code .

Now you need to update dbt_project.yml to configure how your models and seeds are materialized.

  • In VS Code, open dbt_project.yml from the file explorer on the left.
  • Replace the entire contents of the file with the following configuration:
name: 'bluecode_payments'
version: '1.0.0'

profile: 'bluecode_payments'

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

clean-targets:
  - "target"
  - "dbt_packages"
seeds:
  bluecode_payments:
    +schema: raw

models:
  bluecode_payments:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

What does this configuration do?

  • The seeds section tells dbt to load CSV seed data into a dataset with the suffix raw.
  • The models section defines three layers: staging and intermediate are views (cheap, always up-to-date), while marts are tables (fast to query, materialized once).
  • Each layer gets its own BigQuery dataset, keeping your warehouse organized.
  • Save dbt_project.yml.

Now verify your connection to BigQuery is working. Before you run the next command, what do you expect to see if everything is configured correctly?

  • Test your connection by running:
dbt debug

You should see output ending with All checks passed! confirming dbt can reach your BigQuery project.

✔️ I see All checks passed!

Your dbt project is connected to BigQuery and ready to go.

ⓧ I see a connection error

Check these common causes:

  • Open ~/.dbt/profiles.yml (on Windows this is C:\Users\YourName\.dbt\profiles.yml) and verify the keyfile path points to your downloaded JSON file.
  • Verify the project field matches your Google Cloud project ID exactly (check the console header).
  • Make sure the method field says service-account (with a hyphen, not an underscore).
  • Confirm the BigQuery API is enabled in your Google Cloud project.
  • After fixing, run dbt debug again.

Help me troubleshoot my BigQuery connection

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Here is the complete dbt_project.yml you should have:

name: 'bluecode_payments'
version: '1.0.0'

profile: 'bluecode_payments'

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

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

seeds:
  bluecode_payments:
    +schema: raw

models:
  bluecode_payments:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

Your environment is fully wired up. Next up, you will load payment data into BigQuery and build your first staging models.

Seed Payment Data and Build Staging Models

Your dbt project is connected to BigQuery, but BigQuery has no data to transform yet. In a production analytics team, raw data arrives from upstream systems like payment processors or CRMs.

For this project, you will use dbt seeds to load synthetic payment data directly from CSV files committed in your repo. Then you will build staging models that clean and type-cast that raw data into a consistent shape ready for analysis.

In this step, get ready to:
  • Add three seed CSV files representing raw payment transactions, merchants, and customers.
  • Build staging SQL models that clean, type-cast, and add computed columns to the raw seed data.
  • Run the full pipeline and verify the staging views appear in BigQuery.
Add seed CSV files

dbt seeds are CSV files stored in your project's seeds/ directory. When you run dbt build, dbt loads each CSV into your data warehouse as a table.

What is a dbt seed?

A seed is a CSV file that dbt uploads to your warehouse as a table via a batch load job. Seeds let you version-control reference data (like lookup tables or test datasets) alongside your transformation code.

In production, raw data would come from tools like Fivetran or Dagster. The transformation layer you build on top of seeds is identical to what you would build on top of those live sources.

  • In VS Code, create a new file called raw_merchants.csv inside the seeds/ directory of your bluecode_payments project.
  • Paste the following merchant data into the file:
merchant_id,merchant_name,category,country,onboarded_at
merch_01,Cafe Central,food_and_beverage,AT,2023-06-15
merch_02,TechStore Vienna,electronics,AT,2023-07-01
merch_03,BookWorld Online,retail,DE,2023-08-10
merch_04,FitLife Gym,health_and_fitness,AT,2023-09-01
merch_05,Alpine Sports,retail,CH,2023-10-15
  • Save raw_merchants.csv.
  • Create another new file called raw_customers.csv in the same seeds/ directory.
  • Paste the following customer data:
customer_id,customer_name,country,signup_date,account_type
cust_01,Anna Mueller,AT,2023-07-01,bank_linked
cust_02,Thomas Berger,AT,2023-07-15,bank_linked
cust_03,Maria Schmidt,DE,2023-08-01,prepaid
cust_04,Lukas Weber,AT,2023-08-20,bank_linked
cust_05,Sophie Gruber,AT,2023-09-01,prepaid
cust_06,Hans Keller,CH,2023-09-15,bank_linked
cust_07,Eva Hofmann,AT,2023-10-01,bank_linked
cust_08,Felix Bauer,DE,2023-10-15,bank_linked
cust_09,Clara Wagner,AT,2023-11-01,prepaid
cust_10,Michael Steiner,CH,2023-11-15,bank_linked
  • Save raw_customers.csv.
  • Confirm both files exist by running this command in your terminal:
ls seeds/

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

Now create the main transaction data file. This is the largest seed with 50 rows of realistic payment events including successes, failures, retries, refunds, and chargebacks.

  • Create a new file called raw_transactions.csv in the seeds/ directory.
  • Paste the following header and first 25 transactions:
transaction_id,customer_id,merchant_id,amount,currency,payment_method,status,channel,created_at,settled_at,original_transaction_id
txn_001,cust_01,merch_01,25.50,EUR,bank_account,success,app,2024-01-05 10:23:00,2024-01-05 14:45:00,
txn_002,cust_02,merch_02,89.99,EUR,bank_account,success,pos,2024-01-05 11:02:00,2024-01-05 16:30:00,
txn_003,cust_03,merch_01,12.00,EUR,prepaid,failed,app,2024-01-05 12:15:00,,
txn_004,cust_03,merch_01,12.00,EUR,prepaid,success,app,2024-01-05 12:16:00,2024-01-05 17:00:00,txn_003
txn_005,cust_04,merch_03,150.00,EUR,bank_account,success,online,2024-01-06 09:00:00,2024-01-06 15:20:00,
txn_006,cust_05,merch_02,34.75,EUR,prepaid,success,pos,2024-01-06 10:45:00,2024-01-06 14:10:00,
txn_007,cust_01,merch_04,200.00,EUR,bank_account,failed,app,2024-01-07 08:30:00,,
txn_008,cust_01,merch_04,200.00,EUR,bank_account,success,app,2024-01-07 08:31:00,2024-01-07 13:00:00,txn_007
txn_009,cust_06,merch_01,45.00,CHF,bank_account,success,app,2024-01-08 11:00:00,2024-01-08 16:45:00,
txn_010,cust_07,merch_05,78.50,EUR,bank_account,success,pos,2024-01-08 14:20:00,2024-01-08 19:00:00,
txn_011,cust_02,merch_02,89.99,EUR,bank_account,chargeback,pos,2024-01-09 09:00:00,,txn_002
txn_012,cust_08,merch_03,320.00,EUR,bank_account,success,online,2024-01-10 10:00:00,2024-01-10 16:30:00,
txn_013,cust_09,merch_01,15.99,EUR,prepaid,success,app,2024-01-10 12:30:00,2024-01-10 17:15:00,
txn_014,cust_04,merch_03,150.00,EUR,bank_account,refund,online,2024-01-11 08:00:00,,txn_005
txn_015,cust_10,merch_02,55.00,CHF,bank_account,success,pos,2024-01-12 09:30:00,2024-01-12 14:00:00,
txn_016,cust_01,merch_01,30.00,EUR,bank_account,success,app,2024-01-15 10:00:00,2024-01-15 15:30:00,
txn_017,cust_03,merch_04,99.00,EUR,prepaid,failed,online,2024-01-15 11:00:00,,
txn_018,cust_03,merch_04,99.00,EUR,prepaid,failed,online,2024-01-15 11:01:00,,txn_017
txn_019,cust_03,merch_04,99.00,EUR,prepaid,success,online,2024-01-15 11:05:00,2024-01-15 16:00:00,txn_017
txn_020,cust_05,merch_05,42.00,EUR,bank_account,success,pos,2024-01-16 13:00:00,2024-01-16 18:20:00,
txn_021,cust_06,merch_01,60.00,CHF,bank_account,success,app,2024-01-18 09:15:00,2024-01-18 14:00:00,
txn_022,cust_07,merch_02,125.00,EUR,bank_account,success,pos,2024-01-20 10:30:00,2024-01-20 15:45:00,
txn_023,cust_08,merch_03,250.00,EUR,bank_account,failed,online,2024-01-22 14:00:00,,
txn_024,cust_09,merch_01,18.50,EUR,prepaid,success,app,2024-01-25 11:30:00,2024-01-25 16:00:00,
txn_025,cust_10,merch_05,95.00,EUR,bank_account,success,pos,2024-01-28 09:00:00,2024-01-28 14:30:00,
  • Now paste the remaining 25 transactions directly below the last row (no blank line between them):
txn_026,cust_01,merch_02,67.00,EUR,bank_account,success,app,2024-02-01 10:00:00,2024-02-01 15:00:00,
txn_027,cust_02,merch_01,22.50,EUR,bank_account,success,pos,2024-02-02 11:30:00,2024-02-02 16:45:00,
txn_028,cust_04,merch_03,180.00,EUR,bank_account,success,online,2024-02-03 09:00:00,2024-02-03 14:20:00,
txn_029,cust_05,merch_04,55.00,EUR,prepaid,failed,pos,2024-02-05 10:00:00,,
txn_030,cust_05,merch_04,55.00,EUR,prepaid,success,pos,2024-02-05 10:02:00,2024-02-05 15:30:00,txn_029
txn_031,cust_06,merch_01,38.00,CHF,bank_account,success,app,2024-02-06 12:00:00,2024-02-06 17:00:00,
txn_032,cust_07,merch_05,110.00,EUR,bank_account,success,pos,2024-02-08 14:00:00,2024-02-08 19:30:00,
txn_033,cust_08,merch_02,75.00,EUR,bank_account,chargeback,online,2024-02-10 09:00:00,,txn_028
txn_034,cust_09,merch_01,29.99,EUR,prepaid,success,app,2024-02-12 11:00:00,2024-02-12 16:15:00,
txn_035,cust_10,merch_03,200.00,EUR,bank_account,success,online,2024-02-15 10:30:00,2024-02-15 15:45:00,
txn_036,cust_01,merch_04,145.00,EUR,bank_account,success,app,2024-02-18 09:00:00,2024-02-18 14:00:00,
txn_037,cust_02,merch_02,88.00,EUR,bank_account,success,pos,2024-02-20 10:00:00,2024-02-20 15:30:00,
txn_038,cust_03,merch_01,16.00,EUR,prepaid,success,app,2024-02-22 12:00:00,2024-02-22 17:00:00,
txn_039,cust_04,merch_05,63.00,EUR,bank_account,refund,pos,2024-02-25 08:00:00,,txn_025
txn_040,cust_05,merch_03,92.00,EUR,bank_account,success,online,2024-02-28 11:00:00,2024-02-28 16:20:00,
txn_041,cust_06,merch_01,50.00,CHF,bank_account,success,app,2024-03-01 10:00:00,2024-03-01 15:00:00,
txn_042,cust_07,merch_02,135.00,EUR,bank_account,success,pos,2024-03-03 09:30:00,2024-03-03 14:45:00,
txn_043,cust_08,merch_04,210.00,EUR,bank_account,failed,online,2024-03-05 11:00:00,,
txn_044,cust_08,merch_04,210.00,EUR,bank_account,success,online,2024-03-05 11:03:00,2024-03-05 16:30:00,txn_043
txn_045,cust_09,merch_01,24.00,EUR,prepaid,success,app,2024-03-08 12:00:00,2024-03-08 17:00:00,
txn_046,cust_10,merch_05,170.00,EUR,bank_account,success,pos,2024-03-10 10:00:00,2024-03-10 15:15:00,
txn_047,cust_01,merch_03,300.00,EUR,bank_account,success,online,2024-03-12 09:00:00,2024-03-12 14:30:00,
txn_048,cust_02,merch_01,19.00,EUR,bank_account,chargeback,app,2024-03-15 10:00:00,,txn_027
txn_049,cust_03,merch_02,72.00,EUR,prepaid,success,pos,2024-03-18 11:30:00,2024-03-18 16:45:00,
txn_050,cust_04,merch_04,88.00,EUR,bank_account,success,online,2024-03-20 14:00:00,2024-03-20 19:00:00,

What does this transaction data contain?

This synthetic dataset mirrors real mobile payment events. Notice a few important details:

  • Mixed statuses: success, failed, refund, and chargeback represent the full lifecycle of a payment.
  • Retry chains: some transactions reference an original_transaction_id, meaning they are retries of a previous failed attempt.
  • Multiple currencies (EUR, CHF) and channels (app, pos, online) for segmentation.
  • Save raw_transactions.csv.
  • Confirm all three seed files exist by running:
ls seeds/

You should see raw_customers.csv, raw_merchants.csv, and raw_transactions.csv listed.

Don't see all three files?

  • Make sure you saved each file inside the seeds/ directory, not the project root.
  • Check that your terminal is in the bluecode_payments project folder.
  • Verify there are no typos in the filenames. They must match exactly: raw_transactions.csv, raw_merchants.csv, raw_customers.csv.

Help me troubleshoot my seed files not appearing in the seeds/ directory.

Build staging models

Staging models are the first transformation layer in a dbt project. They sit between raw data and business logic, responsible for cleaning column names, casting types, and adding simple computed fields.

  • Delete the models/example/ directory that dbt init created by default.
  • Create a new folder called staging inside the models/ directory.
  • Create a new file called stg_transactions.sql inside models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_transactions') }}
),

staged as (
    select
        transaction_id,
        customer_id,
        merchant_id,
        cast(amount as numeric) as amount,
        currency,
        payment_method,
        status,
        channel,
        cast(created_at as timestamp) as created_at,
        cast(settled_at as timestamp) as settled_at,
        original_transaction_id,
        case
            when status = 'success' then true
            else false
        end as is_successful,
        case
            when settled_at is not null then
                timestamp_diff(cast(settled_at as timestamp), cast(created_at as timestamp), minute)
            else null
        end as settlement_minutes
    from source
)

select * from staged

What does this model do?

  • ref('raw_transactions') tells dbt to read from the seed table it loaded into BigQuery.
  • cast() converts string columns to proper types (numeric for money, timestamp for dates).
  • is_successful is a computed boolean that simplifies downstream filtering.
  • settlement_minutes calculates how long each transaction took to settle, using timestamp_diff.
  • Save stg_transactions.sql.
  • Create a new file called stg_merchants.sql in models/staging/ and paste the following:
with source as (
    select * from {{ ref('raw_merchants') }}
),

staged as (
    select
        merchant_id,
        merchant_name,
        category,
        country,
        cast(onboarded_at as date) as onboarded_at
    from source
)

select * from staged
  • Save stg_merchants.sql.
  • Confirm your staging directory has two files by running:
ls models/staging/

You should see stg_merchants.sql and stg_transactions.sql listed.

  • Create one more file called stg_customers.sql in models/staging/ and paste:
with source as (
    select * from {{ ref('raw_customers') }}
),

staged as (
    select
        customer_id,
        customer_name,
        country,
        cast(signup_date as date) as signup_date,
        account_type
    from source
)

select * from staged

Why the same pattern for all three models?

Every staging model follows the same CTE structure: pull from the raw source, rename and cast columns, then select the final result. This consistency makes your project predictable and easy to maintain.

  • Save stg_customers.sql.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Here is the full stg_transactions.sql for reference:

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

staged as (
    select
        transaction_id,
        customer_id,
        merchant_id,
        cast(amount as numeric) as amount,
        currency,
        payment_method,
        status,
        channel,
        cast(created_at as timestamp) as created_at,
        cast(settled_at as timestamp) as settled_at,
        original_transaction_id,
        case
            when status = 'success' then true
            else false
        end as is_successful,
        case
            when settled_at is not null then
                timestamp_diff(cast(settled_at as timestamp), cast(created_at as timestamp), minute)
            else null
        end as settlement_minutes
    from source
)

select * from staged

Here is stg_merchants.sql:

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

staged as (
    select
        merchant_id,
        merchant_name,
        category,
        country,
        cast(onboarded_at as date) as onboarded_at
    from source
)

select * from staged

And stg_customers.sql:

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

staged as (
    select
        customer_id,
        customer_name,
        country,
        cast(signup_date as date) as signup_date,
        account_type
    from source
)

select * from staged
Run the pipeline and verify in BigQuery

Everything is in place. The dbt build command runs seeds, models, and tests together in dependency order.

Before you run it, what do you expect to happen? Think about it: dbt needs to upload 3 CSV files as tables and then create 3 views on top of them.

  • Run the full pipeline by executing this command:
dbt build

You should see output showing each seed loading successfully and each model completing. Look for green "OK" or "PASS" markers next to each entry.

What did dbt build just do?

  • Loaded raw_transactions.csv, raw_merchants.csv, and raw_customers.csv as tables in your BigQuery raw schema.
  • Created stg_transactions, stg_merchants, and stg_customers as views in your BigQuery staging schema.

Now verify the data landed correctly in BigQuery.

  • Open the BigQuery console in your browser at console.cloud.google.com/bigquery.
  • In the query editor, run the following SQL to count rows in your staging transactions view:
select count(*) as row_count from `[[GCP_PROJECT="your-gcp-project-id"]].[[DATASET_STAGING="your_dataset_staging"]].stg_transactions`

You should see 50 as the result. That confirms all 50 transaction rows flowed from the CSV seed through to the staging view with clean types.

Seeing 0 rows or an error?

  • Check that dbt build completed without errors in your terminal. If a seed failed, verify the CSV has no extra blank lines or inconsistent column counts.
  • Make sure you are querying the correct dataset. Expand your project in the BigQuery sidebar and look for a dataset ending in _staging.
  • If you see a "not found" error, double-check that the project ID in the query matches your GCP project.

Help me troubleshoot why my dbt staging view is not returning 50 rows in BigQuery.

Your payment data is now clean, typed, and ready for analysis. Next up, you will build your first mart model that calculates real payment KPIs directly from these staging views.

Calculate Payment KPIs Directly from Staging

Your staging models are clean, typed, and queryable in BigQuery. The natural next move is to jump straight from that clean data to business metrics.

In this step, you will build a mart model that computes the three payment KPIs a finance team cares about most: transaction success rate, average settlement lag, and chargeback ratio. You will calculate them directly from stg_transactions and see what happens.

In this step, get ready to:
  • Create a mart model that calculates payment KPIs grouped by channel and currency.
  • Run the model and query its results in BigQuery.
  • Compare the output against a manual count to check data accuracy.
Create the mart model

A mart is the final layer in a dbt project. It is where raw data becomes the tables a product manager or analyst queries daily.

  • In VS Code, create a new file at models/marts/mart_payment_kpis.sql.
  • Paste the following code into the file to set up the data filters and calculate the core payment metrics:
-- Naive version reading directly from stg_transactions (produces inflated KPIs)
with payments as (
    select * from {{ ref('stg_transactions') }}
    where status in ('success', 'failed')
),

chargebacks as (
    select * from {{ ref('stg_transactions') }}
    where status = 'chargeback'
),

payment_metrics as (
    select
        channel,
        currency,
        count(*) as total_transactions,
        countif(is_successful) as successful_transactions,
        round(countif(is_successful) / count(*) * 100, 1) as success_rate_pct,
        round(avg(case when is_successful then settlement_minutes end) / 60, 1) as avg_settlement_hours,
        sum(case when is_successful then amount else 0 end) as gross_payment_volume
    from payments
    group by channel, currency
),

What does this code do?

  • The payments CTE filters staging data to only success and failed transactions. The chargebacks CTE isolates chargeback events separately.
  • The payment_metrics CTE calculates success rate, average settlement time (converted from minutes to hours), and gross payment volume, all grouped by channel and currency.
  • The countif(is_successful) function counts only rows where the is_successful boolean from your staging model is true.
  • Below the code you just pasted, add the chargeback calculation and final assembly that joins everything together:
chargeback_metrics as (
    select
        channel,
        currency,
        count(*) as chargeback_count,
        sum(amount) as chargeback_volume
    from chargebacks
    group by channel, currency
),

final as (
    select
        p.channel,
        p.currency,
        p.total_transactions,
        p.successful_transactions,
        p.success_rate_pct,
        p.avg_settlement_hours,
        p.gross_payment_volume,
        coalesce(cb.chargeback_count, 0) as chargeback_count,
        coalesce(cb.chargeback_volume, 0) as chargeback_volume,
        round(
            coalesce(cb.chargeback_count, 0) / nullif(p.successful_transactions, 0) * 100,
            2
        ) as chargeback_rate_pct
    from payment_metrics p
    left join chargeback_metrics cb
        on p.channel = cb.channel
        and p.currency = cb.currency
)

select * from final

What does this code do?

  • The chargeback_metrics CTE counts chargebacks and sums their volume, grouped by channel and currency.
  • The final CTE joins payment metrics with chargeback metrics using a left join so channels with no chargebacks still appear.
  • The chargeback_rate_pct divides chargeback count by successful transactions to show what percentage of completed payments were later disputed.
  • The coalesce function replaces null chargeback values with 0. The nullif prevents a divide-by-zero error if a channel has no successful transactions.
  • Save models/marts/mart_payment_kpis.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file before moving on.

ⓧ I'd like to double check the full code

Your file should contain these sections in order:

  • A comment at the top describing the model.
  • A payments CTE filtering for success and failed statuses.
  • A chargebacks CTE filtering for chargeback status.
  • A payment_metrics CTE with the grouped KPI calculations.
  • A chargeback_metrics CTE counting chargebacks by channel and currency.
  • A final CTE joining payment metrics with chargeback metrics.
  • A select * from final at the end.

Scroll up to the two code blocks above and confirm each section is present in your file in the correct order.

File not saving?

  • Make sure the models/marts/ folder exists. VS Code will create it automatically when you create a file at that path using the New File dialog, but if you are using the terminal you may need to run mkdir models\marts first.
  • Confirm the file is named exactly mart_payment_kpis.sql with no typos.

Why can't I save my mart model file?

Run and inspect results

Your mart model is ready. Time to materialize it in BigQuery and check whether the numbers look right.

  • Build the mart model by running this command in your terminal:
dbt run

You should see output showing your staging views and the new mart table being created, ending with a summary like Completed successfully.

Seeing a compilation error?

  • Check that your SQL file has no missing commas between CTEs. Each CTE except the last (final) must end with ), (closing parenthesis plus comma).
  • Make sure the ref('stg_transactions') uses single quotes, not double quotes.
  • Confirm your virtual environment is still active (you should see the environment name in your terminal prompt).

Why is my dbt run failing with a compilation error?

Now query the mart to see the KPIs. Before you do, think about this: you have 50 transactions in the seed data. Some failed, some succeeded. What overall success rate do you expect to see?

  • Open the BigQuery console in your browser.
  • Run this query in the BigQuery SQL editor to view your KPIs:
select
    channel,
    currency,
    total_transactions,
    successful_transactions,
    success_rate_pct,
    avg_settlement_hours,
    chargeback_rate_pct
from `[[GCP_PROJECT="your-gcp-project-id"]].bluecode_payments_marts.mart_payment_kpis`
order by channel, currency

Look at the success_rate_pct column. You will see values around 84% for the app channel.

That number is too high. If you manually count the distinct successful payment attempts in the raw data, the actual success rate is closer to 72%.

Why are the numbers wrong?

The seed data contains retry chains. When a payment fails, the customer retries and the system links the new attempt to the original via original_transaction_id. For example, txn_003 fails, and txn_004 succeeds as its retry.

Your naive mart counts BOTH as separate transactions. It counts txn_003 as one failed attempt AND txn_004 as one successful attempt. This inflates both the total attempt count and the success count, skewing the rate upward.

The same problem affects txn_017 through txn_019 (two failed retries before a success) and several other chains in the data. You cannot calculate accurate KPIs without deduplicating these retry chains first.

Your mart is calculating KPIs, but the numbers are wrong because duplicate retry attempts inflate the metrics. In the next step, you will fix this by building an intermediate layer that deduplicates transaction chains before the mart sees them.

Fix the Numbers with an Intermediate Enrichment Layer

In the previous step, your mart reported an 84% payment success rate. The real number should be closer to 72%.

The inflation happens because retry attempts are counted as separate transactions. When a payment fails and the customer retries, staging shows both the failure and the retry as independent rows. Production analytics teams solve this with an intermediate layer that deduplicates and classifies data before it reaches any business metrics.

In this step, get ready to:
  • Build an intermediate model that deduplicates retry attempts and classifies transaction types.
  • Update the mart to read from the intermediate layer instead of staging.
  • Verify the KPIs now show correct numbers.
Build the intermediate model

The intermediate layer sits between staging and marts. Its job is to deduplicate retry chains and enrich each transaction with merchant and customer context.

Why an intermediate layer?

In the staging-to-marts pattern, staging models clean and type raw data. Marts calculate business metrics. But some transformations are too complex for either layer alone.

Intermediate models handle deduplication, joins, and classification that staging is too early for and marts should not repeat across multiple downstream tables.

  • Create a new file called int_transactions_enriched.sql inside the models/intermediate/ folder of your bluecode_payments project.
  • Start the file with the following code that pulls in source CTEs and deduplicates retry attempts:
with transactions as (
    select * from {{ ref('stg_transactions') }}
),

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

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

final_attempts as (
    select
        t.*,
        row_number() over (
            partition by coalesce(t.original_transaction_id, t.transaction_id)
            order by t.created_at desc
        ) as attempt_rank
    from transactions t
    where t.status in ('success', 'failed')
),

deduplicated_payments as (
    select * from final_attempts
    where attempt_rank = 1
),

What does this code do?

  • The three source CTEs (transactions, merchants, customers) pull in the cleaned staging data you built earlier.
  • The final_attempts CTE uses ROW_NUMBER() partitioned by the transaction chain (coalesce(original_transaction_id, transaction_id)) to rank attempts newest-first.
  • The deduplicated_payments CTE keeps only the final attempt (attempt_rank = 1) per chain, eliminating double-counted retries.
  • Directly below the deduplicated_payments CTE, add the lifecycle events and combination logic:
lifecycle_events as (
    select * from transactions
    where status in ('refund', 'chargeback')
),

combined as (
    select * from deduplicated_payments
    union all
    select
        *,
        1 as attempt_rank
    from lifecycle_events
),

What does this code do?

Refunds and chargebacks are not payment attempts. They are lifecycle events that happened after a successful payment. The lifecycle_events CTE keeps them separate, and combined merges them back with the deduplicated payments so the mart can calculate chargeback ratios correctly.

  • Complete the file by adding the enrichment CTE and final select below the combined CTE:
enriched as (
    select
        c.transaction_id,
        c.customer_id,
        c.merchant_id,
        c.amount,
        c.currency,
        c.payment_method,
        c.status,
        c.channel,
        c.created_at,
        c.settled_at,
        c.original_transaction_id,
        c.is_successful,
        c.settlement_minutes,
        m.merchant_name,
        m.category as merchant_category,
        m.country as merchant_country,
        cust.customer_name,
        cust.account_type,
        case
            when c.status in ('success', 'failed') then 'payment'
            when c.status = 'refund' then 'refund'
            when c.status = 'chargeback' then 'chargeback'
        end as transaction_type
    from combined c
    left join merchants m on c.merchant_id = m.merchant_id
    left join customers cust on c.customer_id = cust.customer_id
)

select * from enriched

What does this code do?

  • The enriched CTE joins merchant and customer context onto each transaction, adding columns like merchant_name and account_type.
  • The transaction_type column classifies each row as payment, refund, or chargeback. The mart can now filter cleanly by type instead of relying on raw status values.
  • Save int_transactions_enriched.sql.
  • Verify the intermediate model builds by running:
dbt run

You should see a line like OK created sql view model for int_transactions_enriched and the run finishes with Completed successfully.

Seeing a compilation error?

  • Check that your file is saved inside models/intermediate/ (not models/staging/).
  • Make sure the three code chunks are pasted in order with no gaps or duplicate commas between CTEs.
  • Confirm there is no trailing comma after the closing parenthesis of enriched (the last CTE before select * from enriched).

Still stuck? Why is my dbt run failing with a compilation error on int_transactions_enriched?

✔️ Awesome, I've got everything!

Great. Make sure you have saved int_transactions_enriched.sql and the model built successfully.

ⓧ I'd like to double check the full code

Your int_transactions_enriched.sql file is over 30 lines, so confirm these sections are present in order:

  • The with keyword opens the file, followed by transactions, merchants, and customers source CTEs.
  • The final_attempts CTE with the row_number() window function, followed by deduplicated_payments.
  • The lifecycle_events and combined CTEs with union all.
  • The enriched CTE with joins and transaction_type classification, ending with select * from enriched.
Update the mart to use the intermediate model

The intermediate model is live, but the mart still reads from staging. Time to rewire it.

  • Open models/marts/mart_payment_kpis.sql.
  • Find the top of the file where the payments and chargebacks CTEs are defined. It currently looks like this:
-- Naive version reading directly from stg_transactions (produces inflated KPIs)
with payments as (
    select * from {{ ref('stg_transactions') }}
    where status in ('success', 'failed')
),

chargebacks as (
    select * from {{ ref('stg_transactions') }}
    where status = 'chargeback'
),
  • Replace that entire block (including the comment at the top) with the following:
with payments as (
    select * from {{ ref('int_transactions_enriched') }}
    where transaction_type = 'payment'
),

chargebacks as (
    select * from {{ ref('int_transactions_enriched') }}
    where transaction_type = 'chargeback'
),

What changed?

  • The source changed from stg_transactions to int_transactions_enriched. The mart now reads deduplicated data.
  • The filter changed from status in ('success', 'failed') to transaction_type = 'payment'. The intermediate model already classified each row, so the mart uses that clean label.
  • Save mart_payment_kpis.sql.

Before you run this, do you think the success rate will still be 84%, or will it drop? Why?

  • Rebuild the models by running:
dbt run

The run should complete successfully with all models showing OK.

Seeing a dependency error?

  • Make sure you spelled int_transactions_enriched exactly the same in the ref() call and in the filename.
  • Check that int_transactions_enriched.sql is inside models/intermediate/ (not models/marts/).

Still stuck? Why is dbt saying it cannot find the model int_transactions_enriched?

Query the corrected KPIs

The mart has been rebuilt with deduplicated data. Time to check whether the numbers are correct.

  • Switch to the BigQuery console in your browser.
  • Run the following query in the query editor (replace the dataset name with your actual dataset):
select
    channel,
    currency,
    total_transactions,
    success_rate_pct,
    avg_settlement_hours,
    chargeback_rate_pct
from [[DATASET="your_dataset_marts"]].mart_payment_kpis
order by channel, currency

You should see results where the overall success rate across channels is approximately 72%, not 84%. The avg_settlement_hours column shows settlement time per channel, and chargeback_rate_pct shows chargebacks as a percentage of successful transactions.

Why did the numbers change?

The naive mart counted every retry attempt as a separate transaction. For example, txn_017 (failed), txn_018 (failed retry), and txn_019 (success retry) were three rows. The naive mart counted them as 3 attempts with 1 success (33% for that chain), but it also inflated the total attempt count across all channels.

The intermediate model collapses that chain to a single row: the final attempt txn_019 (success). Now the mart counts 1 transaction with 1 success for that chain.

Not seeing the mart table in BigQuery?

  • Make sure dbt run completed without errors. Look for Completed successfully in the terminal output.
  • Check that you are querying the correct dataset. Your marts dataset name includes the schema suffix _marts (e.g., dbt_marts).

Still stuck? Why can I not see mart_payment_kpis in my BigQuery dataset?

Your KPIs are now trustworthy. The intermediate layer eliminated the double-counting that inflated your metrics. Next up, you will add dbt tests and generate documentation to prove this data layer is production-ready.

Add dbt Tests and Generate Documentation

Your KPIs are now accurate thanks to the intermediate deduplication layer. But correct models without tests are a ticking time bomb.

If upstream data changes tomorrow (new payment methods, null IDs, duplicate records), your KPIs will silently break. In this step, you will add dbt schema tests to catch data quality issues before they reach stakeholders, then generate a documentation site that visualizes your entire pipeline lineage.

In this step, get ready to:
  • Define schema tests (unique, not_null, accepted_values, relationships) for every model layer.
  • Run tests and intentionally break one to prove the safety net catches bad data.
  • Generate dbt documentation with a full lineage DAG.
Add schema tests

dbt schema tests live in YAML files alongside your models. Each test declares an expectation about a column, and dbt automatically generates a SQL query to check it against your live data.

  • Create a new file called schema.yml inside your models/staging/ folder.
  • Add the following YAML to define tests for stg_transactions:
version: 2

models:
  - name: stg_transactions
    description: "Cleaned and typed payment transactions from the raw seed data."
    columns:
      - name: transaction_id
        description: "Unique identifier for each transaction event."
        tests:
          - unique
          - not_null
      - name: status
        description: "Transaction outcome: success, failed, refund, or chargeback."
        tests:
          - accepted_values:
              values: ['success', 'failed', 'refund', 'chargeback']
      - name: channel
        description: "Payment channel: app, pos, or online."
        tests:
          - accepted_values:
              values: ['app', 'pos', 'online']
      - name: merchant_id
        description: "Reference to the merchant receiving the payment."
        tests:
          - not_null
          - relationships:
              to: ref('stg_merchants')
              field: merchant_id

What do these tests do?

  • unique confirms no duplicate transaction_id values exist in the table.
  • not_null ensures critical columns always have a value.
  • accepted_values checks that every row contains one of the listed values. If a new status like pending appeared, this test would catch it.
  • relationships works like a foreign key check. Every merchant_id in transactions must exist in stg_merchants.
  • Below the stg_transactions model definition (still inside the same schema.yml file), add tests for the merchants and customers models:

  - name: stg_merchants
    description: "Merchant profiles with category and location."
    columns:
      - name: merchant_id
        description: "Unique merchant identifier."
        tests:
          - unique
          - not_null

  - name: stg_customers
    description: "Customer profiles with account type."
    columns:
      - name: customer_id
        description: "Unique customer identifier."
        tests:
          - unique
          - not_null

What do these tests do?

These ensure every merchant and customer has a unique, non-null primary key. If a seed CSV ever introduced a duplicate merchant_id, the unique test would fail immediately.

✔️ Awesome, I've got everything!

Great. Make sure you have saved models/staging/schema.yml.

ⓧ I'd like to double check the full code

Your models/staging/schema.yml should contain both chunks above in order. Scroll up and confirm:

  • The file starts with version: 2 and defines stg_transactions with four tested columns (transaction_id, status, channel, merchant_id).
  • Immediately below, stg_merchants and stg_customers each have unique and not_null tests on their ID columns.
  • Save models/staging/schema.yml.
  • Confirm your staging tests work by running:
dbt test

You should see each test listed with PASS next to it, and a summary line showing zero errors.

Seeing a YAML parsing error?

  • YAML is indentation-sensitive. Make sure you use spaces (not tabs) and that each - name: entry is indented consistently at two spaces per level.
  • Check that values: in the accepted_values test is on the line below and indented further than accepted_values:.

Why is my dbt test failing with a YAML error?

  • Now create a new file called schema.yml inside your models/intermediate/ folder and add the following:
version: 2

models:
  - name: int_transactions_enriched
    description: >
      Deduplicated and enriched transaction records. Retry attempts are collapsed
      to their final outcome. Refunds and chargebacks are preserved as separate
      lifecycle events. Joined with merchant and customer context.
    columns:
      - name: transaction_id
        description: "Unique identifier for each transaction event."
        tests:
          - unique
          - not_null
      - name: transaction_type
        description: "Classified as payment, refund, or chargeback."
        tests:
          - not_null
          - accepted_values:
              values: ['payment', 'refund', 'chargeback']

What does this test?

The unique test on transaction_id confirms that the deduplication logic actually works. If the intermediate model ever let duplicate retries through, this test would catch it.

The accepted_values test on transaction_type ensures every row is classified as payment, refund, or chargeback. No unclassified rows can slip through.

  • Save models/intermediate/schema.yml.
  • Create one more schema.yml file inside models/marts/ and add the following:
version: 2

models:
  - name: mart_payment_kpis
    description: >
      Payment performance KPIs segmented by channel and currency.
      Includes success rate, average settlement time, gross payment volume,
      and chargeback ratio. Built from deduplicated transaction data.
    columns:
      - name: channel
        description: "Payment channel: app, pos, or online."
        tests:
          - not_null
      - name: currency
        description: "Transaction currency code."
        tests:
          - not_null
      - name: success_rate_pct
        description: "Percentage of payment attempts that succeeded."
      - name: avg_settlement_hours
        description: "Average hours from transaction creation to settlement."
      - name: chargeback_rate_pct
        description: "Chargebacks as percentage of successful transactions."

Why only not_null tests here?

The mart groups rows by channel and currency. If either were null, you would get an unlabeled row in your KPI output that silently hides data from the other groups. The not_null test prevents that.

The metric columns (success_rate_pct, avg_settlement_hours, chargeback_rate_pct) are documented but not tested. They are computed values, so a not_null test would be too strict (some groups legitimately have no chargebacks).

  • Save models/marts/schema.yml.
Run tests and verify

All three schema files are in place. Time to run the full test suite and prove every expectation holds.

  • Before you run this, how many individual test assertions do you think dbt will execute across all three schema files?
  • Run all tests by executing:
dbt test

You should see every test listed with PASS and a final summary showing PASS=13 with zero errors. That is 13 individual assertions across your three schema files.

Seeing a test failure?

  • If the relationships test fails, confirm that you have already run dbt seed and dbt run at least once. Tests query live tables, so the models need to exist.
  • If a unique test fails on int_transactions_enriched, double check the deduplication logic in that model. Re-run dbt run then dbt test again.

Why is my dbt test failing?

Tests passing is great, but how do you know they actually catch problems? Let's intentionally break one.

  • In models/staging/schema.yml, find the accepted_values test for the status column.
  • Find this line:
              values: ['success', 'failed', 'refund', 'chargeback']
  • Remove 'chargeback' from the list so it looks like this:
              values: ['success', 'failed', 'refund']
  • Save the file and run the tests again:
dbt test

You should now see the accepted_values test for status report FAIL. dbt found rows with chargeback that are no longer in the allowed list. That is your safety net in action.

Why does this matter?

In production, this same test would fire if an upstream system introduced a new status value like pending that your models do not handle. You would catch the issue in CI before stakeholders ever see incorrect numbers.

  • Now revert the change. Add 'chargeback' back so the line reads:
              values: ['success', 'failed', 'refund', 'chargeback']
  • Save models/staging/schema.yml and confirm the tests pass again by running:
dbt test

All 13 tests should show PASS again. Your data layer now has a safety net that catches silent breakage.

Generate and explore documentation

Tests prove your models are correct. Documentation proves your data layer is understandable. dbt can auto-generate a documentation site from the descriptions and tests you just wrote.

  • Generate the documentation catalog by running:
dbt docs generate

You should see output confirming that dbt compiled your project and generated a catalog of all models, sources, and tests.

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

What does this command do?

This starts a local web server on port 8080 and opens your browser to the dbt documentation site. It will keep your terminal occupied while the server runs.

To stop the server later, press Ctrl+C in the terminal.

What do you expect to see when the lineage DAG loads? Think about how many layers your data flows through.

Your browser opens to http://localhost:8080. Click the lineage graph icon (bottom-right corner of the page) to see the full DAG. You should see your three seed nodes (raw_transactions, raw_merchants, raw_customers) flowing into staging views, then into int_transactions_enriched, and finally into mart_payment_kpis.

Browser did not open automatically?

  • Manually open http://localhost:8080 in your browser.
  • If you see a connection refused error, check that the dbt docs serve command is still running in your terminal.

Why can't I see the dbt documentation site?

Your pipeline is now tested, documented, and visualized. Next up, you will extend it with a monthly trend analysis in the Secret Mission.

Secret mission

Build a Monthly Trend Analysis Mart

Your KPI mart gives a static snapshot of payment health. But a product manager's first question will always be: are things getting better or worse? In this secret mission, you'll build a monthly trend analysis mart using SQL window functions to calculate month-over-month changes in payment volume, success rate, and chargeback ratio.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on the BigQuery sandbox, which is free indefinitely with no billing account required. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • A Google Cloud project with BigQuery sandbox (free, no billing enabled).
  • dbt-generated tables and views in your BigQuery dataset (within 10 GB free storage limit).
  • Local bluecode_payments project folder on your Desktop (includes virtual environment and all dbt files).
  • Service account JSON keyfile downloaded to your machine.
  • dbt connection profile at ~/.dbt/profiles.yml.

Keep everything running

No action needed. Choose this if you plan to keep building on your dbt project or want to experiment with additional models.

  • BigQuery sandbox is free indefinitely. No billing account exists, so you cannot be charged.
  • Tables in sandbox mode automatically expire after 60 days if you do not query them.
  • Your local project files and virtual environment remain on your Desktop for future use.
  • The service account keyfile stays on disk. Keep it secure and do not commit it to version control.

Pause - I'll come back to this later

BigQuery sandbox has no running compute to pause. There is nothing to shut down or stop.

  • Your BigQuery tables will remain available for 60 days without any action.
  • If you return after 60 days and tables have expired, re-run dbt build from your local project to recreate everything.
  • Deactivate your Python virtual environment by running:
deactivate

Your local files stay intact and ready to pick up where you left off.

Delete - I don't want to use this again

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

  • Delete the Google Cloud project. In the Google Cloud console, go to the Manage resources page. Select your project, click Delete, enter the project ID, and click Shut down. This removes all BigQuery datasets, tables, views, and the service account.
  • Delete your local project folder by running:
Remove-Item -Recurse -Force ~\Desktop\bluecode_payments
  • Delete the service account JSON keyfile. If you saved it outside the project folder, locate and delete it manually from File Explorer.
  • Delete your dbt profile by running:
Remove-Item ~\.dbt\profiles.yml

What about Python and Google Cloud SDK?

Python and Google Cloud SDK are general-purpose tools you may use in other projects. You do not need to uninstall them unless you specifically want to reclaim disk space.

Nice Work!

Nice Work!

Nice work! You just built a production-style analytics engineering layer that transforms raw payment transactions into trusted KPI models using dbt and BigQuery.

You've learned how to:

  • Build a complete dbt project with a staging > intermediate > marts layer pattern that deduplicates retry attempts and produces correct payment KPIs like success rate, settlement lag, and chargeback ratio.
  • Discover why jumping from raw data directly to business metrics produces wrong numbers, and fix it with an intermediate enrichment layer that collapses transaction chains to their final outcome.
  • Add dbt tests and documentation that catch data quality issues before they reach stakeholders, with a full lineage DAG proving end-to-end data ownership.
  • Secret Mission: Built a monthly trend mart using LAG() window functions to calculate month-over-month changes in payment volume, success rate, and chargeback ratio.

Ready to quiz yourself?