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

> **Never scan unfiltered**
>
> These facts are billion-row tables. Never query without a filter. Always constrain by `SAMPLE_BARCODE`, by `PATIENT_ID` / `CASEBUNDLING_ID`, and/or by `GENE_NAME` (or `GENE_ID`) before you select. A bare `SELECT *` or a gene-only filter with no sample/patient constraint scans the whole billion+ rows.

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

```sql
SELECT sample_barcode, patient_id, gene_tpm, gene_count
FROM CLINICOGENOMICS.RNASEQ.FACT_SALMON_GENE_EXPRESSION
WHERE gene_name = 'EGFR'
  AND patient_id = 12345678
ORDER 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):

```sql
SELECT e.sample_barcode,
       e.patient_id,
       e.casebundling_id,
       e.gene_tpm,
       s.*
FROM CLINICOGENOMICS.RNASEQ.FACT_SALMON_GENE_EXPRESSION AS e
JOIN CLINICOGENOMICS.LIMS_PUB.BASE_SIGNATERA AS s
  ON e.patient_id = s.patient_id
 AND e.casebundling_id = s.casebundling_id
WHERE e.gene_name = 'EGFR'
ORDER 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.