Bridging Teradata’s NORMALIZE and the Islands & Gaps Problem — for Bi-Temporal SCD2 Architectures in Modern SQL Platforms
When working with historized data — especially in a bi-temporal SCD2 architecture — we often encounter redundant timelines, also known in SQL as the Islands and Gaps Problem. The issue becomes visible when a single source table contains multiple business aspects (like customer master data) that evolve independently over time.
Let’s take a simple example.
The Problem: Too Many Versions, Too Little Change
Imagine a source system that tracks customers (parties) using Slowly Changing Dimension Type 2 (SCD2) logic. Every time any attribute changes — name, marital status, or even marketing preference — the system creates a new record with a new validity period.
Now suppose that same record also contains address information. Even though the address often remains unchanged, it gets copied into every new version of the customer record.
When we later try to model the address as a separate entity in our integration layer, we face an inflated timeline:
- Dozens of overlapping or consecutive records
- All with the same address
- Only their surrogate validity periods differ
This phenomenon is a classic case of the Islands and Gaps Problem — the challenge of identifying and merging consecutive or overlapping time intervals where values remain constant.
In our SCD2 timelines, each “island” represents a stretch of time where an attribute (like address) didn’t change, even though other attributes triggered new versions.
Before we can load the party_address entity into its own SCD2 table, we need to compress the timeline — merging consecutive records with identical business values.
Enter Teradata’s NORMALIZE Command
Teradata offered an elegant solution for this decades ago. The NORMALIZE command can take overlapping or consecutive time periods and merge them automatically when the data values are the same.
In SQL terms, it turns this:
into this:
— With a single SQL statement.
Teradata’s NORMALIZE effectively automates the Islands and Gaps Problem solution for temporal data: it detects consecutive “islands” of identical attribute values and merges them into a single, continuous validity period.
What NORMALIZE Does Under the Hood
The command essentially groups by key and compares consecutive records based on ordering (e.g. VALID_FROM). If two consecutive records have the same non-key attributes, it merges them by extending the VALID_TO date of the first one.
It’s a powerful temporal compression mechanism — one that’s surprisingly rare in other data platforms.
Reproducing NORMALIZE in Modern Platforms
Most modern cloud data platforms — including Databricks, Snowflake, PostgreSQL, and DuckDB — do not have an equivalent built-in command.
But you can implement the same behavior easily using SQL window functions such as LAG, LEAD, and cumulative grouping logic.
Although not labeled as such, this approach is a modern SQL implementation of the Islands and Gaps Problem — grouping consecutive records with identical attribute values into continuous ranges.
🧩 Step 1 — Detect Changes
SELECT
Party_ID,
Address,
Valid_From,
Valid_To,
CASE
WHEN LAG(Address) OVER (PARTITION BY Party_ID ORDER BY Valid_From) = Address THEN 0
ELSE 1
END AS Change_Flag
FROM party_source;
🧩 Step 2 — Assign Groups of Identical Values
SELECT
Party_ID,
Address,
Valid_From,
Valid_To,
SUM(Change_Flag) OVER (PARTITION BY Party_ID ORDER BY Valid_From
ROWS UNBOUNDED PRECEDING) AS grp
FROM flagged_data;
🧩 Step 3 — Aggregate Consecutive Rows
SELECT
Party_ID,
Address,
MIN(Valid_From) AS Valid_From,
MAX(Valid_To) AS Valid_To
FROM grouped_data
GROUP BY Party_ID, Address, grp
ORDER BY Party_ID, Valid_From;
The result: a compressed timeline, identical to what NORMALIZE produces.
Why This Matters in Bi-Temporal SCD2 Architectures
In a bi-temporal SCD2 design, every entity maintains both a functional (business) and a technical (load or system) timeline. This ensures full reproducibility and auditability — a cornerstone of regulatory-grade data platforms.
However, when multiple aspects of a business object are bundled together in one source, their SCD2 logic is intertwined. Without timeline compression, unrelated changes (like marital status updates) can fragment the functional timeline of other attributes (like address).
By compressing the timeline:
- We create a functional history specific to the entity or attribute
- We drastically reduce redundancy and improve query performance
- We align historization with business semantics rather than source behavior
Although this pattern is also applicable in Data Vault satellites, our reference implementation focuses on bi-temporal SCD2 integration layers — where reproducibility, lineage, and data quality are non-negotiable.
How Other Platforms Compare
Press enter or click to view image in full size
Example Implementations Across Platforms
Snowflake
WITH change_flags AS (
SELECT ,
CASE WHEN LAG(Address) OVER (PARTITION BY Party_ID ORDER BY Valid_From) = Address
THEN 0 ELSE 1 END AS Change_Flag
FROM PARTY_SOURCE
)
SELECT Party_ID, Address,
MIN(Valid_From) AS Valid_From,
MAX(Valid_To) AS Valid_To
FROM (
SELECT ,
SUM(Change_Flag) OVER (PARTITION BY Party_ID ORDER BY Valid_From) AS grp
FROM change_flags
)
GROUP BY Party_ID, Address, grp;
PostgreSQL
WITH cte AS (
SELECT ,
CASE WHEN LAG(Address) OVER w = Address THEN 0 ELSE 1 END AS cf
FROM party_source
WINDOW w AS (PARTITION BY Party_ID ORDER BY Valid_From)
)
SELECT Party_ID, Address,
MIN(Valid_From) AS Valid_From,
MAX(Valid_To) AS Valid_To
FROM (
SELECT ,
SUM(cf) OVER (PARTITION BY Party_ID ORDER BY Valid_From) AS grp
FROM cte
) t
GROUP BY Party_ID, Address, grp
ORDER BY Party_ID, Valid_From;
Databricks SQL
WITH flagged AS (
SELECT ,
CASE WHEN LAG(Address) OVER (PARTITION BY Party_ID ORDER BY Valid_From) = Address THEN 0 ELSE 1 END AS flag
FROM party_source
)
SELECT Party_ID, Address,
MIN(Valid_From) AS Valid_From,
MAX(Valid_To) AS Valid_To
FROM (
SELECT ,
SUM(flag) OVER (PARTITION BY Party_ID ORDER BY Valid_From ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
FROM flagged
)
GROUP BY Party_ID, Address, grp;
DuckDB
WITH changes AS (
SELECT ,
CASE WHEN LAG(Address) OVER (PARTITION BY Party_ID ORDER BY Valid_From) = Address THEN 0 ELSE 1 END AS cf
FROM party_source
)
SELECT Party_ID, Address,
MIN(Valid_From) AS Valid_From,
MAX(Valid_To) AS Valid_To
FROM (
SELECT ,
SUM(cf) OVER (PARTITION BY Party_ID ORDER BY Valid_From) AS grp
FROM changes
)
GROUP BY Party_ID, Address, grp;
Building a Reusable Pattern
You can wrap this logic in:
- A dbt macro (if your platform uses dbt)
- A Spark function (for Databricks)
- A view or SQL UDF (for Postgres, DuckDB, or Snowflake)
- Or define it as a model-driven transformation type, e.g.
normalize_timeline
In a metadata-driven, bi-temporal SCD2 platform, this becomes a composable transformation — converting technical historization into functional history.
Press enter or click to view image in full size
In Summary
By applying this pattern, we bridge a decades-old SQL problem — the Islands and Gaps Problem — with modern metadata-driven data engineering.
It’s a simple but essential step in transforming noisy, versioned timelines into functional histories that truly reflect business reality.
📝 Acknowledgment
A special thanks to Joey Gomes for pointing out that this issue is formally known as the Islands and Gaps Problem. That connection helped make this article clearer for readers approaching the topic from a broader SQL perspective.