# 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:

```sql
{{ config(cluster_by=['casefile_id']) }}

WITH source AS (
    SELECT * FROM {{ ref('int_base_oh') }}
)
SELECT
    {{ hash_with_salt('source.casefile_id') }}::VARCHAR(64) AS casefile_id,
    {{ hash_with_salt('source.patient_id') }}::VARCHAR(64)  AS patient_id,
    {{ transform_zip_to_zip3('source.patient_zipcode') }}   AS patient_zip_code,
    {{ map_race('source.patient_race') }}                   AS patient_race,
    source.test_type,
    source.patient_gender
    -- ... non-PHI columns; OMIT names, dob (raw), email, phone, address
FROM source
```

> **New DEID fields need re-review**
>
> Adding any new DEID table, or surfacing a new field on the DEID side, requires expert-determination re-review before it is exposed. The cert covers a specific set of fields and transforms.