Skip to content

Source & CDM drivers

The ETL Engine ships with five drivers. Every driver works on either side — source, CDM, or both. Source and CDM choices are independent: SQL Server in / PostgreSQL out is just as valid as CSV → CSV.

CSV

The CSV driver reads a directory of files. The connection string is the path to the directory:

source database {
  driver: "csv"
  connection string: "/etc/etl-engine.d/db/"
}

CSV is the simplest driver to set up and is a good choice for one-shot migrations and the initial development cycle. Each .csv file in the directory becomes a source table; on the target side, each CDM table is written to its own CSV file.

On the target side the CSV driver runs the full CDM lifecycle, the same as the RDBMS drivers: create omop cdm pre-creates each CDM file with the canonical OMOP header, clean omop data resets each file back to that header (the file-level equivalent of SQL TRUNCATE), and a single CDM table may be written by several etl_map steps that each append their own columns. A CSV → CSV run therefore behaves like CSV → PostgreSQL apart from where the rows land — useful for testing a mapping or building a portable extract with the exact CDM shape the database target would produce.

The derived-table automations — observation_period, condition_era, drug_era and dose_era — run against a CSV target too (drug_era and dose_era read the Athena vocabulary CSVs directly), and so does add preceding visit ids — on a CSV target it rewrites the produced visit_occurrence / visit_detail files in place. No post automation requires a database target. See automation → post block.

MariaDB / MySQL

The MariaDB driver speaks the MariaDB and MySQL wire protocol and uses the key-value connection string described in cdm database.

cdm database {
  driver: "mariadb"
  connection string: "user=omop dbname=omop_cdm host=db.example.com password=secret"
}

MariaDB does not support schemas, so there is no options key for setting a search path.

PostgreSQL (native)

The native PostgreSQL driver uses the standard libpq client and the same key-value connection-string format. It supports an options key, forwarded to the PostgreSQL client — typically --search_path= to select a CDM schema:

cdm database {
  driver: "postgresql"
  connection string: "user=postgres host=db.example.com port=5432 dbname=postgres options='--search_path=omop_cdm' password=secret"
}

PostgreSQL over ODBC (psql-odbc)

The same PostgreSQL backend, accessed through ODBC. Use this when your environment standardises on ODBC for all RDBMS connectivity, or when you need an ODBC-only driver path through a corporate ODBC gateway. Connection-string format follows ODBC conventions (semicolon-separated key/value pairs):

cdm database {
  driver: "psql-odbc"
  connection string: "Driver={PostgreSQL Unicode};Server=db.example.com;Port=5432;Database=omop;Uid=etl;Pwd=secret"
}

The shipped container bundles a PostgreSQL ODBC driver, so no host-side ODBC configuration is required when running the engine inside the container.

For most deployments the native PostgreSQL driver is the better choice — it's faster (no ODBC round-trip), handles options in the expected libpq format, and is the path most heavily exercised by the test suite.

SQL Server

The SQL Server driver also speaks ODBC. The connection string uses ODBC conventions:

cdm database {
  driver: "sqlserver"
  connection string: "Driver={ODBC Driver 18 for SQL Server};Server=sqlserver.example.com,1433;Database=omop;UID=etl;PWD=secret;TrustServerCertificate=yes"
}

The shipped Docker image bundles Microsoft's ODBC Driver 18 for SQL Server, so no host-side ODBC configuration is required when running the engine inside the container.

For more on connection-string formats, consult the relevant database vendor's documentation.

Choosing a driver combination

There is no required pairing between the source and CDM drivers. Some common patterns:

  • CSV → PostgreSQL — source data in flat-file extracts, CDM in PostgreSQL.
  • PostgreSQL → PostgreSQL — schema-to-schema CDM transformation inside a single database.
  • SQL Server → PostgreSQL — pulling from a SQL Server EHR into an OMOP CDM hosted in PostgreSQL.
  • CSV → SQL Server — bulk-loading from extracts into a SQL Server CDM.
  • CSV → CSV — testing a mapping or building a portable extract.