Predictive optimization system table reference

This article outlines the predictive optimization operation history table schema and provides sample queries. Predictive optimization optimizes your data layout for peak performance and cost efficiency. The system table tracks the operation history of this feature. For information on predictive optimization, see Predictive optimization for Unity Catalog managed tables.

Table path: This system table is located at system.storage.predictive_optimization_operations_history.

Delivery considerations

• The predictive optimization system table updates within two hours. However, billing information can take up to 24 hours to populate the data.
• Predictive optimization might run multiple operations on the same cluster. If so, the share of DBUs attributed to each of the multiple operations is approximated. This is why the usage_unit is set to ESTIMATED_DBU. Still, the total number of DBUs spent on the cluster will be accurate.

Predictive optimization table schema

The predictive optimization operation history system table uses the following schema:

Operation metrics

The metrics recorded in the operation_metrics column vary depending on the operation type:

Example queries

The following sections include sample queries you can use to gain insights into the predictive optimization system table. For these queries to work, you need to replace the parameter values with your own values.

This article includes the following example queries:

• How many estimated DBUs has predictive optimization used in the last 30 days?
• On which tables did predictive optimization spend the most in the last 30 days (estimated cost)?
• On which tables is predictive optimization performing the most operations?
• For a given catalog, how many total bytes have been compacted?
• What tables had the most bytes vacuumed?
• What is the success rate for operations run by predictive optimization?

How many estimated DBUs has predictive optimization used in the last 30 days?

SELECT SUM(usage_quantity)
  FROM system.storage.predictive_optimization_operations_history
  WHERE
    usage_unit = "ESTIMATED_DBU"
    AND timestampdiff(day, start_time, Now()) < 30;

To find the same value for a specific ETL pipeline, you can first find the tables in that pipeline, and then search for the DBUs:

-- Find all full table names for the pipeline:
WITH pipeline_mapping AS (
  SELECT DISTINCT target_table_full_name AS target_table_name
  FROM system.access.table_lineage
  WHERE entity_type = 'PIPELINE' AND entity_id = :pipeline_id
)
-- Select all operations for any table in that pipeline:
SELECT SUM(usage_quantity)
  FROM system.storage.predictive_optimization_operations_history
  WHERE
    CONCAT_WS('.', catalog_name, schema_name, table_name)
      IN ( SELECT target_table_name FROM pipeline_mapping)
    AND usage_unit = "ESTIMATED_DBU"
    AND timestampdiff(day, start_time, Now()) < 30;

On which tables did predictive optimization spend the most in the last 30 days (estimated cost)?

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  SUM(usage_quantity) as totalDbus
FROM system.storage.predictive_optimization_operations_history
WHERE
  usage_unit = "ESTIMATED_DBU"
  AND timestampdiff(day, start_time, Now()) < 30
GROUP BY ALL
ORDER BY totalDbus DESC;

On which tables is predictive optimization performing the most operations?

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  operation_type,
  COUNT(DISTINCT operation_id) as operations
FROM system.storage.predictive_optimization_operations_history
GROUP BY ALL
ORDER BY operations DESC;

For a given catalog, how many total bytes have been compacted?

SELECT
  schema_name,
  table_name,
  SUM(operation_metrics["amount_of_data_compacted_bytes"]) as bytesCompacted
FROM system.storage.predictive_optimization_operations_history
WHERE
  metastore_name = :metastore_name
  AND catalog_name = :catalog_name
  AND operation_type = "COMPACTION"
GROUP BY ALL
ORDER BY bytesCompacted DESC;

What tables had the most bytes vacuumed?

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  SUM(operation_metrics["amount_of_data_deleted_bytes"]) as bytesVacuumed
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = "VACUUM"
GROUP BY ALL
ORDER BY bytesVacuumed DESC;

What is the success rate for operations run by predictive optimization?

WITH operation_counts AS (
  SELECT
    COUNT(DISTINCT (CASE WHEN operation_status = "SUCCESSFUL" THEN operation_id END)) as successes,
    COUNT(DISTINCT operation_id) as total_operations
  FROM system.storage.predictive_optimization_operations_history
 )
SELECT successes / total_operations as success_rate
FROM operation_counts;