Null Business Keys

Data Vault is a methodology used for modeling data in large-scale data warehouse environments. It’s designed to handle rapidly changing data and easily scalable. One of the challenges faced by data modelers is handling null business keys in Data Vault. In this article, we’ll explore how to model links with null business keys, using an example scenario involving the Entity_Roles table.

Understanding the Challenge

The question we’re dealing with here revolves around how to model the Entity_Roles table when there are null values in the linked data fields. In particular, the table might have records where certain fields like Project or Task are null, creating a challenge when attempting to create links between different entities. Let’s break down the situation and find the best way to handle these null values and ensure smooth data modeling.

Context: The Entity_Roles Table

The Entity_Roles table holds information about the relationship between entities and their roles in different tasks and projects. However, there may be situations where a given record in the table has missing (null) values for certain business keys, such as Project or Task.

What Are Business Keys and Links?

Before diving into the solution, let’s first clarify what business keys and links are in Data Vault. A business key is a unique identifier for a specific business object, such as a project or task. Links are used in Data Vault to represent relationships between business keys. The Entity_Roles table, for example, might act as a link between an entity, its role, and the task or project it’s associated with.

Approaching the Problem: Handling Null Business Keys

The key challenge here is dealing with the null values in the Entity_Roles table. Null values in business keys can create issues when trying to establish relationships between entities. A typical solution in Data Vault is to replace these null values with zero keys. A zero key represents an unknown business key, allowing the link to still function properly despite missing data.

Option 1: Using Zero Keys

One approach to handling null values is to use zero keys for any missing business keys. For instance, if a Task or Project is null, we can assign a zero key to represent the unknown value. The advantage of using zero keys is that it helps maintain query efficiency by ensuring consistent joins, and it allows us to deal with missing data without breaking the link.

Option 2: Splitting the Link into Two Views

Another option could be to pre-stage and create two views to split the Entity_Roles table: one where Task is not null, and another where Project is not null. However, this approach is not recommended because splitting the data may lead to unnecessary complexity and potential inconsistencies in the data. In general, it’s a good practice in Data Vault to avoid splitting data at this level.

Option 3: Handling Multi-Activity Data

If the Entity_Roles table contains multiple rows for the same relationship (e.g., an entity linked to both a task and a project), you may need to account for multi-activity data. This can be done by using a Multi-Active Satellite, which allows you to capture multiple descriptions of the same link. This ensures that you can store all the variations of a relationship, such as different roles or project-task assignments, in the same link.

Designing the Solution

When it comes to designing the solution, there are a few key design decisions to make:

1. Create a Single Link

Rather than splitting the Entity_Roles table, it’s generally better to keep it as a single link. This approach allows you to maintain all relationships in one place and makes the system simpler to manage.

2. Using a Multi-Active Satellite

If there are multiple active roles or tasks associated with a single entity, then using a Multi-Active Satellite is a good option. This satellite would store all the variations of the role-task-project relationships, allowing you to track all relevant data points.

3. Handling Null Values with Zero Keys

As mentioned earlier, replacing null values with zero keys is a good practice in Data Vault. This helps maintain referential integrity, ensures that your links remain intact, and avoids the need for complex filtering or splitting of data.

4. Use of CDC Satellites for Intraday Changes

If your data involves intraday changes (i.e., updates that occur within a single day), you may want to use Change Data Capture (CDC) Satellites. These satellites track changes at the record level, allowing you to preserve the history of the relationships between entities, roles, tasks, and projects.

Additional Considerations

There are a few other considerations when modeling links with null business keys:

1. Should I Create a Weak Hub for ID?

No, it’s generally not recommended to create a weak hub for the ID. Instead, treat the ID as a descriptive attribute and store it in the satellite attached to the link.

2. Where Should I Put Descriptive Data?

Descriptive data should go in the satellite. This can include things like roles, entity types, or other attributes that provide more context to the link between entities.

3. Should I Put the Role in the Link?

If the role is an important business key, you could consider putting it in its own hub. However, if the role is just a descriptive attribute, it’s best to store it in the satellite attached to the link. If the role becomes a business key later, you can refactor the design and create a hub for it.

Final Thoughts

Modeling links with null business keys is a common challenge in Data Vault. The key is to handle null values appropriately, whether that’s by using zero keys, leveraging multi-active satellites, or managing intraday changes with CDC satellites. By maintaining a clear and consistent approach to handling these null values and relationships, you can ensure that your Data Vault design is scalable, efficient, and capable of handling complex data scenarios.

Watch the Video

Modeling Project Tasks and Actions

In the world of Data Vault, creating effective data models to capture project tasks and actions is a crucial part of building scalable, efficient, and auditable data solutions. One of the challenges that data engineers face when building such models is ensuring that the history of project tasks and actions is captured correctly, while also accounting for the complexity of slowly changing data and continuous changes like those seen in Change Data Capture (CDC) environments.

Understanding the Data Model

Let’s start by understanding the data model in question. We have three key tables:

• Projects: This table contains details about various projects.
• Tasks: This table contains tasks associated with each project. It has a foreign key linking back to the Projects table.
• Actions: Each task can have one or more actions associated with it, and the Actions table has a foreign key linking back to the Tasks table.

The objective is to create a Project Dimension and a Task Dimension. The Task Dimension should include an additional attribute that represents the latest action associated with each task. To solve this, there are multiple approaches you could take, but two common ones stand out:

• Multi-Active Satellite (MA-SAT) based on sets: The simplest approach would be to use a MA-SAT model based on sets, selecting the latest action for each task within those sets. However, this can be complex in a CDC environment.
• Multi-Active Satellite (MA-SAT) based on an MA-Attribute: Instead of using sets, this model would use an action ID as an MA-Attribute, helping to simplify the approach while avoiding the issues created by capturing sets.

Challenges with the CDC Environment

When your source system uses Change Data Capture (CDC), the problem of handling sets becomes more complicated. In CDC, you’re dealing with a stream of changes where new records are added, existing records are updated, and old records may be deleted. This constant flow of data presents two key challenges:

• Capturing sets is not straightforward: In a typical MA-SAT approach, a set captures all records related to a specific task or project, but with CDC data, you’re more likely to receive individual changes, rather than a full snapshot of all records at once.
• Volume management: In a CDC environment, where actions are continually added, there is a risk that using sets will generate a massive volume of records. For example, if a new action is added to a task every day, this could result in an overwhelming number of records over time.

Given these challenges, using an MA-Attribute approach where the action ID serves as the key for the task’s latest action provides a more scalable solution. Instead of creating an ever-expanding set, you can focus on just the latest action for each task, keeping the volume under control.

Solution: Using a Non-Historized Link

The idea behind using a Non-Historized Link in this case is to capture actions as they come in from the source system, without the need for sets. In this setup, a Non-Historized Link captures the relationship between tasks and actions and includes the action ID. When new actions come in, they are inserted into the link table, and updates or deletions are handled as changes to the data. This provides a streamlined and efficient way to track the most recent actions associated with each task.

Handling Updates and Deletions

In CDC environments, when updates and deletions occur, it’s important to track these events accurately. The solution to this is technical counter transactions. When an update occurs in the source system, it’s treated as a deletion of the old version and an insert of the new version. This allows the system to track changes over time and ensures the history is accurately captured.

For deletions, we simply negate the value of the original record, ensuring the deleted record is not included in any aggregations. This negation technique is useful for tracking the current state of each task, action, or project.

Building the Task Dimension

The task dimension is where we’ll include the latest action for each task. To do this, we can use a technique involving a Point-in-Time (PIT) table, which captures the latest version of data (in this case, the latest action for each task) by linking the task hub to the appropriate satellite data.

Here’s how this works:

• Create the PIT table for tasks: This table will store the latest version of each task, including references to the task satellite and the action satellite.
• Use window functions: To retrieve the latest action for each task, you can apply window functions in your SQL queries to order by the business date (or load date) and select the latest action based on these values.

Once the PIT table is created, it can be used to build the Task Dimension, which will then reflect the latest action for each task.

Optimizing the Approach

One of the key considerations when implementing this model is performance. Using window functions and PIT tables is an effective way to manage the volume of records, but it’s important to ensure your database is optimized for these types of queries. In high-volume environments, applying business logic during the PIT table loading process can reduce the load time and make data delivery faster.

Conclusion

Modeling project tasks and actions in Data Vault requires careful consideration of the data flow, especially in environments that rely on CDC. By using Non-Historized Links, Multi-Active Satellites based on MA-Attributes, and PIT tables, you can create a scalable, efficient model that captures the latest actions associated with each task, without generating excessive volumes of data.

By applying these techniques, you can streamline the data flow, optimize performance, and ensure the data is always accurate and up-to-date, making it easier for your data users to access the information they need.

Watch the Video

What Is the Unit of Work?

The Unit of Work is a fundamental concept in Data Vault modeling. It refers to the idea of capturing all the related business keys from a source system together in a single Link structure. This ensures that the relationships represented in the raw data are maintained intact in your Raw Data Vault.

Think of it as keeping all pieces of a puzzle together. When you break the pieces apart without a clear strategy to reassemble them, you risk losing the original picture—or worse, creating a picture that never existed in the first place.

The Key Question: What Happens If You Split Links?

The question posed in our Data Vault Friday session was:

“It would be good to refresh the topic of the Unit of Work. What is the information I’d be losing if I split wider Links into smaller pairs (DrivingKey -> Key1/Key2 etc)? I can think of a technical setup where I’d have multiple Links and Effectivity Satellites and I should be able to get the same results to any point in time… What information exactly gets lost if I split Links?”

The Risk of Splitting Links: A Practical Example

Let’s start with a simple example to illustrate the point:

Imagine you have a relationship between three business entities—Customer, Store, and Product. Customers go into Stores and purchase Products. If you model this relationship as a single Link that includes all three Hubs (Customer, Store, Product), you have a clear and accurate picture of that transaction.

Now, suppose you decide to split this Link into two smaller Links:

• Customer -> Store
• Customer -> Product

At first glance, this may seem fine. However, when you attempt to reconstruct the original dataset by joining these two Links, you may create new combinations that never existed. For example:

• Customer A visited Store K and purchased Product X (valid)
• Customer A visited Store L (also valid), but when you join this with Product X, you create a false relationship—Customer A purchased Product X at Store L (which never happened in reality).

This phenomenon, where new, artificial rows are generated during joins, leads to data inconsistencies. You’ve effectively introduced relationships that didn’t exist in your source system. That’s the major risk when splitting Links.

The Jedi Test: Verifying Your Model

To avoid these issues, Michael recommends applying what he calls the Jedi Test (yes, Star Wars fans, rejoice!).

The Jedi Test is a simple but powerful validation: Try to reconstruct the original source dataset from your target Data Vault model. If you can recreate the original rows exactly—no more, no less—you’re golden. If you end up with extra rows or missing rows, you’ve got a problem.

This test helps you ensure that your Links are modeled correctly and that no data is lost or inaccurately recreated when splitting Links.

Driving Keys and Unit of Work

One scenario where splitting Links causes definitive problems is when you’re dealing with Driving Keys. Driving Keys are a subset of business keys that uniquely identify a relationship.

Michael explains that splitting components of a Driving Key almost always results in a Unit of Work problem. If you break apart a Driving Key, you risk losing the integrity of your data relationships. That’s why it’s best to keep all Hub references from the same source together in a Unit of Work Link within the Raw Data Vault.

He emphasizes that although he’s not a fan of Driving Keys (calling it a “hate-love relationship”), there are scenarios where they are necessary, and when they are, you must be cautious not to split them.

The Safe Bet: Keep Links Together in the Raw Data Vault

The best practice? Keep all Hub references from the same source table together in one Link—the Unit of Work Link.

This approach guarantees that the full relationship is captured as it appeared in the source data. You can always split these relationships later in the Business Vault, where transformations and additional rules can be applied. But the Raw Data Vault should act as your trusted, immutable source of truth.

By maintaining the Unit of Work in your Raw Data Vault, you protect yourself from potential data inconsistencies and ensure you can always rebuild accurate, reliable data structures downstream.

What If You Have to Split the Link?

Sometimes, technical limitations force you to split a Link. For instance:

• Your database system limits the number of Hub references you can include in a composite index.
• You have descriptive data at different granularities, making a wide Link impractical.

In these cases, Michael suggests a few strategies:

1. Keep the Unit of Work Link as your base Link.
2. Create additional Links to capture specific granularity needs (Customer -> Product, Customer -> Store, etc.).
3. If you split Links, ensure you also capture any missing relationships—for example, if you have Customer -> Store and Customer -> Product, you might also need Store -> Product to maintain the complete relationship picture.

These extra Links allow you to recreate the original dataset accurately if needed, but they add complexity. More Links mean more entities, more joins, and potentially slower query performance. That’s why Michael emphasizes the importance of an efficient model and recommends keeping Links together whenever possible.

Efficiency Considerations

From a performance perspective, fewer Links with more Hub references often result in faster queries. You reduce the number of joins and simplify your model. Conversely, more Links increase complexity and the potential for errors.

Therefore, the trade-off is clear: Simplicity and efficiency with a Unit of Work Link versus flexibility with additional Links—but at the cost of complexity and higher risk.

Final Thoughts and Recommendations

To summarize:

• Always aim to keep Hub references from the same source together in a Unit of Work Link in your Raw Data Vault.
• Perform the Jedi Test to ensure you aren’t losing or falsely creating data relationships.
• If you must split Links, document and manage them carefully, ensuring you maintain all necessary relationships (even those not obvious at first glance).
• Save complex splits and transformations for the Business Vault, where they can be more easily corrected without reloading the entire dataset.

At the end of the day, a well-structured Unit of Work ensures your Data Vault remains a solid, reliable foundation for analytics, reporting, and data science initiatives.

Watch the Video

Understanding Data Vault on Wide Tables

Storing data as wide tables in a data lake while applying a logical Data Vault layer using views presents unique challenges. The goal is to virtualize a raw Data Vault model on top of a data lake while ensuring optimal performance.

Key Considerations for Performance Optimization

• Data Remains the Same: Regardless of whether you use Iceberg, Snowflake, or another technology, the fundamental data characteristics remain unchanged. Issues like dirty data and transformations still apply.
• Descriptive vs. Transactional Data: Wide tables typically contain a mix of master and transactional data. Most attributes tend to be descriptive, especially in master data.
• Granularity Matters: Properly defining granularity helps structure the Data Vault model efficiently, especially for hubs, links, and satellites.

Virtualizing Data Vault on a Data Lake

When implementing a virtual Data Vault, consider:

• Hubs and Links: These should be materialized instead of virtualized. Business keys from wide tables should be extracted and stored in separate iceberg tables for efficiency.
• Satellites: Virtualizing satellites using views is recommended, but pay attention to GDPR and personal data separation.
• Indexing Performance: Hubs, links, PIT (Point-in-Time) tables, and bridges serve as indexes in a data lake environment.

GDPR and Data Privacy Challenges

One of the biggest concerns in a wide table approach is data privacy. Since wide tables often include personal data, you must consider logical deletion, encryption, or physical separation techniques to comply with regulations.

Enhancing Performance with Materialized Structures

To achieve good query performance, consider materializing certain structures:

• Materialized Hubs and Links: These structures act as indexes and improve data retrieval efficiency.
• PIT and Bridge Tables: These further optimize queries by structuring data in a way that minimizes computational load.
• Denormalized Information Marts: End-users should query fully materialized information marts, ensuring high-speed access.

Does Datavault4dbt Support This Approach?

Discussions around Datavault4dbt suggest it may support this approach in the future. If you’re working on a project with this implementation, consider reaching out to Scalefree to explore collaboration opportunities.

Conclusion

Applying Data Vault on wide tables within a data lake architecture requires careful planning. The key takeaways include:

• Virtualizing satellites while materializing hubs and links for performance.
• Addressing GDPR concerns by separating or encrypting personal data.
• Using PIT and bridge tables to enhance indexing and query speed.
• Building fully denormalized information marts for end-user access.

By following these best practices, organizations can ensure efficient and scalable Data Vault implementations on wide tables.

Watch the Video

Investing in Data Warehousing

Data warehousing is a crucial step for businesses looking to manage, integrate, and analyze large volumes of data efficiently. But at what point should a company consider investing in a data warehouse? Many assume that only large enterprises need data warehousing, but the reality is more nuanced.

Is Company Size the Right Factor?

Many businesses believe that data warehousing is only necessary when they reach a certain size. However, size alone is not the determining factor. Instead, the key driver for investing in data warehousing is the complexity of data management and the business challenges that arise from it.

Understanding Business Needs and Data Complexity

Some businesses need data solutions early, even during the startup phase. For example, data-driven companies—such as tech startups that rely on analytics—often invest in data warehousing early to gain insights and improve decision-making.

On the other hand, traditional businesses, like small manufacturing firms, may not require advanced data solutions immediately. If a business operates without much digital data, spreadsheets or simple databases may suffice for years.

When Data Integration Becomes a Challenge

As businesses grow, they tend to use multiple systems for different operations. For example, customer data may reside in a CRM, sales data in an ERP, and marketing data in yet another platform. When companies need a unified view of their operations, data warehousing becomes a necessity.

Operational reports from systems like SAP or Salesforce can work for a while, but eventually, businesses realize they need to consolidate data from various sources to make better strategic decisions.

Regulatory and Compliance Requirements

In some industries, regulatory requirements mandate specific reporting and auditing capabilities. A cleaning service provider, for instance, may need to generate reports on the number of windows cleaned to comply with contract terms. Failure to provide such reports can result in financial penalties.

In such cases, investing in data warehousing can be justified by calculating the potential financial losses due to non-compliance. Businesses in regulated industries, such as finance and healthcare, often prioritize data warehousing earlier for compliance reasons.

External Data and Decision-Making

Some businesses receive vast amounts of external data from suppliers, partners, or customers. If decisions rely on analyzing large datasets, a data warehouse can streamline operations, improve accuracy, and reduce manual effort.

For example, a company relying on Excel sheets for pricing decisions may struggle to track and compare data efficiently. A data warehouse helps aggregate, cleanse, and analyze data, enabling better decision-making.

Cost vs. Benefit Analysis

While budget constraints may delay investment in data warehousing, businesses should assess the cost of inefficiencies. If manual data handling, poor integration, or compliance risks cost the company significant time and money, the investment in a data warehouse is often justified.

Final Thoughts

Investing in data warehousing is not solely about company size but rather about business needs and data complexity. Companies facing data-driven challenges, compliance requirements, or inefficiencies in data management should consider investing in a data warehouse—regardless of their size.

Watch the Video

PII Business Keys

In modern data architecture, handling Personally Identifiable Information (PII) is a crucial aspect of maintaining data privacy and integrity. One common challenge in Data Vault modeling is determining how to properly load artificial hubs when using PII fields as business keys. In this article, we explore different approaches and best practices to handle this scenario effectively.

Understanding the Loading Process for Artificial Hubs

When an artificial hub is created using a PII-based business key, a key question arises: should we load one UUID per person, or should we generate multiple UUIDs for each version or change? Additionally, how do we manage this in the ETL process?

Solution 1: Using the Technical ID as the Business Key

One approach is to integrate data using the technical ID (e.g., employee ID from a CRM system) instead of the PII-based business key. This method ensures that the actual PII data remains in the satellite, making deletions easier while maintaining integrity in data integration.

Solution 2: Storing Both Technical and Business Keys in the Hub

Another option is to load both the technical ID and the PII-based business key into the same hub. A same-as-link can be used to create a mapping between the two, allowing flexibility in identifying records while ensuring that the satellites reference the technical ID for consistency.

Solution 3: Separating Technical and Business Keys into Two Hubs

For greater flexibility, technical IDs and business keys can be stored in separate hubs with a linking mechanism. While this approach introduces additional complexity, it provides a structured way to manage mappings between different keys while keeping PII data separate.

Managing UUIDs in the ETL Process

If the source system does not provide a technical ID, an artificial UUID must be generated. This requires maintaining a lookup table in the staging layer to map each business key to a UUID. This mapping must be handled efficiently in the ETL process to ensure consistency across data loads.

Handling PII Deletions

When a delete request is received, it is essential to remove PII data while preserving relationships in the data model. Using the technical ID ensures that descriptive information remains, while direct identifiers are removed. Additionally, solutions like Delta Lake or Iceberg tables can help manage deletions effectively in a data lake environment.

Conclusion

Choosing the right approach for handling PII-based business keys depends on the specific use case and integration requirements. Using a technical ID simplifies integration but may not always be feasible. The same-as-link approach provides a balanced solution, while separate hubs offer greater flexibility at the cost of added complexity. Ultimately, a well-structured ETL process is key to ensuring data integrity and compliance with privacy regulations.

Watch the Video