Transformations
Slowly Changing Dimension
Keeps a dimension table's history: Type 1 overwrites old values with the latest, Type 2 adds a dated version row for every change and marks which version is current.
When to use it
Use it when customer, product or employee attributes change over time and reports must either show only today's values (Type 1) or what was true on any date (Type 2). Feed it the current dimension followed by the new extract, e.g. through Merge Rows.
In a pipeline
File InputMerge RowsSlowly Changing DimensionSQL Server Output
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| SCD type | select | — | 2 |
| Business key columns | select | Yes | Select key columns… |
| Columns to track for changes | select | Yes | Select columns… |
| Effective from column | text | — | Effective From |
| Effective to column | text | — | Effective To |
| Current flag column | text | — | Is Current |
Example
A customer moves (Type 2)
- SCD type=Type 2 — keep history as version rows
- Business key columns=CustomerId
- Columns to track for changes=City
| CustomerId | City | Effective From |
|---|---|---|
| C-7 | Leeds | 2026-01-01 |
| C-9 | York | 2026-01-01 |
| C-7 | Hull | 2026-08-20 |
| C-9 | York | 2026-08-20 |
| CustomerId | City | Effective From | Effective To | Is Current |
|---|---|---|---|---|
| C-7 | Leeds | 2026-01-01 | 2026-08-20 | N |
| C-7 | Hull | 2026-08-20 | — | Y |
| C-9 | York | 2026-01-01 | — | Y |
York did not change, so C-9's second row is folded into its existing version; the node reports how many rows were folded.
Worth knowing
- Rows of a key are taken in Effective From order when every one has a date, otherwise in the order they arrive — put the existing dimension before the new extract.
- A version without an Effective From date starts today (YYYY-MM-DD).
- Values are compared as text, except that numbers written differently (40 and 40.0) count as the same.
- Type 1 keeps every row and only rewrites the tracked columns with the latest values of the key; add Deduplicate by Key afterwards for one row per key.