ClinVar-GKM parquet — join seams

The five places tables join. Three of them dispatch on an id prefix or a type column and silently drop rows if you join only one branch. Figures are from the 2026-10-18 release.

Seam A · Variant → Proposition

The clean one. All four proposition tables resolve to variation.id at 100 % — 28,077,355 references, zero unresolved. They share one column schema (from 1.1.0), so one glob, read_parquet('proposition*.parquet'), reads them all through subject_variant_id.

Query — one variant, every proposition (one glob reads all four tables)

-- the 4 proposition tables share one schema; read them as one table
SELECT v.id, v.name, p.type, p.predicate
FROM 'variation.parquet' v
JOIN read_parquet('proposition*.parquet') p ON p.subject_variant_id = v.id
WHERE v.id = 'clinvar:51592';

id name type predicate clinvar:51592 NM_000059.4(BRCA2):c.4090A>C (p.Ile1364Leu) VariantPathogenicityProposition isCausalFor clinvar:51592 NM_000059.4(BRCA2):c.4090A>C (p.Ile1364Leu) ClinvarNotProvidedProposition hasNoProvidedClassificationFor … (65 propositions for this variant: 60 VariantPathogenicityProposition / isCausalFor, 5 ClinvarNotProvidedProposition / hasNoProvidedClassificationFor)

Seam B · Proposition → Condition or ConditionSet

Every proposition table's object_id is polymorphic, deterministic, and complete: object_type names its table. Condition objects split three ways by prefix, and all three resolve; two land in conditionSet — one accessioned, one minted — so read the prefix to know which kind of set you have, but join both to the same table. The 13 therapeutic-response propositions point at a therapy or therapyGroup instead, with the tumor type in condition_qualifier_id.

Not drawn: 13 therapeutic-response objects, 6 therapy and 7 therapyGroup. conditionSet.concepts[] holds ids, not names — expand it and re-join to condition to get member names; a minted set can nest a clinvar.traitset: member, so expansion is one level deeper than it looks.

Query — dispatch object_id on object_type, resolved to condition / conditionSet names

-- resolve the object to a name: condition.name, or the set's members joined by ';'
WITH set_names AS (
    SELECT cs.id, string_agg(c.name, '; ') AS member_names
    FROM 'conditionSet.parquet' cs, unnest(cs.concepts) AS t(cid)
    LEFT JOIN 'condition.parquet' c ON c.id = cid
    GROUP BY cs.id
)
SELECT DISTINCT p.object_id, p.object_type,
    coalesce(c.name, sn.member_names, 'UNRESOLVED') AS condition_name
FROM 'propositionPathogenicity.parquet' p
-- CASE keeps each join an equi-join (fast); an AND in ON plans a nested loop
LEFT JOIN 'condition.parquet' c  ON c.id  = CASE WHEN p.object_type = 'condition'    THEN p.object_id END
LEFT JOIN set_names           sn ON sn.id = CASE WHEN p.object_type = 'conditionSet' THEN p.object_id END
WHERE p.object_id IN (
    'clinvar.trait:16789', 'clinvar.traitset:10013',
    'clinvar.conditionset:vcv-c620785e2025efde81bdd15c814fa83d')
ORDER BY p.object_type;

object_id object_type condition_name clinvar.trait:16789 condition not specified clinvar.traitset:10013 conditionSet GM1 gangliosidosis; Mucopolysaccharidosis, MPS-IV-B clinvar.conditionset:vcv-… conditionSet Epidermodysplasia verruciformis; Inborn genetic diseases

Seam C · Proposition → Statements · 1:1

proposition_id is unique within each statement table and across all three combined — 28,075,787 references, 28,075,787 distinct. A proposition has at most one statement, so it is effectively a side-table of its statement. Only the 1,568 evidence-line targets in propositionSomaticClinicalImpact (diagnostic, prognostic, therapeutic response) have no statement.

Counts are, in order, how many scv / rcv / vcv rows resolve to that proposition table. Each tier's four shares sum to its exact row count, so joining propositionPathogenicity alone silently loses the remainder; join the glob.

Query — SCVs across proposition tables: scv + classification + proposition type + variant & condition names

-- read all four proposition tables keeping subject + object, then resolve both to names.
-- (a statement's object is always a condition or set; therapy objects belong only to the
--  therapeutic-response propositions, which no statement classifies.)
WITH prop AS (
    SELECT id, type, subject_variant_id AS variant_id, object_id
      FROM read_parquet('proposition*.parquet')
),
set_names AS (
    SELECT cs.id, string_agg(c.name, '; ') AS member_names
    FROM 'conditionSet.parquet' cs, unnest(cs.concepts) AS t(cid)
    LEFT JOIN 'condition.parquet' c ON c.id = cid GROUP BY cs.id
)
SELECT s.id AS scv_id, s.classification.name AS classification, p.type AS proposition_type,
       p.variant_id, v.name AS variant_name,
       p.object_id, coalesce(c.name, sn.member_names) AS object_name
FROM 'scv.parquet' s
JOIN prop p ON p.id = s.proposition_id
LEFT JOIN 'variation.parquet' v  ON v.id  = p.variant_id
LEFT JOIN 'condition.parquet' c  ON c.id  = p.object_id
LEFT JOIN set_names           sn ON sn.id = p.object_id
WHERE s.id IN ('clinvar.submission:SCV000056982.2',
               'clinvar.submission:SCV007129927.1',
               'clinvar.submission:SCV002796738.1')
ORDER BY proposition_type;

scv_id classification proposition_type variant_id variant_name object_id object_name …SCV000056982.2 association ClinvarAssociationProposition clinvar:40131 NM_000237.3(LPL):c.*1671T>C clinvar.trait:9723 High density lipoprotein cholesterol… …SCV007129927.1 Likely oncogenic VariantOncogenicityProposition clinvar:428975 NM_001042492.3(NF1):c.1721+1G>T clinvar.trait:32759 Neoplasm …SCV002796738.1 Benign VariantPathogenicityProposition clinvar:143120 NM_001171.6(ABCC6):c.793A>G… clinvar.traitset:12598 Pseudoxanthoma elasticum, forme fruste;…

Seam D · Aggregate nesting — a 2-level forest, SCVs always at the leaves

A root statement carries the overall classification and cites no SCVs directly; its child statements partition the submissions by evidence subgroup and cite the SCVs. Every aggregate child has exactly one parent, and no edge crosses an accession.

A real group: VCV000012871.94-G-PATH is the root and cites three child statements and zero SCVs; the children cite 30, 2 and 1 SCVs. Root suffixes are the bare classification (-G-PATH, -G-NP, -G-DR…); child suffixes add the evidence subgroup (-CP, -NOCP, -EP). Depth stops at 2, so a fixed two-hop walk is complete.

Query — one aggregate group: root cites children, children cite SCVs

-- unnest has_evidence_lines → evidence_items for one accession
SELECT s.id AS statement, count(*) AS cites
FROM (SELECT id, unnest(has_evidence_lines) el_id
      FROM 'vcv.parquet' WHERE id LIKE 'VCV000012871.%') s
JOIN (SELECT id, unnest(evidence_items) item
      FROM 'vcv_evidenceLine.parquet') el ON el.id = s.el_id
GROUP BY 1 ORDER BY 1;

statement cites VCV000012871.94-G-PATH 3 ← the 3 children VCV000012871.94-G-PATH-CP 30 ← SCVs VCV000012871.94-G-PATH-EP 1 VCV000012871.94-G-PATH-NOCP 2

Seam E · every SCV to its RCV and VCV group, in one pass

Because no aggregate edge ever crosses an accession, the accession in the citing statement's own id already names the group — so one pass over has_evidence_lines ⋈ evidence_items yields every SCV↔accession pair. No recursion, no self-join on the edges.

Each SCV belongs to exactly one VCV accession and exactly one RCV accession, and the pass covers all 6,853,781 — a complete grouping key you can materialise once. Keep statement_id as well as the accession: it tells you which evidence subgroup (-CP / -NOCP / -EP) cited the SCV.

Query — every SCV → its VCV accession in one pass (swap vcv/rcv for the other tier)

-- the accession in the citing statement id names the group; no recursion needed
SELECT regexp_extract(e.parent, '^VCV[0-9]+', 0) AS vcv_accession, e.child AS scv_id
FROM (
    SELECT s.id AS parent, it.item AS child
    FROM (SELECT id, unnest(has_evidence_lines) AS el_id FROM 'vcv.parquet') s
    JOIN (SELECT id, unnest(evidence_items)     AS item  FROM 'vcv_evidenceLine.parquet') it
         ON it.id = s.el_id
) e
WHERE e.child LIKE 'clinvar.submission:%';

vcv_accession scv_id VCV000012871 clinvar.submission:SCV000616386.4 VCV000012871 clinvar.submission:SCV000207677.1 … (6,853,781 pairs — every SCV, exactly one VCV accession)

Every count, table, and query output here is from the ClinVar-GKM 2026-10-18 release — figures change between releases, so don't assume they are the latest. See Downloads → Parquet Files for current releases.