Epic Clarity Data Model Guide for Healthcare Data Engineers

-- PAT_CLASS_C, ENC_TYPE_C, SEX_C
-- Always join to ZC_[CATEGORY] to decode
SELECT PAT_CLASS_C, zc.NAME as pat_class_desc
FROM PAT_ENC e
LEFT JOIN ZC_PAT_CLASS zc ON zc.PAT_CLASS_C = e.PAT_CLASS_C

SELECT
    e.PAT_ENC_CSN_ID          AS enctr_id,
    e.PAT_ID                  AS pat_id,
    e.CONTACT_DATE            AS enctr_dt,
    zt.NAME                   AS enc_type_desc,
    d.DEPARTMENT_NAME         AS dept_nm,
    dx.ICD10_CODE             AS prim_diag_cd,
    edg.DX_NAME               AS prim_diag_desc,
    s.PROV_NAME               AS rndrng_prvdr_nm,
    s.NPI                     AS rndrng_prvdr_npi
FROM PAT_ENC e
LEFT JOIN ZC_ENC_TYPE zt    ON zt.ENC_TYPE_C = e.ENC_TYPE_C
LEFT JOIN CLARITY_DEP d     ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
LEFT JOIN PAT_ENC_DX dx     ON dx.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
                            AND dx.LINE = 1
LEFT JOIN CLARITY_EDG edg   ON edg.DX_ID = dx.DX_ID
LEFT JOIN CLARITY_SER s     ON s.PROV_ID = e.VISIT_PROV_ID
WHERE e.CONTACT_DATE >= DATEADD(month, -12, GETDATE())
  AND e.ENC_CLOSED_YN = 'Y'
ORDER BY e.CONTACT_DATE DESC;

SELECT
    e.PAT_ENC_CSN_ID                          AS enctr_id,
    h.HOSP_ADMSN_TIME                         AS admit_ts,
    h.HOSP_DISCHRG_TIME                       AS dschrg_ts,
    DATEDIFF(day,
        h.HOSP_ADMSN_TIME,
        h.HOSP_DISCHRG_TIME)                  AS los_days,
    zd.NAME                                   AS dschrg_dispo_desc,
    h.DRG_MPI_CODE                            AS ms_drg_cd,
    p.BIRTH_DATE                              AS pat_dob
FROM PAT_ENC e
JOIN PAT_ENC_HSP h  ON h.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
JOIN PATIENT p      ON p.PAT_ID = e.PAT_ID
LEFT JOIN ZC_DISCH_DISP zd ON zd.DISCH_DISP_C = h.DISCH_DISP_C
WHERE h.HOSP_ADMSN_TIME >= DATEADD(year, -1, GETDATE())
  AND h.HOSP_DISCHRG_TIME IS NOT NULL;

SELECT
    op.ORDER_PROC_ID      AS order_id,
    op.PAT_ENC_CSN_ID     AS enctr_id,
    eap.PROC_NAME         AS proc_nm,
    eap.PROC_CODE         AS cpt_cd,
    r.LINE                AS result_line,
    r.ORD_VALUE           AS result_val,
    r.REFERENCE_UNIT      AS result_unit,
    r.REFERENCE_LOW       AS ref_rng_low,
    r.REFERENCE_HIGH      AS ref_rng_high,
    r.RESULT_FLAG         AS abnrml_flg
FROM ORDER_PROC op
JOIN CLARITY_EAP eap    ON eap.PROC_ID = op.PROC_ID
LEFT JOIN ORDER_RESULTS r ON r.ORDER_ID = op.ORDER_PROC_ID
WHERE op.ORDER_TIME >= DATEADD(month, -3, GETDATE())
  AND op.ORDER_STATUS_C = 5  -- 5 = Resulted
ORDER BY op.ORDER_TIME DESC, r.LINE;

SELECT
    a.PAT_ENC_CSN_ID      AS enctr_id,
    a.EVENT_TIME          AS adt_event_ts,
    za.NAME               AS adt_event_type,
    fd.DEPARTMENT_NAME    AS from_dept_nm,
    td.DEPARTMENT_NAME    AS to_dept_nm,
    DATEDIFF(minute,
        LAG(a.EVENT_TIME) OVER (
            PARTITION BY a.PAT_ENC_CSN_ID
            ORDER BY a.EVENT_TIME),
        a.EVENT_TIME)     AS mins_in_prior_location
FROM CLARITY_ADT a
LEFT JOIN ZC_ADT_EVENT_TYPE za ON za.EVENT_TYPE_C = a.EVENT_TYPE_C
LEFT JOIN CLARITY_DEP fd ON fd.DEPARTMENT_ID = a.FROM_DEPARTMENT_ID
LEFT JOIN CLARITY_DEP td ON td.DEPARTMENT_ID = a.TO_DEPARTMENT_ID
WHERE a.EVENT_TIME >= DATEADD(day, -30, GETDATE())
ORDER BY a.PAT_ENC_CSN_ID, a.EVENT_TIME;

-- models/staging/stg_clarity_encounters.sql
{{ config(materialized='view', schema='staging', tags=['clarity', 'encounters']) }}

with source as (
    select * from {{ source('clarity', 'PAT_ENC') }}
),
enc_type as (
    select ENC_TYPE_C, NAME as enc_type_desc
    from {{ source('clarity', 'ZC_ENC_TYPE') }}
),
staged as (
    select
        -- ISO-11179 naming
        e.PAT_ENC_CSN_ID::varchar(18)   as enctr_id,
        e.PAT_ID::varchar(18)            as pat_id,
        e.DEPARTMENT_ID::varchar(18)     as dept_id,
        e.VISIT_PROV_ID::varchar(18)     as rndrng_prvdr_id,
        e.CONTACT_DATE::date             as enctr_dt,
        e.APPT_TIME::timestamp           as appt_ts,
        e.ENC_TYPE_C                     as enc_type_c,
        et.enc_type_desc,
        e.ENC_CLOSED_YN                  as enc_closed_flg
    from source e
    left join enc_type et on et.ENC_TYPE_C = e.ENC_TYPE_C
    where e.CONTACT_DATE >= dateadd(year, -3, current_date)
)
select * from staged

# models/staging/stg_clarity_encounters.yml
version: 2
models:
  - name: stg_clarity_encounters
    columns:
      - name: enctr_id
        tests: [not_null, unique]
      - name: pat_id
        tests: [not_null]
      - name: enctr_dt
        tests: [not_null]
      - name: enc_closed_flg
        tests:
          - accepted_values:
              values: ['Y', 'N']

SELECT PAT_ENC_CSN_ID, COUNT(*) as cnt
FROM stg_clarity_encounters
GROUP BY PAT_ENC_CSN_ID
HAVING COUNT(*) > 1;

SELECT enc_type_c, COUNT(*) as cnt
FROM stg_clarity_encounters
WHERE enc_type_desc IS NULL
  AND enc_type_c IS NOT NULL
GROUP BY enc_type_c;

SELECT PROV_ID, PROV_NAME, PROV_TYPE
FROM CLARITY_SER
WHERE NPI IS NULL
   OR LEN(NPI) != 10
  AND PROV_TYPE_C NOT IN (20, 21);

SELECT PAT_ENC_CSN_ID,
    DATEDIFF(day, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) as los_days
FROM PAT_ENC_HSP
WHERE HOSP_DISCHRG_TIME < HOSP_ADMSN_TIME
   OR DATEDIFF(day, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) > 365;

SELECT YEAR(CONTACT_DATE) as yr,
       MONTH(CONTACT_DATE) as mo,
       ENC_CLOSED_YN,
       COUNT(*) as enc_cnt
FROM PAT_ENC
WHERE CONTACT_DATE >= DATEADD(month, -6, GETDATE())
GROUP BY YEAR(CONTACT_DATE), MONTH(CONTACT_DATE), ENC_CLOSED_YN
ORDER BY yr, mo;