Context
Problem & Responsibility
Operational Problem
Operational data needed to reach analytical models reliably while supporting backfills, retries and production promotion.
My Responsibility
Implemented event-driven synchronization, incremental transformation, monitoring and controlled recovery workflows.
System Design
Architecture & Workflow
- 01
PostgreSQL/PostGIS transactional source
- 02
Transactional outbox and event-driven refresh
- 03
BigQuery replica, staging and marts
- 04
dbt incremental models
- 05
Backfill processing, monitoring and failure recovery
- 06
A periodic safety-net job that re-triggers failed or incomplete synchronization work automatically
- 07
Separate development and production datasets, schema-drift protection and repeatable promotion
- 08
Transaction handling around dispatch and refresh state so a failure never leaves processing in an inconsistent state
Deep Dive
Inside the Build
Approach
Implemented event-driven synchronization, incremental transformation, monitoring and controlled recovery workflows. Designed and improved a transactional-outbox synchronization pattern with event-driven refresh, dbt staging/marts, monitoring, recovery and production-promotion controls.
- 01
Built synchronization around field and field-season-history entities, keeping development and production datasets separate.
- 02
Used dbt staging views and incremental marts to prepare analytical dimensions, including deduplication, cleaning, outlier handling and geospatial transformation.
- 03
Introduced monitoring, failure alerts and a scheduled safety-net process that automatically retriggers failed or incomplete synchronization work.
- 04
Completed a 101,722-record backfill using 5,000-row batches after identifying a PostgreSQL parameter-limit constraint.
- 05
Improved transaction handling around dispatch and refresh state so failures never leave processing in an inconsistent state.
Evidence
Scale & Measurable Impact
- Completed a 101,722-record backfill using controlled 5,000-row batches
- Improved recoverability between operational and analytical systems
- Supported development-to-production promotion