Change Data Capture (CDC) is a data integration technique that monitors a source database for row-level changes — inserts, updates, and deletes — and captures those changes as they occur, enabling downstream systems (data warehouses, data lakes, analytics platforms) to receive only the changes since the last synchronisation rather than re-extracting the entire source table on every refresh. Where a traditional full-table extraction reads every row of the GL_BALANCES table on every ETL run (an increasingly expensive operation as the table grows over years of transactions), a CDC-based extraction reads only the rows that changed since the last extraction — dramatically reducing extraction volume, reducing load on the source OLTP database, and enabling higher-frequency data refresh cycles that would be impractical with full-table extraction at scale.
CDC Implementation Methods
| CDC Method | How It Works | Source Requirements | Latency |
|---|---|---|---|
| Log-based CDC | Reads the database transaction log (redo log in Oracle, WAL in PostgreSQL) — captures every committed change without modifying source tables | Database admin access to transaction logs; Oracle Supplemental Logging enabled | Near-real-time — seconds to minutes |
| Trigger-based CDC | Database triggers fire on INSERT/UPDATE/DELETE and write change records to a CDC staging table | Permission to create triggers and staging tables on source DB | Real-time — immediate on commit |
| Timestamp-based CDC | Queries source table for rows where LAST_UPDATED_DATE > last extraction timestamp | Source table must have a reliable last-updated timestamp column | Batch — refresh frequency dependent on extraction schedule |
| Oracle GoldenGate | Oracle’s enterprise log-based CDC — captures Oracle redo log changes and replicates to target systems | Oracle GoldenGate licence; Oracle Supplemental Logging enabled | Near-real-time (seconds) |
CDC for Oracle EBS Finance Data Integration
In GCC enterprise finance data warehouses built on Oracle EBS source data, the standard CDC approach is timestamp-based — filtering the EBS GL_BALANCES, AP_INVOICES_ALL, and AR_PAYMENT_SCHEDULES tables on their LAST_UPDATE_DATE column to extract only rows modified since the previous extraction run. Oracle EBS maintains LAST_UPDATE_DATE on most transactional tables, making timestamp-based CDC feasible without requiring database log access. The limitation of timestamp-based CDC is that it cannot detect deletes — a GL journal that is reversed and deleted in Oracle EBS will not appear in the timestamp-based change extract after deletion, and the data warehouse will continue to carry the deleted row indefinitely unless a periodic reconciliation scan identifies and removes it. Log-based CDC (Oracle GoldenGate) resolves the delete detection problem but requires additional infrastructure investment and Oracle licensing.
CDC and ZATCA Real-Time Reporting
Saudi Arabia’s ZATCA Phase 2 Fatoora integration creates a specific CDC use case for GCC enterprises: the ZATCA clearance status of e-invoices must be tracked as it changes — an invoice submitted to ZATCA for clearance starts in Pending status and transitions to Cleared or Rejected within minutes. For organisations that include ZATCA clearance status in their finance dashboards (showing what percentage of today’s invoices have been cleared, pending, or rejected), CDC-based monitoring of the ZATCA integration table — capturing clearance status changes as they occur — provides real-time visibility without polling the ZATCA API repeatedly.
What Goes Wrong in Practice
The most common CDC implementation failure in Oracle EBS environments is a timestamp-based extraction that misses changes — because the source table’s LAST_UPDATE_DATE column has not been consistently maintained. In Oracle EBS, the LAST_UPDATE_DATE column is updated by Oracle’s standard API calls but may not be updated by direct SQL modifications made by third-party tools, custom extensions, or DBA scripts applied outside Oracle’s standard API layer. Changes applied directly to Oracle EBS tables without using Oracle’s APIs will not have updated LAST_UPDATE_DATE values and will be invisible to timestamp-based CDC — remaining in the source system but not appearing in the data warehouse update. All Oracle EBS modifications must be made through Oracle’s standard API layer to maintain CDC reliability.
How Loop Wise Solutions Implements CDC
We design CDC strategies for each source table in the finance data integration scope — selecting the appropriate method based on delete detection requirements, available infrastructure, and acceptable latency — and implement reconciliation checks that periodically compare source and target row counts to detect untracked changes that the CDC mechanism may have missed.
