Skip to content
Data Engine Thinking Data Engine Thinking Samples

Granularity and Redundancy

Granularity determines the level of detail captured in snapshots, while redundancy refers to the repetition of unchanged data across consecutive snapshots. Balancing these factors is crucial for efficient storage and meaningful analysis.

Key considerations:

  • Snapshot frequency impacts both granularity and storage requirements
  • Redundant data can be eliminated through compacting techniques
  • Finer granularity enables more detailed temporal analysis
  • Coarser granularity reduces storage but may lose temporal precision
/*******************************************************************************
 * Data Engine Thinking
 *******************************************************************************
 *
 * Purpose:
 *   - SQL example for Granularity and redundancy.
 *
 * Disclaimer:
 *   - See disclaimer.md in the repository root.
 *
 ******************************************************************************/


WITH [Sample] AS
(
  SELECT '100\@|' AS SURROGATE_KEY,
         NULL AS COLUMN_VALUE,
         CONVERT(DATETIME2(7),'1900-01-01') AS FROM_TIMESTAMP,
         CONVERT(DATETIME2(7),'2025-02-01') AS BEFORE_TIMESTAMP
  UNION
  SELECT '100\@|' AS SURROGATE_KEY,
         'A' AS COLUMN_VALUE,
         CONVERT(DATETIME2(7),'2025-02-01') AS FROM_TIMESTAMP,
         CONVERT(DATETIME2(7),'2025-02-15') AS BEFORE_TIMESTAMP
  UNION
  SELECT '100\@|' AS SURROGATE_KEY,
         'B' AS COLUMN_VALUE,
         CONVERT(DATETIME2(7),'2025-02-15') AS FROM_TIMESTAMP,
         CONVERT(DATETIME2(7),'2025-03-20') AS BEFORE_TIMESTAMP
  UNION
  SELECT '100\@|' AS SURROGATE_KEY,
         'C' AS COLUMN_VALUE,
         CONVERT(DATETIME2(7),'2025-03-20') AS FROM_TIMESTAMP,
         CONVERT(DATETIME2(7),'2025-09-20') AS BEFORE_TIMESTAMP
  UNION
  SELECT '100\@|' AS SURROGATE_KEY,
         'D' AS COLUMN_VALUE,
         CONVERT(DATETIME2(7),'2025-09-20') AS FROM_TIMESTAMP,
         CONVERT(DATETIME2(7),'9999-12-31') AS BEFORE_TIMESTAMP
)
,[Dates] AS
(
    -- Create a list of 12 months (end-of-month timestamps)
    SELECT [Snapshot Timestamp] = EOMONTH(CONVERT(DATETIME2(7),'2025-01-31'))
    UNION ALL
    SELECT [Snapshot Timestamp] = EOMONTH(DATEADD(MONTH, 1, [Snapshot Timestamp]))
    FROM DATES
    WHERE [Snapshot Timestamp] < CONVERT(DATETIME2(7),'2025-12-31')
)
SELECT *
FROM [Sample]
JOIN [Dates]
  ON [Snapshot Timestamp] >= FROM_TIMESTAMP
 AND [Snapshot Timestamp] <  BEFORE_TIMESTAMP