rwdocs
Querying & Analysis
View

Query Cookbook

Example queries and patterns for each major dataset in the CLINICOGENOMICS warehouse. Start here for a working query instead of a blank editor.

LIMS foundation queries

The LIMS_PUB product base tables are the primary analytics source — wide clinical bases joined from staging, clustered on casefile_id. A typical starting point counts distinct casefiles in the Renasight base:

foundation.sql
SELECT COUNT(DISTINCT casefile_id) AS casefilesFROM CLINICOGENOMICS.LIMS_PUB.BASE_RENASIGHT;

Use the per-product base tables: BASE_RENASIGHT (Organ Health), and BASE_SIGNATERA / BASE_ALTERA / BASE_LATITUDE (Oncology). See the Schema Reference for the full list.

Cohort & partner queries

Partner cohort tables in LIMS_COHORTS are pre-filtered and pre-joined — query them directly rather than re-joining to foundation. List what is available, then query a cohort:

cohorts.sql
SHOW TABLES IN SCHEMA CLINICOGENOMICS.LIMS_COHORTS;
SELECT COUNT(DISTINCT casefile_id) AS patientsFROM CLINICOGENOMICS.LIMS_COHORTS.OH_VERTEX_SPONSORED;

More cookbooks

Dataset-specific cookbooks live on their own pages:

  • Genomics (VARIANTS) Queries — performance-critical variant-call patterns built around Search Optimization columns.
  • Dashboard & Reporting Queries — the DASHBOARD marts and ADS clinical abstraction data.
  • Cortex Intelligence Agent — natural-language SQL via the AI agent.