Azure Databricks Access
CXM reads the cost, usage and configuration of your Azure Databricks workspaces to recommend optimizations. This guide adds Databricks access to an existing CXM Azure integration. You do not create a new identity: CXM uses the same Service Principal (cxm-asset-crawler).
What CXM Reads
| Data | Source | Access |
|---|---|---|
| Databricks usage and list prices | system.billing | SELECT |
| Clusters, node types and warehouse events | system.compute | SELECT |
| Jobs and pipeline runs | system.lakeflow | SELECT |
| SQL query history (duration, size, warehouse) | system.query | SELECT |
| Audit events | system.access | SELECT |
| Databricks commit unit (DBCU) reservations | Azure reservations | Reservations Reader |
system.query holds the SQL text and the user of each query. system.access holds the audit events of the account. CXM uses this data only to find cost optimizations. To exclude them, skip the system.query and system.access lines of the grants in Step 2.4. With Terraform, remove query and access from the system_schemas input of the submodule.
Prerequisites
- The CXM Azure integration is set up (Terraform or Manual). Keep its Client ID and its Tenant ID at hand.
- Your workspaces are attached to a Unity Catalog metastore.
- A workspace admin of one workspace per metastore adds the Service Principal and creates the SQL warehouse.
- An account admin or a metastore admin enables the system schemas.
- A user who is both a metastore admin and an account admin runs the grants. A workspace admin cannot make grants on objects that the workspace admin does not own (Admin privileges in Unity Catalog, Grant access to system tables).
- A metastore that automatic Unity Catalog enablement created may have no metastore admin. In this case, an account admin assigns the metastore admin role first (same two pages).
- For DBCU reservations: a user with the User Access Administrator role at root scope
/(Delegate roles to all reservations). A Global Administrator gets this role through elevated access.
With Terraform
The Terraform module does Step 1 and Step 2, except the enable of the system schemas. The manual steps below are for a reader without Terraform.
- Step 1: set
enable_reservations_reader = trueon the root modulegithub.com/cxmlabs/terraform-azure-cxm-integration, the module of the Terraform setup. If you elevated access for the apply, give the identity that runs Terraform Reservations Reader at/providers/Microsoft.Capacitybefore you remove the elevated access. Otherwise, later plans cannot read the assignment (reservations section of the root README). - Step 2: first enable the system schemas by hand, as Step 2, item 3 says. The module does not enable them. Then call the submodule
github.com/cxmlabs/terraform-azure-cxm-integration//terraform-azure-databricks-accessonce per metastore. The submodule adds the Service Principal, the grants, thecxmwarehouse and Can use. - Step 3: send the Tenant ID, and the values that
terraform output cxm_databricks_valuesprints for each submodule call.
One identity holds the User Access Administrator role at root scope / and the workspace admin, metastore admin and account admin roles, and runs the apply of this configuration one time. Only if no single identity can hold all these roles, put the submodule in a configuration of its own, with the Client ID as a literal and no depends_on.
terraform {
required_providers {
databricks = {
source = "databricks/databricks"
}
}
}
provider "databricks" {
# The URL of one workspace of the metastore
host = "https://adb-1234567890123456.7.azuredatabricks.net"
}
module "cxm_integration" {
source = "github.com/cxmlabs/terraform-azure-cxm-integration"
# Merge into the main.tf of the Terraform setup
enable_reservations_reader = true
}
module "cxm_databricks_access" {
source = "github.com/cxmlabs/terraform-azure-cxm-integration//terraform-azure-databricks-access"
service_principal_application_id = module.cxm_integration.client_id
depends_on = [module.cxm_integration]
}
output "cxm_databricks_values" {
value = {
workspace_url = module.cxm_databricks_access.workspace_url
sql_warehouse_id = module.cxm_databricks_access.sql_warehouse_id
}
}
The submodule README gives the inputs and the provider alias for a second metastore. If you already did the manual steps:
- The reservations section of the root README gives the
importblock of the Reservations Reader assignment. - If you created the
cxmwarehouse by hand, delete it before the apply, or import it intomodule.cxm_databricks_access.databricks_sql_endpoint.cxm. Otherwise, the apply creates a secondcxmwarehouse.
Step 1: Give Read Access to Reservations
A user with the User Access Administrator role at root scope / runs this step. A Global Administrator gets this role through elevated access.
Azure reservations, DBCU reservations included, are outside subscriptions. The subscription Reader role does not cover them. Run:
az role assignment create \
--assignee <client-id> \
--role "Reservations Reader" \
--scope /providers/Microsoft.Capacity
If you are a Global Administrator who elevated access for this step, remove the elevated access after the command (Elevate access).
Step 2: Give Read Access to Each Workspace
system.billing.usage and system.billing.list_prices hold the whole account. system.compute, system.lakeflow, system.query.history and the workspace events of system.access.audit hold only the region of the metastore. An account has one metastore per region. Do this step in one workspace per metastore (System tables).
-
As a workspace admin, open Settings > Identity and access > Service principals > Manage > Add service principal > Add new. Choose Microsoft Entra ID managed, and paste the application (client) ID of the Azure integration (Manage service principals). A Databricks managed Service Principal does not map the Entra ID token of CXM. Give it the Databricks SQL access entitlement.
-
As a workspace admin, open SQL Warehouses > Create SQL warehouse. Name it
cxm, set the size to 2X-Small and auto-stop to 10 minutes. Set Scaling to a minimum and a maximum of 1 cluster. In its Permissions, give the CXM Service Principal Can use. -
As a metastore admin or an account admin, enable the system schemas (system-schemas commands).
databricks metastores currentprints themetastore_idof the workspace. Use it asMETASTORE_ID. Then list the system schemas:databricks metastores currentdatabricks system-schemas list METASTORE_IDEnable each schema whose
stateisAVAILABLE, one run for each schema. Skip a schema inENABLE_COMPLETEDorMANAGED, because it is already on. A schema inUNAVAILABLEcannot be enabled. Write each schema name assystem-schemas listprints it:databricks system-schemas enable METASTORE_ID billingdatabricks system-schemas enable METASTORE_ID computedatabricks system-schemas enable METASTORE_ID lakeflowdatabricks system-schemas enable METASTORE_ID querydatabricks system-schemas enable METASTORE_ID access -
As a user who is both a metastore admin and an account admin, open the SQL editor, select the
cxmwarehouse or another SQL warehouse that you can use, and run these grants. Replace<client-id>with the Client ID:GRANT USE CATALOG ON CATALOG system TO `<client-id>`;GRANT USE SCHEMA, SELECT ON SCHEMA system.billing TO `<client-id>`;GRANT USE SCHEMA, SELECT ON SCHEMA system.compute TO `<client-id>`;GRANT USE SCHEMA, SELECT ON SCHEMA system.lakeflow TO `<client-id>`;GRANT USE SCHEMA, SELECT ON SCHEMA system.query TO `<client-id>`;GRANT USE SCHEMA, SELECT ON SCHEMA system.access TO `<client-id>`;The grants are additive. They do not change the grants of other users.
Step 3: Provide Values to CXM
Share the following with CXM, for each workspace of Step 2. CXM uses the Tenant ID to choose the Service Principal when your organization has more than one Azure datasource:
Tenant ID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Workspace URL: https://adb-1234567890123456.7.azuredatabricks.net
SQL Warehouse ID: xxxxxxxxxxxxxxxx
Security Considerations
- What this page grants: Reservations Reader on the reservations,
USE CATALOGon thesystemcatalog,USE SCHEMAandSELECTon the system schemas of Step 2.4, and Can use on thecxmwarehouse. CXM runs only read queries. - Default workspace access: The Service Principal joins the workspace
usersgroup, as each workspace user does. On a workspace with a workspace catalog, workspace users getUSE CATALOGon that catalog, andUSE SCHEMA,CREATE TABLE,CREATE VOLUME,CREATE MODEL,CREATE FUNCTIONandCREATE MATERIALIZED VIEWon itsdefaultschema (Admin privileges in Unity Catalog). The Databricks SQL access entitlement gives access to queries, dashboards and SQL warehouses (Entitlements). To see these grants, runSHOW GRANTS ON CATALOG <workspace-catalog>;andSHOW GRANTS ON SCHEMA <workspace-catalog>.default;. - Separate cost: CXM queries run on the
cxmwarehouse, so their cost stays separate from your other warehouses. - Audit trail: Each CXM query is in
system.query.history, under the CXM Service Principal.