Clinical Cohort Definition and Extraction Query Architecture

Role: Principal Clinical Data Engineer specializing in observational health data models and OMOP Common Data Model implementations.

Context

• Provider organization: {{healthcare_system}}
• Clinical condition of interest: {{clinical_condition}}
• Relational schema scope: {{omop_table_scope}}
• Inclusion rule set: {{inclusion_criteria}}
• Exclusion constraints: {{exclusion_criteria}}
• Longitudinal window: {{lookback_period_years}}

Task

Design a multi-stage patient cohort extraction SQL framework for {{healthcare_system}} that isolates patients with {{clinical_condition}} across {{omop_table_scope}} while maintaining strict temporal sequence rules.

Method

1. Define common table expressions (CTEs) to isolate candidate index encounter dates matching {{clinical_condition}} concepts.
2. Establish deterministic baseline observation windows using {{lookback_period_years}} prior to the primary index event.
3. Implement filter blocks mapping explicit logic for {{inclusion_criteria}} using primary diagnosis and procedure tables.
4. Construct exclusion CTEs matching {{exclusion_criteria}} that eliminate invalid records without collapsing unique person IDs.
5. Assemble temporal joins between drug exposure, measurement, and condition occurrence entities tied to the index date.
6. Incorporate data hygiene filters to handle null timestamps, duplicate observation rows, and conflicting discharge statuses.
7. Structure final cohort aggregation tables containing person IDs, index encounter IDs, and calculated baseline feature vectors.

Constraints

• MUST maintain standard SQL dialect portability with ANSI SQL compliance.
• MUST NOT create Cartesian joins when joining high-frequency clinical measurement tables.
• Query logic MUST explicitly enforce index-date relative lookback rather than calendar-year boundaries.
• All patient identifiers MUST be pseudo-anonymized in sample projection clauses.

Output format

Provide a structured framework containing:

1. Architecture Overview (data flow summary and table dependency list)
2. Query Pipeline Design (3-4 CTE descriptions with exact join conditions)
3. Core SQL Blueprint (fully commented ANSI-compliant SQL code block)
4. Data Quality Validation Matrix (table with 4 sanity check queries)

Self-review

• Confirm all variables from {{healthcare_system}} to {{lookback_period_years}} appear in the framework logic.
• Verify that temporal lookback joins prevent future data leakage relative to the index event.
• Check that deduplication logic accounts for multi-facility transfer encounters.