--- id: casos/scd-type-2 area: Data usage: common effort: medium grain: Entity x version (_available_from, _available_until) or entity x snapshot day --- # History with SCD Type 2 How each customer, contract or deal looked on any past date, with the owner, plan and status of that time, without today's state rewriting the past. ## Executive summary **For:** Companies that need to answer how something looked on a past date, with sources that only show today's state. **What it solves** - A past month's number does not change when a record changes today. - Each fact is joined to the owner, plan and status that were valid on its date. - Data the source deletes after a few days is kept. **Why it matters:** Billing, CRM and the product database only keep current state: the old plan disappears, the deal owner is today's owner and yesterday's status is overwritten. Without history there is no retroactive MRR, no commission by the owner of the month, no portfolio on a past date and no trend for a health score. And history only exists from the day it is turned on, so every day without it is a day lost. **Business questions it answers** - What were this customer's plan and status at the end of March? - Who owned each deal when it was won? - How many contracts or properties were active on the last day of each month? - What segment was the company in when it bought? - What changed on this record in the last week, and when? **What you need** - The sources connected to Nekt, with history (SCD Type 2) turned on for state tables - The stable business key of each entity - The list of fields whose changes matter to the business **What you get** - A versions table per entity, with validity start and end - A view with only the current version for day-to-day use - End-of-day or month-end snapshots for metrics - An AI agent that answers how something looked on any date ## Detailed ### The problem Most sources only return the current picture. When a customer changes plan, owner or status, the old value is overwritten and every report about the past starts using the new value. In one real base, changing a company's segment, account executive or monthly value rewrote all of its past sales. Other sources keep only 30 or 45 days, and every full sync shrinks the past. ### Business rules 1. **Turn history on at the source, now.** 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. (Avoid: Waiting until someone asks for history to turn it on.) 2. **The entity is the business key, not the surrogate.** 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. (Avoid: Counting accounts by the surrogate code.) 3. **Questions about the past use the interval.** 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. (Avoid: Joining March deposits to the customer's current advisor.) 4. **Current means _available_until is null.** 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. (Avoid: Summing the versions table as if it were the master record.) 5. **Validity in UTC, cut-off in the business time zone.** _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. (Avoid: Truncating the UTC timestamp to a date.) 6. **Several versions in a day: the last one counts.** 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. (Avoid: Using the first version of the day, or each entity's last row in the month.) 7. **A new version is not a business event.** 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. (Avoid: Counting versions as status changes.) 8. **Short-retention sources need an accumulator.** 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. (Avoid: Treating the raw table as a historical archive.) 9. **Snapshots of derived tables need a notebook.** 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. (Avoid: Writing the day's snapshot by append, with no key.) 10. **A closed month is not recalculated.** 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. (Avoid: Recalculating every month on every run.) ### Which kind of table answers the question Classify the source before answering anything about time. - **Current record** → Only answers about today (`overwrites the past`) - **Versions (SCD Type 2)** → State on any date (`_available_from <= date < _available_until`) - **Daily snapshot** → State at the end of each day (`1 row per entity and day`) - **Month-end snapshot** → Frozen close (`last instant of the month, local time`) - **Source event history** → When each change happened (`propertieswithhistory (HubSpot), stage_history (Bitrix24)`) - **Last known observation** → Each entity at its most recent date (`report the lag between entities`) ### Definitions | Metric | Definition | |---|---| | Version | entity row valid from _available_from (inclusive) to _available_until (exclusive); the current one has a null _available_until | | State on date D | the entity's version where _available_from <= end of day D and (_available_until null or greater), with D in the business time zone | | Daily snapshot | each entity's last version on each day, 1 row per entity and day | | Month-end snapshot | each entity's state at the last instant of the month, in the business time zone, with the same cut-off for all | | Start of history | the table's smallest _available_from; before it there is only state rebuilt from events, invoices or stage history | | Field change | a version where the field's value differs from its value in the same entity's previous version | | Broken validity | an entity with no current version, or with two current versions at the same time | | Business date | the date the fact happened (creation, win, cancellation); never _nekt_sync_at or _available_from | ### Pitfalls - **An overwrite erased the series.** A daily snapshot was overwritten by mistake and almost three weeks of history had to be rebuilt. How to avoid: Add a guard before writing: refuse empty output and abort if any day already written disappears. - **The full sync ate the archive.** A full sync of an analytics source rewrote the table with the current window and deleted more than half of the rows at once. How to avoid: Accumulate into a table of your own by merge and never use the raw table as an archive. - **Broken validity at the source.** A versioned dimension from a partner had accounts with no version flagged as current, one of them with dozens of versions. How to avoid: Test for one current version per entity every day and use the date interval, not the source's current flag. - **The past with today's owner.** Commissions joined deals won in past months to the current owner, and people who changed portfolios took credit for sales they did not make. How to avoid: Join on the owner valid on the win date. - **The first version is not the beginning.** Each entity's first version was the one from the day history was turned on. Read as if it held since creation, it assigned today's segment and plan to old sales. How to avoid: Record when history starts and rebuild the earlier period from events, invoices or stage history. - **Backfill with the load date.** A backfill wrote contract attributes with the load date and contaminated the expansion calculation for earlier months. How to avoid: Flag backfill rows and never use the load date as the business date. Validated on real data: In Nekt's own Stripe account, about 7 in 10 subscriptions already existed before the first history record, so the first version is not the start of the subscription. Every subscription has exactly one current version and the intervals are continuous, with no gaps or overlaps. And only about 1 in 5 new versions changed the status: most changed the latest invoice or the billing period, which is why counting versions is not counting status changes. ### Acceptance test - [ ] Each non-deleted entity has exactly one version with a null _available_until. - [ ] Each entity's intervals are continuous: one version's end is the next one's start, with no gaps or overlaps. - [ ] The current view has the same number of entities and the same values as the source's current record. - [ ] Running the pipeline again does not change the snapshot of any closed month. - [ ] Running the daily snapshot twice on the same day does not create a new row. ### Context document ```text History and dates - Before answering about time, classify the table: current record, versions (SCD Type 2), daily snapshot, monthly snapshot, event history or last known observation. Do not call a current record history. - SCD Type 2 tables have _available_from, _available_until and _deleted. Current version: _available_until null and _deleted false. - State on a date: _available_from <= date and (_available_until null or > date). Convert from UTC to America/Sao_Paulo before cutting by day or month. - Join each fact to the version valid on the fact's date (owner, plan, status, segment), never to today's. - The entity is the business key. Surrogate codes change with every version. - A new version is not a status change: compare the field with the previous version. - _nekt_sync_at and _available_from are not business dates. Use the event date. - History starts at the table's smallest _available_from. Before that, say there is no history or use the event source indicated. - Closed months come from the frozen snapshot. Do not recalculate them with current records. Client exceptions: - : history for turned on ; before that, use . - : month-end snapshot cut off at