A duplicated invoice line can make MRR look healthier than it is. A missing refund can do the same thing, while a bad subscription end date can spread revenue into months that don’t belong to the customer.
When dbt tests SaaS revenue data at the right points, founders and RevOps teams catch those failures before they reach a board deck or growth dashboard. The goal is not to test every column. It is to protect the revenue rules that change decisions.
How dbt Tests SaaS Revenue Models Start With a Clear Contract
Write down the model grain and business definitions before adding tests. dbt brings software practices such as testing and documentation into analytics work, as outlined in dbt’s product overview. However, a technically valid model can still report the wrong revenue if the team has not agreed on its terms.
Use a representative warehouse structure:
| Model | Grain | Purpose |
|---|---|---|
stg_subscriptions | One subscription version | Cleans billing-system subscription records |
stg_invoice_lines | One invoice line | Stores billed amounts, service periods, and invoice state |
stg_payments | One payment or refund event | Tracks settled cash and refunds |
fct_revenue_schedule | One invoice line per service day | Allocates recognized revenue over time |
fct_mrr_bridge | One account per calendar month | Explains MRR movement |
State these assumptions in model documentation:
account_ididentifies the commercial account. A customer contact may change, but the account remains the reporting entity.customer_ididentifies the billing customer. One account can have multiple customer records in more complex B2B setups.- Subscription periods use a half-open range:
period_startis included andperiod_endis excluded. Therefore, the end date must be later than the start date. - Invoice states are
draft,open,paid,void, anduncollectible. Payment and refund states live in separate fields or tables. - This example reports in USD through
amount_usddecimal fields. Keep the source currency incurrency_codefor auditability. - Booked revenue is contracted value. Billed revenue comes from issued invoice lines. Collected revenue is settled payment cash, less refunds. Recognized revenue is the amount allocated to the service period.
These definitions need finance approval. dbt tests SaaS revenue logic, but they do not establish accounting compliance or replace a finance-led reconciliation.
A revenue schedule can pass every technical test and still be wrong if its billing, collection, and recognition definitions differ from finance’s definitions.
Keep raw sources at the edge of the project with source(), then use ref() for models inside dbt. The dbt platform supports this version-controlled approach, which makes a revenue rule easier to review before it changes production reporting.
Add YAML Tests at the Revenue Grain
Schema tests belong next to the model they protect, usually in a YAML file beside the SQL model. Start with primary keys, required fields, status values, and foreign keys. These checks stop duplicates, orphaned facts, and bad classifications early.
For a recent dbt project, this compact YAML is valid and can be saved as models/marts/fct_revenue_schedule.yml:
{"version": 2, "models": [{"name": "fct_revenue_schedule", "columns": [{"name": "revenue_schedule_key", "data_tests": ["not_null", "unique"]}, {"name": "account_id", "data_tests": [{"relationships": {"arguments": {"to": "ref('dim_accounts')", "field": "account_id"}, "config": {"severity": "error"}}}]}, {"name": "source_invoice_line_id", "data_tests": ["not_null"]}, {"name": "service_date", "data_tests": ["not_null"]}, {"name": "currency_code", "data_tests": [{"accepted_values": {"arguments": {"values": ["USD"]}, "config": {"severity": "error"}}}]}]}]}
The business rule is simple: every daily recognition row must identify one source line, one valid account, one service date, and one permitted reporting currency. A failure means dbt returns the offending rows. unique catches duplicate schedule rows that could overstate recognized revenue. The relationship test catches a revenue fact with no valid account dimension.
Apply the same pattern upstream. In stg_invoice_lines, test invoice_line_id with not_null and unique. Test invoice_status against the approved invoice states. In stg_payments, require payment_id, invoice_id, payment_status, and amount_usd.
Use severity: error for a broken revenue grain, null amount, orphaned revenue fact, or unsupported currency. A production dashboard should not refresh with those defects. Set severity: warn for fields that need review but do not alter a financial total, such as a missing optional coupon code.
Generic tests are the foundation, but they cannot validate rules that compare several columns or models. dbt tests SaaS revenue models best when singular tests cover those higher-order rules.
Use Singular SQL Tests for Revenue Rules
A singular test is a SQL file in the tests directory. It passes when the query returns zero rows. This pattern is useful when a rule spans an invoice, its schedule, and its payment activity. dbt testing guidance also distinguishes these custom checks from reusable schema tests.
Block recognized revenue above billed revenue
Place revenue_recognized_exceeds_billed.sql in tests/. It compares each source invoice line with its total recognition schedule:
with scheduled as (select source_invoice_line_id, sum(recognized_revenue_usd) as recognized_usd from {{ ref('fct_revenue_schedule') }} group by 1), billed as (select invoice_line_id, net_billed_amount_usd from {{ ref('stg_invoice_lines') }}) select s.source_invoice_line_id from scheduled s join billed b on s.source_invoice_line_id = b.invoice_line_id where s.recognized_usd > b.net_billed_amount_usd + 0.01
This should use error severity. Any returned line has more recognized revenue than the net billed amount, allowing a one-cent rounding tolerance. Finance may define exceptions for credits, write-offs, or manual journals. Add those exclusions only after finance confirms them.
Reject invalid subscription periods
Place subscription_period_is_invalid.sql in tests/:
select subscription_id, period_start, period_end from {{ ref('stg_subscriptions') }} where period_start is null or period_end is null or period_end <= period_start
This is an error because the schedule cannot allocate revenue reliably without a valid service range. It also catches same-day periods when the warehouse uses date-level values. If your billing platform allows same-day subscriptions, model timestamps instead of dates and document that exception.
Keep refunds consistent with payments
Place refund_state_matches_amount.sql in tests/:
select payment_id from {{ ref('stg_payments') }} where refunded_amount_usd < 0 or refunded_amount_usd > amount_usd or (refund_status in ('refunded', 'partially_refunded') and refunded_amount_usd <= 0) or (refund_status = 'none' and refunded_amount_usd <> 0)
Use error severity. A refund larger than the settled payment can overstate negative collections. A refunded state with a zero amount creates a different reporting failure, because cash and customer status disagree.
Reconcile the MRR bridge equation
Keep mrr_bridge_does_not_tie.sql in tests/:
select account_id, month_start from {{ ref('fct_mrr_bridge') }} where abs(ending_mrr_usd - (beginning_mrr_usd + new_mrr_usd + expansion_mrr_usd - contraction_mrr_usd - churned_mrr_usd)) > 0.01
This should also be an error. MRR can change sharply for a legitimate annual contract, but every change still needs a bridge category. Use a separate warning test for an unusually large month-over-month MRR movement, such as more than 50%, because that movement may be real.
Run Revenue Tests in CI and Review Failures
Run dbt build in pull-request checks so models and their tests run together. For larger projects, a state-based selection such as state:modified+ keeps CI focused on changed models and downstream dependencies.
Store failed test rows when your warehouse budget permits it. The row sample shortens debugging because the operator sees the invoice, account, or payment that failed. Route error failures to the people who own billing transformations. Route warnings to the data or RevOps queue, then review their row counts over time.
Don’t turn every strange value into an error. Use warnings for new plan names, late-arriving records, or unusual MRR swings. Promote a warning to an error once the team agrees that it should block reporting.
Build Trust One Revenue Rule at a Time
Start with the grain: one schedule row per invoice line and service date, plus one MRR bridge row per account and month. Then protect keys, relationships, dates, refund states, and revenue equations with dbt tests SaaS revenue reporting can depend on.
Next, add the YAML tests and four singular tests to a staging branch, then reconcile a completed month against finance’s approved totals. Add internal links to related guides on SaaS revenue modeling, dbt source freshness, and dbt CI so readers can extend the workflow without guessing.
