--- title: Snowflake - Data Store description: Snowflake - Data Store generates a Stage and a Store layer for Snowflake as SnowSQL scripts with stored procedure loaders. --- [Skip to content](https://knowledge.bigenius-x.com/snowflake-data-store#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 - Data Store **Applies to** Platform: Snowflake Architecture: Data Store Snowflake - Data Store generates a Stage and a Store layer for Snowflake as SnowSQL scripts with stored procedure loaders. Select the Generator Configuration `Snowflake DataStore` when you add a project whose Data Store runs on Snowflake. ### Scope and output - Platform: Snowflake. - Layers: Source, Stage and Store. Dataflow Modeling shows Stage and the Data Store in the Bronze column. See [Understand the Layering concept](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en). - Output: the layer scripts `SQL\L010_Stage.sql` and `SQL\L020_Store.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 source databases. - 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 Stage source views read each source table as `..`, with placeholders for the database and schema. [Discover a Snowflake database](https://knowledge.bigenius-x.com/discover-a-snowflake-database?hsLang=en) creates the source model objects, and [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en) lists the discovery types. You can [import a linked project](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) as a source and [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). ### Supported model objects - [Source](https://knowledge.bigenius-x.com/create-a-source?hsLang=en) - [Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en) - [Store Entity](https://knowledge.bigenius-x.com/create-a-store-entity?hsLang=en) - [Store Composite](https://knowledge.bigenius-x.com/create-a-store-composite?hsLang=en) ### Capabilities and limitations - Names: generated names are upper case without quotes, for example `STG_ST_BDC_CUSTOMER`. - 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 Store Entity has no hash key and no surrogate key. Its primary key is the business key; a historized Store Entity adds `BG_ValidFromTimestamp` in front. Snowflake does not enforce them, see [Overview of Constraints](https://docs.snowflake.com/en/sql-reference/constraints-overview). - Change detection: [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en) on the Store Entity sets how rows are compared; the default is `Hash Value Comparison`. - Delete detection: [Configure delete detection](https://knowledge.bigenius-x.com/configure-a-delete-detection-behavior?hsLang=en) adds the column `BG_IsDeleted` and a delete detection table. - Data quality: Store Entity supports [data quality rules](https://knowledge.bigenius-x.com/understand-data-quality-rules-overview?hsLang=en). - Indexes: index management is not available. - 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` - 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 Store](https://knowledge.bigenius-x.com/microsoft-sql-server-data-store?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` | | 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 Store Entity `bk_multipart_not_nullable` with the business key terms `BDC_SHOP_ID` and `BDC_CUSTOMER_ID` has four SCD1 terms. It generates this table. The excerpt shows the example database `DWH` and schema `STORE` in place of the placeholders: ``` CREATE OR REPLACE TABLE DWH.STORE.DS_SE_BK_MULTIPART_NOT_NULLABLE ( BG_LOADTIMESTAMP TIMESTAMP_TZ NOT NULL ,BG_UPDATETIMESTAMP TIMESTAMP_TZ NOT NULL ,BG_SOURCESYSTEM VARCHAR NULL ,BG_ROWHASHSCD1 BINARY(32) 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 ,CONSTRAINT PK_DS_SE_BK_MULTIPART_NOT_NULLABLE PRIMARY KEY (BDC_SHOP_ID, BDC_CUSTOMER_ID) ) ; ``` The hashing view builds the row hash of the SCD1 terms. It joins them with the separator `_` and replaces NULL with `_NULL_`: ``` SHA2_BINARY(IFNULL(FIRST_NAME, '_NULL_') || '_' || IFNULL(LAST_NAME, '_NULL_') || '_' || IFNULL(CAST(DATE_OF_BIRTH AS VARCHAR), '_NULL_') || '_' || IFNULL(LOCATION, '_NULL_'), 256) AS BG_ROWHASHSCD1 ``` ### Release differences Project Settings shows the Generator Version of your project. - Generator 2.2: 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 properties Increment Management Type on Stage, and [Delete Detection Partial Terms](https://knowledge.bigenius-x.com/delete-detection-partial-terms?hsLang=en) and [Delete Detection Partial Operator](https://knowledge.bigenius-x.com/delete-detection-partial-operator?hsLang=en) on Store Entity. - 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). ### 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: [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), [Artifact Output Mode (Project)](https://knowledge.bigenius-x.com/artifact-output-mode-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) - 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) - Snowflake: [Stage](https://knowledge.bigenius-x.com/snowflake-stage?hsLang=en), [DataVault](https://knowledge.bigenius-x.com/snowflake-datavault?hsLang=en), [Datamart](https://knowledge.bigenius-x.com/snowflake-datamart?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