Skip to content
Data Engine Thinking Data Engine Thinking Samples

Solution Pattern - SQL Server Family - Snapshot-Based PIT

Purpose

Section titled “Purpose”

This Solution Pattern shows how to create a snapshot-based Point-In-Time (PIT) table: one record per key for each chosen snapshot moment, carrying pointers to the Satellite records in effect at that moment.

Motivation

Section titled “Motivation”

Not every consumer needs the full change history. Reporting is often periodic — daily, weekly, end-of-month — and only the state as at those moments matters. A snapshot-based PIT resolves, for each snapshot moment, which record of each Satellite applies, producing a compact structure that is far smaller than the full timeline while still answering the questions consumers actually ask.

Applicability

Section titled “Applicability”

This pattern applies when periodic states are sufficient for delivery, or when the full timeline-based PIT is too large to maintain economically. It assumes the Satellites carry state time periods (closed-open) and, for bitemporal selection, inscription timestamps.

Structure

Section titled “Structure”

The statement resolves the pointers for one snapshot moment, defined by two parameters:

  • The assertion snapshot timestamp filters on the assertion timeline — only records inscribed up to that moment participate. Defaulting it to the current time (SYSUTCDATETIME()) means “as currently known”; an earlier value reproduces what was known at that time.
  • The state snapshot timestamp selects on the state timeline — for each Satellite, the record whose state period covers the snapshot moment.
  • Per key, the result carries the snapshot timestamp, the Hub’s surrogate key, and each Satellite’s record locator (surrogate key, inscription timestamp, inscription record identifier, state from timestamp), plus optional frequently-used columns.

Running the statement for successive snapshot moments and appending the results builds up the PIT.

/*******************************************************************************
 * Data Engine Thinking
 *******************************************************************************
 *
 * Purpose:
 *   - Snapshot-based PIT: one record per key per snapshot moment.
 *
 * Disclaimer:
 *   - See disclaimer.md in the repository root.
 *
 ******************************************************************************/


DECLARE @AssertionSnapshotTimestamp DATETIME2(7) = SYSUTCDATETIME();
DECLARE @StateSnapshotTimestamp DATETIME2(7) = CAST('2025-02-25' AS DATETIME2(7));


SELECT
  -- New snapshot timestamp
  @StateSnapshotTimestamp AS [Snapshot_Timestamp],
  -- Hub surrogate key(s)
  Hub.[Customer_Surrogate_Key]  AS [Hub_Customer_Customer_Surrogate_Key],
  -- Pointer Satellite 1
  Sat1.[Customer_Surrogate_Key] AS [Sat_Customer_Details_Sales_Customer_Surrogate_Key],
  Sat1.[Inscription_Timestamp]  AS [Sat_Customer_Details_Sales_Inscription_Timestamp],
  Sat1.[Inscription_Record_Id]  AS [Sat_Customer_Details_Sales_Inscription_Record_Id],
  Sat1.[State_From_Timestamp]   AS [Sat_Customer_Details_Sales_From_Timestamp],
  -- Pointer Satellite 2
  Sat2.[Customer_Surrogate_Key] AS [Sat_Customer_Details_Contact_Customer_Surrogate_Key],
  Sat2.[Inscription_Timestamp]  AS [Sat_Customer_Details_Contact_Inscription_Timestamp],
  Sat2.[Inscription_Record_Id]  AS [Sat_Customer_Details_Contact_Inscription_Record_Id],
  Sat2.[State_From_Timestamp]   AS [Sat_Customer_Details_Contact_From_Timestamp],
  -- Optional columns
  Hub.[Customer_Code]
FROM [Hub_Customer] Hub
LEFT JOIN [Sat_Customer_Details_Sales] sat1
ON
  -- Assertion timeline
  sat1.[Inscription_Timestamp] <= @AssertionSnapshotTimestamp
  -- State timeline
  AND sat1.[State_From_Timestamp]   <= @StateSnapshotTimestamp
  AND sat1.[State_Before_Timestamp] >  @StateSnapshotTimestamp
LEFT JOIN [Sat_Customer_Details_Contact] sat2
ON
  -- Assertion timeline
  sat2.[Inscription_Timestamp] <= @AssertionSnapshotTimestamp
  -- State timeline
  AND sat2.[State_From_Timestamp]   <= @StateSnapshotTimestamp
  AND sat2.[State_Before_Timestamp] >  @StateSnapshotTimestamp

Implementation guidelines

Section titled “Implementation guidelines”
  • Generate the statement from metadata, with the snapshot timestamps as parameters; the same template then serves every snapshot cadence.
  • Keep the two snapshot parameters distinct: the assertion timestamp answers “as known when?”, the state timestamp answers “as applicable when?”. Setting both is what makes the selection bitemporal.
  • Use period-covering predicates with closed-open semantics (from <= snapshot AND before > snapshot), so each Satellite contributes exactly one record per key per snapshot.
  • Append snapshots in load order and index on key and snapshot timestamp; consumers filter on the snapshot timestamp to select their reporting moment.
  • Keep the PIT pointer-based, as with the timeline-based variant; the payload is attached at delivery time via equi-joins.

Considerations and consequences

Section titled “Considerations and consequences”
  • Only the chosen snapshot moments are queryable. Questions about states between snapshots cannot be answered from the snapshot PIT — that is the trade-off against the timeline-based PIT, and the reason the two variants coexist.
  • Late-arriving data inscribed after a snapshot was taken is not reflected in that snapshot. When this matters, recompute recent snapshots (the assertion parameter makes any snapshot reproducible).
  • Size grows linearly with keys × snapshot moments, predictably and independently of how often the underlying data changes.
  • As with any PIT, the structure is derived: rebuildable from the integration layer, refreshed on a schedule aligned with the snapshot cadence.
Section titled “Related patterns”