Automation or mechanization? What real data warehouse automation would take

Automation makes a promise: the work disappears and the scarce expertise stops being needed. Today’s data warehouse automation raises that expectation and does not meet it.

Why does it fall short? And what would have to change for it to deliver what its name claims?

What automation actually means

Start with the word. Automation is not the same as mechanization, and the gap between the two is the whole argument.

Mechanization gives a person a better machine. The work still runs on a human: someone guides it, feeds it, decides the next move. A power drill is mechanization. It drives screws faster than a screwdriver, but a person still holds it and still has to know where every screw goes.

Automation takes the human out of the step. The task runs on its own, no one stands over it, and the skill it used to demand stops being scarce. A thermostat is automation. It senses, decides, and acts, and you stop thinking about it.

Point that at data warehousing. It is called data warehouse automation, so the expectation is set by the name: the technical work of building the warehouse should run on its own, and the deep technical skill it takes today should stop being a prerequisite.

One thing is deliberately not on the table. Defining what the business needs is not the part to automate. Someone has to say what a customer is, how revenue is counted, which numbers matter. That is input, and it is human by design. The promise of automation is about what the machine does once that input exists: the technical construction, not the business definition.

So the fair test from here is narrow. Not whether a human still thinks, because a human always will. The test is whether a specialist still has to build the technical layer by hand, in whole or in part, and whether it still demands the expertise that automation was supposed to remove.

What today’s automation actually does

Today’s data warehouse automation is good at part of the job. Most of it is built around Data Vault, and for good reason: it gives clear, repeatable patterns to build on. Within that frame, the automation does genuine work. It generates tables. It generates the boilerplate ETL that moves, hashes, and loads. It takes a lot of hand-coding off the desk.

But two tasks stay manual, and they are the ones that matter.

The first is the Data Vault model itself: which hubs and links, how to split the satellites, when an effectivity satellite is needed. Some tools do propose a Raw Vault model from the source structures, which helps. But a proposal still has to be reviewed and corrected by a person, and that review takes the same Data Vault knowledge the proposal was meant to spare. The second is the business logic that turns raw data into the numbers people report on. The logic is the same in any model. What makes it hard here is writing it against the Data Vault: you have to know how the hubs, links, and satellites join, and how to line their histories up, before you can express a single rule.

Both of these are work inside the build, not input before it. So the build goes 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 the signature of mechanization: a machine that handles the routine while a person does what takes skill. And it is the same skill each time: a working knowledge of Data Vault.

It is tempting to read this as less technical than it sounds, because a well-built Data Vault is business-centric. The business key sits at the center, and every table hangs off it. That is true, and it is what makes the model sound. But business-centered is not business-level. The business says “customer.” Data Vault answers with a hub, a set of satellites split by rate of change and source, hashed keys, and links to everything the customer touches. That is a technical representation of the business, and reading or changing it still takes Data Vault expertise. The model is organized around the business, yet the work in front of you is technical.

You define the business. The technical layer still has to be built, and here is where that work lands today:

So the work that should be about the business turns into work about the model. Every requirement is translated into hubs and links before anything runs, and the question that started it all waits at the edge of the room.

Speak business, not Data Vault

The obvious fix is to mechanize harder: generate more of the Data Vault model, propose more of the ETL, shave more off the manual steps. That helps, but it leaves the specialist exactly where they were, in the loop and doing the technical work.

The real fix changes what you describe. Today the warehouse is described in technical terms: hubs, links, and satellites. Describe it in the terms of the business instead: what a customer is, how it relates to an order, how revenue is defined, and where the data for each of these comes from. The description says what the data means rather than how it is stored, and from it the Data Vault is derived.

This is not natural language, and it is not no-code. You still write a precise, explicit definition. It is a conceptual model: the business objects and their relationships in a structured, text-based form, with the logic in SQL against those objects. The description is every bit as exact as it was before. What changed is its subject: the business.

A semantic layer solves a different problem. It gives the business a way to query the warehouse in business terms. The conceptual model is not a view laid over a finished warehouse. It is where the warehouse comes from.

What this approach automates

Once the conceptual model is the starting point, the two steps that stay manual today can both be derived from it.

The first is the Data Vault model. It is derived from the business definition in full. No one hand-models hubs, links, and satellites, and no one reviews a proposed model the way today’s tools require.

That puts the weight on the approach itself. To derive the model, it has to recognize the patterns in the conceptual model and its mapped sources. Such patterns can be timelines, classifications, hierarchies, or states. And the conceptual model has to let you express the information each pattern needs. Meet those conditions, and the Data Vault follows from the definition.

The second is the business logic. You still write it, but against your business objects, with the joins a business person would expect, and without knowing the Data Vault underneath. It is then translated into the real Data Vault SQL: the right tables, the right joins, the history lined up. The rule you wrote and the rule that runs are the same rule, one expressed in business terms and one in Data Vault terms. You only write the first.

Here is the same build, side by side:

The two rows that were manual are now automated. What stays with you sits above the table entirely: defining the business, the part that was never technical to begin with.

Be honest about what this removes and what it does not. It does not remove the thinking: writing the business logic is real work, and getting the business model right still takes judgment. When a rule is genuinely unusual, you still have SQL to express it, so the hard cases are not locked out. What it removes is the need to know Data Vault.

What about the data marts?

So far this has been about the Data Vault. But most people do not report on the Data Vault. They report on data marts, the star schemas the business actually queries. Does the argument hold there?

It does, with one honest difference. The vault could be derived from the business definition you already had. A mart needs a little more from you, not more technical work, but a few decisions about how the business wants to read the data.

Today, building a mart means assembling data out of the Data Vault by hand. You navigate the hubs, links, and satellites, and you build the helper tables that make that bearable: point-in-time tables, bridge tables. It takes the same Data Vault knowledge, and it is manual. Then, as before, the automation only covers the last stretch, turning a finished logical model into physical tables.

The same conceptual-model approach reaches the marts. On top of the conceptual model, you give a handful of decisions: whether a report needs the current state, the historical state, or both, at what grain, who may see what, star or snowflake, and which dimensions are shared across marts. From the conceptual model and those decisions, the technical build is derived: the dimension and fact tables, the slowly changing dimension logic, and the loads. The assembly out of the Data Vault is derived with them, so no one hand-builds a point-in-time table, and no one needs to know the Data Vault to get a mart.

These decisions reflect business requirements. A type-two slowly changing dimension is a good example. You do not build one here. It is what you get when you state that a dimension has to be analyzed with its full history. You give the requirement, and the technical form follows.

So the line holds in the presentation layer too. You give the requirements and the few design choices, and the build follows from them.

Stop mechanizing, start real automation

Why does today’s data warehouse automation fall short? Because it automates the wrong layer. It speeds up the technical build but leaves the two steps that need real expertise, the Data Vault model and the business logic, in human hands. The work stays technical, and the business waits.

What would have to change? The description. Stop building the warehouse in Data Vault terms and define it in business terms, then derive the Data Vault from that. When the definition is the business model, the technical build is a consequence, not a project.

We are building conflux9 to do exactly that. You define your business objects, their relationships, and their logic in SQL against those objects. From that, conflux9 derives the Data Vault, the physical model, the transformations, and the orchestration, and keeps the Data Vault as an implementation detail you never have to open.

This does not remove the data engineer. It removes the work that was never the point: hand-building hubs, links, and satellites, and translating every requirement into technical structure. What is left is the part that always mattered, understanding the business and stating it clearly. The Data Vault expertise stops being a prerequisite, and the focus moves to where it counts: the business.

That is the difference between mechanization and automation. One makes the manual work faster. The other makes it disappear and hands you back the business.