Understand Load Control

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse · Oracle

Load control is the set of tables, procedures, tasks or notebooks that biGENIUS-X generates to run the loaders of a solution in dependency order. This article explains the load-control options, the objects of the native load control and how configuration, plans, executions and recovery relate. Read it before you choose LoadControl Options or follow the load-control workflows.

The dbt generators have no load control and no load-control properties; dbt runs the models in the order of their references.

Load-control options

The project property LoadControl Options selects how the loaders are orchestrated:

Native Load Control (Microsoft SQL Server, Oracle): tables and stored procedures in the target database register the loaders, build execution plans, run them and log each step.
Multi-Threading Notebook (Databricks, Fabric Lakehouse, Fabric Warehouse): a notebook runs the loader notebooks in dependency order on four threads.
Depending Tasks, Stage-Driven Tasks, Near-Realtime Tasks (Snowflake): Snowflake tasks run the loaders.
Airflow (all of the above except Fabric Warehouse): the generator writes an Apache Airflow DAG with one task per loader; Airflow orchestrates the load.

The rest of this article describes the native load control of Microsoft SQL Server and Oracle. The platform articles under Load data with a native load control describe the notebooks and tasks.

Objects of the native load control

On Microsoft SQL Server the load control lives in its own schema, LC by default, set with the placeholder loadcontrol#loadcontrol#schema_name. It holds these tables:

LoadConfig, LoadConfig_LoadObject, LoadConfig_LoadObject_Dependency: the load configuration, its load objects (one per loader of a model object, part and dataflow) and their dependencies.
ExecutionPlan, ExecutionPlan_LoadObject: execution plans and the load objects each plan runs.
Execution, Execution_Step, Execution_LoadObject_Dependency: each run of a plan, its steps and the dependencies copied for that run.

Oracle uses the same model with other names; the workflows list them under Platform differences.

The four load-control tasks

Configure: the deployment registers a load configuration named after the project, with all load objects of the active model objects and their dependencies.
Plan: an execution plan selects load objects of one or more configurations. The generated plan DEV_<project> contains all load objects of the project.
Execute: Start_Execution creates an execution with one step per load object and runs each step when the steps it depends on are finished.
Monitor: procedures and views show the status, result, row counts and error of every step.

Errors and recovery

Each load object of a plan has an error behavior. With ContinueOnError, the default, a failed step disables the steps that depend on it, and all other steps run. With AbortOnError, the first failed step cancels the execution. Resume_Execution starts a new execution with the steps that did not succeed. It keeps the load effective timestamp of the failed execution.

One plan has at most one active execution. Starting the plan again while it runs adds a second worker to the active execution instead of starting a new one.

Deploying again

Configuration keeps the IDs of existing load objects, so execution plans stay valid when you deploy a changed model. The property Deploy LoadControl decides whether a deployment also recreates the load-control tables. That article describes how several projects share one load control.

Example

The Data Vault project Datavault.Mssql registers 103 load objects. One run of DEV_Datavault.Mssql fails because the Stage table of CUSTOMER is missing:

Start_Execution [54]: Execution plan [DEV_Datavault.Mssql] completed
by execution [3] with 34 FAILED or DISABLED load steps.

The number after Start_Execution is the session ID of the worker.

The Stage Loader of CUSTOMER has the result Failure. The 33 loaders that depend on it are Disabled, and the other 69 steps have Success. After the table is restored, Resume_Execution runs execution 4 with the 34 remaining steps, and all of them succeed.