Blog

Polarising Website

Change Data Capture for Regulated Enterprises: Design, Not Tools

Secure infrastructure supporting transaction log capture

Change data capture (CDC) streams row-level changes from a source database so downstream systems stay continuously synchronised without repeated full-table reloads. The main approaches are log-based, trigger-based, timestamp polling and application-level events, each trading off freshness, load and complexity differently. Choose CDC when freshness genuinely matters and full reloads are becoming too slow or too expensive to sustain.

TL;DR:

  • Log-based CDC provides the most faithful ordering and minimal source load but requires specific permissions and licensing, making setup more complex.
  • Consumers must implement idempotent upsert logic with commit position checks to avoid applying duplicate changes after restarts or re-deliveries.
  • Maintaining a schema history separate from events and storing schema versions alongside applied records ensures correct interpretation of schema changes over time.
  • Proper risk management involves monitoring capture latency, consumer lag, error rates, and retention, as falling behind retention windows can prevent recovery without fresh snapshots.
  • Data in CDC streams often includes sensitive information, requiring encryption, column filtering, and strict access control to safeguard against unintended exposure.
Polarising
polarising.com
Design More Reliable Data Integrations
Polarising helps enterprises build AI integrated systems that connect complex business processes with governance and accountability from the outset.

Explore Polarising

Table of Contents

Overview and core concepts

A change event is a structured record describing one modification to one row: an insert, update or delete, along with the data involved. Most CDC systems generate these events by watching a capture instance, the mechanism attached to a source table or its transaction log that decides what gets captured and how.

Two ideas anchor everything else. The first is the offset or log sequence number (LSN), a marker that identifies a precise position in the source’s change stream. Every connector needs to know its current offset so it can resume exactly where it left off after a restart. The second is the distinction between a snapshot and a stream: a snapshot captures the full state of a table at one moment, while the stream carries everything that changes afterwards. Nearly every production CDC pipeline needs both, an initial snapshot for baseline state and a continuous stream for everything after.

This differs from periodic ETL in a structural way, not just a speed way. Traditional batch ETL re-reads whole tables on a schedule and infers what changed, which gets expensive and stale as tables grow. CDC instead captures each change once, at the moment it happens, and pushes it downstream.

A simplified update event typically carries:

  • Operation type: insert, update or delete.
  • Key: the primary key identifying the affected row.
  • Before and after state: the row’s previous and new values (update only).
  • Commit position: the LSN or offset marking exactly when the change committed.

CDC implementation families and trade-offs

Four implementation families cover almost every production CDC system, and picking the wrong one for your constraints is the most common early mistake.

Log-based capture reads the database’s own transaction log (binlog, WAL, redo log) rather than querying tables directly. It preserves commit ordering faithfully and adds minimal load to the source because it never runs extra queries against live tables. The trade-off is setup complexity: log-based connectors need specific permissions, log retention settings and, for some databases, licensed features. Debezium’s connectors for MySQL, PostgreSQL and SQL Server all work this way.

Trigger-based capture attaches database triggers to source tables, writing each change into a shadow table. It works on databases without accessible transaction logs and gives fine control over what gets captured. The cost is real: triggers execute inside the original transaction, adding latency to every write and creating another object to maintain through schema changes.

Timestamp or version polling queries a table periodically for rows where a last_modified column is newer than the last poll. It is the simplest to build with no special database features required. Academic and systems literature notes that frequent polling competes directly with OLTP workload and can reduce transaction throughput on the source, and it cannot reliably detect deletes without extra columns.

Application-level events and the outbox pattern have the application itself emit change events, often into an outbox table written inside the same transaction as the business data. This carries richer semantics than a raw row diff, business intent rather than just “column X changed”, but it shifts consistency responsibility onto application code. Where a source platform cannot expose a native CDC stream, an outbox pattern preserves atomicity between the database write and the event publication better than publishing after the write completes.

A short decision checklist:

  • Need strict ordering and minimal source load: pick log-based capture.
  • Database has no exposed log but supports triggers: use trigger-based capture.
  • Freshness requirements are loose and simplicity matters most: timestamp polling can work.
  • Business events need semantic richness or the source is application-owned: use outbox or application events.

Event shape, schema history and metadata

A well-formed change event needs five fields to be genuinely useful downstream: the operation type, the key, the commit position or timestamp, the before and after payload, and a schema id or version. Missing any of these forces consumers to guess, which is where silent data corruption tends to start.

Schema history matters because source tables change. A column gets added, a type gets widened, a table gets renamed, and every event captured before and after that change needs to be interpretable correctly. Debezium’s documentation treats schema history as a first-class store, separate from the event stream itself, precisely so a connector can restart and still decode old events correctly against the schema version they were written under. SQL Server’s native CDC feature works similarly: it records commit LSN and operation metadata alongside each row and relies on tracked capture instances to interpret changes consistently.

Consumers carry real responsibilities too. Because CDC streams can redeliver events after a restart or a rebalance, consumers need idempotent apply logic, typically an upsert keyed on the primary key plus a check against the commit position, so a duplicate delivery never double-applies a change.

  • Deduplication key: combine the row key and commit position to detect repeats.
  • Upsert, not insert: write logic that overwrites cleanly rather than failing on conflict.
  • Ordering awareness: apply changes for a given key in commit order, even if the stream interleaves keys.

Pro Tip: Store the schema version alongside every applied record in the target, not just in the connector, so a downstream analyst can trace a strange value back to the exact schema it was captured under.

End-to-end CDC workflow and architecture

A reliable CDC pipeline is less about the capture mechanism and more about how state is managed from source to target. Debezium’s storage documentation frames this well: offsets, snapshot position and schema history determine whether a pipeline is safe to restart, far more than which capture method feeds it. The workflow generally runs in this order:

  1. Inventory the source: list the tables and columns that actually need capturing, and exclude anything irrelevant to reduce noise and load.
  2. Take a consistent initial snapshot: read the current state of every included table at one consistent point, so downstream systems start from a known baseline rather than an empty target.
  3. Record the starting log position: note the exact LSN or offset at the moment the snapshot completes, so streaming can pick up from precisely that point without gaps or duplicates.
  4. Stream committed changes to durable storage: push captured events into a broker such as Kafka or a managed topic service, persisting offsets so the connector can resume after any restart.
  5. Apply changes idempotently at the target: consumers upsert by key and commit position, tolerating redelivery without corrupting state.
  6. Reconcile periodically: run row counts or checksums between source and target on a schedule to catch drift the stream itself might miss.
  7. Monitor lag, errors and retention: track how far behind consumers are, how many records fail to apply, and how much retention headroom remains before old changes are cleaned up.

The operational artefacts that make this resumable, an offset store, a schema-history store and monitoring dashboards, are not optional extras. Without persistent offsets and schema history, a connector restart risks re-reading from the wrong position or misinterpreting events captured under an older schema. That single design decision, treating state management as seriously as the capture mechanism, separates pipelines that survive their first production incident from ones that do not.

Common use cases and decision guide

CDC earns its complexity in a specific set of scenarios, and understanding which one you are solving for shapes the whole design.

  • Real-time analytics and lakehouse ingestion: streaming changes into a warehouse or lakehouse keeps dashboards current without nightly batch jobs; research describes CDC feeding stream processors that maintain materialised views for exactly this purpose.
  • Replication and migration: continuous replication lets teams migrate databases with minimal downtime. The Government Digital Service’s PostgreSQL migration ran replication for around ten days, caught up within a couple of hours, and cut over with roughly 11 seconds of downtime.
  • Index and cache synchronisation: search indexes and caches stay accurate when they consume the same change stream as the primary store rather than being rebuilt on a schedule.
  • Audit feeds: a change stream gives a record of what happened and when, though it needs deliberate retention design to serve as a durable audit trail.
  • Event-driven integration: services react to changes elsewhere in the business without polling each other directly.

Choose CDC when freshness is a real requirement, latency tolerance is measured in seconds or minutes rather than hours, and the team has the operational maturity to run a persistent streaming pipeline rather than a scheduled job.

Operational concerns, observability and reliability

Production CDC pipelines fail quietly far more often than they fail loudly, which makes observability the difference between a minor blip and a data-integrity incident.

Four metrics matter most: capture latency (time from commit to event availability), consumer lag (how far behind the latest offset a consumer sits), failed or scan errors (records the connector or consumer could not process) and retention headroom (how much unconsumed history remains before cleanup removes it).

Retention is a hard operational dependency, not a tuning knob to ignore. Microsoft’s guidance on administering and monitoring change data capture notes that if a consumer falls behind past the retention and cleanup window, it cannot resume from its previous position at all: the history it needs has already been deleted. Monitoring lag and retention headroom together, rather than separately, is what catches this before it becomes unrecoverable.

CDC is not an automatic audit history and does not guarantee real-time delivery on its own. Microsoft’s own documentation on change data capture and related SQL Server features is explicit that cleanup jobs remove change-table entries and, once a consumer falls too far behind, resumption from prior positions becomes impossible without a fresh snapshot.

  • Schema drift: a DDL change on the source should trigger a schema-history update, not a silent misread of new columns.
  • Ad-hoc snapshots: keep a documented procedure for re-snapshotting a table when a consumer needs to rebuild state from scratch.
  • Replay recovery: test, before you need it, that a connector can be pointed at an older offset and correctly replay forward.
  • Cutover runbooks: document the exact sequence for freezing writes, confirming the stream has caught up, and switching reads to the new target.

Implementation checklist and best practices

A CDC rollout benefits from a fixed sequence rather than an improvised one, because most production incidents trace back to a step skipped early.

  1. Inventory tables and columns: capture only what downstream systems actually consume.
  2. Choose the capture method: log-based where the log is accessible, trigger-based or outbox where it is not.
  3. Plan the initial snapshot: define how consistency is achieved and how long the snapshot is expected to take.
  4. Persist offsets and schema history: treat both as durable stores from day one, not an afterthought added after the first outage.
  5. Build idempotent consumers: upsert by key and commit position before writing any downstream logic that assumes single delivery.
  6. Stand up monitoring dashboards: capture latency, consumer lag, failed records and retention headroom, visible in one place.
  7. Set lag SLAs and alerts: define what “too far behind” means numerically and alert on it automatically.
  8. Define retention policy explicitly: choose a window with enough margin for realistic consumer downtime, not the platform default.
  9. Test cutover and disaster recovery: rehearse the freeze, catch-up and switch sequence before relying on it in production.
  10. Document ownership and runbooks: name who is paged when lag breaches the SLA and what they do next.

Pro Tip: Rehearse a full replay from an old offset on a non-production environment before go-live. It is the single test most teams skip and the one that catches the most dangerous class of bug.

Security implications and best practices in CDC implementations

CDC streams often carry the same sensitive data as the source tables they capture, sometimes more visibly, because a stream of before-and-after values can expose data that a well-permissioned application view would normally hide. That makes access control on the change stream itself, not just the source database, a genuine requirement rather than a nice-to-have.

A few practices reduce exposure meaningfully. Encrypt data in transit between the source, the broker and every consumer, and encrypt data at rest in the schema-history and offset stores, since these persist copies of structural and positional metadata that can reveal business logic. Apply column-level filtering at capture time so regulated fields such as payment details or health records are excluded, masked or tokenised before they ever reach a shared topic, rather than relying on downstream consumers to filter responsibly. Restrict who can read the change topics with the same rigour applied to the source tables, and audit access to schema-history and offset stores, since tampering with either can corrupt every downstream consumer silently.

Sensitive data filtered before encrypted stream delivery

Connector credentials deserve particular care because they typically need elevated database permissions to read logs or manage triggers. Rotate these credentials on a schedule and scope them to the minimum access the capture mechanism actually needs, rather than reusing an existing administrative account out of convenience.

Compatibility and integration challenges with various databases and platforms

Not every database exposes change data the same way, and that inconsistency shapes what is realistically achievable. Some platforms offer native, well-documented CDC features (SQL Server’s built-in capture instances, PostgreSQL’s logical replication slots), while others require a third-party connector or fall back to trigger-based or polling approaches entirely.

Licensing and edition limits complicate this further: some database editions restrict log-based CDC to premium tiers, forcing teams onto trigger-based or polling alternatives they would otherwise avoid. Cloud-managed databases add another layer, since access to the underlying transaction log is sometimes restricted or mediated through a proprietary API rather than the open log format a self-hosted connector would expect.

Cross-platform pipelines, capturing from one database engine and writing to a different one, or fanning a single source out to several heterogeneous targets, need careful handling of type mapping. A timestamp, decimal or enumerated type rarely maps one-to-one between engines, and getting this wrong silently corrupts data rather than raising an error. Version compatibility between source database, connector and broker also needs active management: a connector certified against one database version may behave differently, or not connect at all, against the next major release.

Standards and protocols commonly used in CDC

CDC does not rely on one universal standard, but a handful of formats and protocols recur across most implementations. Kafka Connect, the framework most log-based connectors including Debezium build on, defines a common structure for source connectors, sink connectors and the topic-based delivery model between them.

Event payloads commonly use Avro or JSON with an accompanying schema, often registered in a schema registry so producers and consumers agree on structure without hardcoding it. This matters directly for the schema-history problem described earlier: a registry gives every event a traceable schema version.

Database-native protocols underpin the capture layer itself: MySQL’s binary log format, PostgreSQL’s logical decoding and replication slots, and SQL Server’s transaction log all define how a connector reads committed changes at the source. None of these are CDC-specific standards as such, they are the database’s own replication protocols, repurposed by connectors to feed a change stream. There is no single cross-database CDC protocol, which is precisely why connector choice and compatibility, covered above, matters so much in practice.

Tools and technologies available for CDC, including open source and commercial options

Debezium is the most widely adopted open source CDC platform, built on Kafka Connect, with connectors for MySQL, PostgreSQL, SQL Server, MongoDB and several other databases. It handles snapshotting, schema history and offset management as documented features rather than something teams have to build themselves.

Database vendors also ship native CDC features: SQL Server’s built-in change data capture, using tracked capture instances and functions such as sys.fn_all_changes_<capture_instance> to query captured rows, and PostgreSQL’s logical replication.

Commercial and managed platforms extend this further, offering hosted connectors, managed brokers and integration platforms that reduce the operational burden of running Kafka Connect clusters directly. Integration suites such as Boomi, MuleSoft, TIBCO and Solace increasingly incorporate CDC connectors alongside their broader integration tooling, useful where CDC is one piece of a larger enterprise integration estate rather than a standalone project.

Choosing between them comes down to the same decision criteria as the implementation family itself: how much operational ownership the team wants, what the source database actually supports, and whether CDC needs to sit inside a wider integration platform or can run as a dedicated pipeline.

Polarising perspective: enterprise CDC in regulated environments

Our approach to CDC always starts with the freshness and cutover objectives, never the tool. We define how fresh data genuinely needs to be, then pilot a snapshot against a representative slice of the source before committing to a full production build. That order matters: a pilot exposes schema quirks, retention gaps and load concerns while the cost of changing course is still low.

Working across finance, healthcare and telecommunications has shown us that regulated environments punish shortcuts, particularly around retention policy and schema-history handling, harder than most greenfield projects ever do. Experienced nearshore teams bring that experience directly into delivery rather than treating it as a lesson learned after an incident. We approach enterprise integration work the same way, with governance built in from the pilot stage rather than retrofitted before go-live.

— Polarising Team

How Polarising can help with your CDC pipeline

Designing a CDC pipeline that survives contact with production means getting the inventory, snapshot strategy and offset management right before a single connector goes live. That is the part most teams underestimate, and it is where an experienced delivery partner earns its place.

Polarising

Polarising works on exactly this kind of problem: enterprise integration, AI engineering and legacy modernisation for organisations running complex, regulated estates. An initial engagement typically starts with an inventory of the source systems and candidate tables, a pilot snapshot against a representative dataset, and a delivery plan covering monitoring, retention and cutover before full rollout begins. Teams experienced in finance, healthcare and telecommunications environments have carried this kind of work where getting schema history and retention wrong is not a minor bug.

If you are planning a CDC rollout or a related migration, our enterprise integration services are the place to start the conversation.

Sources

FAQ

What does change data capture mean?

Change data capture is a technique for identifying and streaming row-level changes, inserts, updates and deletes, from a source database as they happen. It replaces repeated full-table reads with a continuous stream of just what changed, typically sourced from the database’s transaction log, triggers or application events.

What are the disadvantages of change data capture?

CDC adds operational complexity: it needs persistent offset and schema-history storage, active monitoring of lag and errors, and explicit retention policy design. Polling-based CDC in particular can increase load on the source database and reduce transaction throughput, and if consumers fall too far behind the retention window, they may be unable to resume without a fresh snapshot.

How do I enable change data capture?

The steps depend on the database, but generally involve enabling log access or native CDC features, selecting the tables and columns to capture, taking a consistent initial snapshot, and configuring a connector to stream from the recorded log position onward. SQL Server, for example, uses dedicated system procedures and capture instances to track changes per table.

What is change data capture in Salesforce?

Salesforce Change Data Capture publishes change events for records in supported objects, letting external systems subscribe to inserts, updates, deletes and undeletes without polling the Salesforce API repeatedly. It follows the same underlying pattern as database-level CDC: a stream of change events rather than scheduled full extracts, though the definitions and configuration are specific to the Salesforce platform.