Skip to main content

PyPI Lint Test Coverage Docs Python 3.12 License: MIT Release

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...
├── dependencies/    # Datasets that other repos build (optional)
└── 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.

Each folder in dependencies/ is another dataset, usually a Git submodule. Its tables (metadata/) and scripts (derive/) can be used by this dataset, but they are not built: that dataset builds them in the same database.

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 ~/.pgpass file or the PGPASSWORD environment 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:

  1. Creates the database if it does not exist, and the schema that records what was built (the checksums of the tables and scripts).
  2. 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 the transform/ scripts on it.
  3. 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_log column, and a warning gives their count.
  4. Runs the derive/ scripts that changed, each one after the scripts that create what it uses.
  5. 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"]
                }
            }
        ]
    }
}
  • name is the header of the column in the data file, and rename is its name in the database.
  • dtype has the type for each dialect.
  • A column with "transform": true is not in the data file. A transform/ script fills it.
  • A foreign key makes the table depend on the one it references, which therefore is built first.
  • Tables can also have indexes and unpivot (to turn columns into rows). The metadata generated by the commands above, and the code of TableMeta, 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 crosstab takes, 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.5

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for datakiln 0.3.0.5
File Size Uploaded Provenance Actions
datakiln-0.3.0.5.tar.gz 66.0 kB With provenance Details for datakiln-0.3.0.5.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for datakiln 0.3.0.5
File Interpreter ABI Platform Actions
datakiln-0.3.0.5-py3-none-any.whl Python 3 none any Details for datakiln-0.3.0.5-py3-none-any.whl

Total release size: 143.5 kB

Release files / datakiln-0.3.0.5.tar.gz

Download URL datakiln-0.3.0.5.tar.gz
Size 66.0 kB
Tags Source
SHA-256 checksum
How to use checksums
09c685fcc488e70c2a66cf7249871115481552b7512d7d2ed1382a4cc96a1eb7
BLAKE2b-256 checksum
How to use checksums
6f2e6e4e21bf62693e65a8ff7b15ff9eaae61f68d38600d46cb60e315dcff585
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 16, 2026.

Transparency log

Release files / datakiln-0.3.0.5-py3-none-any.whl

Download URL datakiln-0.3.0.5-py3-none-any.whl
Size 77.4 kB
Tags Python 3
SHA-256 checksum
How to use checksums
62c5d451ea09b786c58a3c5b5a7973f5a1c1514a49d85b5e618912e4e323e676
BLAKE2b-256 checksum
How to use checksums
f64eb6e43987081f4460f97e4cdbd436a7b6a040b685c786b427f524bd437b2f
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 16, 2026.

Transparency log

Security for datakiln 0.3.0.5

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.

Select from 2 files

Attestation for datakiln-0.3.0.5.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 16, 2026.

Transparency log

A 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.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page