Add a unique id

Add a column holding a random, hashed or time-ordered identifier that is unique to each row and does not depend on row order.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Add a random, hashed or time-ordered identifier to each row

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE with_ids AS
SELECT order_id, customer,
       uuid() AS random_id,
       md5(order_id::VARCHAR) AS hashed_id,
       uuidv7() AS time_id
FROM orders o;
-- the hashed id is the md5 of the key, which is unique because order_id is; it depends on the key and not on row order, so reading the rows in another order gives the same ids
CREATE TEMP TABLE reordered AS
SELECT order_id, md5(order_id::VARCHAR) AS hashed_id FROM (SELECT * FROM orders ORDER BY order_id DESC) o;
SELECT order_id, hashed_id FROM with_ids ORDER BY order_id;
SELECT CASE WHEN ((SELECT count(DISTINCT random_id) = 10 AND count(DISTINCT hashed_id) = 10 AND count(DISTINCT time_id) = 10 FROM with_ids) AND (SELECT bool_and(length(random_id::VARCHAR) = 36 AND length(hashed_id) = 32) FROM with_ids) AND (SELECT bool_and(substr(time_id::VARCHAR, 15, 1) = '7' AND uuid_extract_timestamp(time_id) > now() - INTERVAL 1 HOUR) FROM with_ids) AND (SELECT count(*) FROM with_ids w JOIN reordered r USING (order_id, hashed_id)) = 10) THEN 'as expected' ELSE error('the random, hashed and time-ordered ids were not ten distinct values each, or the hashed id changed with row order') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the random, hashed and time-ordered ids were not ten distinct values each, or the hashed id changed with row order”.
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)

Snowflake ID Generatorread 2026-09-27
Add a time-ordered 64-bit unique id built by the Snowflake ID algorithm

AWS Glue (1)

UUIDread 2026-09-27
Add a column holding a universally unique identifier per row

Duckle (2)

Surrogate Keyread 2026-09-25
add a dimension key: md5 of the natural key, or a sequence
UUIDread 2026-09-25
add a UUID v4 column per row

EasyMorph (1)

Generate unique identifiersread 2026-09-27
Add a column holding a random globally unique identifier per row

Ibis (1)

RandomUUIDread 2026-09-27
Generate a random UUID

Background

A Universally Unique IDentifier (UUID) is 128 bits long and requires no central registration process.^[1] Version 4 UUIDs (UUIDv4) are meant for generating UUIDs from truly random or pseudorandom numbers.^[1] Version 7 UUIDs (UUIDv7) put a Unix timestamp in milliseconds in their most significant 48 bits and fill most of the rest with random bits.^[1]

When to use it

One of the main reasons for using UUIDs is that no centralized authority is required to administer them, so generation on demand can be completely automated.^[1] Time-ordered monotonic UUIDs benefit from greater database-index locality, because new values are near each other in the index.^[1] In DuckDB, uuid_extract_timestamp extracts a TIMESTAMP WITH TIME ZONE from a UUIDv7 value.^[2] Choose UUIDv7 when the id will be indexed or sorted by when rows were made, and UUIDv4 when no creation time should be readable from it. (Meridian's view) Generate a random id once and store it, rather than recomputing it in a view that other tables join on. (Meridian's view)

A hashed surrogate key is traditionally made by concatenating the fields required to make a row unique and applying a cryptographic hash, usually md5, to the result.^[3] In BigQuery, Redshift and Snowflake, concat returns null if any referenced column is null, so each column is wrapped in a coalesce before hashing.^[3] Concatenating fields without a separator can give two rows the same key, as user 123 with product 123 and user 1231 with product 23 both give 123123.^[3] Before hashing, put a separator between the fields and give nulls a placeholder that cannot occur in the data. (Meridian's view)

The method

The Internet Engineering Task Force (IETF) published Request for Comments (RFC) 9562, the specification of UUIDs, in May 2024.^[1] RFC 9562 obsoletes RFC 4122.^[1] UUIDs were originally used in the Apollo Network Computing System (NCS), later in the Open Software Foundation's (OSF's) Distributed Computing Environment (DCE), and then in Microsoft Windows platforms.^[1]

The UUIDv7 timestamp is the number of milliseconds since midnight 1 Jan 1970 UTC, leap seconds excluded.^[1] UUID implementations should use a cryptographically secure pseudorandom number generator (CSPRNG), to give values that are difficult to predict and have a low likelihood of collision.^[1] Version 5 UUIDs (UUIDv5) are created by computing a SHA-1 hash over a namespace ID concatenated with a name.^[1] Where feasible, UUIDs should be stored in databases as the underlying 128-bit binary value, since storing them as text takes 288 bits.^[1]

In DuckDB, uuid() and gen_random_uuid() return a random UUIDv4, uuidv7() returns a UUIDv7, and md5 returns the MD5 hash of a string as a VARCHAR.^[2]

Compared with the alternatives

A surrogate key is frequently a sequential number, such as an identity column, a serial or a SEQUENCE, and some databases provide a UUID data type for surrogate keys.^[4] Use a row number to record a row's position, and a UUID or a hash to record its identity. (Meridian's view)

Sources

[1]RFC 9562: Universally Unique IDentifiers (UUIDs)standard
[2]Utility Functions – DuckDBdocumentation
[3]Generating Surrogate Keys Across Warehouses | dbt Developer Blogsecondary source
[4]Surrogate key - Wikipediasecondary source