Skip to main content
search
0

Unit of Work (UOW) Links in Data Vault

Unit of Work Links in Data Vault: Purpose, Strategy, and Flow.BI

As FlowBI — the GenAI tool that generates Raw Data Vault models by analyzing source data — becomes more widely used, questions about its specific modeling decisions are coming up more frequently. One of those questions is about a particular Link type that FlowBI produces: the Unit of Work Link. What is it, why does it exist, and when should you use it? This post breaks it down.



Unit of Work Links: What They Are and Why They Matter

In Data Vault modeling, a Link captures a relationship between two or more Hubs — a business event or association expressed through a combination of Business Keys. When modeling a source table that contains multiple Hub references, a natural modeling instinct is to split those relationships into separate, more focused Links. One Link for a customer-product relationship, another for an employee-customer relationship, and so on.

Splitting Links is perfectly valid in Data Vault — but it comes with a constraint. Certain Business Keys must remain together to preserve what is called the Unit of Work: the set of Business Keys that belong together as a single, consistent business event. Separate them incorrectly, and you risk breaking data consistency in your model. This is one of the more subtle but consequential mistakes a modeler can make, and it’s discussed in detail in the Data Vault training and certification curriculum.

The Unit of Work Link is the answer to that risk. It is a Link that spans all Hub references coming from a single source table — unsplit, unfiltered, capturing the full set of Business Keys exactly as they appear together in the source. Think of it as the safe baseline: a Link that guarantees the Unit of Work is preserved, no matter what splitting decisions get made on top of it.

The Human Modeler Problem — and How FlowBI Handles It

FlowBI works by analyzing source data profiles and generating the Hubs, Links, and Satellites needed to capture that data correctly in a Raw Data Vault — one that passes the JEDI test. It integrates with automation tools including Data Vault Builder, Datavault4dbt, and others.

But here’s the design challenge FlowBI had to address: it imitates a human data modeler. And human data modelers make mistakes. One of the most common is splitting Links incorrectly and inadvertently violating the Unit of Work. So the question during development was: do you try to eliminate these mistakes entirely — at the cost of no longer truly imitating human modeling behavior — or do you accept that errors can happen and build a counter-strategy?

FlowBI chose the counter-strategy. The Unit of Work Link is that strategy. By always generating a Link that preserves the full set of Hub references from a source table, FlowBI ensures there is always a valid, consistent fallback in the Raw Data Vault — even if additional, more specific Links turn out to have been split incorrectly.

The Practical Modeling Strategy Behind It

This isn’t just an AI design decision — it reflects a sound modeling practice for human modelers as well. The approach works like this:

When working with a source table that contains multiple Hub references, always create one Link that spans all of them. This is your Unit of Work Link. Then, if your understanding of the source data is strong enough — if you’ve been able to validate the relationships and run the appropriate tests — you can introduce additional, more specific Links that extract individual relationships from the source. A hierarchy Link, an employee-to-customer Link, a product-to-order Link, and so on.

The key point is that the Unit of Work Link remains. It doesn’t get replaced. If one of the more specific Links turns out to violate the Unit of Work — because the source data behaved differently than expected, or because access to production data was limited during modeling — you still have the original Link to fall back on. The model remains valid. You can retrieve the correct relationship from the Unit of Work Link and fix the problem without reloading the Raw Data Vault.

The less familiar you are with a source dataset — particularly in cases where you can’t access production data directly or can’t run comprehensive validation tests — the more valuable this approach becomes. When in doubt, don’t split. Protect the Unit of Work first.

Splitting in the Business Vault Instead

If a Unit of Work Link exists in the Raw Data Vault and you later want to split it into more focused relationships, that work belongs in the Business Vault — not the Raw Vault. You use the Unit of Work Link as the basis for either virtual or materialized Links in the Business Vault, where the split is applied.

The advantage of this approach is containment. If the split is wrong — if it turns out to violate the Unit of Work — the fix happens in the Business Vault. The Raw Data Vault doesn’t need to be reloaded. The source of truth stays intact. You correct the business logic without touching the foundation.

This is exactly the kind of separation of concerns that makes Data Vault resilient. Raw data is captured as-is, close to the source. Business logic — including relationship refinement — happens in the layer designed for it.

A Rule Worth Adopting for Any Modeler

The Unit of Work Link isn’t just a FlowBI artifact. It’s a principle any Data Vault modeler can and should apply. If you’re splitting Links in your Raw Data Vault, ask yourself: where is your Unit of Work? Is there a Link in your model that preserves the full set of Business Keys from each source table, regardless of how you’ve split them elsewhere?

If the answer is no, you’re relying on every split being correct — and on your understanding of the source data being complete. That’s a reasonable bet when you know the data well. It’s a riskier one when you don’t. The Unit of Work Link costs very little to include and provides a meaningful safety net in return.

To go deeper on Link modeling, Unit of Work concepts, and the full Data Vault methodology, explore our Data Vault certification program. And if you’re new to Data Vault, the free handbook — available as a hard copy or ebook — is a solid introduction to the core concepts.

Watch the Video

The AI-Enabling Data Platform: Unlocking Scalable, High-Quality AI Applications

AI Enabling Data Platform

Is your company building an AI time bomb?

Many businesses are rushing to deploy AI prototypes that look impressive during a demo but hide massive, systemic risks. From “hallucinating” bots that give dangerous advice to customers to catastrophic legal liabilities, simple AI setups can quickly become a corporate nightmare.

If your AI strategy depends on unorganized data and ungoverned workflows, you aren’t just experimenting, you are creating a “data debt” that could bankrupt your project or compromise your company’s reputation. If you want to move beyond these risky experiments and build AI that is efficient, scalable, trusted, and actually works for your business, you need a different approach. Learn how an AI-Enabling Data Platform protects your company while unlocking the true power of high-quality, scalable AI.

The AI-enabling Data Platform – Unlocking high-quality AI Applications

To scale AI effectively, organizations must move beyond unmanaged prototypes toward an AI-Enabling Data Platform that addresses security risks and poor data governance. By transforming fragmented data into governed Feature Marts, this architecture ensures the high-quality, compliant data foundation necessary for reliable AI workflows. This shift ultimately solves the maintenance and liability issues that typically hinder AI return on investment. Learn more in our upcoming webinar on February 17th, 2026!

Watch Webinar Recording

Moving Beyond the Prototype

It usually starts with a spark of excitement. You build a small AI tool or workflow using a Large Language Model (LLM), and it works! It answers questions, summarizes text, and saves your team hours of manual labor. This is the “honeymoon phase,” where everything feels possible and the technology seems like magic.

But then, you try to scale. You move from a single user to a whole department, or from a small test folder to your entire company database. Suddenly, things get quite complex. The AI starts making mistakes it didn’t make before so you extend your AI workflows with data adjustments and exceptions, and the system starts breaking regularly. The legal team finds out about the project and starts asking difficult questions regarding data privacy and “black box” decision-making.

Does this sound familiar? You may have seen this in your own projects: A demo that looks great in a controlled environment but cannot handle the pressure of real, messy business use, and gets stuck in PoC purgatory. Without a professional foundation, your AI applications quickly change from being a business asset to becoming a massive liability.

Why Your Current AI Setup is Failing

To understand the solution, we must first look at why most AI initiatives fail when they leave the lab. The problem is almost always the same: a total lack of governance and messy (non-cleansed, non-standardized, or non-integrated) data.

While major LLM models are “trained” generally, they often lack access to the specific “facts” of your business in a way they can understand. This leads to several major threats:

  • The “Hallucination” Risk: If the AI isn’t connected to a “Single Source of Facts,” it guesses. It makes up facts about your product features, delivery times, or prices. In a business setting, a wrong answer isn’t just a mistake but a breach of trust that can quickly destroy a customer relationship.
  • The Maintenance Nightmare: Without a central data platform, every time your source data structure or business logic changes, you have to manually update every single AI tool and workflow you’ve built that touches this piece of data. This makes long-term maintenance impossible and kills the hoped-for ROI of your new AI application.
  • The Legal Challenge: Legal frameworks don’t magically disappear when working with AI. Furthermore, additional frameworks like the EU AI Act are adding new layers of regulatory compliance requirements. If you cannot explain why your AI gave a specific answer or which data it used, you could face massive fines. Using sensitive data without a clear audit trail is a gamble most companies cannot afford.

The Two Traps of Modern AI Development

After the honeymoon phase of the LLM era, companies want to adapt quickly. However, they almost always fall into one of two typical traps. You might recognize these patterns in your own organization:

Trap 1: The “AI Spaghetti” Trap

In the rush to be “AI-First,” many teams use a mix of different AI workflow tools and agents, connecting them piece-by-piece to solve individual problems. While each piece works, the overall system becomes a tangled mess, which I like to call AI Spaghetti. 

In this trap, there is no central “brain” or data control. Each agent has its own way of looking at data, leading to zero consistency. If you change a price in your main database, some agents might see it, while others are still using an old PDF they found in a different folder.

This “spaghetti” is impossible to maintain, secure, and scale. You spend 90% of your time fixing broken connections, integrations or calculations instead of creating new value. 

The dangerous part is that this doesn’t happen on day one; it builds itself as you add more functionalities and exceptions. Often, these workflows are already in production as they grow, and the only way out is building everything from scratch the right way while maintaining the spaghetti in parallel making the “escape route” quite expensive.

Trap 2: The “Lone Wolf” Liability Trap

To bypass what they see as “slow corporate IT,” some teams or individuals start building their own AI applications and workflows. This is not inherently concerning for basic operational efficiency, but the trap is found when teams go deeper and start building workflows and applications consuming and transforming bigger junks of company data.

These “Lone Wolves” work around IT and expose the company to major risks to quickly “get the job done,” ignoring necessary governance processes. When a Lone Wolf uploads a customer list or a trade secret to a public model, that data might be used to train future versions of the model, making your secrets public property. Furthermore, with zero oversight, legal frameworks like GDPR, internal data sharing protocols, and IT security are often ignored.

The Solution: The AI-Enabling Data Platform

To escape these traps and unlock real sustainable value, you must move away from “messy” setups. The answer is the AI-Enabling Data Platform. This is not just a place to store data. It is a professional system that transforms raw, fragmented information into high-quality “fuel” for AI.

The platform acts as a protective layer between your messy company data (emails, databases, PDFs, spreadsheets) and your AI applications. Its main job is to provide Feature Marts.

What are Feature Marts?

Think of a Feature Mart as a library of trusted information. Instead of asking the AI to search through a giant, messy database, you provide it with specific “Features”, which essentially are data points that have been cleaned, integrated, and approved by your data experts.

For example, instead of the AI trying to guess a customer’s loyalty status from thousands of raw interaction logs, it simply asks the Feature Mart for the “Customer_Loyalty_Score.” The result is instant, accurate, and governed.

How do they fit into our data architecture?

This is aligned with how we provide data to business users for standard reporting and analytics. We don’t throw non-integrated, uncleaned data without descriptions at business users and ask them to find the perfect KPI. This is why the principles behind a quality data platform stay mostly the same. You can simply build Feature Marts on top of your existing data platform. Instead of “Information Marts,” you now add Feature Marts.

AI Enabling Data Platform

You build feature marts on top of your integrated data layer as part of your “Gold Layer” as it is a data asset ready for consumption by your AI applications, workflows and agents. Those are responsible for automating your operations supporting your business in a variety of tasks.

What becomes critical for high-quality results is a semantic layer. Nowadays, definitions for your data, calculations, and meaning can be added in modern data cataloging tools. These are excellent as they can be used by business users as well as data specialists. A well-constructed Feature Mart, combined with descriptive data, is the perfect recipe for high-quality results from your AI layer.

If you are interested in more details about the data architecture, check out my article about Data Fabric architecture here: Data Vault, Data Mesh & Data Fabric Guide

What You Achieve: Quality, Speed, Cost Efficiency and Trust

When you invest in an AI-Enabling Data Platform, you achieve four critical business outcomes:

AI Enabling Data Platform Key Points

The Path to Success

Building high-quality AI is a journey. You can achieve better results and avoid the risks by following these steps:

  • Stop the “Lone Wolves”: Ensure all major AI projects use a central data platform so they stay safe and governed. Which AI usage is allowed outside IT and where guardrails are necessary should be defined in your organization’s AI strategy.
  • Stop the “AI Spaghetti”: Simple AI use cases can be achieved with basic workflow tools (e.g., n8n, Zapier) without a dedicated platform. Complex AI use cases building on company data should not and only use workflows tools for orchestration. 
  • Build Feature Marts: Don’t just give the AI raw data. Turn your important business data into ready-to-use “features” to increase trust, speed, security and governance.
  • Focus on Governance: Use the platform to control who (and which AI) can see your data. Audit inputs and outputs to ensure quality stays high.
  • Create Cross-functional Teams: The real impact is in automating everyday business processes, which is best achieved through combined teams of data engineers, AI engineers, and business users.
  • Assess and Plan: Get an overview of how AI is currently used, where the biggest risks are, and where the biggest opportunities lie. Create a roadmap including team structure, team skills, architecture, processes, governance and security.

If you want to profit from external expertise, read about our Scalefree Review & Assessment service and reach out to us for a customized review fitting your exact needs.

Conclusion: Real Value is Built on Trust

The AI revolution is not about who has the most expensive model or the flashiest chatbot. It is about who can automate their business most efficiently leveraging AI without losing trust in operations, results, and decisions.
When your AI applications are accurate, safe, and governed, they stop being “risky experiments” and become the engine of your company’s success.
Start by identifying your “Lone Wolves” and bringing them into a governed environment. Look at your most valuable AI use cases and start building the Feature Marts they need to survive in the real world.

What do you think?

Have you seen the “Agentic Spaghetti” trap in your own company? Are you worried about “Lone Wolves” creating legal risks? I would love to hear your experiences and challenges in the comments below or on social media postings (probably only LinkedIn)!

Business Analyst and Data Modeler Collaboration in Data Vault

Business Analyst and Data Modeler Collaboration in Data Vault Projects

One of the most common sources of friction in Data Vault projects isn’t technical — it’s organizational. The collaboration between Business Analysts and Data Modelers is arguably the most important working relationship in the entire delivery chain, yet it’s also one of the least clearly defined. Who does what? Where does one role end and the other begin? What information needs to change hands, and in what format? This post walks through a practical approach to structuring that collaboration, drawn from real project experience.



Why the Business Analyst and Data Modeler Collaboration Is So Critical

When Business Analysts and Data Modelers don’t collaborate effectively, the symptoms show up in the Raw Data Vault. Surrogate keys get nominated as Business Keys. Source system logic bleeds into what should be a raw, business-concept-driven model. Gaps in the information provided to modelers lead to design decisions based on assumptions rather than actual business understanding.

It’s worth clarifying one important point here: the Raw Data Vault is not where business perspectives live. Business logic, business rules, and the way the organization interprets its data — all of that belongs in the Business Vault. The Raw Data Vault should reflect the raw data as it comes from the source, structured around business concepts and Business Keys. Keeping that distinction clear is fundamental to a healthy collaboration between the two roles.

Forget the Line — Work Together

A common instinct is to draw a clean boundary: the Business Analyst works until a certain point, then hands off to the Data Modeler. In practice, this handoff model is where projects run into trouble. Information gets lost in translation. The Data Modeler receives documentation that makes sense from a business perspective but leaves key modeling questions unanswered. The Business Analyst doesn’t know what the Data Modeler actually needs.

A better approach: put everyone in the same room. Business Analysts, Data Modelers, Data Engineers, and dashboard designers all working toward the same deliverable — a report, a KPI, a business process automation. The business user doesn’t care about Data Vault; they care about the output. Build toward that output together.

This doesn’t mean everyone needs to be available full-time. But especially at the start of a project, physical or virtual co-location matters. When the Data Modeler hits a question the Business Analyst’s documentation doesn’t answer, the answer needs to be one conversation away — not a ticket in a queue.

Two additional roles are particularly valuable to have accessible during this phase: a source system specialist who knows the source data structure deeply, and a business user who can validate what’s being built against actual reporting needs. They’re typically time-constrained, so plan interactions with them carefully and make the most of the time you have.

Starting with Concept Classification

Before diving into source tables and column mappings, it pays to start at a higher level. A concept classification session — sometimes called a concept analysis — asks a deceptively simple question: what is your business model?

In a meeting with stakeholders from different departments, you map out the core business objects: customers, products, purchases, factories, whatever is central to how the business operates. You’re not focused on relationships at this stage — you’re building a vocabulary. A taxonomy of the concepts that matter to the business.

The second part of this conversation — often in the same meeting or the next one — asks: how do you identify each of these concepts? This is where it gets interesting. If you have people from finance, production, and sales in the room, you’ll typically get different answers. Finance uses an Oracle ID. Sales uses a Salesforce account key. Production uses an SAP number. Different systems, different keys, all referring to the same underlying concept.

This gives you a set of Business Key candidates. From there, you can examine the actual source data: do these keys exist in the dataset? Are they unique? Do any of them appear across multiple source systems in a way that could serve as a shared integration key? That analysis — even if limited to the data you have in front of you — is enough to identify a strong candidate and move forward. It won’t be perfect. A full analysis of every source system across the enterprise is rarely funded. But a well-reasoned candidate key is enough to start building, and it can be refined as the project progresses.

The Collaboration Spreadsheet: Simple and Effective

Once you’ve identified your concepts and Business Key candidates, the next step is mapping source tables to those concepts and classifying every column. The tool for this doesn’t need to be sophisticated — a spreadsheet works well, and works well precisely because everyone can use it.

The process looks like this: before the meeting, a developer imports the source system metadata into the sheet — column names, data types, lengths, source table. One row per column. Then, in the meeting with the business user and source system specialist, you go through each column and answer a simple question: what is this?

The annotations don’t need to be elaborate. Common classifications include:

  • Business Key — the identified key for this concept
  • Descriptive attribute — goes into a Satellite
  • Link reference — indicates a relationship to another Hub, requires a Link
  • Surrogate Key — captured as descriptive, not used as the Business Key
  • Ignore — not needed for this model

Additional classification dimensions — rate of change, security classification, privacy flags — can be added as columns in the same sheet. Satellite split decisions (which attributes group together into which Satellite) can be noted in comments. The goal is to give the developer enough context to build the metadata for the automation tool without needing another round of meetings.

The key discipline here is consistency. Keep comments patternized. The same type of note should look the same every time. A free-form comment field is useful; a completely unstructured one becomes noise.

From Spreadsheet to Automation Tool Metadata

Once the spreadsheet is complete, the developer translates it into the metadata format required by the automation tool — whether that’s Data Vault Builder, VaultSpeed, Datavault4dbt, or another platform. This translation step takes time and precision: automation tools produce exactly what their metadata specifies. Bad metadata produces bad results. But with a well-annotated spreadsheet as the source, the developer has a clear reference and can resolve most questions independently.

Some projects also require terminology translation at this stage. Source systems — especially SAP — often use abbreviated, language-specific field names that don’t belong in a Data Vault intended for a broader audience. The spreadsheet can include an English translation column, which the business user or source system specialist can complete asynchronously, keeping the meeting time focused on classification rather than translation.

Where AI Is Starting to Help

The concept classification and Business Key identification process described above is time-intensive, and it’s largely limited by how much source system analysis you can afford to fund. This is one area where AI tooling is beginning to make a difference.

Tools like FLOW.BI — developed at Scalefree — can attach to source systems, profile the data automatically, classify attributes, and identify Business Key candidates that appear across multiple systems as potential shared integration keys. The manual process described in this post becomes a validation and refinement step rather than a ground-up analysis. The fundamentals are the same; the speed is different.

The Information Requirement: Starting from the End

One final principle worth emphasizing: start with the target. Before analyzing source systems, ask what needs to be produced. What KPI needs to be calculated? What report needs to be built? What data does that require, and where does it come from?

An information requirement document — a structured template that captures what the business user wants, what they need, and where the data lives — is the ideal starting point for any new delivery. It won’t always be complete. Business users often know what they want but not where the data comes from. That’s fine. The Business Analyst and Data Modeler work together to fill in the gaps. But having even a partial information requirement is better than starting from raw source tables and working backwards.

Scalefree has published a template for information requirements on their blog — searching for “information requirement Scalefree” will bring it up — which can serve as a starting point for teams building this practice.

Making Collaboration Work in Practice

There’s no single formula for Business Analyst and Data Modeler collaboration that works across every project and every team. But a few principles hold consistently: work toward the same deliverable together, use simple tools that everyone can engage with, start from the business concept before diving into source data, and keep the meeting time focused on decisions — not documentation.

The spreadsheet approach is unglamorous. It’s also fast, inclusive, and produces the output the developer actually needs. Sometimes the best collaboration tool is the one everybody already knows how to use.

To learn more about Data Vault modeling practices, Business Key identification, and the full Raw and Business Vault methodology, explore our Data Vault 2.1 Training & Certification. And for a concise introduction to the core concepts, the free Data Vault handbook is available as a physical copy or digital download.

Watch the Video

Refactoring a Data Vault Model

Refactoring a Data Vault Model: Options, Risks, and Best Practices

Source systems change. Columns get added or removed, structures evolve, and sometimes entire business key definitions are overhauled. When that happens to a system feeding your Data Vault, the question isn’t just technical — it’s strategic. Do you modify what you have, or do you build alongside it? This post walks through the main scenarios and the practical options available for each, along with a clear recommendation on where to draw the line between low-risk and high-risk approaches.



When a Column Changes: The Simple Case

The least disruptive scenario is a column-level change in a source table — a new attribute appears, or an existing one disappears. For this, you have a few options depending on your project constraints.

Option 1 — Modify the existing Satellite. If your project allows structural changes, you can add the new column to the existing Satellite with an ALTER TABLE statement. Historical rows will show null values for the new column before its introduction, and a log entry can record exactly when the column was added. Removing a column from a Satellite is generally not done — historical data lives in that column, and dropping it means losing that history.

Option 2 — Create a new Satellite. If you’re not allowed to touch existing structures, or simply prefer not to, you create a new Satellite to capture the new or changed attributes. This Satellite gets added to the relevant PIT Tables. The trade-off is an additional join in your queries, but the existing Satellite and its data remain completely untouched.

Option 3 — Close and replace the Satellite. A slightly more thorough approach: close the existing Satellite (stop loading it) and create a brand new one that reflects the updated structure. The new Satellite starts with a full load from the source, which means some data overlap with the old Satellite. This is handled cleanly at query time using an IIF statement — prefer data from the new Satellite where it exists, fall back to the old one for earlier history. The redundancy is not a problem; it resolves itself during query execution.

The bigger the structural change, the more this third option makes sense. If a source table is overhauled dramatically — many columns removed, many added — creating a fresh Satellite to capture the new shape is often the cleanest path forward.

When the Business Key Changes: The Complex Case

Column-level changes are manageable. Business key changes are where things get genuinely complex — and where the risk calculus shifts significantly.

A business key in Data Vault must be unique over time and across the enterprise. If the current key no longer meets that standard — say, a customer number that was once reliable is now duplicated across regions — you have a structural problem that can cascade through the model. Changing the business key means potentially changing the Hub itself, which in turn affects every Link that references that Hub, and every Satellite attached to those Hubs and Links. The impact can be wide.

At this point, you have two main strategic choices.

Option A: Keep Old and New Structures Separate

The lower-risk approach — and the one most commonly recommended — is to leave the historical Raw Data Vault exactly as it is and build a new Raw Data Vault to capture data under the new structure and key definition.

The reasoning is rooted in a core Data Vault principle: the Raw Data Vault should model data close to how the source systems use it. The business had one structure in the past and a different one going forward. That’s two different realities, and it makes sense to model them separately.

The two Raw Data Vaults then get reconciled in the Business Vault, where business logic handles the combination of old and new data. This might be straightforward — a simple union — or it might be complex, especially if field definitions have changed. For example, if an address field was previously structured (street, house number, zip, city) and is now a free-text memo field that may contain addresses from multiple countries, the logic to normalize and combine that data belongs in the Business Vault. That’s exactly what the Business Vault is designed for.

This approach carries the lowest risk. Historical data is never touched. Nothing can go wrong with data that hasn’t been moved.

Option B: Refactor the Raw Data Vault

The more ambitious option is to refactor the existing Raw Data Vault into a new version — modifying Hubs, Links, and Satellites to reflect the new structure — and then reconstruct historical data within that new model.

This is technically possible, but it comes with a hard requirement: you must be able to reconstruct every historical delivery from the new structure without any data loss. In Data Vault practice, this is validated through what’s known as the “Jedi test” — deriving the old structures from the new ones and verifying the output matches the original data exactly. If the test passes, you can safely drop the old tables and replace them with views that expose the old structure as a backward-compatible interface.

Those views give existing queries time to continue working while users migrate. But they’re a transitional tool, not a permanent one. You’ll want to communicate a clear deprecation timeline — 90 or 180 days is typical — and give users explicit guidance on how to update their queries before the views are dropped.

A word of warning: when those views eventually get dropped, expect complaints. Not because the communication failed, but because, as a rule, nobody reads emails. Plan for it.

Handling Non-Unique Business Keys in the Interim

If a business key loses its uniqueness mid-project and a full refactoring effort will take several sprints, there’s a practical interim solution: a Record Source Tracking Satellite. This allows you to continue working with the existing model while the refactoring is planned and executed in the background. It buys time without requiring an immediate structural overhaul, and it keeps the data pipeline running cleanly during the transition.

Communication: The Overlooked Part of Refactoring

Technical decisions aside, refactoring a Data Vault model is also an organizational event. Users who query your data warehouse need to know when structures change — whether that’s a modified Satellite, a new Hub, or a deprecated view that will be removed in three months.

A simple data warehouse changelog or newsletter goes a long way. When you modify existing entities, inform users. When you introduce views as backward-compatible bridges, tell them the timeline. When the views are going away, tell them what to query instead. This isn’t just good practice — it’s the difference between a smooth migration and a flood of support tickets.

The Bottom Line on Refactoring

Data Vault is built to absorb change, and it does so gracefully at the column level. Descriptive attribute changes — new columns, removed columns, restructured Satellites — are handled with well-defined options and minimal risk. The real challenge arrives when business keys change, because the ripple effects can touch Hubs, Links, and Satellites across the model.

In those cases, the recommended approach is to preserve historical data in the original Raw Data Vault and build a new one for the new structure, using the Business Vault as the reconciliation layer. It’s the lowest-risk path, it keeps your historical data intact, and it puts complex transformation logic exactly where it belongs.

To learn more about Data Vault modeling principles, refactoring strategies, and Business Vault patterns in depth, explore our Data Vault 2.1 Training & Certification. And if you’re new to the methodology, the free Data Vault handbook is a great starting point — available as a hard copy or digital download.

Watch the Video

Source of Data for Business Vault Entities

Business Vault Entities: Using Cross-Satellite Joins for Business Logic

One of the questions that comes up regularly among Data Vault practitioners — especially those working on the Business Vault layer — is whether a Computed Satellite can draw its input data from multiple, unrelated Satellites across different Hubs. It sounds like it might break the rules. It doesn’t. In fact, it’s a very common and entirely valid pattern once you understand what actually constrains a Business Vault Satellite.

Let’s break it down.



Business Vault Computed Satellites: What Are the Real Rules?

When practitioners first encounter the Business Vault, a natural assumption is that a Computed Satellite must only use data from Satellites attached to its own parent Hub. The logic seems sound — keep things connected, keep things clean. But this is not actually a constraint in Data Vault. It’s a misconception.

The real rule for a Computed Satellite is much simpler: what do your results describe? Whatever entity your calculated output describes — that’s your parent. That’s what the Satellite attaches to.

For example, if you’re calculating customer lifetime value using data pulled from order records, transaction histories, product tables, and behavioral data — the result still describes the customer. So your Computed Satellite attaches to the Customer Hub. The source of the input data is largely irrelevant to the attachment decision. What matters is the semantic meaning of the output.

Cross-Satellite Joins: Yes, You Can

So back to the original question: can you build a Business Vault Satellite that joins Satellites from two completely different subjects — even if there’s no Link connecting their parent Hubs?

The answer is yes.

Your business rule query can pull data from anywhere in the model. You can join Satellites on the same Hub, traverse a Link into another Hub and pull Satellite data from there, use business key relationships to establish a match, or even join across entirely disconnected model areas if a descriptive relationship exists in the data itself.

The absence of a Link between two Hubs doesn’t prevent you from joining their Satellite data in a query. If there’s a way to match records — even informally through a shared descriptive attribute in a Satellite — you can use that to establish the relationship in your query logic and proceed.

When to Consider a Business Vault Link Instead

That said, there are situations where formalizing the relationship makes sense. If two Hubs have no Link between them but your business logic consistently requires joining their data, it may be worth creating a Business Link or an Exploration Link in the Business Vault to make that relationship explicit and reusable.

This approach can improve query efficiency and make the model easier to understand for future developers. However, it also adds complexity — a Business Link typically requires an Effectivity Satellite to track the validity of the relationship over time, which may or may not be worth the overhead depending on your use case.

The pragmatic answer: if it’s a one-off calculation and the join works, just write the query. If the relationship is foundational to multiple business rules, formalize it with a Link. Performance and maintainability should guide that decision, not a strict modeling rule.

The Only Real Constraints

In practice, there are two genuine constraints on what you can use as input for a Business Vault Computed Satellite:

  • Query performance: Joining across disconnected parts of your model may not always be efficient. If the join is expensive and runs frequently, optimization — through a Business Link, materialization, or other techniques — is worth considering. But it’s an engineering concern, not a modeling violation.
  • Data Governance: Are you actually allowed to combine those datasets? In organizations with strict data ownership boundaries, combining Satellites from different subject areas or different data owners may require governance approval. That’s a policy question, not a technical one — but it’s one worth asking before you build.

Outside of those two factors, the query is yours to write however you need.

A Practical Example: Customer Lifetime Value

To make this concrete: imagine you need to calculate customer lifetime value (CLV) and attach the result as a Computed Satellite on the Customer Hub. Your inputs might include:

  • Customer profile data from a Satellite on the Customer Hub
  • Order totals from a Satellite on an Order Hub (connected via a Customer-Order Link)
  • Return history from a Satellite on a Returns Hub
  • Promotional sensitivity data from a completely separate marketing model with no direct Link to Customer

Even if the marketing model has no formal Link back to Customer, if there’s a way to match the records — say, via a shared customer identifier in a descriptive Satellite — you can join it. Your CLV business rule can consume all of this data in a single query. The result — a calculated CLV score — describes the customer, so the Computed Satellite attaches to the Customer Hub. Clean, valid, and practical.

This kind of cross-domain business logic is exactly what the Business Vault is designed for. It’s the layer where raw, integrated data gets transformed into meaningful, decision-ready information — and the flexibility to query across the model is one of its most powerful features.

Key Takeaway

When building Computed Satellites in the Business Vault, don’t let the structure of your Raw Data Vault artificially limit your business logic. The query that feeds your Satellite can draw from anywhere in the model. The only questions that matter are: what do your results describe, and is your query performant and governance-compliant?

Get those right, and the rest is just SQL.

To go deeper on Business Vault patterns, Computed Satellites, and the full Data Vault methodology, explore our Data Vault 2.1 Training & Certification — and grab the free Data Vault handbook for a concise overview of the core concepts.

Watch the Video

How to Monitor Data Reliability with dbt Source Freshness in dbt Cloud

How to Validate Data Freshness in dbt Cloud

Ensuring that your data is fresh, reliable, and aligned with your SLAs is one of the most important responsibilities of any analytics engineering or BI team. In modern data stacks, dbt Source Freshness plays a key role in validating that upstream systems are loading data as expected. When used properly, it helps teams identify delays, pipeline failures, or missing updates before they impact models and downstream reporting.

This article walks through a full demo of how to configure, run, and monitor Source Freshness checks in dbt Cloud. It builds on the fundamentals introduced in the first video of our series, where we explained what source freshness is, why it matters, and how dbt evaluates freshness. If you haven’t watched that introduction yet, we recommend doing so first.

In this second part, we go hands-on: reviewing source configurations, running freshness checks using both fields and custom SQL queries, applying optional filters, triggering warnings and failures, and inspecting the results in the dbt Cloud UI and Catalog. By the end, you’ll have a clear understanding of how to integrate freshness checks into your workflows and jobs to maintain a highly trustworthy data foundation.



Understanding the Source Freshness Configuration

The demo starts inside a dbt Cloud project with a YAML file containing our source definitions. For this walkthrough, we are working with a source called dbt_talk_demo_sources, which includes two tables:

  • customer_source
  • employee_source

Inside the configuration block, we define the core freshness thresholds:

  • warning_after: 30 minutes
  • error_after: 60 minutes

These settings tell dbt when to flag a source as slightly stale (warning) or critically outdated (error). They are typically aligned with SLAs and expectations for how often upstream data should be updated.

Using loaded_at_field

For the customer_source table, we use a basic configuration: the table includes an updated_at timestamp column, which dbt uses directly to calculate the freshness. However, the timestamps in this demo are recorded in CET (Europe/Berlin), which means dbt converts them to UTC before evaluating freshness. This highlights a common real-world consideration: time zones must always be handled consistently in freshness checks.

Using loaded_at_query

For employee_source, we use a different approach. This table does not store a timestamp column. Instead, the load timestamps are stored in a metadata table. To handle this, we configure a loaded_at_query—a SQL query that retrieves the latest load time externally. This method is often used when:

  • Timestamps come from an ETL metadata or logging table
  • Data loads use high-watermark patterns
  • You want more control over how freshness timestamps are calculated

In the demo, the query simply selects the MAX(updated_at) value from the metadata table. While simple, it demonstrates how flexible dbt is when working with custom data loading patterns.

Using Optional Filters

dbt also supports an optional filter configuration, which lets you skip certain rows when evaluating freshness. For example, if a table contains soft-deleted records or historical rows that should not count toward freshness checks, you can filter them out. In our demo, the filter excludes rows where deleted = TRUE, ensuring only active records contribute to the freshness calculation.

This becomes particularly useful when old records appear fresher than the latest valid ones, which could skew your results or hide actual issues.

Running Freshness Checks in dbt Cloud

With the configuration in place, we run our first freshness check via the CLI:

dbt source freshness

Before running the check, we insert new rows into the source tables so that the latest data delay is around 20 minutes. Since this is below both the warning and error thresholds, the run reports everything as green.

This confirms that both types of configurations—loaded_at_field and loaded_at_query—are working as expected.

Triggering a Warning

Next, we enable the earlier-mentioned filter configuration on customer_source. After filtering out the deleted rows, the next valid record has a delay of about 50 minutes. When we run:

dbt source freshness -s source:dbt_talk_demo_sources.customer_source

dbt reports a warning state, because the threshold of 30 minutes is exceeded. This demonstrates how filtering can impact the evaluation in meaningful ways.

Triggering a Failure

To understand how a failed freshness check behaves, we insert data with delays exceeding the 60-minute error threshold. Running the same command again produces an error state. The dbt output also shows the exact SQL query it executed to determine freshness—useful when troubleshooting unexpected results.

Including Freshness in dbt Cloud Jobs

dbt Cloud provides two ways to incorporate freshness checks into scheduled jobs:

Option 1: “Run Source Freshness” Checkbox

With this option enabled, dbt automatically runs dbt source freshness as the first step of the job. However, failures do not stop the rest of the job from executing. This mode is ideal when you want visibility but don’t want freshness violations to block model builds.

Option 2: Adding Freshness as a Job Step

Alternatively, you can include freshness checks as an explicit job step. In this case, if freshness fails, subsequent steps are skipped and the job fails. This is the preferred option when:

  • Data reliability is critical
  • Your models depend on up-to-date sources
  • You want strong enforcement of data SLAs

The demo shows examples of both approaches, so you can choose which one best fits your project needs.

Monitoring Freshness in the dbt Cloud Catalog

dbt Cloud makes it easy to monitor freshness results long after the run completes. In the Catalog, you can drill down into each source and see the most recent freshness status, including warnings and errors. This gives data teams better visibility into upstream issues without needing to dive into logs.

For example, in our demo environment, the Catalog displays a warning icon for dbt_talk_demo_sources. Opening the source reveals the individual freshness statuses for each table. This is especially helpful in larger projects where tracking freshness manually would be impractical.

Key Takeaways

This demo highlights the full power and flexibility of dbt Source Freshness in real-world analytics environments. Here are the main lessons:

  • Freshness thresholds provide an essential guardrail for data reliability.
  • loaded_at_field is simple when the timestamp is in the table.
  • loaded_at_query enables more advanced scenarios using external metadata.
  • Filters help refine which rows count toward freshness.
  • dbt distinguishes between OK, warning, and error states in a clear, actionable way.
  • Freshness checks can run as part of your dbt Cloud jobs with configurable strictness levels.
  • The dbt Cloud Catalog provides ongoing visibility into the freshness of all sources.

By combining these tools, you can ensure your source data stays timely, trustworthy, and perfectly aligned with your organization’s SLAs. This ultimately improves downstream analytics quality, enhances user confidence, and reduces the risk of building insights on outdated data.

Watch the Video

How to Derive Dimensions and Facts from a Data Vault Model

Patterns for Deriving Data Vault Dimensions and Facts

If you’ve gone through a Data Vault training, you know that Data Vault is a pattern-driven methodology — from loading the Raw Data Vault all the way through business logic implementation in the Business Vault. But one question that consistently comes up, especially among practitioners bridging the gap between modeling and delivery, is this: How do you actually derive Dimensions and Facts from a Data Vault model?

It’s a great question, and the good news is that just like the rest of Data Vault, there are clear, repeatable patterns for doing it well. Let’s walk through them.



The Pattern-Based Nature of Data Vault Delivery

Data Vault is not just a modeling technique — it’s an entire methodology built around patterns. There are patterns for loading the Raw Data Vault, patterns for modeling it, and patterns for implementing business logic in the Business Vault. The same is true for the information delivery layer, where you produce standard target entities like Dimensions and Facts (or 3NF entities, flat/wide schemas, and more).

Once you internalize these patterns, deriving your target entities becomes a predictable and even automatable process. That’s one of the most underappreciated strengths of Data Vault: the patterns repeat, and the more you use them, the faster and more confident you become.

Deriving Dimensions: The 80/20 Rule

Roughly 80% of your Dimensions will follow one core pattern: they are derived from a combination of a Hub, one or more Satellites, and — critically — a PIT Table (Point-In-Time Table).

The basic query pattern looks like this:

  1. Start with the PIT Table as your base
  2. Join the Hub to retrieve the business key
  3. Join the relevant Satellites for descriptive attributes
  4. Cherry-pick the attributes you need for your Dimension

The remaining 20% of Dimensions come from Links rather than Hubs. In these cases, you start with a PIT Table built on a Link, join the Link itself for hash keys and Dimension references (often called Bridge Dimensions), and then join the Satellites to describe the relationship.

In practice, this covers close to 99% of all Dimension derivations. There are always edge cases, but this two-pattern approach handles the vast majority of real-world scenarios.

Why PIT Tables? The Case for Point-In-Time

If you’re new to PIT Tables, they can look intimidating at first — a structure full of hash keys and timestamps. But once you understand the pattern, it’s actually one of the most elegant tools in the Data Vault toolkit. And here’s the key insight: in modern implementations, PIT Tables are generated by automation tooling, so you rarely build them by hand. You learn how to use them, not how to construct them from scratch.

PIT Tables are essential for several reasons:

Supporting All SCD Types

A PIT Table provides the foundation for Slowly Changing Dimension (SCD) types 0, 1, and 2 — all from the same structure. The snapshot day timestamp embedded in the PIT Table allows you to control the grain and timeline of your Dimension flexibly.

Delivering Stable Reports

Here’s a scenario every analyst will recognize: you pull a report at 9:00 AM and expect it to remain consistent throughout the day. But if your Dimension view is simply joining the latest delta from a Satellite directly, any incoming data update after 9:00 AM will silently change your report’s results.

A PIT Table solves this by identifying the latest valid delta for all hash keys at a specific snapshot timestamp — say, 9:00 AM. This decouples your information delivery from live, incoming data pipelines. Users get stable, predictable reports. The data warehouse and the source pipelines can operate independently without stepping on each other.

This is why using a PIT Table, though it may appear more complex at first, is actually simpler and more aligned with what business users actually need: consistent data at a known point in time, not real-time volatility bleeding into their dashboards.

Decoupling Delivery from Ingestion

Beyond stability, the PIT Table serves a broader architectural purpose: it creates a clean separation between the data loading process and the data delivery process. This is a hallmark of mature data warehouse design — and Data Vault makes it a first-class pattern rather than an afterthought.

Want to deepen your understanding of PIT Tables, Satellites, and the complete Data Vault modeling toolkit? Explore our Data Vault 2.1 Training & Certification to get hands-on with these patterns.

Deriving Facts: Links, Non-Historized Links, and Bridge Tables

Facts are a different story. Where Dimensions describe who and what, Facts capture events, transactions, and messages — think banking transactions, call records, IoT sensor readings, or logistics movements.

For Facts, your primary sources in a Data Vault model are:

  • Non-Historized Links (NH Links) — ideal for transactional events that don’t change
  • Dependent Child Links — for subordinate, granular transactional data
  • Bridge Tables — for pre-aggregated or grain-shifted data
  • Joined Link combinations — for cross-domain Fact derivations

If your underlying Link structure already matches the grain you need for your Fact, you can build a simple view on top of it and you’re done. Clean and efficient.

Grain Management: The Core Challenge of Fact Derivation

Things get more interesting when your source data doesn’t naturally sit at the right grain for your target Fact. This is where Bridge Tables and grain shift operations come into play.

Coarsening Grain with GROUP BY

Imagine an NH Link that captures real-time vehicle positions — five messages per second. Your Fact table only needs one position per hour. You need to reduce the granularity.

You do this with a GROUP BY operation on a subset of the Hub references within the Link. The fewer Hub references you group by, the coarser your grain. The result of this aggregation is materialized into a Bridge Table, which then becomes the base for your Fact view.

Refining Grain by Joining Links

The inverse is also possible. If you need a finer grain than any single Link provides, you join multiple Links on their shared Hub references. For example, if one Link connects Customer and Store, and another connects Store and Product, joining them on the shared Store reference produces a result with three Hub references — a finer granularity than either Link alone.

Other Grain Shift Operations

Beyond GROUP BY and joins, there are other grain-shifting operations worth knowing:

  • DISTINCT operations — to remove duplicate rows at a desired level of aggregation
  • JSON array expansion — splitting a JSON array into multiple rows is itself a grain shift, making rows finer

All of these operations can produce results that are materialized in Bridge Tables for performance and reuse, which then serve as the clean input layer for your Fact views.

PIT Tables for Snapshot Facts

There is one important exception to the “Links and Bridge Tables for Facts” rule: snapshot-based Facts. For periodic snapshots — daily inventory levels, end-of-month balances, weekly pipeline status — a PIT Table can serve as an excellent base for your Fact entity.

The catch is that PIT Tables are sometimes pruned over time to keep them manageable. If you need to preserve a long history of snapshots for your Fact, consider using a dedicated Bridge Table to maintain those snapshots independently from your operational PIT Tables. This gives you full control over retention and history without disrupting the PIT Table’s primary purpose.

Beyond Dimensions and Facts: Wider Applicability

The same patterns extend naturally to other target model types:

  • 3NF (Third Normal Form) entities — derived using similar logic from Hubs, Links, and Satellites
  • Flat and wide schemas — built by joining PIT Tables to Bridge Tables and then joining Satellites to pre-join all descriptive attributes into a single denormalized entity
  • Fully denormalized Fact entities — start with a Bridge Table or Link, join PITs, then join the relevant Satellites to fold all Dimension attributes directly into the Fact

This last pattern — the fully denormalized Fact — is particularly powerful for downstream consumers like machine learning pipelines or self-service analytics tools that benefit from wide, flat tables. And it follows the exact same building blocks.

Summary: The Patterns at a Glance

Target Entity Primary Source Pattern
Standard Dimension (80%) PIT + Hub + Satellites Join PIT → Hub → Satellites, select attributes
Bridge/Relationship Dimension (20%) PIT on Link + Satellites Join PIT → Link → Satellites
Transactional Fact NH Links / Dependent Child Links View on top of Link at correct grain
Aggregated Fact Bridge Table (GROUP BY result) Grain shift → materialize → view
Snapshot Fact PIT Table or Bridge Table Snapshot timestamps → Fact view
Denormalized Fact Bridge + PIT + Satellites Pre-join all descriptive attributes

Learn the Patterns, Apply Them Everywhere

One of the most satisfying aspects of working with Data Vault is that once you’ve internalized these patterns, they compound. Each new project reinforces the same building blocks: Hubs, Links, Satellites, PIT Tables, Bridge Tables. The same logic for deriving a Dimension applies to deriving a 3NF entity. The same grain management principles for Facts carry over to denormalized wide tables.

Data Vault doesn’t ask you to reinvent the wheel for every project — it gives you a set of reliable, tested patterns and asks you to apply them consistently.

If you’re ready to master these patterns in depth — including hands-on work with PIT Tables, Bridge Tables, and full information delivery models — check out our Data Vault 2.1 Certification and Training. And if you’re just getting started, there’s a free Data Vault handbook available as a concise primer covering the core concepts, pros and cons, and when to use Data Vault in your architecture.

The patterns are there. Once you learn them, they’ll follow you everywhere.

Watch the Video

How to Deal With Late Arriving Data

Late Arriving Data

Late arriving or backdated data is a common challenge in data warehousing. In Data Vault, it is important to distinguish between the technical timeline used for loading data and the business timeline representing when events actually occurred in the real world.



1. Technical Timeline vs Business Timeline

When loading data into the Raw Vault, always use a Load Date Timestamp (LDTS):

  • Set when the record first arrives in your target system (landing zone, data lake, or Raw Vault).
  • Never backdate this timestamp—it should always move forward.
  • Used for incremental loading, delta detection, and reproducibility of snapshots.

This timestamp does not reflect the real-world timing of the data. It is purely a technical artifact to track ingestion order.

2. Capturing the Business Timeline

To handle late arriving or backdated data, use descriptive business dates stored in your satellites, such as:

  • Apply Date / Effective Date: When the data became valid in the source system or real world.
  • Last Modified Date: When the record was last changed in the source system.

These business timestamps allow you to create snapshots or temporal views that reflect the true order of events.

3. Timeline Corrections Without an Extended Tracking Satellite

You can correct timelines without adding additional satellites by leveraging the business timestamps stored in your existing satellites:

  1. Create temporal PIT tables or snapshots based on the business timeline, not the load date.
  2. When late-arriving data is detected:
    • Option 1: Rebuild the affected snapshots to include the late data.
    • Option 2: Apply counter transactions to reverse previous measures and apply the updated values.
  3. Always keep the load date unchanged—it only tracks ingestion, not validity.

This approach ensures that your historical reports reflect the correct business sequence without complicating the Raw Vault model.

4. Practical Guidelines

  • Do not order or aggregate data using the load date when interpreting or reporting; always use business dates.
  • Maintain separate timelines:
    • Load Date: Technical, for data ingestion and reproducibility.
    • Business Date: For interpretation, analysis, and handling late arrivals.
  • Rebuild snapshots or use counter transactions as necessary when late data affects measures or aggregates.

Summary

Late arriving data can be handled in Data Vault without adding extra tracking satellites by clearly separating technical and business timelines. Load Date timestamps remain forward-only, while satellites store business dates to drive temporal snapshots and corrections. Using temporal PIT tables, counter transactions, or snapshot rebuilding ensures your analytics reflect the real-world timeline accurately.

Watch the Video

How to Capture CDC Data in Data Vault Satellites

Capture CDC Data

Capturing Change Data Capture (CDC) data in Data Vault can be tricky, especially when the source mostly sends inserts but occasionally produces duplicates or deletions. Understanding how to handle these cases ensures historical accuracy and avoids data inconsistencies in your hubs and satellites.



The Scenario

Consider the following behavior of your source system:

  • Most of the time, rows are insert-only.
  • During initial load, the same row may arrive twice (once in the bulk load and once as an insert within the same batch).
  • Deleted rows may occasionally appear.

These patterns can lead to duplicates if not handled correctly. At first glance, it might look like you need a Non-Historized Link, but duplicates must still be managed properly.

Why Standard Non-Historized Links May Fail

A standard non-historized Link assumes a single row per combination of hubs. When duplicates arrive, either due to CDC or multiple inserts during initial load, the Link cannot naturally distinguish them, leading to primary key conflicts or overwritten data.

A common—but sometimes problematic—solution is adding counter rows to differentiate duplicates. However, this often requires a GROUP BY in the Information Mart, which can cause performance issues, particularly on non-columnar databases.

Recommended Approach: Capture Technical History in Satellites

Instead of modifying the Link, the recommended approach is to handle duplicates in satellites, preserving the raw source events and their arrival order.

Step 1: Use a Satellite with a Load-Date Sequence

For each incoming batch:

  • Assign the CDC load timestamp to the first row of a given parent.
  • If multiple rows for the same parent exist in the batch, increment the timestamp by a small unit (microsecond, millisecond, or nanosecond) for each subsequent row.

This creates a unique ordering of changes while preserving the technical history, without touching the original raw data.

Step 2: Maintain Historical Order

By adding a microsecond increment to the load date for each row:

  • The first row in the CDC batch gets the base timestamp.
  • The second row gets base timestamp + 1 microsecond, the third row +2 microseconds, etc.

This ensures the latest row has the highest load timestamp, which can be used to drive Point-In-Time (PIT) tables and type-1 dimension replacements.

Step 3: Preserve Batch or CDC Metadata

If your CDC source provides a batch ID or subsequence number, include it in the satellite. This allows for:

  • Tracking which records arrived together
  • Reconstructing the technical timeline of changes

If no metadata exists, the microsecond sequencing on the load date is sufficient to order the rows.

Handling Non-Historized Links with Duplicates

In rare cases, a non-historized Link may receive multiple rows for the same key combination. To handle this safely:

  • Extend the alternate key to include the load date (or other sequencing attribute) in the hash key calculation.
  • This ensures each row has a unique primary key without modifying the raw data.

Key points:

  • No need to use counter rows in the raw link.
  • Duplicates are captured and preserved for historical accuracy.
  • Aggregations in PIT or Bridge Tables can be used for reporting, ensuring performance optimization.

Optional: Bridge Tables for Performance

If your Information Mart requires grouping or deduplication and your database struggles with performance:

  • Create a Bridge Table that pre-aggregates or resolves duplicates.
  • The Bridge Table stores only the latest row (or the aggregated result) for reporting.
  • You maintain the raw satellite history in case full lineage or historical reconstruction is needed.

Summary

  • Capture all incoming CDC events in a satellite, including duplicates, without modifying the raw data.
  • Use microsecond increments on the load date to order multiple rows per parent.
  • Include CDC batch metadata if available to preserve groupings and arrival order.
  • For non-historized Links receiving multiple rows, include the load date in the hash calculation.
  • Bridge Tables or PIT tables handle reporting and aggregation efficiently, while maintaining full historical traceability.

This approach preserves auditability, ensures correct historical ordering, and avoids performance issues in the Information Mart.

Watch the Video

dbt Source Freshness: Ensuring Reliable and Timely Data in Your Pipeline

dbt Source Freshness

Data teams rely on timely, accurate, and complete data to support dashboards, KPIs, reporting, and data-driven decision making. But even the most advanced data models and transformation logic cannot fix one critical issue: stale or outdated upstream data. This is where dbt Source Freshness becomes one of the most valuable quality checks in your analytics engineering toolkit.

In this article, we take a close look at what dbt Source Freshness is, why it matters, how it works under the hood, and how you can configure and run freshness checks both locally and in dbt Cloud. If your organization depends on reliable data pipelines—or if you’ve ever discovered too late that a report was built on old data—this guide will help you avoid those costly surprises.



What Is dbt Source Freshness?

Source freshness in dbt is a built-in mechanism that measures how up-to-date data is in your defined source tables. While data transformations can apply logic, aggregations, and business rules, they inherently depend on data arriving on time. If source data is delayed, incomplete, or entirely outdated, every model downstream will reflect that delay.

dbt Source Freshness provides a simple, reliable indicator of whether the data you are working with is fresh enough to support your operational and analytical processes. It helps you answer one crucial question:

“Is the data I’m transforming actually the latest data available?”

When enabling freshness checks, dbt evaluates the most recent timestamp from a specified column in your source table and determines whether that timestamp violates your defined freshness thresholds. These thresholds act as data SLAs for your pipeline.

Why Source Freshness Matters

The importance of monitoring source data freshness cannot be overstated. When upstream data is stale, the consequences cascade throughout your entire analytics ecosystem. Dashboards may show outdated KPIs. Operational teams may make decisions based on incomplete numbers. Forecasts and reports may misrepresent the true state of the business.

One scenario that many data teams have encountered illustrates the problem perfectly: a business report runs on what everyone assumes is the latest data. After a few weeks, the team discovers that the upstream system had stopped updating its tables entirely. What appeared to be fresh data was actually months old. As a result, the report generated incorrect metrics for an extended period.

With source freshness monitoring in place, delays like these can be caught immediately. dbt highlights them clearly, allowing teams to:

  • Detect upstream system failures.
  • Identify delays in ingestion or replication pipelines.
  • Enforce data delivery SLAs with source system owners.
  • Stop inaccurate transformations from running on stale data.

Freshness checks turn what could be a hidden issue into a transparent, actionable signal.

How dbt Source Freshness Works

Source freshness configuration lives directly inside the YAML file where your source is defined. This design decision is intentional—freshness belongs to the source, not to downstream models. Each source or table can have its own customized freshness rules.

A typical source block with freshness configuration looks like this:

sources:
  - name: my_source
    tables:
      - name: orders
        freshness:
          warn_after: {hours: 24}
          error_after: {hours: 48}
        loaded_at_field: updated_at

Let’s break down the key components.

loaded_at_field

This is the timestamp column dbt uses to determine when the most recent record arrived. dbt queries this field, finds the newest timestamp, and calculates its age relative to the current time.

Important: dbt always evaluates freshness in UTC time. If your source system stores local timestamps (e.g., CET, EST), the value in loaded_at_field must be converted to UTC.

Thresholds: warn_after and error_after

Freshness thresholds define what “fresh enough” means. dbt compares the age of the newest record with these time limits and returns one of three statuses:

  • pass – the data is within the acceptable freshness window.
  • warn – the data is late but not critically late.
  • error – the data is beyond the maximum acceptable age.

These thresholds effectively act as SLAs, helping teams formalize expectations about data arrival. For example:

  • Warn after 24 hours.
  • Error after 48 hours.

If the source table hasn’t received new records in over 48 hours, dbt marks the freshness check as an error, signaling that the table is unreliable until updated.

What Happens During a Freshness Check?

When you run a freshness check, dbt performs a straightforward but effective procedure:

  1. dbt queries the loaded_at_field and finds the most recent timestamp.
  2. It calculates the time difference between that timestamp and the current UTC time.
  3. It compares the age of the data to your defined thresholds.
  4. It returns a pass, warn, or error result.

This process is intentionally lightweight and fast. It avoids unnecessary complexity while giving teams a dependable, high-value signal about upstream data timeliness.

How to Run Freshness Checks in dbt

Running a freshness check in dbt is simple. The main command is:

dbt source freshness

This command evaluates freshness for all sources that have freshness configurations defined. You can also target a specific source or table:

dbt source freshness --select source:my_source
dbt source freshness --select source:my_source.orders

When executed, dbt displays the freshness status for each table along with metadata such as:

  • The latest timestamp found.
  • The calculated age of the data.
  • The threshold values used.

Running Freshness Checks in dbt Cloud

dbt Cloud makes managing freshness checks even easier. You can create a dedicated job that runs only freshness checks, or you can add freshness as a step in a larger job. This enables automatic monitoring without requiring manual execution.

Once the job completes, results appear directly in the dbt Cloud UI. For each table, you can see:

  • The age of the most recent record.
  • Whether the table passed, warned, or errored.
  • When the freshness check was last executed.

You can also inspect the detailed logs to understand exactly how dbt evaluated each source.

Why Freshness Checks Should Be a Standard Practice

In modern analytics engineering, data reliability is just as important as transformation logic. Freshness checks are a lightweight yet powerful way to ensure that your source systems are delivering data on time.

Without freshness checks, data issues may go unnoticed until they have already impacted dashboards, stakeholder decisions, or downstream processes. With freshness monitoring enabled, you gain visibility into problems early, allowing your team to respond quickly and prevent incorrect reporting.

As data ecosystems grow more complex—with multiple ingestion pipelines, third-party APIs, and event-based systems—freshness checks provide a simple, standardized way to maintain trust in your data.

Watch the Video

How to Model Multi-Type Business Keys in Data Vault

Multi-Type Business Keys

One of the more subtle challenges in Data Vault modeling appears when a single source delivers multiple types of business keys in one structure. Things get even more complex when those keys:

  • Point to different business objects depending on a type indicator
  • Have different formats than the “real” business keys used in existing hubs
  • Require cleansing or transformation before they can be linked correctly

In this article, we’ll walk through how to model such a scenario in Data Vault, how to think about Links and key types, and—most importantly—where business logic and transformations should live.



The Problem Statement

Let’s restate the scenario in simple terms.

Your source table looks something like this:

| ItemBK | KeyType | KeyValue |
|--------|---------|----------|
| ITEM1  | A       | T123     |
| ITEM1  | B       | 999-45   |

From a business perspective:

  • ItemBK identifies an item (one clear Hub candidate)
  • KeyType determines which business object the key refers to
  • KeyValue contains the actual identifier

The complication comes from the target data model:

  • For KeyType A, the key belongs to Hub A, but the real business key is 123, not T123
  • For KeyType B, the key belongs to Hub B and can be used as-is

So the value T123 must eventually link to Hub A using the cleaned value 123.

This raises three classic Data Vault questions:

  • How should this be modeled from staging to the Raw Data Vault?
  • Should the key type be stored in the Link?
  • Where should key cleansing and transformation happen?

First Principle: Preserve the Source

Before jumping into modeling options, let’s clarify a core Data Vault principle:

The Raw Data Vault must always allow you to recreate the original source delivery.

This means:

  • No irreversible transformations in staging
  • No hidden business rules applied too early
  • Full auditability back to the source

Every modeling decision below is guided by that principle.

Identifying the Core Business Objects

From the example, we can already identify several business concepts:

  • Item → clearly a Hub (Hub Item)
  • KeyValue → a business key, but its meaning depends on KeyType
  • Relationships → Item is related to different business objects depending on KeyType

This is a common pattern: a single source column contains references to different hubs.

Modeling Option 1: Pivot the Source (When Key Types Are Fixed)

If—and this is a big if—you can guarantee that:

  • The number of key types is small
  • The set of key types is stable (e.g. only A and B)

Then a pivoted staging approach can work.

What This Looks Like

You pivot KeyType into separate columns:

| ItemBK | KeyValue_A | KeyValue_B |
|--------|------------|------------|
| ITEM1  | T123       | NULL       |
| ITEM1  | NULL       | 999-45     |

This allows you to:

  • Load Hub Item from ItemBK
  • Load Hub A from KeyValue_A
  • Load Hub B from KeyValue_B
  • Create Links between Item and each Hub

Limitations

This approach breaks down quickly when:

  • A new key type appears
  • Key types are dynamic or unknown
  • The source evolves frequently

In those cases, pivoting creates a brittle staging layer and forces frequent refactoring.

Modeling Option 2: Generic Hub for Typed Keys

When key types are dynamic or extensible, a more robust pattern is required.

In this case, treat the combination of KeyType + KeyValue as a business key.

Create a Generic “Key” Hub

This hub represents “external identifiers” regardless of their semantic meaning.

  • Hub Key
    • Business Key = KeyType + KeyValue

Why composite?

  • Key values may overlap across types
  • Only the combination uniquely identifies the business object

You then:

  • Load Hub Item from ItemBK
  • Load Hub Key from (KeyType, KeyValue)
  • Create a Link between Item and Hub Key

This guarantees:

  • Full preservation of the source
  • No assumptions about future key types
  • Clean separation of concerns

But What About the Existing Hubs (Hub A, Hub B)?

This is where many teams are tempted to inject business logic too early.

Yes, you already have Hub A and Hub B. But the mapping logic that says:

  • “If KeyType = A, this belongs to Hub A”
  • “If KeyType = B, this belongs to Hub B”

is business logic.

Business logic does not belong in the Raw Data Vault.

The Correct Place: Business Vault

In the Business Vault, you can:

  • Create derived business hubs
  • Filter by KeyType
  • Union keys from different raw sources

For example:

  • Business Hub A = existing Hub A UNION keys from Hub Key where KeyType = ‘A’

This approach ensures that:

  • Raw data remains unchanged and auditable
  • Business rules are explicit and versionable
  • Changes in logic don’t force Raw Vault reloads

Should the Key Type Be Stored in the Link?

The answer depends on the modeling option.

Option 1 (Pivoted Source)

No.

Once pivoted, the key type is implicit in the structure. There is no longer a “KeyType” concept in the Link.

Option 2 (Generic Key Hub)

Again, no.

The key type becomes part of the Hub business key. The Link simply connects Item to the generic Key Hub.

If—and only if—key values are guaranteed to be unique across types, an alternative is:

  • Store KeyType in a Satellite on the Hub

But in most real-world cases, using a composite business key is safer.

Where Should Key Cleansing and Transformation Happen?

This is the most important question.

In the example:

  • T123 must become 123 to link to Hub A

This is a business rule.

What Not to Do

  • Do not strip the “T” in staging
  • Do not load cleaned values directly into the Raw Hub

Why?

  • The rule may change
  • You lose the original source value
  • You break auditability

The Recommended Pattern: Same-As Link

Instead, load the raw value T123 as-is and create a Same-As Link.

This link connects:

  • The “raw” business key (T123)
  • The “master” business key (123)

Benefits:

  • Full traceability
  • Explicit business logic
  • Flexible remapping if rules change

Same-As Links are purpose-built for this kind of semantic deduplication.

Putting It All Together

A robust end-to-end solution looks like this:

  • Stage data exactly as delivered
  • Load ItemBK into Hub Item
  • Load (KeyType + KeyValue) into a generic Hub
  • Link Item to the generic Hub
  • Apply business logic in the Business Vault
  • Use Same-As Links to handle key transformations

Key Takeaways

  • Multi-type business keys are common—and tricky
  • Preserve the source structure in the Raw Data Vault
  • Avoid early cleansing and transformation
  • Use composite business keys when uniqueness is not guaranteed
  • Apply business rules in the Business Vault
  • Use Same-As Links for key normalization

Handled correctly, this pattern gives you maximum flexibility, auditability, and long-term stability—exactly what Data Vault was designed for.

Watch the Video

Data Vault Link Naming Convention

Link Naming Convention

One of the most common questions we hear from Data Vault practitioners—especially once a model grows beyond a few hubs and links—is this: how do you define a clear link naming convention, and how do you avoid getting lost in all the different link types when querying current versus historical data? It’s a very practical concern, and if you don’t address it early, your Data Vault can quickly become hard to understand, even for experienced developers.

In this article, we’ll walk through a pragmatic approach to Data Vault link naming conventions. We’ll cover how to name links in a business-friendly way, how to encode technical meaning into the name without overcomplicating it, and how naming helps users understand which links to use for historical data and which are more event- or transaction-oriented.



The First Question: How Do You Name a Link?

Let’s start with the most basic aspect of the question: how do you name a link at all? Imagine a simple business scenario. A customer walks into a store and buys a product. From a Data Vault perspective, this creates a relationship between three business keys: customer, store, and product. This relationship is represented by a link.

Now you have several naming options. You could name the link something like customer_store_product_link, purely describing the hubs it connects. Or you could give it a business-oriented name such as retail_transaction_link or sales_transaction_link.

Our strong recommendation is to prefer business-driven names whenever possible. If the relationship clearly represents a real business concept, then the link should be named after that concept. Calling it a retail transaction link immediately tells business users and developers what this link represents, without forcing them to interpret a technical list of hubs.

That said, there are cases where a business name simply doesn’t exist. Sometimes a link truly represents nothing more than a structural relationship, such as a customer being associated with a store, without a clear transactional or event-based meaning. In those cases, it is perfectly fine to fall back to a composite name like customer_store_link.

The key point here is consistency and intent. If there is a meaningful business name, use it. If not, use a descriptive composite name that clearly shows which hubs are involved.

Ordering Hub Names in Composite Links

When you do use composite names, the next question is often about ordering. Should it be customer_store or store_customer? From a pure Data Vault perspective, links are end-to-end relationships with no inherent direction. Technically, the order does not matter.

However, from a modeling and readability perspective, a consistent ordering rule helps a lot. One practical guideline is to follow the natural business hierarchy if one exists. For example, if you model an industry body and the organizations that belong to it, the industry body is conceptually “above” the organization. In that case, naming the link industrybody_organization_link feels natural and intuitive.

This approach mirrors how people think about hierarchies: from root to leaf, from higher-level concept to lower-level concept. Again, the most important part is not which rule you choose, but that you standardize it across the entire Data Vault.

Standardization Beats Perfection

No naming convention will ever be perfect. What matters far more is that everyone follows the same rules. At Scalefree, for example, we deliberately leave some freedom to developers when choosing link names, with a clear preference for business-oriented naming. At the same time, we provide documented guidelines that explain how to make those decisions consistently.

This balance allows teams to model complex domains without being blocked by overly rigid naming rules, while still keeping the model understandable and navigable.

Dealing with the Many Types of Links

Once you move beyond basic modeling, the real challenge begins: Data Vault doesn’t just have one type of link. Over time, a model accumulates several varieties, each serving a different purpose.

Some of the most common link types include standard links in the Raw Data Vault, business or exploration links in the Business Vault, non-historized links (also known as transactional links in Data Vault 1.0), hierarchical links, same-as links, dependent child links, and even computed or aggregated links used to reuse logic across multiple bridges.

Without a clear convention, these different link types quickly become indistinguishable, making it hard to know which one to use for a specific query or use case.

Prefix or Suffix? Why Suffixes Usually Win

One common design decision is whether to encode link type information as a prefix or a suffix. While prefixes like link_retailtransaction are sometimes used, we generally recommend suffixes instead.

The reason is simple: suffixes group related objects together more naturally. If everything related to a customer starts with “customer”, then the hub, its satellites, and related links appear together when browsing schemas or metadata. This makes the model easier to explore and understand.

For example, a customer hub might be named customer_h, while a satellite could be customer_data_s. Using suffixes ensures that all customer-related entities are visually grouped.

A Compact Suffix-Based Pattern

To keep naming both expressive and machine-readable, we recommend a compact suffix pattern. In this approach, every link ends with an L, indicating that it is a link. Additional single-character markers are added before the L to indicate special link types.

For example, a standard link in the Raw Data Vault simply ends with _l. A non-historized link ends with _nl. A hierarchical link ends with _hl. A same-as link ends with _sl. A dependent child link ends with _dl.

When working with the Business Vault, an additional B is added. A standard Business Vault link ends with _bl. A dependent child link in the Business Vault becomes _bdl. In this scheme, the Raw Data Vault is the default, so there is no explicit “R” marker.

This compact notation might look cryptic at first, but it has significant advantages. It keeps names short, consistent, and easy to parse automatically.

Why Short Codes Are Powerful

Using single-character indicators is not just about aesthetics. It enables powerful automation and governance capabilities. With consistent suffixes, you can use regular expressions on metadata to identify entity types automatically.

For example, you can quickly validate that all dependent child links contain the required hub references, hash keys, and record source attributes. You can also apply automated tests and checks depending on the link type, without relying on manual inspection.

This becomes especially valuable in larger Data Vault environments where hundreds or thousands of entities exist.

Putting It All Together: Naming Examples

Let’s revisit the earlier example of a relationship between customer and store. In the Raw Data Vault, a simple link could be named customer_store_l. If this relationship is modeled in the Business Vault, the name could become customer_store_bl.

If the relationship represents a business transaction and is non-historized, a name like retailtransaction_nl or retailtransaction_bnl (for the Business Vault) clearly communicates both the business meaning and the technical behavior.

These names immediately tell an experienced user what kind of data to expect and how the link should be used.

Which Links to Use for Current vs. Historical Data?

The second part of the original question is just as important: how do you know which links to use when querying current versus historical data?

The key insight here is that almost all Data Vault entities are historical by nature. Links record which relationships have ever existed during the lifetime of the data warehouse. On their own, they usually do not tell you whether a relationship is currently active.

To answer questions about “current” relationships, you typically need additional structures such as effectivity satellites or deletion indicators. These tell you whether a relationship is active, inactive, or deleted at a given point in time.

For example, if customers can change their preferred store, the link captures all store assignments that ever existed. An effectivity satellite tells you which assignment is valid right now.

Standard Links vs. Non-Historized Links

Another important distinction is between long-lived relationships and point-in-time events. Standard links are often used for relationships that exist over a period of time, such as employment, ownership, or assignments.

Non-historized links, on the other hand, are typically used for events, messages, or transactions that occur at a specific moment. A customer purchasing a product, an invoice being issued, or a sensor sending a reading are all examples of point-in-time events.

Even though they are called “non-historized,” these links can still store large volumes of historical data. The name simply reflects that the relationship itself does not persist over time—it happens at a single point in time.

Dependent child links are a special case of this pattern, often used for structures like invoice line items, where detailed data depends on a parent transaction.

Teaching the Model to Users

Finally, no naming convention works unless users understand it. A clear, consistent naming scheme allows you to teach users how to recognize link types, understand their purpose, and choose the right entities for their queries.

Once users know that “_l” means Raw Vault link, “_bl” means Business Vault link, and “_nl” indicates an event-based relationship, they can navigate even large models with confidence.

Final Thoughts

A good Data Vault link naming convention is not about memorizing rules; it’s about reducing cognitive load. Business-driven names improve readability, suffix-based patterns improve structure, and compact codes enable automation and governance.

If you invest the time to define and standardize these conventions early, you will save countless hours later—both for developers and for business users trying to understand and trust your data warehouse.

Watch the Video

Close Menu