Microsoft Fabric - Coalesce Quality Documentation

This guide explains how to connect Coalesce Quality to a Microsoft Fabric workspace securely. We need this information so we can collect metadata about the tables in your Fabric Warehouses and Lakehouses.To finish this guide, you’ll need the following:
→ Permission to register a Microsoft Entra application (service principal) in your tenant, or an admin who can
→ A Fabric administrator to enable the tenant setting that lets service principals call Fabric
→ A workspace admin to give the service principal a role on the workspace (Contributor for query history, Member to also list the workspace’s users)⏱️ Estimated time to finish: 15 minutes.

Microsoft Fabric exposes each workspace through a shared SQL analytics endpoint that speaks T-SQL. Coalesce Quality connects to that endpoint to read the metadata of every Warehouse and Lakehouse in the workspace. A single integration covers the whole workspace. Each Warehouse and Lakehouse appears as a database, and Coalesce Quality queries across them using three-part [database].[schema].[table] names. Authentication is via a Microsoft Entra ID service principal. Fabric does not accept SQL logins, so this is the only way to connect a headless integration.

Data we collect

We read metadata from your workspace’s SQL analytics endpoint using the dedicated service principal described below. Depending on which features you enable in the integration, we ingest:

Catalog: the workspace’s databases (Warehouses and Lakehouse SQL endpoints), schemas, tables, views, stored procedures, functions, columns, data types, and constraints. Fabric constraints (primary key / unique / foreign key) are informational (NOT ENFORCED) but are still read from metadata.
SQL definitions: the T-SQL of views, stored procedures, and functions. Used for lineage and to detect references between objects.
Table metrics (on by default): row counts for anomaly detection, counted with COUNT_BIG(*) for the tables in scope (see Metrics scope below).
Users (opt-in): the workspace’s role assignments and each database’s users, to tell people from service principals in query history. The workspace role assignments need the Member or Admin workspace role.
Query history (recommended, on by default): read from the workspace’s queryinsights system views for cost attribution and SQL and column-level lineage. Requires the Contributor (or higher) workspace role, see Give the service principal a role on the workspace.

Fabric has no row count metadata, so table metrics run COUNT_BIG(*) on every table in the metrics scope, which needs SELECT. Fetch table metrics is on by default. With it off, and without monitors or SQL tests, the integration reads only your schema and DDL, never row data.

Set up access

1. Register an Entra service principal

The integration authenticates as a Microsoft Entra application (service principal) with a client secret. You can create one in the Entra admin center (App registrations → New registration), or with the Azure CLI:

# Sign in to your tenant
az login --tenant <your-tenant>

# 1. Create the app registration
APP_ID=$(az ad app create \
  --display-name "coalesce-quality-fabric-scraper" \
  --sign-in-audience AzureADMyOrg \
  --query appId -o tsv)
echo "Client (application) ID: $APP_ID"

# 2. Create the service principal for that app
az ad sp create --id "$APP_ID"

# 3. Create a client secret (the value is shown only once, store it securely)
az ad app credential reset \
  --id "$APP_ID" \
  --display-name "coalesce-quality" \
  --years 1 \
  --query "{clientId:appId, clientSecret:password, tenantId:tenant}" -o json

Take note of the Client ID, Client secret, and Tenant ID. You enter them in the Coalesce Quality UI.

2. Allow service principals to use Fabric

A Fabric administrator must enable this once for the tenant:

Open the Fabric admin portal → Tenant settings → Developer settings.
Enable “Service principals can use Fabric APIs”.
We recommend limiting it to a security group that contains only this service principal.

Without this setting the service principal cannot obtain a Fabric token and every connection fails.

3. Give the service principal a role on the workspace

A workspace admin adds the service principal to the workspace that holds the Warehouses / Lakehouses you want to monitor. Open the workspace → Manage access → Add people or groups, search for the app registration by name, and assign a role. Which role you need depends on the features you enable:

Feature	Minimum access
Databases, schemas, tables, columns, constraints	Viewer
View / procedure / function SQL definitions (lineage)	Viewer + `GRANT VIEW DEFINITION` (see step 4), or Contributor
Table metrics and monitor samples (row data)	Viewer + `GRANT SELECT` (see step 4), or Contributor
Query history (`queryinsights`)	Contributor, Member, or Admin
Audit table for SQL tests (failing rows)	Contributor, Member, or Admin
Users (workspace role assignments, through the Fabric REST API)	Member or Admin

We recommend Contributor. It covers every feature except the users listing, with no extra SQL grants. For Fetch users and service accounts, choose Member. With a lower role, the listing has only database users, and service principals show as <application id>@<tenant id>. A Viewer cannot read the queryinsights views, even with SQL grants. Choose Viewer only for tightest least privilege, and add the SQL grants in step 4 to recover SQL definitions and metrics. Query history is not available to a Viewer.

4. (Optional) Least-privilege SQL grants for a Viewer

If you assigned Viewer, grant the service principal what the enabled features read. Run this in each Warehouse you monitor, with the app registration’s display name. Fabric finds the Entra principal by name, so you do not create a user first:

GRANT VIEW DEFINITION TO [coalesce-quality-fabric-scraper];   -- SQL definitions / lineage
GRANT SELECT          TO [coalesce-quality-fabric-scraper];   -- only if table metrics / samples are enabled

These grants cannot unlock query history: queryinsights is gated by the workspace role, so leave the service principal on Contributor (or higher) if you want Fetch query history enabled. The same holds for the audit table: a Viewer cannot create it, whatever its SQL grants.

5. Network access

The Fabric SQL analytics endpoint is a public Azure endpoint (<workspace-id>.datawarehouse.fabric.microsoft.com, port 1433, TLS required). There is nothing to allowlist on the Fabric side. See the Coalesce Quality IP Whitelist only if your tenant restricts outbound access to the endpoint by IP.

Input data in the Coalesce Quality UI

Navigate to Data Sources → Add integration → Microsoft Fabric and provide:

Integration name

For example Microsoft Fabric.

SQL analytics endpoint

The workspace SQL analytics endpoint, e.g. <workspace-id>.datawarehouse.fabric.microsoft.com. You’ll find it on any Warehouse or SQL analytics endpoint in the workspace under Settings → SQL analytics endpoint (the SQL connection string). Every Warehouse and Lakehouse in the workspace shares this host, so one integration covers the whole workspace.

Default warehouse (optional)

Name of the warehouse or lakehouse to resolve unqualified queries against. Without a value, Fabric places each connection either in an arbitrary warehouse of the workspace or in master, depending on your tenant (see Microsoft’s Warehouse connectivity), so unqualified names in SQL tests and custom SQL monitors may run against the wrong warehouse or not resolve at all. Use fully qualified names in your SQL, or set this field to the warehouse or lakehouse your SQL refers to. The form warns while the field is empty.

Microsoft Entra service principal

Application (client) ID: the Entra application (client) ID from step 1.
Client secret: the service principal client secret. Stored encrypted and never displayed again in our UI.
Directory (tenant) ID (optional): only needed when the service principal lives in a different Entra tenant than the Fabric endpoint; inferred automatically when left blank.

Data collection

Fetch query history (recommended, on by default): reads the workspace’s queryinsights query history for cost attribution and SQL and column-level lineage. Needs the service principal on the Contributor (or higher) workspace role. Databases without a queryinsights schema (Lakehouse SQL endpoints, Fabric SQL databases, mirrored databases) are skipped.
Fetch users and service accounts (optional): shown once query history is on. Once a day, lists the workspace’s role assignments and each database’s users, so query history shows whether each login is a person or a service principal. Needs the Member or Admin workspace role for the role assignments, which are also where each login’s display name comes from.
Fetch table metrics (required for automatic monitors) (on by default): every 30 minutes, counts the rows of each table in the metrics scope with COUNT_BIG(*). This is cheap on Fabric’s columnar engine, but it uses capacity. Automatic freshness and volume monitors stop working when it is off.

Audit table FQN (optional)

Fully qualified name in the form [warehouse].[schema].[table], for example [analytics].[dbo].[quality_sql_test__audit], of the table where Coalesce Quality writes the failing rows of SQL tests with Save failures on. Brackets are optional, except around a name that holds a space, quote or bracket; no part may hold a single quote. Coalesce Quality creates the table if it does not exist, with one row per failing execution and the failing rows stored as JSON. See the audit table schema. Leave empty if you do not save failing rows.

The warehouse must be a Fabric Warehouse in the same workspace as the SQL analytics endpoint. A Lakehouse cannot hold the table, because its SQL analytics endpoint is read-only. Do not use the Dataflow staging items Fabric creates automatically (DataflowsStagingWarehouse, StagingWarehouseForDataflows_*); Microsoft warns of data loss for them.
The service principal needs the Contributor, Member, or Admin workspace role, see step 3.
Microsoft limits a single value in a Fabric Warehouse to 16 MB. Coalesce Quality keeps the stored rows a margin below that limit: it stores the rows from the start of the result that fit and drops the rest, while rows_count still holds the full count.

Scope

Narrow the workspace down to what you want to monitor. Both scopes support include and exclude rules at the Warehouse, schema, and table levels:

Catalog scope: which Warehouses, schemas, and tables are ingested into the catalog. Empty means everything is ingested.
Metrics scope: which tables are scanned for metrics. Empty means metrics are collected for all ingested tables.

Personal access (user OAuth)

In addition to the workspace service principal above, you can let each Coalesce Quality member authenticate as themselves against Fabric through Microsoft Entra. Queries then run under the member’s own Entra identity and respect the Fabric workspace roles, item permissions, and row-level security on that user. See Data Warehouse Access for the full model and when to enable it. To enable personal access, an Entra admin registers a separate OAuth app once (distinct from the connection service principal above), then pastes its client ID and secret into the Fabric integration’s edit page in Coalesce Quality under Warehouse access.

Register the OAuth app in Microsoft Entra

In the Entra admin center, open App registrations → New registration and give it a name, e.g. coalesce-quality-fabric-user-oauth.
Under Authentication → Add a platform → Web, add the Redirect URI(s) for the Coalesce Quality region(s) your members use, with no trailing slash:
- EU: https://app.synq.io/auth/dwh-oauth/callback
- US: https://app.us.synq.io/auth/dwh-oauth/callback
- AU: https://app.au.synq.io/auth/dwh-oauth/callback
Under API permissions → Add a permission → APIs my organization uses, search for Azure SQL Database, choose Delegated permissions, and add user_impersonation (this is the https://database.windows.net/user_impersonation scope). It needs no tenant admin consent. Each member consents once at sign-in.
Under Certificates & secrets → New client secret, create a secret and copy its value (shown only once).
Copy the Application (client) ID and the client secret.

In Settings → Integrations → (your Fabric integration) → Warehouse access → Personal credentials, paste the Client ID and Client secret. Members will then see this Fabric integration listed under Settings → Warehouse access → My credentials and can connect with their own Entra identity.

Coalesce Quality builds the Entra authorize URL (https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize), so you do not enter an authority or tenant. organizations lets each member’s home tenant resolve the app, so one app registration works across every workspace and Coalesce Quality region whose redirect URI you added in step 2.