What Is Data Warehouse Automation?
One Metadata Model Generates Load Code, Orchestration And Run Logging
September 28, 2026
Data warehouse automation is software that generates your warehouse's load code, the orchestration that runs it in the right order, and the logging that records every run, all from one metadata model. It isn't a scheduler, which runs jobs somebody else wrote. It isn't a drag-and-drop ETL tool either, where every pipeline is still built by hand, just with a mouse. The test is simple: change the model, rebuild, and the code, the job graph and the run logging all change together.
That's the short answer. The rest of this post shows what that looks like as actual output, because the previous edition of this guide promised a complete picture and never put an artifact on the page. This edition does.
What It Isn't
A lot of software gets sold under this label, so it helps to separate the layers.
A scheduler or orchestrator (a SQL Agent job, an Airflow DAG, a Data Factory trigger) decides when things run. It doesn't write the things. If you hand it 300 loads, you wrote 300 loads.
A visual ETL designer gives you a canvas instead of a text editor. That's useful, but each pipeline is still an individual piece of work. Ten new source tables means ten more rounds of dragging, mapping and wiring, and ten more places for a naming convention to drift.
A SQL transformation framework like dbt is very good at what it does: versioned SQL models inside a warehouse that already has data in it. By design it doesn't generate SSIS packages, Data Factory pipelines or Databricks notebooks, and it doesn't pull data out of source systems. It works at a different layer, and plenty of teams run it next to a generator.
Automation, in the sense that matters here, starts from a description of the warehouse (sources, targets, keys, dependencies, batches) and compiles it. We covered the idea behind that in metadata-driven automation. Here we care about the three things the compiler has to emit.
The Three Things It Emits
Load code. The SQL, stored procedures, notebooks or data flows that actually move and reshape rows. Almost every tool in the category generates some of it.
Orchestration. The job graph: which loads run first, which run side by side, what waits on what, and what happens when one fails. This is where a lot of "automation" quietly stops. You get generated load code, and then someone hand-builds the pipelines that call it.
Run logging. A start, an end and an error record for every step, written somewhere you can query. Without it, the generated pipelines are a black box at 2 a.m.
People searching for tools that come with "prebuilt job steps" are usually asking for the second and third items. They already know how to write a load. What they want to stop writing is the plumbing around it.
One Setting Picks the Engine
In BimlFlex, the engine is a project-level setting called the Integration Template. The main choices are SQL Server Integration Services (SSIS), Azure Data Factory, and Databricks. Every pipeline in the project is generated for whichever one you pick.
The metadata that drives orchestration stays put when you switch. BimlFlex sorts objects into execution layers from the dependencies in your model, including any Depends on Object you set, so a satellite never runs before its hub. Each layer is a solve order. Inside a layer, the batch's Threads setting decides how many lanes of loads run at once, and an object with Own Thread checked gets a lane to itself so nothing queues behind it.
You define that once. What the build turns it into depends on the template.
The Same Batch, Three Shapes
Take one batch with two execution layers and a handful of loads in each. Here's what each template turns it into.
Execution layer: A sequence container per pass (SEQC - Load Tables Pass_1, Pass_2), each wired to the previous one by the batch's precedence constraint: An If Condition per solve order (Batch_SolveOrder_1, _2) that calls a sub-pipeline, chained in order: A sub-job per solve order, chained by a parent job
Threads: Execute Package tasks chained into lanes: Execute Pipeline activities that depend on the previous activity in the same thread: Notebook tasks with depends_on pointing at the previous task in the same thread
Own Thread: No precedence constraint, runs alongside: No dependency, starts immediately: No depends_on, starts immediately
On Databricks with Pushdown Processing turned on, the build writes that graph as a Databricks Asset Bundle. Here's the top of a generated parent job (names shortened):
resources:
jobs:
EXM_DV_Job:
name: EXM_DV_Job
tasks:
- task_key: EXM_DV_Job_1
run_job_task:
job_id: ${resources.jobs.EXM_DV_Job_1.id}
- task_key: EXM_DV_Job_2
depends_on:
- task_key: EXM_DV_Job_1
run_job_task:
job_id: ${resources.jobs.EXM_DV_Job_2.id}
Layer two can't start until layer one finishes, and the whole graph resolves inside the bundle with no hardcoded job ids. The full walk through that file, clusters and thread lanes included, is in your Databricks job graph is YAML you own.
On Data Factory, the same batch becomes a batch pipeline that opens with a BimlCatalog lookup and then steps through the layers:
EXM_Batch
LogExecutionStart Lookup on [adf].[LogExecutionStart]
Batch_SolveOrder_1 If Condition -> Execute Pipeline EXM_Batch_1_0
Batch_SolveOrder_2 If Condition -> Execute Pipeline EXM_Batch_2_0
EXM_Batch_1_0 one Execute Pipeline per load, chained by thread
EXM_Batch_2_0
Data Factory has hard limits, and the generator plans for them: a layer that would blow the activity budget gets split into chained sub-pipelines, and a batch with more than 14 solve orders fails the build with a message telling you to cut them down. You find out at build time, not at deployment. Deploying the result is covered in building and deploying Azure Data Factory pipelines.
Switching templates isn't free, and it would be dishonest to say otherwise. A Databricks project needs a compute connection to a cluster, and your connections have to be set up for the new engine. What you don't redo is the dependency graph or the load patterns. Change the template, fix the connections, rebuild.
Every Step Reports In
The third artifact is the one people skip when they evaluate tools, and it's the one that decides whether operations can support the thing.
Every generated step calls the BimlCatalog, the operational database you deploy and own. In SSIS, packages execute [ssis].[LogExecutionStart], [ssis].[LogExecutionEnd] and [ssis].[LogExecutionError]. In Data Factory, pipelines open with a lookup on [adf].[LogExecutionStart] and close with a stored procedure activity on [adf].[LogExecutionEnd]. On Databricks, each generated task notebook wraps its work like this:
try:
dbutils.notebook.run(
"./HUB_Customer_00_Main",
0,
{...},
)
bfx_log_db_execution_end(connection, log_execution.ExecutionID, None, None, False, f"Execution End: {log_execution.PackageName}")
except Exception as e:
bfx_log_db_execution_error(connection, log_execution.ExecutionID, False, False, str(e), f"Execution Error: {log_execution.PackageName}")
raise Exception(e)
Three engines, one log. The tables sit in your own database, so last night's failures are a query away no matter which engine ran them (SQL Server syntax shown):
SELECT p.ProjectName,
p.PackageName,
e.StartTime,
e.Duration,
err.ErrorDescription
FROM bfx.Execution e
JOIN bfx.Package p ON p.PackageID = e.PackageID
LEFT JOIN bfx.ExecutionError err ON err.ExecutionID = e.ExecutionID
WHERE e.ExecutionStatus = 'F'
AND e.StartTime >= DATEADD(DAY, -1, SYSUTCDATETIME())
ORDER BY e.StartTime DESC;
Those are the prebuilt job steps: logging, ordering and error capture that you didn't write and don't maintain by hand.
How to Evaluate a Tool
Feature grids for this category all look the same. Three questions cut through them faster.
What does it emit? Ask to see the generated output for one batch: the load code, the job graph, and the logging calls. If the demo shows generated SQL and then a person building the pipeline that calls it, orchestration isn't automated. If there's no run log you can query, operations will build one later, by hand.
Who owns it? Find out where the generated code lives and where run state is stored. If orchestration and run history live inside the vendor's runtime, the SQL may be recoverable when the contract ends, but the job graph and its history probably aren't. We made the longer case in building an asset or renting a liability.
What happens when you switch targets? Ask them to take one project from its current engine to another, live, and show what had to be rewritten. The honest answer is never "nothing": connections change, and some platform settings only exist on one engine. The answer you want is that the dependency graph, the load patterns and the logging came across without anyone rebuilding them.
A tool that answers all three with artifacts is automating the warehouse. One that answers with screenshots of a canvas is automating the drawing.
