In short
An ETL pipeline fills a target database from a source one, using SQL scripts. The scripts never name the databases directly: they write source. and target., which Linkr substitutes at execution time. That is what lets you take another site’s OMOP conversion pipeline and run it against your own databases.
The only place that writes
Everywhere else, Linkr reads. Databases are attached read-only, and neither widgets nor analysis scripts can modify anything.
An ETL pipeline is the exception, and it is a narrow one: only the target database is opened for writing, and only while the pipeline runs. The source and the vocabulary stay read-only in the same connection, so a script can read one and write the other — INSERT INTO target.person SELECT … FROM source.patients — without ever being able to modify what it reads.
Not to be confused with a project's pipeline
An ETL pipeline lives in the warehouse and goes from one database to another: this is where a source model is converted to OMOP.
A project’s pipeline starts from the warehouse and produces datasets for analysis — long format to wide. The first builds the warehouse, the second exploits it.
The three roles
A pipeline knows its databases by their role, never by their name.
source. — where the data comes from
Your operational database: the patient-record export, an ICU software’s tables. Read-only.
target. — where it goes
Typically an empty OMOP database, created from a schema with New from schema. The only one opened for writing.
vocab. — the reference terminology
The ATHENA vocabulary of a mapping project, to translate your local codes into standard concepts. Optional, read-only.
INSERT INTO target.person (person_id, gender_concept_id, year_of_birth)
SELECT p.id, v.target_concept_id, EXTRACT(year FROM p.birth_date)
FROM source.patients p
LEFT JOIN vocab.concept_relationship v ON v.source_code = p.sex_codeNever write a database's real name in an ETL script
This is the most important rule on this page. A database’s technical name contains an identifier specific to your instance: a script that hardcodes it stops working the moment it is exported elsewhere, where that identifier does not exist.
Roles survive the journey. Whoever imports your pipeline re-selects their two databases in the dropdowns, and every script works — without a single line of SQL being changed.
The substitution happens when running, not when saving: what goes into git stays portable. It is also careful — a source. inside a string, a comment or a longer identifier is left alone.
The tabs
| Tab | What you do there |
|---|---|
| Pipeline | The run board: the scripts as cards, in the order they chain. This is where you pick the source and target databases, reorder, enable or disable a step, and launch. |
| Scripts | The SQL editor proper, with the query results. |
| Browse schemas | Source and target tables side by side — essential while writing the correspondence. |
| Vocabulary | Generate the translation scripts from a concept mapping project. |
| Quality check | Verify what the run produced: both databases’ figures, and a concept-by-concept account of what was mapped. |
Overview, Readme, License and Versioning sit alongside them, as for every other entity.
Vocabulary, without rewriting it by hand
This is the part that saves the most time, and the least obvious to guess at.
Aligning local codes onto standard concepts happens in a concept mapping project — an interface built for it, with suggestions and evaluation. A pipeline’s Vocabulary tab can read that work and turn it into SQL.
You pick the mapping project, you pick the representation — CONCEPT + CONCEPT_RELATIONSHIP, the current OMOP form, or SOURCE_TO_CONCEPT_MAP, or both — and Linkr writes the scripts that load those correspondences into the target database.
The mapping project needs its reference vocabulary
With no ATHENA vocabulary database imported in the mapping project’s Target concepts tab, there is nothing to translate. See Target concepts.
Linkr flags whether a generated script has been edited by hand since, or is already up to date — so an adjustment is not overwritten unnoticed.
Running, and checking
You run a single script while working things out, or the whole chain in order. Progress shows query by query, with the name of the current script and the elapsed time — on a vocabulary script where one statement can take minutes, that is what distinguishes work in progress from a hang.
A failing run stops at the first script in error, and Linkr opens the detail panel directly on the offending script.
Pausing and stopping are not the same
Pause holds the run without ending it: resuming starts again at the interrupted script, within the same run.
Stop ends it. In both cases the statement already sent to the database runs to completion — so part of a script may have been applied. With a half-filled target database, recreating it empty beats re-running on top.
Then comes the Quality check tab, which answers the only question that matters: did the conversion lose anything? It offers two views.
Statistics puts both databases side by side: patients, hospitalizations, unit stays, gender breakdown, lengths of stay, and each table’s row count. An unexpected gap — three thousand patients on one side, two thousand eight hundred on the other — points at a join that silently dropped rows.
Concepts is finer, and works differently from what you might assume: both columns are read from the target database. A source database in its original shape has no comparable columns; once converted to OMOP, however, each table carries both the original concept and the standard one it was mapped to. Comparing the two says exactly how many rows arrived with a source code, and how many actually found a match.
Each concept gets a verdict — Missing, Fewer, More or OK — usable as a filter, and the whole thing exports to CSV. “Missing” is the one to look at first: rows arrived, none were mapped.
The 'Expected rows' column is not redundant
When several source codes point at the same target concept, one code’s row count does not match the expected total. The column therefore shows the sum of every code feeding that concept — otherwise an “OK” verdict next to two different numbers would look like a bug.
Patient counts need a schema on both sides
With no schema mapping on a database, Linkr does not know what a patient is: it can only count rows per table. See Schemas.
Sharing a pipeline
A pipeline exports, versions and publishes like every other entity — and this is where the roles earn their keep.
What travels is the scripts, never the data. A site that has converted its local model to OMOP can publish that work; another installs it, rebinds its two databases, and runs. It is probably the most directly reusable thing in all of Linkr: an OMOP conversion represents months of work, and it transfers in a single export.
Data files are excluded from versioning by default
A pipeline sometimes handles auxiliary files — lookup tables, reference lists. They are gitignored by default and re-included one at a time, explicitly. The default rule is that data does not leave; including it is a conscious decision, taken file by file.
Going further
- Databases — creating the empty target database from a schema.
- Concept mapping — producing the correspondences the vocabulary consumes.
- Data quality — checking the target database once filled.
- Project pipeline — the other pipeline, the one producing datasets.