Load orchestration

Load stack#

Load dependencies are one of the first bottlenecks in a growing warehouse.

The previous chapter explained how to load one table safely. A real pipeline must load many tables safely, in the right order, without making independent tables wait unnecessarily.

At small scale, load order can be managed manually. A developer can hard-code that customers load before orders, orders load before order items, and reference tables load before the facts that use them.

This does not last.

As the warehouse grows, dependencies multiply. A pipeline may contain hundreds or thousands of tables, with tens of thousands of dependency relationships between them. At that scale, load order cannot be safely maintained as a hand-coded sequence.

The load stack approach begins from a simple observation: if the warehouse knows which tables depend on which other tables, load order can be computed at run time.

Orchestrating a load#

The load stack approach rests on three objects.

The dependency table by itself knows the structure of the pipeline, but not what has already loaded.

The load stack by itself knows what has started and ended, but not what depends on what.

The load candidate view combines both. It asks: given the dependency structure, and given the current execution state, which tables are ready to load now?

Once readiness is visible, workers can claim ready tables independently at run-time, without pre-planned orchestration.

Dependency metadata#

Dependency metadata records which tables must be loaded before another table can load.

Suppose a sales pipeline contains six tables:

• Sales.Customer
• Sales.Product
• Reference.Calendar
• Sales.Order
• Sales.OrderItem
• Sales.OrderSummary

The dependency graph might look like this.

The same dependency structure can be recorded in a metadata table.

Example structure of Pipeline.LoadDependency

Each row states one direct dependency.

Sales.Order depends on Sales.Customer. Sales.OrderItem depends on both Sales.Order and Sales.Product. Sales.OrderSummary depends on Sales.Order, Sales.OrderItem, and Reference.Calendar.

Some tables have no dependencies. In this example, Sales.Customer, Sales.Product, and Reference.Calendar can load at the beginning of the workflow because nothing upstream needs to finish first.

The load stack#

The load stack is the list of tables to be loaded in a workflow.

At the beginning of the workflow, the stack is refreshed. Every table that belongs to the workflow is added to Pipeline.LoadStack, with [Is started] = false and [Is ended] = false.

Initial Pipeline.LoadStack

The load stack records execution state. It does not itself decide which table is ready.

A table is ready only when all the tables it depends on have ended. This condition is called execution readiness.

Load candidates#

A load candidate is a table in the load stack that is ready to load.

At the beginning of the workflow, only tables with no unfinished dependencies are ready.

Conceptually, a table is a candidate when:

• it is in the current load stack;
• it has not started;
• none of its dependencies are unfinished.

This logic also works for tables with no dependencies. Their dependency list is empty, so they have no unfinished dependencies.

A load candidate view can expose this list at any time. The view can be simplified if Pipeline.LoadDependency has already been expanded to contain all upstream dependencies, not only direct dependencies.

Initial Pipeline.LoadCandidate

Using the load stack#

The load stack can be used to implement dependency-aware parallel loading.

The basic algorithm is simple.

Step 1—Refresh the load stack#

At the beginning of the workflow, populate Pipeline.LoadStack with the tables that need to load.

Set [Is started] = false and [Is ended] = false for all rows. This creates a visible queue of work for the workflow.

The stack may contain the full pipeline, or only a selected part of it. If a subset of tables is being loaded, the stack should include the necessary upstream tables required for that subset to load correctly.

Step 2—Start workers#

Start any number of workers.

Workers may be stored procedures, functions, notebooks, jobs, or other executable units. Each worker repeatedly checks the load stack until there is no more work to claim.

A worker performs the following loop:

1. Check whether any unstarted tables remain in Pipeline.LoadStack.
2. Query Pipeline.LoadCandidate.
3. If no candidate is available, pause briefly and poll again.
4. If a candidate is available, claim one table by setting [Is started] = true.
5. Load the table.
6. Set [Is ended] = true when the load finishes, fails, or aborts.
7. Repeat until no unstarted tables remain.

The worker exits when there are no rows in Pipeline.LoadStack where [Is started] = false.

Step 3—Claim a candidate#

Once the workers have started, they can pick up load candidates for load.

If three workers are available at the start of the example workflow, each can claim one initial candidate.

After workers claim the initial candidates

A table that has started but not ended is not available to other workers. Downstream tables also remain blocked until their dependencies have ended.

Claiming from the load stack must be done in a way that prevents multiple workers from claiming the same table. This can be achieved by wrapping the read from the load candidate view and the update to the load stack in a transaction. This avoids race conditions.

A simplified claim pattern is:

begin transaction;

select top 1
    [Table name]
from Pipeline.LoadCandidate
where [Workflow ID] = 9001
order by [Table name];

update Pipeline.LoadStack
set
    [Is started] = 1,
    [Worker ID] = 'W01',
    [Load start datetime] = current_timestamp
where
    [Workflow ID] = 9001
    and [Table name] = @TableName
    and [Is started] = 0;

commit transaction;

The exact syntax will vary by SQL platform. The important point is that the worker should not select and claim a candidate in two unsafe steps. The claim should happen atomically.

Step 4—Load, end, and repeat#

When a table finishes loading, the worker marks it as ended.

update Pipeline.LoadStack
set
    [Is ended] = 1,
    [Load end datetime] = current_timestamp
where
    [Workflow ID] = 9001
    and [Table name] = 'Sales.Customer'
    and [Worker ID] = 'W01';

Suppose Sales.Customer finishes first.

After Sales.Customer ends

Sales.Order now becomes a load candidate because its only dependency, Sales.Customer, has ended.

Current Pipeline.LoadCandidate

A worker can now claim Sales.Order.

After Sales.Order is claimed

Suppose Sales.Product, Reference.Calendar, and Sales.Order have all finished.

After Sales.Product, Reference.Calendar, and Sales.Order end

Sales.OrderItem is now ready because both of its dependencies have ended.

Current Pipeline.LoadCandidate

Sales.OrderSummary is not ready yet. Even though Sales.Order and Reference.Calendar have ended, Sales.OrderItem has not.

After a worker claims and finishes Sales.OrderItem, the stack looks like this.

After Sales.OrderItem ends

Sales.OrderSummary is now ready.

Current Pipeline.LoadCandidate

After Sales.OrderSummary ends, the workflow is complete.

Completed Pipeline.LoadStack

The workflow ends when there are no rows in Pipeline.LoadStack where [Is started] = false.

Handling failure#

If a table fails or aborts, the worker still marks it as ended.

This is deliberate. In this pattern, [Is ended] does not mean the load succeeded. It means the worker has finished attempting the load, so the table no longer blocks the workflow.

update Pipeline.LoadStack
set
    [Is ended] = 1,
    [Load end datetime] = current_timestamp,
    [Load status] = 'Failed',
    [Failure message] = 'Source extract timeout'
where
    [Workflow ID] = 9001
    and [Table name] = 'Sales.Order'
    and [Worker ID] = 'W01';

This allows the pipeline to continue. Downstream tables can still run, usually against the last successfully loaded version of the upstream table.

This is consistent with the principle of fault tolerance. A failed table should be visible, logged, and available for repair, but it should not automatically stop unrelated or downstream work unless the pipeline has a specific reason to enforce that behaviour.

The important point is that the decision is visible. The load stack records what started, what ended, and what remains available to run. Errors should be separately logged.

Consequences of the load stack#

Correct order is computed, not hard-coded#

The load candidate view ensures that a table is offered for loading only when its upstream dependencies have ended.

This means correct order does not depend on a developer manually maintaining a sequence of execution steps. The order emerges from dependency metadata and the current state of the load stack.

When a new table is added, the data engineer adds its dependency metadata. The load candidate view uses that metadata to determine when it is ready. The orchestration logic does not need to be rewritten for every new dependency path.

Parallelism follows from readiness#

Parallelism is a consequence of the readiness rule.

If three independent tables are ready, three workers can claim them. If ten independent tables are ready, ten workers can claim them. If only one table is ready, only one table is available.

There is no need for a central process to decide in advance which tables should run side by side. Workers simply claim tables that are ready.

The number of workers can be adjusted according to available resources. The load stack does not need to know how many workers exist. It only needs to expose which tables are ready.

Moreover, the algorithm is load-balanced by default with a greedy approach to assigning work. As soon as a load candidate is ready, a worker picks up the job. There are no situations where a table is ready to load and a worker is available, but the load does not happen.

Execution state is visible and manipulable#

Because the load stack is a table, the state of the workflow can be inspected while the load is running.

The data engineer can see:

• which tables have not started;
• which tables are currently loading;
• which tables have ended;
• which worker claimed each table;
• which tables are blocked;
• which tables failed or aborted.

But visibility is only part of the value.

Because execution state is stored as data, it can also be manipulated. A table can be placed back on the stack by setting [Is started] = false and [Is ended] = false. A subset of tables can be added to the stack for a targeted repair load.

This makes the workflow controllable while it is running.

The load is no longer a hidden procedural state inside a scheduler. It is visible and editable execution state.

Higher-frequency loads#

Most warehouses run in daily batches, but some parts of a warehouse may need to load more frequently.

A load stack allows a selected path through the pipeline to be loaded more often than the whole warehouse. This works if the tables in that path are efficient enough to meet the required frequency, but the orchestration pattern does not need to change.

A path that is incrementally processed can be loaded hourly, every few minutes, or continuously, while the rest of the warehouse remains on its normal batch schedule.

This gives the data engineering team a gradual path toward higher-frequency loading. Instead of adopting a dedicated streaming platform for the whole warehouse, the team can increase load frequency only where the business need and pipeline efficiency justify it.

Cross-technology loading requires shared state, not shared tooling#

The load stack can also support orchestration across technologies.

A complex pipeline may use SQL stored procedures, Spark notebooks, Python functions, or other processing technologies. A central orchestration product may provide connectors for each of these technologies, but the load stack takes a different approach.

The only requirement is that each worker can read and update the shared load stack.

For example, Pipeline.LoadStack and Pipeline.LoadCandidate may be implemented in a SQL database. A SQL procedure, Spark notebook, and Python process can all participate as workers if they can connect to the database, claim candidates, and update the stack.

This makes the load stack technology-agnostic. The coordination happens through shared execution state.

Key ideas

Load dependencies are one of the first bottlenecks in a growing warehouse.

A load stack exposes the pipeline’s execution queue as data. Allowing dependency-aware parallel loading without a central scheduler.

Because execution state is data, the load stack can be inspected, retried, edited, and reused for partial or continuous loading.

The same pattern supports partial loads, higher-frequency loads, and cross-technology orchestration.