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

> **Join on CASEBUNDLING_ID**
>
> For oncology, `CASEBUNDLING_ID` — not `CASEFILE_ID` — is the primary identity. Use it when joining oncology bases to each other or to downstream oncology tables; fall back to `PATIENT_ID` for patient-level rollups.

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

```sql
-- Signatera: patients who were ever ctDNA-positive
SELECT COUNT(*) AS ever_positive_patients
FROM CLINICOGENOMICS.LIMS_PUB.BASE_SIGNATERA_PATIENT
WHERE EVER_POSITIVE = TRUE;

-- Altera: cases by cancer type
SELECT CANCER_TYPE_RAW, COUNT(*) AS cases
FROM CLINICOGENOMICS.LIMS_PUB.BASE_ALTERA
GROUP BY CANCER_TYPE_RAW
ORDER BY cases DESC;

-- Latitude: latest methylation result per patient
SELECT PATIENT_ID, METHYLATION_SCORE, RESULT_CODE, DATE_OF_BLOOD_COLLECTION
FROM (
  SELECT
    PATIENT_ID,
    METHYLATION_SCORE,
    RESULT_CODE,
    DATE_OF_BLOOD_COLLECTION,
    ROW_NUMBER() OVER (
      PARTITION BY PATIENT_ID
      ORDER BY DATE_OF_BLOOD_COLLECTION DESC
    ) AS rn
  FROM CLINICOGENOMICS.LIMS_PUB.BASE_LATITUDE
)
WHERE rn = 1;
```

## Legacy tables

> **BASE_ONC / BASE_OH are legacy**
>
> The older wide tables `BASE_ONC` (~2.7M rows) and `BASE_OH` still exist, but new work should target the per-product bases above. Note that `BASE_ONC` uses `CANCER_TYPE` / `CANCER_SUBTYPE` / `CANCER_STAGE` (no `_RAW` suffix), unlike the current bases.

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