Operational systems love fields that hold only the current value: a Salesforce account's Last_Investment_Policy_Review__c, a subscription's current_plan, a deal's stage. The field is overwritten in place every time the real-world thing changes. By the time the data lands in your deduped model, the history is gone — you see today's value and nothing else.

This page documents the pattern for recovering that lost history as nexus events, and the reusable snapshot_diff_events macro that implements it.

The Trait-vs-State Gap

You could model the current value as a trait on the entity (investment_policy_last_reviewed = '2025-11-03'). That answers "what is it now?" but not "what happened, and when?" A trait is a single overwritten slot; it has no timeline and no trigger_event_id. You cannot build a state machine (completed → renewal_overdue) on top of a value that only ever reflects the present.

States want events underneath them. Each transition should point back to a concrete thing that happened. The gap is: the source field changes in place, so there is no event — only a moving trait.

Append-Only ELT Makes Point-in-Time Reconstructable

The escape hatch is the ELT landing table. Loaders like Fivetran, Airbyte, and our own Nango syncs are append-only at the raw layer: every sync writes a fresh copy of each source row, tagged with an ingestion timestamp (_ingested_at). The deduped model (ref('salesforce_accounts')) keeps only the latest version — but source('salesforce', 'salesforce_accounts') still holds every version ever ingested.

So even though the operational field is overwritten in place, the raw history preserves each distinct value it held across snapshots. Diffing that append-only history recovers the timeline:

flowchart LR
  subgraph raw["source(...) — append-only, all _ingested_at versions"]
    s1["snapshot 2026-02-24<br/>review = 2025-11-03"]
    s2["snapshot 2026-03-10<br/>review = 2025-11-03"]
    s3["snapshot 2026-05-02<br/>review = 2026-04-28"]
  end
  raw -->|diff distinct values| ev
  subgraph ev["nexus events (one per distinct value)"]
    e1["reviewed @ 2025-11-03"]
    e2["reviewed @ 2026-04-28"]
  end
  ev -->|interpretation layer| st["state: completed → renewal_overdue"]

Why Not a dbt Snapshot?

A dbt snapshot solves a similar problem, but only going forward — it starts capturing changes the day you deploy it. Snapshot-diff works against the raw landing history that ELT already accumulated, so it can backfill retroactively: every change observed since the source first synced becomes an event on the first run. The trade-off is that you can only see as far back as ELT does (more on that below). Use a dbt snapshot when you want a clean SCD2 of the source table; use this pattern when you want nexus events out of a field's history, with backfill.

Events-First Principle

Emit events for what happened, not for what it means. A review date appearing in the field is an observable fact: "this household's IPS was reviewed on 2025-11-03." Whether that makes the household completed, renewal_overdue, or anything else is interpretation and belongs in a state model. Keep the event layer dumb and durable; put the business rules (rolling 12-month window, synthetic expiry transitions) in the state on top. See Trait-Derived States for the same separation applied to trait events.

The occurred_at Caveat: Exact vs. Observed

The honesty of occurred_at depends on what the field contains.

• The value is itself a business date (preferred). When the field is "last reviewed on 2025-11-03", the value is the real-world timestamp. occurred_at = timestamp(value) is exact — even for changes that happened before ELT started syncing, because the date is carried in the value.

• The value is not a date (a stage name, a plan tier). Then the best occurred_at available is the first snapshot in which we observed the value — observation time, not change time. This is honest but lag-bounded: the true change happened somewhere in the interval (previous snapshot, this snapshot]. With daily syncs the lag is ≤ 1 day; the very first sync is the floor — anything that changed before it collapses onto that first observation.

Either way _ingested_at records the first snapshot the value appeared in — our "best understanding of when we learned of it" — so the observation lineage is never lost.

The Macro: snapshot_diff_events

nexus.snapshot_diff_events(...) lives in the dbt-nexus package (macros/sources/snapshot_diff_events.sql). It reads an append-only relation, diffs a tracked field, and emits the standard nexus intermediate-event column shape (event_id, occurred_at, event_type, event_name, event_description, source, value, value_unit, _ingested_at, _processed_at) plus any domain columns you carry along.

Parameters

Parameter	Required	Default	Purpose
source_relation	yes	—	Append-only relation with ALL snapshot versions — pass source(...), not a deduped ref(...).
entity_key	yes	—	SQL expression for the entity identifier.
value_expr	yes	—	SQL expression for the tracked field (NULL/blank values are dropped).
event_name	yes	—	The nexus event_name (also event_type unless overridden).
event_type	no	event_name	The nexus event_type.
source	no	'unknown'	The nexus source string.
observed_at_column	no	'_ingested_at'	The append-only snapshot timestamp column.
occurred_at_mode	no	'value'	'value' → timestamp(value) (exact); 'observed' → first-observed snapshot (lag-bounded).
grain	no	'distinct_value'	'distinct_value' → one event per distinct (entity, value); 'change' → one event per value change.
event_description	no	"<name>: <entity>"	SQL expression for the human-readable description (can reference carried additional_columns).
value_column	no	NULL	SQL expression for the numeric value column.
value_unit_column	no	NULL	SQL expression for the value_unit column.
additional_columns	no	{}	{alias: sql_expr} of extra domain columns to carry onto each event (aggregated with max()).

distinct_value vs change grain

• distinct_value (default) — one event per distinct (entity, value) the field ever held. Re-using an earlier value does not create a new event, so the result is idempotent on the value itself. This matches the IPS case: a review date is a real event regardless of how many snapshots carry it. Works with occurred_at_mode = 'value'.

• change — one event per value change in the per-entity snapshot timeline. Distinct from distinct_value only when a value repeats after changing away and back (A → B → A yields two A events). Because a repeated value has no distinct business date to anchor on, change requires occurred_at_mode = 'observed' (the macro raises a compiler error otherwise).

Idempotency and Edge Cases

• Idempotent event_id. Hashed via create_nexus_id('event', [entity, value, event_name]) in distinct_value grain (plus a change index in change grain). Re-runs are stable; each distinct value gets its own durable event.
• First snapshot already has a value. It still becomes an event — its occurred_at is the value's business date (value mode) or the first observation (observed mode). We cannot see further back than ELT does.
• NULL / blank values are excluded — an empty field is not "what happened."
• Multiple changes over time each produce their own event.

Worked Example: Austin Wealth IPS Reviews

Austin Wealth Management's Salesforce account carries Last_Investment_Policy_Review__c, a single date overwritten in place each time a household's Investment Policy Statement is reviewed. The deduped salesforce_accounts model keeps only the latest date, so on its own the field is point-in-time blind. Diffing the append-only raw source recovers every distinct review date a household has ever had.

The implementation in salesforce_account_investment_policy_statement_reviewed_events.sql reads ALL _ingested_at versions of source('salesforce', 'salesforce_accounts'), parses the date, and emits one investment_policy_statement_reviewed event per distinct (account_id, review_date). The value is the review date, so occurred_at = timestamp(review_date) is exact even though observation only began on 2026-02-24. Expressed through the macro:

{{ config(
    enabled=var('nexus', {}).get('sources', {}).get('salesforce', {}).get('enabled', false),
    materialized='table',
    tags=['nexus', 'salesforce', 'intermediate', 'events']
) }}

{{ nexus.snapshot_diff_events(
    source_relation=source('salesforce', 'salesforce_accounts'),
    entity_key="json_extract_scalar(_raw_record, '$.Id')",
    value_expr="safe.parse_date('%Y-%m-%d', nullif(trim(json_extract_scalar(_raw_record, '$.Last_Investment_Policy_Review__c')), ''))",
    event_name='investment_policy_statement_reviewed',
    source='salesforce',
    occurred_at_mode='value',
    grain='distinct_value',
    event_description="concat('Investment Policy Statement reviewed: ', coalesce(account_name, entity_key))",
    additional_columns={
        'account_id': "json_extract_scalar(_raw_record, '$.Id')",
        'account_name': "json_extract_scalar(_raw_record, '$.Name')"
    }
) }}

Those events then feed the investment_policy_statement_review_status state model, which interprets them into never_completed → completed → renewal_overdue using a rolling 12-month window. The completed → renewal_overdue transition can flip with no source change at all — purely as the window slides past the review date — exactly like the canonical synthetic "stale" transition in Trait-Derived States. The events are "what happened"; the state is the interpretation.