Solution Pattern - SQL Server Family - Timeline-Based PIT

Purpose

This Solution Pattern shows how to create a timeline-based Point-In-Time (PIT) table: one record per key for every change moment across the Satellites of a Hub, each carrying pointers to the Satellite records in effect during that period.

Motivation

Combining several time-variant objects requires aligning their timelines — work that grows with every object and every query that needs the combination. A PIT table performs this alignment once: it records, per key and per time period, exactly which record of each Satellite applies. Downstream deliveries (dimensions, facts) then attach the context with simple equi-joins.

The timeline-based variant covers the complete timeline: a record for every change moment of every participating Satellite, so any point in time can be queried at full fidelity.

Applicability

This pattern applies when the combination of a Hub’s Satellites is queried often enough to justify materialising the alignment, and consumers need the full change history rather than periodic snapshots. It assumes the Satellites carry state time periods (closed-open).

Structure

The statement builds the timeline and resolves the pointers:

Timeline. A zero record per key at the low-end default (1900-01-01) is combined with the union of every Satellite’s state from timestamps; a LEAD per key closes each period (high-end default 9999-12-31).
Pointer resolution. The Hub is joined for the key, and each Satellite is joined with a period-covering predicate, so each PIT record points to the Satellite record in effect during that period.
Pointers, not payload. Per Satellite, the PIT carries the record locator — surrogate key, inscription timestamp, inscription record identifier, and state from timestamp — rather than the descriptive attributes themselves. Frequently used columns (such as the business key) can optionally be included.

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

SELECT
  -- New timeline
  TimePeriods.FROM_TIMESTAMP,
  TimePeriods.BEFORE_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
(
  -- Creation of time periods
  SELECT
    [Customer_Surrogate_Key],
    FROM_TIMESTAMP,
    LEAD(FROM_TIMESTAMP,1,'9999-12-31')
        OVER (PARTITION BY [Customer_Surrogate_Key]
        ORDER BY FROM_TIMESTAMP ASC)
      AS BEFORE_TIMESTAMP
  FROM
  (
    -- Creation of a zero key for the timeline
    SELECT
      Hub.[Customer_Surrogate_Key],
      CONVERT(DATETIME2(7), '1900-01-01') AS FROM_TIMESTAMP
    FROM [Hub_Customer] Hub
    UNION
    -- Combined set of all available from timestamp values
    SELECT Sat1.[Customer_Surrogate_Key], Sat1.[State_From_Timestamp] AS FROM_TIMESTAMP
    FROM [Sat_Customer_Details_Sales] Sat1
    UNION
    SELECT Sat2.[Customer_Surrogate_Key], Sat2.[State_From_Timestamp] AS FROM_TIMESTAMP
    FROM [Sat_Customer_Details_Contact] Sat2
   ) Timestamps
) TimePeriods
-- Joining the Hub
INNER JOIN [Hub_Customer] Hub
  ON TimePeriods.[Customer_Surrogate_Key] =  Hub.[Customer_Surrogate_Key]
-- Joining Satellite 1
LEFT JOIN [Sat_Customer_Details_Sales] Sat1
  ON TimePeriods.[Customer_Surrogate_Key] = Sat1.[Customer_Surrogate_Key]
  AND Sat1.[State_From_Timestamp] <= TimePeriods.FROM_TIMESTAMP
  AND Sat1.[State_Before_Timestamp] > TimePeriods.BEFORE_TIMESTAMP
-- Joining Satellite 2
LEFT JOIN [Sat_Customer_Details_Contact] Sat2
  ON TimePeriods.[Customer_Surrogate_Key] = Sat2.[Customer_Surrogate_Key]
  AND Sat2.[State_From_Timestamp] <= TimePeriods.FROM_TIMESTAMP
  AND Sat2.[State_Before_Timestamp] > TimePeriods.BEFORE_TIMESTAMP

Implementation guidelines

Generate the statement from metadata: the Hub, the participating Satellites, and their locator columns are known in the solution metadata, so the same template serves every PIT.
Keep the PIT narrow: pointers and timestamps. The payload stays in the Satellites and is attached at delivery time via equi-joins — see Creating a Dimension from a PIT.
Include a from timestamp source for every participating Satellite in the timeline union; a missed source means invisible changes.
Use the solution’s standard low-end and high-end defaults and closed-open period semantics throughout.
Index the PIT on the key and period columns; its whole purpose is fast lookups.

Considerations and consequences

The PIT contains a record for every change moment of every participating Satellite — full fidelity at the cost of volume. Where periodic states suffice, a snapshot-based PIT is substantially smaller.
The PIT is derived data: it can be truncated and rebuilt from the integration layer at any time, and must be refreshed when the participating Satellites load.
Adding or removing a participating Satellite changes the timeline (and the PIT’s shape); regenerate from metadata rather than altering in place.