One ARR Number: Building an MRR Movement Model in dbt

Last updated: October 2026

Fast-growing software companies often reach a point where nobody can say what annual recurring revenue is. Sales has one figure from the CRM, finance has another from spreadsheets, and the board deck has a third. Each is calculated from slightly different data with slightly different rules, and none of them is obviously wrong.

This article describes how we resolved that for a B2B SaaS company, covered in our finance data modelling case study. The company had grown from 80 to 600 employees in four years and kept its revenue logic in 47 Excel workbooks. Sales reported ARR of $18.2 million, finance $17.8 million, and the board deck showed $18.0 million, the average of the two. Project details come from that engagement. The SQL and YAML are simplified illustrations written for this article.

Find where the numbers diverge

The $400,000 gap between the sales and finance figures came from one place: the join between Salesforce and Stripe. Salesforce records contract amounts at the deal level. Stripe records billing at the subscription item level. When a customer upgrades mid-cycle, Salesforce shows a single amendment record, while Stripe shows a prorated charge, a credit and a new subscription line. Getting that join right took two full weeks, and it accounted for the entire discrepancy.

The lesson for the model is to decide, field by field, which system is the source of truth. We used Stripe for billing amounts and Salesforce for contract dates and customer metadata. Recurring revenue should come from the recurring price of the active subscription items, normalised to a monthly amount. Prorations and credits are one-off adjustments; counting them as MRR is what makes a mid-month upgrade look like a spike followed by a dip.

Data arrived through Hevo: Salesforce opportunities, accounts and contracts every 15 minutes, Stripe subscriptions, invoices, charges and refunds through Hevo's Stripe connector, and NetSuite general ledger data nightly, because finance did not need real-time GL figures and NetSuite's API rate limits made nightly the practical choice.

Three layers, one definition per metric

The project used 134 dbt models in three layers:

The rule that matters most is that each metric is defined in exactly one model. Dashboards, the board pack and ad hoc analysis all read from the marts, so a change to the ARR definition is a reviewed change to one SQL file.

The MRR movement model

The revenue waterfall compares each customer's MRR with the previous month and labels the difference. Two details make it work. The model needs a row for every customer in every month from their first paid month onwards, including months with zero MRR, or churn cannot be detected. And reactivation has to be distinguished from new business, which needs to know whether the customer has ever paid before.

with monthly as (
    -- one row per customer per month from first paid month,
    -- with mrr = 0 in months without an active subscription
    select customer_id, month, mrr
    from {{ ref('int_customer_mrr_monthly') }}
),

compared as (
    select
        customer_id,
        month,
        mrr,
        coalesce(lag(mrr) over (partition by customer_id order by month), 0) as prev_mrr,
        coalesce(max(mrr) over (partition by customer_id order by month
                 rows between unbounded preceding and 1 preceding), 0)        as max_prior_mrr
    from monthly
)

select
    customer_id,
    month,
    prev_mrr,
    mrr,
    mrr - prev_mrr as movement_amount,
    case
        when prev_mrr = 0 and mrr > 0 and max_prior_mrr = 0 then 'new'
        when prev_mrr = 0 and mrr > 0                       then 'reactivation'
        when prev_mrr > 0 and mrr = 0                       then 'churn'
        when mrr > prev_mrr                                 then 'expansion'
        when mrr < prev_mrr                                 then 'contraction'
        else 'flat'
    end as movement_type
from compared

Because the classification is made once per customer per month, a customer cannot be counted as both churned and expanded in the same month. If you calculate movements per subscription and roll them up, add a test for that, because a customer who cancels one product and upgrades another can otherwise appear in both columns.

Testing the business rules, not only the columns

The project ran more than 280 dbt tests. The standard ones, unique and not_null on keys, catch broken joins. The valuable ones encode finance rules: ARR can never be negative, MRR movements must reconcile, and a customer cannot have a churn and an expansion event in the same month. These tests stop a bad number before the finance team sees it.

dbt 1.8 renamed the tests: key to data_tests:, and both still work. From dbt 1.10.5, arguments to generic tests go under an arguments: key, as below.

models:
  - name: stg_salesforce__opportunities
    columns:
      - name: opportunity_id
        data_tests:
          - unique
          - not_null
      - name: stage_name
        data_tests:
          - accepted_values:
              arguments:
                values: ['Prospecting', 'Qualification', 'Proposal',
                         'Negotiation', 'Closed Won', 'Closed Lost']

  - name: fct_annual_recurring_revenue
    data_tests:
      - dbt_utils.expression_is_true:
          arguments:
            expression: "arr >= 0"

The accepted_values test on the opportunity stage earned its place in week six. A Salesforce administrator changed a picklist value on the stage field, which would have silently excluded about $200,000 of closed-won revenue from ARR. The test caught it within 15 minutes of the next dbt run.

The reconciliation rule needs some care. Because each movement is defined as the change from the previous month, opening MRR plus the month's movements equals closing MRR by construction, so a test of that alone can never fail. The useful check is that each month's opening MRR equals the previous month's closing MRR. It fails when a customer drops out of the monthly spine instead of appearing with zero MRR, which is exactly how churn goes missing. Written as a singular test, a SQL file in the tests directory, it returns the months that break the rule.

-- Returns months whose opening MRR differs from the previous
-- month's closing MRR, which means customers are missing.
with by_month as (
    select
        month,
        sum(prev_mrr) as opening_mrr,
        sum(mrr)      as closing_mrr
    from {{ ref('int_mrr_movements') }}
    group by month
),

checked as (
    select
        month,
        opening_mrr,
        lag(closing_mrr) over (order by month) as prior_closing_mrr
    from by_month
)

select *
from checked
where prior_closing_mrr is not null
  and abs(opening_mrr - prior_closing_mrr) > 0.005

Data tests check the data that is there. Unit tests, added in dbt 1.8, check the logic against small, fixed inputs, which is the right tool for the movement classification: each case, including reactivation after a gap, can be written down once and checked on every change. dbt recommends running unit tests in development and CI rather than in production.

unit_tests:
  - name: mrr_movements_classify_each_case
    model: int_mrr_movements
    given:
      - input: ref('int_customer_mrr_monthly')
        rows:
          - {customer_id: 1, month: '2026-01-01', mrr: 1000}
          - {customer_id: 1, month: '2026-02-01', mrr: 1500}
          - {customer_id: 1, month: '2026-03-01', mrr: 0}
          - {customer_id: 1, month: '2026-04-01', mrr: 800}
    expect:
      rows:
        - {customer_id: 1, month: '2026-01-01', movement_type: 'new'}
        - {customer_id: 1, month: '2026-02-01', movement_type: 'expansion'}
        - {customer_id: 1, month: '2026-03-01', movement_type: 'churn'}
        - {customer_id: 1, month: '2026-04-01', movement_type: 'reactivation'}

For tests that auditors may want to review, store_failures saves the failing rows to a table, and the severity, warn_if and error_if settings let a small number of failures warn without stopping the build.

Documentation the finance team will read

We expected the finance analysts to resist moving from Excel to SQL models. dbt's generated documentation won them over: they could see the lineage, read each metric definition in plain English and trace any number back to its source. The FP&A lead said it was the first time she could explain the ARR calculation to a board member without opening Excel. When the auditors asked how net revenue retention is calculated, the answer was a version-controlled definition they could review.

If you want metric definitions that downstream tools can query directly, MetricFlow, the engine behind the dbt Semantic Layer, is open source under the Apache 2.0 licence and can be run locally. Querying the Semantic Layer from BI tools requires a paid dbt platform account at the Starter tier or above.

Roll out one metric at a time

We did not migrate all 47 workbooks at once. ARR and MRR came first and were signed off by the CFO and the auditor. Churn and expansion followed, then net revenue retention, each with its own sign-off cycle. Replacing everything together would have meant one large, contested review instead of several small ones.

Month-end close went from 12 business days to 3.5, the company now reports one ARR figure across every team, and the auditors described it as the best data they had seen at that company size.