data_skipping_summary table-valued function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Returns one row for each leaf column in a Delta Lake or managed Apache Iceberg table. Each row reports whether the column is eligible for data skipping and the percentage of files in the table's current snapshot that contain minimum and maximum statistics for the column.

This function reads the table log and doesn't scan data files.

Requirements

To invoke this function, you must have the SELECT privilege on the table.

Syntax

data_skipping_summary( TABLE ( table_name ) )

Arguments

  • table_name: A three-part identifier that specifies the table.

    The function supports the following table types:

    • Managed and external Delta Lake tables
    • Managed Apache Iceberg tables and Delta Lake tables with Iceberg reads enabled
    • streaming tables
    • materialized views
    • Managed and external shallow clones

    The function doesn't support metric views, foreign tables, or views.

Returns

The function returns the following columns:

  • column_name STRING NOT NULL: The logical column name. Nested structure fields use dotted paths, such as address.street.
  • data_type STRING NOT NULL: The Spark SQL data type, formatted as in DESCRIBE TABLE.
  • is_partition_column BOOLEAN NOT NULL: true if the column is part of the table's partition specification.
  • is_clustering_column BOOLEAN NOT NULL: true if the column is a liquid clustering key.
  • data_skipping_eligible BOOLEAN NOT NULL: true if the table collects data skipping statistics for the column.
  • data_skipping_excluded_reason STRING: The reason a column isn't eligible for data skipping. The value is NULL when the column is eligible. Otherwise, the value is one of the following:
    • IS_PARTITION_COLUMN: The column is a partition column. Partition columns use a separate data skipping mechanism.
    • NOT_IN_INDEXED_COLUMN_SET: The column isn't configured for statistics collection.
    • TYPE_NOT_ELIGIBLE: The column's data type doesn't support data skipping statistics.
  • stats_coverage_pct DOUBLE: The percentage of files in the table's current snapshot that contain minimum and maximum statistics for the column, from 0.0 through 100.0. The value is NULL when the column isn't eligible.

MAP columns, ARRAY columns, and STRUCT containers aren't eligible for data skipping statistics. Leaves within a STRUCT are evaluated separately.

A VARIANT column is reported as one leaf column. It's eligible only if variant data skipping statistics are collected for it. The function doesn't list VARIANT subpaths.

Examples

> SELECT * FROM data_skipping_summary(TABLE(my_catalog.my_schema.events));
  column_name  data_type            is_partition_column  is_clustering_column  data_skipping_eligible  data_skipping_excluded_reason  stats_coverage_pct
  -----------  -------------------  -------------------  --------------------  ----------------------  -----------------------------  ------------------
  event_date   date                 true                 false                 false                   IS_PARTITION_COLUMN            null
  event_type   string               false                false                 true                    null                           98.0
  user_agent   string               false                false                 false                   NOT_IN_INDEXED_COLUMN_SET      null
  metadata     map<string,string>   false                false                 false                   TYPE_NOT_ELIGIBLE              null

Additional resources

  • Last updated on