Table metrics history system table reference
This system table is in Public Preview.
The table metrics history system table records the storage size of your Unity Catalog managed tables every day. Use it to track table growth over time, attribute storage costs to teams, and find tables to optimize or clean up. It records active, vacuumable, and time-travel storage metrics with a file count for each. A total storage size column is also available for accounts enrolled in the Private Preview for total storage metrics.
Table path: This system table is located at system.storage.table_metrics_history.
Requirements
To populate storage metrics, this system table depends on the following:
- Serverless compute: Databricks uses serverless compute to run the background jobs that compute storage size. Databricks covers the cost of these jobs, so you aren't charged for them. If you haven't enabled serverless compute, or you disable it, the storage-metric columns return
NULL. See NULL storage metrics. - Unity Catalog managed tables: The system table records metrics for Unity Catalog managed tables, materialized views, and streaming tables. It doesn't include foreign tables or Hive metastore tables.
Serverless compute isn't available in every region. If your workspace is in a region without serverless compute, the system table doesn't populate storage metrics. See Connect to serverless compute.
Record availability
The table metrics history system table delivers storage metrics with the following characteristics:
- By default, it runs one collection cycle per day to capture new storage metrics.
- Each cycle adds a row for every table, even when the table didn't change since the previous cycle, so you can chart storage size over time.
- Records remain available for 13 months.
- Each row reflects the table's state as of a point during that day.
You might see activity from a service principal named Table Summary Metrics Collection Principal in your audit logs. This is expected. Databricks uses this service principal to run the background jobs that collect storage metrics for your tables. You aren't charged for this activity.
Table metrics history table schema
The table metrics history system table uses the following schema:
Column name | Data type | Description | Example |
|---|---|---|---|
| date | The date the storage metrics correspond to. This is the snapshot date, not the delivery date. |
|
| string | The ID of the account. |
|
| string | The ID of the metastore the table belongs to. |
|
| string | The ID of the table. |
|
| string | The name of the catalog the table belongs to. |
|
| string | The name of the schema the table belongs to. |
|
| string | The name of the table. |
|
| string | The type of table. Possible values are |
|
| struct | For a shallow clone, a struct with the clone |
|
| string | The owner of the table. |
|
| bigint | The size in bytes of the latest version of the table. This excludes time-travel and vacuumable data. For materialized views and streaming tables, this includes the view and its backing table, excluding vacuumable bytes. |
|
| bigint | The number of unique active data files that the Delta Lake log references for the latest snapshot. This excludes vacuumable and time-travel files. A high file count relative to |
|
| timestamp | The table's creation time. |
|
| timestamp | The table's drop time, if applicable. The system table removes a dropped table after its undrop window plus 14 days. |
|
| boolean | Whether the table has predictive optimization enabled. |
|
| timestamp | The time the system table generated this record. |
|
| bigint | The total storage size of the table, including transaction-log, active, vacuumable, and time-travel data. Shows |
|
| bigint | The total number of files, including active, vacuumable, and time-travel files. Shows |
|
| bigint | The size in bytes of historical data that has passed the retention period. |
|
| bigint | The number of unique vacuumable files. |
|
| bigint | The size in bytes of historical data that the table retains for time travel. To lower this value, reduce the table's retention window. |
|
| bigint | The number of unique time-travel files. |
|
| string | How the storage metrics were computed. Possible values are |
|
NULL storage metrics
A storage-metric column can show NULL when:
-
Storage metric collection for the table is still in progress.
-
Collection didn't complete because of a temporary timeout or another collection error.
-
Databricks couldn't access the table's underlying storage because of a connectivity, network, Private Link, or permission issue.
For Private Link configuration guidance, see Configure private connectivity to AWS-managed resources.
-
Serverless compute is disabled or unavailable for the workspace or region. See Connect to serverless compute.
-
The table is outside the supported scope for the feature. Unsupported tables might not appear in the system table.
-
Your account isn't enrolled in the Private Preview for total storage metrics. This applies only to the
total_bytes,total_files, andmetric_typecolumns.
When a storage-metric column is NULL, the system table still populates the identity and metadata columns (table_id, table_name, catalog_name, schema_name, table_type, clone_details, table_owner, predictive_optimization_enabled, account_id, and metastore_id).
Example queries
The following sections include sample queries you can use to gain insight from the table metrics history system table. For these queries to work, replace the parameter values with your own values.
This page includes the following example queries:
- What are my largest tables by active size?
- How does a table's size change over time?
- Which tables retain the most time-travel data?
- Which tables have the most reclaimable storage?
- How is storage distributed across catalogs?
What are my largest tables by active size?
To list your largest tables by active storage size, along with their owners, run the following:
SELECT
catalog_name,
schema_name,
table_name,
table_owner,
active_bytes
FROM system.storage.table_metrics_history
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM system.storage.table_metrics_history)
ORDER BY active_bytes DESC
LIMIT 100;
How does a table's size change over time?
To track how a table's active size changes over time, run the following. It matches on table_id so a rename doesn't affect the results:
SELECT
snapshot_date,
active_bytes
FROM system.storage.table_metrics_history
WHERE table_id = (
SELECT table_id
FROM system.storage.table_metrics_history
WHERE
catalog_name = :catalog_name
AND schema_name = :schema_name
AND table_name = :table_name
LIMIT 1
)
ORDER BY snapshot_date;
Which tables retain the most time-travel data?
To find tables that retain the most time-travel data, to assess where the retention window could be reduced to save on storage costs, run the following:
SELECT
catalog_name,
schema_name,
table_name,
time_travel_bytes
FROM system.storage.table_metrics_history
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM system.storage.table_metrics_history)
ORDER BY time_travel_bytes DESC
LIMIT 100;
Which tables have the most reclaimable storage?
Predictive optimization automatically runs VACUUM as needed, eliminating the need to schedule and manage these operations manually.
To find tables that hold the most data VACUUM can reclaim but don't have predictive optimization enabled, run the following:
SELECT
catalog_name,
schema_name,
table_name,
vacuumable_bytes
FROM system.storage.table_metrics_history
WHERE
snapshot_date = (SELECT MAX(snapshot_date) FROM system.storage.table_metrics_history)
AND predictive_optimization_enabled = false
ORDER BY vacuumable_bytes DESC
LIMIT 100;
How is storage distributed across catalogs?
To group active storage size by catalog as a cost-attribution proxy, run the following:
SELECT
catalog_name,
SUM(active_bytes) AS total_active_bytes
FROM system.storage.table_metrics_history
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM system.storage.table_metrics_history)
GROUP BY catalog_name
ORDER BY total_active_bytes DESC;
Limitations
- Storage metrics aren't available in Databricks on AWS GovCloud.
- The size of a table can't be calculated if its metadata files, such as the transaction log, are in an archived storage tier, such as S3 Glacier.