Stopping the Full Refresh: Implementing CDC in Source Engine

The Cost of the Full Table Scan

When syncing large datasets, the default approach is often a full refresh: wiping the target table and reloading everything from the source. As datasets grow into the millions of rows, this becomes unsustainable. It creates massive spikes in CPU and I/O on the source database, consumes excessive network bandwidth, and leaves the target data stale for the duration of the load.

The solution is Change Data Capture (CDC). Instead of querying the table state, CDC reads the database's transaction logs to identify exactly which rows were inserted, updated, or deleted. By moving to an incremental ingestion model, you reduce the load on your production database and achieve near real-time data availability.

How Source Engine Handles Log-Based Ingestion

Source Engine implements CDC by acting as a replica of the source database. Rather than executing SELECT * statements, the CDC connector hooks into the database's write-ahead log (WAL) or binary log. This allows the engine to capture events as they happen without requiring a "last_updated" timestamp column on every table.

The CDC Pipeline Flow

• Log Monitoring: The connector monitors the source database logs for commit events.
• Event Parsing: Raw log entries are converted into a structured format (Insert, Update, Delete).
• Schema Mapping: The engine maps source types to target types, ensuring the destination schema can accommodate the changes.
• Target Application: Changes are applied to the destination in the same order they occurred at the source to maintain referential integrity.

Worked Example: PostgreSQL Logical Replication

To implement CDC with a PostgreSQL source, you must enable logical replication. This allows the database to stream changes to an external consumer like Source Engine.

Step 1: Database Configuration

Run these commands as a superuser on the PostgreSQL instance to prepare the WAL for logical decoding:

-- Set the WAL level to logical
ALTER SYSTEM SET wal_level = 'logical';

-- Restart the database for changes to take effect
-- (Required for wal_level changes)
RESTART DATABASE;

Step 2: Creating the Replication Slot

Source Engine requires a replication slot to ensure the database does not discard logs before the engine has read them. You can create this via the Source Engine UI or manually via SQL:

-- Create a logical replication slot using the pgoutput plugin
SELECT * FROM pg_create_logical_replication_slot('source_engine_slot', 'pgoutput');

Step 3: Verification

To verify the connection is active and processing data, check the replication lag. Run this on the source database:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn 
FROM pg_replication_slots 
WHERE slot_name = 'source_engine_slot';

Expected Result: The active column should be t (true). If the confirmed_flush_lsn is advancing, the engine is successfully consuming logs.

The Baseline Consistency Trade-off

CDC is not a "plug-and-play" replacement for a full load; it is a supplement. A common point of failure is attempting to start CDC on an existing table without a consistent baseline. If you start the log reader today, you have no data for records created yesterday.

The standard workflow requires an Initial Snapshot. Source Engine performs a one-time full load of the table while simultaneously recording the current log position (LSN). Once the snapshot is complete, it "plays back" all logs that occurred during the snapshot process. If the snapshot is interrupted or the log retention period on the database is too short, you will encounter data gaps, requiring a full reset of the connector.

Practical Limitations

• Log Disk Space: If the Source Engine connector goes offline, the source database will hold onto all transaction logs until the connector returns. This can lead to disk exhaustion on the production server if not monitored.
• Schema Evolution: While CDC handles data changes, structural changes (like ALTER TABLE) may require a manual refresh of the mapping depending on the specific database version.
• Permission Overhead: CDC requires higher privileges (e.g., REPLICATION permissions) than standard SELECT access, which may require security approval in locked-down environments.

Moving to a CDC Pilot

To transition away from full refreshes, start with a single, high-volume table. Enable logical logging, establish a replication slot, and monitor the lag metrics in the Source Engine dashboard. Once you confirm that the target reflects source updates within seconds—without spiking source CPU—you can migrate the rest of your pipeline to an incremental model.