Extraction

Overview

Phase 1 derives stratified prevalence tables from the records in an OMOP CDM database. The main entry point is extract_all(), which runs the full pipeline for one or more clinical domains.

Each domain produces 4 tables:

Table	Content	Key columns
`*_prevalence`	Patient counts per concept x year x sex x age group	concept_id, year, sex, age_group, patient_count, denominator, prevalence
`*_info`	Concept metadata	concept_id, concept_name, vocabulary_id, n_patients_total
`*_chapters`	Hierarchy-based chapter assignments	concept_id, chapter_type, chapter_id, chapter_name
`*_attributes`	SNOMED relationship targets	concept_id, relationship, target_concept_id, target_concept_name

Plus two shared tables: demographics (birth year x sex) and death_counts (deaths by stratum).

Connecting to a database

Syrona connects through CDMConnector. A production OMOP CDM is normally a PostgreSQL database, so syrona_connect_pg() is the main entry point; syrona_connect() opens a local DuckDB file (for example an Eunomia or Synthea test dataset).

PostgreSQL database

library(syrona)

# Direct connection
db <- syrona_connect_pg(
  host = "db-server.example.com",
  dbname = "omop",
  user = "analyst",
  cdm_schema = "cdm",
  write_schema = "results_analyst"
)

# Via SSH tunnel (start tunnel first: ssh -L 5432:localhost:5432 user@server)
db <- syrona_connect_pg(
  host = "localhost",
  dbname = "omop",
  user = "analyst",
  cdm_schema = "ohdsi_cdm_202511",
  write_schema = "results_analyst"
)

Credentials. You can pass password = "..." directly, but it is cleaner to omit it and let RPostgres read the password from a ~/.pgpass file or the PGPASSWORD environment variable — for example, set PGPASSWORD=... in your ~/.Renviron. This keeps the password out of your R scripts and command history.

The write_schema parameter tells CDMConnector where it can create temporary tables. This is required for cohort operations and some extraction queries. On PostgreSQL, this is typically a user-specific results schema.

DuckDB (local files)

# Read-only (default) - safe for shared databases
db <- syrona_connect("path/to/omop.duckdb")

# Writable - needed if you want to create cohort tables in the same DB
db <- syrona_connect("path/to/omop.duckdb", read_only = FALSE)

Running extraction

All domains (default)

tables <- extract_all("Dataset_A", db = db)

This extracts conditions, procedures, and drugs. Results are saved to data/sources/Dataset_A/ and returned as a named list.

Single domain

# Conditions only (fastest)
tables <- extract_all("Dataset_A", db = db, domains = "conditions")

# Drugs only
tables <- extract_all("Dataset_A", db = db, domains = "drugs")

# Conditions + procedures (no drugs)
tables <- extract_all("Dataset_A", db = db, domains = c("conditions", "procedures"))

Shorthand: pass a DuckDB path directly

# This connects, extracts, and disconnects automatically
tables <- extract_all("Synthetic", db = "path/to/omop.duckdb")

What each extractor does

Denominators (ACHILLES-116)

extract_denominators() computes the number of persons observed per year x sex x age group. This is the denominator for all prevalence calculations. A person is counted in a year if their observation period overlaps that year.

Age groups are 10-year decades (0-9, 10-19, …, 70-79, 80+). Ages above 80 are clamped into a single group for statistical stability.

Condition prevalence (ACHILLES-404)

extract_condition_prevalence() counts persons with at least one condition occurrence per concept x year x sex x age group. Events must fall within the person’s observation period. Only standard SNOMED concepts (concept_id != 0) are included.

Condition chapters

extract_condition_chapters() assigns each condition concept to chapters via three classification systems:

body_system - SNOMED hierarchy from “Disorder of body system” (L1 + L2)
disease_category - SNOMED hierarchy from “Disease” (L1 + L2)
icd10_chapter - ICD-10 chapters via SNOMED-to-ICD-10 mapping (code prefix matching)

A concept can belong to multiple chapters. Concepts without an ICD-10 mapping get an “(Unmapped)” pseudo-chapter.

Drug prevalence

extract_drug_prevalence() rolls up drug exposures to the Ingredient level via concept_ancestor. This means a prescription for “Aspirin 100mg tablet” counts toward the “Aspirin” ingredient. One row per ingredient x year x sex x age group.

Drug chapters

extract_drug_chapters() assigns each ingredient to ATC 1st level chapters (e.g. “A. Alimentary tract and metabolism”) via concept_ancestor.

Procedure chapters

extract_procedure_chapters() assigns procedures to two SNOMED hierarchies:

by_method - from “Procedure by method” (L1 + L2)
by_site - from “Procedure by site” (L1 + L2)

k-Anonymity

After extraction, apply_k_anonymity() suppresses small-cell counts (default k=5):

Concepts with fewer than k total patients are dropped entirely
Individual prevalence rows (strata) with fewer than k patients are suppressed
Concepts that lose all prevalence rows to suppression are moved to a *_rare table (keeps aggregate counts but no stratified data)

This ensures no individual can be identified from the output tables.