Schema Reference
The map of which schema holds which domain in CLINICOGENOMICS — staging views, foundation tables, partner cohorts, variant calls, dashboard marts, LLM-extracted events, and reference seeds.
Schema map
| Schema | Objects | Holds |
|---|---|---|
LIMS_PUB | ~38 | Foundation tables: wide clinical bases and casefile entry points. |
LIMS_PUB_STAGING | ~52 | Deduplicated staging views over the LIMS data share. |
LIMS_COHORTS | ~37 | Partner and internal cohort tables (Vertex, Alnylam, etc.). |
VARIANTS | ~25 | Variant call tables, VEP-annotated variants, genetic ethnicities. |
CLINICAL_LLM | ~34 | LLM-extracted clinical event tables plus the raw extraction source. |
DASHBOARD | ~8 | BI dashboard marts (Vertex / Forian DRV, provider analysis, COMPASS). |
LIMS_OPERATIONAL | ~21 | Outreach sync-status, DocuSign, cohort history, provider-project tables. |
SEEDS | ~12 | dbt seed lookup / reference tables. |
RNASEQ | ~8 | RNA-seq Salmon gene/transcript expression facts plus run/QC dims. See Gene Expression (RNA-seq). |
ADS | — | Clinical abstraction (Abstracted Data Store) aggregations; staged in ADS_STAGING. |
IMAGING | — | Imaging data; staged in IMAGING_STAGING. |
ONCOLOGY_ONTOLOGY | ~26 | Oncology ontology / ontology-engine tables (ONT_*, ACT_*). |
APP_COHORTS | — | Application-facing cohort tables. |
STREAMLIT | — | Streamlit app objects. |
NOTEBOOKS | — | Snowflake notebooks (also SHARED_NOTEBOOKS). |
OMICS | 0 | Reserved (empty today; RNA-seq lives in RNASEQ). |
LIMS_PUB: foundation
Foundation tables are clustered on casefile_id with a 24-hour target lag. Object names are the Snowflake names; the matching dbt model is given alongside.
| Object | dbt model | Description |
|---|---|---|
CASEFILE_FILTERED | int_casefile_filtered | Base casefile with status filters, exclusions, CDC dedup. Primary entry point for all downstream models. |
BASE_RENASIGHT | base_renasight | Renasight (Organ Health) product base. Primary OH analytics source. |
BASE_SIGNATERA | base_signatera | Signatera (Oncology) MRD / ctDNA product base (~2.8M rows). |
BASE_ALTERA | base_altera | Altera (Oncology) tissue CGP product base (~49.6K rows). |
BASE_LATITUDE | base_latitude | Latitude (Oncology) methylation MRD product base (~4.1K rows). |
SF_PROVIDER_AGG | int_sf_provider_agg | Salesforce provider aggregation. |
Each oncology base has a patient-grain _PATIENT companion. Oncology identity is CASEBUNDLING_ID, not casefile_id. For per-product detail (grain, key columns, ctDNA vs tissue vs methylation), see Oncology Products.
LIMS_COHORTS: partner cohorts
37 objects in PROD, uppercase, no mart_ prefix. Partner examples: OH_VERTEX_SPONSORED, OH_ALNYLAM_AGXT, OH_ASTRAZENECA_APOL1, OH_NOVARTIS_C3G, ONC_BLADDER. Run SHOW TABLES IN SCHEMA CLINICOGENOMICS.LIMS_COHORTS for the current full list.
VARIANTS
24 objects: raw variant call tables, annotated variants, ethnicity tables, lookup views, and a processing tracker. The four raw SAMPLE_VARIANT_CALLS_* tables have Search Optimization enabled — see Genomics (VARIANTS) Queries. Note: ONC primary identity is CASEBUNDLING_ID, not CASEFILE_ID.
Key join columns
| Column | Type | Description |
|---|---|---|
casefile_id | INT | Primary join key across clinical models. |
patient_id | INT | Patient identifier. |
sample_barcode | VARCHAR | Sample tracking identifier. |
provider_id | INT | Provider identifier. |
npi | VARCHAR(10) | National Provider Identifier. |
-- Live object list for any schemaSELECT TABLE_NAME, TABLE_TYPEFROM CLINICOGENOMICS.INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'LIMS_PUB';Exploring with Horizon Catalog
Horizon Catalog is Snowflake’s built-in data catalog and object explorer in Snowsight. Use it to browse databases → schemas → tables and inspect column-level metadata, tags, and lineage without writing SQL — the fastest way to discover what a schema actually contains.
To open it, sign in to Snowsight and use the Catalog / database object explorer to drill into CLINICOGENOMICS, pick a schema, and select a table to see its columns, data types, tags, and lineage.
Reach for Horizon for interactive browsing and column / tag / lineage discovery. Reach for INFORMATION_SCHEMA (e.g. CLINICOGENOMICS.INFORMATION_SCHEMA.COLUMNS / .TABLES) when you need a programmatic, scriptable listing.
-- Column lookup for a specific tableSELECT COLUMN_NAME, DATA_TYPEFROM CLINICOGENOMICS.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = 'LIMS_PUB' AND TABLE_NAME = 'BASE_SIGNATERA'ORDER BY ORDINAL_POSITION;