datakiln
datakiln builds a database from a repository of files, so that any version of a dataset can be rebuilt later with one command.
Research datasets can span dozens of tables, with views and functions that people build on top of them. Each new release of the data, each fix to a view and each change to a column type leaves a slightly different database behind, and months later, when someone has to reproduce a result, nothing in the database says which version of which file it came from.
Here the data files are versioned, and the metadata keeps track of the source tables, their columns and their constraints. Together with the scripts for the views and functions, that describes the whole database, so a version of the dataset is a version of the repository. Because it remembers what it built last time, moving to another version only reloads what changed and reruns only what depends on it.
Why use it
- To get the dataset exactly as it was when a paper was written, go back to that version of the repository and build again.
- A change to a column type, a key or a view is recorded like any change to code, so others can review it and it can be undone.
- A data pipeline is a chain of steps that depend on one another, and those dependencies usually live in someone's head or in conventions that drift. Here they are inferred from the repository's own files, so every step runs after what it depends on and a change reaches everything built on it.
- How the tables and views depend on each other can be drawn from the repository itself, so whoever picks up a dataset they did not build can see how it fits together, and the picture cannot drift from the code.
Features
- Tables from files. Files of several types are loaded into database
tables as their metadata describes: types, required columns, primary,
unique and foreign keys, indexes, values read as
NULL, and columns unpivoted from wide data. - Incremental builds. A table is rebuilt when its metadata or its data file changes, or when a table it references is rebuilt. A script that builds views or functions runs again when its content changes or what it uses is rebuilt.
- DVC data files. The data files can be tracked by DVC, and the ones a build needs are pulled from the remote.
- Views and functions in dependency order. Each SQL script creates one object (a view, function, type...) from the tables or from other objects. The tools read the SQL to find what each script uses, so the scripts need no numbering, and the order comes from the dependencies.
- Dependency graph. The graph of the tables and scripts can be drawn as a Mermaid flowchart.
- Metadata generation. Table metadata can be generated from sample data files, from a source database in one of several DBMS, or from the forms of a REDCap project.
- Extraction from a database. Data can be extracted from a source database for the group of people a study follows (its cohort). A table is limited to the rows that relate to the cohort or to another extracted table, directly or through a chain of joins, and the tables are extracted in the order that those relations require.
- Pruning and permissions. Objects removed from the repository are listed or dropped, and read access is granted to a database role and its users.
Installation
It needs Python and a database server. The metadata gives a column type for each DBMS, so the tool is meant to work with more than one.
pip install datakiln
This installs one command, datakiln, with the subcommands build,
metadata, graph and extract (extract dbms and extract redcap). Run
datakiln --help, or --help after a subcommand, to see all of its options.
Dataset layout
Every command takes the directory of the dataset:
dataset/
├── .env # Database connection
├── .dataignore # Files to skip (optional)
├── data/ # Data files, one for each table
├── metadata/ # Table metadata, one JSON file for each table
├── transform/ # SQL scripts that run on the loaded data
├── derive/ # SQL scripts that create views, functions...
└── extract/ # Extraction metadata (only to extract from a DBMS)
.dataignore lists files, one for each line, relative to the dataset
directory (derive/old.sql). They are left out of the build.
Environment
The connection to the database is set in the .env file of the dataset
directory. All of the variables are optional and have the defaults below, but
the file itself must exist to build the database.
| Variable | Default | |
|---|---|---|
DB_USER |
the current user | |
DB_HOST |
localhost |
|
DB_PORT |
5432 |
|
DB_NAME |
postgres |
Created if it does not exist. |
DB_DIALECT |
postgresql |
The DBMS of the database to build. |
DB_ISOLATION_LEVEL |
READ COMMITTED |
Transaction isolation level of the connection. |
DB_USER=analyst
DB_HOST=db.example.org
DB_NAME=study
There is no variable for the database password. The connection uses the password mechanism of the DBMS, for example:
- PostgreSQL: a
~/.pgpassfile or thePGPASSWORDenvironment variable.
DB_NAME also names the read role the build creates (see
Permissions).
Data files and DVC
Each table reads the file in data/ that has its name, ignoring the case and
the suffixes (.csv, .csv.gz, .xlsx). The table of metadata/patient.json
reads data/patient.csv.
If data/ is a DVC repository (it has a data/.dvc directory), the files are
the ones DVC tracks. A build pulls the files of the tables it builds from the
DVC remote, and tells a changed file by its checksum. Otherwise data/ is
read as a plain folder and nothing is pulled.
cd dataset/data
dvc init --subdir
dvc remote add -d storage ssh://user@example.org/path/to/storage
dvc add patient.csv
The DVC remote of a pipeline that runs without a terminal needs credentials
that do not ask for a password. For an SSH remote, that is a private key in
~/.ssh, or one named with dvc remote modify --local <remote> keyfile <path>.
Usage
Generate the metadata of the tables
From sample data files, it reads the first 10,000 rows of each file in data/
and writes a JSON file for each table to metadata/:
datakiln metadata dataset
datakiln metadata dataset --delimiter ";"
The columns are named from the headers, the types are guessed from the
data, and the primary key is a meta_row_id column. The schema is public.
Edit each file after: set the schema, the key, the real types and the
descriptions.
From a REDCap project, it downloads the data of each form to data/ and, with
--metadata, writes its metadata:
datakiln extract redcap dataset --api-url https://redcap.example.org/api/ \
--api-key "$REDCAP_TOKEN" --metadata --schema redcap \
--include demographics labs --exclude consent
From a source database, see Extract from a database.
Build the database
datakiln build dataset
This does the following:
- Creates the database if it does not exist, and the schema that records what was built (the checksums of the tables and scripts).
- Loads the data of each table that is new or changed, or that references a
table that changed, into a staging schema (
_<schema>), and runs thetransform/scripts on it. - Moves the rows that satisfy the constraints of each table to its final
table, which has its types, keys and indexes. The rows that do not (a
required value is missing, a foreign key has no match or a key is
duplicated) stay in the staging table, with the reason in its
meta_logcolumn, and a warning gives their count. - Runs the
derive/scripts that changed, each one after the scripts that create what it uses. - Lists what was built before and is no longer in the repository, and grants read access.
| Option | |
|---|---|
--include name... |
Build only these tables and scripts, and what they use. |
--rebuild-all |
Rebuild everything, even what is up to date. |
--prune |
Drop what was removed from the repository. Without it, the objects are only listed. |
--grant user... |
Give read access to these database users. |
--stdout |
Show the log in the terminal. It is always written to build.log. |
# Only one view, with the tables and scripts it needs
datakiln build dataset --include study_derived.labs
# Everything again, and remove what is gone from the repository
datakiln build dataset --rebuild-all --prune
A build stops with an error if what is in the repository needs an object that was removed from it.
Go back to an earlier version
git checkout <commit>
datakiln build dataset
The build compares the checksums of the files and metadata at that commit with
what is in the database, reloads the tables that differ, and reruns the
scripts built on them. Add --prune to drop the objects that did not exist
yet at that commit.
Table metadata
A table is described by a JSON file in metadata/:
{
"schema": "study",
"name": "visit",
"encoding": "utf-8",
"delimiter": ",",
"na_values": ["N/A", "unknown"],
"columns": [
{
"name": "Visit ID",
"rename": "visit_id",
"description": "Identifier of the visit.",
"dtype": {"postgresql": "INTEGER"},
"required": true
},
{
"name": "Patient ID",
"rename": "patient_id",
"dtype": {"postgresql": "INTEGER"},
"required": true
},
{
"name": "Visit date",
"rename": "visit_date",
"dtype": {"postgresql": "DATE"}
}
],
"constraints": {
"primary_key": ["visit_id"],
"uniques": [["patient_id", "visit_date"]],
"foreign_keys": [
{
"columns": ["patient_id"],
"reference": {
"schema": "study",
"table": "patient",
"columns": ["patient_id"]
}
}
]
}
}
nameis the header of the column in the data file, andrenameis its name in the database.dtypehas the type for each dialect.- A column with
"transform": trueis not in the data file. Atransform/script fills it. - A foreign key makes the table depend on the one it references, which therefore is built first.
- Tables can also have
indexesandunpivot(to turn columns into rows). The metadata generated by the commands above, and the code ofTableMeta, show their format.
Transform scripts
The .sql files in transform/ run, in the order of their names, after the
data is loaded into the staging tables and before it moves to the final ones.
They are the place to clean values or to fill the columns marked transform.
Derive scripts
The .sql files in derive/ (in any subfolder) create the objects built from
the tables: views, materialized views, tables, indexes, functions,
procedures, aggregates, types, sequences and schemas.
-- derive/labs.sql
CREATE MATERIALIZED VIEW study_derived.labs AS
SELECT v.patient_id, v.visit_date, l.test, l.value
FROM study.visit AS v
JOIN study.lab AS l USING (visit_id);
The tools read each script to find the objects it uses, so the scripts do not need numbered names. The rules are:
- A script creates exactly one object, and nothing else.
- Tables and views are in a schema that is not
public. - Functions, procedures and aggregates are created and called without a schema, so they live in the default schema of the target database. They are told apart by the types of their parameters, so overloads work.
- A script that creates a schema (
CREATE SCHEMA study_derived) is required by every script that creates something in it. - A query given as text is read too, as the ones a
crosstabtakes, and so are the strings in the body of a function. They follow the same rules as the code around them. - A script whose SQL cannot be parsed, so its dependencies cannot be worked out, is an error that names the file.
Dependency graph
datakiln graph dataset # Mermaid text
datakiln graph dataset --output graph.md # Markdown with a Mermaid diagram
datakiln graph dataset --output graph.html # A page that any browser draws
datakiln graph dataset --direction TB --include study_derived.labs
It draws the tables and the scripts as a diagram, with an arrow from what is
needed to what needs it. The extension of the output file tells the format,
and --include limits the graph to some objects and what they need.
Extract from a database
datakiln extract dbms downloads data from a source database to data/, and can write the table metadata from the source's columns. Each
table is described by a JSON file in extract/:
{
"schema": "source",
"table": "visit",
"filters": [
{
"columns": ["patient_id"],
"reference": {
"schema": "study",
"table": "cohort",
"columns": ["patient_id"]
}
}
]
}
A filter limits the rows to the ones that match a reference table, which is
the cohort or another extracted table. Chains of join, comparison
operators, cohorts given by a .sql file and the chunk_size of the
download are also available. The format is documented in
datakiln/extraction/models.py.
datakiln extract dbms dataset --dialect oracle --host source.example.org --port 1521 \
--database ORCL --user analyst --metadata
| Option | |
|---|---|
--dialect, --host, --port, --database |
The source DBMS, all required. |
--user, --password |
The default user is the current one. |
--metadata |
Also write the JSON metadata of the tables to metadata/. |
--no-data |
Only write the metadata, without downloading data. |
--include table... |
Extract only these tables, and what they need. |
--read-only |
Stream the data, without creating temporary tables in the source. |
--parallel N |
Number of extractions at once. Default is 4. |
The metadata is written in the dialect of the target database, set in the
.env file of the dataset. extract redcap takes the same --data and
--metadata options, and --include and --exclude for forms.
Permissions
The build creates a database role named after the database (DB_NAME). It
gets read access to the schemas of the tables and scripts, which leaves out
the staging schemas and the metadata. --grant gives that role to users:
datakiln build dataset --grant alice bob
Logs
build, metadata and extract write a log to the directory they run in:
build.log, metadata.log or extract.log (for both sources). The log is
overwritten on each run, and --stdout also prints it. graph writes no log.
Development
The unit tests need a database server that the user in .env can use, for
example:
DB_USER=test
DB_HOST=localhost
DB_NAME=test
To work on the code, install it from a clone with the extras for the tests, the lint and the documentation:
pip install -e ".[test,lint,docs]"
pytest tests
flake8 datakiln tests
pylint datakiln
The documentation of the code is at https://lbulgarelli.github.io/datakiln/.
A dataset repository can test its own metadata and scripts by running the
tests in datakiln/test.py from its directory. They create a __test
database, load the tables, run the transform and derive/ scripts, and roll
everything back.
pytest --pyargs datakiln.test
License
MIT. See LICENSE.
Metadata
Release files for datakiln 0.3.0.2
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| datakiln-0.3.0.2.tar.gz | 65.4 kB | With provenance | Details for datakiln-0.3.0.2.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| datakiln-0.3.0.2-py3-none-any.whl | Python 3 | none | any | Details for datakiln-0.3.0.2-py3-none-any.whl |
Total release size: 142.2 kB
Release files / datakiln-0.3.0.2.tar.gz
| Download URL | datakiln-0.3.0.2.tar.gz |
|---|---|
| Size | 65.4 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
735cf19033d23c9a8e15aba11e5341b06480490db86a4d795ad6ccf3bdc6a445
|
|
BLAKE2b-256 checksum How to use checksums |
38124409557cf21b83499d5a7b56c2caad06951562c27064219963321899efad
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.
Transparency logRelease files / datakiln-0.3.0.2-py3-none-any.whl
| Download URL | datakiln-0.3.0.2-py3-none-any.whl |
|---|---|
| Size | 76.8 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
78e7bfa31435bbd8027d91f507a9e5c3582406a4c33371a0ec75cc2c64b887e2
|
|
BLAKE2b-256 checksum How to use checksums |
cf741709258cced73a5d6722c7028809cd2f92d166e45789788324dd2c99f8f7
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.
Transparency logSecurity for datakiln 0.3.0.2
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
Full provenance
Both files in this release have been uploaded with provenance metadata.
Attestation for datakiln-0.3.0.2.tar.gz
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.
Transparency logA note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.