Author: Matillion
Date Posted: Oct 26, 2023
Last Modified: Jun 12, 2024
Data Vault 2.0 Shared Jobs
A pair of Shared Jobs that create and populate Data Vault 2.0 Hub, Link and Satellite tables.
These shared jobs are designed to work as part of a medallion data architecture that you are managing with Matillion ETL. The Data Vault 2.0 objects exist in the silver layer, and they read from “staging views” that you build over bronze layer objects.
- Stage to Hub - takes records from a staging view (or table) and populates a Data Vault 2.0 Hub, Satellite and Error table
- Stage to Link - takes records from a staging view (or table) and populates a Data Vault 2.0 Link, Satellite and Error table
Stage to Hub Parameters
| Parameter | Description |
|---|---|
| Schema | Name of the schema you are working in |
| Database | Name of the database you are working in |
| Record Source | Name of source system |
| DV Name Stub | Stub name of the Data Vault objects (e.g. “dummy” –> h_dummy, s_dummy and e_dummy) |
| Staging object name | Name of the staging view or table |
| Business key columns | Grid variable containing a list of the business key columns |
Stage to Link Parameters
| Parameter | Description |
|---|---|
| Schema | Name of the schema you are working in |
| Database | Name of the database you are working in |
| Record Source | Name of source system |
| Link name suffix | Optionally added to the link table name to distinguish multiple links between the same hubs |
| Staging object name | Name of the staging view or table |
| Hubs to link | Grid variable containing a list of the names of the Hub tables being linked |
Prerequisites
These shared jobs are designed to be standalone and minimalist, so they will create the Data Vault target tables if they don’t exist already. The jobs also create a simplistic hash function named f_dv2_hash which converts business keys into surrogate keys.
Data Vault implementation example
If you had a Data Vault 2.0 model like this, with three Hubs and three Links:
.. then your silver layer Matillion ETL Orchestration Job to populate these objects from bronze layer staging views might look like this:
Downloads
Licensed under: Matillion Free Subscription License
- Download METL-snowflake-AWS-datavault-sharedjob-1.56.melt
- Target: Snowflake on AWS
- Version: 1.56.10 or higher
- Download METL-snowflake-Azure-datavault-sharedjob-1.56.melt
- Target: Snowflake on Azure
- Version: 1.56.10 or higher
- Download METL-snowflake-GCP-datavault-sharedjob-1.56.melt
- Target: Snowflake on GCP
- Version: 1.56.10 or higher