Deterministic surrogate key

You meet it when#

You need a single-column key for a table whose natural key is composite, and the pipeline may process the same source row more than once.

The version that causes trouble#

create table fct_order_lines (
    order_line_key bigint generated always as identity,
    order_id       varchar,
    line_no        int,
    ...
);

An auto-increment key is assigned at insert time. Reprocess the same source row and it gets a different key. Now you have two rows that are the same fact with different identities, downstream joins double-count, and there is no way to tell which is canonical.

The pattern#

select
    md5(order_id || '|' || line_no::varchar) as order_line_key,
    order_id,
    line_no,
    ...
from staging_order_lines;

Same input, same key, forever. Reprocessing overwrites rather than duplicating, and the idempotent window replace becomes genuinely safe.

The separator matters#

md5(a || b) collides: ('ab', 'c') and ('a', 'bc') produce the same string. Use a delimiter that cannot appear in the values:

md5(coalesce(order_id, '') || '|' || coalesce(line_no::varchar, ''))

And coalesce every component — 'x' || null is null in standard SQL, so one null column silently nulls the entire key.

Which columns go in#

Exactly the ones that define the grain, and nothing else. If the grain is one row per order line, the key is order_id plus line_no. Adding a mutable attribute means the key changes when that attribute changes, which defeats the point.

This makes the key a machine-checkable statement of the grain — and the uniqueness test on it is the grain assertion that catches fan-out bugs on the next run.