Transformations
Surrogate Key
Adds a warehouse surrogate key: a counting number, a pattern you write, or a UUID — a key that belongs to your warehouse rather than to the source system.
When to use it
Use it when loading a dimension or staging table that needs its own key, independent of whatever ids the source system uses.
In a pipeline
File InputSurrogate KeySQL Server Output
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| New Column Name | text | Yes | Surrogate Key |
| Insert After | select | — | End of table |
| Key Format | select | — | number |
| Pattern | text | Yes | KEY-{seq:6} |
| Start From | number | — | 1 |
| Step | number | — | 1 |
Example
A readable key for a customer dimension
- New Column Name=Customer Key
- Insert After=End of table
- Key Format=Pattern
- Pattern=CUST-{yyyy}-{seq:5}
- Start From=1
| Source Id | Name |
|---|---|
| A-19 | Acme |
| H-4 | Harbour Foods |
| Source Id | Name | Customer Key |
|---|---|---|
| A-19 | Acme | CUST-2026-00001 |
| H-4 | Harbour Foods | CUST-2026-00002 |
Key Format Number gives plain 1, 2, 3 — the usual choice for a dimension key — and UUID gives a random id per row.
Worth knowing
- Pattern tokens: {seq} is the counter and {seq:5} pads it to five digits, {uuid} is a random id, {yyyy} {yy} {MM} {dd} {HH} {mm} {ss} are the run's date and time, and any other name in braces is read from that row's column, such as {Region}-{seq:4}.
- Start From and Step control the counter, so a later load can carry on where the last one stopped.
- A pattern with neither {seq} nor {uuid} repeats itself on rows with the same values; the node says so when it happens.
- Keys are handed out in the order the rows arrive, so put this node after any sorting or filtering.