How an asynchronous ETL chain works: optimization and flexibility
Extract everything, transform everything, then load everything. In a chain built this way, without checkpoints or error isolation, a single record can force you to rerun completed work. It is easy to draw on a whiteboard, and expensive when you need to recover at real volume.
The operational question is simple: after a failure at 80%, do you know what was loaded, what remains, and how to resume without duplicating writes?
The problem is the lack of recovery
A batch does not have to be one all-or-nothing transaction. It can process chunks, save checkpoints and isolate rejected records; Spring Batch, for example, supports restarting interrupted steps. Nor does “synchronous” necessarily mean “wait for the entire batch”: SSIS can pass rows through a synchronous transformation as they arrive.
The dangerous case is the opaque batch: the report announces a failure at 80%, without identifying committed writes or allowing a controlled restart. You pay again to extract and transform data that was already fine. A million records behind one mishandled edge case, and there goes the cutover window.
Asynchronous decouples the steps
An asynchronous architecture with queues is one way to decouple extract, transform and load. Each step becomes a separate station, connected to the others by a durable queue:
- extraction drops records into a queue, within the planned capacity limits;
- transformation consumes that queue as soon as a record is available, without waiting for extraction to finish;
- loading takes over the same way, downstream.
The stages can work in parallel on different records. While the last extract is still arriving, the first records can already be loaded. Operations that require a complete set, such as some aggregations, and dependencies between objects still need their own waiting points.
Optimization comes from parallelism and isolation
Once decoupled, each station is sized for what it does. Transformation is CPU-heavy? Add consumers if the work can be shared. The target limits connections? Cap loading. The queue absorbs a temporary mismatch, but sustained throughput remains bounded by the bottleneck: if producers outpace consumers, backlog and delay grow. Monitor the queue and slow upstream production before capacity runs out — backpressure. This is the limit of queue-based load leveling.
A rejected record can be isolated in an error queue while independent objects proceed. A rejected part can still hold up order lines that reference it: track those dependencies and release children after the parent is validated. Per-record qualification provides an individual verdict; it does not remove relationships between objects.
Flexibility comes from the same break
Decoupling makes changes easier if the route is wired correctly. Competing consumers on one queue share the work: they increase a stage’s capacity. Mandatory enrichment belongs before loading, which consumes its output. A second target that needs the same data requires distribution to separate queues or subscriptions, using a publish-subscribe model. It must not take a share of the messages intended for the first target.
Replay also needs preparation. Retain the inputs and rule versions, identify the scope affected by a correction, including its dependencies, and reapply changes in the required order. Delivery can be repeated: writes must be idempotent, for example through a stable key and a controlled update. An older replayed state must not overwrite a newer one either. A queue alone guarantees neither that outcome nor processing order with multiple consumers.
What it changes for a migration
A migration must distinguish loaded data, rejected data and data waiting on a dependency. Asynchronous processing makes decoupling and targeted recovery easier; a batch with the same tracking and restart capabilities can also support successive corrections.
These are the capabilities behind the Run / Fix loops in our methodology. Measure the gain through recovery: what you can correct and replay without unnecessarily repeating completed work, while keeping the target consistent. Full rehearsals are still needed to measure the complete path and the cutover window.
Where this fits in a full programme is covered in the ERP data migration guide, and on the PLM side, where load order is dictated by the product structure, in the PLM data migration guide.
Two planning questions have their own articles: how to combine targeted iterations and full rehearsals, and how to prepare the delta window using the same business rules with explicit change capture and reconciliation.