Oncology Products

Oncology product base tables live in CLINICOGENOMICS.LIMS_PUB: Signatera (MRD/ctDNA), Altera (tissue CGP), and Latitude (methylation MRD). Each is a Dynamic Table clustered on casefile_id with a 24-hour target lag, and each has a patient-grain _PATIENT companion.

Products

Three current per-product bases. Build new analytics on these rather than the legacy wide tables. Each base is at casefile (test/blood-draw) grain; the _PATIENT companion rolls up to one row per patient.

Product	Base table	Grain	Rows (approx)	What it holds
Signatera	`BASE_SIGNATERA`	Casefile (blood draw)	~2.8M	Personalized MRD / ctDNA blood test results.
Altera	`BASE_ALTERA`	Casefile (tissue)	~49.6K	Tissue-based comprehensive genomic profiling (CGP).
Latitude	`BASE_LATITUDE`	Casefile (blood draw)	~4.1K	Methylation-based MRD test results.

Grain & identity

Each base table is at casefile grain — one row per test or blood draw. The matching _PATIENT companion (BASE_SIGNATERA_PATIENT, BASE_ALTERA_PATIENT, BASE_LATITUDE_PATIENT) rolls those rows up to one row per patient, with aggregated/LATEST_* columns for ever-positive flags, draw counts, and most-recent results.

Oncology primary identity is CASEBUNDLING_ID. CASEFILE_ID and PATIENT_ID are also present on every base table, but a casebundle groups the casefiles that make up a single oncology order.

Key columns

Product	Notable columns
Signatera	`MEANTUMORMOLECULES` (FLOAT, ctDNA quantity), `RESULT_CODE`, `RESULT_DATE`, `DATE_OF_BLOOD_COLLECTION`, `DATE_OF_TISSUE_COLLECTION`, `ACTIVE_WINDOW_FLAG`, `TNM_STAGE`, `CANCER_TYPE_RAW`, `CANCER_SUBTYPE_RAW`, `CANCER_STAGE_RAW`, `TESTORDER_PROGRAM`, `ECOG_RATING`. Patient rollups: `EVER_POSITIVE`, `TOTAL_BLOOD_DRAWS`, `LATEST_MTM`, `FIRST_POSITIVE_DATE`, `DAYS_SINCE_LAST_DRAW`.
Altera	`CANCER_TYPE_RAW`, `CANCER_SUBTYPE_RAW`, `CANCER_STAGE_RAW`, `DATE_OF_TISSUE_COLLECTION`, `RESULT_CODE`, `RESULT_DATE`, `PARTNER_ORDER_STATUS`. Patient companion has `_SEEN` and `LATEST_` columns.
Latitude	`METHYLATION_SCORE`, `TNM_STAGE`, `DATE_OF_BLOOD_COLLECTION`, `DATE_OF_SURGERY`, `DATE_OF_DIAGNOSIS`, `ACTIVE_WINDOW_FLAG`, `RESULT_CODE`.

Signatera tissue samples carry variant calls — for variant-level analysis cross-reference Genomics (VARIANTS) Queries. RNA-seq expression for these tumors lives on Gene Expression (RNA-seq).

Examples

oncology_examples.sql

1-- Signatera: patients who were ever ctDNA-positive2SELECT COUNT(*) AS ever_positive_patients3FROM CLINICOGENOMICS.LIMS_PUB.BASE_SIGNATERA_PATIENT4WHERE EVER_POSITIVE = TRUE;5
6-- Altera: cases by cancer type7SELECT CANCER_TYPE_RAW, COUNT(*) AS cases8FROM CLINICOGENOMICS.LIMS_PUB.BASE_ALTERA9GROUP BY CANCER_TYPE_RAW10ORDER BY cases DESC;11
12-- Latitude: latest methylation result per patient13SELECT PATIENT_ID, METHYLATION_SCORE, RESULT_CODE, DATE_OF_BLOOD_COLLECTION14FROM (15  SELECT16    PATIENT_ID,17    METHYLATION_SCORE,18    RESULT_CODE,19    DATE_OF_BLOOD_COLLECTION,20    ROW_NUMBER() OVER (21      PARTITION BY PATIENT_ID22      ORDER BY DATE_OF_BLOOD_COLLECTION DESC23    ) AS rn24  FROM CLINICOGENOMICS.LIMS_PUB.BASE_LATITUDE25)26WHERE rn = 1;

Legacy tables

Explore in Horizon Catalog

For full column-level detail, open Snowflake's Horizon Catalog in Snowsight (Catalog / object explorer) and browse CLINICOGENOMICS → LIMS_PUB, or query CLINICOGENOMICS.INFORMATION_SCHEMA.COLUMNS. See the Schema Reference page, which documents the Horizon Catalog.