Exploring datavault4dbt

In our initial post of this series, we delved into the creation of our staging layer using DataVault4dbt, an open-source package designed for Data Vault 2.0 within dbt. In this installment, we embark on the journey to construct our first standard Data Vault 2.0 model entities in the Raw Vault, including Hubs, Links, and Satellites. As in our previous post, we recommend staying up-to-date with the latest changes and adaptations in the DataVault4dbt package by referring to the project’s GitHub repository Wiki.

Before We Start

Before we get started, ensure that you have the DataVault4dbt package correctly installed in your packages.yml file and that you’ve executed dbt deps.

For this tutorial, we’ll be using the TPCH Snowflake Sample Data. Moreover, we assume you’ve already established your staging model, which includes the calculation of hashkeys and hashdiffs. Here’s a snippet from our staging model‘s configuration, which we’ll need later when creating the Raw Vault entities:

stg_orders

A. Standard Hub with datavault4dbt

Hubs are constructed based on a unique list of business keys, making their configuration relatively straightforward. In this example, we’ll be creating the Hub for orders:

order_h

• hashkey: the hashkey name in the staging model
• business_keys: name of the business key used as input for the previously mentioned hashkey
• source_models: name of our staging model

B. Standard Link with datavault4dbt

Link models establish connections between business keys. In our case, we’ll create a connection between the previously formed Order Hub and the Customer Hub:

order_customer_1

• link_hashkey: hashkey of the Link, generated using the foreign keys from the Hubs in the staging layer
• foreign_haskeys: a list of foreign hashkeys to be included in our link
• source_models: name of our staging model

C. Standard Satellite Version 0 with datavault4dbt

Following Data Vault 2.0 standards, Version 0 Satellites are created as incremental tables. In our example, the Satellite will be connected to the previously generated Order Hub:

order_0s

• parent_hashkey: name of the parent entity’s hashkey, in our case, the Order Hub
• src_hashdiff: hashdiff already calculated on the staging model
• src_payload: original columns used in the hashdiff calculation
• source_model: name of our staging model

D. Standard Satellite Version 1 with datavault4dbt

Additionally, the Version 1 Satellite is a virtually generated entity created on top of our Version 0 Satellite. Beyond the materialization type, the main difference with the V0 Satellite is the introduction of a new column for calculating the load end date. The load end date will be useful for us downstream when dealing with PIT tables in the Business Vault.

order_s

• sat_v0: name of the related Version 0 Satellite
• hashkey: hashkey name of the parent entity, in our case, the order Hub
• hashdiff: hashdiff already calculated on the staging model
• ledts_alias: name of the load end date column to be generated
• add_is_current_flag: when true, it generates a new column flagging the last loaded rows based on the load end date

Conclusion

In this journey through the creation of Raw Vault standard entities, we’ve established a strong foundation for our Data Vault 2.0 architecture. By utilizing DataVault4dbt within dbt, we’ve simplified the development of Hubs, Links, and Satellites. These fundamental building blocks are the cornerstone of a robust and scalable data warehousing solution. As we progress in this series, we’ll continue to explore advanced concepts and delve into the intricacies of Data Vault modeling, preparing us to unlock the full potential of our data.

Introduction

In the realm of fast-paced data management, efficiency and effectiveness are paramount. Specializing in data warehousing solutions, leveraging automation tools like WhereScape can significantly enhance our ability to deliver value to stakeholders. However, to truly harness the power of WhereScape and optimize our workflows, it’s essential to adhere to best practices. In this article, we delve into key strategies and best practices for maximizing efficiency and effectiveness when working with WhereScape 3D + RED.

Data Vault Standards

Implementing Data Vault 2.0 methodology requires adherence to established standards to ensure consistency, scalability, and maintainability of the data warehouse solution. Below, we outline key aspects of Data Vault standards that should be defined and followed rigorously:

1. Hashing Standards:
– Define the hash algorithm for generating hash keys and specify input/output formats to ensure compatibility

2. Load Date Timestamp (LDTS): CDC vs. Full Load:
– Determine LDTS capture approach and granularity for accurate data lineage tracking

3. Naming Conventions: Prefix/Suffix:
– Establish consistent naming conventions for Data Vault objects to enhance readability

4. Ghost Records:
– Add Ghost Records to Satellite entities so that equal joins are made possible in ad-hoc queries against the Raw Vault

Adhering to Data Vault 2.0 standards is crucial for maintaining compatibility and interoperability with other implementations. Customizations may be necessary in WhereScape to align with these standards, but deviations should be carefully evaluated and documented.

WhereScape Architectural Setup

Setting up the WhereScape environment requires careful planning to ensure efficient development and deployment processes. Key considerations for architecting the WhereScape environment effectively include:

1. Multiple Environments for RED:
– Configure distinct environments within WhereScape RED to facilitate development, testing, and production stages

2. Recommended Setup:
– Aim for a setup consisting of at least four instances: development (dev), testing (test), pre-production (preprod), and production (prod)

3. Understanding the Role of WhereScape 3D:
– Recognize WhereScape 3D as a development and design tool for creating and modifying Data Vault models

4. Clear Development/Deployment Path:
– Enforce a disciplined approach to development and deployment, starting from WhereScape 3D for model design

Emphasizing the distinction between WhereScape 3D and RED environments is crucial to maintaining consistency and minimizing risks.

Customizations in WhereScape

Approach customizations with caution to ensure the stability and maintainability of your data warehouse solution. Key considerations for handling customizations effectively include:

1. Always Make Copies First:
– Create copies of original components before making any customizations to avoid overwriting or modifying OOTB components

2. Avoid Over-Engineering:
– Keep customizations simple and straightforward to minimize complexity and maintenance burden

3. Best Practices of Data Vault 2.0:
– Focus on delivering business value and follow Data Vault 2.0 best practices to ensure scalability and flexibility

Avoiding bad practices such as merging stages of hubs, links, or satellites from a single source table is essential for maintaining consistency.

Job Scheduler Best Practices

Efficient job scheduling is crucial for maximizing data warehouse performance. Key practices for optimizing job scheduling with WhereScape include:

1. Utilize Load Parallelism:
– Enable parallel loading for various components to distribute workload across available hardware components

2. Understand Hardware Components:
– Thoroughly understand available hardware components to optimize load parallelism effectively

3. Experiment with Configuration Options:
– Fine-tune job execution based on available hardware resources and workload characteristics

4. Avoid Nested Jobs:
– Minimize nested jobs to ensure optimal performance and resource utilization.

By following these best practices, you can harness the full potential of load parallelism in WhereScape Scheduler to accelerate your Data Vault loading processes.

General Tips for WhereScape

In addition to specific best practices, consider the following tips to enhance your overall experience and efficiency with WhereScape:

1. Avoid Applying Soft Business Rules in WhereScape 3D:
– Incorporate soft business rules in WhereScape RED instead of 3D to maintain clarity and consistency

2. Utilize Projects and Groups:
– Organize and manage your development efforts effectively using projects in WhereScape RED and groups in 3D and RED

3. Prepare Content for Deployments:
– Thoroughly prepare and validate content before deploying changes or updates from WhereScape 3D to WhereScape RED

4. Inspect WhereScape 3D & RED Documentation:
– Regularly review documentation provided by WhereScape to better understand platform capabilities and features

By incorporating these tips into your workflow, you can enhance proficiency and productivity with WhereScape.

Conclusion

Working with WhereScape offers opportunities to streamline data warehouse development and deliver value efficiently. By following best practices and embracing a culture of collaboration and continuous improvement, BI Developers can navigate complexities with confidence, driving innovation and achieving transformative outcomes for organizations and stakeholders. If you want to learn more about Wherescape best practices and how to successfully implement a Data Vault 2.0 with it then check out our newest workshop Data Vault 2.0 Automation with Wherescape.