dbt models your team will actually trust.

-- models/marts/fct_revenue.sql (before)
-- Grain: invoice_id. Source: raw.invoices joined to raw.refunds.
-- Tests: not_null(invoice_id), unique(invoice_id).
-- The reconciliation between fct_revenue and the GL runs in a
-- spreadsheet. The gap is "explained" by a footnote the day before
-- close. The dbt test suite does not catch the double-count.
select
  i.invoice_id,
  i.amount,
  r.refund_amount
from raw.invoices i
left join raw.refunds r using (invoice_id)

-- models/marts/fct_revenue.sql (after)
{{ config(
  materialized='incremental',
  incremental_strategy='merge',
  unique_key='invoice_id',
  contract={'enforced': True, 'grain': 'one row per invoice_id'},
  on_schema_change='append_new_columns',
  snowflake_warehouse='transforming_xs',
  tags=['finance', 'marts']
) }}

with invoices as (select * from {{ ref('stg_invoices') }}),
     refunds as (select * from {{ ref('stg_refunds') }})
select
  i.invoice_id,
  i.amount                                            as gross_revenue,
  coalesce(r.refund_amount, 0)                        as refund_amount,
  i.amount - coalesce(r.refund_amount, 0)             as net_revenue,
  i.updated_at
from invoices i
left join refunds r using (invoice_id)
{% if is_incremental() %}
  where i.updated_at > (select max(updated_at) from {{ this }})
{% endif %}