omop-duck-db

Tools for building an OMOP Common Data Model (CDM) v5.4 database in DuckDB, loading the OHDSI Athena vocabulary, and mapping PCORnet-format source data into the CDM. Provided as both an R package and a Python package, with parallel ETL implementations that share the same schema and concept-mapping SQL, so a human (or an AI agent) picking either language gets the same result.

Scope

This repo provides general-purpose mapping/ETL tooling and a small fabricated sample fixture for testing – it does not itself ingest, store, or process any protected or identifiable data. Real-world use against production source data happens outside this repo, against your own data infrastructure.

Layout

• inst/extdata/5.4/duckdb/*.sql — OMOP CDM v5.4 DDL, generated once via the CommonDataModel R package. This is the schema source of truth for both the R and Python ETL paths — neither re-declares CREATE TABLE by hand.
• inst/sql/mapping_macros.sql — DuckDB SQL macros for source-code → standard-concept resolution, shared by both ETLs.
• R/ — the R package: schema build, vocabulary load/refresh, PCORnet ETL, Google Drive snapshot sync, BigQuery live connector.
• inst/scripts/ — thin command-line wrappers around the R package’s functions, for scripting/automation use without writing R code.
• python/omop_etl/ — the Python package (build_omop_cdm.py), mirrors the R package table-for-table.
• tests/fixtures/pcornet_sample/ — small fabricated sample rows used to smoke-test both ETLs against each other.
• config/ — non-secret config templates (*.yml.example). Copy to *.yml and fill in locally; the real files are gitignored.

The generated database file and any vocabulary download archive are not committed to git — they’re large binaries. See “Large files” below.

Installation

R:

# install.packages("remotes")
remotes::install_github("jkylearmstrong/omop-duck-db")

Python:

pip install "omop-duck-db @ git+https://github.com/jkylearmstrong/omop-duck-db"

Example

A schema builds into a fresh, empty DuckDB file with a single call:

library(omopduckdb)

db_path <- tempfile(fileext = ".duckdb")
build_schema(db_path)
#> Schema built at C:\Users\jkyle\AppData\Local\Temp\RtmpUxRe4v\file158ac510dca8.duckdb

con <- DBI::dbConnect(duckdb::duckdb(), db_path, read_only = TRUE)
DBI::dbListTables(con)[1:10]
#>  [1] "care_site"            "cdm_source"           "cohort"              
#>  [4] "cohort_definition"    "concept"              "concept_ancestor"    
#>  [7] "concept_class"        "concept_relationship" "concept_synonym"     
#> [10] "condition_era"
DBI::dbDisconnect(con, shutdown = TRUE)

From there, load_vocabulary() loads a vocabulary download, and etl_pcornet() maps a PCORnet-format source directory into the CDM tables just created — see “Building the database” below for the full workflow, or inst/scripts/ for command-line equivalents of each step.

Building the database

1. Generate the CDM v5.4 schema and load the vocabulary:

   library(omopduckdb)
   build_schema("omop_cdm.duckdb")
   load_vocabulary("path/to/vocabulary_download", "omop_cdm.duckdb")
   

2. Map a source directory into the CDM, with either implementation (they read the same source layout and produce equivalent output):

   etl_pcornet("path/to/source_extract", "omop_cdm.duckdb")
   

   python -m omop_etl.build_omop_cdm --source-dir path/to/source_extract
   

Command-line equivalents of the R functions live in inst/scripts/, e.g. Rscript inst/scripts/etl_pcornet.R --source-dir path/to/source_extract.

Refreshing the vocabulary

• From a new download: load_vocabulary("path/to/new_download", "omop_cdm.duckdb"). Each table is truncated and reloaded inside its own transaction with a row-count sanity check, so a bad/partial file can only fail that one table (rolled back, left untouched), not half-clobber the rest of the vocabulary.

• Live, from BigQuery (e.g. a public or your own OMOP-shaped dataset): load_from_bigquery(billing_project = "...", dataset = "...", tables = c("concept", "concept_ancestor", "concept_relationship")). A GCP project ID is required for query billing/quota even against public datasets (set one in config/bigquery.yml, copied from config/bigquery.yml.example).

  First-time auth: the BigQuery OAuth flow needs an interactive R session to open a browser — it fails silently if run non-interactively before a token exists. Run this once, in an interactive session (not Rscript), and approve the browser prompt:

  bigrquery::bq_auth()
  

  The resulting token is cached and reused by later non-interactive runs.

Custom mapping and retroactive remapping

When standard Athena vocabularies lack mappings for site-specific or unmapped codes:

1. Audit unmapped codes:

   export_unmapped_codes("omop_cdm.duckdb", "condition_occurrence", "unmapped_conditions.csv")
   

2. Import custom crosswalks or Usagi exports:

   # Load custom source_to_concept_map CSV
   import_source_to_concept_map("mappings.csv", "omop_cdm.duckdb")
   
   # Or import OHDSI Usagi review export CSV
   import_usagi_mappings("usagi_reviewed.csv", "omop_cdm.duckdb")
   

3. Retroactively remap populated CDM tables:

   # Preview changes without modifying data (dry run)
   remap_cdm_table("condition_occurrence", "omop_cdm.duckdb", dry_run = TRUE)
   
   # Apply remapping across all tables and rebuild condition/drug eras
   remap_all("omop_cdm.duckdb")
   

Equivalent functions are available in Python via from omop_etl import remap_all, import_usagi_mappings.

Testing

tests/fixtures/pcornet_sample/ is a small set of fabricated (not real) sample rows used to smoke-test both ETLs and check they produce identical output.

R: devtools::test() (or R CMD check) runs the testthat suite.

Python: pytest runs the Python-side tests, including a cross-language parity check that runs both ETLs against the fixture and diffs every row (skipped automatically if R isn’t available on the machine running the tests).

Large files (Google Drive)

Instead of git/git-LFS, the generated database and vocabulary archive can be synced through Google Drive:

gdrive_upload_snapshot()    # push the current database + vocabulary archive
gdrive_download_latest()    # pull the latest snapshot instead of rebuilding

Configure the target Drive folder in config/gdrive.yml (copy from config/gdrive.yml.example).

Known limitations

• Athena vocabulary downloads do not include CPT4 concept names (AMA copyright) — a separate licensed tool (bundled with the download) populates them if you have a UMLS license. Until that’s run, CPT4-coded procedures map to concept_id 0.
• A handful of source fields (e.g. admission source) aren’t mapped yet; see inline comments in the ETL code for specifics.
• The Python side doesn’t yet have its own BigQuery connector — use the R connector for that, either way, into the same database file.