Make Data Warehousing great again!

Author: albertgd

All objects (advanced level) of Data Vault 2.0

In Data Vault 2.0, there are several types of advanced objects that can be used to model and manage data in an enterprise data warehouse. These include:

Basic objects

  1. Hubs: A hub is a central point in the Data Vault model that represents a unique business entity. Hubs are used to store the unique business keys and attributes of an entity, and they act as the central reference point for all related satellite and link objects.
  2. Satellites: A satellite is a supporting object in the Data Vault model that stores additional attributes and historical data about a business entity represented by a hub. Satellites are used to capture changes in the attributes of an entity over time.
  3. Links: A link is a connecting object in the Data Vault model that represents a relationship between two or more business entities. Links are used to storing the relationships between entities and the attributes that describe those relationships.


Intermediate objects

  1. Bridges: A bridge is an advanced object in the Data Vault model that is used to model many-to-many relationships between entities. Bridges are used to store the relationships between entities and the attributes that describe those relationships in a way that is more flexible and efficient than using multiple links.
  2. Reference Data: Reference data is a type of satellite object that stores data that is used to classify or categorize other data in the Data Vault model. Reference data can be used to store codes, descriptions, and other attributes that are used to describe business entities in the Data Vault model.

    https://www.briansestrada.com/2017/09/data-vault-20-9-modelado-references.html
  3. Link Satellite (LSAT): link satellite is a type of object that is used to store additional attributes and historical data about a link object in the Data Vault model. Link satellites are used to capture changes in the attributes of a link over time, and they are typically used to store data that is specific to the link object and not relevant to the entities that are related by the link.

    Link satellites are implemented as tables in a database, and they are typically composed of three columns: a link identifier, a satellite identifier, and a satellite attribute column. The link identifier is a foreign key that references the link object, and the satellite identifier is a unique identifier for the link satellite. The satellite attribute column is used to store the attributes and data that are specific to the link satellite.

    Link satellites are used in conjunction with hubs, satellites, and links in the Data Vault model. The hub stores the unique business keys and attributes of an entity, the satellite stores additional attributes and historical data about the entity, and the link is used to represent the relationship between the entities. The link satellite is used to store data about the link object itself and the relationship between the entities.

    https://communities.sas.com/t5/SAS-Communities-Library/Using-SAS-DI-Studio-to-Load-a-Data-Vault-Part-II-DV2-0/ta-p/221776
    Image copyright: Anna Brown (https://communities.sas.com/t5/SAS-Communities-Library/Using-SAS-DI-Studio-to-Load-a-Data-Vault-Part-II-DV2-0/ta-p/221776)

Advance objects

  1. Non-historized Links (NHLINK): A non-historized link is a type of link object in the Data Vault model that is used to represent a current or active relationship between two or more business entities. Non-historized links do not store historical data about the relationships between entities, and they are typically used to represent relationships that are expected to remain unchanged over time.

    https://www.scalefree.com/modeling/the-value-of-non-historized-links/
    Image copyright: Scalefree (https://www.scalefree.com/modeling/the-value-of-non-historized-links/)
  2. Point-in-Time (PIT) Tables: A Point-in-Time (PIT) table is a type of table in the Data Vault model that stores historical data about the state of a business entity at a specific point in time. PIT tables are used to capture snapshot data about an entity and can be used to track changes in the attributes of an entity over time.

    Image copyright: KentGraziano (https://vertabelo.com/blog/data-vault-series-the-business-data-vault/)
  3. Same as link (SAL): Same as Link is a type of connecting object that is used to represent a relationship between two or more business entities that are considered to be the same or equivalent. SALs are used to eliminate redundancy and consolidate data in the Data Vault model.

    SALs are typically used when two or more entities represent the same real-world concept or object, but they have different business keys or identifiers. For example, a customer may have multiple accounts with a company, and each account may have a different account number. A SAL could be used to link the different accounts together and indicate that they represent the same customer.


    Image copyright: Michael Olschimke (https://www.sciencedirect.com/topics/computer-science/raw-data-vault)

Did I miss any object? please let me know in the comments section below.

When to use or not use Data Vault

Data Vault is a data modeling technique that is often used when there is a need to store and manage large amounts of data in a data warehouse, and where the data is expected to change frequently or have complex relationships. It is well-suited for situations where the data needs to be accessed and processed in real-time or near real-time, and where the data needs to be maintained in a way that allows for easy tracking of changes over time.

When to use it

Data Vault is also well-suited for projects that require the ability to track the history and evolution of data over time. It provides a robust and flexible framework for storing and managing data, and can be easily adapted to changing business needs.

Scenarios to detect that you need it

  • When the data is expected to change frequently: Data Vault is designed to handle data that is expected to change frequently and can be used to track and store data changes over time.
  • When the data has complex relationships: Data Vault is well-suited for storing and managing data with complex relationships, as it uses a flexible, hub-and-link architecture to store data in a way that preserves these relationships.
  • When the data needs to be accessed and processed in real-time or near real-time: Data Vault is often used in combination with real-time or near real-time ETL processes, which can be useful for scenarios where the data needs to be accessed and processed as it is being generated or updated.
  • When the data needs to be maintained in a way that allows for easy tracking of changes over time: Data Vault is designed to store data in a way that allows for easy tracking of changes over time, which can be useful for scenarios where the data needs to be maintained in a way that allows for easy auditing or analysis.

When you do NOT need it

No no situation

On the other hand, Data Vault may not be the best choice for all projects. It can be more complex and resource-intensive to implement compared to some other data modeling techniques, and may not be the most suitable choice for projects with more limited resources or simpler data structures.

Data Vault may not be the best choice in situations where the data is not expected to change frequently, or where the data relationships are relatively simple. Additionally, Data Vault may not be the most appropriate choice for situations where the data does not need to be accessed and processed in real-time, or where the data volume is relatively small.

Scenarios where you’ll find that you DO NOT need it

  • When the data is not expected to change frequently: If the data is not expected to change frequently, it may be more efficient to use a different data modeling technique that is better suited for static data.
  • When the data has relatively simple relationships: If the data has relatively simple relationships, it may be more efficient to use a different data modeling technique that is better suited for storing and managing simpler data structures.
  • When the data does not need to be accessed and processed in real-time or near real-time: If the data does not need to be accessed and processed in real-time or near real-time, it may be more efficient to use a different data modeling technique that is better suited for batch processing.
  • When the data volume is relatively small: If the data volume is relatively small, it may be more efficient to use a different data modeling technique that is better suited for smaller data sets.

Key factors to identify when evaluating Data Vault as an option

There are a few key factors to consider when determining if Data Vault is the most appropriate data modeling technique for a particular project:

  1. Data volume: Data Vault is typically used to store and manage large amounts of data, and may not be the most efficient choice for smaller data sets.
  2. Data complexity: Data Vault is well-suited for storing and managing data with complex relationships, and may not be the most efficient choice for data with relatively simple relationships.
  3. Data change frequency: Data Vault is designed to handle data that is expected to change frequently, and may not be the most efficient choice for data that is not expected to change frequently.
  4. Real-time or near real-time processing requirements: Data Vault is often used in combination with real-time or near real-time ETL processes, and may not be the most appropriate choice for situations where the data does not need to be accessed and processed in real-time or near real-time.
  5. Data maintenance requirements: Data Vault is designed to store data in a way that allows for easy tracking of changes over time, which may be useful for scenarios where the data needs to be maintained in a way that allows for easy auditing or analysis. If these requirements are not present, a different data modeling technique may be more appropriate.

Conclusion

Overall, the specific choice of data modeling technique will depend on the needs and goals of the project, as well as the resources and constraints of the organization. Careful planning and evaluation of the trade-offs and benefits of different approaches may be necessary in order to determine the most appropriate solution.

Why we need to Hash our IDs in Data Vault 2.0

Hashing is a technique used to transform a piece of data, such as an identifier, into a fixed-size string of characters, known as a hash. The resulting hash is unique to the input data and has a fixed length, making it useful for various purposes, including data storage and retrieval.

In the context of data vault 2.0, hashing is used to obscure the original values of identifiers in order to protect the privacy of individuals or organizations. By replacing sensitive identifiers with hashed values, it becomes more difficult for unauthorized parties to access or use the original data. This can be especially important when dealing with sensitive or personal information, such as social security numbers or financial data.

Hashing will make an ID totally impossible to get any meaning unless we have the connection between the original ID and the hashed ID

Hashing can also be useful for improving the performance of data management systems by allowing for faster searches and comparisons. When data is stored in a hashed format, it can be more efficiently indexed and retrieved, which can help to reduce the time and resources required to access and process the data.

Overall, hashing is an important technique in data management and can be used to protect the privacy and security of data, as well as improve the performance of data management systems.

Advantages of Hashing IDs in DV2.0

There are several advantages to hashing identifiers in data vault 2.0:

  1. Protects privacy: Hashing can help to protect the privacy of individuals or organisations by obscuring sensitive identifiers such as social security numbers or financial data. This can be especially important when dealing with sensitive or personal information.
  2. Improves security: By replacing sensitive identifiers with hashed values, it becomes more difficult for unauthorized parties to access or use the original data. This can help to improve the overall security of the data vault.
  3. Improves performance: Hashing can help to improve the performance of data management systems by allowing for faster searches and comparisons. When data is stored in a hashed format, it can be more efficiently indexed and retrieved, which can help to reduce the time and resources required to access and process the data.
  4. Reduces storage requirements: Hashing can help to reduce the amount of storage space required to store data, as the resulting hash is typically smaller than the original identifier. This can be especially useful when dealing with large datasets.

Overall, hashing is a useful technique in data management and can provide a number of benefits in the context of data vault 2.0.

Best practices when splitting your satellites in Data Vault modelling

In data vault modeling, satellites are used to store historical and contextual data about an entity in a data vault. When splitting satellites, it is important to consider the following best practices:

  1. Use a consistent and logical naming convention: Use a naming convention that is easy to understand and follow. This will help you easily identify and locate the satellites you need.
  2. Keep related data together: Group data that is related or belongs to the same entity in the same satellite. This will make it easier to understand and analyse the data.
  3. Avoid overloading satellites: Avoid adding too much data to a single satellite. If a satellite becomes too large, it can be difficult to manage and maintain.
  4. Use the correct data types: Make sure to use the correct data types for each attribute in the satellite. This will ensure that the data is stored and used efficiently.
  5. Consider data integrity: When splitting satellites, make sure to consider the impact on data integrity. You want to ensure that you do not lose any data or create inconsistencies when splitting the satellites.

By following these best practices, you can ensure that your satellites are organised and maintained in a way that makes it easy to understand and use the data in your data vault.

Criteria to follow when splitting your Satellites

There are a few steps you can follow to split your satellites in data vault modeling:

  1. Identify the reason for splitting: Determine the reason for splitting the satellites. This could be because the satellite has become too large, or because you want to group related data together in a more logical way.
  2. Determine the criteria for splitting: Decide on the criteria for splitting the satellite. This could be based on the type of data being stored, the time period it covers, or any other relevant factors.
  3. Create a new satellite: Create a new satellite for the data that meets the splitting criteria. Make sure to use a consistent and logical naming convention, and to include all relevant attributes in the new satellite.
  4. Migrate the data: Migrate the data from the old satellite to the new satellite. Make sure to carefully check the data to ensure that it has been migrated correctly and that there are no inconsistencies or data loss.
  5. Update any related links: If the satellite being split is linked to other objects in the data vault, make sure to update the links to point to the new satellite.

Avoiding when splitting your Satellites

When splitting satellites in data vault modeling, it is important to avoid the following:

  1. Losing data: Make sure to carefully migrate all data from the old satellite to the new satellite, to ensure that no data is lost during the split.
  2. Creating inconsistencies: Pay attention to the data being migrated, to ensure that it is consistent and that there are no inconsistencies introduced during the split.
  3. Overloading the new satellite: Avoid adding too much data to the new satellite. If a satellite becomes too large, it can be difficult to manage and maintain.
  4. Using an inconsistent naming convention: Make sure to use a consistent and logical naming convention when creating the new satellite. This will help you easily identify and locate the satellite in the future.

By avoiding these pitfalls, you can ensure that the process of splitting your satellites in the data vault is smooth and does not compromise the integrity of your data

Using Variant data type on DV2.0

What is Variant data type?

A variant data type is a data type that can store a range of different data types, including numerical, string, and Boolean values. This allows for greater flexibility in storing data, as the same field can be used to store a variety of different types of data.

In some programming languages, the variant data type is called “dynamic” or “any” data type. It is often used in situations where the type of data being stored is not known in advance, or where the data may change over time.

For example, in a Data Vault modelling scenario, a variant data type could be used to store data from a variety of sources, including databases, flat files, and APIs. This allows the Data Vault to accommodate a wide range of data types and formats, without requiring any transformation or cleansing of the data.

Variant data types on Data Vault 2.0

Using variant data types in Data Vault modelling can provide a number of advantages. Some of these advantages include:

  1. Improved data integrity: By using Variant data types, you can ensure that the data stored in your Data Vault is accurate and consistent, even when the data sources may be inconsistent or incomplete. This helps to ensure the quality and reliability of your data.
  2. Enhanced flexibility: Variant data types allow you to store data flexibly, which can be helpful when dealing with data that may change over time or may be incomplete. This can help to reduce the need for data cleansing or transformation, as the Data Vault can accommodate a wide range of data types and formats.
  3. Improved performance: Using variant data types can help improve your Data Vault’s performance, as the system can more efficiently store and retrieve data. This can be particularly important when working with large volumes of data.
  4. Improved scalability: By using Variant data types, you can more easily scale your Data Vault to accommodate growing volumes of data. This can help to ensure that your Data Vault remains functional and efficient as your data needs change over time.

Overall, using Variant data types in Data Vault modelling can help to improve the accuracy, flexibility, performance, and scalability of your data management system.

Where can I find Variant data types?

Variant data types are supported by a wide range of systems and programming languages. Some examples include:

Databases

  1. Microsoft SQL Server: In SQL Server, the “sql_variant” data type is used to store values of different data types. This data type can store values of any data type, except for “text,” “ntext,” and “image” data types.
  2. MySQL: In MySQL, the “JSON” data type is used to store values in a variant data format. The JSON data type can store values of any data type, including arrays and objects.
  3. PostgreSQL: In PostgreSQL, the “anyarray” data type is used to store arrays of values of any data type. The “anyelement” data type can be used to store values of any data type, including arrays and objects.
  4. Oracle Database: In Oracle Database, the “ANYDATA” data type is used to store values of any data type. This data type can be used to store values of any type, including integers, strings, and objects.

Cloud database providers

  1. Amazon Web Services (AWS): AWS offers a number of cloud databases that support variant data types, including Amazon Aurora, Amazon DynamoDB, and Amazon Redshift.
  2. Microsoft Azure: Microsoft Azure offers a number of cloud databases that support variant data types, including Azure SQL Database, Azure Cosmos DB, and Azure Synapse Analytics (formerly SQL Data Warehouse).
  3. Google Cloud Platform (GCP): GCP offers a number of cloud databases that support variant data types, including Cloud Bigtable, Cloud Datastore, and Cloud SQL.
  4. Snowflake: In Snowflake, the “VARIANT” data type is used to store values of different data types in a single field. The VARIANT data type can store values of any data type, including integers, strings, and objects.

    In addition to the VARIANT data type, Snowflake also supports a number of other data types, including numeric, string, Boolean, and date/time data types. These data types can be used to store specific types of data in a more efficient manner, depending on the needs of the application or query.

Programming languages

  1. Python: In Python, the “Any” data type is used to store values of any data type. This data type is part of the “typing” module and can be used to store values of any type, including integers, strings, and objects.
  2. Java: In Java, the “Object” data type is used to store values of any data type. This data type is part of the Java language and can be used to store values of any type, including integers, strings, and objects.

Variant will help you to develop in a more agile way

Using variant data types can help to create a more agile data warehouse development process by providing greater flexibility in storing and managing data. Some specific ways in which variant data types can improve the agility of data warehouse development include:

  1. Accommodating changing data sources: By using Variant data types, you can more easily accommodate changes to the data sources that feed your data warehouse. For example, if a new data source becomes available or an existing data source changes its schema, you can use variant data types to more easily incorporate the new data into your data warehouse.
  2. Simplifying data cleansing and transformation: By using Variant data types, you can often reduce the need for data cleansing and transformation, as the data warehouse can more easily accommodate a wide range of data types and formats. This can help to streamline the data load process and reduce the time and effort required to get new data into the data warehouse.
  3. Improving query performance: By using Variant data types, you can often improve the performance of queries against your data warehouse, as the system can more efficiently store and retrieve data. This can be particularly important when working with large volumes of data.

How Data Vault 2.0 works with Data Mesh

Introduction

Data Vault is a data modeling approach that is designed to provide a flexible and scalable foundation for storing and managing data in a data warehouse. It is based on the idea of creating a central repository for all data in an organization, which is then used to support the various data needs of the organization, such as reporting, analytics, and data integration.

Data Mesh is a methodology for building and managing data systems that emphasizes collaboration, transparency, and shared ownership. It is based on the idea of creating a decentralized architecture for data management, in which teams are empowered to take ownership of the data they produce and use, and to work together to create a shared understanding of the data landscape.

In a Data Mesh environment, the Data Vault can be used as a central repository for storing and managing data that is shared across the organization. This can include data from multiple sources, such as transactional systems, IoT devices, and external data sources. The Data Vault can be used to store both raw data and transformed data, and can be accessed by various teams and applications as needed.

By using the Data Vault as a central repository for data in a Data Mesh environment, organizations can benefit from the flexibility and scalability of the Data Vault model, while also taking advantage of the decentralized, collaborative approach of Data Mesh. This can help organizations to better manage their data assets, foster greater collaboration among teams, and improve the overall quality and value of their data.

How Data Vault helps with Data Mesh architecture

Data Vault can help with Data Mesh in a number of ways:

  1. Central repository: Data Vault can serve as a central repository for storing and managing data in a Data Mesh environment. This can help to ensure that data is consistent, reliable, and easily accessible to all teams and applications that need it.
  2. Flexibility and scalability: Data Vault is designed to be flexible and scalable, which can be particularly useful in a Data Mesh environment where teams are working on a variety of data-related projects. The Data Vault model can accommodate a wide range of data types and structures, and can easily adapt to changes in the data landscape over time.
  3. Data governance: The Data Vault can help to facilitate data governance in a Data Mesh environment by providing a central location for storing and managing data. This can help to ensure that data is consistently defined and properly documented, and can make it easier for teams to access and use data in a consistent way.
  4. Collaboration: By using Data Vault as a central repository for data in a Data Mesh environment, teams can more easily collaborate on data-related projects. The Data Vault model provides a clear, structured approach for storing and managing data, which can help teams to work together more effectively and to achieve better results.
  5. Data Integration: The Data Vault can be used to integrate data from multiple sources in a Data Mesh environment. This can help to ensure that data is properly cleaned, transformed, and integrated, and can make it easier for teams to access and use data from different sources.
  6. Data Sharing: The Data Vault can be used to store data that is shared across the organization in a Data Mesh environment. This can help to foster collaboration and shared ownership of data, and can make it easier for teams to access and use data that is relevant to their work.

Overall, Data Vault can support Data Mesh by providing a flexible, scalable, and well-governed foundation for storing and managing data in a decentralized, collaborative environment.

Introduction to DataVault 2.0

My previous blog, where I had articles for more than 10 years around Data Warehousing and Data Modeling was deleted due to some misunderstanding with my hosting provider, but after some thought I would like to start fresh a new one and focus mainly on Data Modeling and Data Warehousing surrounding my favourite architecture, Data Vault 2.0.

DISCLAIMER: If you already know DV2.0 then you can skip this article.

What is Data Vault 2.0

Data Vault 2.0 is a data modeling approach that was developed by Dan Linstedt and is designed to support the creation of a long-term, scalable data warehouse. It is based on the original Data Vault model, which was designed to provide a flexible and scalable way to manage data in a data warehouse environment.

Data Vault 2.0 is an extension of the original Data Vault model and includes additional features and improvements. Some of the key features of Data Vault 2.0 include:

  • A focus on building a data warehouse that can be easily maintained and evolved over time
  • A modular design that allows data to be added or modified without requiring changes to the entire data model
  • The use of standardized, reusable components to improve the efficiency and speed of data modeling
  • A flexible, scalable architecture that can support large volumes of data and high levels of concurrency

Data Vault 2.0 is often used in conjunction with other data management tools and technologies, such as ETL (extract, transform, load) tools and data lakes, to support the creation of a comprehensive data management solution.

Main advantages

There are several advantages to using Data Vault 2.0 as a data modeling approach:

  1. Scalability: Data Vault 2.0 is designed to support the management of large volumes of data and high levels of concurrency, making it well-suited for use in data warehouses with a high volume of data.
  2. Modularity: The modular design of Data Vault 2.0 allows data to be added or modified without requiring changes to the entire data model, which can make it easier to maintain and evolve the data warehouse over time.
  3. Reusability: Data Vault 2.0 uses standardized, reusable components, which can improve the efficiency and speed of data modeling.
  4. Flexibility: Data Vault 2.0 is a flexible data modeling approach that can accommodate a wide range of data types and structures.
  5. Historical data management: Data Vault 2.0 is designed to support the management of historical data, allowing users to track changes to data over time and support the creation of historical reports.
  6. Data governance: Data Vault 2.0 includes features that support data governance, such as the ability to track data lineage and ensure data quality.
  7. Integration with other tools: Data Vault 2.0 can be used in conjunction with other data management tools and technologies, such as ETL (extract, transform, load) tools and data lakes, to support the creation of a comprehensive data management solution.

Main disadvantages

Some potential disadvantages of using Data Vault 2.0 as a data modeling approach include:

  1. Complexity: Data Vault 2.0 can be complex to implement and may require specialized training and expertise to use effectively.
  2. Performance: Data Vault 2.0 can potentially result in a larger number of tables and relationships compared to other data modeling approaches, which may impact query performance.
  3. Lack of support for certain types of data: Data Vault 2.0 may not be well-suited for certain types of data, such as data with complex relationships or data that requires a high level of normalization.
  4. Limited support for real-time reporting: Data Vault 2.0 is primarily designed for use in data warehouses and may not be well-suited for supporting real-time reporting or analytics.
  5. Data quality challenges: Data Vault 2.0 relies on the accuracy and completeness of the data being loaded into the data warehouse, and may not include built-in features for data cleansing or validation.

It’s worth noting that the suitability of Data Vault 2.0 for a given situation will depend on the specific requirements and constraints of the data management project. It may be necessary to carefully consider the trade-offs and potential drawbacks of Data Vault 2.0 before deciding to use it as the data modeling approach.

Conclusion

Knowing that all the alternative techniques such 3NF or Dimensional Modeling, have also their pros and cons, I would say DV2.0 is the best option for a complex, long lasting and modern Data Warehouse. We could say, that it is a recent technique that can fit in our current technologies.