Managing System Schemas in Databricks: Enabling, Access Control, and Dashboard Creation

If you have recently enabled Unity Catalog in your account, you should see a system catalog along with other default catalogs included with Unity Catalog. In the system catalog, you should find a single information_schema schema.

initial view of system catalog

However, Databricks offers much more than just information_schema tables. It includes audit logs, table and column lineage, billable usage and pricing, marketplace events, and more. Please see the full list of available tables.

To make these tables visible in the system catalog, they need to be enabled by the account admin via the REST API.

First, you will need to find your metastore ID. The metastore ID can be found in any Unity Catalog schema details tab from the UI. Or you can easily get it from the CLI command. Run the following CLI command to obtain the metastore ID, and copy it.

databricks unity-catalog metastores list

Next, you may want to check the tables available to you. Adjust and run the following curl command in your local CLI or via a Databricks notebook shell cell. For the Personal Access Token (PAT), create a new one or use an existing one if you have it stored. Replace the workspace address and metastore ID in the command with your own workspace and metastore ID that you copied from the results of the above command.

curl -v -X GET -H "Authorization: Bearer <PAT Token>" "https://<workspace>.cloud.databricks.com/api/2.0/unity-catalog/metastores/<metastore-id>/systemschemas"

In my case, I have the Databricks CLI (legacy version) installed on my local machine. I have stored frequently used items, such as the Databricks PAT, URL, etc., in the environment variables on my machine. Since I am using a Windows machine, my environment variables are wrapped with percentage signs.

curl -v -X GET -H "Authorization: Bearer %DATABRICKS_PROD_TOKEN%" "%DATABRICKS_PROD_URL%/api/2.0/unity-catalog/metastores/<my_metastore_id>/systemschemas"

It will output a JSON result similar to the one below:

{"schemas":[{"schema":"access","state":"<AVAILABLE OR EnableCompleted>"},{"schema":"billing","state":"<AVAILABLE OR EnableCompleted>"},{"schema":"information_schema","state":"<AVAILABLE OR EnableCompleted>"}]}

If it states ‘Available,’ this indicates that the system schema is available but not yet enabled in your account. If it displays ‘EnableCompleted,’ then the system schema is already enabled in your account, and you should be able to view it under the system catalog in Data Explorer.

In my case, all the schemas were marked as ‘Available’ because none of them had been enabled in my account.

You can enable the available schemas one at a time. Choose the schema you wish to enable, then replace your PAT, workspace address, metastore ID, and the schema name in the command below and run it. In the following command, I am enabling the billing schema.

curl -v -X PUT -H "Authorization: Bearer %DATABRICKS_PROD_TOKEN%" "%DATABRICKS_PROD_URL%/api/2.0/unity-catalog/metastores/<my_metastore_id>/systemschemas/billing"

If the command succeeds, you will see ‘HTTP/1.1 200 OK’ among the multiple lines that are returned, indicating success. Now, you can go to your workspace and check the system catalog. You should see the new schema that you have just enabled. In my case, the new billing schema appeared in the catalog.

system catalog view after enabling billing schema

As I explored the billing schema, I clicked on each table one by one to see what information was available. The ‘list_prices’ table shows the following fields:

databricks system.billing.list_prices table fields

The ‘usage’ table shows the following fields:

databricks system.billing.usage table fields

By default, only account admins have access to the system schemas, which is particularly important for the billing schema. Even if you have previously granted access to different groups or principals to the system catalog, newly enabled schemas will be visible only to account admins.

default permissions to databricks system schemas

If you want to grant permission to a group or principal, you can do so using the ‘Grant’ button found in the Permissions tab of the schema or table’s UI, or you can achieve this via SQL commands.

If you need to remove or disable one of the system tables, replace the necessary parts in the command below and execute it:

curl -v -X DELETE -H "Authorization: Bearer %DATABRICKS_PROD_TOKEN%" "%DATABRICKS_PROD_URL%/api/2.0/unity-catalog/metastores/<my_metastore_id>/systemschemas/billing"

The system schemas and tables, specific to your Databricks account’s metadata, are shared via DeltaSharing. You won’t be able to modify them. However, they can be utilized to monitor activities within your workspace or account and to create dashboards.

Here is an excellent example of a dashboard for cost management and forecasting, using the billing usage table provided by the system catalog.

example of using billing.usage table in a dashboard