Gene Expression (RNA-seq)

RNA-seq gene- and transcript-level expression lives in CLINICOGENOMICS.RNASEQ, quantified by Salmon. The gene-expression fact is ~1.78 billion rows (one row per sample × gene across ~22,660 samples), so every query starts from a filter, not a full scan.

Tables

Everything is in CLINICOGENOMICS.RNASEQ: two fact tables for expression, dimension tables for the transcript→gene map and per-run metadata, and a QC fact.

Table	Grain	What it holds
`FACT_SALMON_GENE_EXPRESSION`	sample × gene	Salmon gene-level quantification (~1.78B rows): counts, TPM, scaled counts, lengths.
`FACT_SALMON_TRANSCRIPT_EXPRESSION`	sample × transcript	Salmon transcript-level quantification: counts, TPM, lengths, bootstrap variance.
`DIM_TX2GENE`	transcript	Transcript→gene map (TRANSCRIPT_ID → GENE_ID).
`DIM_RUN`	run	Run metadata (~231 runs): environment, S3 pubdir, created timestamp. Plus DIM_RUN_PARAMETERS, DIM_RUN_SOFTWARE_VERSIONS, DIM_RUN_FILE_PATHS for params, tool versions, and file paths (incl. BAM S3 paths).
`FACT_MULTIQC_METRICS`	sample × metric	Per-sample MultiQC QC metrics (module / metric name / value).

There is also an OMICS schema, but it is empty in PROD today (reserved); document and query RNASEQ.

Grain and scale

FACT_SALMON_GENE_EXPRESSION is grained at one row per (SAMPLE_BARCODE × GENE_ID) and holds ~1,783,885,840 rows (1.78 billion) across ~22,660 distinct samples and ~231 runs. FACT_SALMON_TRANSCRIPT_EXPRESSION is finer still.

Joining to clinical and oncology

Expression rows carry the same identity keys as the clinical/oncology bases, so you can join directly without a bridge table:

SAMPLE_BARCODE — sample identity. One barcode is one sequenced RNA sample.
PATIENT_ID, CASEBUNDLING_ID, TISSUE_CASEFILE_ID — join keys to clinical and oncology bases, e.g. CLINICOGENOMICS.LIMS_PUB.BASE_SIGNATERA and BASE_ALTERA. See the Oncology Products page for those base tables and their grain.
DIM_TX2GENE — map transcript IDs to gene IDs when working from FACT_SALMON_TRANSCRIPT_EXPRESSION.

Choosing a metric

Each row carries several abundance measures. Pick by use case:

GENE_TPM (FLOAT) — transcripts-per-million, length- and depth-normalized. Use this for cross-sample comparison and for ranking/expression-level queries.
GENE_COUNT (NUMBER) — raw estimated counts. Not normalized; do not compare across samples directly.
GENE_COUNT_SCALED / GENE_COUNT_LENGTH_SCALED — scaled counts intended for differential-expression tools (tximport-style inputs), not for ad-hoc comparison.

Supporting columns include GENE_LENGTH, GENE_EFFECTIVE_LENGTH, NUM_TRANSCRIPTS, and PIPELINE_VERSION. For QC filtering (e.g. dropping low-quality samples), join FACT_MULTIQC_METRICS on SAMPLE_BARCODE and filter by MODULE_NAME / METRIC_NAME.

Example queries

EGFR expression (TPM) across samples for one patient:

egfr_tpm.sql

1SELECT sample_barcode, patient_id, gene_tpm, gene_count2FROM CLINICOGENOMICS.RNASEQ.FACT_SALMON_GENE_EXPRESSION3WHERE gene_name = 'EGFR'4  AND patient_id = 123456785ORDER BY gene_tpm DESC;

Join gene expression to a Signatera cohort on PATIENT_ID / CASEBUNDLING_ID (filter the fact first so the scan stays small):

egfr_x_signatera.sql

1SELECT e.sample_barcode,2       e.patient_id,3       e.casebundling_id,4       e.gene_tpm,5       s.*6FROM CLINICOGENOMICS.RNASEQ.FACT_SALMON_GENE_EXPRESSION AS e7JOIN CLINICOGENOMICS.LIMS_PUB.BASE_SIGNATERA AS s8  ON e.patient_id = s.patient_id9 AND e.casebundling_id = s.casebundling_id10WHERE e.gene_name = 'EGFR'11ORDER BY e.gene_tpm DESC;

Explore in Horizon Catalog

To browse the schema interactively, open the Snowsight Horizon Catalog (object explorer) and drill into CLINICOGENOMICS → RNASEQ to see tables, columns, and row counts. Or query CLINICOGENOMICS.INFORMATION_SCHEMA.COLUMNS filtered to TABLE_SCHEMA = 'RNASEQ'. Horizon Catalog is documented on the Schema Reference page.