rwdocs
Querying & Analysis
View

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.

Tables

TableTypeRows (PROD, order of magnitude)
SAMPLE_VARIANT_CALLS_OH_GRCH37OH raw calls~130 billion
SAMPLE_VARIANT_CALLS_ONC_GRCH37ONC raw calls~23 billion
VARIANT_CALLS_ANNOTATED_OH_GRCH37OH annotated~90 billion
GENETIC_ETHNICITIES_OHOH ethnicity (curated)~270K
GENETIC_ETHNICITIES_ONCONC ethnicity (curated)~360K

These grow as the pipelines load. For exact counts:

sql
SELECT TABLE_NAME, ROW_COUNTFROM CLINICOGENOMICS.INFORMATION_SCHEMA.TABLESWHERE 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

Example queries

PASS variants for an OH casefile on chromosome 17:

oh_pass_variants.sql
SELECT chrom, pos, ref, alt, caller, variant_type, gt, filterFROM CLINICOGENOMICS.VARIANTS.SAMPLE_VARIANT_CALLS_OH_GRCH37WHERE casefile_id = 12345678  AND chrom = 'chr17'  AND filter = 'PASS'ORDER BY pos;

Somatic tumor variants for an ONC casebundle:

onc_somatic.sql
SELECT chrom, pos, ref, alt, caller,       tumor_gt, tumor_vaf, mutation_statusFROM CLINICOGENOMICS.VARIANTS.SAMPLE_VARIANT_CALLS_ONC_GRCH37WHERE 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.