Build something that is not there yet
Preserve business-effective history and what was known at each reporting cutoff
- A backdated correction must change today's reading of the past, yet a report issued earlier has to stay reproducible as it was published.
- Finance or regulators ask both what was in force on a date and what was known at a reporting cutoff, and a single history table answers only one of them.
- Corrections arrive late or overlap an earlier version, and nobody can say which version applied or when it became known.
Keep each business version with its own valid interval and recorded interval, never overwriting history, and answer bounded questions by effective date and by knowledge cutoff, with correction lineage, flagged unresolved overlaps and stable reruns.
Use this Recipe
Use the Vibedata Recipe `bitemporal-business-history` at https://github.com/accelerate-data/vibedata-cookbooks/blob/main/recipes/bitemporal-business-history/recipe.md Read the Recipe and execute it in the context of the current Intent.
Recipe id bitemporal-business-history · Canonical definition
Verified by
What has to be observably true before this Recipe is finished.
- The approved contract defines the entity, version key, tracked attribute, interval ends, precision, time zone, what a cutoff includes, each consumer's grain, the dates and cutoffs answered, and how corrections, overlaps and unusable changes are treated.
- Source evidence shows a recorded time carried by each change. A change without one is listed with its reason, or fails the run naming it when that is approved, and never gets a load, run or inferred time.
- Every usable change exists as its own version with both intervals. After a correction the superseded version remains with its own keys and lineage, and no version is overwritten, merged or deleted.
- Both consumers' published answers equal expected answers derived independently from sources and approved rules, fixed before the consumers exist, for a grid of dates and cutoffs. A correction recorded after an early cutoff leaves that cutoff's answer unchanged and changes a later cutoff's.
- Both axes are pinned at their boundaries by expected results that differ from their neighbours: an effective date at a version's start, just before its end and at its end, and a cutoff just before, at and after a correction's recording and a series' first recording, per the approved rules.
- Deliberately wrong consumers, such as one ignoring the cutoff, one ignoring the effective date and one using the wrong end bound, each fail at least one expected case against the same expectations.
- A date or cutoff with no applicable answer gets the approved disposition, and an unknown at that cutoff stays distinguishable from a date no version ever covered; a missing result never reads as zero or as a broken build.
- An overlap the correction rules cannot resolve is flagged with the versions and recorded times involved, and the contested dates carry no chosen winner; any wider withholding than the contested dates is the approved policy and is stated, and dates outside it keep answering.
- Every change delivered is accounted for once, as a version, an approved retraction, a set-aside with exactly one reason or an approved failure naming it; counts reconcile at each stage.
- A rerun on unchanged input leaves versions, keys and intervals identical, and a larger input adding a later correction changes only what that correction governs, with earlier versions and keys unchanged.
- The answers, failures and flagged exceptions hold when read back from the built objects on the actual supported target, and the fixture returns to its starting state after reset.
Recipe promptThe task specification the agent reads. Reference only — it is not what you copy.
Build a two-axis version history for <tracked_attribute> of <entity_name> and two bounded consumers for the questions required in this Intent: what applied on <effective_date> and what was known at <reporting_cutoff>. Inspect approved requirements and source evidence first, and confirm that the source records a time for its changes before building anything. Keep each version with its own business-valid interval and recorded interval, keep correction lineage, never overwrite or delete a version, and expose overlaps the rules cannot resolve. Keep Domain sources read-only; use a labelled synthetic fixture or an approved writable fixture with reset when the Domain has no such feed. Prove the discriminating case where a backdated correction changes the corrected period for later cutoffs while an earlier cutoff keeps the earlier answer, both axes at their boundaries, unresolved overlaps, and an unchanged rerun that leaves the ledger identical.
Agent guidanceHow the agent approaches the work, and what it will not do.
Inherit the Intent's repository, platform, Domain and sources. Inspect approved requirements and source evidence, and decide from the data whether each change carries a recorded time; stop and report when it does not. Use Ask first only for unresolved semantics, and record the entity, version key, intervals, cutoff meaning and dispositions before building. Check installed dbt and adapter versions. Derive the version key from the change's own identity so it survives rebuilds and reordering. Author expected answers by hand from the fixture before the consumers exist. Build the version ledger once with both axes, resolve corrections only by the agreed rule, and have both consumers share one resolution rule so they cannot disagree. Run the rerun and larger-input comparisons and the wrong-consumer checks against the same expectations, read the outputs back and verify fixture reset. Report missing recorded time, missing history or unexecuted required cases as incomplete verification.
Composes
- profiling-source-data
- authoring-dbt-project-artifact
- generating-dbt-model
- dbt-unit-testing
- running-dbt-in-sandbox
- verifying
Asks first
Semantic decisions the Intent cannot supply. Never context Studio already holds.
- If unresolved, which entity, attribute, changes and analytical questions must this history answer, at which effective dates and cutoffs, and which source supplies each change's identity and recorded time, meaning when the source recorded it or when this organisation received it?
- If unspecified, how is a correction identified and resolved: by an explicit link, by period or by another rule, may it cover only part of the version it corrects, and what happens with competing corrections of one version?
- If unresolved, what do the consumers return for dates contested by an unresolvable overlap, and what do they return for a date with no applicable version or a cutoff before the first recording?
- If unspecified, what do the interval ends include, what precision and time zone apply, what does a cutoff include, and which knowledge does the effective-date question read when no cutoff is given?
- If unresolved, how are unusable changes, retractions, source deletions and reappearing entities recorded as new versions or flags, given that the ledger keeps every earlier version, and what do the consumers return for them?
Guardrails
- Never reconstruct what was known from a present-state snapshot, a load stamp or a file time, and never backdate an observation; periods before the first recording stay unknown at earlier cutoffs.
- Valid time and recorded time are distinct axes. Do not collapse them into one validity interval, and do not let an observation boundary stand in for when a change took effect.
- Never overwrite, merge or delete a version to apply a correction. Do not infer a correction from equal periods or recording order unless that rule was approved.
- Do not pick a winner for an unresolvable overlap, deduplicate it away or drop the contested rows through an inner join.
- Keep Domain sources read-only. Mutate only an independently authored synthetic fixture or an approved writable fixture with a verified reset, and never mix synthetic rows into observed results.
- Execute on the actual supported Intent target and supplied sandbox without switching engines; support here covers duckdb_local only.
- Keep the deliverable to the version ledger, its two consumers and necessary checks. Single-axis dimension history, converting an existing slowly changing procedure and source ingestion belong to their own Recipes.
- A build that compiles or reruns cleanly does not prove the outcome; the changed case, boundary results, wrong-consumer failures and reconciliation are required execution evidence.