In the modern data landscape, “stale data” is often as good as no data. To keep data warehouses, lakes, and analytics platforms in sync with operational systems, engineers rely on a process called Change Data Capture (CDC).

If you are looking to move away from bulky batch processing and toward real-time streaming, understanding CDC is your first step.

What exactly is CDC?

Change Data Capture (CDC) is the process of identifying and capturing changes made to a database—including Data Manipulation Language (DML) like inserts, updates, and deletes, as well as Data Definition Language (DDL) like schema changes.

Instead of copying an entire database every night, CDC allows you to track only what has moved, changed, or disappeared. However, the “how” matters just as much as the “what.” Depending on your database access and performance requirements, there are three primary methods to achieve this.

1. Log-Based CDC: The Gold Standard

Every modern database records its activities (DML and DDL) in a transaction log (e.g., Redo logs in Oracle, Write-Ahead Logs in PostgreSQL). In this method, a CDC application reads these logs directly.

• How it works: The application parses the transaction logs to find activities related to specific tables of interest.
• Pros: * Minimal Impact: It doesn’t query the live tables, so performance impact on the source DB is negligible.
  • Comprehensive: It captures every single change, including deletes.
  • Transactional Consistency: It captures the commit boundary of each transaction providing a valid unit of work to act on by downstream CDC processes.
• Cons: * Complexity: It requires scanning all logs, even if your specific tables haven’t changed much.
  • Access: The user must have high-level permissions to read the database’s internal transaction logs.

2. Trigger-Based CDC: The Watchdog

As the name implies, this method relies on database triggers—small snippets of code that run automatically when a specific event occurs.

• How it works: You create triggers on your tables of interest. When an insert, update, or delete happens, the trigger catches it and writes the change to a separate staging table. A downstream process then reads from this staging table.
• Pros: * Targeted: It only records changes for the specific tables you care about, unlike log-based methods that scan the entire DB activity.
• Cons: * Performance Hit: Every write to your main table now requires a second write to the staging table, adding overhead to the operational database.
  • Management: Requires permission to create and maintain triggers on source tables.

3. Query-Based CDC: The Simple Approach

This is the most “manual” version of CDC. It relies on specific columns within your tables to identify what is new.

• How it works: The application regularly queries the table for records where a “CDC column” (like last_updated_timestamp or an incremental ID) is greater than the last recorded value.
• Pros: * Easy Setup: No complex log access or trigger logic required.
• Cons: * Blind to Deletes: If a row is deleted, there is no “timestamp” left to query, so the change is missed entirely.
  • Performance & Duplication: Frequent polling can tax the database. Without a primary key, you also risk duplicating data.
  • Schema Requirements: Requires your DDL to include a tracking column, which might not always exist in legacy systems.

Choosing the Right Method

Final Thoughts

CDC is the backbone of the “Real-Time Enterprise.” While Log-Based CDC is generally the preferred method for high-volume, mission-critical systems due to its low impact, Query-Based or Trigger-Based methods remain excellent alternatives for simpler use cases or restricted environments.