Analyzing Clinical Data Warehouses

Analyzing Clinical Data Warehouses

Structures clinical data warehouse queries for quality measurement, research, and operational analytics using OMOP CDM, i2b2, PCORnet, and proprietary warehouse schemas.

Why This Skill Exists

Clinical data warehouses (CDWs) aggregate billions of patient records from EHRs, claims, registries, and lab systems. Analysts who query these warehouses without understanding data provenance, ETL transformations, or common data model semantics produce misleading results — misidentified cohorts, inflated denominators, duplicated encounters, or concept sets that silently exclude relevant patients. Wrong CDW output directly harms quality measurement (CMS Star Ratings, HEDIS), clinical trial feasibility, and population health interventions.

This skill encodes the analytical discipline required to extract defensible datasets. It covers schema selection, concept mapping, ETL validation, cohort definition, temporal logic, and output attestation — the full pipeline from warehouse query to actionable analytic artifact.

Checkpoint A: Pre-Query Intake (Mandatory)

Before writing any SQL or configuring any extract, the analyst must answer:

1. What is the business or research question in one declarative sentence?
2. Which CDW platform and version is the target (Epic Caboodle/Clarity, Cerner HealtheIntent, OMOP v5.4, i2b2, PCORnet CDM v6, custom)?
3. What date range and refresh cadence does the analysis require?
4. What patient population is in scope (age, payer, geography, clinical condition)?
5. Which outcome, exposure, or quality measure defines the primary variable?
6. Are there IRB/HIPAA limitations — limited dataset, de-identified, or identified access?
7. What is the target output format (flat file, dashboard feed, REDCap import, registry submission)?
8. Who is the downstream consumer (quality team, research PI, C-suite, payer)?

Documents to Request

• Data dictionary or schema documentation for the target CDW
• ETL specification or mapping document (source-to-target)
• IRB protocol or data use agreement (if research)
• Quality measure specification (NQF, HEDIS, CMS) if applicable
• Previous versions of the analytic query or report (if iteration)
• Concept set definitions or value set OIDs from VSAC
• Data quality report from most recent ETL refresh

Step 1: Schema Reconnaissance and Data Profiling

Identify the Common Data Model

Data Profiling Checklist

• Run row counts on core tables; compare to expected patient volume
• Check NULL rates on critical columns (visit_start_date, concept_id, person_id)
• Validate date ranges — confirm ETL includes the analysis window
• Profile concept_id distributions; flag unmapped (concept_id = 0 in OMOP) rows
• Confirm referential integrity (every condition_occurrence.person_id exists in person table)
• Examine the ETL log for load errors, truncation warnings, or skipped source rows
• Document the data refresh date — never present stale data as current

Step 2: Concept Mapping and Value Set Construction

Building Defensible Concept Sets

1. Start with the authoritative value set (VSAC OID for quality measures, study protocol appendix for research)
2. Map each source code to the CDM vocabulary — in OMOP, use the concept_relationship table to traverse hierarchies
3. Apply the INCLUDE_DESCENDANTS flag only when clinically justified (e.g., including all children of SNOMED "Diabetes mellitus" 73211009)
4. Manually review edge cases: "unspecified" codes, "history of" vs. active condition, laterality modifiers
5. Validate concept set against a known gold-standard cohort or chart-reviewed sample

Common Pitfalls

Step 3: Cohort Definition and Temporal Logic

Cohort Entry Criteria

Define each inclusion and exclusion criterion as a discrete, testable SQL predicate:

• Index event: The first qualifying occurrence (e.g., first inpatient visit with primary diagnosis of AMI)
• Lookback window: Period before index for baseline characteristics (typically 365 days)
• Clean window: Required observation time before index to confirm incident (not prevalent) cases
• Follow-up window: Required observation time after index for outcome ascertainment
• Washout period: Gap required between treatment episodes for new-user designs

Temporal Logic Rules

1. Use observation_period (OMOP) or ENROLLMENT (PCORnet) to confirm continuous enrollment
2. Never assume absence of a record means absence of a condition
3. Apply era-building logic for chronic conditions — OMOP provides condition_era, drug_era tables
4. Handle multiple qualifying index events consistently (first, last, or all)
5. Document the gap allowance for era construction (default 30 days for condition, persistence gap for drugs)

Step 4: Query Construction and ETL Validation

SQL Standards for CDW Queries

• Use CTEs (WITH clauses) for readability; name each CTE after its logical role (eligible_patients, index_events, outcomes)
• Never use SELECT * in production queries — enumerate columns explicitly
• Cast dates consistently; use DATE type, not DATETIME, unless time precision matters
• Apply explicit JOIN types (INNER, LEFT) — avoid implicit comma joins
• Comment each CTE with the criterion it implements and the expected row count range

ETL Validation Queries

Run these before trusting any extract:

1. Completeness: Compare CDW patient count to source system census for the same period
2. Conformance: Check that coded fields contain only valid vocabulary codes (no orphan concept_ids)
3. Plausibility: Verify that temporal ordering makes sense (no discharge before admission, no death before last visit)
4. Uniqueness: Confirm no duplicate primary keys in fact tables
5. Timeliness: Verify the most recent record date matches the documented ETL refresh date

Step 5: Output Attestation and Documentation

Required Output Metadata

Every analytic extract must include:

• Query execution date and CDW refresh date
• CDW platform, version, and vocabulary version
• Complete SQL or query logic (version-controlled)
• Row counts at each filtering step (attrition table)
• Concept sets used with all included codes
• Known data quality issues and their expected impact
• De-identification method applied (if applicable)

Attrition Table Format

Step 6: Common Quality Measure Patterns

HEDIS / CMS Quality Measure CDW Implementation

Research Cohort vs. Quality Measure Differences

Checkpoint B: Post-Extract Alignment (Mandatory)

Before releasing any analytic output:

1. Does the final cohort size fall within the expected range for the target population?
2. Have you reviewed a random sample of 20 patients against source charts or operational reports?
3. Is the attrition table complete with no unexplained large drops?
4. Have all concept sets been reviewed by a clinical subject matter expert?
5. Has the output been checked for PHI leakage (SSN, MRN, names) if de-identification was required?

Quality Audit

Guidelines

• Always query through the CDM vocabulary layer — never hard-code source codes in OMOP or i2b2 queries
• Treat every ETL as lossy until proven otherwise; validate before trusting transformed data
• Use the OHDSI Data Quality Dashboard or equivalent profiling tool on every new data source
• Document the exact vocabulary version and ETL date in every output artifact
• Never conflate prescribing data with dispensing data — they answer different clinical questions
• Build concept sets incrementally and review with a clinician; automated descendant expansion introduces false positives
• Apply the minimum necessary standard — extract only the columns and rows needed for the analysis
• When results seem too clean or too large, investigate before celebrating — CDW artifacts are more common than genuine signals