Change Data Capture for ERP

ERP databases are a harder CDC target than a typical application database, for reasons specific to how ERPs are built: heavily normalized schemas with dozens of tables representing one logical business object, batch-oriented posting processes that write many rows in a single transaction, and — for a platform like Acumatica running on SQL Server — a multi-tenant schema where every table carries a company/branch identifier that CDC consumers need to respect. Getting ERP change data into a warehouse in near-real-time is valuable for finance and ops dashboards, but the source-side complexity is real.

Why poll-based extraction falls short for ERP

The traditional ERP integration pattern — a nightly or hourly job querying tables filtered on LastModifiedDateTime — misses hard deletes entirely (a voided invoice, a reversed journal entry) and can miss intermediate states when multiple updates land between polling windows, which matters when downstream reporting needs to reconcile against an audit trail. It also puts repeated read load on the same database handling live transaction processing, competing for the same I/O the ERP itself needs during business hours. Log-based CDC avoids both problems by reading the transaction log directly rather than querying tables.

-- Enable CDC at the database level
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on a specific table, scoped to a capture instance
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'ARInvoice',
  @role_name     = NULL,
  @capture_instance = N'dbo_ARInvoice';

-- Downstream consumers query the change table directly,
-- or a connector reads it and republishes to Kafka/warehouse.
SELECT *
FROM cdc.dbo_ARInvoice_CT
WHERE __$start_lsn > @last_processed_lsn
ORDER BY __$start_lsn;

The multi-table business object problem

A single AR invoice in a normalized ERP schema touches a header table, one or more line-item tables, tax detail tables, and GL distribution tables — a "row changed" event on any one of them is meaningless downstream without the others. The workable pattern is capturing change events per table as CDC naturally produces them, then reassembling the complete business object in a downstream transformation step (a streaming join or a batch reconciliation job) rather than trying to make the CDC layer itself understand ERP-level business objects. Keep CDC dumb and table-scoped; do the domain reassembly downstream.

Batch posting and transactional boundaries

ERPs frequently post in large batches inside a single database transaction — a month-end close process might insert or update tens of thousands of rows in one commit. Log-based CDC preserves transactional ordering, so downstream consumers can group change events by their originating transaction (via the log sequence number or an equivalent) to process a batch posting as one logical unit rather than thousands of unrelated row events, which matters if a downstream job needs to know a close process completed rather than reacting to each row individually.

Wrapping up

CDC against an ERP database works the same way it does anywhere else at the mechanical level — read the transaction log, don't poll — but the normalized multi-table schema, tenant/branch scoping, and vendor-controlled schema changes are ERP-specific complications worth planning for upfront. Keep the CDC layer scoped to individual tables and reassemble business objects downstream, and treat tenant/branch filtering as a correctness requirement, not an optimization.