By Jason Lepp and Harsh Joshi. About 6 minutes.
Most data teams build the same things over and over. A date dimension here. A customer dimension there. A fact table for sales, another for usage, each one hand-written, each one slightly different from the last. It works, until you have forty of them and no two were built the same way. Then every change turns into a small archaeology project, and the person who can read the code has usually left.
We didn’t want to work like that. So we built our dimensional modeling as software.
Treating the model as a product
When we join an engagement, the data model is where most of the real work lives. Dimensions, facts, surrogate keys, slowly changing history: the plumbing that turns raw source data into something a business can actually trust. Most firms treat each of those as a one-off deliverable. Write it, ship it, move on. We treat it as a product we maintain once and reuse everywhere.
In practice that means a Python package that installs into any Databricks environment. It has tests, type checking, and a build pipeline, the same standards you would expect from any real software, because that is what it is.
What’s in it
The core is a small, deliberate set of utilities:
- Dimension handling that manages slowly changing dimensions and assigns surrogate keys, so every dimension in every model behaves the same way. In practice that means Type 1 dimensions, where the current value overwrites the old. That is a deliberate simplicity call: it is what the work has actually needed, and we don’t carry history-tracking machinery a model isn’t using.
- Fact handling that creates and upserts fact tables against those dimensions.
- Surrogate key generation and lookup, written once, tested once, reused everywhere.
- A chain-of-responsibility pattern for dimension processing, so new behaviour is added by dropping in a handler rather than rewriting what already works.
- Automatic run tracking. Every process records its start, end, and status to a history table in Unity Catalog, so when something breaks overnight you can see exactly what ran and what didn’t, without spelunking through logs.
The chain-of-responsibility pattern is worth pausing on, because it is what keeps that dimension code safe to change. Processing runs through a chain of handlers in a set order: a Type 1 handler, then any others, then a default. A request to build a dimension enters the chain, and each handler either recognizes it and does the work or passes it down the line. The shared logic sits in abstract classes underneath. The benefit lands the day the rules change. Today the models need Type 1. If a future one needs history tracking, that is a new handler dropped into the chain, not a rewrite of code that already works, and nothing downstream needs re-testing, because nothing downstream changed.
On top of the framework sit conformed dimensions: shared reference tables, a date dimension, a well dimension, a corporate hierarchy, built once and used by every downstream model. Everyone reports off the same definitions, so “revenue” or “active well” means one thing across the business, not five. Underneath all of it is master data: the authoritative, reconciled reference data the dimensions are built from.
Native in Databricks, integrated with Microsoft Fabric
Building the model is only half the job. The other half is getting it into the tools the business actually opens. We do the heavy engineering natively in Databricks, then publish the finished model into Microsoft Fabric automatically, as part of the same pipeline that built it. No separate hand-off, no manual refresh. The model the business sees is always the model we built.
Two things about this matter to a client.
First, optionality. The data is engineered once, in Databricks, and lands in Fabric, the tool most of our clients already license and already know how to use. Their analysts build in Power BI on a trusted, automatically refreshed model, or they query the same data directly in Databricks. We are not selling anyone a new stack. We are making the one they already pay for work harder, and opening doors to analytics they could not easily reach before.
Second, and this is the part that actually protects the business: the meaning of the data stays consistent no matter which tool you open. The definitions, the calculations, the business logic live in one governed model, not re-created differently in every report. A number means the same thing whether it is read in Power BI, queried in Databricks, or pulled into Excel. Same context, same value, same accuracy, everywhere. That is what makes the optionality safe. Give people more tools without it and you have just handed them more ways to disagree about the numbers. Give them one governed model underneath, and they can pick whatever tool suits them and still land on the same version of the truth.
Why it compounds
The payoff is in the conventions. Over many engagements we have settled on a consistent way to build these models, and we have written scripts around those conventions. Point them at the source tables and they wire up a full semantic model, even one with dozens of facts and dimensions and hundreds of columns, in a few hours. That includes putting measures on the columns of the fact that need them, common ones like time intelligence included, so the model arrives usable rather than half-built.
Because the conventions do the heavy lifting, the first pass is done in hours, not weeks. That matters less for the speed itself than for what it changes. The model gets into the hands of the business almost immediately, so they can look at real numbers and tell us what is wrong, what is missing, what to rename. The iteration starts on day one instead of after a month of setup.
That is what compounds. Each engagement sharpens the conventions, and sharper conventions make the next model faster to stand up. It is the same idea behind the five dominos: each build makes the next one easier. This is what it looks like in code.
The discipline behind it
None of this works without engineering discipline, and that is the part that does not show up in a demo. The framework is unit tested. It is type checked. Changes go through pull requests with real reviews. The way we build, branch, and ship is documented, so a new engineer, ours or the client’s, can pick it up and follow it. Invisible on day one. Decisive over a two-year engagement.
One moment makes it concrete. Near the end of a project, the business changed its mind and renamed almost every column in almost every fact table. Done by hand, that is days or weeks of careful, error-prone work: rename the columns, rename every measure that referenced them, fix every relationship, repoint every report. With the framework, once the underlying tables were changed, rebuilding the entire semantic model, the column renames, the measure renames, the relationship updates, took under an hour. Agreeing the new names with the business took longer than making the change on the backend. Repointing the reports took time too. But the part that used to carry the risk, rebuilding the model itself, was no longer the hard part.
When we leave, the client does not inherit a folder of bespoke notebooks that only made sense to whoever wrote them. They inherit a documented, tested framework their own engineers can read, extend, and trust. New source system next year? Same patterns. New fact table? Same utilities. The model keeps paying off long after we are gone.
A platform is the fourth domino. Data and analytics is the fifth. This is how we build the fifth one so it actually compounds: not as a pile of deliverables, but as a product built to outlast us.
If you are building or buying data modeling and want to talk through how to make it compound rather than accumulate, the thirty-minute conversation is open. No proposal attached.
About this piece
This is the fourth essay in a monthly Insights series on data, capability building, and what real engagements teach us. The first introduced the five dominos framework. The second covered what buyers actually want from consultants. The third looked at why so many pilots stall before they reach production. This one comes from the engineering team, on how we build the models that make the fifth domino compound.
Jason Lepp and Harsh Joshi are Senior Data Architects at Domino Collective, a Calgary-based data and technology partner.
