Get Help

Test Microsoft Fabric

Everything in a Fabric workspace that holds data can be tested: the warehouse and the lakehouse through their SQL endpoint with a service principal, the semantic model through the workspace's XMLA endpoint. And because it is one CAT project, the lakehouse, the warehouse and the model can be tested against each other.

What you can test

Item How CAT reaches it Provider
Warehouse its SQL connection string (a TDS endpoint, port 1433) SqlServer@2
Lakehouse its SQL analytics endpoint — same kind of connection string, read-only SqlServer@2
Semantic model the workspace’s XMLA endpoint Dax@2 — exactly as a Power BI workspace model, see Test a semantic model in a workspace

Authentication is Microsoft Entra ID only — your account interactively, a service principal unattended. SQL authentication does not exist in Fabric. In CAT Studio the technologies are MS Fabric Warehouse, MS Fabric Lakehouse and MS Fabric Semantic Model.

Warehouse and lakehouse

  1. Copy the SQL connection string from the item: open the warehouse (or the lakehouse’s SQL analytics endpoint) → Settings → SQL connection string → copy. It is a server name; the warehouse (or lakehouse) name is the database — give it as Initial Catalog/Database, otherwise you land in master.
  2. A service principal with access to the item (an app registration with a secret, added to the workspace or granted on the item) — the Fabric administrator’s one-time job; interactive use works with your own account and MFA.
  3. The data source:
Data sources:
- Name: fabric warehouse
  Provider: SqlServer@2
  Connection string: >
    Server=%FABRIC_SQL_ENDPOINT%;
    Database=SalesWarehouse;
    Authentication=Active Directory Service Principal;
    User Id=%FABRIC_CLIENT_ID%;
    Password=%FABRIC_CLIENT_SECRET%;
    Encrypt=True;

A lakehouse is the same data source with its own endpoint and name; its SQL endpoint is read-only, which is all a test needs. Interactive: Authentication=Active Directory Interactive and no user/password — you are prompted, MFA included, not for schedulers.

The tests that pay off

Fabric stacks its layers in one workspace, and a CAT project sees all of them at once:

Data sources:
- Name: lakehouse
  Provider: SqlServer@2
  Connection string: "%FABRIC_LAKEHOUSE_CONNECTION_STRING%"
- Name: warehouse
  Provider: SqlServer@2
  Connection string: "%FABRIC_WAREHOUSE_CONNECTION_STRING%"
- Name: sales model
  Provider: Dax@2
  Connection string: "%FABRIC_MODEL_CONNECTION_STRING%"

Tests:
- Name: Warehouse orders equal the lakehouse
  Suite: Lakehouse vs warehouse
  First data source: lakehouse
  First query: SELECT order_id, amount FROM dbo.orders ORDER BY order_id
  Second data source: warehouse
  Second query: SELECT order_id, amount FROM sales.orders ORDER BY order_id
  Expectation: sets match
  Key: order_id

- Name: Model sales per year equal the warehouse
  Suite: Model vs warehouse
  First data source: sales model
  First query: |
    EVALUATE SUMMARIZECOLUMNS('Date'[Year], "Sales", [Total Sales])
    ORDER BY 'Date'[Year]
  Second data source: warehouse
  Second query: SELECT YEAR(order_date), SUM(amount) FROM sales.orders GROUP BY YEAR(order_date) ORDER BY 1
  Expectation: sets match
  Key: 1
  Tolerance: 0.01

Add the source system outside Fabric as a fourth data source and the chain is complete: source → lakehouse → warehouse → semantic model, each step a test. Smoke tests on every table, and tests generated from INFORMATION_SCHEMA of the warehouse (Generate tests from metadata), round it off.

Where CAT runs

Outside Fabric: your machine, a build agent, a scheduler — any CAT tool. CAT needs a .NET runtime on the machine it runs on, and a Fabric notebook does not let you install one; it also would not see the source systems the best tests compare against. (Running inside Databricks is possible — CAT in Databricks notebooks — and still usually not the right place.)

← CAT in Databricks notebooks
Test Dataverse →