Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Fabric Warehouse Data Vault

Applies to
Platform: Fabric Warehouse
Architecture: Data Vault

Fabric Warehouse Data Vault generates a Stage, Raw Vault and Business Vault for Fabric Warehouse as T-SQL notebooks with a notebook load control. Select the Generator Configuration Fabric Warehouse DataVault when you add a project whose Data Vault runs in a Fabric Warehouse. This article lists what the configuration supports and where it differs from Microsoft SQL Server Data Vault.

Hierarchy Flatten is not supported on Fabric Warehouse and is missing from the model object list below. For a model object of this type, validation reports warning ID-35003: "Fabric Warehouse uses Synapse SQL, which currently does not support the required recursive common table expressions."

Scope and output

  • Platform: Microsoft Fabric Warehouse. The configuration is available since Generator 2.0.
  • Layers: Source, Stage, Raw Vault and Business Vault. Dataflow Modeling shows Stage in the Bronze column and both vault layers in the Silver column. See Understand the Layering concept.
  • Output: T-SQL deployment notebooks, a deployment pipeline definition and Python load control notebooks, described in Fabric Warehouse - Artifacts.

Prerequisites

  • A Fabric workspace with a warehouse for the target layers. The deployment notebooks switch to the existing warehouse with USE and create missing schemas; they create no warehouse.
  • A lakehouse for the load control execution log. The load control deployment notebook creates the log as a Delta table there.
  • An Azure Key Vault with the tenant ID, client ID and client secret of a service principal. The load control notebook uses them to connect to the warehouse.

A setup example is in Fabric Warehouse - Target environment. The placeholders for the warehouse, schema, lakehouse, Key Vault and secret names are listed in Fabric Warehouse - Placeholder values.

Sources and linked projects

Stage source views read each source table by its three-part name [<database>].[<schema>].[<table>]. The source system has the type Microsoft Fabric Warehouse, and its database and schema names are placeholders that you fill in when you replace the placeholders. The supported discovery types are listed in Source systems per generator configuration.

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

Supported model objects

Source and Stage

Raw Vault

Business Vault

Capabilities and limitations

  • Keys: primary keys and unique constraints are created as NONCLUSTERED and NOT ENFORCED.
  • Foreign keys: Foreign Key Mode offers None and Create Disabled, and the default is Create Disabled. Create Disabled creates the foreign keys as NOT ENFORCED constraints; None creates no constraint. Create Enforced is not available.
  • Indexes: index management is not available, and the generated tables have no indexes.
  • Identity values: the tables have no IDENTITY columns. A loader computes identity values such as BG_ID as the current maximum in the target plus ROW_NUMBER().
  • Deployment: a deployment notebook holds at most the number of objects set in Notebook Deployment Objects Limit; further objects go to the next notebook. The pipeline definition runs the load control deployment first, then the deployment notebooks in order. See Fabric Warehouse - Deployment.
  • Loading: LoadControl Options offers only Multi-Threading Notebook. This native load control of Fabric Warehouse is a Python notebook that runs the loaders in dependency order. See Fabric Warehouse - Load data - Native Load Control.
  • Data types: validation reports warning ID-35000 when the length, precision or scale of a term does not fit its Fabric Warehouse data type. Warning ID-35001 flags a business key term of type varchar(max) or varbinary(max), because constraints on it could block the deployment.

Project properties

The configuration offers these project properties, with their defaults:

Platform differences

The table compares the configuration with Microsoft SQL Server Data Vault of the same release:

ItemFabric WarehouseMicrosoft SQL Server
Timestamp Default Termsdatetime2(6)datetimeoffset
Hash Default Termsvarbinary(20)binary(20)
Text Default Termsvarcharnvarchar
Persist Validity defaultonoff
Enforced foreign keysnot availableavailable
Index managementnot availableavailable
Artifact Output Modenot availableavailable
Target Object Comment Typenot availableavailable

LoadControl Options offers Multi-Threading Notebook on Fabric Warehouse and Native Load Control or Airflow on Microsoft SQL Server. Notebook Deployment Objects Limit exists only on Fabric Warehouse.

These Default Terms belong to each group:

  • Timestamps: BG_LoadTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_UpdateTimestamp, BG_EffectiveTimestamp, DVPIT_ForeignKey_LoadDate
  • Hash keys and row hashes: Hub_HK, Link_HK, DrivingKeyLink_HK, PIT_HK, DVLINK_ForeignKey_Identity, DVPIT_ForeignKey_Identity, BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
  • Text, same lengths on both platforms: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus

Example

A Hub common_customer with the business key term CUSTOMER_ID generates this table. The excerpt shows the example schema rdv in place of the placeholder and adds line breaks:

CREATE TABLE [rdv].[RDV_HUB_common_customer] (
     [BG_LoadTimestamp] DATETIME2(6) NOT NULL
    ,[Hub_HK] VARBINARY(20) NOT NULL
    ,[BG_SourceSystem] VARCHAR(255) NULL
    ,[CUSTOMER_ID] INT NOT NULL
)
;
ALTER TABLE [rdv].[RDV_HUB_common_customer]
    ADD CONSTRAINT [PK_RDV_HUB_common_customer]
    PRIMARY KEY NONCLUSTERED ([Hub_HK]) NOT ENFORCED;
ALTER TABLE [rdv].[RDV_HUB_common_customer]
    ADD CONSTRAINT [UC_RDV_HUB_common_customer]
    UNIQUE NONCLUSTERED ([CUSTOMER_ID]) NOT ENFORCED;

With Microsoft SQL Server Data Vault, the three Default Terms of this Hub get the types datetimeoffset, binary(20) and nvarchar(255).

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.0: first release of the configuration.
  • Generator 2.2: Vault Out; the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object property Increment Management Type; the relationship property Use Embryo Loader; the validation warnings ID-35000 to ID-35003, including the Hierarchy Flatten warning.
  • 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.

Related articles