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 |
---|---|---|---|
|
string |
The ID of the Databricks account |
|
|
string |
The ID of the Unity Catalog> metastore |
|
|
string |
The ID of the clean room event |
|
|
string |
Name of the clean room associated with the event |
|
|
string |
The ID of central clean room |
|
|
string |
Global metastore ID of the collaborator who initiated the event |
|
|
timestamp |
Timestamp when the event took place |
|
|
string |
The type of the event. See Logged clean room events. |
|
|
struct |
The metadata of the event type |
|
|
struct |
The metadata of the event type |
|
|
struct |
The metadata of the event type |
|
|
struct |
The metadata of the event type |
|
|
struct |
The metadata of the event type |
|
Logged clean room events
This system table logs the following events:
CLEAN_ROOM_CREATED
: User creates a new clean roomCLEAN_ROOM_DELETED
: User deletes a clean roomRUN_NOTEBOOK_STARTED
: User initates a notebook run in a clean roomRUN_NOTEBOOK_COMPLETED
: A notebook run is completed. Check therun_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