Fabric Warehouse - DataVault and Mart

Applies to
Platform: Fabric Warehouse
Architecture: Data Vault · Data Mart

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

Hierarchy Flatten and Mart Hierarchy Flatten are not supported on Fabric Warehouse and are missing from the model object list below. For a model object of these types, validation reports warning ID-35003: "Fabric Warehouse uses Synapse SQL, which currently does not support the required recursive common table expressions." See Model object type not supported on Fabric Warehouse (Business Rule).

Scope and output

Platform: Microsoft Fabric Warehouse. The configuration is available since Generator 2.0.
Layers: Source, Stage, Raw Vault, Business Vault and Mart. Dataflow Modeling shows Stage in the Bronze column, both vault layers in the Silver column and the Mart model objects in the Datamart column, which is Gold. 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. The supported discovery types are listed in Source Systems per Generator Configurations.

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

Supported model objects

Mart

Capabilities and limitations

Keys: primary keys and unique constraints are created as NONCLUSTERED and NOT ENFORCED. A Mart Dimension table has a primary key on its surrogate key and a unique constraint on its business key terms; a Mart Fact table has no primary key.
Foreign keys: Foreign Key Mode offers None and Create Disabled, and the default is Create Disabled. 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 and the Mart Dimension surrogate key as the current maximum in the target plus ROW_NUMBER().
Unknown member: the deployment adds a row with the surrogate key -1 to each Mart Dimension table when the row is missing. Custom Singleton Values sets its values.
Loading order: the relationship property Use Embryo Loader is available, see Embryo Loader.
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, 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:

Hash Algorithm: SHA1; also SHA2_256 and MD5
Hash Casing: None; also Upper and Lower
Hash Concatenation Separator: _
Hash Null Placeholder: _NULL_
Hash Trim: off
Begin of Universe: 19000101
End of Universe: 99991231
Persist Validity: on
Create Result Views: on
Configure LoadControl: on
Deploy LoadControl: on
LoadControl Options: Multi-Threading Notebook
Notebook Deployment Objects Limit: 100
Deployment Options: Generate Drop Table Statements

Platform differences

The table compares the configuration with Microsoft SQL Server - DataVault and Mart of the same release:

Item	Fabric Warehouse	Microsoft SQL Server
Timestamp Default Terms	`datetime2(6)`	`datetimeoffset`
Hash Default Terms	`varbinary(20)`	`binary(20)`
Text Default Terms	`varchar`	`nvarchar`
Surrogate key column	`INT`, set by the loader	`INT IDENTITY`
Persist Validity default	on	off
Enforced foreign keys	not available	available
Index management	not available	available
Artifact Output Mode	not available	available
Target Object Comment Type	not available	available

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 Satellite BDC_WRITTEN_OFF_GOODS on the Hub of the same name generates this table. The excerpt shows the example schema rdv in place of the placeholder and adds line breaks:

CREATE TABLE [rdv].[RDV_SAT_BDC_WRITTEN_OFF_GOODS] (
     [BG_LoadTimestamp] DATETIME2(6) NOT NULL
    ,[Hub_HK] VARBINARY(20) NOT NULL
    ,[BG_SourceSystem] VARCHAR(255) NULL
    ,[BG_ValidFromTimestamp] DATETIME2(6) NOT NULL
    ,[BG_RowHash] VARBINARY(20) NOT NULL
    ,[WRITTEN_OFF_EUR] DECIMAL(18,2) NULL
    ,[REMARKS] VARCHAR(500) NULL
    ,[BU] VARCHAR(2) NOT NULL
)
;
ALTER TABLE [rdv].[RDV_SAT_BDC_WRITTEN_OFF_GOODS]
    ADD CONSTRAINT [PK_RDV_SAT_BDC_WRITTEN_OFF_GOODS]
    PRIMARY KEY NONCLUSTERED ([Hub_HK], [BG_ValidFromTimestamp])
    NOT ENFORCED;

With Microsoft SQL Server - DataVault and Mart, the five Default Terms of this Satellite 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 and Mart 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.