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

> **PROD is the source of truth**
>
> Object counts below are an approximate snapshot and grow over time. For the live list, run `SELECT SCHEMA_NAME FROM CLINICOGENOMICS.INFORMATION_SCHEMA.SCHEMATA`.

## 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**.

> **BASE_OH / BASE_ONC are legacy**
>
> The wide `BASE_OH` and `BASE_ONC` tables still exist but are legacy. Build new work on the per-product base tables above; each also has `_PATIENT` (patient-grain) and `_RESEARCH` (view) variants.

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

```sql
-- Live object list for any schema
SELECT TABLE_NAME, TABLE_TYPE
FROM CLINICOGENOMICS.INFORMATION_SCHEMA.TABLES
WHERE 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.

```sql
-- Column lookup for a specific table
SELECT COLUMN_NAME, DATA_TYPE
FROM CLINICOGENOMICS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'LIMS_PUB'
  AND TABLE_NAME = 'BASE_SIGNATERA'
ORDER BY ORDINAL_POSITION;
```