The challenge
The company’s operational data lived in several production databases, with no reliable, queryable copy for analysis. Reports depended on manual exports, so the numbers were stale as soon as they were shared, and nobody could confidently trace a figure back to its source.
The solution
I designed an ingestion layer that moves data from each source into a central warehouse automatically, keeping the raw data intact at every step. Analysts now query one consistent, up-to-date source with standard SQL instead of waiting for exports.
What I built
- Incremental extractors for MongoDB and PostgreSQL that load only new and changed records, tracked by a watermark for each table.
- A bronze layer on cloud storage that keeps the raw data exactly as it arrived, organised by source and table.
- BigQuery external tables on top of the bronze files, so the data can be queried in SQL without a separate load step.
How it works
Each run reads the latest watermark for a table, extracts only the records that changed since then, and writes them to the bronze layer before moving the watermark forward. If a run fails, the watermark stays where it was and the next run resumes from the same point, so no data is skipped. Because the raw files are never modified, any downstream model can be rebuilt from scratch at any time.
Result
Analysts work from fresh data using plain SQL, and every figure can be traced back to the raw files it came from. Adding a new source follows the same pattern, which keeps the cost of extending the system low.
What I would do next
Add automated data-quality checks between the bronze layer and the models built on top of it, along with alerts for late or failed loads.