At the time of writing (29th June 2023) the following T-SQL commands are not supported in a Fabric Data Warehouse.
- Identity Columns
- Merge
To see the full list of T-SQL limitations please refer to the documentation link below:
https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area
Here at Purple Frog, all of our Data Warehouses are knitted together using surrogate keys to link Dimension tables to Fact tables following the Kimball design principals. For example, our Fact Sales table would reference a surrogate key in our Dim Product Table and perhaps our Dim Store Table.
Similarly, we use the Merge statement to blend our curated and transformed data into our Dimension or Fact based on identifying Business Keys (unique identifiers) and SCD logic depending on if we want to apply Type 0, 1 or 2 SCD logic.
So, how do we overcome these two major limitations? Quite simply, we fall back to traditional T-SQL with the use of ROW_NUMBER, Stored Procedures and DML INSERT / UPDATE Statements.
ROW_NUMBER can be used to generate the equivalent of an “Identity” column in your Dimension or Fact table. By using ROW_NUMBER alongside the use of a variable to retrieve the current max “Identity” column value you’ll be able to replicate the functionality offered previously be Identity Columns.
For example:
Remember you’ll need to order any ROW_NUMBER function by your Business Keys. In my example above these are ProductID and ProductName.
Type 0, 1 and 2 SCDs can be easily managed through the use of Stored Procedures and alongside DML INSERT / UPDATE statements. By building in the ROW_NUMBER logic from above into your Stored Procedures you should quite easily be able to replicate Identity Columns and Type 0, 1 and 2 SCD Merge Statements.
Below is an example of Type 2 SCD Dimension table created in a Fabric environment where the Type 2 change we are tracking is Price.
Let me know if you’ve found a better way of overcoming these two limitations in the Fabric Data Warehouse world…
