Dual SCD2 gives us powerful history. But let’s be honest: it’s not always easy to query.
Joining facts and dimensions across time validity is notoriously complex. The SQL quickly turns into a jungle of BETWEEN conditions, overlapping ranges, and awkward joins. Performance suffers, and developers lose confidence.
That’s where Point-in-Time (PIT) helper tables come in.
What are PIT tables?
PIT tables are precomputed helper structures that link a fact record to the correct dimension record at a specific point in time. Instead of evaluating ranges at query time, you join directly through the PIT.
The effect is dramatic:
- Queries shrink from dozens of lines of time logic to a handful of joins.
- Performance improves, because the heavy lifting has been pre-computed.
- Results are more reliable, because the time logic is standardized once.
Internal and external value
- Internal (within the platform): PIT tables simplify joins between the historisation and integration layers. Pipelines run faster, and logic is reused consistently.
- External (for consumers): PIT tables underpin the exposure views that provide reproducible snapshots, functional cuts of data, or “as-of” reporting.
Relationship to snapshots
Snapshots try to solve the “what did it look like” question, but they miss context and validity. PIT + dual SCD2 solves both: you get true history and fast access.
(For a deeper dive: Technical deep dive: dual SCD2, snapshots, and PIT tables.)
In practice, PIT tables are the glue between “beautifully historized” and “practically usable.”