Repository navigation
Design durable user surrogate key (replace email-based user_pk) #2383
Description
Description/Context
user_pk = hash(lower(email)) is unstable: an email change re-keys the user and orphans historical fact rows on full refresh (24 downstream assets join on user_fk per OpenMetadata); two people sharing an email merge into one. This is a design problem beyond the immediate NULL-email collapse bug.
Plan/Design
Design and migrate to a durable key — user_global_id where present, platform source IDs otherwise — with email demoted to an attribute. Coordinate with reverse-ETL / Hightouch consumers of user_pk.
Also fold in the dup-audit recommendation of a user_pk_from_email() macro as an interim single-definition guard: the hash(lower(email)) idiom is currently repeated 7× in dim_user.sql (lines 310, 354, 404, 449, 494, 538) and has leaked into marts__combined__users and bridge_user_courserun_role.
Blocked by: the NULL-email collapse fix (tk-fix-dim-user-null-email-identity-collapse-244cd0).
P1. Part of #2375 (2026-07 dbt warehouse audit). Witan task: tk-design-durable-user-surrogate-key-replace-email--04b0a2.
Activity
blarghmatey commentedon Aug 20, 2026
Closed: delivered by PR #2497 (commit a20a728). dim_user.sql now derives user_pk via generate_surrogate_key(['user_identity_source', 'user_identity_id']) over coalesce(user_global_id, id_source_user_id, email), demoting email to a last-resort fallback and exposing the provenance as user_pk_source. No email-based surrogate keys remain in the dimensional layer.