CDC at Enterprise Scale: Lessons from Managing 55TB of Healthcare Supply Chain Data
How one healthcare company built a CDC framework that processes 55 TB monthly across 64 ERP systems to power national medical device supply chain operations.
Change Data Capture at Scale: Lessons from Building a National Healthcare CDC Framework
When most engineers think about Change Data Capture (CDC), they think about syncing a few database tables or keeping a data warehouse up to date. That's a perfectly valid use case.
But what happens when CDC isn't just a technical pattern—it’s the backbone of a system that tracks medical devices used in trauma surgeries across an entire country?
That's the reality I worked with as a Data Engineering Specialist at one of America's largest healthcare companies. Over three years, I helped architect and implement a CDC framework that processed 55 TB of data monthly, ingested changes from 64 heterogeneous ERP systems, and served as the critical data layer for national healthcare supply chain operations.
This article shares what I learned—the hard technical lessons, the architectural decisions, and the principles I'd apply again in any large-scale CDC implementation.
What Is CDC and Why Does It Matter at Scale?
Change Data Capture is the process of identifying and capturing changes made to data in a source system—insertions, updates, and deletes—and delivering those changes to downstream systems in near real time.
At small scale, CDC is straightforward. At enterprise scale, across dozens of heterogeneous source systems, it becomes one of the most complex problems in data engineering.
The fundamental challenge: every source system speaks a different language.
SAP has its own change tracking mechanisms. JD Edwards handles deltas differently. Legacy ERPs may have no native CDC support at all. Oracle, Teradata, and SQL Server each have their own approaches to transaction logs, timestamps, and change detection.
When you're ingesting from 64 of these systems simultaneously, you don't just need a CDC solution—you need a CDC framework: one that is system-agnostic, scalable, reliable, and maintainable.
The Architecture: Building a System-Agnostic CDC Framework
The core design principle we followed was separation of concerns. The framework had to work regardless of the source system, the change detection method, or the downstream consumer.
Here's how we structured it:
Layer 1 — Source Connectivity & Change Detection
Different ERP systems required different CDC strategies:
- Log-based CDC for systems that exposed transaction logs (Oracle redo logs, SQL Server CDC)
- Timestamp-based CDC for systems with reliable last-modified timestamps
- Diff-based CDC for legacy systems with no native change tracking—comparing snapshots to identify deltas
- Trigger-based CDC for systems where we could implement database triggers
The key insight here: don't force every source into the same CDC pattern. Respect the capabilities of each source system and build adapters accordingly.
Layer 2 — Ingestion & Standardization
Once changes were captured at the source, they flowed into a standardized ingestion layer built on Apache Kafka for real-time streaming and PySpark on Databricks for batch processing of high-volume historical loads.
Every change event was normalized into a standard envelope format, regardless of source:
{
source_system: "SAP_ECC",
table_name: "VBAK",
operation: "UPDATE",
timestamp: "2020-06-15T14:32:00Z",
before_image: { ... },
after_image: { ... },
transaction_id: "TXN_ABC123"
}
This standardization was critical. Downstream consumers shouldn't need to know whether a change came from SAP or JD Edwards—they just consume events.
Layer 3 — The Lakehouse (Bronze, Silver, Gold)
All ingested changes landed in a Lakehouse architecture built on Delta Lake:
- Bronze layer — raw change events, exactly as received, immutable
- Silver layer — cleansed, deduplicated, business-key-resolved records
- Gold layer — aggregated, domain-specific datasets ready for analytics and reporting
The Bronze layer was our safety net. If anything went wrong downstream, we could always reprocess from raw captured changes. This proved invaluable during schema evolution events—when a source system changed its table structure, we had the full history to replay.
Layer 4 — Orchestration & Monitoring
At 55 TB/month across 64 source systems, pipeline failures are not a question of if—they're a question of when and how fast you detect them.
We built orchestration using Apache Airflow with custom sensors that monitored:
- Lag between source change and downstream arrival
- Record count reconciliation at each pipeline stage
- Schema drift detection
- Data quality validation gates before Silver and Gold promotion
Every pipeline had an SLA. Breaching an SLA triggered automatic alerts. We maintained 99%+ pipeline reliability across all source systems.
The Hardest Problems We Solved
Problem 1 — Deduplication at Scale
At high ingestion rates, duplicate events are inevitable. Network retries, source system quirks, and at-least-once delivery guarantees all contribute.
Our solution: deterministic event IDs generated from a hash of source system + table + primary key + transaction timestamp. Before writing to Silver layer, we deduplicated against a Bloom filter of recently seen event IDs. This eliminated duplicates with minimal performance overhead.
Problem 2 — Schema Evolution
In a 64-ERP environment, source schemas change. Columns get added, renamed, or removed. Types change.
We handled this with schema registry integration and automated schema evolution policies:
- Backward-compatible changes (adding nullable columns) — automatically handled
- Breaking changes — triggered alerts and required manual review before pipeline resumption
- Full schema history maintained in the Bronze layer for reprocessing
Problem 3 — Handling Late-Arriving Data
Distributed systems produce late-arriving data. A change captured at the source at 2 p.m. might arrive at the ingestion layer at 4 p.m. due to network delays or system backpressure.
We implemented watermarking in our Spark streaming jobs—allowing a configurable late-arrival window before closing a micro-batch. For our use case, a 30-minute late-arrival window balanced correctness with processing latency.
Problem 4 — The Initial Load Problem
CDC captures changes. But when you onboard a new source system, you need the full current state—the initial snapshot—before CDC can meaningfully begin.
For large tables (hundreds of millions of rows), this initial load had to run without impacting the source system. Our approach: parallel chunked extraction with chunk sizes calibrated to source system capacity, running during off-peak hours, with resumability in case of failures.
Data Quality: The Hidden Challenge of CDC at Scale
When data powers national healthcare supply chain decisions—medical device tracking, inventory management, clinical operations—data quality isn't optional. It's a patient safety issue.
We implemented a multi-layer data quality framework:
- At ingestion: schema validation, null checks on critical fields, referential integrity validation
- At Silver promotion: business rule validation, cross-system reconciliation, statistical anomaly detection
- At Gold promotion: domain-specific validation, SLA compliance checks, executive dashboard readiness gates
The result: an 85% reduction in data quality incidents compared to the prior batch-based approach. Stakeholders went from discovering data issues days later to being alerted within minutes.
