4 July 2026 · Ibtisam Ahmed Khan

Automatic tests for scientific data: a dbt primer

How to attach rules to a materials dataset, no negative band gaps, one entry per polymorph, that run on every refresh and fail loudly.

Software engineers test their code automatically: every change runs a suite of checks before it ships. dbt brings the same idea to data. This primer explains what it is, what the tests look like for a materials dataset, and how to try it in an afternoon.

What dbt is

dbt (data build tool, free and open source) manages the transformation layer of a data pipeline: the SQL that turns raw loaded tables into clean, analysis-ready ones. Two ideas make it valuable for science:

1. Transformations become version-controlled files. Every cleaning step is a plain SQL file in a git repository, reviewable and reproducible, instead of clicks or one-off scripts.
2. Tests attach to columns. You declare, in plain YAML, what must always be true of the data, and dbt checks it on every run.

What the tests look like

Here is a test file for a materials table:

models:
  - name: materials
    columns:
      - name: band_gap
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 16
      - name: density
        tests:
          - dbt_utils.accepted_range:
              min_value: 0.3
              max_value: 25
      - name: material_id
        tests:
          - unique
          - not_null

Read it as a set of physical claims: a band gap can never be negative and never above 16 eV; a solid’s density lands between lithium-light and osmium-heavy; every entry has exactly one identifier. When the data refreshes, every rule runs. A violation stops the pipeline with a named failure instead of letting a broken value flow downstream into a model or a paper.

Why this matters more in science than in business

A sales dashboard with a wrong number gets noticed by someone whose job depends on it, usually within days. A wrong number in a research dataset can survive for years: cited, merged into other datasets, used as training data. Two features of research life make automated tests disproportionately valuable:

• Datasets outlive their makers. The person who knew that column 7 had unit problems graduates and leaves. A dbt test is that knowledge, made permanent and executable.
• Errors are physically checkable. Science has an advantage business data lacks: hard physical bounds. A shear modulus cannot be negative; a formation energy of -900 eV/atom is not a discovery, it is a parsing bug. These make excellent tests, and real public databases do contain such values, a physically impossible negative shear modulus has sat in a major materials database’s records, precisely because nothing in the pipeline asserted it could not.

Trying it in an afternoon

The minimal path, assuming some comfort with a terminal:

1. pip install dbt-duckdb, DuckDB is a zero-setup database that runs from a single file, ideal for trying things
2. dbt init my_materials_project scaffolds the folder structure
3. Load any materials CSV you have into DuckDB, write one SELECT-based model over it, and add a YAML test block like the one above
4. dbt build runs the transformations and every test, and tells you exactly which rows violated what

The concepts transfer even if you never adopt the tool: the discipline of writing down, in executable form, what must always be true of your data. Some groups implement the same idea with plain Python assertions in their pipeline, which is a fine start, dbt simply standardises it, scales it, and makes it legible to collaborators.

The general principle

Every dataset carries implicit assumptions: values in range, one row per entity, units consistent. Left implicit, they get violated silently. The whole trick, whatever the tool, is making the assumptions explicit and automatic, so that data quality stops being a property of how careful people were the week the dataset was made, and becomes a property of the system.

Found a mistake? Good, tell me. This publication flags its own suspect values. Reach me on LinkedIn.