From Manual Execution to Intelligent ETL Process Orchestration

How to design an orchestration layer to coordinate data processes, validate dependencies, and improve operational traceability.

The Problem: Processes That Relied Too Heavily on Human Intervention

In organizations that process large volumes of operational and statistical data, ETL (Extract, Transform, Load) workflows can become a bottleneck. The challenge is not simply to execute each procedure, but to ensure that it runs in the correct order, with the required data, and with appropriate mechanisms for detecting and recovering from failures.

In the scenario that motivated this project, several processes had to run sequentially. Some collected information for specific periods, while others calculated indicators based on that data. Running a step before its dependencies were ready could produce incomplete or inconsistent results.

The workflow presented several risks:

• Execution depended on someone starting procedures in the correct order.
• There was no centralized queue to coordinate requests and reduce duplicate executions.
• Failures outside business hours could go unnoticed for hours.
• An interrupted process could leave an outdated execution status and block new attempts.
• Scattered logs made it difficult to reconstruct what had run, when it had run, and what the outcome had been.

As a result, the operation was difficult to monitor and required human attention throughout extended execution windows.

The Solution: An Orchestration Layer

The proposed solution was to introduce a layer responsible for coordinating executions, validating dependencies, and recording results—without having to replace every existing process.

The architecture was organized around three capabilities.

1. Centralized Execution Queue

Each request is recorded in a queue with enough information to manage it and track its progress, including:

• Process type and processing period.
• Priority and execution parameters.
• Request status.
• Creation, start, and completion timestamps.
• Information needed to detect duplicate requests.

The queue separates an execution request from its actual processing. It also helps prevent equivalent requests from running simultaneously when business rules do not allow it.

2. Worker and Dependency Validation

A processing component picks up pending requests and checks whether the conditions required to run them have been met.

For example, a statistical calculation should not begin until the data collection processes that feed it have completed successfully for the relevant period. If dependencies are not ready yet, the request can remain pending and be evaluated again later.

This validation turns dependencies—which may previously have existed only in operators’ knowledge—into explicit system rules.

When tasks have complex relationships, it is useful to model them as a directed graph and validate that no cycles prevent a valid execution order from being determined.

3. Execution Monitoring and Recovery from Stale States

A monitoring process reviews executions marked as active and compares their recorded status with observable signals from the underlying process.

If an execution is no longer active but its record still says it is running, the system can flag it for review or classify it as interrupted according to predefined rules. It can also apply duration limits to identify processes that exceed their expected runtime.

This check must be designed carefully: the existence of a process identifier alone does not always prove that a task is functioning correctly. Restarts, identifier reuse, and differences between the application state and the external process state must be taken into account.

Controlled Retries After Failures

Not every failure is permanent. Some are caused by temporary connectivity issues or resource availability problems. For this reason, the system uses a configurable retry policy:

1. Record the failure and the corresponding attempt.
2. Wait for an interval before trying again.
3. Gradually increase the waiting period when the policy allows it.
4. Enforce a maximum number of attempts.
5. Flag the execution for review and notify the appropriate people, when applicable, once retries are exhausted.

Using backoff prevents continuous retries that could put additional pressure on an already failing resource. Limits should be adapted to the nature of each process and the available operational window.

Monitoring and recovery parameters should be adjustable in a controlled manner, with validation and change traceability, without requiring changes to each task’s core logic.

Design Challenges

During development, the main challenges went beyond launching processes. It was necessary to define how the system should behave under concurrency, interruptions, and incomplete data conditions.

Challenge	Approach
Dependencies between processes	Explicit dependency rules and execution-order validation.
Simultaneous requests	Concurrency controls and constraints to prevent disallowed duplicates.
Interrupted executions	Periodic monitoring and reconciliation between recorded and observable states.
Limited operating windows	Task scheduling based on operational constraints.
Historical data by period	Clear identification of the processing period and integrity checks.
Notifications and results	Status recording and notifications when configured conditions are met.
Legitimately empty tables	Validation that distinguishes a valid absence of data from a processing failure.

One important lesson was that an empty table does not necessarily indicate an error. In certain periods, there may legitimately be no events for a given category. Validation should reflect business rules rather than assume that every absence of records is a failure.

Incremental Evolution and Idempotency

The solution evolved in several stages. First, control and audit structures were defined. Notifications, integration with existing processes, the execution queue, dependency validation, retries, and monitoring for interrupted tasks were then added.

This incremental approach made it possible to organize the work around individual capabilities and validate each change before moving forward.

Database migrations, in particular, must be designed carefully. Idempotency helps ensure that an operation can be repeated without producing unintended additional effects. In production environments, it is also important to review preconditions, back up data when appropriate, record changes, and avoid unnecessary destructive operations.

Making a migration idempotent does not, by itself, make the entire business process idempotent. Each task must define what should happen if it runs more than once or resumes after an interruption.

Results: More Control and Less Manual Monitoring

Adding orchestration helped move the operation toward a more controlled model, with centralized statuses, pre-execution validation, and recovery mechanisms. Benefits to pursue and assess through operational metrics include:

• Fewer errors caused by incorrect execution sequences.
• Greater visibility into process status.
• Earlier detection of interrupted executions.
• Less need for continuous manual monitoring.
• Better traceability for incident analysis and execution reconstruction.
• More orderly recovery from transient failures.

To measure impact rigorously, compare equivalent periods before and after implementation. Useful metrics include total cycle duration, the number of failed executions, the number of manual interventions, average failure-detection time, and the percentage of executions completed successfully.

Avoid publishing improvement percentages or specific operational figures unless they are documented, the comparison is valid, and disclosure has been authorized.

Lessons Learned

1. Recorded States Must Be Verifiable

A status such as IN_PROGRESS is a stored declaration, not a guarantee that the process is still alive. Monitoring should compare records with real signals and account for ambiguous cases.

2. Dependencies Must Be Explicit

Relying on each operator to know the correct order is not a scalable strategy. Rules should be part of the system and verified before each stage begins.

3. Missing Data Needs Context

Some periods have no events for certain categories. Validation must distinguish a legitimate absence of data from an incomplete collection process.

4. Idempotency Should Be Considered from the Start

Tasks and migrations need defined behavior when faced with repetition, retries, and interruptions. This reduces the risk of duplicating effects or leaving data in inconsistent states.

5. Retries Also Consume Resources

Repeating an operation immediately can make an incident worse. Limits, waiting intervals, and conditions for stopping attempts should be part of the recovery policy.

6. Observability Is Part of the Architecture

Recording statuses, timings, relevant parameters, and results makes it easier to detect problems and understand what happened. Logs that are published or shared must exclude credentials, personal data, internal identifiers, and other confidential details.

Technologies Used

The project brought together database, backend, ETL, and monitoring-interface technologies. At a high level, the stack included:

• Database: Oracle Database.
• Backend: Node.js and Express.
• Database integration: Oracle driver for Node.js.
• Data processing: ETL tool.
• Frontend: React and a UI component library.
• Scheduling: Database scheduler.
• Deployment and execution: Process manager for the backend and a web server for the frontend.
• Authentication: Authentication and authorization mechanisms appropriate to the environment.

The technology choices matter less than clearly defining each component’s responsibilities, failure boundaries, and communication patterns.

Conclusion

ETL orchestration is not simply about automating a sequence of commands. It is about building a mechanism that understands dependencies, controls concurrent requests, records results, and responds predictably to interruptions.

Automation adds value when it reduces reliance on manual steps and makes executions more observable and recoverable. Human oversight remains important, but it can focus on exceptions and decisions that require judgment instead of continuously monitoring every task.

If you are designing a similar solution, start by documenting dependencies, defining execution states, and deciding how the system should recover from failure. Adding a queue and a worker is only part of the work; reliability comes from the rules that connect all the components.