6 min readJust now
–
Recently, I worked with an organization that operates business-critical workloads across two active data center sites while modernizing core systems running on a mix of mainframe and distributed platforms. Because these systems support continuous transaction processing, a traditional cut-over or bulk-load migration approach would have introduced unacceptable downtime and data divergence risks. The organization required a data movement approach that would maintain ongoing consistency across both sites throughout the modernization effort and continue to do so post-migration.
To achieve this, Change Data Capture (CDC) was selected to continuously **replicate changes from the mainframe system of record to independent target databases in both sites. **The arch…
6 min readJust now
–
Recently, I worked with an organization that operates business-critical workloads across two active data center sites while modernizing core systems running on a mix of mainframe and distributed platforms. Because these systems support continuous transaction processing, a traditional cut-over or bulk-load migration approach would have introduced unacceptable downtime and data divergence risks. The organization required a data movement approach that would maintain ongoing consistency across both sites throughout the modernization effort and continue to do so post-migration.
To achieve this, Change Data Capture (CDC) was selected to continuously **replicate changes from the mainframe system of record to independent target databases in both sites. **The architecture uses CDC fan-out, where each committed change is captured once from the mainframe logs and applied to two parallel Oracle targets, with no database-to-database replication between them. This ensures both sites remain synchronized while preserving operational independence for failover or isolation scenarios.
In this article, I’ll explain:
✔️ What CDC is
✔️ Key approaches to CDC and when each method is appropriate
✔️ How CDC supports auditability, traceability, and compliance requirements
✔️ Operational and sizing considerations to ensure performance and consistency
What is Change Data Capture (CDC)?
CDC refers to techniques that detect and propagate incremental changes (inserts, updates, and deletes) from a source system to one or more target databases or downstream services. Instead of reloading entire tables, CDC moves only the data that has changed, reducing load on operational systems while keeping dependent applications and analytics environments up to date.
CDC is particularly valuable when source systems must remain online during modernization, when data must be synchronized across multiple data centers, or when downstream consumers (analytics, integration platforms, microservices, etc.) require fresh operational data without directly querying core systems.
Common use cases include:
- Modernizing legacy or mainframe applications while keeping existing interfaces active
- Maintaining synchronized data copies across two or more operational sites
- Feeding cloud data platforms or data lakes without bulk reprocessing
- Supporting real-time event pipelines, dashboards, and ML feature stores
Press enter or click to view image in full size
Source: Author
Common methods
There are several methods for executing CDC. Common types of CDC include:
- Log-based CDC
- Trigger-based CDC
- Polling-based CDC
- Timestamp-based CDC
The appropriate method depends on system architecture, volume profile, and access constraints:
Press enter or click to view image in full size
Source: Author
1) Log-based CDC (Preferred for High-Volume / Core Systems)
Every transactional database records changes in a transaction log. Log-based CDC reads these logs directly to capture changes without touching application queries or table logic.
This approach is commonly used when replicating from mainframe IMS or Db2 into distributed relational platforms, because it avoids adding workload to the mainframe transaction path. IBM provides log-based CDC capabilities across mainframe and distributed environments through different products. IBM InfoSphere Data Replication** **supports operational replication and multi-target fan-out to systems such as Oracle, Kafka, and Db2 LUW, while IBM Data Gate enables low-overhead log-based replication from Db2 for z/OS into analytics platforms like Db2 Warehouse and cloud lakehouses.
Strengths
- Lowest source impact (no table scans/triggers).
- Near-real-time: CDC streams changes with low delay.
- Supports zero-downtime coexistence and phased cutovers
- Capture can be paused more safely for things like bulk loads, or if the business is facing huge load demands (like Black Friday sales)
Considerations
- Requires log access/permissions (sometimes DBA/security friction).
- Some schema evolutions (e.g., column rename vs. drop/add) require additional handling depending on DB engine/version.
PostgreSQL example:
-- 1) Enable logical decoding (one-time DB setting; requires restart)ALTER SYSTEM SET wal_level = logical;-- 2) Tell Postgres which tables to publish changes for.CREATE PUBLICATION cdc_pub FOR TABLE public.customers, public.orders;-- 3) Create a logical replication slot that streams changes.SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput');
2) Trigger-based CDC
Trigger-based CDC uses database triggers to capture row changes (INSERT/UPDATE/DELETE) as they happen and write them into an audit/shadow table for downstream consumption (replication, analytics, ML features, compliance).
Strengths
- Works when log access is restricted.
- Fine-grained capture at source. Triggers can filter by DML type (insert/update/delete), columns changed, or calling user/app; you can redact PII or split history into multiple audit tables based on business rules.
- Only changes that meet defined criteria are written to the audit/shadow table.
Considerations
- Highest source overhead.
- Can impact latency and contention under load.
- Troubleshooting complexity is generally higher than log-based CDC.
Example: Capturing only changes when a customer’s subscription tier changes, for downstream churn analytics.
CREATE TABLE customer_tier_changes ( customer_id INT, old_tier TEXT, new_tier TEXT, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);-- Trigger function: capture only UPDATEs where 'tier' actually changedCREATE OR REPLACE FUNCTION record_tier_change()RETURNS TRIGGER AS $$BEGIN IF NEW.tier IS DISTINCT FROM OLD.tier THEN INSERT INTO customer_tier_changes (customer_id, old_tier, new_tier) VALUES (OLD.id, OLD.tier, NEW.tier); END IF; RETURN NEW;END;$$ LANGUAGE plpgsql;-- Trigger: fires after each row update on 'customers'CREATE TRIGGER trg_record_tier_changeAFTER UPDATE ON customersFOR EACH ROWEXECUTE FUNCTION record_tier_change();
3) Polling-based CDC
Polling-based CDC periodically queries a table to detect new or changed records based on a monotonic column such as a version counter or last-updated timestamp.
Strengths
- Straightforward implementation (just a SELECT query).
- No schema modifications and no need for elevated DB privileges.
- Works well for append-mostly or slow-to-change tables.
Considerations
- Not real-time: Data freshness depends on polling frequency.
- Potential extra load if polling large tables too frequently.
- Requires reliable tracking of the “last processed” offset (e.g., stored in a control table or pipeline state).
Example: An e-commerce team updates product pricing and descriptions several times a day. The analytics platform only needs to refresh product details for reporting every 10 minutes, not in real time.
-- Fetch only products that changed since last polling cycleSELECT product_id, name, price, version_numberFROM productsWHERE version_number > :last_version_numberORDER BY version_number ASC;
*:last_version_number** is the highest version_number processed previously (stored by the pipeline).*
4) Timestamp-based CDC
When your source tables include a reliable “last changed” column (for example, last_modified), you can incrementally pull only the rows that changed since your last successful load. This is common in line-of-business apps (orders, customers, invoices) where schemas already track update times for audit or UI sorting.
Strengths
- Simple & low-friction when a trustworthy timestamp/version column already exists.
- Tool & DB agnostic (works with JDBC/ODBC, SQL engines, views).
- Cost-aware: only pulls deltas; no full-table scans if indexed.
Considerations
- Load from polling: frequent queries can add read pressure; mitigate with indexes & replicas.
- Deletes: pure timestamp filters miss deletes unless you implement soft deletes (e.g.,
is_deleted) or a deletion log. - Correctness depends on clocks & discipline: the column must update on every change; clock drift or app-side misses undermine accuracy.
- Late/Backdated updates: ensure your job overlaps windows or uses
>=with a small safety lag to avoid missing boundary rows.
Example: Incrementally export changed orders to your warehouse every 5 minutes. The job remembers the last processed timestamp (*:watermark_ts*) and re-reads with a 2-minute look-back to avoid missing late commits. Soft deletes are included via *is_deleted*.
-- Incremental pull of changed orders since the last watermark,-- with a small safety window to catch late commits.-- Assumes:-- 1) orders.last_modified updates on INSERT/UPDATE/soft delete-- 2) an index on (last_modified, id)-- 3) a soft delete flag orders.is_deleted (boolean)SELECT o.id, o.customer_id, o.status, o.total_amount, o.is_deleted, o.last_modifiedFROM orders AS oWHERE o.last_modified >= (:watermark_ts - INTERVAL '2 minutes')ORDER BY o.last_modified, o.id;
After loading, set the new watermark to the max *last_modified* you actually ingested.
Audit Replication and Change Traceability
CDC pipelines often enrich change events as they are streamed by including audit metadata such as:
- **Who **made the change? (e.g., user ID, service identity, application session)
- **What **type of change (INSERT / UPDATE / DELETE)?
- **When **did it change? (commit timestamp or log sequence timestamp)
The result is an immutable audit trail that supports compliance, replay, traceability, and historical reconstruction.
Sizing and Performance Considerations
CDC pipelines typically operate either continuously (log-based and trigger-based) or at scheduled intervals (timestamp/polling-based). In both cases, replication jobs require predictable compute, network, and memory capacity to ensure changes are captured and applied within acceptable latency.
Key dimensions include:
- CPU / vCPU allocation for log parsing, serialization, and parallel apply
- Thread / worker concurrency based on change volume and latency requirements
- Network bandwidth and socket stability, especially across regions or load balancers
- Memory buffers to smooth bursty workloads and reduce replication lag
Summary
CDC provides a low-disruption path to system modernization by synchronizing data across environments in near real time while core systems remain online. By capturing and replicating only incremental changes, CDC reduces load on operational systems and supports coexistence patterns required during phased migrations, multi-site architectures, and real-time data applications.