topic Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique in Data Engineering

How to incrementally backup system.information_schema.table_privileges (no streaming, no unique keys

Danish11052000 — Fri, 23 Jan 2026 15:05:03 GMT

I'm trying to incrementally backup system.information_schema.table_privileges but facing challenges:

No streaming support: Is streaming supported: False
No unique columns for MERGE: All columns contain common values, no natural key combination
No timestamp columns availability: Can't track changes incrementally

Table schema:

GRANTOR (STRING, NOT NULL) - Principal that granted privilege
GRANTEE (STRING, NOT NULL) - Principal receiving privilege
TABLE_CATALOG (STRING, NOT NULL) - Catalog name
TABLE_SCHEMA (STRING, NOT NULL) - Schema name
TABLE_NAME (STRING, NOT NULL) - Table name
PRIVILEGE_TYPE (STRING, NOT NULL) - Privilege type
IS_GRANTABLE (STRING, NOT NULL) - Always 'NO'
INHERITED_FROM (STRING, NOT NULL) - Ancestor relation

Problems:

Spark streaming fails: [UNSUPPORTED_FEATURE.TABLE_OPERATION]
Batch MERGE fails: No reliable unique key columns
No last_altered/created_time for incremental logic

Any solution would be really helpful!

Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique

MoJaMa — Fri, 23 Jan 2026 15:59:28 GMT

information_schema is not a Delta Table, which is why you can't stream from it. They are basically views on top of the information coming straight from the control plane database.

Also your query is actually going to be quite slow/expensive (you probably have noted that a select * type query on it is slow since it is a union of all the views from each individual catalog's information_schema).

What's the goal here with trying to maintain a "daily" snapshot?

Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique

Danish11052000 — Fri, 23 Jan 2026 17:24:34 GMT

Since we need the previous permissions of a table that was dropped, I thought taking a backup of the system.information_schema.table_privileges table would be helpful. any idea if there is an alternative way to retrieve this information?

Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique

MoJaMa — Fri, 23 Jan 2026 20:59:45 GMT

Got it. You could try going it another way, with the audit tables. You can tweak this query to add whatever actions you want to monitor, extract the securable name, changes, securable_type etc and run it every day (1 day interval) / compare over time. You could even build alerts off it (let's say you really care about a specific catalog/schema/table) etc.

Query:

select request_params, * from `system`.`access`.`audit` WHERE service_name = 'unityCatalog' and action_name = 'updatePermissions' and event_time >= current_timestamp() - interval 30 day -- and workspace_id = 'your workspace id' -- optional ;

Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique

Hubert-Dudek — Fri, 23 Jan 2026 22:24:13 GMT

Nice catch with audit table, and actually, that one can be read as CDF stream, and we can pass action_name to filter

Re: How to incrementally backup system.information_schema.table_privileges (no streaming, no unique

MoJaMa — Fri, 23 Jan 2026 22:35:15 GMT

Thanks for pointing that out Hubert.

It's quite confusing to most (all?) users that "information_schema" is the exception inside the system catalog.

You can stream from all other schemas since they are delta-shares. but information_schema is special 🙂