topic Re: Centralized Location of Table History/Timestamps in Unity Catalog in Data Engineering

Centralized Location of Table History/Timestamps in Unity Catalog

Eric_Kieft — Thu, 20 Mar 2025 15:28:33 GMT

Is there a centralized location in Unity Catalog that retains the table history, specifically the last timestamp, for managed delta tables?

DESCRIBE HISTORY will provide it for a specific table, but I would like to get it for a number of tables.

information_schema.tables contains last_altered, however this appears to be intended for tracking changes to the table structure itself and does not change with all updates, such as inserts.

Re: Centralized Location of Table History/Timestamps in Unity Catalog

noorbasha534 — Thu, 20 Mar 2025 16:06:41 GMT

exactly I was looking for the same. Snowflake provides 'last update' column which doesn't include DDL operations. Also, if Databricks provides such a column, it needs to be ensured that not just DDL, maintenance operations (VACUUM, OPTIMIZE etc) are excluded....

Re: Centralized Location of Table History/Timestamps in Unity Catalog

noorbasha534 — Thu, 20 Mar 2025 16:07:32 GMT

someone from observability team of Databricks can pitch in here. We have a need to display all stale tables based on such a date column.

Re: Centralized Location of Table History/Timestamps in Unity Catalog

Brahmareddy — Thu, 20 Mar 2025 16:52:32 GMT

Hi Eric_Kieft,

How are you doing today?, As per my understanding, yeah, Unity Catalog doesn’t currently provide a direct system table that tracks all table modifications (including inserts/updates) across multiple managed Delta tables. DESCRIBE HISTORY works per table, but for multiple tables, you'd need a workaround.

A good approach is to use system tables in Unity Catalog, specifically system.access.table_changes or system.access.audit_logs, to track changes at a broader level. Alternatively, if you need a quick way to get the last modified timestamp for multiple tables, you could query Delta’s transaction logs stored in _delta_log or create a metadata tracking table that captures the latest DESCRIBE HISTORY result for all tables.

If this is something you need regularly, you could automate it by running a scheduled job that collects DESCRIBE HISTORY timestamps for all tables and stores them in a central table for easy querying.

Regards,

Brahma

Re: Centralized Location of Table History/Timestamps in Unity Catalog

noorbasha534 — Thu, 03 Apr 2025 20:02:25 GMT

@Brahmareddy do you see this table in your system catalog - system.access.table_changes

Re: Centralized Location of Table History/Timestamps in Unity Catalog

Priyanka_Biswas — Thu, 03 Apr 2025 20:28:29 GMT

Hi @Eric_Kieft @noorbasha534 system.access.table_lineage includes a record for each read or write event on a Unity Catalog table or path. This includes but is not limited to job runs, notebook runs, and dashboards updated with the read or write event.

Please find the doc for reference:

https://docs.databricks.com/aws/en/admin/system-tables/lineage

https://learn.microsoft.com/en-us/azure/databricks/admin/system-tables/lineage#table-lineage-table