Snowflake dbt - DataVault

Applies to
Platform: Snowflake dbt
Architecture: Data Vault

Snowflake dbt - DataVault generates a Stage, Raw Vault and Business Vault for Snowflake as a dbt project. Select the Generator Configuration Snowflake Dbt DataVault when you add a project and want to load your Data Vault with dbt on Snowflake. This article lists what the configuration supports and where it differs from Snowflake - DataVault.

The configuration offers no load control, no deployment scripts and no data quality checks. The full list of features that are not available is under Capabilities and limitations.

Scope and output

Platform: Snowflake. The configuration is available since Generator 2.4. It is the only Snowflake dbt configuration.
Layers: Source, Stage, Raw Vault and Business Vault. See Understand the Layering concept.
Output: one dbt project with SQL models, model YAML files and macros, described in dbt - Artifacts. dbt creates and loads the tables, see dbt - Deployment.

Prerequisites

A Snowflake account, dbt Core with the adapter dbt-snowflake, and a connection profile, as described in Snowflake dbt - Target environment. dbt 2 is not supported with Snowflake dbt.
The values in dbt_project.yml, listed in dbt - Placeholder values.

Sources

Source systems have the type Snowflake, as with Snowflake - DataVault. To create source model objects from Snowflake tables, see Discover a Snowflake database. The database and schema of each source system are variables in dbt_project.yml.

Supported model objects

Capabilities and limitations

Loading: dbt runs the models in the order of their references. A model object with more than one dataflow gets one loader model per dataflow.
Incremental reads: a model reads only new source rows when BG_IncrementalFilter is mapped to the source's BG_LoadTimestamp, see Configure an incremental load.
Keys: the model YAML files declare primary keys, unique constraints on business keys and, with Foreign Key Mode Create Disabled (default), foreign keys. Foreign Key Mode offers None and Create Disabled. Snowflake creates these constraints as not enforced; only NOT NULL is enforced.
Tables: Stage models and Cached model objects are transient tables, see dbt - Artifacts.
Hashes: Hash Algorithm offers HASH, SHA2-256 and MD5. Hash keys and row hashes have the data type BINARY(32).
Data quality: data quality checks are not available.
Reference Table: the table has no unknown-member row.

Implementation Type offers these values per model object type:

Hubs, Links, all satellite types and Reference Table: Permanent
PIT and Business PIT: Permanent or Cached
Stage: Cached (default) or Virtual
RawVault Composite, BusinessVault Composite and Vault Out: Cached or Virtual (default)
Hierarchy Flatten: Cached
Current View, History View and their Business counterparts: Virtual

These properties of Snowflake - DataVault are not available:

Model object: Load Caching, Use Embryo Loader (see Embryo Loader), Increment Management Type
Dataflow: Use Stream, Warehouse Placeholder Variable Name, Ignore Downstream Dependencies
Project: Configure LoadControl, LoadControl Options, Deployment Options, Artifact Output Mode

Project properties

Project Settings shows these properties in the groups Governance, Hashing, Historization and Implementation, with their defaults:

Apply Sensitivity Level Tags: off
Sensitivity Level Tag Name: SENSITIVITY_LEVEL
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: off
Target Object Comment Type: None; also Tables and Views

Model object properties: Deduplication Method, Sort Order within Partition, Implementation Type, Version Valid From Term, Delete Detection Method, Foreign Key Mode, Custom Singleton Values, Driving Key, Calculate Effectivity End From Begin and Excluded from Delta Detection.

Platform differences

Compared with Snowflake - DataVault of the same release:

Output: a dbt project with SQL models instead of deployment scripts and stored procedures.
Loading: dbt runs the models; there is no load control and no Apache Airflow option.
Create Result Views: off by default; on by default in Snowflake - DataVault.
Target Object Comment Type: no value Tables.

Compared with Databricks dbt - DataVault:

Each layer in dbt_project.yml has a warehouse, +snowflake_warehouse.
Hash Algorithm offers three values instead of SHA-256 only.
The properties Use Unique Constraints, File Format, Optimization Method and Optimization Method Columns are not available. The unique constraints are always declared.

Example

A Hub common_customer with the business key term CUSTOMER_ID gives the models rdv_hub_common_customer_source and rdv_hub_common_customer. The excerpt of models/rawvault/models_rawvault.yml declares its keys:

  - name: rdv_hub_common_customer
    constraints:
      - type: primary_key
        columns: [HUB_HK]
        name: PK_RDV_HUB_COMMON_CUSTOMER
      - type: unique
        columns: [CUSTOMER_ID]
        name: UC_RDV_HUB_COMMON_CUSTOMER
    columns:
      - name: BG_LOADTIMESTAMP
        data_type: TIMESTAMP_TZ
      - name: BG_SOURCESYSTEM
        data_type: VARCHAR
      - name: CUSTOMER_ID
        data_type: INTEGER
      - name: HUB_HK
        data_type: BINARY(32)

The excerpt leaves out descriptions, the not_null constraints and warn_unsupported: false. After the run, INFORMATION_SCHEMA.TABLE_CONSTRAINTS of the target database lists both constraints of the table with ENFORCED = NO.