Backdated corrections, hard deletes, and a point-in-time join

Two failures with two causes.

The transient pending state is a grain problem. A 04:00 snapshot samples whatever the source looks like at 04:00; if check_cols includes status, each captured transition becomes a row. Three rows for one plan change means the snapshot caught pending, then confirmed, then a correction on consecutive days. Fix it before the snapshot: build a stg_subscription_daily model that reduces the feed to one row per subscriber per day - row_number() over (partition by subscriber_id, date(updated_at) order by updated_at desc) filtered to 1. The snapshot can then only ever record one state per day. Document that the SCD grain is daily: 'what plan was in force on 14 March' is answerable, 'how many minutes was it pending' is not.

Backdated corrections break the timestamp strategy because dbt expects updated_at to move forward; it compares the incoming timestamp to the stored dbt_valid_from and will not apply a row that is not newer, so a correction dated five days ago is dropped or applied out of order, producing exactly the backwards flip you see. Options: filter the feed so updated_at never regresses (cheap, but discards real corrections); snapshot on a monotonic ingestion column instead (correct ordering, but dbt_valid_from becomes load time rather than business effective time); or keep business time and run a scoped dbt snapshot --full-refresh for the affected subscribers to rebuild that history.

The trade-off is business effective date versus monotonicity. Use the ingestion-time variant if downstream only asks 'which plan at month start'; use the scoped rebuild if the effective date is contractual.

A snapshot is only aware of rows its query returns. When a subscriber disappears from the source, nothing arrives to close the open record, so dbt_valid_to stays null and the row counts as active forever - that is your 4% over-count.

Three approaches, increasing in effort:

1. Use the snapshot's hard-delete handling if your dbt version supports it (the hard_deletes: invalidate configuration arrived in dbt 1.9). dbt compares the incoming key set against open records and closes the ones that vanished, setting dbt_valid_to plus a deleted flag column. Least code, but it changes the snapshot's output shape, so every downstream join must tolerate the flag and the new closing semantics.
2. Force a closing row through the normal mechanism. Build a key spine of current source keys unioned with the snapshot's open keys, left join the source, and emit a tombstone row with status = 'deleted' and a fresh timestamp for missing keys. With check_cols covering status, the next run closes the old record and opens a deleted one. Fully portable, more SQL, but it records a business-meaningful value instead of a metadata flag.
3. Prefer an event if the source has one - cancelled_at or a cancellation events table. Modelling the state change directly beats inferring it from absence, and it survives a source that sometimes truncates and reloads.

Here, with 12,000 rows already stale and a business concept (cancellation) that clearly exists, I would take option 3 if the feed exposes it and option 2 otherwise; option 1 is attractive only if version support and downstream tolerance are both confirmed. Whichever you choose, add the invariant test: open snapshot records per day must equal active keys in the source. That single check catches both missed deletes and spurious closes.

The join must be half-open on both ends:

select ...
from fct_subscription_months m
join snap_subscriptions s
  on  m.subscriber_id = s.subscriber_id
  and m.month_start >= cast(s.dbt_valid_from as date)
  and m.month_start <  cast(coalesce(s.dbt_valid_to, '9999-12-31') as date)

Why < on the upper bound: dbt sets the superseded record's dbt_valid_to equal to the new record's dbt_valid_from. Using <= therefore matches two snapshot rows on the handover date and double-counts that month. Cast both sides explicitly - dbt_valid_from is a timestamp, often with a timezone, while month_start is a date, and an implicit cast at a day boundary can slide a record into the wrong month.

Three checks before trusting the number:

• Exactly one snapshot row per subscriber per month: a unique_combination_of_columns test on (subscriber_id, month_start) in the joined model. That one test catches boundary bugs, duplicate snapshot rows, and overlapping validity ranges simultaneously.
• Coverage: subscribers whose first dbt_valid_from is after month_start have genuinely unknown plan history. Surface them with a left join and a plan_status of unknown rather than letting an inner join silently drop billed months.
• Sub-day changes: at monthly grain, two changes in one month collapse to whichever record covers month_start. That is defensible if the definition is 'plan in force at month start' - write that sentence into the model description, because the next analyst will otherwise assume a month-average and produce a different total from the same tables.