From five systems of record to one: data engineering for lab analytics

Every AI project I've written about this week (Genie, the fungal-detection pipeline, the thrips counter) stood on the same unglamorous foundation: getting lab data out of the systems where it's captured and into a shape where analysis and machine learning can actually use it. Nobody demos the ETL. Everybody depends on it.

A modern R&D organization doesn't have "a database." It has a LIMS holding samples, tests, and results (sometimes more than one, across sites and business units) plus custom applications built for specific workflows, instruments producing files in their own dialects, ELNs like Benchling, and the spreadsheets that refuse to die. The same sample gets described three different ways. The same assay follows different naming conventions per site. Master data drifts. And somewhere downstream, an analyst or a model is expected to produce a trustworthy answer from all of it.

The reality of lab data

Lab data is hostile to analytics in specific, predictable ways. Results come with qualifiers (<0.01, TNTC) that break naive numeric parsing. Tests get re-run and invalidated, so "the result" is a slowly-correcting story, not a single value. Units hide in free text. Timestamps live in half a dozen timezones. And identity is a mess: the same physical sample can carry different identifiers in the LIMS, the instrument software, and the custom app that scheduled the work.

Multiply that across multiple LIMS deployments and a handful of custom apps, and you don't have a data problem, you have an integration problem wearing a data problem's clothes.

The work: from systems of record to analysis-ready

The pipeline I build follows the same arc every time, whatever the tools: land the data raw, conform it, and serve it analysis-ready.

It starts with SQL: querying and wrangling directly against source systems. You can't pipeline what you can't query, and lab work rewards people who can read a LIMS schema like a map: samples, batches, tests, results, and the master data tying them together. Getting the grain right (one row per what, exactly?) is the decision everything downstream inherits.

From there, ETL into Snowflake. In previous roles I built pipelines bringing data from several sources (LIMS exports, custom applications, instrument outputs) into Snowflake, where it gets cleaned and conformed into tables an analyst can actually trust. Continuous feeds landed through Snowpipe; for some LIMS systems, change-data-capture pipelines kept Snowflake in sync with the source (only changed rows moving, no full reloads), and streams and tasks propagated those changes downstream. Snowflake's separation of storage and compute fits lab workloads well. The data is always there, and you only pay for the heavy lifting when a reprocessing run or a big backfill demands it. Two features turned out to be quietly perfect for regulated work: time travel, which rewinds any table to any point for an audit or a debugging session, and zero-copy cloning, which spins up full-fidelity dev and test environments without duplicating a byte of storage.

That work also reached into Snowflake's AI layer. Snowpark ML let models train where the data already lives: no export-to-a-separate-stack round trip, no lineage lost in transit. And Cortex put a natural-language front end on governed lab data: Cortex Analyst turning scientists' plain-English questions into SQL against curated tables. Same instinct as Genie (meet the scientist where they are), executed entirely inside the warehouse.

Databricks is the analytics and ML-prep layer. I ran a Databricks POC at Greenlight Biosciences on Delta Lake in a medallion layout (bronze for raw landings, silver for cleaned and conformed, gold for analysis-ready) with MLflow tracking experiments and versioning models, all governed under Unity Catalog. Even a POC in regulated R&D needs lineage from day one; "we'll add governance later" is how you get a platform nobody is allowed to use.

Now at Regeneron I'm onboarding our department (PMPD) onto the platform and architecting its Databricks setup: Unity Catalog design, workspace organization, medallion layers for PMPD data, cost controls via cluster policies, and the path from ad-hoc exploration to scheduled, monitored jobs. Heavier transforms, exploratory analysis, and the handoff to machine learning all live on the same platform, and now I'm designing the platform itself.

And for the ML work, feature stores: versioned, point-in-time-correct features with a contract between data engineering and modeling. A model trained on features computed one way and served features computed another way is a silent failure waiting to happen. The feature store is what prevents it.

Under the hood

SQL → the universal adapter. Every system eventually answers SQL or exports to something SQL can read. Fluency here is the highest-leverage skill in lab data work: it turns every new system from a black box into a queryable one.
Warehouse → Snowflake, ML included. ELT over ETL where it makes sense: land raw, transform in the warehouse, keep lineage. Raw layers are append-only; nothing upstream gets destroyed by a bad transform. Snowpipe, CDC, and streams/tasks handle continuous ingestion; time travel and zero-copy clones make auditing and safe experimentation cheap. And with Snowpark ML and Cortex, the warehouse isn't just where data rests; it's where models train and where scientists query in plain English, against governed data, without an export-and-hope round trip.
Compute → Databricks. Notebooks for exploration, jobs for production, same code graduating between them. Delta Lake's medallion pattern gives every dataset a maturity address: bronze is "as landed," gold is "trust this." The Greenlight POC ran MLflow for experiment tracking, because a model without versioned runs and registered artifacts is a rumor, not an asset. Beyond using the platform, I'm architecting it for my department at Regeneron: Unity Catalog design, workspace organization, medallion data layout, and the path from ad-hoc analysis to scheduled, monitored pipelines. Platform architecture is data engineering at one level up: you're designing how everyone else does data.
Governance → designed in, not bolted on. Unity Catalog in Databricks, column-level lineage and access controls in Snowflake. In regulated R&D, governance is what makes data usable, not what slows it down. A dataset nobody is allowed to touch is the same as a dataset that doesn't exist. Every pipeline I build carries its audit trail with it: who landed what, when, transformed by which code, into which table.
ML contract → feature stores. Training-serving skew is a data engineering bug, not a modeling bug. Versioned features with point-in-time correctness fix it structurally.
Quality gates → validate at ingestion. Lab data has silent corruption modes: units in free text, qualified results, invalidated retests. Catching them at the gate beats debugging them in a model's output. (My lims-data-quality CLI is the lightweight, open-source version of this instinct: validate the export before it poisons anything downstream.)

Practical lessons

Model the domain before the pipeline. Samples, batches, tests, results, master data: if the domain model is wrong, every pipeline built on it is wrong in the same direction.
Treat master data as infrastructure. It's the join key to everything. Inconsistent master data doesn't cause one bad report; it causes joins that silently drop or duplicate rows across every report. (This is the same lesson behind the Corteva POC: master data quality is load-bearing.)
Build for re-runs. Lab data gets corrected: retests, invalidations, amended results. Pipelines must be idempotent and reprocessable, or every correction becomes a fire drill.
Resolve identity early. Across multiple LIMS and custom apps, entity resolution (which "sample 12345" is the sample 12345?) is a first-class problem, not a cleanup step.
Design governance in, not on. Lineage, access control, and audit trails aren't overhead in regulated R&D, they're the reason the data is allowed to exist. Build them into the pipeline and nobody has to retrofit them under audit pressure.
Make experimentation cheap and safe. Zero-copy clones, isolated workspaces, rewindable tables: every mechanism that lets someone try something without fear pays for itself in the analyses that actually get attempted.
The best data engineering is invisible. Success looks like analysts who never have to ask "where did this number come from," because the answer is always the same pipeline, the same definitions, the same validated tables.

AI gets the demos; data engineering gets the outcomes. Every accuracy number I've published started as somebody's join key matching, somebody's unit parsing correctly, somebody's invalidated retest excluded properly. The models are the visible tip. This is the rest of the iceberg, and in regulated R&D, it's the part that determines whether anyone is allowed to trust the tip.