Fault-Tolerant
ETL Sync Pipeline
Bidirectional ETL sync pipeline between MongoDB and Salesforce CRM — built with SSIS. Schema validation, retry logic, dead-letter queuing, and a full immutable audit trail. Eliminated weekly manual reconciliation at a financial-scale operation.
Weekly manual reconciliation between MongoDB and Salesforce — error-prone and unscalable at financial data volumes.
At FIS Global, transaction data lived in MongoDB while customer-facing records lived in Salesforce. Every week, a team member would run a set of comparison queries, identify discrepancies, and manually patch records in one or both systems. At financial scale, even a 1% error rate represented thousands of records.
The manual process also had no audit trail — if a reconciliation introduced an error, there was no way to trace when, what, or why a record changed. The goal: replace the entire process with a fault-tolerant automated pipeline that handled failures explicitly and logged everything.
Extract → Validate → Transform → Load, with failure paths as first-class citizens.
Building failures into the design — not bolting them on after.
Records that fail schema validation or the Salesforce upsert don't crash the pipeline — they go to a dead-letter queue with a full record of why they failed. The retry scheduler uses exponential backoff with a max of 5 attempts before alerting a human. This means the pipeline handles transient network issues automatically, and only real data problems surface to the team.
The biggest source of data corruption in ETL pipelines is late validation — transforming first, discovering type mismatches after the load. I inverted this: every extracted record is validated against the target schema before any transformation begins. Invalid records are quarantined immediately. 95% accuracy isn't a measurement of success rate — it's a statement that 5% of source records had genuine data quality issues in the upstream system.
Every Salesforce upsert writes a before/after snapshot to a PostgreSQL audit table before the write executes. If a reconciliation introduces an error, we can trace exactly when it happened, what the record looked like before and after, and which pipeline run caused it. This was the primary ask from the compliance team and turned what was previously a black box into a fully observable system.