Shape / columns · All operations

Hash several columns into one key

A variant of Hash or mask a column

Combine several columns into one checksum or surrogate-key column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Hash several columns into one key that does not collide on separators or nulls

SQL
CREATE TEMP TABLE parts AS SELECT * FROM (VALUES
  (1, 'a|b', 'c', 'x'), (2, 'a', 'b|c', 'x'), (3, 'a', NULL, 'x'), (4, NULL, 'a', 'x'), (5, 'a', '', 'x'), (6, 'a|b', 'c', 'x')
) t(id, k1, k2, k3);
-- JSON text of the row keeps each part apart, so a pipe in a value and a null are not mistaken for a separator or an empty string
CREATE TEMP TABLE result AS
SELECT id, md5(to_json(row(k1, k2, k3))) AS row_key FROM parts;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT count(DISTINCT row_key) FROM result) = 5 AND (SELECT count(*) FROM result WHERE length(row_key) = 32) = 6 AND (SELECT count(DISTINCT row_key) FROM result WHERE id IN (1, 6)) = 1 AND (SELECT row_key FROM result WHERE id = 1) = md5('["a|b","c","x"]')) THEN 'as expected' ELSE error('the rows were not given one key each, equal rows the same key, with a pipe, a null and an empty string kept apart') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the rows were not given one key each, equal rows the same key, with a pipe, a null and an empty string kept apart”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
  • Add a checksumread 2026-09-27

    Add a checksum column (CRC32, MD5, SHA) computed over chosen fields

Bruin (1)
dbt-utils (1)
Duckle (1)
Pentaho Data Integration (1)
  • Add a Checksumread 2026-09-27

    Add a checksum column computed over each input row

Polars (1)
SQLMesh (1)
  • @GENERATE_SURROGATE_KEYread 2026-09-27

    Hash the concatenated text of several columns into a surrogate key, MD5 by default

Talend (1)
  • tAddCRCRowread 2026-09-27

    Add a CRC checksum column computed from one or more columns, as a surrogate key

Background

Hashing several columns into one key concatenates the fields that make a row unique and hashes them, giving one id per combination of values.[1] A key built this way gives a table a primary key where its data has none, such as a table that is unique on a user and a product together.[1]

When to use it

Build one where a table's grain is a combination of columns and no single column is unique on its own. (Meridian's view) In BigQuery, Redshift and Snowflake, concat returns null if any of its columns is null, so each column needs wrapping in a coalesce before hashing.[1] In DuckDB, the || operator returns NULL if any input is NULL, while concat and concat_ws skip NULL inputs.[2] Coalescing nulls to an empty string means a null and an empty string can no longer be told apart in the key.[1] Without a separator, different rows can concatenate to the same string, as a user 123 with product 123 and a user 1231 with product 23 both give 123123.[1] Use a separator that cannot appear in the data, or hash a structured form of the row such as its JSON text, so that a value holding the separator cannot make two rows collide. (Meridian's view)

Polars' hash_rows does not guarantee stable results across Polars versions, only within a single version.[3] In DuckDB, hash returns a UBIGINT, and the hash function it uses may change across DuckDB versions.[4] Store keys computed with a named digest such as MD5 or SHA-256, not an engine's internal hash, so that a key written today matches one computed after an upgrade. (Meridian's view)

The method

In Apache Hop, an optional separator between field values helps produce more unique hash codes, and an optional prefix such as CUST or PROD keeps keys from different topics apart.[5]

A cryptographic hash with an n-bit output is expected to have a collision resistance strength of n/2 bits, because of the birthday paradox.[6] Non-cryptographic error-detecting codes such as cyclic redundancy checks guard only against accidental changes, since an intentional spoof can readily be made to share the code value.[6]

Compared with the alternatives

A surrogate key in the database sense is not derived from application data, unlike a natural or business key.[7] A database-generated surrogate key is frequently a sequential number, such as an identity column or a PostgreSQL serial.[7] Prefer a hashed key when a table is rebuilt from scratch and its keys must come out the same each time, and a generated sequence when the key must survive a change to the columns it would be built from. (Meridian's view)

Sources

  1. [1]Generating Surrogate Keys Across Warehouses | dbt Developer Blogsecondary source
  2. [2]Text Functions – DuckDBdocumentation
  3. [3]polars.DataFrame.hash_rows — Polars documentationdocumentation
  4. [4]Utility Functions – DuckDBdocumentation
  5. [5]Add a checksum | Apache Hopdocumentation
  6. [6]Cryptographic hash function - Wikipediasecondary source
  7. [7]Surrogate key - Wikipediasecondary source