--- title: Snowflake - Datamart description: Snowflake - Datamart generates a Mart layer for Snowflake from the objects of linked projects, as SnowSQL scripts with stored procedure loaders. --- [Skip to content](https://knowledge.bigenius-x.com/snowflake-datamart#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) # Snowflake - Datamart **Applies to** Platform: Snowflake Architecture: Data Mart Snowflake - Datamart 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](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en). - 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](https://knowledge.bigenius-x.com/snowflake-artifacts?hsLang=en). ### 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](https://knowledge.bigenius-x.com/snowflake-target-environment?hsLang=en), the placeholders in [Snowflake - Placeholder values](https://knowledge.bigenius-x.com/snowflake-placeholder-values?hsLang=en). ### 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](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) before you model the Mart layer. You can also [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). Mart source views read the layers of the linked project by three-part name, with their own database and schema placeholders. ### Supported model objects - [Mart Dimension](https://knowledge.bigenius-x.com/create-a-mart-dimension?hsLang=en) - [Mart Fact](https://knowledge.bigenius-x.com/create-a-mart-fact?hsLang=en) - [Mart Composite](https://knowledge.bigenius-x.com/create-a-mart-composite?hsLang=en) - [Mart Hierarchy Flatten](https://knowledge.bigenius-x.com/create-a-mart-hierachy-flatten?hsLang=en) - [Mart Out](https://knowledge.bigenius-x.com/create-a-mart-out?hsLang=en), since Generator 2.2 ### Capabilities and limitations - Names: generated names are upper case without quotes, for example `DM_MD_BK_MULTIPART_NOT_NULLABLE`. - Loaders: stored procedures `_LOADER` in Snowflake Scripting. Each loads in one transaction and logs its row counts with `SYSTEM$LOG_INFO`. - Tables: [Implementation Type](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en) `Cached` creates a transient table, `Permanent` a permanent one. - Keys: a Mart Dimension has the surrogate key `_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](https://docs.snowflake.com/en/sql-reference/constraints-overview). - Foreign keys: [Foreign Key Mode](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en) offers `None` and `Create Disabled` (default), not `Create Enforced`. - 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](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) sets its values. - Change detection: [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en) 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](https://knowledge.bigenius-x.com/how-bigenius-x-builds-hash-values?hsLang=en). - 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](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), each deployment replaces the tables with empty ones and drops the load control schema. See [Snowflake - Deployment](https://knowledge.bigenius-x.com/snowflake-deployment?hsLang=en). - Loading: [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) offers Snowflake tasks and Apache Airflow. [Warehouse Placeholder Variable Name](https://knowledge.bigenius-x.com/warehouse-placeholder-variable-name-dataflow?hsLang=en) moves the tasks of a dataflow to another warehouse. - Governance: [Apply Sensitivity Level Tags](https://knowledge.bigenius-x.com/apply-sensitivity-level-tags-project?hsLang=en) adds a script that creates the tag `SENSITIVITY_LEVEL`. ### Project properties The configuration offers these project properties, with their defaults: - [Hash Algorithm](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en): `SHA2-256`; also `HASH` and `MD5` - [Hash Casing](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en): `None`; also `Upper` and `Lower` - [Hash Concatenation Separator](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en): `_` - [Hash Null Placeholder](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en): `_NULL_` - [Hash Trim](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en): off - [Begin of Universe](https://knowledge.bigenius-x.com/begin-of-universe-project?hsLang=en): `1900-01-01` - [End of Universe](https://knowledge.bigenius-x.com/end-of-universe-project?hsLang=en): `9999-12-31` - [Persist Validity](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en): on - [Create Result Views](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en): on - [Target Object Comment Type](https://knowledge.bigenius-x.com/target-object-comment-type-project?hsLang=en): `None`; also `Tables` and `Tables and Views` - [Artifact Output Mode](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en): `Layer Composites`; also `Deployment Objects` - [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en): `Generate Drop Table Statements` and `Generate Drop Load Control Schema` - [Apply Sensitivity Level Tags](https://knowledge.bigenius-x.com/apply-sensitivity-level-tags-project?hsLang=en): off - [Sensitivity Level Tag Name](https://knowledge.bigenius-x.com/sensitivity-level-tag-name-project?hsLang=en): `SENSITIVITY_LEVEL` - [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en): on - [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en): `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 - Datamart](https://knowledge.bigenius-x.com/microsoft-sql-server-datamart?hsLang=en) 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](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en) | 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](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) 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](https://knowledge.bigenius-x.com/generators-release-2.4.0?hsLang=en). - Generator 1.10: the project property [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en). Since Generator 2.0, Delta Detection is a property of the Mart Dimension. ### Related articles - Previous: - Snowflake: [Target environment](https://knowledge.bigenius-x.com/snowflake-target-environment?hsLang=en) - Next: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) - Snowflake: [Artifacts](https://knowledge.bigenius-x.com/snowflake-artifacts?hsLang=en), [Deployment](https://knowledge.bigenius-x.com/snowflake-deployment?hsLang=en), [Load data - Native Load Control](https://knowledge.bigenius-x.com/snowflake-load-data-native-load-control?hsLang=en), [Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/snowflake-load-data-apache-airflow-load-control?hsLang=en) - Reference: [Foreign Key Mode (Model Object)](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en), [Delta Detection (Model Object)](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en), [Hash Algorithm (Project)](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en), [Warehouse Placeholder Variable Name (Dataflow)](https://knowledge.bigenius-x.com/warehouse-placeholder-variable-name-dataflow?hsLang=en), [BG\_MartDimension\_Identity](https://knowledge.bigenius-x.com/bg_martdimension_identity?hsLang=en), [BG\_MartFact\_ForeignKey\_Identity](https://knowledge.bigenius-x.com/bg_martfact_foreignkey_identity?hsLang=en) - Snowflake: [Placeholder values](https://knowledge.bigenius-x.com/snowflake-placeholder-values?hsLang=en) - See also: [Available Generator Configurations](https://knowledge.bigenius-x.com/available-generator-configurations?hsLang=en), [Understand the Generator Configuration](https://knowledge.bigenius-x.com/understand-the-generator-configuration?hsLang=en), [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en), [Use Linked Projects](https://knowledge.bigenius-x.com/use-linked-projects?hsLang=en) - Snowflake: [DataVault](https://knowledge.bigenius-x.com/snowflake-datavault?hsLang=en), [DataVault and Mart](https://knowledge.bigenius-x.com/snowflake-datavault-and-mart?hsLang=en), [Data Store](https://knowledge.bigenius-x.com/snowflake-data-store?hsLang=en), [Dimensional and Mart](https://knowledge.bigenius-x.com/snowflake-dimensional-and-mart?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG