23 Oct Simplifying Staging in Data Vault with AutomateDV
Simplifying Staging in Data Vault with AutomateDV
One of the essential components of building a Data Vault is staging. It serves as the bridge between raw data and your Data Vault, ensuring that the information you use is not only accurate but also secure. In this blog post, we’ll explore what staging is, the different types of staging, and how AutomateDV handles them effectively to build a Data Vault.
What is Staging?
The staging area is the area in which data is stored, processed, and (lightly) transformed before it enters your Data Vault. It acts as a crucial intermediary step in the data integration pipeline, ensuring that the data is both reliable and secure for downstream processing. In the context of AutomateDV, staging plays a pivotal role in shaping the data before it becomes part of your Data Vault.
Raw Staging – Getting Data into the Data Warehouse
Raw staging is the initial phase where raw data is added to the data warehouse with no change to the data.
The goal is to load the data without making any changes or transformations from the source system. This is an essential step as it preserves the integrity of the original data.
Primed Staging – Adding Value and Security
Primed staging (preparing your data before it enters the raw vault) takes the raw data from the previous stage and adds extra layers of value and security. It introduces additional columns to the data. Some of these columns may be copies of other columns with minor transformations applied (original columns are retained for audit purposes):
- Hashkeys for Performance
These columns serve various purposes, including hashing rows to ensure uniqueness and consistent key-length for optimisation. It is important to note that hashkeys are not required in Data Vault but are recommended due to their performance benefits on most platforms. However, modern data cloud platforms like Snowflake do not incur much performance impact when using natural keys instead of hashkeys.
- Hashdiff for Tracking Changes
Hashdiffs are a critical feature of prime staging. It enables the tracking of changes in the data by comparing the hash values of rows. This is very similar to the role of a checksum. When changes occur in the payload or descriptive columns (such as customer email or address), hashdiffs detects these changes, allowing you to capture and monitor alterations in the data over time.
- Load Date Time
Prime staging includes a “load date time” column, which records the current timestamp or date when the data is first loaded into the data warehouse (not the source systems). This timestamp serves as a reference point, helping you keep track of when each piece of data entered the staging area.
- Effective From (Applied date)
The “effective from” column is the business-effective date of a record. It specifies the date or timestamp from which the data becomes effective
- Record Source
Record source columns enable you to categorise and identify the origin of the data. For example, you can differentiate between data from customers’ online orders on Shopify and data from in-store purchases. This feature helps maintain data lineage and auditability, as well as understand the sources of your information.
- Renaming Columns
Prime staging also allows for the renaming of columns to ensure consistency and compatibility with your Data Vault model. To continue with the Shopify vs in-store example, if your source systems use different names for the same entity (e.g., “cus_id” vs. “cus_account”), prime staging can harmonise these column names.
However, care should be taken when renaming columns as it can cause problems with your ability to audit.
Summary
In conclusion, staging is a critical process in AutomateDV’s Data Vault automation workflow. It ensures that your data is not only loaded efficiently but also enriched, secured, and well-prepared for its journey into the Data Vault and beyond.
With powerful metadata-driven features, you can easily generate hashdiffs, hashkeys, composite keys and many other useful columns to prepare your Data Vault. AutomateDV simplifies and streamlines the staging process, making it a robust and indispensable part of your Data Vault strategy.
For more information on the AutomateDV staging functionality, click here to read our extensive documentation on staging.