Clean room events system table reference

Preview

This system table is in Public Preview. To access the table, the schema must be enabled in your system catalog. For more information, see Enable system table schemas.

The clean room events table records actions taken by you or your collaborators on clean rooms (Public Preview) in your account. This table includes regional data from across your account.

For more information on clean rooms, see What is Databricks Clean Rooms?.

Table path: This system table is located at clean_room_events.

Clean room events system table schema

The clean room events system table uses the following schema:

Column name

Data type

Description

Example

account_id

string

The ID of the Databricks account

7af234db-66d7-4db3

-bbf0-956098224879

metastore_id

string

The ID of the Unity Catalog> metastore

5a31ba44-bbf4-4174

-bf33-e1fa078e6765

event_id

string

The ID of the clean room event

db52a413-7a0a-4d49

-b742-7ae5f06bc4b2

clean_room_name

string

Name of the clean room associated with the event

market-analysis

central_clean_room_id

string

The ID of central clean room

e01b6a78-1336-47e1

-b63d-3e49aa5b627

initiator_global_metastore_id

string

Global metastore ID of the collaborator who initiated the event

aws:us-west-2:ec22936d

-cd29-4421-a88a-883fb356776a

event_time

timestamp

Timestamp when the event took place

2023-01-01T01:01:01.123

event_type

string

The type of the event. See Logged clean room events.

CLEAN_ROOM_CREATED

clean_room_created_metadata

struct

The metadata of the event type CLEAN_ROOM_CREATED

{

"collaborators": [

{"collaborator_global_metastore_id":

"aws:us-west-2:ec22936d-cd29-4421-a88a-883fb356776a"},

{"collaborator_global_metastore_id":

"azure:wesus:”3be05a26-5a83-478c-8428-9ef9aa67b5d0"}]

}

clean_room_deleted_metadata

struct

The metadata of the event type CLEAN_ROOM_DELETED

{

"central_clean_room_id": "af0d4563-2267-412c-9d4a

-8a59c4895c18"

}

run_notebook_started_metadata

struct

The metadata of the event type RUN_NOTEBOOK_STARTED

{

"notebook_name": "market analysis",

"notebook_checksum": "7072696E7468656C6C6F776F7264",

"run_id": "TaskRunId-634124444694206"

}

run_notebook_completed_metadata

struct

The metadata of the event type RUN_NOTEBOOK_COMPLETED

{

"notebook_name": "market analysis",

"run_id": "TaskRunId-634124444694206",

"state": "SUCCESS",

"duration_in_seconds": 156

}

clean_room_assets_updated

struct

The metadata of the event type CLEAN_ROOM_ASSETS_UPDATED

{

"added_assets": [{

"data_object_type": "TABLE",

"name": "sales",

"catalog": "demo"}],

"updated_assets":[],

"removed_assets": []

}

Logged clean room events

This system table logs the following events:

  • CLEAN_ROOM_CREATED: User creates a new clean room

  • CLEAN_ROOM_DELETED: User deletes a clean room

  • RUN_NOTEBOOK_STARTED: User initates a notebook run in a clean room

  • RUN_NOTEBOOK_COMPLETED: A notebook run is completed. Check the run_notebook_completed_metadata to see if the run was successfull or not.

  • CLEAN_ROOM_ASSETS_UPDATED: A clean room’s assets are updated. This could be adding, updating, or deleting assets.

Monitor clean room billable usage

To learn more about clean room usage and costs, you can join this table with the billing.usage table using the central_clean_room_id column. In the billing.usage table, clean room usage is attributed using the usage_metadata.central_clean_room_id key.

Sample queries

The following sample queries help you gain insight into clean room activity in your account:

List all events from a specified clean room

Note

Replace <clean_room_name> with your specified clean room name.

SELECT
 event_time, initiator_global_metastore_id, event_type,
 CASE
   WHEN event_type = 'CLEAN_ROOM_CREATED' THEN to_json(clean_room_created_metadata)
   WHEN event_type = 'CLEAN_ROOM_DELETED' THEN to_json(clean_room_deleted_metadata)
   WHEN event_type = 'RUN_NOTEBOOK_STARTED' THEN to_json(run_notebook_started_metadata)
   WHEN event_type = 'RUN_NOTEBOOK_COMPLETED' THEN to_json(run_notebook_completed_metadata)
   WHEN event_type = 'CLEAN_ROOM_ASSETS_UPDATED' THEN to_json(clean_room_assets_updated_metadata)
   ELSE ""
 END AS event_metadata
 FROM system.access.clean_room_events
 WHERE clean_room_name = '<clean_room_name>'
ORDER BY event_time DESC

List all events initiated by a specified collaborator

Note

Replace <collaborator global metastore ID> with the collaborator’s global metastore ID, also known as the sharing identifier.

SELECT
 clean_room_name, event_type, event_time,
 CASE
   WHEN event_type = 'CLEAN_ROOM_CREATED' THEN to_json(clean_room_created_metadata)
   WHEN event_type = 'CLEAN_ROOM_DELETED' THEN to_json(clean_room_deleted_metadata)
   WHEN event_type = 'RUN_NOTEBOOK_STARTED' THEN to_json(run_notebook_started_metadata)
   WHEN event_type = 'RUN_NOTEBOOK_COMPLETED' THEN to_json(run_notebook_completed_metadata)
   WHEN event_type = 'CLEAN_ROOM_ASSETS_UPDATED' THEN to_json(clean_room_assets_updated_metadata)
   ELSE ""
 END AS event_metadata
 FROM system.access.clean_room_events
 WHERE initiator_global_metastore_id = '<collaborator global metastore ID>'
 ORDER BY clean_room_name, event_time DESC

List all relevant collaborators of the clean rooms

SELECT DISTINCT explode(transform(clean_room_created_metadata.collaborators, x -> x.collaborator_global_metastore_id)) AS collaborator
FROM system.access.clean_room_events
WHERE event_type = 'CLEAN_ROOM_CREATED'

List the slowest notebook runs

SELECT
 clean_room_name,
 run_notebook_completed_metadata.notebook_name AS notebook_name,
 run_notebook_completed_metadata.duration_in_seconds AS duration_in_seconds,
 run_notebook_completed_metadata.run_id AS run_id
FROM system.access.clean_room_events
WHERE event_type = 'RUN_NOTEBOOK_COMPLETED'
 AND run_notebook_completed_metadata.state = 'SUCCESS'
 AND run_notebook_completed_metadata.duration_in_seconds IS NOT NULL
ORDER BY duration_in_seconds DESC

Success run rate for every notebook

SELECT
 clean_room_name,
 run_notebook_completed_metadata.notebook_name AS notebook_name,
 ROUND(COUNT(CASE WHEN run_notebook_completed_metadata.state = 'SUCCESS' THEN 1 END) * 1.0 / COUNT(*) * 100) AS success_rate
FROM system.access.clean_room_events
WHERE event_type = 'RUN_NOTEBOOK_COMPLETED'
GROUP BY clean_room_name, notebook_name
ORDER BY clean_room_name, notebook_name

List all the events where a specified table was added to clean rooms

Note

Replace the table name default.sales with your specified table name.

SELECT
 clean_room_name, initiator_global_metastore_id, event_time, clean_room_assets_updated_metadata
FROM system.access.clean_room_events
WHERE event_type = 'CLEAN_ROOM_ASSETS_UPDATED'
 AND exists(clean_room_assets_updated_metadata.added_assets, item -> item.data_object_type = 'TABLE' AND item.name = 'default.sales')
ORDER BY clean_room_name, event_time DESC