Watch the Video

Navigating GDPR Compliance in Data Warehousing

In today’s digital age, GDPR compliance is a crucial aspect for any organization dealing with personal data. With the rise of data warehousing and advanced modeling solutions like Data Vault 2.0 (DV 2.0), questions often arise about how to handle Personally Identifiable Information (PII) within these frameworks. This article addresses some common concerns and provides practical recommendations for ensuring GDPR compliance in data warehouses.

Understanding the Challenge

GDPR mandates that personal data must be handled with the utmost care, ensuring individuals’ privacy and security. In the context of data warehousing, this often translates to managing business keys that might contain PII. Let’s dive into some specific questions raised around this topic:

1. How should activity history be managed when the main hub contains a PII business key?
2. Is it best practice to use hashed business keys in link tables to improve load performance?
3. Should artificial keys originate from each business domain, and how should they be managed if not?

Question #1: Managing Activity History with PII Business Keys

The Problem

In a typical data warehouse model, customer records might include PII, such as social security numbers or tax IDs. According to GDPR, it’s crucial that activity history is not traceable back to the individual once they exercise their right to be forgotten.

The Solution

One effective approach is to split descriptive attributes into different satellites—one for personal data and another for non-personal data. This way, when a deletion request is made, only the personal satellite needs to be purged. The non-personal satellite can retain anonymized data, maintaining the integrity of the dataset while ensuring compliance.

Question #2: Using Hashed Business Keys in Link Tables

The Problem

Hashing business keys is often recommended in DV 2.0 to improve load performance. However, directly using business keys in link tables can pose a challenge, especially when those keys contain PII.

The Solution

In DV 2.0, it’s a standard practice to use hashed values of business key components rather than the business keys themselves. This approach ensures better performance and security. Here’s how it works:

1. Hash the Business Key: Use a cryptographic hash function (e.g., SHA-256) to convert the business key into a hashed key.
2. Use Hashed Keys in Link Tables: The hashed key then serves as the foreign key in link tables, ensuring that PII is not directly exposed.

Question #3: Originating and Managing Artificial Keys

The Problem

There’s a debate on whether artificial keys should be generated within each business domain or within the data warehouse itself. This raises concerns about consistency and management, especially if the artificial key must be derived from PII.

The Solution

Artificial keys should ideally be generated within the data warehouse to maintain consistency and control. Here’s the process:

1. Generate a UUID: Use a universally unique identifier (UUID) for the artificial key. This ensures randomness and reduces the risk of duplication.
2. Link Artificial Keys to Business Keys: Establish a relationship between the artificial key and the business key within the data warehouse, ensuring that the artificial key is never exposed in operational systems.

Handling Scenarios Without Artificial Keys

If generating artificial keys within the data warehouse is not feasible, the data warehouse should still generate these keys upon ingestion. This method ensures that all keys are managed consistently and securely.

Ensuring Compliance and Security

Satellite Splitting

By splitting satellites into personal and non-personal data, organizations can easily manage deletion requests without compromising data integrity.

Cryptographic Hashing

Utilizing cryptographic hashing for business keys in link tables enhances both security and performance, crucial for maintaining GDPR compliance.

Artificial Keys Management

Generating artificial keys within the data warehouse ensures consistency and security, reducing the risk of PII exposure.

Regular Audits and Legal Consultation

Regular audits and consultations with legal experts ensure ongoing compliance with GDPR and other regulations. Implementing these practices helps organizations stay ahead of potential compliance issues.

Conclusion

Handling PII in data warehouses requires careful planning and robust solutions. By implementing satellite splitting, cryptographic hashing, and consistent artificial key management, organizations can ensure GDPR compliance while maintaining data integrity and performance. Regular audits and legal advice further bolster these practices, ensuring that data handling processes remain secure and compliant with evolving regulations.

Watch the Video

Managing Data Vault Performance with Incremental Changes and Deletions

In the world of data warehousing, the Data Vault methodology has emerged as a robust and scalable solution for managing vast amounts of data. However, one common concern among practitioners is how to efficiently handle incremental changes and deletions, particularly when dealing with structures containing billions of rows. This article aims to elucidate the process, focusing on the questions around loading structures, performance considerations, and practical strategies for maintaining efficiency.

Understanding the Basics: Tracking Changes and Deletions

The core principle of Data Vault involves capturing all changes and deletions incrementally. This ensures that the data warehouse remains an accurate historical record of the enterprise’s data. Here’s a simplified illustration of how this can be achieved:

1. Initial Load: When a new transaction is recorded, it is inserted into the Data Vault as a new record. For instance, if customer A purchases product C at store B on day one, this transaction is recorded with a value of €7.
2. Handling Updates: If the value of the transaction changes from €7 to €5 on day two, instead of updating the existing record, two new records are created: one to nullify the original transaction (-€7) and another to represent the new transaction (€5).
3. Dealing with Deletions: If a transaction is deleted, it is handled similarly by inserting a record that nullifies the original transaction.

This method ensures that the Data Vault remains immutable, as records are never directly altered once inserted. Instead, changes are tracked by adding new records, which simplifies loading processes and maintains data integrity.

Loading Structures: The Practical Approach

Loading structures in Data Vault can be challenging, especially when dealing with large datasets. Here are some practical strategies:

Using CDC (Change Data Capture)

If the source system supports CDC, this is the most straightforward method:

• Insert New Records: Directly insert new records into the target system.
• Handle Updates and Deletes: For updates and deletes, insert the corresponding counter transactions.

CDC provides a clear and efficient way to track changes and deletions, significantly simplifying the loading process.

Full Load vs. Incremental Load

In scenarios where full loads are used (though rare for very large datasets), the process involves:

• Identifying New Records: Select records from the staging area that do not exist in the target and insert them with a counter of one.
• Identifying Deletions: Select records from the target that do not exist in the staging area and insert counter transactions to nullify them.

While full loads can be intensive, they can be managed effectively by optimizing the identification of new and deleted records.

Performance Considerations

Handling billions of rows requires careful planning to avoid performance bottlenecks. Here are some strategies to mitigate performance issues:

Parallel Processing

By running multiple processes in parallel, you can significantly speed up the loading process. For example, separate processes can handle inserts and counter transactions concurrently.

Hash Keys and Indexes

Using hash keys and indexes efficiently can reduce the time needed to check for existing records. Ensure that your hash keys include all relevant business keys and transaction IDs to maintain uniqueness.

High-Water Marks and System Indicators

Some systems, like Oracle, offer features like SCN (System Change Number) or row versions that can help identify modified records. Using these indicators can reduce the amount of data processed by focusing only on recently changed records.

Practical Example: Incremental Loading without CDC

In cases where CDC is not available, you can still achieve efficient incremental loading:

1. Incremental Updates from Source: If the source system provides daily increments (inserted and updated records), use this data to update the target.
2. Handling Deletions: For deleted records, you might need an additional table or mechanism to track deletions. If such a table is available, use it to insert counter transactions.
3. Full Load Approach: If only full loads are available, implement a two-step process to identify and handle new, updated, and deleted records.

Conclusion

Managing incremental changes and deletions in Data Vault structures, especially for large datasets, requires a combination of strategies tailored to the specific capabilities of your source systems. Whether using CDC, full loads, or incremental updates, the goal remains the same: to maintain an accurate and efficient data warehouse. By understanding the principles and applying practical solutions, you can handle the complexities of Data Vault performance effectively.

Remember, the key to success lies in thorough planning, efficient use of system capabilities, and continuous optimization of your data loading processes. By following these guidelines, you can ensure that your Data Vault implementation scales efficiently, even as your data volumes grow.

Watch the Video

Exploring Data Vault 2.0: Managing Hashing Costs in Smaller Environments

In the evolving landscape of data management, Data Vault 2.0 stands out as a robust methodology designed for scalability, flexibility, and consistency across diverse technological environments. A crucial component of Data Vault 2.0 is the use of hashing for business keys (BKs) and hash diffs. Hashing ensures data integrity and efficiency, especially in distributed systems. However, the performance costs associated with hashing can sometimes become a significant concern. This blog post delves into the nuances of hashing in Data Vault 2.0, the trade-offs involved, and when it might be feasible to deviate from the standard approach.

The Role of Hashing in Data Vault 2.0

Data Vault 2.0 leverages hashing to create unique, consistent identifiers for business keys and to detect changes in data efficiently. This method is technologically agnostic, meaning it can be implemented across various databases and data platforms, whether on-premises or in the cloud. The primary advantages of hashing include:

1. Consistency Across Systems: Hashing ensures that business keys are consistent and unique across different systems and regions.
2. Improved Query Performance: Pre-calculating hash diffs can make query execution faster and more efficient, transferring the computational load from query time to data loading time.
3. Simplified Data Integration: Hash keys provide a straightforward way to manage and integrate data from multiple sources, reducing the complexity of data joins.

Challenges of Hashing

Despite its benefits, hashing can introduce performance challenges, particularly in the following scenarios:

1. Wide Tables: Calculating hash diffs for tables with a large number of columns can be computationally intensive.
2. Complex Hash Functions: Ensuring that hash functions generate unique strings can be complex and resource-heavy.
3. Hardware Limitations: On-premises environments with limited hardware capabilities might struggle with the additional computational load required for hashing.

Evaluating Hashing Alternatives

When faced with performance concerns, particularly in smaller, local solutions, it’s essential to consider whether deviating from the standard hashing approach would be beneficial. There are three primary options to consider:

1. Hash Keys: The default and recommended option for most environments, especially those involving distributed systems or diverse technologies.
2. Sequences: A legacy approach from Data Vault 1.0 that uses sequential numbers as identifiers.
3. Business Keys: Using the original business keys directly as identifiers.

The Case Against Sequences

Sequences, although a viable option, are generally not recommended in modern Data Vault implementations due to several drawbacks:

• Lookup Overhead: Sequences require lookups during data loading, which can slow down the process significantly.
• Orchestration Complexity: Managing sequences adds complexity to the loading process, particularly in real-time scenarios.
• Distributed System Challenges: Sequences do not perform well in distributed environments where parts of the solution might reside in different locations (e.g., cloud and on-premises).

Hash Keys vs. Business Keys

When deciding between hash keys and business keys, the choice largely depends on the specific technology stack and the environment. Here are some considerations:

Hash Keys

• Pros: Provide a consistent, fixed-length identifier that simplifies joins and queries across various systems. They are particularly beneficial in mixed environments.
• Cons: Slightly higher computational cost during data loading compared to sequences. However, the consistent performance across queries often outweighs this drawback.

Business Keys

• Pros: Directly using business keys can simplify the architecture in environments where the data platform supports efficient handling of these keys.
• Cons: Can lead to complex and less efficient joins, especially in mixed or distributed environments.

Performance Optimization Strategies for Hashing

For environments where hashing performance is a concern, several optimization strategies can be employed:

1. Leverage Hardware Acceleration: On-premises environments can benefit from hardware acceleration, such as PCIe express cards with crypto chips, to offload hash computation from the CPU.
2. Utilize Optimized Libraries: Many platforms use highly optimized libraries (e.g., OpenSSL) for hash computations, which can significantly improve performance.
3. Incremental Loads: Ensure that performance evaluations consider multiple load cycles to capture the benefits of hash diffs during delta checks, not just initial loads.

Future Trends and Recommendations

Looking forward, the evolution of data platforms and technologies might shift the balance towards using business keys more frequently. As Massively Parallel Processing (MPP) databases become more prevalent, their native support for efficient key management could make business keys a more attractive option. However, until such technologies are ubiquitous, the default recommendation remains to use hash keys for their broad compatibility and consistent performance.

Conclusion

Data Vault 2.0’s approach to hashing business keys and hash diffs provides significant advantages in terms of consistency, scalability, and performance. While the performance costs of hashing can be a concern, particularly in smaller environments with limited hardware, careful consideration of the available options and optimization strategies can mitigate these issues. Ultimately, the decision should be guided by the specific technological context and future-proofing considerations.

For most scenarios, hash keys remain the recommended approach due to their versatility and robustness in mixed and distributed environments. However, as technology evolves, the use of business keys might become more feasible, highlighting the importance of staying informed about the latest trends and advancements in data management.

Watch the Video

In our ongoing series, our CEO Michael Olschimke addresses a complex question from the audience regarding the use of Multi Active Satellites (MAS) on Links within a Data Vault 2.0 model. This topic touches on advanced aspects of data modeling, particularly in the context of handling multiple active records.

The question posed was, “Can the Multi Active Satellites be used on LINKs too (considering that on Link we have the option of using the child dependent key)? Please ignore the fact that the link doesn’t have a Hash column on all HUB keys.” Michael’s response delves into the practical application of MAS on Links, an area that can greatly enhance the flexibility and scalability of data models. He explains that while traditionally Multi Active Satellites are used with Hubs to track multiple active records, their application on Links is feasible and beneficial. By leveraging the child dependent key, it is possible to maintain multiple active relationships between entities, which is particularly useful in scenarios where relationships are dynamic and subject to frequent changes.

Drawing on his 15 years of experience in Information Technology, with a focus on Business Intelligence over the past eight years, Michael offers a nuanced perspective on this topic. He highlights that while the absence of a hash column on all HUB keys might pose a challenge, it can be mitigated through careful design and implementation strategies. By ensuring that each Link is adequately documented and structured, organizations can effectively use MAS to capture the complexity of real-world relationships without sacrificing data integrity or performance.

In conclusion, Michael emphasizes the importance of flexibility and adaptability in data modeling. Implementing Multi-Active Satellites on Links can provide significant advantages in managing complex data relationships, allowing for more granular and accurate data analysis. This approach aligns with best practices in Data Vault 2.0 and supports the goal of creating robust, scalable, and responsive data architectures. Michael encourages practitioners to challenge conventional boundaries and explore innovative solutions to meet their unique data management needs.

Watch the Video

In our ongoing series, our BI Consultant Lorenz Kindling addresses a question from the audience about managing soft deletes in an insert-only data environment. This topic is particularly relevant for those in the field of data warehousing, where maintaining historical data integrity and accuracy is paramount.

The question posed was, “How to track soft deletes with insert only?” Lorenz’s response explores the complexities and best practices for implementing soft deletes within an insert-only framework. He explains that soft deletes involve marking records as inactive rather than physically removing them from the database. This approach is crucial for maintaining a comprehensive historical record and ensuring that data integrity is not compromised. Lorenz suggests using a specific status indicator or a flag within the data model to denote records that are logically deleted. This allows for efficient querying and reporting without the risk of losing historical data.

Lorenz, who has been advising renowned companies since 2021 at Scalefree International, draws on his extensive experience in Business Intelligence and Enterprise Data Warehousing to provide practical insights. He emphasizes that by carefully planning and implementing a robust soft delete mechanism, organizations can achieve a balance between data retention and performance. Lorenz’s approach ensures that data warehouses remain both scalable and efficient, even as they grow and evolve over time.

In conclusion, Lorenz highlights the importance of adopting best practices in data warehouse automation and Data Vault modeling to manage soft deletes effectively. By using insert-only methods with proper indicators for soft deletes, organizations can maintain the integrity and usability of their data warehouses, thereby supporting long-term business intelligence and analytics goals. This strategy not only addresses common data warehousing challenges but also aligns with modern data management principles.

Watch the Video

In our ongoing series, our CEO Michael Olschimke addresses a question from the audience about modelling daily exchange rates within the Data Vault framework for a non-banking industry. The query highlights a common challenge faced by many organizations: integrating and managing exchange rate data effectively.

The question posed was, “How would you model daily exchange rates in Data Vault 2.0 for a non-banking industry? We are already using a reference table for the list of currencies (I guess we would have currency as a hub in the banking industry, but that is not our case). Now we also need daily exchange rates for currency conversions in the datamart layer. I would start with a Link for exchange rates, but do we need to create a hub for currencies? How about existing references to currency in the existing model (currently in SAT, because we have currency as a reference table)?”

Michael’s response delves into the intricacies of data modelling in such scenarios. He suggests that even though your industry is non-banking, establishing a structured and scalable way to manage exchange rate data is crucial. Using a Link for exchange rates is a good starting point, but creating a hub for currencies could provide additional benefits. This hub would act as a central repository for all currency-related information, ensuring consistency and ease of access across different layers of the data architecture. Additionally, integrating existing references to currencies within the model can streamline operations and enhance the accuracy of financial data analytics.

In conclusion, Michael emphasizes the importance of a well-thought-out data architecture. By creating a dedicated hub for currencies and effectively linking exchange rate data, organizations can ensure more accurate and efficient currency conversions in their datamart layer. This approach not only aligns with best practices in data vault modeling but also supports the broader goal of maintaining data integrity and usability across the enterprise.

Watch the Video

In our latest video, BI Consultant Julian Brunner tackles a pressing query: “Where to implement data quality techniques? Is it possible to clean dirty data at the entry point into the raw data vault?” Data quality is foundational for informed decision-making, and Julian’s expertise shines as he navigates this critical terrain.

Julian highlights the importance of a holistic approach to data quality management, emphasizing the need for robust frameworks spanning the entire data lifecycle. Whether it’s validation rules, data profiling, or cleansing algorithms, proactive measures at every stage can fortify data integrity.