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?.

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