DataVault4Coalesce - Data Vault Implementation

BI, Guide

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION

TABLE OF CONTENTS

1. WHAT IS DATAVAULT4COALESCE?

2

2. HOW TO INSTALL DATAVAULT4COALESCE?

3

3. ADDING DATA SOURCES

4

4. STAGES

6

5. HUBS

11

6. ADDITIONAL ENTITIES

15

7. SUMMARY

16

1. WHAT IS DATAVAULT4COALESCE?

Coalesce is an automation tool that combines a front-end GUI with high customizability. It’s easy to learn and includes features like modeling, Code generation, orchestration, documentation, testing, source control, and more. To help implement Data Vault in a Coalesce Instance, Scalefree has released Datavault4Coalesce. It includes a collection of macros and database object templates specifically tailored for Data Vault 2.0 objects. This guide aims to provide an introduction of how to install and use our package. It does not provide a comprehensive documentation of Datavault4Coalesce. Such a documentation can be found on the Github Wiki.

COALESCE TRANSFORMATION TALKS EVERY THIRD THURSDAY OF THE MONTH

CLICK HERE TO REGISTER FOR THE NEXT SESSION

https://scalefr.ee/coalesce-talks

2/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

2. HOW TO INSTALL DATAVAULT4COALESCE?

If you don’t have a Coalesce Environment yet, you can easily get started by using Snowflake Partner Connect. After setting up a new project, create a first workspace for your development and launch it. Now enter the “Build Settings” and navigate to the “Packages” section. Here you can now browse all available Coalesce packages, or directly choose “Install”.

In here, paste the PackageID of Datavault4Coalesce, @coalesce/data-vault-by-scalefree :

After defining a Package Alias, click on “Install” to integrate Datavault4Coalesce in your project. Now we can start using it!

3/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

3. ADDING DATA SOURCES One of the first steps in every Coalesce project is to add Sources to your DAG. Open your project browser and select “Nodes” in the left pane to expand it. Next to the search bar, press the “+” and select “Add Sources”:

4/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

In the next pane, select all source objects that you want to make accessible in your project. This guide will use the TPCH dataset, as provided in the SNOWFLAKE_SAMPLE_DATA database. After selecting, use the “Add X Sources” button in the bottom right corner, which will add the sources as new nodes to your explorer.

Your project is now ready to use the first Datavault4Coalesce node type, Stages!

5/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

4. STAGES FOUNDATION OF A RAW DATA VAULT To build your raw data vault, you always start with a Stage. In the Stage, you absorb all source data and add hash keys, load dates, and other technical columns like the record source columns. You should already see your sources added in the last step within the Node Overview. To create Stages for all of these nodes, select the sources (you can multi-select using Shift) and then right-click. This opens a menu that allows you to add nodes. Here, choose “Datavault4Coalesce” and then “Datavault by Scalefree: Stage”. After that, you will see a stage node next to each source node.

6/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

VALIDATING YOUR STAGES To ensure that everything is functioning correctly and your stages are properly set up, click the “Run All” button located in the upper-right corner of the interface. This action validates the configurations and processes all the stages. After running, double-click on the stage “ DVSTG_Customer ” to inspect its details. You should notice that two new columns, LDTS (Load Date Timestamp) and RSRC (Record Source), have been automatically added. These columns are crucial for tracking when each record was loaded and identifying its source system. To verify that the data has been loaded correctly, click the “Fetch Data” button. This will display the data within the stage, allowing you to confirm that all records are present and correctly formatted. ADDING HASH KEYS TO THE STAGE Continuing with your Data Vault development, the next step is to add hash keys in the stage. Hash keys are essential for uniquely identifying records and linking data across different tables.

1. Locate the C_Custkey column in the “ DVSTG_Customer ” stage.

2. Right-click on the C_Custkey column and select “Generate Hash Column” from the context menu.

3. A new hash key column will appear at the bottom of the column list. Rename this column appropriately. For instance, HK_Customer_H to reflect its purpose.

7/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

You can generate Link Hash Keys and Hash Diff columns for your satellites in a similar fashion:

— Link Hash Keys: Select multiple columns that together form a unique key for the link, right-click, and choose “Generate Hash Column”. Rename the new hash key accordingly. — Hash Diffs: Select the columns that you want to track for changes in your satellite, right-click, and select “Generate Hash Diff Column”. After adding and renaming the hash keys in the UI, you need to rerun the stage to incorporate the new hash key columns into your data. Click the “Create” button to process the updated stage.

8/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

To verify that the hash keys have been added correctly, click the “Fetch Data” button once more. You should now see the newly added hash key columns and their corresponding values in the data. This confirms that the hash keys are successfully integrated into your stage.

TIP

Consistently naming your hash key columns (e.g., prefixing with HK_ for Hash Keys and HD_ for Hash Diffs) can greatly improve the readability and maintainability of your Data Vault schema.

9/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

NOTE

When you create a stage, ghost records are automatically generated. Ghost records serve as placeholders for missing data, ensuring referential integrity within your Data Vault. If your use case doesn’t require ghost records, you can deactivate this feature in the stage settings.

Additionally, if your stage contains multi-active data (situations where multiple records can be active simultaneously for the same business key) you can enable the “Contains Multi-active Data” option in the stage configuration. This ensures that the Data Vault accurately handles multiple active records.

10/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

5. HUBS REPRESENTING CORE BUSINESS ENTITIES

After creating a Data Vault Stage, we have everything to populate the first real Data Vault entity. This section will show how to create a Hub with Datavault4Coalesce, based on the previously created “DVSTG_CUSTOMER”.

To start, jump back into the column overview of “DVSTG_CUSTOMER”. Now select all columns that are needed for a Customer Hub:

— The Hub Hashkey, HK_CUSTOMER_H

— The Business Key, C_CUSTKEY

— The Load Date Timestamp, LDTS

— The Record Source, RSRC

11/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

After multi-selecting these four columns, right-click one of them and select “Add Node”->”Datavault4Coalesce”->”Datavault by Scalefree: Hub”:

This automatically creates and opens a new node, HUB_CUSTOMER . In here, just configure which column represents the Hub Hashkey. Within the right config pane, expand “Data Vault”, select the HK_CUSTOMER_H as the Hub Hash Key column, and use the arrow to move it to the selected columns:

12/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

Additionally, adjust the Storage Location under Node Properties to match your desired physical location.

Now that you have configured your Hub, press the “CREATE” button at the bottom to physically create your Hub Table.

13/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

The execution was successful, but for now, only the CREATE TABLE statement has been executed, and the Hub is still empty. To load data into our Hub, use the “RUN” button right next to the “CREATE” button. This will actually load data into your Hub:

With these simple steps, a Hub has been created and properly loaded. All other Datavault4Coalesce entities are created similarly, with different parameters each. For more entity-specific information, please refer to our official documentation.

14/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

6. ADDITIONAL ENTITIES STRUCTURING BUSINESS DATA Beyond hubs, the Datavault4Coalesce Package includes several other essential entity types that work together to comprehensively model and manage business data. Here’s a brief overview:

— Standard Link: Captures relationships between hubs, representing associations between unique business keys.

— Non-Historized Link: Similar to a standard link but does not track historical changes. It’s useful for relationships that are static and do not change over time.

— Standard Satellite: Stores descriptive attributes related to a hub or link. It tracks historical changes, allowing you to see how attribute values have evolved.

— Multi-Active Satellite: Handles scenarios where multiple records can be active simultaneously for the same business key.

— Non-Historized Satellite: Contains attributes that don’t require historical tracking. This simplifies data management for static information that doesn’t change over time.

15/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

— Record-Tracking Satellite: A specialized satellite linked to a hub or link that stores metadata about data loading events, such as load timestamps and source identifiers, to facilitate detailed data lineage tracking and auditing. — PIT (Point-In-Time): Provides a snapshot of data at specific points in time, simplifying complex queries and enabling efficient reporting on historical data states. — Snapshot Control: Manages the creation and maintenance of snapshots, allowing you to control how and when data snapshots are taken for reporting or backup purposes. By incorporating these additional entities into your Data Vault model, you can accurately represent various aspects of your business data. Each entity type serves a specific purpose, helping you build a well-structured data warehouse that meets your analytical and reporting needs. 7. SUMMARY With Datavault4Coalesce you can implement a Data Vault 2.0 within minutes. If you want to know more about our package or have specific questions, please visit https://scalefr.ee/dv4coalesce or contact us via contact@scalefree.com.

16/16

DATAVAULT4COALESCE: HOW TO ACCELERATE YOUR DATA VAULT IMPLEMENTATION © SCALEFREE INTERNATIONAL GMBH 2025

Scalefree International GmbH Schützenallee 3 30519 Hannover Germany

P: +49 (511) 879 893 41 M:+49 (175) 811 0336 F: +49 (511) 879 893 49

info@scalefree.com www.scalefree.com

CEOs: Michael Olschimke & Christof Wenzeritt

Page 1 Page 2 Page 3 Page 4 Page 5 Page 6 Page 7 Page 8 Page 9 Page 10 Page 11 Page 12 Page 13 Page 14 Page 15 Page 16 Page 17 Page 18

Powered by