← All work

CDC-without-CDC replication engine

Engineering owner, with support from senior engineers · Aviation client, reusable across enterprises · 2026 · Updated September 2026

A from-scratch replication engine that detects change on source systems with no native CDC, using hierarchical hashing to land Fivetran-format SCD2 history in Snowflake.

Outcome Validated end to end; production rollout was in progress when I left.

  • Distributed Systems
  • Data Engineering
  • Snowflake
  • OpenFlow / NiFi
  • Apache NiFi (OpenFlow)
  • Snowflake stored procedures
  • SCD2
  • Merkle-style hierarchical hashing
  • PostgreSQL
  • CI/CD

Context

Some enterprise source systems have no native change data capture. Standard connectors cannot tell what changed without doing a full reload every cycle, which is slow, expensive, and loses historical change records. An aviation client (and other enterprises with the same gap) needed Fivetran-format SCD2 history without being able to turn on CDC at the source.

This is the hardest engineering problem I have worked on: detect change efficiently when the source refuses to tell you what changed.

My role and ownership

I built the engine from scratch and then hardened it for production alongside senior engineers. I owned the core detection design and the Snowflake-side history application; the hardening of concurrency and cluster behaviour was collaborative.

Technical approach

  • Designed a hierarchical-hash (“CDC without CDC”) engine on Apache NiFi (OpenFlow). It hashes the source in tiers (super-block, then block, then row) and drills only into partitions whose hash changed, so most unchanged data is skipped cheaply.
  • Emitted insert/update/delete deltas to a buffer, and a Snowflake stored procedure applied Fivetran-format SCD2 history from those deltas.
  • Hardened it for production with senior engineers:
    • Root-caused a multi-node NiFi cluster bug where CRON triggers fired on every node, duplicating sync cycles and breaking merges. Fixed it by pinning trigger execution to the primary node.
    • Made the merges dedup-safe (QUALIFY ROW_NUMBER) and added an atomic single-flight cycle lock with stale self-heal so overlapping runs could not corrupt state.
  • Proved it end to end against a live PostgreSQL source: initial load, update, delete, idempotency, and multi-partition drill-down.

Tradeoffs

Hierarchical hashing trades a little upfront hashing cost for a reduction in data transferred for mostly unchanged sources, which is the right trade for large, mostly-static sources. The harder tradeoffs were about correctness under concurrency: I chose strict idempotency and a single-flight lock over raw throughput, because a replication engine that is occasionally wrong is worse than one that is slightly slower.

Outcome

In a live PostgreSQL validation, the engine detected one changed row out of 1,000 with no false positives. Feasibility was proven end to end, and production rollout was in progress when I left. Because it is source-agnostic, it is reusable across other clients with the same no-CDC gap.

Reflection

The cluster bug was the sharpest lesson: a scheduler behaving correctly on one node can be silently catastrophic across a cluster. It pushed me to treat idempotency and single-flight execution as first-class requirements rather than afterthoughts. If I took it further, I would add automated chaos tests around node failover to prove the concurrency guarantees continuously, not just at validation time.