Back to Home
Data ScienceJuly 9, 2026

Enforcing Scd Type 2 Integrity For Real Time Feature Store Feeds

S

Written by

Sage Stream

Why I got stuck: “the feature went backwards”

I was building a real-time feature feed for an AI model—think “latest customer attributes right now”—and I assumed my joins were safe. Then a weird thing happened during evaluation: a downstream prediction stream started behaving as if the customer’s data time-traveled.

What I found was surprisingly specific: my “dimension” table (slow-changing attributes like customer tier, region, eligibility) was implemented as SCD Type 2 (slowly changing dimension with history). But my real-time pipeline wasn’t enforcing the rules that guarantee Type 2 integrity. Result: some feature rows were pulled from an older “version” of the same entity even when newer versions existed—so features went backwards in time.

This post is the exact guardrail I built: a set of SQL checks that detect SCD Type 2 integrity violations and block feature materialization when they occur.

The exact failure mode (SCD Type 2, but with broken validity windows)

In SCD Type 2, each entity (say customer_id) has multiple versions. Each version is valid for a time range:

  • valid_from: when this version starts
  • valid_to: when this version ends (often NULL for the current version)

Correct invariant (Type 2): For a given customer_id:

  1. Valid ranges must not overlap.
  2. There must be exactly one “current” row (valid_to IS NULL).
  3. When you order by valid_from, each next version should start after the previous one ends.

In my case, the pipeline had no enforcement, so invalid history quietly produced wrong “latest” rows.

Minimal schema I used

I used two tables:

  • customer_dim_scd2: history table
  • events_stream: real-time events that we enrich with customer features
-- customer_dim_scd2 (history table)
-- customer_id: entity key
-- tier: some attribute used as a feature
-- valid_from, valid_to: SCD2 validity window (valid_to NULL => current)

-- events_stream (real-time events)
-- event_id: unique event key
-- customer_id: join key
-- event_ts: timestamp when the event happened

The enrichment query looked like a typical “as-of join”:

  • for each event at event_ts, pick the dimension row where valid_from <= event_ts < valid_to
  • if valid_to is NULL, treat it as “infinity”

That’s correct only if the dimension history satisfies the invariants.

Step 1: I wrote an “SCD Type 2 integrity” SQL test suite

I focused on checks that are:

  • fast enough to run per batch
  • strict enough to catch the time-travel bug
  • deterministic (so it doesn’t hide behind probabilistic sampling)

1) Detect overlapping validity windows

Overlaps mean two versions are simultaneously valid—this breaks “as-of” logic.

-- Overlap detection for a single customer_id
-- Two rows overlap if their ranges intersect.

WITH ordered AS (
  SELECT
    customer_id,
    valid_from,
    COALESCE(valid_to, TIMESTAMP '9999-12-31') AS valid_to,
    tier,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from) AS rn
  FROM customer_dim_scd2
),
pairs AS (
  SELECT
    a.customer_id,
    a.rn AS rn_a,
    a.valid_from AS a_valid_from,
    a.valid_to   AS a_valid_to,
    b.rn AS rn_b,
    b.valid_from AS b_valid_from,
    b.valid_to   AS b_valid_to
  FROM ordered a
  JOIN ordered b
    ON a.customer_id = b.customer_id
   AND a.rn < b.rn
)
SELECT *
FROM pairs
WHERE
  -- intersection: a_start < b_end AND b_start < a_end
  a_valid_from < b_valid_to
  AND b_valid_from < a_valid_to
ORDER BY customer_id, rn_a, rn_b;

What happens when I run this?

  • If it returns rows, at least one pair of versions overlaps for the same customer_id.
  • That’s enough to block materialization because an as-of join can pick the wrong row (or produce duplicates).

2) Detect multiple “current” rows

If more than one row has valid_to IS NULL, then “latest” is ambiguous.

SELECT
  customer_id,
  COUNT(*) AS current_row_count
FROM customer_dim_scd2
WHERE valid_to IS NULL
GROUP BY customer_id
HAVING COUNT(*) <> 1
ORDER BY customer_id;

3) Detect invalid ordering / back-to-back gaps and reversals

Even without overlaps, you can have reversed windows or nonsensical values.

SELECT
  customer_id,
  valid_from,
  valid_to
FROM (
  SELECT
    customer_id,
    valid_from,
    valid_to,
    LAG(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) AS prev_valid_from,
    LAG(COALESCE(valid_to, TIMESTAMP '9999-12-31')) OVER (PARTITION BY customer_id ORDER BY valid_from) AS prev_valid_to
  FROM customer_dim_scd2
) t
WHERE
  -- next start should not be before previous end
  valid_from < prev_valid_to
ORDER BY customer_id, valid_from;

Step 2: I wired these tests into a single “gate” query

Instead of running three separate ad-hoc queries in a notebook, I consolidated them into a single gate that returns is_valid = true/false plus human-readable reasons.

Here’s a working pattern for Postgres. (The SQL is standard-ish; adapt timestamps if needed.)

WITH
overlaps AS (
  WITH ordered AS (
    SELECT
      customer_id,
      valid_from,
      COALESCE(valid_to, TIMESTAMP '9999-12-31') AS valid_to,
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from) AS rn
    FROM customer_dim_scd2
  ),
  pairs AS (
    SELECT
      a.customer_id,
      a.rn AS rn_a,
      b.rn AS rn_b
    FROM ordered a
    JOIN ordered b
      ON a.customer_id = b.customer_id
     AND a.rn < b.rn
  )
  SELECT
    o.customer_id
  FROM (
    SELECT
      a.customer_id,
      a.rn_a,
      a.rn_b,
      a_valid_from,
      a_valid_to,
      b_valid_from,
      b_valid_to
    FROM (
      SELECT
        a.customer_id,
        a.rn AS rn_a,
        a.valid_from AS a_valid_from,
        a.valid_to AS a_valid_to,
        b.rn AS rn_b,
        b.valid_from AS b_valid_from,
        b.valid_to AS b_valid_to
      FROM ordered a
      JOIN ordered b
        ON a.customer_id = b.customer_id
       AND a.rn < b.rn
    ) x
  ) z
  WHERE z.a_valid_from < z.b_valid_to
    AND z.b_valid_from < z.a_valid_to
),
bad_currents AS (
  SELECT customer_id
  FROM customer_dim_scd2
  WHERE valid_to IS NULL
  GROUP BY customer_id
  HAVING COUNT(*) <> 1
),
bad_ordering AS (
  SELECT customer_id
  FROM (
    SELECT
      customer_id,
      valid_from,
      LAG(COALESCE(valid_to, TIMESTAMP '9999-12-31')) OVER (PARTITION BY customer_id ORDER BY valid_from) AS prev_valid_to
    FROM customer_dim_scd2
  ) t
  WHERE prev_valid_to IS NOT NULL
    AND valid_from < prev_valid_to
)
SELECT
  CASE
    WHEN EXISTS (SELECT 1 FROM overlaps)
      OR EXISTS (SELECT 1 FROM bad_currents)
      OR EXISTS (SELECT 1 FROM bad_ordering)
    THEN false
    ELSE true
  END AS is_valid,

  ARRAY_REMOVE(ARRAY[
    CASE WHEN EXISTS (SELECT 1 FROM overlaps) THEN 'overlapping_validity_windows' END,
    CASE WHEN EXISTS (SELECT 1 FROM bad_currents) THEN 'multiple_current_rows' END,
    CASE WHEN EXISTS (SELECT 1 FROM bad_ordering) THEN 'bad_temporal_ordering' END
  ], NULL) AS failure_reasons;

Why this worked for my pipeline: it made “Type 2 integrity” a single decision point. I could stop the real-time enrichment/materalization immediately instead of letting the wrong feature values propagate.

Step 3: enforce the gate in real-time feature materialization

I treated the integrity check as a blocking precondition for any batch (or micro-batch) job that populates the feature store.

Example: materialize features only if dimension history is valid

Assuming:

  • events arrive with event_ts
  • features depend on tier at that timestamp
-- Pseudocode-ish SQL pattern:
-- 1) run the gate
-- 2) if valid, do the as-of join enrichment

WITH gate AS (
  SELECT
    CASE
      WHEN EXISTS (SELECT 1 FROM customer_dim_scd2 WHERE valid_to IS NULL GROUP BY customer_id HAVING COUNT(*) <> 1)
      THEN false ELSE true
    END AS is_valid
),
enriched AS (
  SELECT
    e.event_id,
    e.event_ts,
    d.tier
  FROM events_stream e
  JOIN customer_dim_scd2 d
    ON d.customer_id = e.customer_id
   AND d.valid_from <= e.event_ts
   AND e.event_ts < COALESCE(d.valid_to, TIMESTAMP '9999-12-31')
  CROSS JOIN gate g
  WHERE g.is_valid
)
SELECT *
FROM enriched;

What happens when dimension history is broken?

  • The WHERE g.is_valid clause prevents enrichment from producing any output.
  • In my setup, that also prevented publishing partial/wrong features to downstream model consumers.

Step 4: catch it earlier with a “duplicate as-of match” check

Even with good windows, the as-of join can still produce multiple matches if overlaps exist.

So I added a “join cardinality guard”:

SELECT
  e.event_id,
  COUNT(*) AS match_count
FROM events_stream e
JOIN customer_dim_scd2 d
  ON d.customer_id = e.customer_id
 AND d.valid_from <= e.event_ts
 AND e.event_ts < COALESCE(d.valid_to, TIMESTAMP '9999-12-31')
GROUP BY e.event_id
HAVING COUNT(*) <> 1
ORDER BY match_count DESC, e.event_id;

Interpretation:

  • match_count = 1 is correct.
  • match_count = 0 means there’s no valid dimension row for that event timestamp.
  • match_count > 1 means overlaps exist (or history is inconsistent).

This check gave me a direct signal tied to the real-world join behavior, not just the raw SCD fields.

Step 5: (Practical part) I turned the SQL into an automated blocker

I ran the gate using a tiny Python script. The script:

  1. executes the gate query
  2. if is_valid is false, raises an exception (stopping the pipeline run)
  3. otherwise proceeds to materialize features
import psycopg2
from psycopg2.extras import RealDictCursor

GATE_SQL = """
WITH
overlaps AS (
  WITH ordered AS (
    SELECT
      customer_id,
      valid_from,
      COALESCE(valid_to, TIMESTAMP '9999-12-31') AS valid_to,
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from) AS rn
    FROM customer_dim_scd2
  )
  SELECT a.customer_id
  FROM (
    SELECT
      a.customer_id,
      a.valid_from AS a_valid_from,
      a.valid_to   AS a_valid_to,
      b.valid_from AS b_valid_from,
      b.valid_to   AS b_valid_to
    FROM ordered a
    JOIN ordered b
      ON a.customer_id = b.customer_id
     AND a.rn < b.rn
  ) x
  WHERE x.a_valid_from < x.b_valid_to
    AND x.b_valid_from < x.a_valid_to
  GROUP BY customer_id
),
bad_currents AS (
  SELECT customer_id
  FROM customer_dim_scd2
  WHERE valid_to IS NULL
  GROUP BY customer_id
  HAVING COUNT(*) <> 1
),
bad_ordering AS (
  SELECT customer_id
  FROM (
    SELECT
      customer_id,
      valid_from,
      LAG(COALESCE(valid_to, TIMESTAMP '9999-12-31')) OVER (PARTITION BY customer_id ORDER BY valid_from) AS prev_valid_to
    FROM customer_dim_scd2
  ) t
  WHERE prev_valid_to IS NOT NULL
    AND valid_from < prev_valid_to
)
SELECT
  CASE
    WHEN EXISTS (SELECT 1 FROM overlaps)
      OR EXISTS (SELECT 1 FROM bad_currents)
      OR EXISTS (SELECT 1 FROM bad_ordering)
    THEN false
    ELSE true
  END AS is_valid,

  ARRAY_REMOVE(ARRAY[
    CASE WHEN EXISTS (SELECT 1 FROM overlaps) THEN 'overlapping_validity_windows' END,
    CASE WHEN EXISTS (SELECT 1 FROM bad_currents) THEN 'multiple_current_rows' END,
    CASE WHEN EXISTS (SELECT 1 FROM bad_ordering) THEN 'bad_temporal_ordering' END
  ], NULL) AS failure_reasons;
"""

def gate_check_and_materialize():
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        dbname="mydb",
        user="myuser",
        password="mypassword",
    )
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(GATE_SQL)
            row = cur.fetchone()

            if not row["is_valid"]:
                reasons = row["failure_reasons"]
                raise RuntimeError(f"SCD2 gate failed: {reasons}")

            # If valid, proceed with materialization.
            # Replace this with your actual feature-store write logic.
            cur.execute("""
                CREATE TEMP TABLE enriched_features AS
                SELECT
                  e.event_id,
                  e.event_ts,
                  d.tier
                FROM events_stream e
                JOIN customer_dim_scd2 d
                  ON d.customer_id = e.customer_id
                 AND d.valid_from <= e.event_ts
                 AND e.event_ts < COALESCE(d.valid_to, TIMESTAMP '9999-12-31');
            """)
            conn.commit()

            print("Gate passed; enriched features materialized.")
    finally:
        conn.close()

if __name__ == "__main__":
    gate_check_and_materialize()

Why I like this approach: the “go/no-go” decision is based on data rules, not on heuristics like “it looks fine” or “the last run didn’t fail.”

What I learned about AI Data Governance from this

This wasn’t just a pipeline bug; it was an AI Data Governance control. The governance issue was: my model’s training/serving feature semantics depended on the correctness of SCD Type 2 history, but nothing guaranteed that correctness.

By treating SCD Type 2 integrity as a first-class requirement—and blocking feature materialization when it fails—I prevented silent data corruption that would otherwise show up much later as unexplained model degradation.

In short: I learned that for real-time AI systems, governance must include temporal correctness checks (validity windows, current-row uniqueness, and join cardinality), not just freshness and schema validation.