· Shannon Alliance · Life Sciences · 4 min read
From Dark Data to Daily Insights: How BigQuery Transformed a Digital Pathology Lab
A digital pathology partner needed a regular count of samples stained with a given assay, and which analysis was paired with it. The answer lived in three systems.
Leadership at a partner of ours, a digital pathology lab, had a business question that they needed to answer on a regular basis: “How many samples have we stained with X? And what analysis did we combine with that stain?”
On the surface, this seems like a very easy question for a digital pathology lab to answer. But our partner had a problem: the data needed to find that answer lived inside three different systems. SVS and TIFF image files sat on a local network drive, sample records lived inside their LIMS, and study timelines lived inside their project management software. No single database connected them.
Finding the answer required staff to open individual scan files manually and ask scientists to recall past projects from memory. Shannon Alliance solved this problem by writing a Python script to extract metadata from image headers, directory paths, and filenames. We loaded that metadata into Google BigQuery, joined it with LIMS records using sample IDs, and built a targeted Flask and React web app on top of the data.
That search tool solved the immediate question for leadership. The same joined dataset also gave project managers real-time visibility into slide processing stages, keeping scientists focused at the bench.
Mapping the disconnected data
To answer leadership’s question, we had to combine data across three separate tools:
- Image files (SVS and TIFF). File headers store scanner models and creation dates. Folder paths contain client names and study IDs. Filenames contain sample IDs.
- The LIMS. Stores sample metadata, assay details, and clinical results.
- Project management software. Stores study timelines, deliverables, and milestone dates.
Before our project, staff cross-referenced these three sources manually because no common key connected them. We wrote a Python script that parses the file headers, folder paths, and filenames using regular expressions. The script extracts the sample ID from the filename and uses it as a primary key to match every image file directly to its corresponding row in the LIMS.
Storing and joining data in BigQuery
We loaded years of historical scan archives and daily incoming files into Google BigQuery using a three-tier architecture:
- Bronze. Receives raw file metadata, database dumps, and live API payloads without cleanup.
- Silver. Standardizes timestamps to UTC, cleans client and study names, and joins image file records to LIMS rows using the sample ID.
- Gold. Aggregates pre-calculated metrics, including stain counts by client, study volumes by date, and slide processing completion rates.
BigQuery returns queries from these Gold tables in under a second. We query BigQuery directly from the application, which eliminates the need for a separate caching layer. After backfilling the legacy file archives, an automated daily job parses new image files and refreshes the Gold tables.
Giving leadership the answer
Instead of requiring lab directors to write SQL queries or navigate complex dashboards, we built a simple web application using a Flask API and a React frontend. A user types a stain or client into a search bar, and the application returns historical study volumes instantly.
Unlocking real-time progress for project managers
After we launched the stain search page, project managers asked us to solve another daily headache.
Project managers regularly update clients on whether slides are accessioned, scanned, analyzed, or flagged for review. Because that status lived at the bench, project managers constantly emailed, messaged, or walked into the lab to interrupt scientists for updates.
Our BigQuery database already combined LIMS sample statuses with image scan timestamps, so the system calculated slide progress automatically behind the scenes. We added a study status view to the React app. Project managers can now look up a study and view live slide progress instantly, keeping scientists focused on lab work.
Key takeaways for lab data engineering
- Extract metadata from existing paths. File headers, folder structures, and filenames follow implicit schemas. Parse them programmatically instead of asking staff to tag files manually.
- Establish a common join key. Join file metadata to your primary LIMS using sample IDs or accession numbers.
- Build targeted search interfaces. A single-purpose search tool solves operational problems faster than a bloated, general-purpose dashboard.
