Model subscription revenue with dbt

Know what one row means.

subscriptions

Key · subscription_id

One row per account subscription period.

invoices

Key · invoice_id

One row per subscription billing month.

payments

Key · payment_id

One row per invoice payment attempt.

int_paid_subscription_invoices

Key · invoice_id

One paid monthly invoice with successful collection evidence.

mart_subscription_revenue_monthly

Key · invoice_month + account_id + subscription_id

One row per invoice month, account, and subscription.

Fix the meaning before the code.

Recognized subscription revenue

The amount_due on a paid monthly invoice, assigned to its invoice month. Release v1.0.0 has monthly billing only.

Collected amount

The sum of successful payment attempts for an invoice. Failed attempts remain operational evidence but contribute zero.

Monthly recurring revenue

The contracted mrr stored on subscriptions. It is useful for lifecycle analysis but is not substituted for paid invoice revenue.

Work from source grain to tested output.

1. 01

   Declare sources and standardize billing fields

   Keep identifiers stable, cast date and numeric fields at the staging boundary, and preserve invoice and payment statuses for auditability.

   version: 2
   
   sources:
     - name: saas_billing
       schema: raw
       tables:
         - name: subscriptions
           columns:
             - name: subscription_id
               tests: [unique, not_null]
         - name: invoices
           columns:
             - name: invoice_id
               tests: [unique, not_null]
         - name: payments
           columns:
             - name: payment_id
               tests: [unique, not_null]

   with source as (
   
       select * from {{ source('saas_billing', 'invoices') }}
   
   )
   
   select
       invoice_id::text as invoice_id,
       subscription_id::text as subscription_id,
       invoice_date::date as invoice_date,
       due_date::date as due_date,
       amount_due::numeric as amount_due,
       invoice_status::text as invoice_status
   from source

   with source as (
   
       select * from {{ source('saas_billing', 'subscriptions') }}
   
   )
   
   select
       subscription_id::text as subscription_id,
       account_id::text as account_id,
       plan_id::text as plan_id,
       started_at::date as started_at,
       ended_at::date as ended_at,
       status::text as subscription_status,
       seats::integer as seats,
       mrr::numeric as contracted_mrr
   from source

   with source as (
   
       select * from {{ source('saas_billing', 'payments') }}
   
   )
   
   select
       payment_id::text as payment_id,
       invoice_id::text as invoice_id,
       attempted_at::date as attempted_at,
       payment_status::text as payment_status,
       amount::numeric as payment_amount
   from source

   Verification

   • Primary source identifiers are unique and non-null.
   • Money uses Postgres numeric rather than binary floating point.
   • Failed payment attempts remain available for collections analysis.
2. 02

   Build the reusable paid-invoice ledger

   Aggregate successful collections to one row per invoice before joining them to invoice and subscription grains.

   with invoices as (
   
       select * from {{ ref('stg_saas_billing__invoices') }}
   
   ),
   
   subscriptions as (
   
       select * from {{ ref('stg_saas_billing__subscriptions') }}
   
   ),
   
   successful_payments as (
   
       select
           invoice_id,
           sum(payment_amount) as collected_amount
       from {{ ref('stg_saas_billing__payments') }}
       where payment_status = 'succeeded'
       group by invoice_id
   
   )
   
   select
       invoice.invoice_id,
       subscription.account_id,
       invoice.subscription_id,
       date_trunc('month', invoice.invoice_date)::date as invoice_month,
       invoice.amount_due as recognized_revenue,
       coalesce(payment.collected_amount, 0)::numeric as collected_amount,
       subscription.contracted_mrr
   from invoices invoice
   inner join subscriptions subscription
       on subscription.subscription_id = invoice.subscription_id
   left join successful_payments payment
       on payment.invoice_id = invoice.invoice_id
   where invoice.invoice_status = 'paid'

   Verification

   • One source invoice produces at most one ledger row.
   • Open and void invoices do not enter recognized revenue.
   • Several failed attempts followed by one success do not multiply invoice revenue.
3. 03

   Publish the monthly mart and enforce its contract

   Aggregate only after the invoice-level ledger exists, then test grain, required dimensions, and revenue reconciliation.

   with paid_subscription_invoices as (
   
       select * from {{ ref('int_paid_subscription_invoices') }}
   
   )
   
   select
       invoice_month,
       account_id,
       subscription_id,
       count(*)::bigint as paid_invoice_count,
       sum(recognized_revenue)::numeric as recognized_revenue,
       sum(collected_amount)::numeric as collected_amount
   from paid_subscription_invoices
   group by
       invoice_month,
       account_id,
       subscription_id

   version: 2
   
   models:
     - name: mart_subscription_revenue_monthly
       description: >
         Paid invoice revenue by invoice month, account, and subscription.
       tests:
         - dbt_utils.unique_combination_of_columns:
             arguments:
               combination_of_columns:
                 - invoice_month
                 - account_id
                 - subscription_id
       columns:
         - name: invoice_month
           tests: [not_null]
         - name: account_id
           tests: [not_null]
         - name: subscription_id
           tests: [not_null]
         - name: paid_invoice_count
           tests:
             - not_null
             - dbt_utils.accepted_range:
                 arguments:
                   min_value: 1
                   inclusive: true

   with ledger_total as (
   
       select sum(recognized_revenue) as recognized_revenue
       from {{ ref('int_paid_subscription_invoices') }}
   
   ),
   
   mart_total as (
   
       select sum(recognized_revenue) as recognized_revenue
       from {{ ref('mart_subscription_revenue_monthly') }}
   
   )
   
   select
       ledger_total.recognized_revenue as ledger_revenue,
       mart_total.recognized_revenue as mart_revenue
   from ledger_total
   cross join mart_total
   where ledger_total.recognized_revenue
       is distinct from mart_total.recognized_revenue

   select
       invoice_id,
       recognized_revenue,
       collected_amount
   from {{ ref('int_paid_subscription_invoices') }}
   where recognized_revenue is distinct from collected_amount

   Verification

   • dbt test returns no duplicate-grain or null-dimension failures.
   • Both singular reconciliation tests return zero rows.
   • Contracted MRR remains separate from recognized invoice revenue.

What this result does not claim.

• —The SQL follows the repository's observed Postgres dbt profile and uses Postgres casts and date_trunc.
• —Release v1.0.0 uses monthly invoices; annual contracts, proration, credits, and partial-period allocation are not represented.
• —This is an instructional operating definition, not an accounting-policy determination under ASC 606 or IFRS 15.
• —The source has no currency column, so the walkthrough assumes one reporting currency and does not support conversion.
• —The revenue mart does not calculate contracted MRR movement, churn, expansion, or point-in-time entitlement state.

Open the matching dataset and exercise.