# Genomics (VARIANTS) Queries

Copy-paste SQL for the genomic variant-call data in `CLINICOGENOMICS.VARIANTS`. The per-sample call tables hold tens of billions of rows, so every pattern is built around the Search Optimization Service columns — query in seconds instead of scanning the whole table.

> **Use GRCh37**
>
> All queries use `CLINICOGENOMICS.VARIANTS.*` (there is no VDS schema in PROD). The GRCh38 tables exist but are empty in PROD — use the `*_GRCH37` tables.

## Tables

| Table | Type | Rows (PROD, order of magnitude) |
| --- | --- | --- |
| `SAMPLE_VARIANT_CALLS_OH_GRCH37` | OH raw calls | ~130 billion |
| `SAMPLE_VARIANT_CALLS_ONC_GRCH37` | ONC raw calls | ~23 billion |
| `VARIANT_CALLS_ANNOTATED_OH_GRCH37` | OH annotated | ~90 billion |
| `GENETIC_ETHNICITIES_OH` | OH ethnicity (curated) | ~270K |
| `GENETIC_ETHNICITIES_ONC` | ONC ethnicity (curated) | ~360K |

These grow as the pipelines load. For exact counts:

```sql
SELECT TABLE_NAME, ROW_COUNT
FROM CLINICOGENOMICS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'VARIANTS'
ORDER BY TABLE_NAME;
```

## Search Optimization columns (read this first)

The four `SAMPLE_VARIANT_CALLS_*` tables have SOS enabled for equality predicates on the columns below. Always include at least one; without one, the query scans tens of billions of rows.

- **OH tables:** `CASEFILE_ID`, `PATIENT_ID`, `SAMPLE_ID`, `SAMPLE_BARCODE`, `(CHROM, POS)`, `CALLER`, `VARIANT_TYPE`, `GT`, `FILTER`
- **ONC tables:** `CASEBUNDLING_ID`, `PATIENT_ID`, `TISSUE_CASEFILE_ID`, `TUMOR_SAMPLE_BARCODE`, `(CHROM, POS)`, `MUTATION_STATUS`, `TUMOR_GT`, `FILTER`

> **Identifier differs by line of business**
>
> OH tables key on `CASEFILE_ID`; ONC tables key on `CASEBUNDLING_ID` (and carry `TISSUE_CASEFILE_ID` for the tissue sample).

## Example queries

PASS variants for an OH casefile on chromosome 17:

```sql
SELECT chrom, pos, ref, alt, caller, variant_type, gt, filter
FROM CLINICOGENOMICS.VARIANTS.SAMPLE_VARIANT_CALLS_OH_GRCH37
WHERE casefile_id = 12345678
  AND chrom = 'chr17'
  AND filter = 'PASS'
ORDER BY pos;
```

Somatic tumor variants for an ONC casebundle:

```sql
SELECT chrom, pos, ref, alt, caller,
       tumor_gt, tumor_vaf, mutation_status
FROM CLINICOGENOMICS.VARIANTS.SAMPLE_VARIANT_CALLS_ONC_GRCH37
WHERE casebundling_id = 87654321
  AND filter = 'PASS'
ORDER BY chrom, pos;
```

## Anti-patterns

Each of these scans tens of billions of rows:

- `SELECT *` with no `WHERE` clause.
- `WHERE chrom = 'chr17' AND pos BETWEEN 1000 AND 2000` without a `casefile_id` equality — SOS accelerates equality, not ranges.
- Joining a `SAMPLE_VARIANT_CALLS_*` table to `LIMS_PUB` without an SOS equality predicate on the variant-table side.