Analytics Layer for Investor Reporting

Transform messy fintech data into a tested star schema with dbt.

Introduction

30 Second Summary

Every fintech startup reaches the moment when investors ask "What's your ARPU? What's your MAU?" and someone has to pull numbers from a database full of duplicates, missing values, and orphaned records. If the numbers are wrong, trust evaporates.

In this project, you will build a complete analytics layer using dbt that transforms messy raw fintech data into a tested, documented star schema producing trustworthy investor metrics on demand.

What You'll Build

One command rebuilds your entire analytical pipeline from raw CSV seeds through cleaned staging models to investor-ready fact and dimension tables, with automated tests catching any data quality regressions before bad numbers reach a pitch deck.

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

  • A running dbt project where you can type one command and rebuild the entire analytical layer from raw data to investor-ready metrics.
  • Automated data quality tests that catch duplicates, nulls, and referential integrity violations. You can add a new raw data source and the system tells you immediately if it breaks anything.
  • A full documentation site with a visual lineage graph you can open in a browser, showing which raw tables feed which models and which tests protect which columns.
  • Secret Mission: Write a custom test macro for business rules and build a monthly metrics model that calculates ARPU and MAU.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, JOIN, GROUP BY, WHERE) and familiar with using the terminal. This project assumes you already have Python and Docker installed on your machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This is your chance to think through the business problem before any hands-on work begins.

Set Up Docker PostgreSQL and dbt

To build an analytics layer, you need two things: a database to store and query data, and a transformation framework to make your SQL testable and documented.

In this step, you will spin up a PostgreSQL database using Docker and install dbt (data build tool), the framework that turns your raw SQL into a version-controlled, tested pipeline.

In this step, get ready to:
  • Start a PostgreSQL 16 container with Docker Compose.
  • Install dbt-core and the PostgreSQL adapter in a virtual environment.
  • Initialize a dbt project and confirm it connects to your database.
Start a PostgreSQL container with Docker

Docker lets you run a full PostgreSQL database in a disposable container. No manual installation, no system-level configuration.

  • Press the Super key to open your search bar.
  • Type Terminal and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir fintech-analytics
cd fintech-analytics
  • Confirm the folder exists by running:
ls ~/Desktop

You should see fintech-analytics listed in the output.

Docker Compose reads a YAML file to know which containers to run. You will create a docker-compose.yml file that defines a PostgreSQL 16 container.

  • Create the docker-compose.yml file by running this command:
cat > docker-compose.yml << 'EOF'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: dbt_user
      POSTGRES_PASSWORD: dbt_pass
      POSTGRES_DB: analytics
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
EOF

What does this file do?

  • image: postgres:16 pulls the official PostgreSQL 16 image from Docker Hub.
  • POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB set the database credentials and name that dbt will connect to.
  • ports: "5432:5432" maps the container's PostgreSQL port to your machine so dbt can reach it at localhost:5432.
  • The named volume pgdata persists your database between container restarts.
  • Start the PostgreSQL container in the background by running:
docker compose up -d
  • Verify the container is running by running:
docker compose ps

You should see a container with postgres:16 listed and a status of Up.

Container not starting?

  • Make sure Docker Engine is running. If docker compose returns an error about the daemon, start Docker first.
  • If port 5432 is already in use, another PostgreSQL instance may be running on your system. Stop it before retrying.

Need more help? Why is my Docker container failing to start?

Create a virtual environment and install dbt

A virtual environment isolates your project's Python packages from the rest of your system. This prevents version conflicts between projects.

  • Create and activate a Python virtual environment by running these commands:
python3 -m venv dbt-env
source dbt-env/bin/activate

Your terminal prompt now shows (dbt-env) at the beginning, confirming the virtual environment is active.

  • Upgrade pip and install dbt-postgres by running these commands:
python -m pip install --upgrade pip wheel setuptools
python -m pip install dbt-postgres

Why dbt-postgres and not dbt-core?

The dbt-postgres package automatically installs dbt-core as a dependency. You get both the framework and the PostgreSQL adapter in a single install command.

  • Verify the installation by running:
dbt --version

You should see output listing dbt-core version 1.12.0 and the dbt-postgres plugin.

Command not found after install?

  • Make sure your virtual environment is still active. Look for (dbt-env) in your prompt. If it disappeared, reactivate with source dbt-env/bin/activate.
  • If you see a permissions error, confirm you are using python -m pip install (not bare pip install outside the venv).

Need more help? Why can't my terminal find the dbt command after I installed it?

Initialize the dbt project and validate the connection

The dbt init command scaffolds a new project with the standard folder structure that dbt expects.

  • Initialize the project by running:
dbt init fintech_analytics --skip-profile-setup

What does --skip-profile-setup do?

By default, dbt init launches an interactive wizard that asks for your database connection details. The --skip-profile-setup flag skips those prompts so you can write the profile manually with exact values.

  • Move into the new project directory by running:
cd fintech_analytics
  • Confirm the scaffold was created by running:
ls

You should see dbt_project.yml, models/, and other scaffolded files.

The scaffold includes example models you do not need. You will replace them with the proper staging/ and marts/ directory structure.

  • Remove the example models and create the correct directories by running:
rm -rf models/example
mkdir -p models/staging models/marts seeds macros data-tests
  • Open the project in VS Code by running:
code .

VS Code opens with your fintech_analytics project loaded in the Explorer sidebar.

  • In VS Code, open dbt_project.yml from the Explorer sidebar.
  • Replace all the contents with this configuration:
name: fintech_analytics
profile: fintech_analytics

seed-paths: ["seeds"]
model-paths: ["models"]
test-paths: ["data-tests"]
macro-paths: ["macros"]

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

What does this configuration do?

  • profile: fintech_analytics tells dbt which connection profile to look up in profiles.yml.
  • The path settings point dbt to the directories where your models, seeds, tests, and macros live.
  • staging: +materialized: view makes staging models lightweight views (no data copied).
  • marts: +materialized: table makes mart models physical tables (faster for analytical queries).
  • Save dbt_project.yml with Ctrl+S.

Now you need to tell dbt how to connect to your PostgreSQL container. dbt looks for connection details in ~/.dbt/profiles.yml.

  • Create the profiles directory and file by running this command in VS Code's integrated terminal (Ctrl+` to open it):
mkdir -p ~/.dbt
cat > ~/.dbt/profiles.yml << 'EOF'
fintech_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: dbt_user
      password: dbt_pass
      dbname: analytics
      schema: public
      threads: 4
EOF

What do these profile fields mean?

  • host: localhost and port: 5432 match the port mapping in your docker-compose.yml.
  • user, password, and dbname match the POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB values from your Docker configuration.
  • threads: 4 means dbt can run up to 4 models in parallel during a build.

Before you run the next command, what do you think dbt debug will report? It checks your project file, your profile, and tries connecting to the database.

  • Validate the full setup by running:
dbt debug

You should see a series of checks ending with "All checks passed!" This confirms dbt can find your project, read your profile, and connect to the PostgreSQL container.

Seeing a connection error?

  • Confirm your Docker container is still running with docker compose ps (run this from ~/Desktop/fintech-analytics/).
  • Double-check that the user, password, and dbname values in ~/.dbt/profiles.yml match the POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB values in docker-compose.yml. They must be identical.
  • Make sure you are running dbt debug from inside the fintech_analytics/ directory (where dbt_project.yml lives).

Need more help? Why is dbt debug failing to connect to my PostgreSQL container?

✔️ Awesome, I've got everything!

Great. Double check you have saved dbt_project.yml and confirmed "All checks passed!" from dbt debug.

ⓧ I'd like to double check the full code

Here are the three configuration files you should have at this point.

docker-compose.yml (in ~/Desktop/fintech-analytics/):

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: dbt_user
      POSTGRES_PASSWORD: dbt_pass
      POSTGRES_DB: analytics
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

dbt_project.yml (in ~/Desktop/fintech-analytics/fintech_analytics/):

name: fintech_analytics
profile: fintech_analytics

seed-paths: ["seeds"]
model-paths: ["models"]
test-paths: ["data-tests"]
macro-paths: ["macros"]

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

profiles.yml (in ~/.dbt/):

fintech_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: dbt_user
      password: dbt_pass
      dbname: analytics
      schema: public
      threads: 4

Your database is running, dbt is installed, and the connection is confirmed. Next up, you will load raw data into PostgreSQL and build your first staging models.

Load Raw Data and Build Staging Models

In Step 1 you proved that dbt can talk to your database. Now you need actual data flowing through the system.

In this step, you will load raw fintech data into PostgreSQL and build staging models that read from it. This is your first "it works" moment where you see data move from CSV files all the way through dbt into queryable views.

In this step, get ready to:
  • Load three CSV seed files into PostgreSQL as raw tables.
  • Declare a source definition so dbt knows where raw data lives.
  • Build staging models that read from the raw tables and cast columns to proper types.
Create seed data and load it into PostgreSQL

dbt seeds are CSV files that dbt loads directly into your database as tables. They simulate the raw data extract that would come from a production OLTP system in a real pipeline.

  • Create the directories for your seed files and staging models by running this command:
mkdir -p seeds models/staging
  • Create seeds/raw_users.csv with the following content:
user_id,username,email,platform,signup_date,country
1,alice_w,alice@email.com,ios,2024-01-15,ID
2,bob_tan,bob@email.com,android,2024-01-20,ID
3,charlie_k,charlie@email.com,web,2024-02-01,SG
4,diana_r,diana@email.com,ios,2024-02-10,ID
5,eve_s,eve@email.com,android,2024-02-15,MY
6,frank_l,frank@email.com,web,2024-03-01,ID
7,grace_m,grace@email.com,ios,2024-03-10,ID
8,henry_p,henry@email.com,android,2024-03-15,SG
9,ivan_q,,ios,2024-04-01,ID
10,julia_n,julia@email.com,web,2024-04-10,ID
2,bob_tan_dup,bob2@email.com,android,2024-01-20,ID
  • Create seeds/raw_transactions.csv with this content:
transaction_id,user_id,amount,currency,transaction_type,created_at
1001,1,50000,IDR,deposit,2024-02-01
1002,2,75000,IDR,deposit,2024-02-05
1003,1,25000,IDR,withdrawal,2024-02-10
1004,3,100000,IDR,deposit,2024-02-15
1005,4,,IDR,deposit,2024-02-20
1006,5,30000,IDR,deposit,2024-03-01
1007,6,60000,IDR,deposit,2024-03-05
1008,2,45000,IDR,withdrawal,2024-03-10
1009,7,80000,IDR,deposit,2024-03-15
1010,99,50000,IDR,deposit,2024-03-20
1011,8,55000,IDR,deposit,2024-04-01
1012,1,40000,IDR,deposit,2024-04-05
  • Create seeds/raw_app_events.csv with this content:
event_id,user_id,event_type,platform,event_timestamp
5001,1,login,ios,2024-02-01 08:30:00
5002,1,view_balance,ios,2024-02-01 08:31:00
5003,2,login,android,2024-02-01 09:00:00
5004,3,login,web,2024-02-02 10:00:00
5005,4,login,ios,2024-02-15 11:00:00
5006,1,login,ios,2024-03-01 08:00:00
5007,2,login,android,2024-03-01 09:30:00
5008,5,login,android,2024-03-02 10:00:00
5009,6,login,web,2024-03-05 11:00:00
5010,7,login,ios,2024-03-10 08:00:00
5011,8,login,android,2024-04-01 09:00:00
5012,1,login,ios,2024-04-05 08:00:00
  • Confirm the seed files exist by running:
ls seeds/

You should see raw_app_events.csv, raw_transactions.csv, and raw_users.csv listed.

What quality issues are hiding in this data?

This data has three deliberate problems that mirror real OLTP extracts. The last row of raw_users.csv is a duplicate user_id=2. Transaction 1005 in raw_transactions.csv has a blank amount. Transaction 1010 references user_id=99 which does not exist in the users table.

You will leave these issues in place for now. A later step adds tests that catch them automatically.

  • Load the seed CSVs into PostgreSQL by running:
dbt seed

What does this command do?

dbt seed reads every CSV in your seeds/ directory and creates a corresponding table in your PostgreSQL database. Each CSV becomes a table with the same name (e.g. raw_users.csv becomes the raw_users table).

You should see output showing each seed file loaded with a row count, ending with a summary line like Completed successfully.

Seeing a connection error?

Make sure your PostgreSQL container is still running. Run docker compose ps to check. If the container is stopped, restart it with docker compose start.

Also confirm your virtual environment is active. You should see (dbt-env) in your terminal prompt. If not, reactivate it with source dbt-env/bin/activate.

help me fix a dbt seed connection error

Declare sources and write staging models

Now that raw tables exist in the database, you need to tell dbt where they are. A source declaration maps your raw tables so staging models can reference them with the {{ source() }} macro.

  • Create models/staging/__sources.yml with this content:
version: 2

sources:
  - name: raw
    schema: public
    tables:
      - name: raw_users
      - name: raw_transactions
      - name: raw_app_events

Why declare sources instead of writing raw table names?

The {{ source() }} macro does two things. It generates the correct schema-qualified table name automatically. It also tracks lineage so dbt knows which raw tables feed which models. In production, you could swap the schema without editing any SQL.

  • Create models/staging/stg_users.sql with this staging model:
with source as (
    select * from {{ source('raw', 'raw_users') }}
)

select
    user_id,
    username,
    email,
    platform,
    cast(signup_date as date) as signup_date,
    country
from source
  • Run dbt run to materialize your first staging model:
dbt run

You should see output showing 1 view model created successfully with an OK status.

  • Create models/staging/stg_transactions.sql:
with source as (
    select * from {{ source('raw', 'raw_transactions') }}
)

select
    transaction_id,
    user_id,
    cast(amount as numeric) as amount_idr,
    currency,
    transaction_type,
    cast(created_at as date) as transaction_date
from source
  • Create models/staging/stg_app_events.sql:
with source as (
    select * from {{ source('raw', 'raw_app_events') }}
)

select
    event_id,
    user_id,
    event_type,
    platform,
    cast(event_timestamp as timestamp) as event_timestamp,
    cast(event_timestamp as date) as event_date
from source
  • Run dbt run again to materialize all three staging models:
dbt run

What do these staging models do?

Each staging model reads from one raw source table using a CTE (Common Table Expression) called source. The select statement then casts columns to proper types (strings to dates, amounts to numeric) and renames columns to use consistent naming conventions like amount_idr and transaction_date.

These are intentionally naive. They do basic type casting but do NOT handle the data quality problems yet. The duplicate user and null amount pass straight through.

You should see 3 view models created, each with OK status. The summary line shows PASS=3.

Seeing a compilation error?

Check that __sources.yml is inside models/staging/ (not in the project root). The source name raw in the YAML must exactly match what you reference in the SQL with {{ source('raw', ...) }}.

Also confirm the table names in __sources.yml match the CSV file names without the .csv extension (e.g. raw_users not raw_users.csv).

help me fix a dbt source compilation error

Preview staging output and spot the quality issues

Your staging models are materializing as views. Before moving on, preview the actual data to confirm everything flows through correctly.

Before you run the next command, what do you expect to see for user_id 2? Will it appear once or twice?

  • Preview the users staging model by running:
dbt show -s stg_users --limit 11

You should see a table printed in the terminal showing all rows from stg_users. Notice that user_id 2 appears twice. The duplicate is still there because the staging model does not filter it out yet.

  • Preview the transactions staging model by running:
dbt show -s stg_transactions --limit 12

Look at the row for transaction_id 1005. The amount_idr column is null. Transaction 1010 references user_id 99 which does not exist in the users table. Both problems flow through unchecked.

Why leave these problems in place?

These quality issues are exactly the kind of problems that corrupt investor metrics. A duplicate user inflates MAU. A null amount corrupts ARPU. An orphaned transaction skews revenue totals.

In a later step you will add automated tests that catch these issues and then fix the staging models to handle them. For now, seeing the problems flow through untouched proves why tests matter.

Data is flowing from CSV seeds through staging views into queryable output. Next up, you will reshape this data into a star schema with fact and dimension tables designed for the investor queries.

Build the Star Schema Mart Models

Your staging models clean raw data, but they don't answer business questions directly. An investor asking "What's our ARPU this month?" can't get that from a cleaned list of transactions.

Mart models reshape data into a star schema specifically designed for analytical queries. You will build dimension tables (who, when) and fact tables (what happened) that make investor questions trivial to answer.

In this step, get ready to:
  • Build dimension models (dim_users and dim_date) that describe the who and when.
  • Build fact models (fact_transactions and fact_daily_activity) that capture what happened.
  • Run the full DAG and confirm the star schema materializes as tables.
Build the dimension models

Dimension tables describe the entities you slice metrics by. You need a user dimension and a date dimension to answer questions like "ARPU by month" or "MAU by platform."

  • Create the models/marts/ directory by running this command:
mkdir models/marts
  • Create a new file called dim_users.sql inside models/marts/ and add the following code:
with users as (
    select * from {{ ref('stg_users') }}
)

select
    user_id,
    username,
    email,
    platform,
    signup_date,
    country
from users

What does this code do?

This model pulls from your stg_users staging view using the ref() function. The ref() function tells dbt about the dependency so it runs models in the correct order.

Right now this is a simple pass-through. The dimension reshaping is minimal because stg_users already has the columns we need. The value is in the separation of concerns: staging cleans, marts shape for analytics.

  • Save dim_users.sql.
  • Now create a file called dim_date.sql inside models/marts/ and add this code:
with date_spine as (
    select
        generate_series(
            '2024-01-01'::date,
            '2024-06-30'::date,
            '1 day'::interval
        )::date as date_day
)

select
    date_day,
    extract(year from date_day)::int as year,
    extract(month from date_day)::int as month,
    extract(day from date_day)::int as day_of_month,
    trim(to_char(date_day, 'Day')) as day_name,
    trim(to_char(date_day, 'Month')) as month_name
from date_spine

What does this code do?

This model uses PostgreSQL's generate_series function to create one row for every day from January 1 to June 30, 2024. This is called a date spine.

Each row gets calendar attributes (year, month, day name) that make time-based queries easy. Instead of writing extract(month from ...) in every query, analysts just join to dim_date and filter on the month column.

  • Save dim_date.sql.
  • Run the dimension models to confirm they materialize by running:
dbt run --select dim_users dim_date

You should see output showing both models created successfully, like:

1 of 2 OK created sql table model public.dim_users .... [SELECT in 0.XXs]
2 of 2 OK created sql table model public.dim_date ..... [SELECT in 0.XXs]

Seeing a compilation error?

  • Check that your file is saved inside models/marts/ and not inside models/staging/.
  • Make sure ref('stg_users') in dim_users.sql matches the filename stg_users.sql exactly (no typos, case-sensitive).
  • Confirm your PostgreSQL container is still running with docker ps.

Help me debug my dbt dimension model error.

Build the fact models

Fact tables record events and transactions. Your fact_transactions model needs to enforce referential integrity by only keeping transactions from users that actually exist.

  • Create a file called fact_transactions.sql inside models/marts/ and add this code:
with transactions as (
    select * from {{ ref('stg_transactions') }}
),

valid_transactions as (
    select
        t.*
    from transactions t
    inner join {{ ref('stg_users') }} u
        on t.user_id = u.user_id
)

select
    transaction_id,
    user_id,
    amount_idr,
    currency,
    transaction_type,
    transaction_date
from valid_transactions

What does this code do?

The inner join with stg_users is the key line. It keeps only transactions where the user_id exists in the users table. This means the orphaned transaction (user_id=99, who doesn't exist in raw_users) gets excluded automatically.

This is referential integrity enforcement at the transformation layer. The raw data has no foreign key constraints, but your mart model enforces the relationship through the join.

  • Save fact_transactions.sql.
  • Create a file called fact_daily_activity.sql inside models/marts/ and add this code:
with events as (
    select * from {{ ref('stg_app_events') }}
),

daily_activity as (
    select
        user_id,
        event_date as activity_date,
        count(*) as event_count,
        count(distinct event_type) as distinct_actions
    from events
    group by user_id, event_date
)

select
    user_id,
    activity_date,
    event_count,
    distinct_actions
from daily_activity

What does this code do?

This model aggregates raw app events into one row per user per day. Instead of individual login and view_balance events, you get a daily summary: how many total events and how many distinct action types.

This pre-aggregation makes MAU (Monthly Active Users) calculations fast. To count MAU, you just count distinct user_id values in a given month from this table.

  • Save fact_daily_activity.sql.
Run the full DAG and verify the star schema

Your dbt_project.yml already configures staging models as views and mart models as tables. This means your new mart models will be materialized as persistent tables in PostgreSQL.

Why tables for marts and views for staging?

Views re-run the SQL every time they're queried. That's fine for staging (lightweight transforms). Tables store results on disk and query instantly. Marts serve analysts who run the same queries repeatedly, so tables give them fast reads.

This is the OLAP pattern: precompute the expensive joins and aggregations so that analytical queries are fast.

Before you run the full DAG, what do you think will happen to the transaction with user_id=99 that doesn't exist in the users table?

  • Run all models in the project by running:
dbt run

You should see all 7 models (3 staging views + 4 mart tables) execute in dependency order, with each showing OK status.

  • Preview the fact_transactions output to confirm referential integrity by running:
dbt show -s fact_transactions

You should see a table with 5 rows of transaction data. Notice that transaction_id 1010 (user_id=99) is missing from the output. The inner join excluded it because user_id 99 doesn't exist in stg_users.

Are the data quality issues fixed?

Not yet. The orphaned transaction (user_id=99) is gone thanks to the inner join. But the duplicate user (user_id=2 appears twice) and the null transaction amount (transaction_id=1005) still flow through from staging.

You'll fix those in the next step by adding tests that catch them and then updating the staging models.

Models failing with a database error?

  • If you see a "relation does not exist" error, make sure you ran dbt seed in the previous step. The staging models depend on the seed tables existing in PostgreSQL.
  • If dim_date fails, check that generate_series is spelled correctly and the date strings use single quotes, not backticks.
  • Confirm your Docker container is running with docker ps. If it stopped, restart it with docker compose start.

Help me debug my dbt run error for mart models.

✔️ Awesome, I've got everything!

Great. Make sure you've saved all four files in models/marts/.

ⓧ I'd like to double check the full code

Here are all four mart model files. Scroll up and confirm each one matches what you have.

models/marts/dim_users.sql

with users as (
    select * from {{ ref('stg_users') }}
)

select
    user_id,
    username,
    email,
    platform,
    signup_date,
    country
from users

models/marts/dim_date.sql

with date_spine as (
    select
        generate_series(
            '2024-01-01'::date,
            '2024-06-30'::date,
            '1 day'::interval
        )::date as date_day
)

select
    date_day,
    extract(year from date_day)::int as year,
    extract(month from date_day)::int as month,
    extract(day from date_day)::int as day_of_month,
    trim(to_char(date_day, 'Day')) as day_name,
    trim(to_char(date_day, 'Month')) as month_name
from date_spine

models/marts/fact_transactions.sql

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

valid_transactions as (
    select
        t.*
    from transactions t
    inner join {{ ref('stg_users') }} u
        on t.user_id = u.user_id
)

select
    transaction_id,
    user_id,
    amount_idr,
    currency,
    transaction_type,
    transaction_date
from valid_transactions

models/marts/fact_daily_activity.sql

with events as (
    select * from {{ ref('stg_app_events') }}
),

daily_activity as (
    select
        user_id,
        event_date as activity_date,
        count(*) as event_count,
        count(distinct event_type) as distinct_actions
    from events
    group by user_id, event_date
)

select
    user_id,
    activity_date,
    event_count,
    distinct_actions
from daily_activity

Your star schema is live. You have dimensions describing who and when, plus facts recording transactions and daily activity. Next up, you'll add automated tests to catch the data quality issues that are still hiding in the pipeline.

Add Tests and Fix Your Models

Your mart models look correct on the surface. They query cleanly and produce rows. But the data quality problems from the raw layer flowed straight through without any guardrails.

The duplicate user inflates MAU. The null transaction amount corrupts ARPU. The orphaned transaction skews revenue. Without automated tests, you ship wrong numbers to investors. In this step, you will add tests that catch these issues, watch them fail, then fix the staging models so the entire analytical layer becomes trustworthy.

In this step, get ready to:
  • Define schema tests for all staging and mart models.
  • Watch the tests fail and identify the root cause in the staging layer.
  • Fix stg_users and stg_transactions so all tests pass.
Add schema tests to your staging and mart models

Schema tests in dbt are assertions you declare in a YAML file. Each test generates a SQL query that returns failing rows. If zero rows come back, the test passes.

  • Create a new file called _staging__models.yml inside your models/staging/ directory.
  • Paste the following content to define tests for stg_users:
version: 2

models:
  - name: stg_users
    description: Cleaned and deduplicated user records from the raw OLTP extract.
    columns:
      - name: user_id
        description: Unique identifier for each user.
        tests:
          - unique
          - not_null
      - name: email
        description: User email address.
        tests:
          - not_null
      - name: platform
        description: Platform the user signed up on.
        tests:
          - accepted_values:
              values: ['ios', 'android', 'web']

What do these tests assert?

  • unique asserts no duplicate values exist in that column.
  • not_null asserts every row has a value (no SQL NULLs).
  • accepted_values asserts only the listed values appear in that column.
  • Add the following directly below the stg_users block in the same file to define tests for stg_transactions and stg_app_events:
  - name: stg_transactions
    description: Financial transactions with null amounts filtered out.
    columns:
      - name: transaction_id
        description: Unique identifier for each transaction.
        tests:
          - unique
          - not_null
      - name: amount_idr
        description: Transaction amount in Indonesian Rupiah.
        tests:
          - not_null
      - name: user_id
        description: Reference to the user who made the transaction.
        tests:
          - not_null

  - name: stg_app_events
    description: User activity events from the mobile and web apps.
    columns:
      - name: event_id
        description: Unique identifier for each event.
        tests:
          - unique
          - not_null

Why not_null on amount_idr?

A null transaction amount produces nonsense when you calculate averages or sums. If even one null slips through, your ARPU calculation divides by the wrong count and reports an incorrect number to investors.

  • Save _staging__models.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file.

ⓧ I'd like to double check the full code

Your file should contain both chunks in order: the stg_users block (starting with version: 2) followed by the stg_transactions and stg_app_events blocks appended directly below. Scroll up and confirm both chunks are present in order.

Now define tests for the mart layer, including a referential integrity check that verifies every transaction links to a real user.

  • Create a new file called _marts__models.yml inside your models/marts/ directory.
  • Paste the following content to define tests for the dimension tables:
version: 2

models:
  - name: dim_users
    description: User dimension table containing one row per unique user with their attributes.
    columns:
      - name: user_id
        description: Primary key.
        tests:
          - unique
          - not_null

  - name: dim_date
    description: Date dimension table with calendar attributes for every day in the analysis period.
    columns:
      - name: date_day
        description: Primary key, one row per calendar day.
        tests:
          - unique
          - not_null
  • Add the following directly below the dim_date block in the same file to define tests for the fact tables:
  - name: fact_transactions
    description: Financial transactions with only valid user references. Orphaned transactions are excluded.
    columns:
      - name: transaction_id
        description: Primary key.
        tests:
          - unique
          - not_null
      - name: user_id
        description: Foreign key to dim_users.
        tests:
          - not_null
          - relationships:
              to: ref('dim_users')
              field: user_id

  - name: fact_daily_activity
    description: Aggregated daily user activity from app events.
    columns:
      - name: user_id
        description: Foreign key to dim_users.
        tests:
          - not_null

What does the relationships test do?

The relationships test checks that every value in fact_transactions.user_id exists in dim_users.user_id. If an orphaned transaction references a user that does not exist, this test catches it.

  • Save _marts__models.yml.

✔️ Awesome, I've got everything!

Great. Double check you have saved the file.

ⓧ I'd like to double check the full code

Your file should contain both chunks in order: the dim_users and dim_date block (starting with version: 2) followed by the fact_transactions and fact_daily_activity blocks appended directly below. Scroll up and confirm both chunks are present.

Before you run the tests, think about what you expect to happen. Your raw data has a duplicate user_id, a null transaction amount, and an orphaned user reference. Do you think all tests will pass on the first try?

  • Run your tests by executing this command:
dbt test

You will see several tests marked as FAIL in the output. The summary line shows a non-zero error count. This is exactly what should happen.

Which tests failed and why?

  • unique on stg_users.user_id fails because user_id 2 appears twice in the raw data.
  • not_null on stg_transactions.amount_idr fails because transaction 1005 has a null amount.
  • unique on dim_users.user_id fails because dim_users inherits the duplicate from stg_users.

These failures prove the tests are doing their job. The data quality problems existed all along. Now you know exactly where to fix them.

Seeing compilation errors instead of test failures?

  • Check that your YAML indentation uses spaces, not tabs. YAML is sensitive to indentation.
  • Confirm the name values in your YAML match the SQL filenames exactly (stg_users, not stg-users).
  • Make sure each file is saved in the correct directory (models/staging/ or models/marts/).

Why are my dbt tests showing compilation errors instead of failures?

Fix the staging models to pass all tests

The right place to fix data quality issues is the staging layer. Fixing here means every downstream model automatically receives clean data without any changes to the mart SQL.

  • Open models/staging/stg_users.sql in VS Code.
  • Replace the entire file contents with the following. This adds a deduplication step using a window function:
with source as (
    select * from {{ source('raw', 'raw_users') }}
),

deduplicated as (
    select
        *,
        row_number() over (partition by user_id order by signup_date) as row_num
    from source
)

select
    user_id,
    username,
    email,
    platform,
    cast(signup_date as date) as signup_date,
    country
from deduplicated
where row_num = 1

What does this deduplication logic do?

ROW_NUMBER() assigns a sequential number to each row within a partition. By partitioning on user_id and ordering by signup_date, the first occurrence gets row_num = 1.

The WHERE row_num = 1 filter keeps only the first occurrence of each user_id, dropping any duplicates. This is the standard deduplication pattern in SQL.

  • Save stg_users.sql.

Now fix the transactions model. The null amount needs to be filtered out at the source.

  • Open models/staging/stg_transactions.sql.
  • Find the last two lines of the file:
    cast(created_at as date) as transaction_date
from source
  • Add a WHERE clause on a new line below from source so the end of your file looks like this:
    cast(created_at as date) as transaction_date
from source
where amount is not null

Why filter on the raw column name?

The WHERE clause references amount (the original column name from the source) rather than amount_idr (the alias). In SQL, WHERE is evaluated before SELECT aliases, so you must use the source column name.

  • Save stg_transactions.sql.

✔️ Awesome, I've got everything!

Great. Both staging models are now fixed.

ⓧ I'd like to double check the full code

Here is the complete stg_users.sql:

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

deduplicated as (
    select
        *,
        row_number() over (partition by user_id order by signup_date) as row_num
    from source
)

select
    user_id,
    username,
    email,
    platform,
    cast(signup_date as date) as signup_date,
    country
from deduplicated
where row_num = 1

Here is the complete stg_transactions.sql:

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

select
    transaction_id,
    user_id,
    cast(amount as numeric) as amount_idr,
    currency,
    transaction_type,
    cast(created_at as date) as transaction_date
from source
where amount is not null

Not sure where to add the WHERE clause?

The where amount is not null line goes on a new line directly below from source. Make sure there is no blank line between them.

Help me fix my stg_transactions.sql WHERE clause placement.

Rebuild everything and confirm all tests pass

The dbt build command runs seeds, models, and tests in dependency order. One command rebuilds the entire pipeline and validates it end to end.

Before you run this, do you think all tests will pass now? The deduplication removes the duplicate user. The WHERE clause removes null amounts. And fact_transactions already inner-joins with stg_users, which excludes orphaned references.

  • Rebuild and test everything by running:
dbt build

Every test now shows PASS. The summary line shows zero errors. Your analytical layer is trustworthy.

What did dbt build do?

  • Loaded the seed CSVs into PostgreSQL.
  • Rebuilt all staging views with the new deduplication and null-filtering logic.
  • Rebuilt all mart tables from the now-clean staging views.
  • Ran every schema test. All passed because the fixes in staging propagated to every downstream model.

Still seeing test failures after fixing?

  • Confirm stg_users.sql has where row_num = 1 as the very last line of the file.
  • Confirm stg_transactions.sql has where amount is not null as the last line.
  • Make sure you ran dbt build (not just dbt test) so the models are rebuilt with the new SQL before tests execute.

My dbt tests are still failing after I fixed the staging models. Help me debug.

Your data now has guardrails. If a duplicate or null sneaks in through a future data load, these tests catch it immediately before bad numbers reach investor reports. Next up, you will generate documentation and a lineage graph so anyone can understand how the data flows.

Generate Documentation and Lineage Graph

Your models are tested and trustworthy. But without documentation, no one else (including future-you) can understand what each model does or how data flows through the system.

dbt auto-generates a documentation site with a visual lineage graph showing every dependency in your project. In this step, you will confirm your model descriptions are in place, generate the documentation catalog, and launch the site locally. This is what you show in an interview or link from your GitHub README.

In this step, get ready to:
  • Confirm your model and column descriptions are defined in the YAML schema files.
  • Generate the documentation catalog with dbt docs generate.
  • Launch the documentation site and explore the lineage graph.
Confirm your model descriptions

Documentation in dbt lives right alongside your schema definitions. The descriptions you already added to your YAML files when configuring tests are exactly what dbt uses to populate the documentation site.

  • Open models/staging/_staging__models.yml in VS Code.
  • Confirm that each model has a description field and each column has a description field.

Your staging YAML should already look like this:

version: 2

models:
  - name: stg_users
    description: Cleaned and deduplicated user records from the raw OLTP extract.
    columns:
      - name: user_id
        description: Unique identifier for each user.
        tests:
          - unique
          - not_null
      - name: email
        description: User email address.
        tests:
          - not_null
      - name: platform
        description: Platform the user signed up on.
        tests:
          - accepted_values:
              values: ['ios', 'android', 'web']

Why do descriptions live in the YAML files?

Keeping descriptions next to your tests and schema definitions means documentation stays version-controlled and up-to-date. When you change a model, the description lives in the same file you are editing. dbt reads these descriptions at generate time and injects them into the documentation site.

  • Open models/marts/_marts__models.yml and confirm each model also has its descriptions.

Your marts YAML should already have descriptions like these:

models:
  - name: dim_users
    description: User dimension table containing one row per unique user with their attributes.
    columns:
      - name: user_id
        description: Primary key.
        tests:
          - unique
          - not_null

  - name: fact_transactions
    description: Financial transactions with only valid user references. Orphaned transactions are excluded.
    columns:
      - name: transaction_id
        description: Primary key.
        tests:
          - unique
          - not_null
      - name: user_id
        description: Foreign key to dim_users.
        tests:
          - not_null
          - relationships:
              to: ref('dim_users')
              field: user_id

What do these descriptions power?

Every description field becomes searchable text in the documentation site. When another engineer clicks a model in the lineage graph, these descriptions explain its purpose and what each column represents.

Generate the documentation catalog

With descriptions confirmed, you can now build the documentation catalog. This command reads your YAML files and queries the database to produce a full metadata snapshot.

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

What does this command do?

dbt docs generate creates two files in your target/ directory: manifest.json (your project's structure, models, and tests) and catalog.json (column types and metadata queried directly from your database).

Together, these files power the documentation website. The catalog connects your YAML descriptions to the actual columns that exist in PostgreSQL.

  • Confirm the catalog was generated by running:
ls target/catalog.json

You should see target/catalog.json listed in the output.

Don't see catalog.json?

Make sure you ran dbt docs generate from inside your fintech_analytics project directory (the folder containing dbt_project.yml). Also confirm your virtual environment is active and your PostgreSQL container is still running.

Why isn't dbt docs generate producing a catalog.json file?

Launch and explore the documentation site

The catalog is built. Now you can serve the documentation as a local website and explore how your data flows from raw seeds through staging views into mart tables.

  • Launch the documentation site by running:
dbt docs serve

What happens when you run this?

dbt docs serve starts a local web server and opens the documentation site in your default browser automatically. The site is a fully interactive catalog of your project: models, sources, tests, and their relationships.

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

Before you look at the site, what do you expect to see in the lineage graph? Think about how many models you built and which ones depend on which.

Your browser opens to a documentation site showing all your models. Here is what to explore:

  • Click the lineage graph icon (a small network diagram icon) in the bottom-right corner of the page to open the full DAG view.
  • Follow the path from your raw sources (raw_users, raw_transactions, raw_app_events) through the staging views (stg_users, stg_transactions, stg_app_events) into the mart tables (dim_users, dim_date, fact_transactions, fact_daily_activity).
  • Click on fact_transactions in the graph. You should see its description ("Financial transactions with only valid user references. Orphaned transactions are excluded."), its columns, and the tests attached to each column.
  • Click on stg_users and confirm you can see its description and the unique/not_null tests listed.

What does the lineage graph prove?

The graph shows that fact_transactions depends on both stg_transactions and stg_users (because of the referential integrity join). It also shows that dim_users and fact_transactions both depend on stg_users. These are your data contracts made visible.

Browser didn't open?

If your browser did not open automatically, check your terminal output for the URL and port number (something like http://localhost:8080). Copy that URL and paste it into your browser manually.

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

Your analytics layer is now fully documented. One command rebuilds the entire pipeline, tests catch regressions, and anyone can open the lineage graph to understand how raw data becomes investor-ready metrics. Next up: a Secret Mission that takes this even further with custom test macros and a real ARPU metrics model.

Secret mission

Write a Custom Test Macro and Build ARPU Metrics

Your tests catch nulls, duplicates, and broken references. But what about business logic violations like negative transaction amounts? And your investor deck still needs the actual KPIs: ARPU, MAU, total revenue by month. Time to write your own reusable test and build the metrics model that ties everything together.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • A Docker container running PostgreSQL 16 on localhost:5432 (uses minimal disk space, no ongoing cost).
  • A Python virtual environment (dbt-env/) with dbt-core and dbt-postgres installed.
  • dbt project files (fintech_analytics/ directory with models, seeds, macros, and tests).

Keep everything running

No action needed. Choose this if you plan to keep iterating on your dbt project or want to experiment further with models and tests.

  • Your Docker container stays running and uses approximately 200MB of RAM.
  • Your virtual environment stays active in any terminal session where you sourced it.
  • Your dbt project files remain on disk, ready for you to push to GitHub as a portfolio piece.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your data and files stay on disk so you can resume later.

  • If dbt docs serve is still running, press Ctrl+C in that terminal to stop the documentation server.
  • Stop the PostgreSQL container by running:
docker compose stop
  • Your database data persists in the Docker volume. When you are ready to resume, restart with:
docker compose start
  • Reactivate your virtual environment with source dbt-env/bin/activate and you are back where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. Your dbt project files can remain for your GitHub portfolio if you choose.

  • If dbt docs serve is still running, press Ctrl+C to stop it.
  • Remove the Docker container, network, and database volume by running:
docker compose down -v
  • Deactivate your virtual environment by running:
deactivate
  • Delete the virtual environment folder by running:
rm -rf dbt-env/
  • If you also want to remove the project files entirely, navigate to your Desktop and run:
rm -rf fintech_analytics/
  • If you want to keep the project for your portfolio, skip that last command and push the fintech_analytics/ folder to GitHub instead.

Nice Work!

Nice Work!

Nice work! You just built a production-grade analytics layer that transforms messy raw fintech data into trustworthy investor metrics using dbt.

You've learned how to:

  • Build a complete dbt project that transforms raw fintech data through staging views into a tested star schema with dimension and fact tables (dim_users, dim_date, fact_transactions, fact_daily_activity).
  • Experience real-world data quality problems (duplicates, nulls, orphaned records) and fix them systematically in the staging layer using deduplication and null filtering.
  • Add automated schema tests (unique, not_null, accepted_values, relationships) and generate full documentation with a visual lineage graph showing data flow from raw seeds to mart tables.
  • Secret Mission: Write a custom reusable test macro and build a monthly_metrics model calculating ARPU and MAU.

Ready to quiz yourself?