Deploying Case Insensitive Warehouse using Deployment Pipelines

Overview

I wrote a blog post last year on Fabric Warehouses and the new Case Insensitive collation, you can read more here. One of the issues that Case Insensitive Warehouses have is when deploying using Deployment Pipelines, when you deploy it changes the collation back to Case Sensitive…not ideal. Good news though, this limitation looks to have been lifted. Let’s take a look.

---

Walkthrough

Let’s go through an example, we’ll create a Case Insensitive Warehouse, create tables, setup a Deployment Pipeline, and review the results.

Create Case Insensitive Warehouse

To create a new Case Insensitive Warehouse, you can use this script run within a Fabric Notebook within the workspace you want to create in, it uses Semantic Link to create the Warehouse:

#sempy version 0.4.0 or higher
!pip install semantic-link --q 
import json
import sempy.fabric as fabric

--get the current workspace id
workspace_id=spark.conf.get("trident.workspace.id")

#Instantiate the client
client = fabric.FabricRestClient()

#create the payload
uri = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/items"
payload = { 
  "type": "Warehouse", 
  "displayName": "DE_DW_CASEINSENSITIVE", 
  "description": "Case Insensitive Warehouse", 
  "creationPayload": { 
    "defaultCollation": "Latin1_General_100_CI_AS_KS_WS_SC_UTF8" 
  } 
}

# Call the REST API
response = client.post(uri,json= payload)
display(response)

Once the Warehouse has been created, we can check to make sure it’s Case Insensitive by running this T-SQL script on the Warehouse:

SELECT collation_name 
FROM sys.databases
WHERE Name = 'DE_DW_CASEINSENSITIVE';

Create Tables

Let’s create a couple of tables within the Warehouse.

CREATE TABLE dbo.DimProduct
(
    ProductKey INT,
    ProductName VARCHAR(255),
    ProductModel VARCHAR(50)
);
GO

CREATE TABLE dbo.FactWebTelemetry
(
    ProductKey INT,
    PageViews INT,
    Purchases INT
);
GO

Deployment Pipelines

Now it’s time to deploy to another workspace. Create a new Deployment Pipeline with the relevant number of stages, and assign the workspaces. E.G my development workspace where I created the initial Case Insensitive Warehouse is PoC Main so I’ve assigned to the first stage Development. It’ll deploy to a Test workspace called WH Test.

Choose the Case Insensitive Warehouse, then click Deploy

After the deployment has succeeded, run the T-SQL Collation script on the deployed Warehouse in the test workspace and we should see the Case Insensitive collation.