Skip to content
Data Engine Thinking Data Engine Thinking Samples

Solution Pattern - SQL Server Family - Combining Multiple Time-Variant Objects

Purpose

Section titled “Purpose”

This Solution Pattern shows how to combine three or more time-variant data objects in SQL by first constructing a unified timeline of all change moments, and then joining each object’s context to that timeline.

Motivation

Section titled “Motivation”

Joining two time-variant objects pairwise works well, but iterating pairwise joins becomes unwieldy as the number of objects grows: the timeline complexity increases with each additional object, and every temporal boundary must be considered for segmentation.

Building the combined timeline first — one grid of time periods per key, covering every change moment of every participating object — turns the problem around. Each object is then joined to the grid with a simple point-in-time predicate, regardless of how many objects participate.

Applicability

Section titled “Applicability”

This pattern applies when combining data from several time-variant objects that share a key — typically multiple Satellites describing the same Hub — for delivery or further processing. It assumes each object carries a from timestamp per change; before timestamps are derived as part of the pattern.

Structure

Section titled “Structure”

The statement is built in three steps:

  • Collect the timeline. The union of every object’s from timestamps per key, together with a zero record per key at a low-end default (1900-01-01), produces the complete set of boundary moments. The zero record ensures the timeline covers the key from the beginning of time, so context that arrives later simply shows as absent in the earliest periods.
  • Close the periods. A LEAD over the collected timestamps per key derives the before timestamp that closes each interval, with the high-end default (9999-12-31) for the open period.
  • Assemble the result. The Hub is joined for the business key, and each Satellite is joined with a point-in-time predicate: the Satellite record whose period covers the grid segment supplies the context for that segment. LEFT OUTER JOINs keep segments where an object has no valid data yet, with NULL attributes for that side.
/*******************************************************************************
 * Data Engine Thinking
 *******************************************************************************
 *
 * Purpose:
 *   - SQL example for Combining multiple time-variant objects.
 *
 * Disclaimer:
 *   - See disclaimer.md in the repository root.
 *
 ******************************************************************************/


-- Creation of a zero key for the date range
SELECT Hub.<Key>, CONVERT(DATETIME2(7), '1900-01-01') AS FROM_TIMESTAMP FROM <Hub> Hub
UNION
SELECT Sat1.<Key>, Sat1.<From Timestamp> AS FROM_TIMESTAMP FROM <Satellite 1> Sat1
UNION
SELECT Sat2.<Key>, Sat2.<From Timestamp> AS FROM_TIMESTAMP FROM <Satellite 2> Sat2




-- Creation of time periods
SELECT
  <Key>,
  FROM_TIMESTAMP,
  LEAD(FROM_TIMESTAMP,1,'9999-12-31') OVER (PARTITION BY <Key> ORDER BY FROM_TIMESTAMP ASC) AS BEFORE_TIMESTAMP
FROM
(
  <results from step 1>
) Timestamps




SELECT
  Hub.<Key>,
  -- Data Item Mappings for the Hub
  Hub.<Business Key>,
  -- Data Item Mappings for Satellite 1
  Sat1.<Column 1>,
  Sat1.<Column 2>,
  Sat1.<Column 3>,
  -- Data Item Mappings for Satellite 2
  Sat2.<Column 1>,
  Sat2.<Column 2>,
  FROM_TIMESTAMP
FROM
(
  <results from step 2>
) TimePeriods
-- Joining the Hub, for the business key
INNER JOIN <Hub> Hub
   ON TimePeriods.<Key> =  Hub.<Key>
-- Joining the context for Satellite 1
LEFT OUTER JOIN <Satellite 1> Sat1
   ON TimePeriods.CUSTOMER_SK = Sat1.[CUSTOMER_SK]
  AND Sat1.<From Timestamp> <= TimePeriods.FROM_TIMESTAMP
  AND Sat1.<Before Timestamp> > TimePeriods.BEFORE_TIMESTAMP
-- Joining the context for Satellite 2
LEFT OUTER JOIN <Satellite 2> Sat1
   ON TimePeriods.CUSTOMER_SK = Sat2.[CUSTOMER_SK]
  AND Sat2.<From Timestamp> <= TimePeriods.FROM_TIMESTAMP
  AND Sat2.<Before Timestamp> > TimePeriods.BEFORE_TIMESTAMP

Implementation guidelines

Section titled “Implementation guidelines”
  • Generate the statement from metadata: the participating objects, their key mapping, and the data item mappings per object are supplied by the solution metadata, so the same template serves any combination.
  • Include a from timestamp source for every participating object in the timeline collection — a boundary missed in step one is a change invisible in the result.
  • Use the standard low-end (1900-01-01) and high-end (9999-12-31) defaults consistently with the rest of the solution.
  • Use closed-open period semantics in the point-in-time predicates, so each grid segment matches exactly one record per object.
  • This construction is the basis of a Point-In-Time (PIT) table: materialising the grid (with the matched keys and timestamps per object) pre-computes the combination and avoids repeating the window functions and range joins at query time. Consider a PIT when the combination is queried frequently or when many objects participate.

Considerations and consequences

Section titled “Considerations and consequences”
  • The grid contains a segment for every change moment of every object, so the result is more granular than any input. With many frequently-changing objects the segment count grows accordingly — performance optimisation becomes critical, which is the main argument for materialising as a PIT.
  • Segments preceding an object’s first record carry NULLs for that object’s attributes; consumers must handle these explicitly.
  • Adjacent segments can carry identical values when a boundary of one object does not change the selected columns of another. Apply compacting afterwards if this redundancy is unwanted.
  • The pattern presumes each object’s own timeline is consistent; defects propagate into the combined result.
Section titled “Related patterns”