RCV Procedures¶
Overview¶
RCV statement generation is split across two stored procedures that run sequentially:
gks_rcv_proc-- builds aggregation tables through a two-layer aggregation hierarchy, progressively combining SCV-level data into condition-specific RCV-level summariesgks_rcv_statement_proc-- transforms those aggregation tables into GKS-formatted RCV statements with nested evidence lines and condition data
Both procedures accept the same parameters:
| Parameter | Type | Description |
|---|---|---|
on_date |
DATE |
Identifies the ClinVar release schema to process |
debug |
BOOL |
When TRUE, writes temp tables as persistent tables for inspection |
gks_rcv_proc (Aggregation)¶
This procedure materializes base data from SCV-level sources and builds two layers of progressively broader aggregation. Each layer groups records at a coarser level, applying submission-level-specific classification and review status logic. The aggregation logic mirrors VCV but operates per (variation, condition) pair rather than per variation.
Step 1: Build temp_rcv_base_data¶
Materializes base data by joining scv_summary with rcv_mapping (unnesting scv_accessions), rcv_accession, clinvar_statement_types, clinvar_clinsig_types, clinvar_proposition_types, and submission_level. This step produces one row per SCV with all the metadata needed for condition-specific aggregation.
Key derivations:
- full_rcv_id -- formatted as
{rcv_accession}.{version}fromrcv_accession - trait_set_id -- the condition grouping key from
rcv_mapping, used in all downstream GROUP BY clauses - original_submission_level -- raw code from the
submission_levellookup table - submission_level -- passes through unchanged; PG and EP are independent submission levels
- submission_level_label -- human-readable label from the lookup table
- statement_group -- category code from
clinvar_statement_types(G for germline, S for somatic) - prop_type -- proposition type code from
clinvar_proposition_types
Output: temp_rcv_base_data -- one row per SCV with classification, proposition, condition, and submission level metadata. Internal
Step 2: Build gks_rcv_grouping_base_agg¶
Core aggregation step that groups SCVs by (rcv_accession, trait_set_id, statement_group, prop_type, submission_level, tier_grouping). Tier grouping is only populated for somatic clinical impact (sci) propositions; it is NULL for all other proposition types.
The step uses four CTEs:
| CTE | Purpose |
|---|---|
core_agg |
GROUP BY with ARRAY_AGG of SCV IDs, contributing submission levels, and unique submitter count |
label_counts |
Per-classification-label SCV counts with significance values |
conflict_strings |
Aggregated classification labels, significance counts, and formatted conflict explanation strings |
somatic_conditions |
Condition names for somatic sci propositions, joined from gks_scv_condition_mapping |
The final_prep CTE applies submission-level-specific logic:
- PG -- no conflict detection; fixed aggregate review status
practice guideline - EP -- no conflict detection; fixed aggregate review status
reviewed by expert panel - CP -- conflict detection with review status upgrades: single submitter, multiple submitters with no conflicts, or conflicting classifications
- FLAG -- fixed label: "no classifications from unflagged records"
- NOCL -- passthrough label: "not provided"
- aggregate_review_status -- derived for all submission levels based on submitter count and conflict state
ID format: {RCV}.{ver}-{group}-{prop}-{level}[-{tier}]
Output: gks_rcv_grouping_base_agg -- one row per aggregation group. Pipeline table
Step 3: Build gks_rcv_grouping_tier_agg¶
Aggregates Base Grouping records by tier within each submission level. This layer applies only to somatic clinical impact (sci) propositions where tier_grouping IS NOT NULL.
The step ranks tiers by tier_priority (ascending) and SCV count (descending), then designates:
- The top-ranked tier as contributing
- All other tiers as non-contributing
- Secondary traits from non-contributing tiers that are not already present in the top tier
The aggregate label appends secondary trait information when applicable.
ID format: {RCV}.{ver}-{group}-{prop}-{level}
Output: gks_rcv_grouping_tier_agg -- one row per submission level within a proposition type. Pipeline table
Step 4: Build gks_rcv_aggregate_contribution¶
Submission-level aggregator using winner-takes-all ranking. Takes a unified input of Tier Grouping output (tiered records) combined with non-tiered Base Grouping records (tier_grouping IS NULL).
Records are ranked by submission level within each (rcv_accession, trait_set_id, statement_group, prop_type) group using the explicit ordering PG=6, EP=5, CP=4, NOCP=3, NOCL=2, FLAG=1. The highest-ranked submission level becomes the contributing result; all others become non-contributing.
A key difference from VCV: the winner-takes-all partition is by rcv_accession (not variation_id), since each RCV accession already represents a unique (variation, condition) pair.
Non-contributing details are preserved as an array of structs containing the layer ID, submission level, aggregate label, and conflicting explanation for each non-contributing record.
ID format: {RCV}.{ver}-{group}-{prop}
Output: gks_rcv_aggregate_contribution -- one row per proposition type within a statement group. Pipeline table
gks_rcv_statement_proc (Statement Generation)¶
This procedure transforms the aggregation tables produced by gks_rcv_proc into GKS-formatted RCV statements. It resolves condition data, generates statement structures at each layer (BASE), inlines evidence items into nested structures (PRE), then combines the results into a final output table.
PG and EP are independent submission levels. Each produces a single aggregate label and a single objectCondition value at every layer, using the same structure as every other submission level.
The procedure executes 8 sections: condition data resolution, three BASE steps, three PRE steps, and one FINAL section.
Condition Data Resolution¶
Before building statement structures, the procedure materializes temp_rcv_condition_data by joining rcv_mapping (unnesting scv_accessions) with gks_scv_condition_sets and selecting one representative SCV per RCV. This table provides the full condition concept (a Condition MappableConcept or a ConditionSet ConceptSet, with extensions excluded) needed to populate objectCondition in the proposition.
Output: temp_rcv_condition_data -- condition concept per RCV accession. Internal
BASE Steps¶
Each BASE section reads from the corresponding aggregation table and produces a statement structure with the following fields:
| Field | Description |
|---|---|
classification |
A Classification concept with name (the aggregate label) and optional conflictingExplanation extension. Used at every layer for every submission level |
confidence |
The submission level label (e.g., "expert panel", "assertion criteria provided") |
direction |
Derived from the classification label; passed through from the contributing SCV for single-SCV aggregations |
strength |
Derived from the classification label; passed through from the contributing SCV for single-SCV aggregations |
proposition |
Contains objectCondition (the condition from temp_rcv_condition_data -- either a Condition MappableConcept or a ConditionSet ConceptSet, extensions excluded), the SCV-matching proposition type from clinvar_proposition_types.gks_type, the SCV-matching predicate from clinvar_proposition_types.gks_predicate, and subjectVariant reference |
extensions |
Array with clinvarReviewStatus value |
evidenceLines |
References to child IDs (SCV IDs for Base Grouping, contributing/non-contributing statement IDs for Tier Grouping and Aggregate Contribution) |
The objectCondition value is the SCV's condition sourced from temp_rcv_condition_data -- just the condition itself, not wrapped with a classification. This same structure is produced at every step with no recombination.
Step-specific differences:
- Base Grouping BASE -- references SCV IDs directly in evidence lines; includes tier info in the proposition for tiered records
- Tier Grouping BASE -- references Base Grouping IDs; somatic only; includes contributing and non-contributing evidence lines
- Aggregate Contribution BASE -- references a single contributing child (from Tier Grouping or Base Grouping) plus non-contributing details
Output: temp_rcv_grouping_base_statements, temp_rcv_grouping_tier_statements, temp_rcv_agg_contribution_statements -- one per step. Internal
Base Grouping PRE¶
Inlines SCV evidence items from gks_dict_scv. Evidence lines are rewritten to reference SCV IDs in clinvar.submission:{scv_id} format. The classification, confidence, direction, strength, and proposition fields are carried forward unchanged from the BASE.
Output: temp_rcv_grouping_base_pre Internal
Tier Grouping PRE¶
Inlines Base Grouping PRE evidence items into Tier Grouping statements (somatic only). The classification, confidence, direction, strength, and proposition fields are passed through unchanged. Contributing and non-contributing evidence lines are rebuilt with the full inlined Base Grouping PRE statement structures.
Output: temp_rcv_grouping_tier_pre Internal
Aggregate Contribution PRE¶
Inlines evidence items from either Tier Grouping PRE or Base Grouping PRE (using COALESCE to check Tier Grouping first, then Base Grouping). The classification, confidence, direction, strength, and proposition fields are passed through unchanged from the BASE.
Output: temp_rcv_agg_contribution_pre Internal
FINAL¶
Selects all Aggregate Contribution PRE statements into the final output table.
Output: gks_dict_rcv -- the complete set of RCV statements ready for JSON serialization by gks_json_proc. Pipeline table
Output Tables¶
| Table | Procedure | Description | Role |
|---|---|---|---|
temp_rcv_base_data |
gks_rcv_proc |
Materialized SCV base data with condition and submission level mappings | Internal |
gks_rcv_grouping_base_agg |
gks_rcv_proc |
Base grouping aggregation by rcv_accession + group + prop + level (+ tier) | Pipeline table |
gks_rcv_grouping_tier_agg |
gks_rcv_proc |
Tier grouping aggregation within submission level (somatic only) | Pipeline table |
gks_rcv_aggregate_contribution |
gks_rcv_proc |
Submission level aggregation with winner-takes-all (partitioned by rcv_accession) | Pipeline table |
temp_rcv_condition_data |
gks_rcv_statement_proc |
Condition data resolved from rcv_mapping + gks_scv_condition_sets | Internal |
temp_rcv_grouping_base_statements |
gks_rcv_statement_proc |
BASE statement structures for Base Grouping step | Internal |
temp_rcv_grouping_tier_statements |
gks_rcv_statement_proc |
BASE statement structures for Tier Grouping step (somatic only) | Internal |
temp_rcv_agg_contribution_statements |
gks_rcv_statement_proc |
BASE statement structures for Aggregate Contribution step | Internal |
temp_rcv_grouping_base_pre |
gks_rcv_statement_proc |
PRE statement structures with inlined SCV evidence | Internal |
temp_rcv_grouping_tier_pre |
gks_rcv_statement_proc |
PRE statement structures with inlined Base Grouping evidence (somatic only) | Internal |
temp_rcv_agg_contribution_pre |
gks_rcv_statement_proc |
PRE statement structures with inlined evidence for Aggregate Contribution | Internal |
gks_dict_rcv |
gks_rcv_statement_proc |
Final RCV statements from Aggregate Contribution PRE | Pipeline table |
Dependencies¶
gks_rcv_proc¶
- Source Tables:
scv_summary,rcv_mapping,rcv_accession,gks_scv_condition_mapping - Lookup Tables:
clinvar_statement_types,clinvar_clinsig_types,clinvar_proposition_types,submission_level - UDFs:
clinvar_ingest.schema_on,clinvar_ingest.cleanup_temp_tables - Upstream Procedures:
gks_scv_statement_proc(forgks_scv_condition_mapping)
gks_rcv_statement_proc¶
- Aggregation Tables:
gks_rcv_grouping_base_agg,gks_rcv_grouping_tier_agg,gks_rcv_aggregate_contribution - Condition Tables:
rcv_mapping,gks_scv_condition_sets - Statement Tables:
gks_dict_scv - Source Tables:
scv_summary - Lookup Tables:
clinvar_statement_categories,clinvar_proposition_types,submission_level,clinvar_clinsig_types - UDFs:
clinvar_ingest.schema_on,clinvar_ingest.cleanup_temp_tables - Upstream Procedures:
gks_rcv_proc,gks_scv_statement_proc - Downstream Consumers:
gks_json_proc