2 July 2026 · Ibtisam Ahmed Khan

The data engineering stack, translated for materials science

What ETL, SQL, dbt, Airflow, Kafka and Spark actually do, each explained with a concrete materials use case, and the two worth learning first.

Software companies solved a problem fifteen years ago that materials science still struggles with: moving data reliably from where it is born to where it is used, cleaning it, testing it, and tracing it. The tools they built are open source and mature. This article explains the six core ones, each translated into materials terms, so you can tell which are relevant to your work and which are safely ignorable for now.

ETL: the shape of every pipeline

ETL stands for Extract, Transform, Load, and it is less a tool than a discipline: split any data workflow into three separate, rerunnable stages.

• Extract: pull raw records from the source, an API like the Materials Project, an instrument’s output files, a supplier’s spreadsheet.
• Transform: standardise units, harmonise names (so TiO2 and O2Ti become one substance), merge sources, compute derived fields.
• Load: write the clean result into a database or well-organised files.

The value is in the separation. When the source changes or a bug appears in the cleaning logic, you rerun one stage instead of untangling one giant script. Most research code does all three implicitly in one file; making the stages explicit is the single cheapest upgrade available.

SQL: asking questions of data at rest

Once materials data lives in a real database instead of forty CSV files, questions become queries:

SELECT formula, band_gap, density
FROM materials
WHERE band_gap BETWEEN 1 AND 3
  AND is_stable = true
ORDER BY density;

That is the whole language’s character: declare what you want, let the database work out how. SQL is fifty years old, runs everywhere, and remains the highest value per hour of learning in this entire stack. If you take one thing from this article, take this one.

dbt: tests attached to data

dbt manages SQL transformations, but its killer feature for science is column tests: rules attached to a dataset that run automatically on every refresh.

columns:
  - name: band_gap
    tests:
      - not_null
      - accepted_range: {min: 0, max: 16}
  - name: material_id
    tests:
      - unique

A negative band gap fails the build. A duplicate entry fails the build. The pipeline stops loudly instead of passing a broken value downstream to a model or a paper. Compare that with how research data is usually checked: someone eyeballs a plot once, the week the dataset is made, and the checks live in that person’s memory. Real example of the stakes: a major public materials database has carried a physically impossible negative shear modulus for a ZnO polymorph, exactly the kind of value a one-line range test catches at the source. dbt turns data quality from a person’s vigilance into infrastructure that outlives them.

Airflow: the schedule and the safety net

An orchestrator runs pipelines on a schedule, retries failed steps, and refuses to run step three when step two broke. The materials translation: a nightly job that fetches new database entries, validates them, and updates your working dataset, with a defined behaviour for every failure mode.

Why it matters beyond convenience: subtle bugs hide in unorchestrated pipelines. A database query without an explicit sort order, for instance, can return entries in different orders on different days and silently miss the one stable polymorph you cared about. An orchestrated pipeline with a validation step after every fetch converts that class of luck into design.

Kafka: data in motion

Everything above deals with data at rest. Kafka handles streams: continuous flows of events moving between systems in real time. The materials translation is the laboratory itself: every furnace log, XRD frame, and sensor reading is an event. Today most of that lands in local files and dies there. Streaming infrastructure is how self-driving labs, facilities running autonomous experiment loops, keep instruments, models, and databases synchronised. If you are not building one of those, you can know Kafka exists and move on.

Spark: when one machine stops being enough

Spark spreads computation across many machines. Thousands of materials fit comfortably on a laptop; screening millions of candidate structures, the scale of modern generative-model output, does not. Most materials teams do not need Spark yet. The value of knowing it exists is recognising the moment your dataset outgrows pandas, so you reach for the right tool instead of writing a three-week for-loop.

Where to start

Not with all six. Two changes put a typical materials dataset ahead of most of what gets published: structure your workflow as explicit extract, transform, and load stages, and attach automated range and uniqueness tests to every value you rely on. Both are days of work, not months, and both keep paying off after the person who set them up has moved on, which, in research, is the failure mode that matters most.

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