Today’s data warehouse automation handles the simpler parts of building a warehouse and stops at the hard part. That gap can be closed, and closing it raises a second question: what role AI should play.
Mechanization, automation, and AI
Today’s automation is mechanization: it handles the routine and leaves the expertise-heavy work to people. In contrast, a conceptual layer changes what can be automated. Describe the warehouse in business terms, derive the technical build from it, and the degree of automation rises far enough to deserve the name.
AI does not close the gap on its own. Added to today’s approach, it saves time but does not turn mechanization into automation. It earns its place when you build the conceptual layer instead, where it makes the specification faster to write without changing what gets derived.
We look at four scenarios:
- Today’s mechanization: what data warehouse automation does now, and where it stops.
- The conceptual layer: describe the business, derive the warehouse, reach real automation.
- AI on today’s manual steps: the manual work runs faster, and it stays mechanization.
- The conceptual layer, AI assisted: it is faster to write with AI, and the warehouse is derived deterministically.
Scenario 1: today’s mechanization
Start with what exists. Data warehouse automation today is built mostly around Data Vault. Within that frame, the automation does part of the job. Give it a logical model and it derives the physical model and its DDL, and generates the boilerplate ETL that moves, hashes, and loads the raw data into the Raw Vault. That takes a lot of hand-coding off the desk.
Two tasks stay manual, and they are the ones that still demand Data Vault expertise. The first is the Data Vault logical model. A tool can propose one from the source structures, but a proposal that mirrors the sources one to one does little to integrate them. The model still has to be reviewed and adjusted, and that is where the Data Vault know-how is needed.
The second is the business logic that feeds the Business Vault, written against the Data Vault, which means knowing how the hubs, links, and satellites join and how to line their histories up before you can express a single rule.
So the build runs in steps. You model part of it by hand, the tool generates from that, you write the next part by hand, the tool processes it. Automated work, stitched together by hand. That is mechanization rather than automation. We covered the distinction in full in Automation or mechanization?.
Scenario 2: move the work up
Real automation does not come from making the technical work faster. It comes from moving the work somewhere else. Put a conceptual layer above the integration layer and let the human work there, in the terms of the business rather than the terms of Data Vault.
You specify the business concepts, their keys and relationships, where their data comes from, and the logic that defines each metric. From that specification the entire integration layer is derived: the Data Vault logical model, the physical model, and all the load and transformation pipelines, including the two steps that were manual in scenario 1.
For this to be automation, the derivation has to be deterministic, and that means no AI is involved. Given the same conceptual layer, it produces the same warehouse, every time, and no one reviews a proposed model or corrects generated Vault SQL. This is the key move, and it rests on one thing: what the conceptual layer has to contain.
What goes into the conceptual layer
Deterministic derivation is only possible if the conceptual layer carries enough information to build from. So what is in it?
It is a directory of text files, not prose and not a diagram. The structural definition could live in YAML: each business concept with its attributes, its business key, and how it relates to the other concepts, along with the source mappings that say where its data comes from. Open the tree in an editor and you are looking at the whole warehouse, versioned and reviewed like any other code. We go into that side of it in Your data warehouse as code.
The conceptual layer does not stop at that basic definition. When they matter, other kinds of information go in as well. Timelines are one: the dates a source carries that say when a piece of information was present in it, or from when it is valid in business terms. Classifications of business concepts are another: which classifications a business concept has, and how each one is determined.
The derivation works by recognizing patterns. A Data Vault is not an arbitrary structure. It is a set of recurring shapes: a business concept with a stable key becomes a hub, a relationship becomes a link, an attribute that changes over time becomes a satellite split by rate of change and source. Not every pattern maps to a single table: a classification can turn into several Data Vault tables at once. Recognized across the whole conceptual layer, these patterns are what the Data Vault logical model is derived from.
That is the whole condition. The conceptual layer has to carry, for every concept and relationship, the information a pattern needs to be identified. With that in place, the logical model, the physical model, and the pipelines follow with no guesswork and no proposal to review.
The conceptual layer also holds the business logic. Defining it should take none of the Data Vault complexity. At the same time, it should stay fully flexible. The answer is SQL for the flexibility, written against the business concepts instead of the Data Vault tables.
Because the logical and physical Data Vault are both derived from the conceptual layer, SQL that reads business concepts can be translated automatically into SQL that reads and joins the Data Vault tables. A business concept spans several of those tables, so the translation handles the joins across them, and your SQL stays much simpler. A SELECT over your business concepts is all you write, and from it the full ETL for that transformation is generated.
As a result, the technical build of the warehouse is derived in full and abstracted away, and the focus shifts to the business requirements.
What about using AI?
Both approaches so far leave AI out. Adding it is the obvious next move, so take each in turn and see what AI changes. The two places to try it are the manual steps in today’s setup and the writing of the conceptual layer in the new approach.
Scenario 3: AI on today’s manual steps
First bring in AI, but keep today’s setup. This is the tempting shortcut. You have two manual steps left in scenario 1, so you point AI at them. Let it propose the Data Vault logical model and draft the Business Vault preparation SQL.
It can save time, because a proposed model is faster to start from than a blank one, and drafted SQL is faster than typing it out. But it does not move the problem. A proposal still has to be reviewed and corrected, and reviewing a Data Vault model or generated Vault SQL takes the same Data Vault knowledge the proposal was supposed to spare.
The work stays in the integration layer, it stays technical, and the expertise it demands stays exactly where it was. The Data Vault complexity has not gone anywhere.
AI applied to the technical layer produces technical artifacts, and technical artifacts have to be judged on technical grounds. AI changed the manual work from writing to reviewing, but it did not remove it. The bottleneck was never the writing. It was the expertise, and the review needs it too. So the build never runs on its own, and it stays mechanization.
Scenario 4: the conceptual layer, AI assisted
Now take the conceptual layer from scenario 2, with its deterministic derivation, and add AI where it actually fits: writing the conceptual layer. AI can assist with defining the business concepts, suggesting relationships, and writing the SQL that defines a metric on the business concepts. It can work from whatever you provide, whether use cases, prompts, or source metadata.
The difference from scenario 3 is what gets reviewed. In scenario 3, AI hands you a Data Vault model, and you check it as a Data Vault model. Here, AI hands you a business concept or a metric definition, and you check it: are these the right attributes, does this attribute come from the right source, is revenue counted this way. That review takes knowledge of the business, not Data Vault expertise.
The technical build underneath stays deterministic, so AI can never introduce a technical error into the warehouse. What it shapes is the business specification, and that is what the review checks.
AI’s contribution here is speed, on the one part still left to the human: building the conceptual layer.
The accelerator, not the key
Step back and the four cases come down to one thing. Adding AI to today’s setup changes nothing structural: the work stays in the integration layer, stays technical, stays mechanization. Moving the work up to the conceptual layer closes the gap, if the whole integration layer is derived from it deterministically. What closes it is not whether AI is involved. It is where the human works.
conflux9 is the project we are building for exactly this. You define your business concepts, their relationships, and their logic in SQL against those concepts, and we are building AI support to help you write that conceptual layer faster. Whether you use AI or not, conflux9 derives the Data Vault logical model, the physical model, and the pipelines from it, deterministically. The Data Vault stays an implementation detail no one has to open.
We keep AI out of the build itself. The derivation stays deterministic, so nothing technical depends on AI. We use it instead to help write the conceptual layer faster. That makes AI an accelerator, not the key.