Snowflake Data Mart

Applies to
Platform: Snowflake
Architecture: Data Mart

Snowflake Data Mart generates a Mart layer for Snowflake from the objects of linked projects, as SnowSQL scripts with stored procedure loaders. Select the Generator Configuration Snowflake DataMart when you add a project whose data marts run on Snowflake.

Scope and output

Platform: Snowflake.
Layers: Mart, and the layers of the imported linked projects. Dataflow Modeling shows the imported objects in the Linked column in Silver and the Mart model objects in the Datamart column, which is Gold. See Understand the Layering concept.
Output: the layer script SQL\L010_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 databases of the linked projects.
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

The configuration reads only model objects of linked projects, for example of a Data Vault or Data Store project. It has no discovery of its own. Import a linked project before you model the Mart layer. You can also export this project as a linked project.

Mart source views read the layers of the linked project by three-part name, with their own database and schema placeholders.

Supported model objects

Capabilities and limitations

Names: generated names are upper case without quotes, for example DM_MD_BK_MULTIPART_NOT_NULLABLE.
Loaders: stored procedures <table name>_LOADER in Snowflake Scripting. Each loads in one transaction and logs its row counts with SYSTEM$LOG_INFO.
Tables: Implementation Type Cached creates a transient table, Permanent a permanent one.
Keys: a Mart Dimension has the surrogate key <model object name>_SK INTEGER IDENTITY ORDER as primary key and a unique constraint on its 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. Create Disabled creates the foreign keys as NOT ENFORCED constraints; None creates no constraint.
Indexes: index management is not available.
Unknown member: the deployment adds a row with the surrogate key -1 to each Mart Dimension. Custom Singleton Values sets its values.
Change detection: Delta Detection on the Mart Dimension sets how rows are compared; the default is Hash Value Comparison.
Hashing: 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.
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

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
Row hashes, BINARY(32): 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_MartDimension_Identity, BG_MartFact_ForeignKey_Identity
Flags, BOOLEAN: BG_IsDeleted
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 Mart of the same release:

Item	Snowflake	Microsoft SQL Server
Timestamp Default Terms	`TIMESTAMP_TZ`	`datetimeoffset`
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 with the business key terms BDC_SHOP_ID and BDC_CUSTOMER_ID generates this table. Its surrogate key is an identity column. The excerpt shows the example database DWH and schema MART in place of the placeholders:

CREATE OR REPLACE TABLE DWH.MART.DM_MD_BK_MULTIPART_NOT_NULLABLE (
     BG_SOURCESYSTEM VARCHAR NULL
    ,BG_LOADTIMESTAMP TIMESTAMP_TZ NOT NULL
    ,BK_MULTIPART_NOT_NULLABLE_SK INTEGER IDENTITY ORDER NOT NULL
    ,BDC_SHOP_ID VARCHAR NOT NULL
    ,BDC_CUSTOMER_ID VARCHAR NOT NULL
    ,FIRST_NAME VARCHAR NULL
    ,LAST_NAME VARCHAR NULL
    ,DATE_OF_BIRTH DATE NULL
    ,LOCATION VARCHAR NULL
    ,BG_ROWHASHSCD1 BINARY(32) NOT NULL
    ,CONSTRAINT PK_DM_MD_BK_MULTIPART_NOT_NULLABLE
        PRIMARY KEY (BK_MULTIPART_NOT_NULLABLE_SK)
    ,CONSTRAINT UC_DM_MD_BK_MULTIPART_NOT_NULLABLE
        UNIQUE (BDC_SHOP_ID, BDC_CUSTOMER_ID)
)
;

The deployment then inserts the unknown-member row with BK_MULTIPART_NOT_NULLABLE_SK -1. Its source view reads BDV_CURRENT_BDC_CUSTOMER of the linked Data Vault project.

Release differences

Project Settings shows the Generator Version of your project.

Generator 2.2: 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 on Mart Fact.
Generator 2.3: the model object property Custom Singleton Values on Mart Dimension.
Generator 2.4: 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.