Snowflake - DataVault and Mart

Applies to
Platform: Snowflake
Architecture: Data Vault · Data Mart

Snowflake - DataVault and Mart generates a Stage, Raw Vault, Business Vault and Mart for Snowflake as SnowSQL scripts with stored procedure loaders. Select the Generator Configuration Snowflake DataVault & Mart when you add a project whose Data Vault and data marts run on Snowflake.

Scope and output

Platform: Snowflake.
Layers: Source, Stage, Raw Vault, Business Vault and Mart. Dataflow Modeling shows Stage in the Bronze column, both vault layers in the Silver column and the Mart model objects in the Datamart column, which is Gold. See Understand the Layering concept.
Output: the layer scripts SQL\L010_Stage.sql to SQL\L040_Mart.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 Configurations lists the discovery types.

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

Supported model objects

Mart

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. A Mart Dimension with Implementation Type Cached gets a transient table.
Keys: the primary key of a Hub or Link is its hash key, and of a Mart Dimension its surrogate key <model object name>_SK INTEGER IDENTITY ORDER. Both get a unique constraint on the business key terms; a Mart Fact has no primary key. Snowflake does not enforce them, see Overview of Constraints.
Foreign keys: Foreign Key Mode offers None and Create Disabled (default), not Create Enforced.
Indexes: index management is not available.
Unknown member: Hubs, Links, Satellites and Link Satellites get a row with the hash key X'00', and each Mart Dimension gets a row with the surrogate key -1. Custom Singleton Values sets their 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, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity
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 - DataVault and Mart 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 Mart Dimension bk_multipart_not_nullable reads the Business Current View BDV_CURRENT_BDC_CUSTOMER of the same project. The excerpt uses the example database DWH and the schemas BV and MART for the placeholders:

CREATE OR REPLACE VIEW DWH.MART.DM_MD_BK_MULTIPART_NOT_NULLABLE_SOURCE
    COPY GRANTS
AS
SELECT
     CAST(NULL AS VARCHAR) AS BG_SOURCESYSTEM
    ,S1.BDC_SHOP_ID AS BDC_SHOP_ID
    ,S1.BDC_CUSTOMER_ID AS BDC_CUSTOMER_ID
    ,S1.FIRST_NAME AS FIRST_NAME
    ,S1.LAST_NAME AS LAST_NAME
    ,S1.DATE_OF_BIRTH AS DATE_OF_BIRTH
    ,S1.LOCATION AS LOCATION
FROM DWH.BV.BDV_CURRENT_BDC_CUSTOMER AS S1
;

The view DM_MD_BK_MULTIPART_NOT_NULLABLE_HASHING builds the row hash BG_ROWHASHSCD1 of the four SCD1 terms FIRST_NAME, LAST_NAME, DATE_OF_BIRTH and LOCATION with SHA2_BINARY(..., 256).

Release differences

Project Settings shows the Generator Version of your project.

Generator 2.2: Vault Out and Mart 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. Since Generator 2.0, Delta Detection is a property of the Mart Dimension.