On this page
Name the reference version question
The source dates shown in this guide identify documentation checks on 22 October 2026, not original publication dates. Start by naming the fact being attached: a category label, exchange rate, region code, threshold, or other reference value. Record the event key and its time basis separately. “Current reference table” is not a reproducible version unless current means a named snapshot or an explicit retrieval point.
| Join field | Record | Stop if |
|---|---|---|
| Business key | Normalized key and source spelling | Whitespace or case changes meaning |
| Event time | Timestamp and timezone context | Local time is compared to UTC |
| Effective interval | Start, end, inclusivity | Open end has no stated rule |
| Reference version | Snapshot id and retrieval time | Only a file name is retained |
| Tie rule | Newest, highest priority, or hold | Multiple rows can win silently |
| Unmatched rows | Count and examples using synthetic ids | No report exists |
A versioned join needs an explicit answer to “which row was eligible at this event time?” Keep a source key and a join key if they differ. Do not overwrite the raw value with a normalized label before preserving the evidence used to choose it.
Check effective intervals and overlapping versions
Represent each reference row with an effective start, effective end, snapshot identifier, and update observation. Choose whether the start is inclusive and whether an end is exclusive. Adjacent intervals should meet without overlap if that is the owner rule. An overlap is not resolved by sorting unless the rule says which row wins and why.
| Case | Synthetic rows | Finding |
|---|---|---|
| Adjacent | v1 ends at T, v2 starts at T | Boundary rule required |
| Overlap | v1 and v2 both cover T | Ambiguous unless tie rule exists |
| Gap | No row covers T | Reference unavailable at event time |
| Late update | Snapshot changes after event | Retrieval time differs from event time |
| Duplicate key | Two rows share key and interval | Unique-key evidence fails |
The dbt Developer Hub, (opens in a new tab) documentation distinguishes a unique key from a changed-at signal. Neither field alone proves that an effective interval is complete. Retain the owner’s interval policy and report rows that violate it rather than selecting a convenient answer.
Record the derivation from snapshot to joined output
Treat the reference snapshot, join activity, and output table as separate artifacts. A useful record includes snapshot id, source URI or identifier, retrieval timestamp, query or filter version, key normalization rule, interval policy, and row-level match status. If a manual override exists, record it as an activity and keep the automatic result.
- Identify the input event artifact and its time basis.
- Identify the reference snapshot and its update evidence.
- Record the key normalization and interval predicate.
- Count zero, one, many, and held matches.
- Store the output value with the selected reference id.
- Link corrections or overrides to a separate activity.
Run a synthetic join matrix
Synthetic versioned reference fixture
event_key | event_time | reference_key | result
fixture-a | 2026-01-10T12:00Z | R1 | v1
fixture-a | 2026-02-10T12:00Z | R1 | hold:overlap
fixture-b | 2026-02-10T12:00Z | R2 | hold:gap
fixture-c | 2026-02-10T12:00Z | R3 | hold:unmatched
reference: R1 v1 [2026-01-01,2026-02-15)
reference: R1 v2 [2026-02-01,2026-03-01)
reference: R2 no row for February
reference: R3 duplicate key withheld| Assertion | Expected evidence | Do not do |
|---|---|---|
| One match | Reference id and interval recorded | Keep only display value |
| Overlap | Both candidate ids and tie rule status | Choose newest by habit |
| Gap | No reference row and owner question | Fill from adjacent version |
| Unmatched | Synthetic key and count | Publish a null as if resolved |
| Re-run | Same snapshot and predicate reproduce result | Use moving current data |
In this fixture, R1 v1 covers 1 February through 15 February and R1 v2 starts on 1 February, so the 10 February event is covered by both half-open intervals. hold:overlap is therefore the expected result. If v2 started on 15 February instead, the same event would select v1 under this interval convention.
The fixture is deliberately invented. Add a boundary event exactly at an interval end, a key with case and whitespace differences, and a reference update observed after the event. The expected result should be a status plus evidence, not a guessed label.
Keep reference joins separate from business conclusions
A versioned join can show which reference row was eligible and where evidence is missing. It cannot establish that a reference value is correct, that an event is authorized, or that a downstream decision is valid. Keep reference stewardship and domain interpretation outside the join report.
Pair this guide with the dataset version change log for release history and the data provenance log for artifact lineage. After a supported non-health scope, authority to describe the collection, and a controlled reference packet are established, the qualified appraisal request path may be a preparation step. It does not run joins, choose reference values, create a quote, match a buyer, or grant a license.
Takeaways
- Define the event time, reference key, effective interval, and version together.
- Report overlap, gaps, duplicates, and unmatched keys instead of hiding them.
- Retain snapshot, predicate, normalization, and selected reference id.
- Model corrections as separate provenance activities.
- Do not treat a successful join as proof of reference correctness.