Skip to content

Pipeline overview

The ETL Engine is — exactly what its name says — an Extract / Transform / Load pipeline. The three stages are clearly separated inside the engine, share a common driver layer, and run concurrently for as long as the dependency graph allows.

┌──────────────┐     ┌────────────────────┐     ┌──────────────┐
│  Extraction  │ --> │  Transformation    │ --> │     Load     │
│              │     │                    │     │              │
│  source-side │     │  Rabbit-in-a-Hat   │     │  CDM-side    │
│  drivers     │     │  rules + DSL       │     │  drivers     │
└──────────────┘     └────────────────────┘     └──────────────┘
        ▲                                                ▼
        │                                                │
        └────── shared driver layer (CSV / MariaDB / ────┘
                MySQL / PostgreSQL / SQL Server)

Extraction

Source-side drivers read records from the source database. Available drivers are:

  • CSV — read a directory of CSV files. Drop them into etc/etl-engine.d/db/.
  • MariaDB / MySQL — read from a live database via the standard connection string.
  • PostgreSQL — same.
  • SQL Server — read from Microsoft SQL Server via ODBC.

See Drivers for the connection-string formats.

Source data flows into the engine's typed value model — the four internal types are INT, FLOAT, DATE, and STRING. Database- specific column types (varchar, real, timestamp-with-zone, etc.) are mapped onto these four; the rest of the pipeline never has to care which RDBMS the data came from.

Transformation

The transformation stage is where the bulk of the engine's value lives. It takes the Rabbit-in-a-Hat file, the rule chains embedded in its logic fields — and in the Comments fields of tables and fields that have no logic field of their own — the Usagi mapping files, and the OMOP standard vocabulary, and produces transformed CDM rows.

Concretely, the engine:

  1. Builds an internal model from the Rabbit-in-a-Hat file.
  2. Resolves all DSL rule chains, validating types and parameters.
  3. Builds a dependency graph among CDM tables (basic dependencies from Rabbit-in-a-Hat arrows, complex ones from Join / LeftJoin, and user-declared ones from DependOn).
  4. Schedules tables in dependency order.
  5. Dispatches source rows to a worker-thread pool, which runs the per-table rule chain on each row.

For the language and the rule catalog, see the DSL Guide and the Rule Reference.

Load

CDM-side drivers write transformed records to the destination database. The same driver set is available for the load side as for extraction — CSV, MariaDB / MySQL, PostgreSQL, and SQL Server — which means source and target choices are independent. Common combinations include:

Source Target When you'd use it
CSV PostgreSQL One-shot migrations from extracts.
PostgreSQL PostgreSQL Schema-to-schema CDM transformation in one DB.
SQL Server PostgreSQL Pulling from a SQL Server EHR into an OMOP CDM.
CSV SQL Server Loading bulk extracts into a SQL Server CDM.

Dependency model

The engine derives table dependencies from three sources:

  • Basic dependencies — drawn explicitly in Rabbit-in-a-Hat as source-to-CDM table arrows.
  • Complex dependencies — generated by Join / LeftJoin rules and by GetCDMTableId, which introduces an implicit dependency on the looked-up table.
  • User-declared dependencies — the DependOn table rule, used when foreign-key relationships in the destination database are not inferable from the Rabbit-in-a-Hat model.

Cyclic dependencies

DependOn can be used to construct a cycle, in which case no table is transformed and no data is loaded. The engine does not currently break cycles automatically — review your DependOn rules carefully.

What the engine does not do

  • It does not write SQL. The destination driver issues bulk inserts; the rule language is value-oriented, not query-oriented.
  • It does not modify source data. The source side is read-only.
  • It does not infer mappings. Every CDM field that needs a value must be wired up in Rabbit-in-a-Hat — either as an arrow from a source field, a rule on the CDM field, or via a virtual field plus rule.