ClinVar-GKM parquet — table map

22 tables, 91.5 M rows (2026-10-18 release). Six small areas you can hold in your head one at a time; the joins between them are on the Joining Tables page.

The six areas

Arrows run in the direction of the reference: a proposition points at a variant, a statement points at a proposition. The statement tiers are not parallel — every SCV is cited once inside RCV and once inside VCV through evidence_items[], and never the reverse.

  verified 100 %
  polymorphic — dispatch on id prefix
  natural-key join
  lives inside a JSON column, not a typed column

Query — row counts by area

SELECT 'variation' AS section, count(*) n FROM 'variation.parquet'
UNION ALL SELECT 'condition',           count(*) FROM 'condition.parquet'
UNION ALL SELECT 'propositionPathogenicity', count(*) FROM 'propositionPathogenicity.parquet'
UNION ALL SELECT 'scv', count(*) FROM 'scv.parquet'
UNION ALL SELECT 'rcv', count(*) FROM 'rcv.parquet'
UNION ALL SELECT 'vcv', count(*) FROM 'vcv.parquet';

variation 4,691,827 condition 23,414 propositionPathogenicity 27,868,969 scv 6,853,781 rcv 12,056,355 vcv 9,165,651

Area 1 — Variant, the subject of every proposition

members[] and constraints[] both leave variation; the VRS prefix picks the table (VA. allele, CN. count, CX. change) and each branch is exactly 100 %. The green edge is a gene reference by NCBI CURIE inside a JSON extension — 7,397,007 refs covering all 92,989 genes.

Query — drill one variant through VRS (members[] → allele → location → sequenceReference)

-- members[] holds VRS ids; the VA. prefix dispatches to allele
SELECT v.id AS variation, v.name AS variation_name,
       a.id AS allele, l.id AS location, sr.id AS seqref
FROM 'variation.parquet' v, unnest(v.members) AS t(member)
LEFT JOIN 'allele.parquet'            a  ON a.id  = member
LEFT JOIN 'location.parquet'          l  ON l.id  = a.location_id
LEFT JOIN 'sequenceReference.parquet' sr ON sr.id = l.sequence_reference_id
WHERE v.id = 'clinvar:51592';

variation variation_name allele location seqref clinvar:51592 NM_000059.4(BRCA2):c.4090A>C (p.Ile1364Leu) ga4gh:VA.Apas… ga4gh:SL.Gga5… SQ._0wi-qoD…

Area 2 — Condition & therapy, the object

conditionSet holds two kinds of row: 5,404 accessioned ClinVar TraitSets and 95,302 sets minted for VCV-level aggregates. therapyGroup fans out the same way through its members[] column of clinvar.therapy: ids. Releases before 1.1.0 have no members column: the members are only inside the JSON value.

Query — expand a conditionSet to member names (concepts[] holds ids, not names)

-- concepts[] holds ids; join to condition, then join the names with ';'
SELECT cs.id, cs.membershipOperator AS op, string_agg(c.name, '; ') AS members
FROM 'conditionSet.parquet' cs, unnest(cs.concepts) AS t(cid)
LEFT JOIN 'condition.parquet' c ON c.id = cid
WHERE cs.id = 'clinvar.conditionset:vcv-50aae75c5e3a7740ab0c2f3ff9ebd970'
GROUP BY cs.id, cs.membershipOperator;

id op members clinvar.conditionset:vcv-50aae75… OR Charcot-Marie-Tooth disease type 2; not specified

Area 3 — Proposition, what is being claimed

All four proposition tables have this shape, with the same column names (from 1.1.0). The object slot is the only polymorphic one: object_type names its table, and every branch resolves. Dispatch is deterministic — no object id ever matches both condition and conditionSet.

Query — every condition a variant is asserted for (object ids resolved to names)

-- resolve each 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, coalesce(c.name, sn.member_names) AS condition
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.subject_variant_id = 'clinvar:51592'            -- NM_000059.4(BRCA2):c.4090A>C
  AND coalesce(c.name, sn.member_names) IS NOT NULL
ORDER BY condition LIMIT 12;

object_id condition clinvar.trait:32337 Breast and/or ovarian cancer clinvar.trait:636 Breast-ovarian cancer, familial, susceptibility to, 2 clinvar.conditionset:vcv-2604d0… Breast-ovarian cancer…; Familial cancer of breast; … (8 members) clinvar.conditionset:vcv-5f128f… Breast-ovarian cancer…; Hereditary cancer-predisposing… (4 members) clinvar.trait:536 Familial cancer of breast clinvar.trait:1276 Fanconi anemia complementation group D1 clinvar.trait:16761 Hereditary breast ovarian cancer syndrome clinvar.trait:18746 Hereditary cancer-predisposing syndrome clinvar.trait:17556 not provided clinvar.trait:16789 not specified

Area 4 — SCV, one submitter's assertion

evidenceLine here is the small 1,568-row table, and it is a shared-PK subtype, not a reference: evidenceLine.id is the scv.id. Do not confuse it with rcv_evidenceLine / vcv_evidenceLine, which are different tables in a different key space. The submitted condition resolves to the accessioned level (clinvar.trait: / clinvar.traitset:).

Query — an SCV's submitter and classification

-- contributions[].contributor is the submitter id
SELECT DISTINCT s.classification.name AS classification, sub.name AS submitter
FROM 'scv.parquet' s, unnest(s.contributions) AS t(con)
JOIN 'submitter.parquet' sub ON sub.id = con.contributor
WHERE s.proposition_id IN (
    SELECT id FROM read_parquet('proposition*.parquet') WHERE subject_variant_id = 'clinvar:51592')
LIMIT 12;

classification submitter Likely benign Blueprint Genetics Benign Genome Diagnostics Laboratory, University Medical Center Utrecht Benign Clinical Genetics DNA and cytogenetics Diagnostics Lab, Erasmus MC, Erasmus Medical Center Benign BRCAlab, Lund University Benign Department of Pathology and Laboratory Medicine, Sinai Health System Benign Clinical Genetics Laboratory, Department of Pathology, Netherlands Cancer Institute Benign Ambry Genetics … (many submitters; classifications span Benign / Likely benign / Uncertain significance / not provided)

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.