Slowly Changing Dimensions in Talend: Type 2 Loads That Survive Reruns

Every warehouse eventually needs history: what was this customer's segment at the time of the order? That is a Type 2 slowly changing dimension, and it is the single most common place we see Talend jobs quietly corrupt data. Not because the concept is hard, but because reruns, late-arriving rows, and NULL handling break naive implementations.

This tutorial is the pattern we deploy on client estates, with the failure modes that motivated each rule.

The target shape

A Type 2 dimension row carries the business key plus versioning columns:

Column	Purpose
dim_sk	Surrogate key, unique per version
customer_id	Business (natural) key
attributes	The tracked columns
row_hash	Hash of tracked attributes
valid_from / valid_to	Effective dating
is_current	Convenience flag for the latest version

Two conventions worth fixing before you write a line of Talend:

• Half-open intervals. valid_from <= t < valid_to. Closed intervals force you to subtract a second somewhere, and that arithmetic is always wrong at least once.
• A sentinel high date, not NULL, for the open version — 9999-12-31. Joins with BETWEEN and NULL do not do what you want, and a sentinel keeps the temporal join a single predicate.

Option A: tDBSCD (and when it is fine)

Talend ships SCD components — tMysqlSCD, tPostgresqlSCD, tOracleSCD and friends — with a GUI editor where you drag attributes into Type 0/1/2/3 buckets. They work, and for a dimension of a few hundred thousand rows against a supported RDBMS they are the fastest thing to build.

Where they stop being a good answer:

• They issue row-by-row DML. At a few million rows on a cloud warehouse with per-statement latency, that is hours, not minutes.
• There is no SCD component for Snowflake, BigQuery, Databricks or Redshift in the way there is for classic RDBMS targets, and those are where most new dimensions live.
• The logic lives in a GUI dialog, so it is hard to code review and hard to unit test.

Use them for legacy on-prem targets. For anything cloud-columnar, build the load explicitly.

Option B: hash compare + set-based MERGE

The shape of the job:

tDBInput (source snapshot / CDC delta)
  --Main--> tMap  (compute row_hash, normalise NULLs, add load_ts)
  --Main--> bulk load --> stg_customer_dim
  --OnSubjobOk--> tDBRow (MERGE closing + opening versions)
  --OnSubjobOk--> tDBRow (assertions)

All the heavy lifting happens in the warehouse. Talend's job is to land a clean, deduplicated, hashed staging table and then orchestrate one deterministic SQL step.

Computing a change hash you can trust

In tMap, build a single expression over the tracked attributes:

StringHandling.MD5(
    StringHandling.DOWNCASE(
        (row1.segment  == null ? "\u0001" : row1.segment.trim())  + "|" +
        (row1.tier     == null ? "\u0001" : row1.tier.trim())     + "|" +
        (row1.country  == null ? "\u0001" : row1.country.trim())  + "|" +
        (row1.credit_limit == null ? "\u0001" : row1.credit_limit.toPlainString())
    )
)

Four details that matter more than the hash algorithm:

1. A NULL sentinel that cannot appear in data. Concatenating a raw NULL as an empty string makes ("A", null) and ("A", "") hash identically — a real change you will never detect. Use a control character.
2. A delimiter, for the same reason: ("AB","C") and ("A","BC") must not collide.
3. Canonical formatting for numbers and dates. BigDecimal.toPlainString() rather than toString() (scientific notation for small values), and ISO-8601 with an explicit zone for timestamps. A trailing-zero scale change is not a business change; do not open a new version for it.
4. Hash only the tracked columns. Include last_updated_by or an ETL timestamp and every run produces a new version. We have seen dimensions grow 40x from exactly this.

Compute the same hash the same way when the row is first inserted, and store it. Comparing a stored hash to a freshly computed hash is one integer comparison per key instead of a wide column-by-column IS DISTINCT FROM chain.

Deduplicating the delta first

If your source is CDC rather than a full snapshot, one run can contain several versions of the same key. Feed staging through a tSortRow on (business_key, source_ts) and then a tAggregateRow / tUniqRow — or handle it in the MERGE with a window function. What you must not do is let two rows for the same key hit the MERGE unordered; most warehouses will either error on non-deterministic matches or pick one arbitrarily.

If intermediate versions are themselves history you need, do not collapse them — load them in timestamp order and let effective dating record each transition.

The MERGE

The portable two-statement version (Snowflake syntax; the same shape works on BigQuery, Databricks and Postgres 15+):

-- 1. Close versions whose tracked attributes changed
UPDATE customer_dim d
SET    valid_to   = s.effective_ts,
       is_current = FALSE
FROM   stg_customer_dim s
WHERE  d.customer_id = s.customer_id
  AND  d.is_current
  AND  d.row_hash <> s.row_hash
  AND  s.effective_ts > d.valid_from;

-- 2. Insert new and changed versions
INSERT INTO customer_dim
      (customer_id, segment, tier, country, credit_limit,
       row_hash, valid_from, valid_to, is_current)
SELECT s.customer_id, s.segment, s.tier, s.country, s.credit_limit,
       s.row_hash, s.effective_ts, '9999-12-31'::timestamp, TRUE
FROM   stg_customer_dim s
LEFT   JOIN customer_dim d
         ON d.customer_id = s.customer_id
        AND d.is_current
WHERE  d.customer_id IS NULL          -- brand new key
   OR  d.row_hash   <> s.row_hash;    -- genuine change

Run both inside one transaction. If the target does not support transactional DDL/DML across statements, write to a clone and swap.

Why this is idempotent

Rerun the same staging batch and nothing happens: step 1 finds no is_current row whose hash differs, step 2's LEFT JOIN matches an identical hash and filters everything out. That property is worth protecting, because reruns are not exceptional — a failed downstream step, a redeployed Kubernetes CronJob or an operator clicking retry will all produce one.

Test it explicitly. Our standard smoke test runs the job twice against a fixed fixture and asserts the row count is unchanged.

Late-arriving and out-of-order rows

The naive close-and-insert above assumes each new row is newer than the current version. When it is not — a backfill, a replayed Kafka partition, a source system with clock skew — you need to insert between existing versions:

-- find the version whose interval contains the late row
SELECT dim_sk, valid_from, valid_to
FROM   customer_dim
WHERE  customer_id = :key
  AND  :effective_ts >= valid_from
  AND  :effective_ts <  valid_to;

Split that interval: set its valid_to to the late row's effective_ts, then insert the late version running from effective_ts to the old valid_to. is_current is untouched unless the late row lands in the open interval.

Our pragmatic rule: route rows where effective_ts < (SELECT MAX(valid_from) ...) down a separate, lower-volume "late" path with the interval-splitting logic, and keep the hot path simple. Mixing both into one MERGE produces SQL nobody can review.

Surrogate keys

Let the warehouse generate them — IDENTITY, a sequence, or GENERATED ALWAYS AS IDENTITY. Do not use tMap with a Numeric.sequence() counter for a persisted key: it resets per job execution, and two concurrent runs will collide.

If you need deterministic keys (handy for reproducible test data), hash business_key || valid_from into a 128-bit value rather than incrementing a counter.

Assertions worth failing the job on

Add a final tDBRow / tAssertCatcher step that hard-fails on:

• More than one is_current = TRUE row per business key.
• Overlapping intervals: for any key, valid_to of a version greater than valid_from of the next.
• Gaps, if your model does not allow them.
• valid_from >= valid_to on any row.
• Version count growing faster than a sane threshold per run — the classic symptom of a hash that includes an ETL timestamp.

SELECT customer_id, COUNT(*) AS open_versions
FROM   customer_dim
WHERE  is_current
GROUP  BY customer_id
HAVING COUNT(*) > 1;

A dimension that silently doubles is far more expensive to unwind six months later than a job that fails tonight.

Types you can mix in the same table

Type 2 is rarely applied to every column. Corrections — a misspelled name, a fixed postcode — should be Type 1: overwrite in place, across all versions, and exclude the column from the hash. Keep the column lists explicit in one place in the job (a context variable or a small metadata table), because a column silently added to the source and picked up by a SELECT * will otherwise start generating versions.

Deletes

If the source hard-deletes, a snapshot load can detect it with an anti-join; CDC gives you the event directly. Either way, do not delete the dimension row — close it (valid_to = delete_ts, is_current = FALSE) and, if downstream needs to distinguish, set a deleted_flag. Facts already pointing at that surrogate key must keep resolving.

Checklist

• Half-open intervals, sentinel high date.
• Hash only tracked columns, with NULL sentinel and delimiter, canonical number/date formatting.
• Deduplicate the delta before the MERGE.
• Close-then-insert in one transaction.
• Rerun the fixture twice in CI and assert stability.
• Assert one open version per key, no overlaps, on every run.

If you have a Type 2 dimension that nobody trusts anymore, the fastest route back is usually to rebuild history from the source's change log with this pattern and diff it against the current table — we do that regularly, and the diff itself is normally where the interesting business questions surface.