Shape · IDs and hashing

Surrogate Key

Gives each distinct key a compact ID: a hash that stays the same across runs, or a number from 1 to N.

Also known as: Dimension key

How it works

Natural key is one or more columns that identify an entity. Mode Hash (default) writes the md5 of the key, with NULL parts handled, so the same key gets the same id in every run and file. Sequence numbers the distinct keys 1 to N in key order, NULLs last, and every row sharing a key gets the same number, which changes when new keys sort in between. The id is appended as surrogate_key unless you set Output column.

Inputs and outputs

Inputs

data — Table

Outputs

data — Table

Settings

The Attributes panel with a Surrogate Key node selected.

The Attributes panel of Surrogate Key, as in the app: the controls work, but nothing runs — there is no data behind it.

The same settings, as the Assistant writes them

{
  keyColumns: string[]
  mode: "hash"|"sequence"
  outputColumn: string
}

hash = md5 of the key (stable across runs); sequence = 1..N over the DISTINCT keys ordered by the key (rows sharing a key share a number)

Example

Build a store dimension key from chain and store_code: natural key both columns, mode Sequence, output store_key.

chain	store_code	store_key
Alpha	001	1
Alpha	002	2
Alpha	001	1