Change Data Capture From ERP Into a Lakehouse: Log-Based CDC vs Nightly Batch

Almost every ERP-to-analytics pipeline starts as a nightly batch: at 2am, extract the tables you need, load them into the lakehouse, refresh the reports, done by morning. It is the default for good reasons — it is simple to build, easy to reason about, and it fits the mental model everyone already has of "last night’s data is ready today".

For a great many reporting needs, that is genuinely enough. A management pack read each morning, a month-end close, weekly KPI trending — none of these need data fresher than the overnight run, and a batch pipeline serves them reliably with minimal moving parts.

The reason to look past batch is not fashion. It is that three specific pressures — freshness, source load, and deletes — eventually make the simple pattern the limiting factor, and that is when log-based CDC earns its complexity.

The first limit is freshness. A nightly batch means every number is up to a day old; an operations team that needs to act within a shift cannot wait for tomorrow’s load. The batch window itself cannot simply be run more often without hitting the second limit.

That second limit is source load. Re-extracting large tables hammers the ERP during the batch window, and on a system that also serves overnight jobs — or that operates across time zones where "2am" is someone else’s business hours — that load is a real cost. Running the batch every hour to improve freshness multiplies the load, often to the point the ERP team objects.

The third limit is deletes, and it is the quiet one. A naïve batch that extracts "rows modified since yesterday" cannot see a row that was deleted — the row is gone, with no timestamp to catch it. Full reloads see deletes but are expensive; incremental batches are cheap but miss them. This is exactly how cancelled orders linger as open in the lakehouse long after the ERP removed them.

Log-based change data capture reads the database’s own transaction log — the record the database keeps of every committed change — and streams those changes out as a feed of inserts, updates and deletes, in order, as they happen. Instead of asking the ERP "what changed?", it watches what the database already wrote down.

This fixes all three batch limits at once. Freshness becomes near-real-time, because changes flow continuously rather than in a nightly window. Source load during business hours drops sharply, because reading the log is far lighter than re-extracting tables — the database already produced the log for its own recovery. And deletes are captured natively, because a delete is a logged event like any other, carrying the information needed to apply it downstream.

On a Microsoft Fabric estate this often arrives through Mirroring, which replicates a source database into OneLake as Delta tables with low latency and no pipeline to hand-build; on Databricks the equivalent lands through Delta Live Tables or a streaming ingest. Either way the lakehouse receives a change feed carrying the operation type, which is exactly what downstream logic needs to stay in step with the ERP.

Log-based CDC is not a free upgrade, and selling it as one leads to disappointment. It needs access to the database transaction log, which the ERP or database team must enable and which some locked-down or SaaS ERPs do not expose at all — and where it is not available, CDC is simply off the table for that source. The setup is more involved than a batch extract, and it introduces a streaming component that has to be operated, monitored and reasoned about when something lags.

There is also real engineering in applying the change feed correctly — ordering, idempotency so a replayed change does not double-apply, and handling schema changes in the source. A streaming pipeline that is built carelessly can drift from the source just as a batch can, only in more complicated ways. CDC removes the batch problems and adds streaming ones; it does not remove the need for discipline.

So the honest framing is a trade: CDC buys freshness, lighter source load and native deletes, and pays in setup, log access and operational complexity. On a slow-changing reference table read once a day, that trade is a bad one. On a high-volume orders table that operations act on within the hour, it is clearly worth it.

The decision is rarely "CDC or batch" for the whole estate — it is per table, by requirement. Put log-based CDC on the handful of high-value transactional tables where freshness, source load or deletes actually matter: orders, shipments, inventory movements, financial postings. Leave nightly batch on the slow-changing reference and master data — product, customer, chart of accounts — where daily is fine and simplicity is a virtue.

This hybrid is usually the right answer because it spends complexity only where it pays. You get near-real-time, delete-aware feeds on the tables operations live on, and cheap, simple batch on everything that does not need more. The estate as a whole stays as simple as it can be while being as fresh as it needs to be.

So decide table by table against three questions: how fresh does this genuinely need to be, can the source bear repeated extraction, and do deletes matter here? Where the answers are "current", "no" and "yes", CDC earns its place. Where they are "daily is fine", "yes" and "not really", batch is the better engineering. Most estates land on a mix — and that mix, chosen deliberately, is the mark of a platform designed rather than defaulted.