WWoman
Databricks Partner
In response to kenmyers-8451

‎02-04-2025 08:23 AM

If you are trying to see access on a certain table query_history is a bad way to do this, parsing the SQL statement is prone to many errors. For example, if the current catalog and schema are set the query may look like "select * from my_view", where the view is accessing the table you are interested in, so from the SQL you will not be able to determine the catalog or schema. If someone creates a view onto the tables you are interested in you may not see the schema or table name in the SQL at all.

The best way to determine this is to use the lineage tables. (https://learn.microsoft.com/en-us/azure/databricks/admin/system-tables/lineage). These table track (among other things) access to metadata and data for the objects (tables and views).

To find access for a specific table from 2024-12-01 to 2024-12-31 the query would be something like:

SELECT
CASE WHEN target_type is null and source_type is not null THEN 'read'
     WHEN target_type is not null and source_type is null THEN 'write'
     WHEN target_type is not null and source_type is not null THEN 'read and write'
     ELSE 'unknown'
END as access_type,
target_table_full_name, source_type, target_type, created_by, tl.event_time
FROM system.access.table_lineage tl
WHERE lower(source_table_full_name) like 'my_catalog.my_schema.my_table'
    and tl.event_date >= '2024-12-01'
    and tl.event_date <- '2024-12-31' ;