Developer Reference
Data model — source to dashboard lineage
Every source table, every column, every Silver entity, every dbt model, and every mart table — fully traced to the dashboard KPI it powers.
Five-layer data architecture
From source systems to dashboard-ready marts
Data flows through five layers before reaching a dashboard. Each layer is write-once relative to the layer below — you can always replay Silver from Bronze, and Gold from Silver, without touching source systems.
Golden rule: Bronze is write-once. Silver is canonical. Gold is recomputable. Marts are disposable. If a dbt mart has a bug, drop and rebuild — never touch Bronze or Silver to fix it.
| Layer | Name | Technology | Refresh cadence | What is stored | Written by |
|---|---|---|---|---|---|
| 0 | Source systems | SQL Server · REST APIs · SFTP · MLLP | Real-time → daily | Live operational data in vendor-native schemas. Never query directly for analytics — always extract to Bronze first. | Epic, ERP, HR, Payer |
| 1 | Bronze — raw | S3 / GCS / ADLS · Parquet / .hl7 | On receipt (immutable) | Exact bytes as received. No transformation. Partitioned by tenant/source/date. 7-year HIPAA retention. | Ingestion pipeline |
| 2 | Silver — canonical | PostgreSQL + Parquet | <8s HL7 · daily batch | Parsed, validated, MPI-matched. One canonical row per entity. Schema enforced. RLS by tenant_id. | Stream processor + Airflow |
| 3 | Gold — dbt models | Snowflake + dbt Core | Hourly ops · nightly analytics | Business-logic tables: LOS, denial attribution, supply cost per case, staffing ratios, risk adjustment. | dbt scheduled runs |
| 4 | Marts — dashboard-ready | Snowflake views + Metabase | Nightly full + intraday incremental | Pre-aggregated, pre-joined. One mart per dashboard area. No PHI in AI narrative mart. | dbt mart models |
Silver canonical entities
dim_patient
Sources: MPI engine
PK: enterprise_patient_id (UUID)
Golden record ID. Never exposed in dashboards. PHI columns (mrn, dob) encrypted at rest.
fact_encounter
Sources: PAT_ENC + HSP_ACCOUNT
PK: encounter_id = PAT_ENC_CSN_ID
~5M rows/year. LOS, DRG, readmit_30d_flag (self-join), attending_prov_id, patient_class decoded.
fact_claim
Sources: CLAIM_INFO + ARPB + 835 EDI
PK: claim_id
Denied_flag, denial_category, preventable_flag derived. paid_amount from 835 only — not in Clarity.
fact_or_supply
Sources: ERP CASECHARGES + OR_LOG
PK: supply_line_id (synthetic)
Item cost per surgical case. Two-step join: ERP case_id → bridge → OR_CASE_ID → PAT_ENC_CSN.
fact_staffing_shift
Sources: SNO_SCHED + UKG/Kronos
PK: shift_id (synthetic)
Hours scheduled vs worked. Labour cost calculated. staff_type critical (travel = 2–3× rate).
fact_referral
Sources: REFERRAL table
PK: referral_id
Used for 90-day leakage self-join. Multi-facility: union all Epic instances first.