Modelling Salesforce History Tables

Salesforce tracks changes to configured attributes by storing them in history tables. This data, which includes record ID, field name, old and new values, and timestamps, presents a unique challenge for Data Vault modeling. In this article, we’ll explore an optimal way to model this data using Data Vault principles.

Understanding Salesforce History Tables

Salesforce allows tracking of specific attribute changes within objects like Contacts. These changes are stored in history tables such as ContactHistory. Each entry logs:

• Record ID (e.g., Contact ID)
• Field Name
• Old Value
• New Value
• Timestamp

Challenges in Modeling Salesforce History Data

When designing a Data Vault model for this history data, there are key challenges to consider:

• Handling multiple changes for the same record within a short time frame
• Maintaining referential integrity
• Efficiently querying and pivoting data for reporting

Approach: Multi-Active Satellite

A common initial approach is to model the history table as a multi-active satellite attached to a Contact Hub, with the field name as the dependent key. However, this approach has pitfalls:

• Duplicates can arise if multiple changes occur for the same field in the same batch
• Timestamp-based keys are unreliable due to possible duplicate timestamps

To counter this, a unique sequence number should be assigned in the staging area and used as a dependent key.

Optimized Approach: Non-Historized Link

Instead of a multi-active satellite, a non-historized link can be used to model Salesforce history data more efficiently. Here’s how it works:

• Create a non-historized link connecting the Contact and User hubs.
• Store change-related attributes (field name, old value, new value, timestamp) directly within this link.
• Use the timestamp as an event-based attribute rather than part of the primary key.

This approach avoids the need for complex joins and simplifies querying.

Efficient Data Retrieval: Pivoting

Since history tables are structured in a key-value format, queries often require pivoting. By using database pivot functions, we can restructure the data into a more usable format for reporting without excessive joins.

Alternative Consideration: JSON Storage

Another approach is to store change data as a JSON object in a standard satellite. This method offers flexibility, particularly when dealing with a large number of attributes. However, it complicates querying and should be used only when necessary.

Conclusion

For most cases, a non-historized link is the optimal way to model Salesforce history tables in Data Vault. It simplifies data storage, reduces the need for extensive joins, and enhances query performance. Multi-active satellites are an alternative but require careful handling of duplicate timestamps and field changes.

Watch the Video

Business Use Cases in Data Vault

In the world of data management and integration, businesses face many challenges. Data Vault is a methodology designed to address these challenges, offering a flexible and scalable solution for integrating and managing data across an enterprise. But when is it the right time to use Data Vault? Are there specific business scenarios where Data Vault’s power truly shines? This article explores the core benefits of Data Vault, its use cases, and how it can solve complex data integration problems.

Understanding the Pain Points

Before diving into when and where Data Vault is most beneficial, it’s important to understand the underlying pain points businesses face in their data management processes. According to Michael Olschimke, CEO of Scalefree, understanding the business pain points is crucial. If there is no significant problem, there may be no need for a new solution like Data Vault. The key is identifying situations where current methods fall short in handling data integration, privacy regulations, and evolving business rules.

The most common pain point is the challenge of data integration. Modern businesses typically operate with data spread across multiple sources, from internal systems to external data feeds. Integrating these data sources into a single, unified view is one of the biggest challenges. Whether you’re trying to generate reports, create dashboards, or analyze data for business insights, you need a consistent and reliable method to integrate data from diverse systems. This is where Data Vault excels.

The Core Strength of Data Vault: Data Integration

Data Vault is designed specifically for situations where integration is a priority. If a business needs to bring together multiple disparate data sources into a single framework for reporting, Data Vault offers a robust solution. Its flexibility allows businesses to combine data from different systems, apply various business logic, and present the data in a meaningful way.

In contrast to other methods, Data Vault shines when the data integration needs are complex. Simply dumping data into a data lake may seem like an easy solution, but it leaves businesses with the challenge of how to integrate these disparate datasets into a cohesive model. Without a clear method for integration, data lakes become isolated silos of information, and producing integrated reports becomes a significant challenge.

Addressing Regulatory Compliance and Privacy

In today’s data-driven world, businesses must also address regulatory requirements such as GDPR. One of the strengths of Data Vault is its built-in support for privacy and security regulations. When managing sensitive data, businesses need to ensure compliance with privacy regulations, including the ability to delete or anonymize personal data when necessary.

While other methods can also address regulatory concerns, Data Vault provides out-of-the-box patterns and solutions that are easy to implement and scale. For example, Data Vault allows businesses to securely store data, apply business rules, and remove or anonymize personal attributes without disrupting the overall data structure. This capability is crucial in today’s regulatory environment, where compliance is not just a best practice but a legal requirement.

Handling Changing Business Rules Over Time

Another key use case for Data Vault arises when businesses face changing business rules. Over time, companies evolve, and with this evolution comes changes in how data is processed and interpreted. For example, a business might need to apply different versions of a business rule to historical and current data, depending on when the rule was in effect.

Data Vault provides a solution to this challenge by separating the data transformation processes and storing them in the “business vault.” This separation allows businesses to apply different versions of business rules to different datasets. For instance, you might apply one rule to data from the previous year and a different rule to the current year’s data. This flexibility allows companies to adapt to new business requirements without overhauling their data architecture every time the rules change.

Scalability and Flexibility

As businesses grow and their data needs become more complex, the scalability of their data management solutions becomes critical. Data Vault is highly scalable because it allows companies to add new data sources, apply new business rules, and adjust their data models as needed without requiring a complete redesign of their data infrastructure.

One of the most powerful features of Data Vault is its ability to “creatively destruct” incoming data. This means that data from different source systems can be broken down into fundamental components—such as business keys, relationships, and descriptive data. These components can then be recombined in any format or structure that suits the business’s reporting or analytical needs, whether that’s a star schema, flat tables, or any other target structure. This flexibility ensures that businesses can meet various use cases and reporting requirements using the same data platform.

Data Vault and Business Intelligence

In the realm of business intelligence (BI), Data Vault stands out as an effective method for managing large, complex datasets. It offers businesses the ability to handle multiple use cases, such as generating reports, analyzing trends, and forecasting future performance. Because it integrates data from multiple sources, it provides a single, reliable source of truth for reporting and analysis.

Unlike traditional BI systems, which often require multiple data platforms or complex ETL (extract, transform, load) processes, Data Vault allows businesses to use a single platform for all their BI needs. Whether you’re running operational reports, building data marts, or creating advanced analytics models, Data Vault’s flexibility ensures that businesses can handle various BI scenarios without the need for separate systems or tools.

Addressing Complex Data Models

While Data Vault is highly flexible, it can also become more complex as businesses face increasingly complex data models. The complexity arises when businesses deal with dirty data, unclear business key definitions, or overlapping data from different source systems. In these situations, Data Vault allows companies to address these challenges by adding new components to their data models, such as hubs, links, and satellites.

For instance, if a business has two different source systems with different business key definitions, Data Vault can create a new hub to store these keys and establish relationships between them. Similarly, when data quality is an issue, Data Vault allows businesses to add computed satellites to clean the data before it’s used for reporting or analysis. While these additional components can increase the complexity of the data model, they are essential for solving the challenges presented by messy, inconsistent, or incomplete data.

When Should You Use Data Vault?

Ultimately, the decision to use Data Vault depends on your business’s data requirements. If your data integration needs are relatively simple, or if you don’t have stringent privacy or regulatory requirements, other solutions might suffice. However, for businesses dealing with complex datasets, evolving business rules, and compliance challenges, Data Vault provides a comprehensive, scalable solution that addresses all these needs.

When evaluating whether Data Vault is the right choice for your organization, it’s essential to assess your current and future data needs. If you require robust data integration, the ability to apply different business rules over time, and compliance with privacy regulations, Data Vault is a powerful tool that can handle these challenges. Its flexibility and scalability ensure that it can grow with your business as your data needs evolve.

Conclusion

Data Vault is a powerful methodology for businesses that need to integrate complex data from multiple sources, apply evolving business rules, and comply with privacy regulations. While it may not be necessary for every business, for those facing challenges in these areas, Data Vault offers a robust, flexible, and scalable solution. By breaking down and restructuring data in a way that supports various reporting and analytical needs, Data Vault ensures businesses can keep up with the ever-changing demands of today’s data-driven world.

Watch the Video

Understanding GDPR in the Context of Data Vault

GDPR compliance is a critical concern for organizations handling personal data. Data Vault, a well-structured data modeling approach, offers a robust solution for meeting GDPR requirements, particularly in two key areas: data security and data privacy.

Data Security in Data Vault

Data security involves protecting existing data from unauthorized access. Data Vault supports this through two levels of security:

• Row-Level Security: This ensures that users can only access records relevant to them. It can be implemented via database row-level security features or view layers.
• Column-Level Security: Attributes are separated based on security classification. Each classification is stored in a separate Satellite, with access granted accordingly.

By controlling access at both row and column levels, organizations can ensure compliance with GDPR’s data access requirements.

Data Privacy and Deletion in Data Vault

Data privacy focuses on removing personal data when required. Data Vault’s design allows for the physical deletion of personal data without affecting the integrity of the entire dataset. This is achieved through:

• Satellite Splitting: Personal and non-personal data are stored in separate Satellites. When a deletion request is made, only the personal data Satellite needs to be altered.
• Data Retention Policies: Different personal data attributes may have varying retention periods. Separate Satellites are created for attributes that must be deleted at different times.
• Point-in-Time (PIT) Table Updates: When personal data is deleted, PIT tables are rebuilt to reflect the absence of that data.

This approach ensures that deleted data is no longer accessible or retrievable, aligning with GDPR’s right to be forgotten.

Access Control Lists (ACL) in Data Vault

Managing user access to data is another essential aspect of GDPR compliance. Data Vault facilitates this through an ACL system modeled using Hubs and Links:

• A User Hub stores information about individual users.
• A User Group Hub categorizes users into groups with shared permissions.
• A Customer Hub and Bank Account Hub manage customer and account details.
• A Link connects users, user groups, and customers.
• An Effectivity Satellite records the time periods during which users have access to specific data.

By applying this structure, access control can be managed dynamically, ensuring that only authorized users can view or modify data.

Security vs. Privacy: A Crucial Distinction

When discussing GDPR, it’s essential to distinguish between security and privacy:

• Security: The data remains in the system, but access is restricted based on security policies.
• Privacy: The data is physically removed from the system when no longer needed.

Organizations should ensure that security officers and privacy officers handle these concerns separately to avoid misconceptions, such as assuming filtered data is deleted when it is still present in the database.

Conclusion

Data Vault provides a comprehensive approach to managing GDPR requirements through built-in security and privacy mechanisms. By structuring data appropriately and implementing proper access control and deletion strategies, organizations can achieve GDPR compliance efficiently.

Watch the Video

How to Explaining Data Vault

When introducing Data Vault to business users, it’s important to communicate its value in a way that resonates with them. Instead of focusing on the technical details, it’s best to highlight the benefits and business impact.

Understanding the Audience

Business users, including executives and commercial leaders, generally don’t need to understand the intricacies of Data Vault. They are more interested in the outcomes, such as data accessibility, security, and adaptability.

Explaining Data Vault with a Simple Analogy

Imagine you are building a house. As a homeowner, you don’t need to know every construction detail; you just want to ensure that it’s solid, safe, and has all the necessary features. Similarly, business leaders don’t need to understand the technical framework of Data Vault—they just want a reliable data management system that supports their decision-making.

Focusing on Business Value

Instead of using the term “Data Vault,” it’s often more effective to discuss the advantages of a managed data platform:

• Data Security & Privacy: Ensures compliance with regulations like GDPR while securing sensitive information.
• Data Integration: Consolidates structured, semi-structured, and unstructured data from multiple sources.
• Auditability & Transparency: Provides full data lineage, ensuring that every data point can be traced back to its source.
• Agile Data Delivery: Enables incremental delivery of insights, so business teams don’t have to wait months or years to see results.
• Adaptability to Change: Easily adjusts to changes in source systems, business logic, and reporting needs.
• Handling Multiple Business Timelines: Supports complex business requirements, including postdating and backdating of records.
• Scalability: Handles large datasets and high-speed data processing.

Delivering Business Outcomes

Business leaders care about measurable outcomes. With a Data Vault-based platform, they can expect:

• Improved decision-making with accurate, timely data.
• Faster adaptation to market changes and customer demands.
• Cost efficiency through a structured yet flexible data architecture.
• Enhanced reporting and analytics with reliable data sources.

Making the Pitch to Business Users

When discussing Data Vault with executives, avoid technical jargon and focus on business goals. Instead of saying, “We use Data Vault 2.0 for data modeling,” say, “We have a data platform that ensures secure, auditable, and easily accessible insights to drive your business forward.”

By emphasizing real-world benefits, you can effectively communicate the value of Data Vault without overwhelming non-technical stakeholders with complexity.

Conclusion

Communicating the benefits of Data Vault to business users requires a shift from technical explanations to business value discussions. By framing the conversation around security, agility, and data-driven decision-making, you can successfully gain buy-in from stakeholders and demonstrate the impact of a well-managed data platform.

Watch the Video

Why use Data Vault 2.0 to Tackle GDPR?

Today, we explore how Data Vault 2.0 can be a powerful tool for addressing the challenges posed by the General Data Protection Regulation (GDPR). GDPR requires organizations to protect the personal data of European citizens and grants individuals the “right to be forgotten”. This article outlines how Data Vault 2.0 can simplify compliance with GDPR while maintaining the integrity of your data warehouse.

Understanding GDPR and its Challenges

GDPR, implemented in 2018 by the European Union, sets strict rules for handling personal data. One key aspect is the right to be forgotten, allowing individuals to request the deletion of their personal information from an organization’s systems. For data warehousing and analytics, this can be particularly challenging as organizations often need to retain some data for analytical purposes while complying with GDPR’s deletion requirements.

The Data Vault 2.0 Approach to GDPR

Data Vault 2.0 provides a structured way to tackle GDPR compliance through its unique data modeling techniques. At its core, Data Vault separates data into three main components: Hubs, Links, and Satellites. Satellites are used to store descriptive attributes of business keys, and with GDPR, we can utilize a method called Satellite Splits to manage personal and non-personal data effectively.

Satellite Splits

Satellite splits involve creating separate Satellites for personal and non-personal data. For example:

• Personal Satellite: Contains personal information such as names, addresses, and email addresses. This data must be deleted if a customer exercises their right to be forgotten.
• Non-Personal Satellite: Stores non-identifiable data such as regions or generated technical data, which can be retained for analytics even after personal data is removed.

When a deletion request is received, you can simply delete the records from the Personal Satellite while retaining the non-personal data for analytical use. This ensures compliance with GDPR while preserving valuable business insights.

Addressing Privacy-Relevant Business Keys

One of the challenges with GDPR is managing business keys that are tied to personal data, such as social security numbers. If such keys are used in Hubs, deleting personal data becomes complicated. Here’s how Data Vault 2.0 handles this:

Using Artificial Hubs

To avoid using personal attributes as business keys, Data Vault 2.0 introduces artificial Hubs. These Hubs assign unique, non-identifiable numbers to replace personal identifiers. For example:

• An artificial Hub might contain a generated number for each customer’s car insurance data.
• A Link connects the artificial Hub to the personal data stored in a Satellite.

When a customer requests deletion, you delete the connection between the personal identifier and the artificial number in the Link. The artificial Hub remains intact, allowing you to retain non-personal data for analytics without risking re-identification.

Best Practices for Implementing GDPR with Data Vault 2.0

• Avoid Personal Identifiers as Business Keys: Always opt for non-personal or artificial identifiers wherever possible to simplify the model.
• Use Randomized Identifiers: Generate UUIDs or random sequence numbers to prevent reverse-engineering personal data.
• Collaborate with Legal Teams: Work closely with legal experts to define which data can be retained and which must be deleted under GDPR.

By adhering to these practices, organizations can create a robust Data Vault model that simplifies GDPR compliance while maintaining data integrity and analytics capabilities.

Conclusion

Data Vault 2.0 offers a flexible and efficient approach to tackling GDPR challenges. By leveraging Satellite splits and artificial Hubs, organizations can balance regulatory compliance with business needs. While managing GDPR compliance may seem complex at first, the structured approach of Data Vault 2.0 ensures that your data remains both secure and useful.

For further learning, join the Data Vault Innovators Community or participate in Data Vault Fridays hosted by Scalefree. These resources provide valuable insights and opportunities to explore topics like GDPR, data warehousing, and more.

Watch the Video