rwdocs
Design & Architecture
View

Architecture Overview

CLINICOGENOMICS holds clinical, genomic, and operational data from LIMS, Salesforce, Kepler, and partner feeds. It flows through a layered, medallion-style pipeline: a Fivetran share lands raw rows, staging views clean them, foundation Dynamic Tables build core entities, and cohort and dashboard marts serve analyst-ready datasets.

High-level data flow

text
DATA_SHARE.LIMSRDS_PRODLIMS   (LIMS MySQL replica via Fivetran, change tracking)        │        ▼Staging Views (LIMS_PUB_STAGING)   — dedup, soft-delete filter; resolve at query time        │        ▼Foundation Dynamic Tables (LIMS_PUB)   — core clinical entities, 24h target lag        │        ▼Cohort Dynamic Tables (LIMS_COHORTS)   — partner-specific marts        │        ▼Dashboard Dynamic Tables (DASHBOARD)   — BI delivery reports

Additional sources feed the same chain: Salesforce (DATA_SHARE.SALESFORCE), Kepler (DATA_SHARE.KEPLER_DASHBOARD), and Clinical LLM (CLINICAL_LLM.LLM_EXTRACTION_RAW). Those marts power cohort dashboards, de-identified partner exports, Cortex semantic agents, and Clinical LLM extraction.

Key principles

  • All pipelines are incremental; only changed rows propagate.
  • Foundation tables are Dynamic Tables with a 24-hour target lag and AUTO refresh mode.
  • The staging layer uses views (not materialized) except Clinical LLM, which uses a Dynamic Table.
  • All staging views over DATA_SHARE.LIMSRDS_PRODLIMS include WHERE _fivetran_deleted = false.
  • DEID models apply HIPAA Expert Determination transforms and materialize in a separate database.

Three databases

DatabasePurposeEnvironments
CLINICOGENOMICSPrimary PHI database. All clinical, genomic, and operational data. This is where dbt models live.Dev · Preprod · Prod
CLINICOGENOMICS_DEIDDe-identified data for external partners (Forian, Veradigm, Veritas, Panalgo). Schemas mirror CLINICOGENOMICS plus vendor-specific schemas.Dev · Preprod · Prod
ANNOTATIONSReference annotation databases for variant interpretation: gnomAD, ClinVar, CADD, REVEL, AlphaMissense, VEP.Dev · Preprod · Prod

Key technical decisions

  • Foundation tables are Dynamic Tables. The source share provides change tracking, enabling DTs end-to-end. Snowflake Task infrastructure is fully retired.
  • 24-hour target lag, AUTO refresh. All DTs refresh within 24 hours of upstream changes via change tracking.
  • Clustering on casefile_id. Most foundation and cohort DTs are clustered for fast joins.
  • Search Optimization Service is enabled on the large genomics tables for fast equality lookups.
  • Semantic views power Cortex Agents, deployed via the Snowflake Labs dbt package.