Subscription Analytics Models in dbt

Build a layered dbt project that models MRR and subscription lifecycle on Supabase.

Introduction

30 Second Summary

Every subscription business asks the same question each month: "Did we grow, shrink, or churn?" Without a reliable system to track revenue changes, teams end up with conflicting spreadsheets and guesswork.

In this project, you will build a dbt analytics pipeline on Supabase PostgreSQL that transforms raw subscription data into tested, business-ready MRR metrics with automatic movement classification.

What You'll Build

A layered dbt project (staging → intermediate → marts) that calculates monthly recurring revenue per customer and classifies every month-over-month change as new, churn, reactivation, upgrade, or downgrade.

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

  • A dbt project connected to a Supabase PostgreSQL database with seeded subscription data loaded and validated.
  • A three-layer model (staging → intermediate → marts) that gap-fills customer timelines and computes MRR at the customer-month grain.
  • Tested mart tables (fct_mrr_movements and fct_mrr_summary) with browsable documentation, ready for dashboards that answer "what happened to revenue this month and why?"
  • Secret Mission: Add a retention cohort model that calculates monthly logo retention, NRR, and GRR by signup cohort.

Are there any prerequisites?

You should have basic SQL knowledge (SELECT, JOIN, GROUP BY) and be comfortable running commands in a terminal. No prior experience with dbt or analytics engineering is required.

Before We Start

Before We Start...

Set Up Your Environment

Before you can model subscription data, you need a working analytics stack. That means a Python environment with dbt installed and a database to run transformations against.

In this step, you will install dbt, create a free Supabase PostgreSQL database, and wire everything together so dbt can connect to your database and run SQL.

In this step, get ready to:
  • Install Python, create a virtual environment, and install dbt.
  • Create a free Supabase project and retrieve your connection string.
  • Initialize a dbt project and verify your database connection.
Install Python and dbt

dbt is a Python-based tool that transforms data inside your database using SQL. You need Python 3.10+ installed to run it.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Check your Python version by running this command:
python3 --version

✔️ I see version 3.10 or higher

Python is ready. Continue to the next instruction below.

ⓧ I see a version lower than 3.10

Your system has an older Python that won't work with dbt. You need to install Python 3.10+ alongside your existing version.

  • Download the latest Python installer from python.org/downloads.
  • Run the installer. On macOS, this installs a versioned binary (e.g. python3.12) that does not overwrite your system Python. On Windows, check Add python.exe to PATH during installation.
  • After installation, re-run python3 --version to confirm you see 3.10 or higher. On macOS, if the old version still appears, use the full path (e.g. python3.12 --version) and substitute python3.12 for python3 in all commands that follow.

ⓧ Command not found

Python is not installed on your machine.

  • Download the latest Python installer from python.org/downloads.
  • Run the installer. On Windows, check Add python.exe to PATH before clicking Install Now.
  • Close and reopen your terminal, then run python3 --version to confirm installation.

A virtual environment keeps dbt's packages isolated from other Python projects on your machine.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create a virtual environment and activate it by running these commands:

macOS

python3 -m venv dbt-env
source dbt-env/bin/activate

Windows

python -m venv dbt-env
dbt-env\Scripts\activate

You should see (dbt-env) at the start of your terminal prompt. This confirms the virtual environment is active.

  • Install dbt-core and the Postgres adapter by running this command:
python -m pip install dbt-core dbt-postgres

What does this command do?

This installs two packages. dbt-core is the transformation engine that compiles and runs your SQL models. dbt-postgres is the adapter that lets dbt talk to PostgreSQL databases like Supabase.

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

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

Command not found after installing?

Make sure your virtual environment is still active. You should see (dbt-env) in your prompt. If not, re-run the activate command from above.

help me troubleshoot my dbt installation

Create a Supabase project

Supabase provides a free managed PostgreSQL database. This is where dbt will store and transform your subscription data.

  • Open supabase.com/dashboard in your browser.
  • Sign up for a free account (or sign in if you already have one).
  • Click New Project.
  • Enter a project name (e.g. subscription-analytics).
  • Set a strong database password. Copy this password somewhere safe. You will need it for your dbt connection.
  • Select a region close to you.
  • Click Create new project.
  • Wait for the project to finish provisioning (this takes about 1 minute).

Why Supabase instead of local PostgreSQL?

Supabase gives you a production-like managed database with zero installation. You get a built-in SQL editor for quick queries and it runs on their free tier with no credit card required.

Now retrieve the connection string that dbt will use.

  • Click the Connect button at the top of the page.
  • In the connection panel, find the Direct connection section.
  • Copy the connection string. It follows the format:
postgresql://postgres:[YOUR-PASSWORD]@db.[project-id].supabase.co:5432/postgres

What do these parts mean?

  • postgres (before the colon) is your database username.
  • [YOUR-PASSWORD] is the password you set during project creation.
  • db.[project-id].supabase.co is your database host.
  • 5432 is the PostgreSQL port.
  • postgres (at the end) is the database name.

IPv4-only networks

Direct connections use IPv6 by default. If your network is IPv4-only and you don't have the IPv4 add-on, use the session pooler connection (found in the same panel) instead. It connects via aws-[region].pooler.supabase.com on port 5432.

Initialize the dbt project

The dbt init command scaffolds a new project with the standard directory structure and creates your connection profile.

  • Make sure you are still in your Desktop directory with (dbt-env) active.
  • Initialize your dbt project by running:
dbt init subscription_analytics

What will dbt ask you?

dbt will prompt you to select a database adapter. Choose postgres from the list (type the number next to it and press Enter). It will then ask for your connection details (host, port, user, password, database, schema, threads).

For now, you can enter placeholder values for the prompts. You will replace them with the correct Supabase values in the next instruction.

  • After the command completes, move into the project folder by running:
cd subscription_analytics

dbt created a profiles.yml file at ~/.dbt/profiles.yml. This is where your database connection lives. You need to update it with your Supabase credentials.

  • Open ~/.dbt/profiles.yml in your code editor and replace its contents with the following:
subscription_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: db.[[SUPABASE_PROJECT_ID="your-project-id"]].supabase.co
      user: postgres
      password: your-database-password-here
      port: 5432
      dbname: postgres
      schema: public
      threads: 4

Where do I find these values?

  • host: The part after the @ in your connection string, before the colon. It looks like db.abcdefgh.supabase.co.
  • password: The password you set when creating your Supabase project.
  • Everything else stays as shown above.
  • Save profiles.yml.

Now verify the connection works.

  • Test your connection by running:
dbt debug

You should see Connection test: OK connection ok at the bottom of the output.

Connection test failed?

  • Double-check that the host value in profiles.yml matches your Supabase project. It should look like db.abcdefgh.supabase.co with no postgresql:// prefix.
  • Confirm the password in profiles.yml matches what you set during Supabase project creation.
  • If you see a timeout, your network may be IPv4-only. Switch your host to the session pooler address from the Supabase Connect panel.

help me fix my dbt debug connection failure to Supabase

Now confirm your project file looks correct.

  • Open dbt_project.yml in your code editor (it is in the subscription_analytics folder you just created).
  • Verify the contents match the following structure. If dbt init generated extra sample model configuration at the bottom, delete it so the file looks like this:
name: 'subscription_analytics'
version: '1.0.0'

profile: 'subscription_analytics'

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

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

What does this file do?

dbt_project.yml is the root configuration for your dbt project. The profile field tells dbt which entry in profiles.yml to use for database credentials. The path settings tell dbt where to find your models, seeds, and tests.

  • Save dbt_project.yml.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Here is the full dbt_project.yml file:

name: 'subscription_analytics'
version: '1.0.0'

profile: 'subscription_analytics'

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

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

And here is the full ~/.dbt/profiles.yml file (with your own Supabase host and password):

subscription_analytics:
  target: dev
  outputs:
    dev:
      type: postgres
      host: db.[project-id].supabase.co
      user: postgres
      password: [YOUR-PASSWORD]
      port: 5432
      dbname: postgres
      schema: public
      threads: 4

Your analytics stack is ready. dbt is installed, your Supabase database is live, and the connection is verified. Next up, you will seed realistic subscription data into your database.

Seed Raw Subscription Data

Your dbt project is initialized and connected to Supabase. Now it needs data to work with.

In dbt, seeds are CSV files that live inside your project and get loaded directly into your database. They are perfect for small, version-controlled reference datasets. In this step, you will create a realistic subscription dataset, configure dbt to handle its column types correctly, and load it into Supabase.

In this step, get ready to:
  • Create a seed CSV with 14 subscription records covering real SaaS lifecycle scenarios.
  • Configure column types and load the seed into Supabase.
  • Add the dbt_utils package as a project dependency.
Create the seed CSV

Your seed data needs to represent realistic subscription lifecycle events. This means including customers who sign up, upgrade, downgrade, churn, and reactivate over a 12-month period.

  • Create a new file called raw_subscriptions.csv inside your seeds/ folder by running this command:
touch seeds/raw_subscriptions.csv
  • Confirm the file exists by running:
ls seeds/

You should see raw_subscriptions.csv listed in the output.

Don't see the file?

Make sure you are running the command from the subscription_analytics project directory. If the seeds/ folder does not exist, create it first with mkdir seeds.

Help me find my raw_subscriptions.csv file

  • Open seeds/raw_subscriptions.csv in your editor and paste the following data:
subscription_id,customer_id,plan_name,mrr_amount,start_date,end_date
sub_001,cust_001,starter,29.00,2024-01-15,
sub_002,cust_002,professional,79.00,2024-01-20,2024-06-30
sub_003,cust_003,starter,29.00,2024-02-01,2024-04-30
sub_004,cust_003,professional,79.00,2024-07-01,
sub_005,cust_004,enterprise,199.00,2024-03-01,2024-09-30
sub_006,cust_005,professional,79.00,2024-01-10,2024-03-31
sub_007,cust_005,starter,29.00,2024-08-01,
sub_008,cust_006,starter,29.00,2024-04-01,2024-07-31
sub_009,cust_007,enterprise,199.00,2024-02-15,
sub_010,cust_007,enterprise,249.00,2024-06-01,
sub_011,cust_008,professional,79.00,2024-05-01,2024-05-31
sub_012,cust_009,starter,29.00,2024-03-01,
sub_013,cust_009,professional,79.00,2024-06-01,
sub_014,cust_010,enterprise,199.00,2024-01-01,2024-12-31

What does this data represent?

Each row is a subscription with a start date and an optional end date. An empty end_date means the subscription is still active.

  • cust_001 is a long-running starter customer (no end date, never churned).
  • cust_003 started on starter, churned, then reactivated on professional. This is a reactivation plus upgrade.
  • cust_005 was on professional, churned, then came back on starter. This is a reactivation plus downgrade.
  • cust_007 upgraded from $199 to $249 enterprise mid-year.
  • cust_008 churned after just one month.
  • Save seeds/raw_subscriptions.csv.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

subscription_id,customer_id,plan_name,mrr_amount,start_date,end_date
sub_001,cust_001,starter,29.00,2024-01-15,
sub_002,cust_002,professional,79.00,2024-01-20,2024-06-30
sub_003,cust_003,starter,29.00,2024-02-01,2024-04-30
sub_004,cust_003,professional,79.00,2024-07-01,
sub_005,cust_004,enterprise,199.00,2024-03-01,2024-09-30
sub_006,cust_005,professional,79.00,2024-01-10,2024-03-31
sub_007,cust_005,starter,29.00,2024-08-01,
sub_008,cust_006,starter,29.00,2024-04-01,2024-07-31
sub_009,cust_007,enterprise,199.00,2024-02-15,
sub_010,cust_007,enterprise,249.00,2024-06-01,
sub_011,cust_008,professional,79.00,2024-05-01,2024-05-31
sub_012,cust_009,starter,29.00,2024-03-01,
sub_013,cust_009,professional,79.00,2024-06-01,
sub_014,cust_010,enterprise,199.00,2024-01-01,2024-12-31
Configure seeds and load them into Supabase

Without explicit column type configuration, dbt may infer dates as text and decimals as integers. You need to tell dbt exactly how to type each column.

  • Open dbt_project.yml and add the following seeds configuration block at the very bottom of the file:
seeds:
  subscription_analytics:
    raw_subscriptions:
      +column_types:
        subscription_id: varchar
        customer_id: varchar
        plan_name: varchar
        mrr_amount: numeric(10,2)
        start_date: date
        end_date: date

What does this configuration do?

The +column_types property tells dbt exactly what PostgreSQL types to use when creating the seed table. Without this, mrr_amount could be stored as an integer (losing decimal precision) and dates could be stored as text (breaking date math later).

The nesting follows dbt's config hierarchy: project name, then seed file name, then the property.

  • Save dbt_project.yml.

You won't see a change yet. The next command uses this configuration.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before running the next command.

ⓧ I'd like to double check the full code

name: 'subscription_analytics'
version: '1.0.0'

profile: 'subscription_analytics'

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

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

seeds:
  subscription_analytics:
    raw_subscriptions:
      +column_types:
        subscription_id: varchar
        customer_id: varchar
        plan_name: varchar
        mrr_amount: numeric(10,2)
        start_date: date
        end_date: date
  • Load the seed data into your Supabase database by running:
dbt seed

What should I see?

The output shows dbt creating a table called raw_subscriptions in your public schema and inserting 14 rows. You should see a success message confirming the seed loaded without errors.

The 14 rows of your CSV are now a real table in Supabase that your dbt models can reference.

Seeing a connection error?

If you see a connection timeout, your network may be IPv4-only. Supabase direct connections use IPv6 by default. Switch to the session pooler connection string in your profiles.yml. You can find this string by clicking Connect at the top of your Supabase dashboard and selecting the session mode option.

If you see a password authentication error, double-check that the password in profiles.yml matches the one you set during Supabase project creation.

Help me troubleshoot my dbt seed connection error

Add dbt_utils as a dependency

Later in this project, you will need a utility called date_spine to generate a series of monthly dates. This macro lives in the dbt_utils package, the standard community utility library for dbt.

  • Create a new file called packages.yml in the root of your project (same level as dbt_project.yml) and paste the following:
packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.3.0,<2.0.0"

What does this file do?

The packages.yml file tells dbt which external packages your project depends on. The version range >=1.3.0,<2.0.0 means any compatible release in the 1.x line.

When you run dbt deps, dbt downloads the package source code into a dbt_packages/ directory inside your project.

  • Save packages.yml.
  • Install the package by running:
dbt deps

You should see output confirming the package was installed successfully into the dbt_packages/ directory.

Seeing a packages.yml not found error?

Make sure packages.yml is in the same directory as dbt_project.yml (the project root). If you accidentally placed it inside seeds/ or another subfolder, dbt will not find it.

Help me fix my dbt deps error

✔️ Awesome, I've got everything!

Great. Your project now has the dbt_utils macros available for use in models.

ⓧ I'd like to double check the full code

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

Your raw subscription data is loaded and your utility package is installed. Next up, you will build the staging layer to clean and prepare this data for analysis.

Build the Staging Layer

Your raw subscription data is sitting in Supabase, but it's not ready for analysis yet. Raw data often contains invalid records that could quietly skew every downstream metric you build on top of it.

The staging layer is the first transformation layer in a dbt project. It acts as a gatekeeper that cleans and filters data before any business logic touches it.

In this step, get ready to:
  • Declare the raw subscription seed as a dbt source.
  • Create a staging model that filters out invalid records.
  • Add schema tests to validate data quality on the staging model.
Define the source

In dbt, a source declaration tells your project where raw data lives. This creates a traceable link between your raw tables and any model that reads from them.

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

sources:
  - name: subscription_analytics
    schema: "{{ target.schema }}"
    tables:
      - name: raw_subscriptions
        description: Seeded raw subscription data

What does this file do?

  • name: subscription_analytics gives this source a name that models will reference.
  • schema: "{{ target.schema }}" tells dbt to look in whatever schema your profile targets. This keeps the config portable across environments.
  • tables lists the actual tables within that source. Here it points to raw_subscriptions (the table dbt seed created in the previous step).
  • Save _sources.yml.

You won't see a change yet. The next chunk uses this source to build your first model.

Create the staging model

The staging model selects from the source and filters out any rows that would corrupt downstream calculations. Records with negative MRR or an end date before the start date are invalid and should be excluded.

  • Create a new file called stg_subscriptions.sql inside models/staging/ and paste the following SQL:
with source as (
    select * from {{ source('subscription_analytics', 'raw_subscriptions') }}
),

cleaned as (
    select
        subscription_id,
        customer_id,
        plan_name,
        mrr_amount,
        start_date,
        end_date
    from source
    where mrr_amount >= 0
      and (end_date is null or end_date >= start_date)
)

select * from cleaned

What does this code do?

  • {{ source('subscription_analytics', 'raw_subscriptions') }} references the source you just declared. dbt compiles this to the actual table name in your database.
  • The cleaned CTE selects only the columns needed and filters out rows where mrr_amount is negative.
  • The end_date is null or end_date >= start_date condition keeps active subscriptions (null end date) and removes rows where someone apparently ended before they started.
  • Save stg_subscriptions.sql.
  • Run the staging model by running this command:
dbt run --select staging

You should see a success message indicating 1 model was created as a view in your Supabase database.

Seeing a compilation error?

  • Check that the source name in stg_subscriptions.sql matches the name field in _sources.yml exactly (subscription_analytics).
  • Make sure _sources.yml is saved inside models/staging/ and not in the project root.
  • Verify the table name raw_subscriptions matches what dbt seed created. You can check this in the Supabase SQL Editor.

Still stuck? Why is my dbt staging model failing to compile against the source?

Add staging tests

Schema tests in dbt validate assumptions about your data automatically. They run assertions like uniqueness, not-null checks, and accepted values on specific columns.

  • Create a new file called _stg__models.yml inside models/staging/ and paste the following content:
version: 2

models:
  - name: stg_subscriptions
    description: Cleaned subscription records with invalid rows filtered out
    columns:
      - name: subscription_id
        description: Unique subscription identifier
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
      - name: plan_name
        tests:
          - accepted_values:
              values: ['starter', 'professional', 'enterprise']
      - name: mrr_amount
        tests:
          - not_null

What do these tests check?

  • unique on subscription_id ensures no duplicate subscription records exist.
  • not_null on subscription_id, customer_id, and mrr_amount ensures critical fields are never empty.
  • accepted_values on plan_name catches any unexpected plan names that might indicate corrupted data.
  • Save _stg__models.yml.
  • Run the tests against your staging model by running this command:
dbt test --select staging

You should see all tests passing with a green success message.

Tests failing?

  • If the unique test fails, check your seed CSV for duplicate subscription_id values.
  • If accepted_values fails, verify the plan names in your CSV match starter, professional, and enterprise exactly (all lowercase).
  • Make sure you ran dbt seed in the previous step so the raw data exists in your database.

Still stuck? Why are my dbt staging tests failing?

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files in models/staging/.

ⓧ I'd like to double check the full code

Here are all three staging files for comparison:

version: 2

sources:
  - name: subscription_analytics
    schema: "{{ target.schema }}"
    tables:
      - name: raw_subscriptions
        description: Seeded raw subscription data
with source as (
    select * from {{ source('subscription_analytics', 'raw_subscriptions') }}
),

cleaned as (
    select
        subscription_id,
        customer_id,
        plan_name,
        mrr_amount,
        start_date,
        end_date
    from source
    where mrr_amount >= 0
      and (end_date is null or end_date >= start_date)
)

select * from cleaned
version: 2

models:
  - name: stg_subscriptions
    description: Cleaned subscription records with invalid rows filtered out
    columns:
      - name: subscription_id
        description: Unique subscription identifier
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
      - name: plan_name
        tests:
          - accepted_values:
              values: ['starter', 'professional', 'enterprise']
      - name: mrr_amount
        tests:
          - not_null

Your staging layer is locked in. Next up, you'll build the intermediate layer that expands subscriptions into monthly time series and calculates per-customer MRR with gap filling.

Build the Intermediate Layer

Your staging layer gives you clean subscription records, but each row represents an entire subscription lifecycle. To answer "what happened to revenue this month?", you need one row per customer per month with their MRR at that point in time.

In this step, you will build the intermediate layer that expands subscriptions into monthly snapshots. This involves generating a calendar of months (a date spine), joining each subscription to its active months, and then aggregating everything at the customer-month level with gap-filling for inactive periods.

In this step, get ready to:
  • Generate a monthly date spine using dbt_utils.
  • Expand subscriptions to one row per active month and aggregate MRR per customer.
  • Run and verify intermediate models in Supabase.
Generate the monthly date spine

A date spine is a continuous sequence of dates at a fixed interval. Your intermediate models need a complete list of months to join against, so you will generate one spanning January 2024 through December 2024.

  • Create the models/intermediate/ directory by running this command:
mkdir -p models/intermediate
  • Create a new file called int_date_spine.sql inside models/intermediate/ and paste the following code:
{{
    config(
        materialized='table'
    )
}}

with spine as (
    {{
        dbt_utils.date_spine(
            datepart="month",
            start_date="cast('2024-01-01' as date)",
            end_date="cast('2025-01-01' as date)"
        )
    }}
)

select
    cast(date_month as date) as date_month
from spine

What does this code do?

The config(materialized='table') block tells dbt to write the output as a physical table in your database instead of a view.

The dbt_utils.date_spine macro generates a row for every month between the start and end dates. The spine includes start_date but not end_date, giving you January 2024 through December 2024 (12 rows).

The cast(date_month as date) ensures a clean date type on the output column.

  • Save int_date_spine.sql.
  • You won't see a change yet. The next model will use this spine to expand subscriptions into monthly rows.

✔️ Awesome, I've got everything!

Great. Make sure you have saved int_date_spine.sql before moving on.

ⓧ I'd like to double check the full code

{{
    config(
        materialized='table'
    )
}}

with spine as (
    {{
        dbt_utils.date_spine(
            datepart="month",
            start_date="cast('2024-01-01' as date)",
            end_date="cast('2025-01-01' as date)"
        )
    }}
)

select
    cast(date_month as date) as date_month
from spine
Expand subscriptions to active months and aggregate

Now you need to determine which months each subscription was active. A subscription is active in a given month if it started on or before the last day of that month, and either has no end date or its end date is on or after the first day of that month.

  • Create a new file called int_subscription_months.sql inside models/intermediate/ and paste the following code:
{{
    config(
        materialized='table'
    )
}}

with subscriptions as (
    select * from {{ ref('stg_subscriptions') }}
),

date_spine as (
    select * from {{ ref('int_date_spine') }}
),

subscription_months as (
    select
        s.subscription_id,
        s.customer_id,
        s.plan_name,
        s.mrr_amount,
        d.date_month
    from subscriptions s
    inner join date_spine d
        on s.start_date <= (d.date_month + interval '1 month' - interval '1 day')::date
        and (s.end_date is null or s.end_date >= d.date_month)
)

select * from subscription_months

What does this join logic do?

The join condition checks two things for each subscription-month pair.

  • start_date <= last day of month ensures the subscription had already begun by the end of that month.
  • end_date is null or end_date >= first day of month ensures the subscription had not yet ended when the month started.

The expression (d.date_month + interval '1 month' - interval '1 day')::date calculates the last day of each month in PostgreSQL.

  • Save int_subscription_months.sql.
  • You won't see a change yet. The next model aggregates these rows at the customer level.

✔️ Awesome, I've got everything!

Great. Make sure you have saved int_subscription_months.sql before moving on.

ⓧ I'd like to double check the full code

{{
    config(
        materialized='table'
    )
}}

with subscriptions as (
    select * from {{ ref('stg_subscriptions') }}
),

date_spine as (
    select * from {{ ref('int_date_spine') }}
),

subscription_months as (
    select
        s.subscription_id,
        s.customer_id,
        s.plan_name,
        s.mrr_amount,
        d.date_month
    from subscriptions s
    inner join date_spine d
        on s.start_date <= (d.date_month + interval '1 month' - interval '1 day')::date
        and (s.end_date is null or s.end_date >= d.date_month)
)

select * from subscription_months

Now you need to aggregate MRR at the customer-month level and fill in gaps for months where a customer had no active subscription. Without gap-filling, the LAG() window function would skip months and produce incorrect comparisons.

Why is gap-filling important?

Imagine a customer who subscribes in January, cancels in March, and reactivates in July. Without gap-filling, the row for July would compare against March (the last active month), making it look like a simple continuation.

By inserting zero-MRR rows for April through June, LAG() correctly sees $0 in June and classifies July as a reactivation.

  • Create a new file called int_customer_monthly_mrr.sql inside models/intermediate/ and paste the following code:
{{
    config(
        materialized='table'
    )
}}

with subscription_months as (
    select * from {{ ref('int_subscription_months') }}
),

customer_mrr as (
    select
        customer_id,
        date_month,
        sum(mrr_amount) as mrr
    from subscription_months
    group by customer_id, date_month
),

customer_bounds as (
    select
        customer_id,
        min(date_month) as first_active_month,
        max(date_month) as last_active_month
    from customer_mrr
    group by customer_id
),

date_spine as (
    select * from {{ ref('int_date_spine') }}
),

customer_spine as (
    select
        cb.customer_id,
        ds.date_month
    from customer_bounds cb
    inner join date_spine ds
        on ds.date_month >= cb.first_active_month
        and ds.date_month <= cb.last_active_month
),

gap_filled as (
    select
        cs.customer_id,
        cs.date_month,
        coalesce(cm.mrr, 0) as mrr
    from customer_spine cs
    left join customer_mrr cm
        on cs.customer_id = cm.customer_id
        and cs.date_month = cm.date_month
),

with_previous as (
    select
        customer_id,
        date_month,
        mrr,
        lag(mrr) over (partition by customer_id order by date_month) as previous_month_mrr
    from gap_filled
)

select * from with_previous

What does this code do?

  • customer_mrr aggregates all active subscriptions per customer per month into a single MRR total.
  • customer_bounds finds the first and last active month for each customer.
  • customer_spine cross-joins each customer with every month between their first and last active month, creating a continuous timeline.
  • gap_filled left-joins the actual MRR data back onto the spine, filling missing months with 0.
  • with_previous uses the LAG() window function to look back one row (one month) and store the previous month's MRR alongside the current month.
  • Save int_customer_monthly_mrr.sql.

Seeing a syntax error in your SQL file?

Make sure the Jinja brackets {{ }} are correctly placed around the config block and ref() calls. dbt uses Jinja templating, so every ref() or config() must be wrapped in double curly braces.

help me fix a syntax error in my dbt intermediate model

✔️ Awesome, I've got everything!

Great. Make sure you have saved int_customer_monthly_mrr.sql before moving on.

ⓧ I'd like to double check the full code

{{
    config(
        materialized='table'
    )
}}

with subscription_months as (
    select * from {{ ref('int_subscription_months') }}
),

customer_mrr as (
    select
        customer_id,
        date_month,
        sum(mrr_amount) as mrr
    from subscription_months
    group by customer_id, date_month
),

customer_bounds as (
    select
        customer_id,
        min(date_month) as first_active_month,
        max(date_month) as last_active_month
    from customer_mrr
    group by customer_id
),

date_spine as (
    select * from {{ ref('int_date_spine') }}
),

customer_spine as (
    select
        cb.customer_id,
        ds.date_month
    from customer_bounds cb
    inner join date_spine ds
        on ds.date_month >= cb.first_active_month
        and ds.date_month <= cb.last_active_month
),

gap_filled as (
    select
        cs.customer_id,
        cs.date_month,
        coalesce(cm.mrr, 0) as mrr
    from customer_spine cs
    left join customer_mrr cm
        on cs.customer_id = cm.customer_id
        and cs.date_month = cm.date_month
),

with_previous as (
    select
        customer_id,
        date_month,
        mrr,
        lag(mrr) over (partition by customer_id order by date_month) as previous_month_mrr
    from gap_filled
)

select * from with_previous
Run and verify intermediate models
  • Run all three intermediate models by running this command:
dbt run --select intermediate

What does this command do?

The --select intermediate flag tells dbt to only run models in the models/intermediate/ directory. dbt automatically resolves dependencies between them, running int_date_spine first, then int_subscription_months, then int_customer_monthly_mrr.

  • You should see output showing all three models completed successfully with a status of OK.

Seeing a compilation or database error?

  • If you see a "relation does not exist" error, make sure you ran dbt seed and dbt run --select staging in previous steps.
  • If you see a Jinja compilation error, check that every ref() call uses the exact model name (e.g. ref('stg_subscriptions'), not a file path).
  • If you see a connection error, verify your Supabase project is still running in the dashboard.

help me debug a dbt run error in my intermediate layer

Now spot-check the results to make sure the data looks correct.

  • Open the Supabase dashboard and navigate to the SQL Editor.
  • Run this query to see the customer-monthly MRR timeline:
select * from int_customer_monthly_mrr
where customer_id = 'cust_003'
order by date_month;

What should I see?

Customer cust_003 had a $29 starter plan from February to April, then no subscription from May to June, then a $79 professional plan from July onward.

You should see rows for every month from February through December. The mrr column should show 29, 29, 29, 0, 0, 79, 79, 79, 79, 79, 79 for the months February through December. The previous_month_mrr column should be NULL for February (no prior month), then match the prior month's MRR for subsequent rows.

Your intermediate layer is complete. You now have a continuous monthly timeline for every customer with their MRR and their previous month's MRR side by side. Next up, you will build the marts layer that classifies each month-over-month change as new, churn, upgrade, downgrade, or reactivation.

Build the Marts Layer

Your intermediate layer now produces a clean, gap-filled timeline of every customer's monthly MRR with the previous month's value attached. The missing piece is the business logic that turns these numbers into answers.

In this step, you will build the marts layer. This is where raw revenue figures become classified movements (new, churn, upgrade, downgrade, reactivation) and roll up into a monthly waterfall summary that any team can query.

In this step, get ready to:
  • Create a fact model that classifies each customer-month into a revenue movement category.
  • Create a summary model that aggregates movements into a monthly MRR waterfall.
  • Add schema tests and run the full pipeline end-to-end.
Create the MRR movements model

The movements model is the core of subscription analytics. It takes each customer-month row and assigns a category based on what happened to their revenue compared to the previous month.

  • Create a new file at models/marts/fct_mrr_movements.sql and add the following code:
{{
    config(
        materialized='table'
    )
}}

with customer_mrr as (
    select * from {{ ref('int_customer_monthly_mrr') }}
),

classified as (
    select
        customer_id,
        date_month,
        mrr,
        previous_month_mrr,
        case
            when previous_month_mrr is null
                then 'new'
            when mrr = 0 and previous_month_mrr > 0
                then 'churn'
            when mrr > 0 and previous_month_mrr = 0
                then 'reactivation'
            when mrr > previous_month_mrr
                then 'upgrade'
            when mrr < previous_month_mrr and mrr > 0
                then 'downgrade'
            else 'no_change'
        end as change_category,
        mrr - coalesce(previous_month_mrr, 0) as mrr_change
    from customer_mrr
)

select * from classified

What does this code do?

The case statement evaluates each customer-month in a specific order. The order matters because a customer with no previous month is always new, regardless of their MRR value.

  • previous_month_mrr is null means the customer had no record last month. This is their first appearance, so they are new.
  • mrr = 0 and previous_month_mrr > 0 means they were paying last month but are not this month. That is churn.
  • mrr > 0 and previous_month_mrr = 0 means they returned after a zero-MRR month. That is a reactivation.
  • The mrr_change column calculates the dollar difference for downstream aggregation.
  • Save models/marts/fct_mrr_movements.sql.

Why this classification order?

The dbt Labs canonical pattern for subscription revenue evaluates categories in this exact sequence: new, churn, reactivation, upgrade, downgrade. A customer whose previous_month_mrr is null cannot be churn, reactivation, upgrade, or downgrade. By checking null first, you avoid false positives in every other category.

Create the MRR summary model

The summary model rolls up individual customer movements into a monthly waterfall. This is the table a Finance team queries to answer "what happened to revenue this month?"

  • Create a new file at models/marts/fct_mrr_summary.sql and add the following code:
{{
    config(
        materialized='table'
    )
}}

with movements as (
    select * from {{ ref('fct_mrr_movements') }}
),

monthly_summary as (
    select
        date_month,
        sum(case when change_category = 'new' then mrr else 0 end) as new_mrr,
        sum(case when change_category = 'reactivation' then mrr else 0 end) as reactivation_mrr,
        sum(case when change_category = 'upgrade' then mrr_change else 0 end) as expansion_mrr,
        sum(case when change_category = 'downgrade' then mrr_change else 0 end) as contraction_mrr,
        sum(case when change_category = 'churn' then previous_month_mrr else 0 end) as churned_mrr,
        sum(mrr) as ending_mrr,
        count(distinct case when mrr > 0 then customer_id end) as active_customers,
        count(distinct case when change_category = 'new' then customer_id end) as new_customers,
        count(distinct case when change_category = 'churn' then customer_id end) as churned_customers
    from movements
    group by date_month
),

with_starting as (
    select
        date_month,
        ending_mrr - new_mrr - reactivation_mrr - expansion_mrr - contraction_mrr + churned_mrr as starting_mrr,
        new_mrr,
        reactivation_mrr,
        expansion_mrr,
        contraction_mrr,
        churned_mrr,
        ending_mrr,
        active_customers,
        new_customers,
        churned_customers
    from monthly_summary
)

select * from with_starting
order by date_month

What does this code do?

The model aggregates all customer movements into a single row per month. Each column isolates a specific type of revenue change.

  • new_mrr sums the full MRR of customers classified as new.
  • expansion_mrr sums only the mrr_change (the delta) for upgrades, not their total MRR.
  • churned_mrr uses previous_month_mrr because the churned customer's current MRR is zero.
  • starting_mrr is derived from ending MRR by reversing all movements. This guarantees the waterfall always reconciles.
  • Save models/marts/fct_mrr_summary.sql.

Confused about starting_mrr?

The formula ending_mrr - new_mrr - reactivation_mrr - expansion_mrr - contraction_mrr + churned_mrr looks backwards, but it is derived from the standard waterfall identity: starting + new + reactivation + expansion + contraction - churned = ending. Rearranging for starting gives the formula you see.

Note that contraction_mrr is already negative (since downgrades produce a negative mrr_change), so subtracting a negative adds it back.

help me understand the starting_mrr waterfall formula

Add mart tests and run the full project

Schema tests in dbt validate that your data meets expectations every time the pipeline runs. You will add tests that ensure no duplicate customer-month rows exist and that every movement falls into a known category.

  • Create a new file at models/marts/_marts__models.yml and add the following configuration:
version: 2

models:
  - name: fct_mrr_movements
    description: >
      One row per customer per month with MRR and the change category
      describing the revenue movement relative to the previous month.
      Grain: customer_id x date_month.
    columns:
      - name: customer_id
        tests:
          - not_null
      - name: date_month
        tests:
          - not_null
      - name: change_category
        tests:
          - accepted_values:
              values: ['new', 'churn', 'reactivation', 'upgrade', 'downgrade', 'no_change']
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - customer_id
            - date_month

  - name: fct_mrr_summary
    description: >
      Monthly MRR waterfall summary showing starting MRR, movements by type,
      and ending MRR with customer counts. One row per month.
    columns:
      - name: date_month
        tests:
          - unique
          - not_null

What do these tests check?

  • dbt_utils.unique_combination_of_columns ensures no customer appears twice in the same month. This protects the grain of the model.
  • accepted_values on change_category catches any row that falls outside the six known categories, which would indicate a logic bug.
  • unique and not_null on fct_mrr_summary.date_month ensures exactly one summary row per month.
  • Save models/marts/_marts__models.yml.
  • Run the entire pipeline from seed to test by running this command:
dbt build

What does dbt build do?

The dbt build command runs seeds, models, and tests in dependency order. It is equivalent to running dbt seed, dbt run, and dbt test together. If any test fails, dbt reports which test and which rows violated it.

  • Verify that all models build successfully and all tests pass. You should see a summary line showing passed tests with no failures.

Seeing test failures?

If the unique_combination_of_columns test fails, a customer has duplicate months in your intermediate layer. Check int_subscription_months.sql for overlapping subscription date ranges in your seed data.

If the accepted_values test fails, your case statement in fct_mrr_movements.sql might be producing a NULL. Make sure the else 'no_change' clause is present at the end.

help me debug my failing dbt tests

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files in the models/marts/ directory.

ⓧ I'd like to double check the full code

Here is the complete code for each mart file.

{{
    config(
        materialized='table'
    )
}}

with customer_mrr as (
    select * from {{ ref('int_customer_monthly_mrr') }}
),

classified as (
    select
        customer_id,
        date_month,
        mrr,
        previous_month_mrr,
        case
            when previous_month_mrr is null
                then 'new'
            when mrr = 0 and previous_month_mrr > 0
                then 'churn'
            when mrr > 0 and previous_month_mrr = 0
                then 'reactivation'
            when mrr > previous_month_mrr
                then 'upgrade'
            when mrr < previous_month_mrr and mrr > 0
                then 'downgrade'
            else 'no_change'
        end as change_category,
        mrr - coalesce(previous_month_mrr, 0) as mrr_change
    from customer_mrr
)

select * from classified
{{
    config(
        materialized='table'
    )
}}

with movements as (
    select * from {{ ref('fct_mrr_movements') }}
),

monthly_summary as (
    select
        date_month,
        sum(case when change_category = 'new' then mrr else 0 end) as new_mrr,
        sum(case when change_category = 'reactivation' then mrr else 0 end) as reactivation_mrr,
        sum(case when change_category = 'upgrade' then mrr_change else 0 end) as expansion_mrr,
        sum(case when change_category = 'downgrade' then mrr_change else 0 end) as contraction_mrr,
        sum(case when change_category = 'churn' then previous_month_mrr else 0 end) as churned_mrr,
        sum(mrr) as ending_mrr,
        count(distinct case when mrr > 0 then customer_id end) as active_customers,
        count(distinct case when change_category = 'new' then customer_id end) as new_customers,
        count(distinct case when change_category = 'churn' then customer_id end) as churned_customers
    from movements
    group by date_month
),

with_starting as (
    select
        date_month,
        ending_mrr - new_mrr - reactivation_mrr - expansion_mrr - contraction_mrr + churned_mrr as starting_mrr,
        new_mrr,
        reactivation_mrr,
        expansion_mrr,
        contraction_mrr,
        churned_mrr,
        ending_mrr,
        active_customers,
        new_customers,
        churned_customers
    from monthly_summary
)

select * from with_starting
order by date_month
version: 2

models:
  - name: fct_mrr_movements
    description: >
      One row per customer per month with MRR and the change category
      describing the revenue movement relative to the previous month.
      Grain: customer_id x date_month.
    columns:
      - name: customer_id
        tests:
          - not_null
      - name: date_month
        tests:
          - not_null
      - name: change_category
        tests:
          - accepted_values:
              values: ['new', 'churn', 'reactivation', 'upgrade', 'downgrade', 'no_change']
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - customer_id
            - date_month

  - name: fct_mrr_summary
    description: >
      Monthly MRR waterfall summary showing starting MRR, movements by type,
      and ending MRR with customer counts. One row per month.
    columns:
      - name: date_month
        tests:
          - unique
          - not_null

Your marts layer is now classifying every dollar of revenue movement and rolling it up into a monthly waterfall. Next up, you will generate documentation and run a validation query to confirm the numbers reconcile.

Generate Documentation and Validate

Your full pipeline is built and tested. The final piece of a production-grade dbt project is documentation that lets anyone on your team explore the data lineage without reading SQL files.

In this step, you will generate browsable docs from your model descriptions and run a final validation query to confirm your MRR waterfall reconciles correctly.

In this step, get ready to:
  • Generate dbt documentation with full DAG lineage.
  • Serve docs locally and browse the model graph.
  • Run a validation query confirming MRR waterfall integrity.
Confirm model descriptions are in place

dbt docs pulls descriptions from your YAML schema files to populate the documentation site. Your _marts__models.yml file already includes descriptions for both mart models from the previous step.

  • Open models/marts/_marts__models.yml and confirm it has description fields for both fct_mrr_movements and fct_mrr_summary.

Your file should look like this:

version: 2

models:
  - name: fct_mrr_movements
    description: >
      One row per customer per month with MRR and the change category
      describing the revenue movement relative to the previous month.
      Grain: customer_id x date_month.
    columns:
      - name: customer_id
        tests:
          - not_null
      - name: date_month
        tests:
          - not_null
      - name: change_category
        tests:
          - accepted_values:
              values: ['new', 'churn', 'reactivation', 'upgrade', 'downgrade', 'no_change']
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - customer_id
            - date_month

  - name: fct_mrr_summary
    description: >
      Monthly MRR waterfall summary showing starting MRR, movements by type,
      and ending MRR with customer counts. One row per month.
    columns:
      - name: date_month
        tests:
          - unique
          - not_null

Why do descriptions matter?

The description fields in your YAML files become searchable, readable documentation on the dbt docs site. Without them, your teammates would need to read raw SQL to understand what each model does.

Good descriptions name the grain (one row per what) and the business purpose. This makes the docs site immediately useful for non-technical stakeholders.

Generate and serve docs

dbt can compile all your model descriptions, tests, and relationships into a static documentation site with a visual DAG (directed acyclic graph) showing how data flows from seed to mart.

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

What does this command do?

This command introspects your database and your dbt project, then writes two files into the target/ folder: catalog.json (column-level metadata from the database) and manifest.json (your model definitions, descriptions, and relationships).

  • You should see a success message confirming the catalog was written.
  • 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 a fully interactive documentation site. You can search models, read descriptions, and explore the DAG.

  • Your browser should open automatically to the docs site. If it does not, navigate to http://localhost:8080 manually.
  • Click the blue graph icon in the bottom-right corner of the page to open the DAG view.
  • You should see the full lineage from raw_subscriptions (seed) through stg_subscriptions (staging) to the intermediate models and finally to fct_mrr_movements and fct_mrr_summary (marts).
  • Press Ctrl+C in your terminal to stop the docs server when you are done browsing.

Docs site not loading?

Make sure you ran dbt docs generate before dbt docs serve. The serve command needs the target/ files to exist first.

If port 8080 is already in use, stop any other process using that port and try again.

Why can't I open the dbt docs site locally?

Run a validation query in Supabase

The ultimate test of an MRR waterfall is that it reconciles. Starting MRR plus all inflows minus all outflows must equal ending MRR for every month.

  • Switch back to your Supabase dashboard in your browser.
  • Click SQL Editor in the left sidebar.
  • Run the following validation query to check your MRR waterfall:
-- Validate MRR waterfall reconciliation
select
    date_month,
    starting_mrr,
    new_mrr,
    reactivation_mrr,
    expansion_mrr,
    contraction_mrr,
    churned_mrr,
    ending_mrr,
    -- This should equal ending_mrr for every row
    starting_mrr + new_mrr + reactivation_mrr + expansion_mrr + contraction_mrr - churned_mrr as calculated_ending_mrr,
    active_customers
from fct_mrr_summary
order by date_month;

What does this query check?

The calculated_ending_mrr column applies the waterfall formula: starting MRR + new + reactivation + expansion + contraction (a negative value) - churned = ending MRR.

If calculated_ending_mrr matches ending_mrr for every row, your pipeline is correctly tracking all revenue movements. Nothing is leaking or double-counted.

  • Click Run in the SQL Editor.
  • Verify that the calculated_ending_mrr column matches the ending_mrr column for every month.

Values don't match?

If calculated_ending_mrr differs from ending_mrr, run dbt build to rebuild all models from scratch. Stale intermediate tables from a partial run can cause mismatches.

Why doesn't my MRR waterfall reconcile?

Your subscription analytics pipeline is fully documented, validated, and ready for anyone on your team to query. Next up is the Secret Mission, where you will build a retention cohort model to track customer loyalty over time.

Secret mission

Retention Cohort Analysis

Your MRR waterfall tells you what happened to revenue this month. But it doesn't answer a deeper question: which signup cohorts stick around the longest, and which ones leak revenue? Build a retention cohort model that calculates logo retention, NRR, and GRR by signup month.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs on the Supabase free tier and uses local files, so there are no ongoing costs.

Resources you used:

  • Supabase project (free-tier PostgreSQL database with your dbt-created tables and seed data).
  • Local subscription_analytics dbt project folder on your Desktop.
  • Python virtual environment (dbt-env folder).

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend your dbt models further.

  • Your Supabase project stays active on the free tier at no cost.
  • Your local subscription_analytics folder and dbt-env virtual environment remain on your machine.
  • Note that Supabase free-tier projects pause automatically after 7 days of inactivity. You can restore them from the dashboard at any time.

Pause - I'll come back to this later

Shut down running processes to free up memory while keeping your files intact for later.

  • Stop the dbt docs server if it is still running by pressing Ctrl+C in the terminal.
  • Deactivate your virtual environment by running:
deactivate
  • Your Supabase project will automatically pause after 7 days of inactivity on the free tier. No manual action needed.
  • When you are ready to resume, navigate back to the subscription_analytics folder, reactivate the virtual environment with source dbt-env/bin/activate, and restore the Supabase project from the dashboard.

Delete - I don't want to use this again

Remove all project resources and start fresh. This cannot be undone.

  • Delete your Supabase project:
  • Open the Supabase dashboard and select your project.
  • Navigate to the General Settings page (under Project Settings in the sidebar).
  • Scroll down and click Delete project. Confirm the deletion when prompted. This permanently removes your database and all associated data.
  • Delete your local project files and virtual environment by running:

macOS

cd ~/Desktop
rm -rf subscription_analytics
rm -rf dbt-env

Windows

cd ~\Desktop
rmdir /s /q subscription_analytics
rmdir /s /q dbt-env
  • Remove the dbt profiles configuration by deleting your profiles.yml file:

macOS

rm ~/.dbt/profiles.yml

Windows

del %USERPROFILE%\.dbt\profiles.yml

Nice Work!

Nice Work!

You just built a production-grade subscription analytics pipeline with dbt on Supabase. Any team in your organization can now query consistent, tested definitions for MRR, churn, and customer lifecycle metrics.

You've learned how to:

  • Connected dbt-core to a Supabase PostgreSQL database and loaded seed data representing real SaaS subscription lifecycles.
  • Built a 3-layer dbt project (staging, intermediate, marts) that models MRR at the customer-month grain with movement classification including new, churn, upgrade, downgrade, and reactivation.
  • Created an MRR waterfall summary with reconciliation validation, automated data tests, and browsable dbt documentation with full DAG lineage.
  • Built a retention cohort model calculating logo retention, NRR, and GRR by signup cohort.

Ready to quiz yourself?