De-Identified Data (DEID)

CLINICOGENOMICS_DEID is a separate database of de-identified clinical and genomic data for external partner deliverables. It mirrors the PHI database’s schema naming, plus vendor-specific schemas.

Overview

All DEID models apply HIPAA Expert Determination de-identification rules (§164.514(b)(1), Brad Malin cert): DOB capping, ZIP3 conversion with low-population suppression, ICD code suppression, race category mapping, and ID tokenization via keyed HMAC-SHA256 hash. DEID Dynamic Tables can reference PHI tables cross-database, e.g. SELECT FROM CLINICOGENOMICS.LIMS_PUB.INT_BASE_OH.

Schema mapping

Schema	Content	Maps to PHI
`LIMS_PUB`	DEID base tables (oh, onc, reference)	`CLINICOGENOMICS.LIMS_PUB`
`LIMS_COHORTS`	DEID partner cohorts + datamarts	`CLINICOGENOMICS.LIMS_COHORTS`
`VARIANTS`	DEID variant calls + genetic ethnicities	`CLINICOGENOMICS.VARIANTS`
`TOKENS`	Token mapping tables (Natera, Forian, Veradigm, Veritas)	DEID-specific
`FORIAN`	Forian raw vendor data (36 tables)	DEID-specific
`VERADIGM`	Veradigm raw vendor data (20 tables)	DEID-specific

Expert determination transforms

DEID-specific dbt macros (in macros/deid/) implement the certified transforms:

Macro	What it does
`cap_dob_expert_determination`	Caps DOB per the age-90 rule: age ≥ 89 → Jan 1 of (current year − 89).
`transform_zip_to_zip3`	Converts ZIP to 3 digits, remaps 3 codes, suppresses 49 low-population ZIP3s to NULL.
`transform_icd_codes_deid`	Remaps BMI codes; suppresses external causes, self-harm, V-codes, Z38.
`hash_with_salt`	Keyed HMAC-SHA256 for tokenizing IDs; salt read at run time from the grant-locked HASH_SALT_KEY.
`map_race`	Maps race/ethnicity to 5 categories: White, Black, Hispanic, Asian or Pacific Islander, Other.

Column mapping: PHI → DEID

When building DEID models from PHI source tables, columns fall into three categories: transformed (hashed IDs, ZIP3, capped age), excluded (names, contact info, street address — never included), and pass-through (~37 columns that need no transformation). A foundation DEID model looks like:

deid_base_oh.sql

1{{ config(cluster_by=['casefile_id']) }}2
3WITH source AS (4    SELECT * FROM {{ ref('int_base_oh') }}5)6SELECT7    {{ hash_with_salt('source.casefile_id') }}::VARCHAR(64) AS casefile_id,8    {{ hash_with_salt('source.patient_id') }}::VARCHAR(64)  AS patient_id,9    {{ transform_zip_to_zip3('source.patient_zipcode') }}   AS patient_zip_code,10    {{ map_race('source.patient_race') }}                   AS patient_race,11    source.test_type,12    source.patient_gender13    -- ... non-PHI columns; OMIT names, dob (raw), email, phone, address14FROM source