Client introduction
Our client is a research team within a pharma company. They run screening experiments to find promising candidates for further research. Every screening run produces a large amount of raw data, together with the configuration and metadata of that run. The results are used to decide which candidates move on to the next selection round.
Challenge
Before anyone could look at the results of a run, the data had to be collected, decoded and matched with the right run details. This was done by hand, every time.
In practice, this work mostly came down to one data scientist writing SQL and scripts. It worked, but it was slow and hard to reproduce. It also meant that when the data scientist was not available, the screening results had to wait. The scientists who needed the results had no way to get to them on their own.
The goal was clear: automate the path from raw run output to results scientists can use, and make sure they can trust the numbers they base their decisions on.
Solution
We built an automated pipeline from raw instrument output to dashboards, using Snowflake, dbt and Airflow.
Automated ingestion
The output, configuration and metadata of every run are loaded automatically by Airflow, either on a schedule or on demand. This replaces the manual loading, and makes sure every run is processed the same way.
A layered data model in dbt
The data moves through three layers. The first layer keeps the raw data as it arrived. In the second layer, the data is decoded and validated against what the experiment was designed to test. The third layer turns it into what the scientists need: validated counts and normalized frequencies per run, condition and selection round, an export of the top candidates for the scientists’ tool of choice, and an overview of the experiments behind them. All models are documented in dbt, and this documentation is open to the scientists as well, not only to the engineers.
Self-service dashboards
The dashboards read directly from the final layer. This means the chemists and biologists who run the experiments can get to their results themselves, without going through their data scientist or a data engineer, and everyone looks at the same numbers.
Data quality built in
Since these numbers are used to pick candidates, they need to be right, and it needs to be clear why they are right. Every record gets an audit status that shows whether it passed or at which step it was rejected. Statistics on the rejected records show where data drops out. Outdated runs can be flagged and excluded, and the same run can’t be loaded twice. Unit tests, end-to-end tests and separate development, QA and production environments make sure a change doesn’t break anything along the way.
Results
The platform is live in production, and the team uses it every day. Two moments showed its value better than any metric.
The first one came during a holiday. Processing a screening run used to depend on the data scientist. Now the rest of the team runs decodings and screening runs themselves, and they kept doing so while the data scientist was away.
The second one came once the new pipeline was running: it flagged wrong values in a reference table the team maintained by hand. In the old way of working, this had gone unnoticed.
On top of that, results reach the dashboards without manual steps, and personal schemas and one-off scripts have been replaced by one tested source of truth.
Conclusion
When we started, getting results out of a screening run depended on one person and a lot of manual work. Now the team has an automated platform that processes every run the same way, shows which data was kept and why, and lets scientists work with their results on their own.
The building blocks are reusable, so new needs such as additional libraries can be added without starting over. Next up are incremental loading and faster dashboards.
