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

Fabric Warehouse - Data Store

Applies to
Platform: Fabric Warehouse
Architecture: Data Store

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

Scope and output

  • Platform: Microsoft Fabric Warehouse. The configuration is available since Generator 2.0.
  • Layers: Source, Stage and Store. Dataflow Modeling shows Stage and the Data Store in the Bronze 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 Configurations.

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

Supported model objects

Implementation Type offers these values:

  • Stage: Cached (default) or Virtual
  • Store Entity: Permanent
  • Store Composite: Virtual (default) or Cached

Capabilities and limitations

  • 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. Keys are created as NONCLUSTERED and NOT ENFORCED.
  • Change detection: Delta Detection on the Store Entity sets how rows are compared. The default Hash Value Comparison compares BG_RowHashScd1 and BG_RowHashScd2.
  • Delete detection: Configure delete detection adds the column BG_IsDeleted and a delete detection table, view and loader.
  • Data quality: Store Entity supports data quality rules.
  • Indexes: index management is not available, and the generated tables have no indexes.
  • Validity: with the default Persist Validity on, historized tables have a BG_ValidToTimestamp column.
  • 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:

Platform differences

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

ItemFabric WarehouseMicrosoft SQL Server
Timestamp Default Termsdatetime2(6)datetimeoffset
Hash Default Termsvarbinary(20)binary(20)
Text Default Termsvarcharnvarchar
Primary keysnonclustered, not enforcedclustered
Persist Validity defaultonoff
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_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp
  • Row hashes: BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
  • Text, same lengths on both platforms: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus

Example

A Store Entity dd_deletedset_scd_0_1_2 with the business key term CUSTOMER_ID has SCD1 and SCD2 terms and delete detection. It generates this table. The excerpt shows the example schema store in place of the placeholder, leaves out seven columns and adds line breaks:

CREATE TABLE [store].[DS_SE_dd_deletedset_scd_0_1_2] (
     [BG_LoadTimestamp] DATETIME2(6) NOT NULL
    ,[BG_UpdateTimestamp] DATETIME2(6) NOT NULL
    ,[BG_SourceSystem] VARCHAR(255) NULL
    ,[BG_ValidFromTimestamp] DATETIME2(6) NOT NULL
    ,[BG_ValidToTimestamp] DATETIME2(6) NULL
    ,[BG_IsDeleted] BIT NOT NULL
    ,[BG_RowHashScd1] VARBINARY(20) NOT NULL
    ,[BG_RowHashScd2] VARBINARY(20) NOT NULL
    ,[CUSTOMER_ID] INT NOT NULL
    ,[CUSTOMER] VARCHAR(100) NOT NULL
    ,[CUSTOMER_TYPE] VARCHAR(100) NOT NULL
)
;
ALTER TABLE [store].[DS_SE_dd_deletedset_scd_0_1_2]
    ADD CONSTRAINT [PK_DS_SE_dd_deletedset_scd_0_1_2]
    PRIMARY KEY NONCLUSTERED ([BG_ValidFromTimestamp], [CUSTOMER_ID])
    NOT ENFORCED;

The table holds every version of a customer; the primary key starts with BG_ValidFromTimestamp. With Microsoft SQL Server - Data Store, the same table has no BG_ValidToTimestamp column, and its primary key is clustered.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.0: first release of the configuration.
  • Generator 2.2: the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object properties Increment Management Type on Stage, and Delete Detection Partial Terms and Delete Detection Partial Operator on Store Entity; the validation warnings ID-35000 and ID-35001.

Related articles