From Hourly to Real-Time: Architecting Event-Driven Pipelines with CDC

The operational reality was worse than the latency metrics suggested. Daily changes for many tables sat below 5%, yet the system reprocessed 100% of records every cycle. Row-level deletions weren’t natively supported, forcing workarounds that further complicated data consistency.

SQLite Trigger-Based Pattern

For SQLite, common in edge devices and embedded applications, CDC requires trigger-based patterns since SQLite doesn’t expose logical replication streams:

CREATE TABLE IF NOT EXISTS cdc_log (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  table_name    TEXT NOT NULL,
  operation     TEXT NOT NULL,
  row_id        INTEGER NOT NULL,
  payload       TEXT NOT NULL,
  created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed     INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_cdc_processed ON cdc_log(processed);

Triggers capture changes into this log table, which a streaming worker polls and publishes to Kafka or Pulsar:

CREATE TRIGGER users_after_update
AFTER UPDATE ON users
BEGIN
  INSERT INTO cdc_log (table_name, operation, row_id, payload)
  VALUES (
    'users',
    'UPDATE',
    NEW.user_id,
    json_object(
      'user_id', NEW.user_id,
      'email', NEW.email,
      'plan', NEW.plan,
      'updated_at', NEW.updated_at
    )
  );
END;

A Python worker then polls unprocessed rows and publishes to your streaming platform of choice. For production deployments, switch from boolean processed flags to checkpoint-based ID tracking to reduce write amplification, and ensure WAL mode is enabled for concurrency between writes and streaming reads.

SQL Server Native CDC

SQL Server offers native CDC capabilities that operate at the transaction log level, avoiding the trigger overhead:

USE [YourDatabaseName];
GO
EXEC sys.sp_cdc_enable_db;
GO

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'YourTableName',
  @role_name     = NULL,
  @supports_net_changes = 1;
GO

SQL Server CDC reads the transaction log directly, capturing changes with minimal performance impact on the transactional database. Change tables store the operation type, timestamp, and affected data, queryable via functions like cdc.fn_cdc_get_all_changes.

The CDC landscape reveals a latency spectrum that vendors often obscure. Salesforce’s ecosystem illustrates this clearly: traditional polling-based integrations introduce 15, 60 seconds of staleness, while Change Data Capture pushes events with sub-second latency.

Platforms like Ampersand leverage Salesforce CDC for sub-second webhooks, critical for AI agents and voice products where polling latency breaks the user experience. In contrast, polling-based platforms (Nango at 15, 30 second intervals, Paragon and Workato with queue-based polling) introduce architectural latency that compounds under load, what engineers call the “noisy neighbor” problem, where one customer’s large sync backs up the queue for everyone else.