Snowflake Data Vault

Applies to
Platform: Snowflake
Architecture: Data Vault

Snowflake Data Vault generates a Stage, Raw Vault and Business Vault for Snowflake as SnowSQL scripts with stored procedure loaders. Select the Generator Configuration Snowflake Datavault when you add a project whose Data Vault runs on Snowflake.

For a Data Vault built with dbt on Snowflake, select Snowflake Dbt DataVault instead. Its artifacts, placeholder values and deployment are described in dbt - Artifacts, dbt - Placeholder values and dbt - Deployment.

Scope and output

Platform: Snowflake.
Layers: Source, Stage, Raw Vault and Business Vault. Dataflow Modeling shows Stage in the Bronze column and both vault layers in the Silver column. See Understand the Layering concept.
Output: the layer scripts SQL\L010_Stage.sql to SQL\L030_Business Vault.sql, the task scripts of the load control or an Airflow DAG in the folder LoadControl, and deploy.ps1 with deploy.sql, described in Snowflake - Artifacts.

Prerequisites

A Snowflake account with the databases of the layers and of the load control, and a warehouse. The deployment creates only the schemas.
A role that creates the objects, runs tasks and reads the source databases.
PowerShell 7 and SnowSQL with a named connection.

The setup is described in Snowflake - Target environment, the placeholders in Snowflake - Placeholder values.

Sources and linked projects

Stage source views read each source table as <database>.<schema>.<table>, with placeholders for the database and schema. Discover a Snowflake database creates the source model objects, and Source systems per generator configuration lists the discovery types.

You can import a linked project as a source and export this project as a linked project.

Supported model objects

Capabilities and limitations

Names: generated names are upper case without quotes, for example STG_ST_BDC_CUSTOMER.
Loaders: stored procedures <table name>_LOADER in Snowflake Scripting. Each loads in one transaction and logs its row counts with SYSTEM$LOG_INFO.
Tables: Stage tables are transient by default; vault tables are permanent.
Keys: Hubs and Links have their hash key as primary key and a unique constraint on the business key terms. Snowflake does not enforce them, see Overview of Constraints.
Foreign keys: Foreign Key Mode offers None and Create Disabled (default), not Create Enforced. Create Disabled creates the foreign keys as NOT ENFORCED constraints; None creates no constraint.
Indexes: index management is not available.
Unknown member: Hubs, Links, Satellites and Link Satellites get a row with the hash key X'00'. Custom Singleton Values sets its values.
Hashing: hash keys and row hashes are SHA2_BINARY(..., 256) in BINARY(32) columns with the default Hash Algorithm. See How biGENIUS-X builds hash values.
Validity: with Persist Validity on (default), historized tables store BG_ValidToTimestamp; with it off, the result view computes it.
Change detection: with Excluded from Delta Detection, changes to the selected terms of a Satellite alone create no new version.
Loading order: Use Embryo Loader on a relationship inserts missing keys into the related Hub or Link before the Satellite loader runs.
Deployment: with the default Deployment Options, each deployment replaces the tables with empty ones and drops the load control schema. See Snowflake - Deployment.
Loading: LoadControl Options offers Snowflake tasks and Apache Airflow. Warehouse Placeholder Variable Name moves the tasks of a dataflow to another warehouse.
Governance: Apply Sensitivity Level Tags adds a script that creates the tag SENSITIVITY_LEVEL.

Project properties

The configuration offers these project properties, with their defaults:

Hash Algorithm: SHA2-256; also HASH and MD5
Hash Casing: None; also Upper and Lower
Hash Concatenation Separator: _
Hash Null Placeholder: _NULL_
Hash Trim: off
Begin of Universe: 1900-01-01
End of Universe: 9999-12-31
Persist Validity: on
Create Result Views: on
Target Object Comment Type: None; also Tables and Tables and Views
Artifact Output Mode: Layer Composites; also Deployment Objects
Deployment Options: Generate Drop Table Statements and Generate Drop Load Control Schema
Apply Sensitivity Level Tags: off
Sensitivity Level Tag Name: SENSITIVITY_LEVEL
Configure LoadControl: on
LoadControl Options: Depending Tasks; also Stage-Driven Tasks, Near-Realtime Tasks and Airflow

Older projects can still show the legacy property Behaviour for Nulled Business Keys in Links; the data quality rules replace it, see Understand Data Quality rules overview.

Data types

The Default Terms get these data types. TIMESTAMP_TZ has no precision, so Snowflake uses precision 9, and VARCHAR has no length:

Timestamps, TIMESTAMP_TZ: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp, DVPIT_ForeignKey_LoadDate, BG_Effectivity_Begin, BG_Effectivity_End
Hash keys and row hashes, BINARY(32): Hub_HK, Link_HK, DrivingKeyLink_HK, PIT_HK, DVLINK_ForeignKey_Identity, DVPIT_ForeignKey_Identity, BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
Text, VARCHAR: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus
Numbers, INTEGER: BG_ID, BG_ErrorCount, BG_DataQuality_ID, BG_Sequence
Flags, BOOLEAN: BG_IsDeleted, BG_Appearance, BG_MAS_HubDifferenceFound
BG_IncrementalFilter, BG_LowerThreshold, BG_UpperThreshold: the data type of the term that you map to BG_IncrementalFilter

Platform differences

The table compares the configuration with Microsoft SQL Server Data Vault of the same release:

Item	Snowflake	Microsoft SQL Server
Timestamp Default Terms	`TIMESTAMP_TZ`	`datetimeoffset`
Effectivity Default Terms	`TIMESTAMP_TZ`	`datetime2`
Hash Default Terms	`BINARY(32)`	`binary(20)`
Text Default Terms	`VARCHAR`	`nvarchar`
Hash Algorithm default	`SHA2-256`	`SHA1`
Persist Validity default	on	off
Deployment Options default	both options	`Generate Drop Table Statements`
Enforced foreign keys	not available	available
Index management	not available	available
Deploy LoadControl	not available	available

On both platforms, LoadControl Options offers Airflow and a native load control: Snowflake tasks on Snowflake, Native Load Control on Microsoft SQL Server.

Example

A Hub BDC_CUSTOMER with the business key terms BDC_SHOP_ID and BDC_CUSTOMER_ID generates this table. The excerpt shows the example database DWH and schema RDV in place of the placeholders:

CREATE OR REPLACE TABLE DWH.RDV.RDV_HUB_BDC_CUSTOMER (
     BG_LOADTIMESTAMP TIMESTAMP_TZ NOT NULL
    ,HUB_HK BINARY(32) NOT NULL
    ,BG_SOURCESYSTEM VARCHAR NULL
    ,BDC_SHOP_ID VARCHAR NOT NULL
    ,BDC_CUSTOMER_ID VARCHAR NOT NULL
    ,CONSTRAINT PK_RDV_HUB_BDC_CUSTOMER PRIMARY KEY (HUB_HK)
    ,CONSTRAINT UC_RDV_HUB_BDC_CUSTOMER
        UNIQUE (BDC_SHOP_ID, BDC_CUSTOMER_ID)
)
;

The deployment then inserts the unknown-member row with HUB_HK X'00' and the value Unknown in both business key columns. The hashing view RDV_HUB_BDC_CUSTOMER_HASHING computes the hash key:

SHA2_BINARY(IFNULL(BDC_SHOP_ID, '_NULL_')
    || '_' || IFNULL(BDC_CUSTOMER_ID, '_NULL_'), 256) AS HUB_HK

Release differences

Project Settings shows the Generator Version of your project.

Generator 2.2: Vault Out; the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder, Hash Trim, Apply Sensitivity Level Tags and Sensitivity Level Tag Name; the model object property Increment Management Type; the relationship property Use Embryo Loader.
Generator 2.3: the model object property Custom Singleton Values.
Generator 2.4: Non-Historized Satellite and Non-Historized Business Satellite; the model object property Excluded from Delta Detection; Hash Algorithm defaults to SHA2-256 instead of HASH and also offers MD5; the hash Default Terms are BINARY(32) instead of NUMBER(19), see Generators Release - 2.4.0.
Generator 1.10: the project property Delta Detection. Generator 2.0 no longer offers it.