FastSSV Rules Reference¶

19. Standard Concept Value Validation¶

Rule ID: concept_standardization.standard_concept_value_validation Severity: ERROR

Intent¶

OMOP semantic rule OMOP_037: The standard_concept column only accepts 'S' (Standard), 'C' (Classification), or NULL (non-standard). Filtering with other values like 'Y', 'N', 1, 0 is incorrect.

How it works¶

This rule analyzes the SQL query to identify standard concept value validation patterns.

Examples¶

Suggested fix¶

Use 'S' for standard, 'C' for classification, or NULL for non-standard concepts.

20. Unit Vocabulary Validation¶

Rule ID: concept_standardization.unit_vocabulary_validation Severity: WARNING

Intent¶

In OMOP CDM, all standard unit concepts use the UCUM (Unified Code for Units of Measure) vocabulary. Unit concept columns (*_unit_concept_id) reference standard concepts from the UCUM vocabulary.

When queries join unit_concept_id to the concept table and filter by
vocabulary_id != 'UCUM', they may:
- Return non-standard units
- Return zero results
- Miss the intended unit concepts

Affected columns:
- measurement.unit_concept_id
- observation.unit_concept_id
- drug_strength.amount_unit_concept_id
- drug_strength.numerator_unit_concept_id
- drug_strength.denominator_unit_concept_id
- specimen.unit_concept_id
- dose_era.unit_concept_id

How it works¶

This rule analyzes the SQL query to identify unit vocabulary validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM measurement m
    JOIN concept c ON m.unit_concept_id = c.concept_id
    WHERE c.vocabulary_id = 'SNOMED'

SELECT *
    FROM observation o
    JOIN concept c
      ON o.unit_concept_id = c.concept_id
      AND c.vocabulary_id = 'LOINC'

Correct patterns:

SELECT *
    FROM measurement m
    JOIN concept c ON m.unit_concept_id = c.concept_id
    WHERE c.vocabulary_id = 'UCUM'

SELECT *
    FROM measurement m
    JOIN concept c ON m.unit_concept_id = c.concept_id
    WHERE c.domain_id = 'Unit'

Suggested fix¶

Use vocabulary_id = 'UCUM' for unit concept lookups, or remove the vocabulary_id filter entirely.

Join Validation Rules¶

These rules validate that table joins follow the OMOP CDM schema foreign key relationships.

1. Care Site ID Join Validation¶

Rule ID: joins.care_site_id_join_validation Severity: ERROR

Intent¶

Clinical tables have care_site_id (foreign key to care_site.care_site_id). Joining on other columns (e.g., care_site_id to location_id) is semantically incorrect and produces wrong results.

How it works¶

This rule analyzes the SQL query to identify care site id join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM visit_occurrence vo
    JOIN care_site cs ON vo.care_site_id = cs.location_id

Correct patterns:

SELECT * FROM visit_occurrence vo
    JOIN care_site cs ON vo.care_site_id = cs.care_site_id

Suggested fix¶

Join using care_site_id on both sides: table.care_site_id = care_site.care_site_id

2. Care Site Join Path Validation¶

Rule ID: joins.care_site_join_validation Severity: WARNING

Intent¶

Clinical tables have care_site_id (foreign key to care_site.care_site_id). Direct joins to location.location_id bypass the care_site intermediary and are semantically incorrect (comparing care_site_id with location_id).

Exception: person.location_id is valid - it represents the person's home address.

How it works¶

This rule analyzes the SQL query to identify care site join path validation patterns.

Examples¶

Violation patterns:

SELECT * FROM visit_occurrence vo
    JOIN location l ON vo.care_site_id = l.location_id

Correct patterns:

SELECT * FROM visit_occurrence vo
    JOIN care_site cs ON vo.care_site_id = cs.care_site_id
    JOIN location l ON cs.location_id = l.location_id

Suggested fix¶

Use: clinical → care_site → location join path.

3. Care Site to Location Join Validation¶

Rule ID: joins.care_site_location_join_validation Severity: ERROR

Intent¶

care_site has location_id (foreign key to location.location_id) to identify the geographic location of the care site. Joining on other columns (e.g., care_site_id to location_id) is semantically incorrect and produces wrong results.

How it works¶

This rule analyzes the SQL query to identify care site to location join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM care_site cs
    JOIN location l ON cs.care_site_id = l.location_id

Correct patterns:

SELECT * FROM care_site cs
    JOIN location l ON cs.location_id = l.location_id

Suggested fix¶

Join care_site to location using location_id: care_site.location_id = location.location_id

4. Clinical Primary Key Join Validation¶

Rule ID: joins.clinical_pk_cross_join_validation Severity: ERROR

Intent¶

Each clinical event table has its own independent primary key sequence: - condition_occurrence_id = 123 → A specific condition event - drug_exposure_id = 123 → A specific drug exposure event - procedure_occurrence_id = 123 → A specific procedure event

These are completely unrelated - they just happen to have overlapping numeric
values. Joining them is always semantically meaningless.

How it works¶

This rule analyzes the SQL query to identify clinical primary key join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM condition_occurrence co
    JOIN drug_exposure de ON co.condition_occurrence_id = de.drug_exposure_id

with independent ID sequences!

Correct patterns:

SELECT * FROM condition_occurrence co
    JOIN drug_exposure de ON co.person_id = de.person_id
      AND co.visit_occurrence_id = de.visit_occurrence_id

Suggested fix¶

Do not join clinical event primary keys. Use shared foreign keys such as person_id or visit_occurrence_id instead.

5. Clinical Tables Require Person ID Linkage¶

Rule ID: joins.clinical_person_id_linkage_validation Severity: ERROR

Intent¶

Joining clinical tables without person_id linkage can match data from different patients, leading to completely invalid results. For example:

- Patient A has condition_start_date = '2020-01-15'
- Patient B has drug_exposure_start_date = '2020-01-15'

Joining on dates alone would incorrectly associate Patient A's condition with
Patient B's drug exposure.

Clinical tables that require person_id linkage: - condition_occurrence - drug_exposure - procedure_occurrence - measurement - observation - visit_occurrence - visit_detail - death - person

How it works¶

This rule analyzes the SQL query to identify clinical tables require person id linkage patterns.

Examples¶

Violation patterns:

SELECT co.condition_concept_id, de.drug_concept_id
    FROM condition_occurrence co
    JOIN drug_exposure de ON co.condition_start_date = de.drug_exposure_start_date

Correct patterns:

SELECT co.condition_concept_id, de.drug_concept_id
    FROM condition_occurrence co
    JOIN drug_exposure de ON co.person_id = de.person_id

SELECT co.condition_concept_id, de.drug_concept_id
    FROM condition_occurrence co
    JOIN person p ON co.person_id = p.person_id
    JOIN drug_exposure de ON p.person_id = de.person_id

Suggested fix¶

Add joins on person_id between clinical tables.

6. Clinical to Visit Detail Join Validation¶

Rule ID: joins.clinical_visit_detail_join_validation Severity: ERROR

Intent¶

Clinical tables have both visit_occurrence_id and visit_detail_id columns. These represent different ID spaces: - visit_occurrence_id: Links to parent visit (visit_occurrence table) - visit_detail_id: Links to detailed sub-visit (visit_detail table)

Both are integers, so joining visit_occurrence_id to visit_detail_id produces
NO TYPE ERROR, but randomly matches unrelated records where IDs happen to be
equal. This silently corrupts analytical results.

How it works¶

This rule analyzes the SQL query to identify clinical to visit detail join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM measurement m
    JOIN visit_detail vd ON m.visit_occurrence_id = vd.visit_detail_id

Correct patterns:

SELECT * FROM measurement m
    JOIN visit_detail vd ON m.visit_detail_id = vd.visit_detail_id

Suggested fix¶

None

7. Cohort to Clinical Table Join Validation¶

Rule ID: joins.cohort_clinical_join_validation Severity: ERROR

Intent¶

The cohort table is a RESULTS table with unique naming: - Uses subject_id (not person_id) to identify patients - This is the ONLY table in OMOP CDM that uses subject_id - All clinical tables use person_id for patient identity

The ONLY valid join is:
cohort.subject_id = clinical_table.person_id

How it works¶

This rule analyzes the SQL query to identify cohort to clinical table join validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM cohort c
    JOIN condition_occurrence co ON c.subject_id = co.condition_occurrence_id

Correct patterns:

SELECT
      c.subject_id,
      co.condition_occurrence_id,
      co.condition_concept_id
    FROM cohort c
    JOIN condition_occurrence co ON c.subject_id = co.person_id
    WHERE c.cohort_definition_id = 123
      AND co.condition_start_date >= c.cohort_start_date
      AND co.condition_start_date <= c.cohort_end_date

Common scenarios¶

• Joining subject_id to primary keys (condition_occurrence_id, etc.)
• Structurally invalid (patient ID ≠ event ID)
• Using person_id from cohort table

Suggested fix¶

Use: cohort.subject_id = clinical.person_id or cohort.subject_id = person.person_id and person.person_id = clinical.person_id

8. Concept Alias Reuse Validation¶

Rule ID: joins.concept_alias_reuse_validation Severity: ERROR

Intent¶

OMOP clinical tables have both standard concept_id columns (e.g., condition_concept_id) and source concept_id columns (e.g., condition_source_concept_id). When you need to join to concept for BOTH, you must use separate aliases. Reusing the same alias causes:

1. Ambiguous references: Which c.concept_id does the ON clause refer to?
2. Last-join-wins: Second JOIN overwrites/conflicts with first JOIN
3. Wrong data returned: You get source when you wanted standard (or vice versa)
4. Silent errors: SQL doesn't error, but results are semantically incorrect

How it works¶

This rule analyzes the SQL query to identify concept alias reuse validation patterns.

Examples¶

Violation patterns:

SELECT c.concept_name
    FROM condition_occurrence co
    JOIN concept c ON co.condition_concept_id = c.concept_id
    JOIN concept c ON co.condition_source_concept_id = c.concept_id

Correct patterns:

SELECT c1.concept_name AS standard_name, c2.concept_name AS source_name
    FROM condition_occurrence co
    JOIN concept c1 ON co.condition_concept_id = c1.concept_id
    JOIN concept c2 ON co.condition_source_concept_id = c2.concept_id

Suggested fix¶

None

9. Concept Ancestor Name Resolution Validation¶

Rule ID: joins.concept_ancestor_name_resolution Severity: ERROR

Intent¶

concept_ancestor has two concept_id columns: - ancestor_concept_id: The parent/higher-level concept - descendant_concept_id: The child/lower-level concept

When joining to the concept table to retrieve concept_name, the join column
determines WHICH concept's name you get.

How it works¶

This rule analyzes the SQL query to identify concept ancestor name resolution validation patterns.

Examples¶

Violation patterns:

SELECT c.concept_name AS descendant_name
    FROM concept_ancestor ca
    JOIN concept c ON ca.ancestor_concept_id = c.concept_id

Correct patterns:

SELECT c.concept_name AS descendant_name
    FROM concept_ancestor ca
    JOIN concept c ON ca.descendant_concept_id = c.concept_id
    WHERE ca.ancestor_concept_id = 201826

Common scenarios¶

• Aliasing as "descendant_name" but joining on ancestor_concept_id
• Returns the parent concept's name, not the descendant's
• Aliasing as "ancestor_name" but joining on descendant_concept_id

Suggested fix¶

Use descendant_concept_id for descendant values and ancestor_concept_id for ancestor values.

10. Concept Join Validation¶

Rule ID: joins.concept_join_validation Severity: ERROR

Intent¶

The concept table has a primary key (concept_id) and several descriptive columns (concept_name, concept_code, vocabulary_id, domain_id, etc.). Joining on descriptive columns instead of the primary key causes:

1. Non-unique matches: concept_name is not unique, causing cartesian joins
2. String matching issues: Case sensitivity, trailing spaces, encoding
3. Performance: String joins are much slower than integer joins
4. Semantic incorrectness: Foreign keys should reference primary keys

How it works¶

This rule analyzes the SQL query to identify concept join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM drug_exposure de
    JOIN concept c ON de.drug_source_value = c.concept_name

Correct patterns:

SELECT * FROM drug_exposure de
    JOIN concept c ON de.drug_concept_id = c.concept_id

Suggested fix¶

None

12. Concept Relationship Requires Relationship ID Filter¶

Rule ID: joins.concept_relationship_requires_relationship_id Severity: ERROR

Intent¶

Each use of concept_relationship must filter on relationship_id to avoid cross-product joins across multiple relationship types.

How it works¶

This rule analyzes the SQL query to identify concept relationship requires relationship id filter patterns.

Examples¶

Suggested fix¶

Add a filter on relationship_id for each concept_relationship alias. Example: cr.relationship_id = 'Maps to'

13. Concept Relationship to Concept Join Validation¶

Rule ID: joins.concept_relationship_concept_join_validation Severity: ERROR

Intent¶

concept_relationship has two concept_id columns: - concept_id_1: The source/origin concept (what you're mapping FROM) - concept_id_2: The target/destination concept (what you're mapping TO)

When joining to the concept table twice to retrieve names for both concepts,
developers often swap the join columns, causing:
- The "source" alias to actually show the target concept's name
- The "target" alias to actually show the source concept's name
- Completely reversed mapping semantics

How it works¶

This rule analyzes the SQL query to identify concept relationship to concept join validation patterns.

Examples¶

Violation patterns:

SELECT
      c_source.concept_name AS source_name,
      c_target.concept_name AS target_name
    FROM concept_relationship cr
    JOIN concept c_source ON cr.concept_id_2 = c_source.concept_id
    JOIN concept c_target ON cr.concept_id_1 = c_target.concept_id

Correct patterns:

SELECT
      c_source.concept_name AS source_name,
      c_target.concept_name AS target_name
    FROM concept_relationship cr
    JOIN concept c_source ON cr.concept_id_1 = c_source.concept_id
    JOIN concept c_target ON cr.concept_id_2 = c_target.concept_id

Common scenarios¶

• Aliasing as "c_source" but joining on concept_id_2
• Returns the target concept's name, not the source
• Aliasing as "c_target" but joining on concept_id_1

Suggested fix¶

Use concept_id_1 for source concepts and concept_id_2 for target concepts.

14. Concept Relationship to Relationship Join Validation¶

Rule ID: joins.concept_relationship_relationship_join_validation Severity: ERROR

Intent¶

The relationship table is a reference table in OMOP that describes relationship types (e.g., 'Maps to', 'Subsumes', 'Is a', 'Has form'). The concept_relationship table references relationship via the relationship_id column (VARCHAR FK to relationship.relationship_id).

How it works¶

This rule analyzes the SQL query to identify concept relationship to relationship join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM concept_relationship cr
    JOIN relationship r ON cr.concept_id_1 = r.relationship_concept_id

Correct patterns:

SELECT * FROM concept_relationship cr
    JOIN relationship r ON cr.relationship_id = r.relationship_id

Common scenarios¶

• Joining concept_id_1 or concept_id_2 (INTEGER) to relationship_concept_id (INTEGER)
• Type matches but semantics are wrong
• concept_id_1/2 are the concepts being related, not the relationship type

Suggested fix¶

Use: concept_relationship.relationship_id = relationship.relationship_id

15. Concept Synonym Join Validation¶

Rule ID: joins.concept_synonym_join_validation Severity: ERROR

Intent¶

The concept_synonym table provides alternative names for concepts: - concept.concept_id = 123 → "Type 2 diabetes mellitus" (primary name) - concept_synonym.concept_id = 123, concept_synonym_name = "Diabetes mellitus type 2" - concept_synonym.concept_id = 123, concept_synonym_name = "T2DM"

Joining on name strings is unreliable because:
1. Names are not unique identifiers
2. Synonyms may match concept names from different concepts
3. String matching is error-prone (case sensitivity, whitespace)

How it works¶

This rule analyzes the SQL query to identify concept synonym join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM concept_synonym cs
    JOIN concept c ON cs.concept_synonym_name = c.concept_name

Correct patterns:

SELECT * FROM concept_synonym cs
    JOIN concept c ON cs.concept_id = c.concept_id

Suggested fix¶

Join concept_synonym to concept using concept_id: concept_synonym.concept_id = concept.concept_id

16. Concept to Concept Class Join Validation¶

Rule ID: joins.concept_concept_class_join_validation Severity: ERROR

Intent¶

The concept_class table is a reference table in OMOP that describes concept classes (e.g., 'Clinical Drug', 'Ingredient', 'Procedure', 'Clinical Finding'). The concept table references concept_class via the concept_class_id column (VARCHAR FK to concept_class.concept_class_id).

How it works¶

This rule analyzes the SQL query to identify concept to concept class join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM concept c
    JOIN concept_class cc ON c.concept_id = cc.concept_class_concept_id

Correct patterns:

SELECT * FROM concept c
    JOIN concept_class cc ON c.concept_class_id = cc.concept_class_id

Common scenarios¶

• Joining concept_id (INTEGER) to concept_class_concept_id (INTEGER)
• Type matches but semantics are wrong
• Joining concept_class_id to concept_class_name

Suggested fix¶

None

17. Concept to Domain Join Validation¶

Rule ID: joins.concept_domain_join_validation Severity: ERROR

Intent¶

The domain table is a reference table in OMOP that describes domains (e.g., 'Condition', 'Drug', 'Procedure', 'Measurement'). The concept table references domain via the domain_id column (VARCHAR FK to domain.domain_id).

How it works¶

This rule analyzes the SQL query to identify concept to domain join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM concept c
    JOIN domain d ON c.concept_id = d.domain_concept_id

Correct patterns:

SELECT * FROM concept c
    JOIN domain d ON c.domain_id = d.domain_id

Common scenarios¶

• Joining concept_id (INTEGER) to domain_concept_id (INTEGER)
• Type matches but semantics are wrong
• Joining domain_id to domain_name

Suggested fix¶

None

18. Concept to Vocabulary Join Validation¶

Rule ID: joins.concept_vocabulary_join_validation Severity: ERROR

Intent¶

The vocabulary table is a reference table in OMOP that describes vocabularies (e.g., 'SNOMED', 'ICD10CM', 'RxNorm'). The concept table references vocabulary via the vocabulary_id column (VARCHAR FK to vocabulary.vocabulary_id).

How it works¶

This rule analyzes the SQL query to identify concept to vocabulary join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM concept c
    JOIN vocabulary v ON c.concept_id = v.vocabulary_concept_id

Correct patterns:

SELECT * FROM concept c
    JOIN vocabulary v ON c.vocabulary_id = v.vocabulary_id

Common scenarios¶

• Joining concept_id (INTEGER) to vocabulary_concept_id (INTEGER)
• Type matches but semantics are wrong
• Joining vocabulary_id to vocabulary_name

Suggested fix¶

None

19. Cost Table Domain Validation¶

Rule ID: joins.cost_table_domain_validation Severity: ERROR

Intent¶

Without the domain filter, a join can produce incorrect results because cost_event_id is a polymorphic foreign key that can reference different tables.

Example: drug_exposure_id = 123 and procedure_occurrence_id = 123 are DIFFERENT
events, but without cost_domain_id filter, both would match cost_event_id = 123.

How it works¶

This rule analyzes the SQL query to identify cost table domain validation patterns.

Examples¶

Violation patterns:

SELECT * FROM cost c
    JOIN drug_exposure de ON c.cost_event_id = de.drug_exposure_id

Correct patterns:

SELECT * FROM cost c
    JOIN drug_exposure de ON c.cost_event_id = de.drug_exposure_id
    WHERE c.cost_domain_id = 'Drug'

Suggested fix¶

Add cost.cost_domain_id = '' matching the joined clinical table.

20. Death to Visit Occurrence Join Validation¶

Rule ID: joins.death_visit_occurrence_join_validation Severity: ERROR

Intent¶

The death table has a unique structure with person_id as both primary key and the ONLY foreign key to other clinical tables. It has NO visit_occurrence_id, provider_id, or care_site_id columns.

The ONLY valid join is:
death.person_id = visit_occurrence.person_id

How it works¶

This rule analyzes the SQL query to identify death to visit occurrence join validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM death d
    JOIN visit_occurrence vo ON d.death_date = vo.visit_end_date

Correct patterns:

SELECT
      d.person_id,
      vo.visit_occurrence_id,
      d.death_date,
      vo.visit_end_date
    FROM death d
    JOIN visit_occurrence vo ON d.person_id = vo.person_id
    WHERE d.death_date = vo.visit_end_date  -- Temporal filter in WHERE

Common scenarios¶

• Temporal joins using dates (death_date = visit_end_date)
• Structurally invalid even if temporally meaningful
• Temporal correlations should use WHERE clause, not JOIN ON

Suggested fix¶

Use: death.person_id = visit_occurrence.person_id

21. Drug Exposure to Drug Strength Join Validation¶

Rule ID: joins.drug_exposure_drug_strength_join_validation Severity: ERROR

Intent¶

The drug_strength table is a vocabulary table that contains strength information for drug formulations. It has NO clinical event columns like drug_exposure_id, person_id, or visit_occurrence_id.

The ONLY valid join is:
drug_exposure.drug_concept_id = drug_strength.drug_concept_id

How it works¶

This rule analyzes the SQL query to identify drug exposure to drug strength join validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM drug_exposure de
    JOIN drug_strength ds ON de.drug_exposure_id = ds.drug_concept_id

Correct patterns:

SELECT
      de.drug_exposure_id,
      ds.amount_value,
      ds.amount_unit_concept_id
    FROM drug_exposure de
    JOIN drug_strength ds ON de.drug_concept_id = ds.drug_concept_id
    WHERE ds.invalid_reason IS NULL

Common scenarios¶

• Joining drug_exposure_id to drug_concept_id
• drug_strength has no drug_exposure_id column
• Joining person_id to drug_concept_id

Suggested fix¶

Use: drug_exposure.drug_concept_id = drug_strength.drug_concept_id

22. Era Table Forbidden Join Validation¶

Rule ID: joins.era_forbidden_join_validation Severity: ERROR

Intent¶

Era tables are DERIVED/AGGREGATED tables built from event tables: - condition_era is derived from condition_occurrence - drug_era is derived from drug_exposure - dose_era is derived from drug_exposure

They represent continuous time periods, not discrete clinical events.
Era tables have NO foreign keys to visit, provider, or care_site.

They ONLY have:
- person_id (FK to person)
- *_concept_id (FK to concept)

Any join to visit/provider/care_site is semantically impossible.

How it works¶

This rule analyzes the SQL query to identify era table forbidden join validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM drug_era de
    JOIN visit_occurrence vo ON de.person_id = vo.person_id

Correct patterns:

SELECT *
    FROM drug_exposure de  -- NOT drug_era
    JOIN visit_occurrence vo ON de.visit_occurrence_id = vo.visit_occurrence_id

SELECT *
    FROM drug_era de
    JOIN person p ON de.person_id = p.person_id
    JOIN concept c ON de.drug_concept_id = c.concept_id

Suggested fix¶

Do not join era tables with visit/provider/care_site. Use event tables (condition_occurrence, drug_exposure) for visit-level analysis.

23. Fact Relationship Polymorphic Join Validation¶

Rule ID: joins.fact_relationship_join_validation Severity: ERROR

Intent¶

fact_relationship links ANY two facts across the entire OMOP CDM. Without domain filtering, ID values collide across tables: - measurement_id = 123 (in measurement table) - condition_occurrence_id = 123 (in condition_occurrence table) - procedure_occurrence_id = 123 (in procedure_occurrence table)

These are COMPLETELY DIFFERENT clinical events!

How it works¶

This rule analyzes the SQL query to identify fact relationship polymorphic join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM fact_relationship fr
    JOIN measurement m ON fr.fact_id_1 = m.measurement_id

Correct patterns:

SELECT * FROM fact_relationship fr
    JOIN measurement m ON fr.fact_id_1 = m.measurement_id
    WHERE fr.domain_concept_id_1 = 21  -- 21 = Measurement domain

Suggested fix¶

Add domain filter matching the clinical table, e.g.: WHERE domain_concept_id_1 =

24. Join Path Validation¶

Rule ID: joins.join_path_validation Severity: WARNING

Intent¶

When a query JOINs concept (or concept_relationship) to a clinical fact table, the join's ON-clause must connect them via concept_id (not domain_id, vocabulary_id, or any other column). A wrong-key join produces an implicit cross-join or silently empty results with no database error.

How it works¶

The rule fires when:

1. The query references a clinical fact table's standard concept field (condition_concept_id, drug_concept_id, etc.), AND
2. The query references concept or concept_relationship, AND
3. None of the recognized linkage patterns are present:
4. Direct JOIN: clinical.<x>_concept_id = concept.concept_id.
5. Implicit (comma) join with the same equality in WHERE.
6. Indirect bridge through a CTE that selects a concept_id column from a vocabulary source.
7. JOIN with unqualified concept_id in the ON clause (when concept is the join target).

Suppression for subquery-only usage. When concept (or concept_relationship) appears only inside a Subquery node — never in the outer query's FROM/JOIN list — the rule suppresses. Examples of suppressed shapes:

-- Scalar lookup
WHERE p.gender_concept_id = (SELECT concept_id FROM concept WHERE concept_name = 'Female' AND domain_id = 'Gender')

-- IN-subquery filter
WHERE de.drug_concept_id IN (SELECT concept_id FROM concept WHERE vocabulary_id = 'RxNorm' AND standard_concept = 'S')

-- EXISTS subquery
WHERE EXISTS (SELECT 1 FROM concept c WHERE c.concept_id = p.gender_concept_id)

In these shapes there's no JOIN to validate; the relevant data-quality concerns (deprecated concepts, trailing whitespace in concept_name, ambiguous multi-row lookups) are covered by other rules: invalid_reason_enforcement, concept_name_whitespace, and standard_concept_enforcement. Suppressing here avoids issuing a join-shaped warning on a non-join query.

Examples¶

Violation pattern (wrong join key):

SELECT co.person_id
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.domain_id;  -- wrong: should be c.concept_id

Correct pattern:

SELECT co.person_id
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id;

Suggested fix¶

JOIN concept ON <clinical_table>.<x>_concept_id = concept.concept_id — connect the clinical fact's concept_id slot to concept.concept_id directly.

25. Left Join Then Where On Right Table¶

Rule ID: joins.left_join_then_where_on_right_table Severity: WARNING

Intent¶

LEFT JOIN semantics: - Returns ALL rows from the left table - Matched rows from right table have values - Unmatched rows from right table have NULL values

When a WHERE clause filters on a right table column with non-NULL conditions:
- Rows where right table column is NULL are filtered out
- This defeats the purpose of LEFT JOIN
- Effectively converts LEFT JOIN to INNER JOIN
- Developer likely intended INNER JOIN or should move filter to JOIN ON

This is a common SQL anti-pattern that produces unexpected results.

How it works¶

This rule analyzes the SQL query to identify left join then where on right table patterns.

Examples¶

Violation patterns:

SELECT co.* FROM condition_occurrence co
    LEFT JOIN visit_occurrence vo ON co.visit_occurrence_id = vo.visit_occurrence_id
    WHERE vo.visit_concept_id = 9201

SELECT p.* FROM person p
    LEFT JOIN location l ON p.location_id = l.location_id
    WHERE l.state = 'CA' AND l.city = 'Los Angeles'

Correct patterns:

SELECT co.* FROM condition_occurrence co
    JOIN visit_occurrence vo ON co.visit_occurrence_id = vo.visit_occurrence_id
    WHERE vo.visit_concept_id = 9201

SELECT co.* FROM condition_occurrence co
    LEFT JOIN visit_occurrence vo ON co.visit_occurrence_id = vo.visit_occurrence_id
    WHERE vo.visit_occurrence_id IS NULL

Common scenarios¶

• Using LEFT JOIN when INNER JOIN is needed
• Filtering right table columns in WHERE instead of JOIN ON
• Not understanding LEFT JOIN NULL behavior

Suggested fix¶

Use INNER JOIN or move filter into JOIN ON clause.

26. Maps To Direction¶

Rule ID: joins.maps_to_direction Severity: WARNING

Intent¶

OMOP semantic rule: Verify that 'Maps to' relationship is used in the correct direction: - concept_id_1 should be the source concept - concept_id_2 should be the standard concept

How it works¶

This rule analyzes the SQL query to identify maps to direction patterns.

Examples¶

Suggested fix¶

Use concept_id_1 for source, concept_id_2 for standard concept

27. Note NLP to Note Join Validation¶

Rule ID: joins.note_nlp_note_join_validation Severity: ERROR

Intent¶

The note_nlp table contains NLP-extracted entities from clinical notes. It's a vocabulary-like extension table that has NO direct patient context columns (no person_id, visit_occurrence_id, provider_id).

The ONLY valid join is:
note_nlp.note_id = note.note_id

How it works¶

This rule analyzes the SQL query to identify note nlp to note join validation patterns.

Examples¶

Violation patterns:

SELECT *
    FROM note_nlp nn
    JOIN note n ON nn.note_nlp_id = n.note_id

Correct patterns:

SELECT
      nn.note_nlp_id,
      nn.lexical_variant,
      n.note_text,
      n.person_id
    FROM note_nlp nn
    JOIN note n ON nn.note_id = n.note_id
    WHERE nn.note_nlp_concept_id = 4329847

Common scenarios¶

• Joining note_nlp_id (PK) to note_id (FK)
• Semantically backwards (like joining person_id to drug_concept_id)
• Trying to join note_nlp directly to person/visit

Suggested fix¶

Use: note_nlp.note_id = note.note_id

28. Payer Plan Period Join Validation¶

Rule ID: joins.payer_plan_period_join_validation Severity: WARNING

Intent¶

A patient can have multiple insurance coverage periods over time: - person_id = 12345, coverage from 2020-01-01 to 2020-12-31 - person_id = 12345, coverage from 2021-01-01 to 2022-06-30 - person_id = 12345, coverage from 2022-07-01 to 2024-12-31

If you join only on person_id, a drug exposure on 2021-06-15 will match
ALL THREE insurance periods, not just the active one!

How it works¶

This rule analyzes the SQL query to identify payer plan period join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM drug_exposure de
    JOIN payer_plan_period pp ON de.person_id = pp.person_id

Correct patterns:

SELECT * FROM drug_exposure de
    JOIN payer_plan_period pp
      ON de.person_id = pp.person_id
      AND de.drug_exposure_start_date BETWEEN
          pp.payer_plan_period_start_date AND pp.payer_plan_period_end_date

Suggested fix¶

Add temporal overlap: clinical_date BETWEEN payer_plan_period_start_date AND payer_plan_period_end_date

29. Person ID Join Validation¶

Rule ID: joins.person_id_join_validation Severity: ERROR

Intent¶

Even if numeric values overlap (person_id=123, visit_occurrence_id=123 both exist), they represent completely different entities: - person_id = 123 → Patient identifier - visit_occurrence_id = 123 → Visit identifier

These are unrelated despite having the same number.

How it works¶

This rule analyzes the SQL query to identify person id join validation patterns.

Examples¶

Violation patterns:

SELECT * FROM condition_occurrence co
    JOIN visit_occurrence vo ON co.person_id = vo.visit_occurrence_id

Correct patterns:

SELECT * FROM condition_occurrence co
    JOIN visit_occurrence vo ON co.person_id = vo.person_id