One warehouse for eleven instruments that disagreed about time
- Client
- Halcyon Bio
- Sector
- Life sciences
- Year
- 2025
- Services
- Data Engineering
18min
instrument-to-dashboard latency, from 26 hours
100%
of models covered by freshness tests
38%
warehouse spend reduction
Challenge
Eleven lab instruments emitted results in six formats, three time zones, and two of them backdated records when a run was re-processed. A nightly Python script stitched it together. When it failed, which was often, nobody found out until a scientist asked why yesterday was missing.
Approach
In order, including the steps that changed the plan.
- 01
Make late data a normal case
The old pipeline assumed records arrived in order. We modelled every source with an explicit event time and ingestion time, so a backdated re-processing updates the correct partition instead of being dropped or double counted.
- 02
Test transformations like software
Every model gained unit tests, referential integrity checks and freshness assertions running in CI. Three of them failed on the first run against production data, which was the fastest way to find two years of quiet drift.
- 03
Agree on definitions once
A semantic layer fixed the meaning of sample, run and valid result in code. Two dashboards that had disagreed since 2023 turned out to be applying different exclusion rules, both defensible, neither documented.
Outcome
Results reach the platform in under twenty minutes. Failures page a named owner with a runbook entry rather than surfacing as a scientist's question three days later, and the reporting layer now has one definition of a valid run.
Built with
- dbt
- Snowflake
- Airflow
- Kafka
- Metabase
“We spent two years arguing about whose number was right. It took six weeks to discover we were both right and neither of us had written the rule down.”
Tell us what you are building.
A system you want built, a model that has to survive real traffic, or a process that should have been automated a year ago. The first conversation costs nothing, and occasionally ends with us telling you not to build it.
Reply within one working day
A person, not an autoresponder.
A 30-minute call, no deck
We ask about constraints, not budget.
A written view within a week
Including the case for not proceeding.