Defining your models as text has become the norm. Put your models in files under version control, and you can diff every change, review it in a pull request, test it in a pipeline, and roll back when a release goes wrong. That is the workflow modern analytics runs on.
We decided to keep it. The conceptual model that defines your warehouse is text too, so you get the same version control, the same diffs, the same reviews. What changes is the level you work at. You describe the warehouse in business objects, not in Data Vault tables. We work at that level because it makes the warehouse much simpler to build without giving up the version control, the review, or the flexibility.
The conceptual model is text
What is in that model? A directory of files. Each business object is a small file that names its attributes, its business key, and how it relates to other objects. The logic that derives a value or defines a metric is SQL, in its own file. The sources and how they map to the objects sit alongside. Open the tree in an editor and you are looking at the entire definition of the warehouse.
From there the workflow is the one you already have. A new metric is a few added lines in a diff. A change to how revenue is defined is reviewed like any other pull request, with the reasoning in the commit next to it. Nothing about the warehouse changes without leaving a trace.
One definition, nothing drifts
The Data Vault model, the physical tables, and the ETL are not maintained by hand alongside the definition. They are derived from it. We chose that because two artifacts kept in step by hand drift apart, and a warehouse whose documentation no longer matches what runs is the normal state of affairs. When the running warehouse is generated from the definition, there is nothing to keep in sync. Change how an object relates to another, or how a metric is calculated, and the implementation is regenerated from the change.
The same reasoning puts the logic in one place. A metric is defined once, in the conceptual model, rather than reimplemented in a few data marts, a BI tool, and a spreadsheet. Everything downstream builds on that one definition, so the calculation is the same everywhere it is used.
This does not cover everything, and it is not meant to. Some figures are worked out at query time in a BI tool, shaped by the context of a single report. A percentage of a filtered total is the clearest case, since it depends on what the report is filtered to. Those belong there.
Write SQL on business objects, not the Data Vault
You still write the logic. The difference is what you write it on. A derived value or a metric is SQL, and that SQL reads from business objects, not from the tables underneath them. The syntax below is an early draft and may change, but the shape is the point.
select p.product_no,
p.price * (1 - cat.vat_rate) as net_price,
p.price * cat.vat_rate as vat_amount
from c9.bo.product as p
join c9.bo.category as cat
using (c9.ref.product_category)c9.bo.product is the product business object, already assembled, not a physical or Data Vault table. The join does not spell out keys or conditions. It names a relationship, product_category, and the condition is supplied for you, aligned in time. You write what you mean.
The same result written by hand for the Data Vault looks more like this:
select hp.product_no,
sp.price * (1 - sc.vat_rate) as net_price,
sp.price * sc.vat_rate as vat_amount
from hub_product as hp
join pit_product as pp
on pp.product_hk = hp.product_hk
join sat_product as sp
on sp.product_hk = pp.product_hk
and sp.load_ts = pp.sat_product_ts
join link_product_category_eff as lpce
on lpce.product_hk = pp.product_hk
and lpce.load_ts <= pp.snapshot_date
and lpce.load_end_ts > pp.snapshot_date
join pit_category as pc
on pc.category_hk = lpce.category_hk
and pc.snapshot_date = pp.snapshot_date
join sat_category as sc
on sc.category_hk = pc.category_hk
and sc.load_ts = pc.sat_category_tsTo write that, you have to know the model: which satellite holds which attribute, and how to line the whole thing up as of one date, through the link’s effectivity and the point-in-time tables. None of that is business logic. It is the cost of working at the storage level instead of the business level.
There is a second saving that is easy to miss. You write SQL only for logic. The standard loading, the raw vault, is derived from the model, so there is no SQL to write for it at all.
Many teams already run on dbt, and we want that to keep working. Your existing pipelines run as they did, and anything else you build on dbt, next to the warehouse or unrelated to it, stays in place. The warehouse fits into that setup rather than replacing it.
The business-object SQL you wrote is translated automatically into dbt models. Because dbt works table by table, one business-object query becomes the several models those tables need, and you write none of them. If dbt is not your choice, we have our own transformation engine that produces the final SQL directly.
Why the abstraction holds
An abstraction is only worth having if it does not leak. The usual failure is that it covers the easy cases and then, at the first real one, drops you back to the layer it was meant to hide. That is why we built our abstraction to hold the hard cases too, and not hand them back to the Data Vault.
For example, take the join. When you write using (c9.ref.product_category), you are not only naming a relationship for readability. The condition is generated for you, and so is the moment it aligns on. Lining two satellites up as of one date is exactly the part that takes Data Vault knowledge by hand, and it is the part the governed join takes over. You can also say which timeline to align on:
join c9.bo.category as cat
using (c9.ref.product_category, c9.tl.inscription)c9.tl.load is when a record was loaded, c9.tl.inscription is when the source said it was valid. You pick one by name.
Another important part is pattern recognition. The structures in source data are not arbitrary. The same shapes recur across systems, and Len Silverston and Paul Agnew catalogued them in “The Data Model Resource Book”: timelines, classifications, hierarchies, states, and more. We design the solution to recognize these patterns in the sources and in the conceptual model, and where a pattern needs more to be pinned down, the conceptual model lets you supply what it requires. For each pattern, the Data Vault modeling that fits it is applied automatically.
What conflux9 is
That is what conflux9 is becoming: one conceptual model, in text and under version control, with the warehouse derived from it. The syntax in the examples above may still change, but the direction is clear: you keep the model, and the warehouse follows.
We are building this in public. Book a call with Reinhard for an early look and to tell us where we have it wrong.