History with SCD Type 2

In Nekt, enable SCD Type 2 on the source table. Each change becomes a new row with _available_from (validity start), _available_until (end, null on the current version) and _deleted. History is valid from the day it was turned on: each entity's first version is its state on that day, not at creation.

Each version may get a new surrogate code. In a versioned dimension from a brokerage, the codes changed with every version of the same account. Identify the entity by the stable business key (subscription id, account code, deal id) and use the surrogate code only to tie a fact to the version valid on that date.

The state on a date is the version where _available_from is less than or equal to the date and _available_until is null or greater than it. To join a fact to a dimension (the advisor on a deposit, the owner of a won deal, the plan on an invoice), use the fact's date, not today's version.

For day-to-day use, publish a view with only the current version (_available_until null and _deleted false) and keep the full timeline in a separate table. Querying the versions table without a filter counts each entity several times.

_available_from and _available_until are UTC instants. Convert to the business time zone (America/Sao_Paulo) before cutting by day or month, otherwise changes made at night fall on the next day and the month-end snapshot picks the wrong version.

SCD writes one version per sync that found a change, so the same entity can have several versions on the same day. In the daily snapshot, use each entity's last version of each day. In the month-end snapshot, use the version valid at the last instant of the month, with the same cut-off for every entity.

A new version may only have changed the billing period, the latest invoice or another technical field. To count changes of status, owner or plan, compare the field with the previous version. Control columns such as _nekt_sync_at never enter the comparison, and _available_from says when the sync saw the change, not when it happened at the source.

Some APIs keep only a few days: Stripe events for about 30, email analytics for about 45, database collections with a TTL. A full sync rewrites the table with the current window and deletes the rest. Accumulate into a table of your own by merge, keyed by entity and day, that only grows, and never swap that merge for an overwrite.

A SQL transformation rebuilds the whole table on every run and keeps no past. To snapshot a calculated number (portfolio, balance, daily MRR), use a notebook that reads its own output, keeps previous days and replaces only the current day. That way, running twice on the same day does not duplicate the snapshot.

A closed month's result is read from the frozen snapshot, not recalculated with today's rule or records. Later corrections go in as an adjustment in the month they were found. In one real base, a qualification recalculated on every run made a month's number shrink after it had been presented.