State history

Enable Nekt's SCD Type 2 on tables that change in place: billing subscriptions and items, CRM customers, deals and companies, product accounts and plans. Every change becomes a version with _available_from, _available_until and _deleted. What came before does not come back: for MRR, rebuild from invoices; for owner and stage, from the CRM property history.

Stripe events are available for about 30 to 40 days, MongoDB collections with a TTL delete documents (about 45 days in a real case) and Amplitude charts and metric spreadsheets return only an overwritten 30-day window. Accumulate into your own table by day and id, with merge, reprocessing the last few days on each run so a failure heals itself.

A SQL transformation on Nekt overwrites the whole table on every run. To snapshot daily MRR, books or aggregated usage, use a notebook with mode merge, or a query that reads its own output and keeps all previous days, replacing only the current day.

Before writing, the routine refuses an empty result or one much smaller than the previous one, unless explicitly allowed. Before a full sync of a large source, take a frozen copy: a full sync that failed halfway once wiped almost five months of events from a table.

In billing, most new versions only advance the billing cycle or swap the latest invoice. To count a change in status, plan, quantity, price or end-of-cycle cancellation notice (cancel_at_period_end), compare the business field with the previous version and count only when it changed.

The state on day D is the version valid at D+1 00:00 Brasília time, that is, D+1 03:00 UTC. With several versions on the same day, the last one wins. Run the session in UTC and convert only at the cutoff, once.

For books, NDR and commissions, the owner in a past month is the owner valid on an agreed day (for example, the 5th, giving the team days 1 to 4 to transfer accounts). Changes after the cutoff are flagged as mid-month transfers, without changing the base owner. The current month uses the latest owner.

When SCD was not turned on in time, recover stage, amount and owner from HubSpot's property history (propertiesWithHistory, one entry per change with value, date and source). If the connector does not bring the property you need, a temporary script fetches it from the API, flagged as temporary and with a removal date.

A past rebuilt from invoices, logs or a backfill is flagged with its origin. Models and indicators use only what was available at the end of the previous period (point-in-time). In a real case, contract attributes rebuilt by a backfill contaminated a churn model and had to be removed.

A closed month is read from the frozen snapshot. A historical reclassification is done once, documented with date and reason, and is not repeated on every run. A later correction goes in as an adjustment in the month it was found.