Cat-VRS Generation (gkm_catvar_proc)¶

Overview¶

The clinvar_ingest.gkm_catvar_proc stored procedure transforms ClinVar variation data into GA4GH Cat-VRS (Categorical Variation Representation Specification) format. It builds categorical variant records that group genomic variants at a higher level for use in clinical assertions — linking VRS-resolved alleles with their expressions, cross-references, constraints, and ClinVar metadata.

The procedure accepts a single parameter — on_date DATE — which identifies the ClinVar release schema to process.

Workflow¶

The procedure executes the following steps sequentially within a loop over the target schema(s) identified by the on_date parameter.

Steps produce three types of output:

• Pipeline table — persists in BigQuery for use by downstream procedures or external processing
• JSON artifact — exported as a JSONL file for public distribution
• Internal — exists only within the procedure and is consumed by later steps

Step 1a: Build temp_seqref¶

Extracts enriched sequence reference records from VRS output. For each distinct accession with a resolved refgetAccession, derives:

• Molecule type from the accession prefix (genomic, mRNA, RNA, or protein)
• Residue alphabet (na or aa) based on accession type
• Assembly extensions mapping assembly version numbers to human-readable names (GRCh38, GRCh37, NCBI36)

Output: Small lookup table joined by subsequent steps. Internal

Step 1b: Build temp_seqloc¶

Extracts distinct sequence locations from VRS output and joins each to its enriched sequence reference from temp_seqref. Transforms range endpoints into the Cat-VRS start_range/end_range array format for imprecise locations.

Output: temp_seqloc — one row per unique VRS sequence location. Internal

Step 2: Build temp_ctxvar_expression¶

Consolidates all variant expressions (SPDI, HGVS, gnomAD) for each variation+accession pair, ranked by a 4-level precedence hierarchy:

1. SPDI (highest) — canonical SPDI from VRS output
2. HGVS — nucleotide expressions from variation_hgvs
3. gnomAD — VCF-style identifiers from variation_loc
4. Derived HGVS (lowest) — positional HGVS from variation_loc when no variation_hgvs entry exists

Only includes expressions for variations that successfully resolved through VRS processing. Selects the best expression as the variant name using HGVS type (descending) and precedence ordering.

Output: temp_ctxvar_expression — one row per variation+accession+assembly. Internal

Step 2b: Build gkm_dict_allele¶

Builds one VRS Allele record per allele id. Two or more ClinVar variations can normalize to the same VRS allele: for example, a duplication in a repeat run that was submitted at two positions, or a ClinVar duplicate variation that has not been merged yet. The record is built per allele, not per variation, so its key is never repeated:

• expressions — the union of every contributing variation's expressions, de-duplicated and ordered by precedence, then syntax, then value.
• name — the Step 2 rule (HGVS type descending, then precedence) applied across that union, with the expression value as the final tie-breaker. The name is always one of the expressions, so the expressions the other variations would have used as the name stay in expressions.

Each contributing variation keeps its own Categorical Variant record, which references the shared allele.

Output: gkm_dict_allele — one row per VRS allele id.

Step 3: Build temp_ctxvar¶

Joins VRS output with expressions and sequence locations to build contextual variant records. Maps VRS output types to categorical variant types:

Output: temp_ctxvar — one row per distinct contextual variant. Internal

Step 4: Build temp_catvar_extension¶

Assembles all extension metadata for each variation into a single extensions array. Includes:

• ClinVar HGVS list with molecular consequences (built from variation_hgvs with SO term lookups)
• Gene associations (from gene_association and gene tables)
• Categorical and VRS type classifications
• VRS processing issues and exceptions
• ClinVar variation metadata (type, subclass, cytogenetic location)

See Categorical Variant Extensions for a reference of all extension types.

Output: temp_catvar_extension — one row per variation with an extensions array. Internal

Step 5: Build temp_catvar_mappings¶

Builds cross-reference mappings for each variation from two sources:

• ClinVar self-reference — an exactMatch mapping to the ClinVar variation identifier
• External cross-references — relatedMatch (or closeMatch for ClinGen) mappings from variation_xref with IRI resolution for known databases (dbSNP, OMIM, UniProtKB, GTR, etc.)

Output: temp_catvar_mappings — one row per variation with a mappings array. Internal

Step 6: Build gkm_catvar_pre¶

Assembles the preliminary categorical variant record by joining contextual variants with their constraints, extensions, and mappings. Generates constraint structures based on categorical variant type:

Output: gkm_catvar_pre — one row per categorical variant with full structured record. Pipeline table

Step 7: Build gkm_catvar¶

Converts the structured records from gkm_catvar_pre into JSON format using TO_JSON with null/empty stripping, then normalizes and keys the output using the clinvar_ingest.normalizeAndKeyById UDF.

Output: gkm_catvar — final JSON-normalized categorical variant records. JSON artifact — exported as variation.jsonl.gz. See Categorical Variants for consumer documentation.

Output Tables¶

Dependencies¶

• UDFs: clinvar_ingest.normalizeAndKeyById, clinvar_ingest.schema_on
• Source Tables: gkm_vrs, variation_loc, variation_hgvs, variation_identity, variation_xref, gene_association, gene
• Upstream Procedures: variation_identity (or variation_identity_incremental), VRS Python processing
• Downstream Consumers: gkm_scv_statement_proc, export pipeline

Examples¶

See Cat-VRS examples in the repository for annotated JSONC examples of the output records.