Table metrics history system table reference

Important

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: Azure Databricks uses serverless compute to run the background jobs that compute storage size. Azure 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.

Note

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.

Note

You might see activity from a service principal named Table Summary Metrics Collection Principal in your audit logs. This is expected. Azure 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
snapshot_date date The date the storage metrics correspond to. This is the snapshot date, not the delivery date. 2025-10-15
account_id string The ID of the account. 11e22ba4-87b9-4cc2-9770-d10b894b7118
metastore_id string The ID of the metastore the table belongs to. 5a31ba44-bbf4-4174-bf33-e1fa078e6765
table_id string The ID of the table. 138ebb4b-3757-41bb-9e18-52b38d3d2836
catalog_name string The name of the catalog the table belongs to. catalog
schema_name string The name of the schema the table belongs to. schema
table_name string The name of the table. table1
table_type string The type of table. Possible values are MANAGED_TABLE, MATERIALIZED_VIEW, and STREAMING_TABLE. MANAGED_TABLE
clone_details struct For a shallow clone, a struct with the clone type and the source table's parent_id. For a shallow clone, active_bytes is 0 because the clone references the source table's data files. {"parent_id": "2a31ba44-...", "type": "SHALLOW"}
table_owner string The owner of the table. user@example.com
active_bytes 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. 450000000
active_files 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 active_bytes indicates a table that benefits from OPTIMIZE. 4
table_creation_time timestamp The table's creation time. 2025-01-09 10:00:00.000+00:00
table_dropped_time timestamp The table's drop time, if applicable. The system table removes a dropped table after its undrop window plus 14 days. 2025-06-09 11:00:00.000+00:00
predictive_optimization_enabled boolean Whether the table has predictive optimization enabled. true
record_generation_time timestamp The time the system table generated this record. 2025-10-15 04:00:00.000+00:00
total_bytes bigint The total storage size of the table, including transaction-log, active, vacuumable, and time-travel data. Shows NULL unless your account is enrolled in the Private Preview for total storage metrics. NULL
total_files bigint The total number of files, including active, vacuumable, and time-travel files. Shows NULL unless your account is enrolled in the Private Preview for total storage metrics. NULL
vacuumable_bytes bigint The size in bytes of historical data that has passed the retention period. VACUUM can reclaim this data. This value can be nonzero even when predictive optimization is enabled, because predictive optimization runs VACUUM periodically when the accumulated storage savings justify the compute cost. See Predictive optimization for Unity Catalog managed tables. 200000000
vacuumable_files bigint The number of unique vacuumable files. 2
time_travel_bytes 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. 126000000
time_travel_files bigint The number of unique time-travel files. 1
metric_type string How the storage metrics were computed. Possible values are ESTIMATED and CALCULATED. Shows NULL unless your account is enrolled in the Private Preview for total storage metrics. NULL

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.

  • Azure 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 Azure 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, and metric_type columns.

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?

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?

Note

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 Azure China regions.
  • 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 Azure Archive.

Additional resources

  • Last updated on