← All case studies

Case study · Fintech · Self-directed investing

Keeping a brokerage platform in sync with change data capture

An automated ETL pipeline that moves investor account data from MSSQL to MongoDB every day with full consistency, built on Debezium, Kafka and Node.js microservices.

Client
ETC Brokerage Services (eHUB)
My role
Technical lead
Employer
Zymr Systems Pvt. Ltd.

The problem

Investor account data had to move from Microsoft SQL Server into MongoDB every day, and for a brokerage the copy has to be complete: a missed update or a lost delete is an account that shows the wrong state.

Copying whole tables on a schedule gets slower as data grows, puts load on the source database, and makes it hard to prove that nothing was skipped. The pipeline needed to capture every change, survive restarts and retries, and stay maintainable as more flows were added.

What I owned

  • Owned the architecture decisions across the stack and the cloud as technical lead
  • Designed the ETL flow and the boundaries between the microservices
  • Mentored the engineers building the services
  • Introduced Jest test automation as a quality gate for releases

Architecture

SourceCaptureStreamProcessTargetSQL ServerCDC enabledFile exchangeSFTPDebeziumSQL Server connectorApache Kafkachange-event topicsSync servicesNode.js · TypeScriptBullMQ workersasync jobs · retriesMongoDBinvestor accountschange logeventsconsumeenqueueupsert
Changes flow left to right: Debezium captures them from SQL Server, Kafka carries them, Node.js services and BullMQ jobs apply them to MongoDB.

How a change travels

  1. A row changes in SQL Server and the database records it in its change data capture tables.
  2. Debezium reads the committed change and publishes it to a Kafka topic, keyed by the row’s primary key so changes to the same row stay in order.
  3. Node.js / TypeScript sync services consume the topic and apply the change to the matching MongoDB document.
  4. Work that is slow or may need retrying runs as BullMQ jobs, together with the SFTP file transfers, so a failure is retried without holding up the stream.

Key decisions

Change data capture instead of scheduled table copies

Only changed rows move, deletes are captured as well as inserts and updates, and the source database is not hit by repeated full-table queries.

Kafka between capture and processing

The stream absorbs bursts, consumers resume from their last committed offset after a restart, and keying by primary key keeps each record’s changes in order.

BullMQ for asynchronous jobs

Retries with backoff, visibility into failed work, and a clean place for file transfers and slower steps that should not block real-time sync.

Tests as a release gate

Jest coverage around the sync and transformation logic made changes safer to ship and reduced production defects by about 15%.

Results

  • Reliable daily synchronization from MSSQL to MongoDB with full data consistency for investor accounts
  • About 15% fewer production defects after introducing Jest test automation
  • Architecture owned end to end as technical lead, with engineers mentored along the way
  • Node.js
  • TypeScript
  • Apache Kafka
  • Debezium
  • BullMQ
  • MSSQL
  • MongoDB
  • SFTP
  • Jest

Client names and results are taken from my résumé. Architecture is simplified and shared without confidential details.