Summary
A derived sub-database is a copy of a database restricted to the patients of a cohort: Linkr copies each of its tables, keeping only those patients’ rows. The result is a database like any other, standing on its own, that keeps a record of its origin — the parent database and the cohort’s criteria — and can therefore be rebuilt once the warehouse has changed. The copy runs on the server.
The need
A hospital warehouse holds every patient. A study concerns only a fraction of them: one ward, one period, one condition.
Working on the whole warehouse forces every analysis to remember the filter — and it only takes one that forgets to produce a wrong figure. A sub-database defines the scope once and materializes it: everything that queries it already works within the right scope, with the same tables and the same column names as the parent database. A query written for one works on the other.
Not the same thing as a cohort
A cohort is a list of patients, used as a filter by analyses. A sub-database is a complete database — the parent database’s tables, restricted to those patients —, which you query, explore and link to projects like any other database.
Deriving a database
A derivation starts from a cohort of the database itself — not a project cohort.
Open the database’s cohorts
In Data Warehouse › Databases, open the parent database, then its Cohorts tab.
Build the cohort
With the usual criteria builder — see Cohorts —, at Patient, Hospitalization or Unit stay level.
Click Derive
The Derive button, in the builder’s toolbar, opens the Derive a database from this cohort dialog.
When the button is missing, or derives nothing
- It only exists in server mode: the copy is written by the server, into a database it manages. See Deployment modes.
- A cohort at Event level cannot be derived: it has no single id to filter the tables on.
- The database’s schema must say where its patients are — see Mapping a schema.
- The cohort must be defined by its criteria, not by hand-written SQL; unsaved edited SQL disables the button.
The Derive dialog
Derive a database from this cohort
Copy every table of this database, keeping only the rows of “Septic shock”. The copy stands on its own: it does not read this database.
Vocabulary, care sites, locations… copied whole, so the copy can be queried on its own. A full OMOP vocabulary takes several GB.
| person | Patients of the cohort (person_id) |
| observation_period | Patients of the cohort (person_id) |
| visit_occurrence | Patients of the cohort (person_id) |
| visit_detail | Patients of the cohort (person_id) |
| condition_occurrence | Patients of the cohort (person_id) |
| drug_exposure | Patients of the cohort (person_id) |
| measurement | Patients of the cohort (person_id) |
| observation | Patients of the cohort (person_id) |
| procedure_occurrence | Patients of the cohort (person_id) |
| death | Patients of the cohort (person_id) |
| care_site | Copied whole |
| concept | Copied whole |
| concept_relationship | Copied whole |
| location | Copied whole |
| vocabulary | Copied whole |
- Already derived into — the derivations already made from this cohort, with their date and patient count. Rebuild makes one again with the cohort’s current criteria, and replaces its content.
- Derive into — a new database, or a schema in an existing database (see below).
- Copy the tables that hold no patient data — ticked by default: vocabulary, care sites, locations… are copied whole, so the copy can be queried on its own. A full OMOP vocabulary takes several GB; unticked, those tables are not copied.
- The plan — what will happen to each table, before anything runs: filtered on the cohort’s patients, stays or unit stays, copied whole, or not copied. A table in red is one the mapping reads without a patient id column: its rows cannot be filtered, so it is not copied.
The cohort’s level decides the filter: each table is filtered on the finest id it carries, without going below the cohort’s level. For a unit-stay cohort, a table with a unit-stay id keeps the cohort’s unit stays; a table with only a hospitalization id keeps the hospitalizations that contain them; a table with only the patient id keeps the patients.
Two destinations
A new DuckDB database
The filtered tables are written to a separate database.
You give it a Database name and a Location — Linkr’s data folder, or a folder on the server. Nothing is written to the parent database: this is the route that works everywhere, including on a read-only warehouse.
A SQL schema in an existing database
The filtered tables live in a new schema of a database that accepts writes.
A DuckDB database Linkr created, or a PostgreSQL database whose settings tick Allow Linkr to write into this database — the user needs the CREATE right there. You choose the SQL schema name (lowercase letters, digits and underscores); on PostgreSQL, Also add it as a database makes it appear in the list of databases. Linkr never changes the existing tables.
Derive starts the copy as a server job, followed in the jobs panel, in the footer. For a new database, Linkr opens it straight away: it reads “Being set up” until the copy ends, then connects on its own. On a large warehouse, count minutes.
What the sub-database becomes
A database like any other: it appears in the list of databases, with its statistics, its schema browser, its parent’s mapping, and it links to projects. Its Overview tab also carries a Derived from a cohort card.
Derived from a cohort
Rebuild it from the criteria recorded here, against the database it was derived from.
That is what lets you answer, six months later, the question that always comes up: what exactly is in there?
Rebuild, on that card, replaces the tables with a new copy of the parent database, filtered on the criteria recorded when it was built — not on those the cohort may have since. It is the thing to do once the warehouse has been updated. The parent database must be in the same workspace, and connected; otherwise the card says so.
Two “Rebuild” buttons, two sets of criteria
From the sub-database’s card, you rebuild with the criteria frozen in its provenance: same scope, fresh data. From the cohort’s Derive dialog, you rebuild with its current criteria: the scope follows the cohort.
Deleting a sub-database offers to delete what Linkr created for it too: its file, or the SQL schema and its tables. Exported, it carries its provenance — and, like any database, never its data: on another instance, the card still names the parent database, but a rebuild is only possible where that one exists.
From an AI assistant
The MCP server exposes the same feature: list_database_cohorts lists a database’s cohorts, plan_cohort_derivation shows the table-by-table plan without writing anything, and derive_database_from_cohort starts the copy. It returns at once, with the job id, which the assistant then follows through to the new database.
And in browser mode?
Derivation does not exist there. Two approaches cover part of the need: a cohort, which defines the scope once and serves as a filter for analyses, or an ETL pipeline, where you write the filtering queries yourself, from a source database to a target one.
Going further
- Cohorts — the criteria builder a derivation starts from.
- Databases — what a sub-database is once built, and a PostgreSQL database’s write permission.
- ETL pipelines — for a transformation derivation cannot do.