check statistics of clustering columns per file to see how liquid clustering works

smoortema — Sun, 01 Mar 2026 20:46:50 GMT

I have a Delta table on which I set up liquid clustering using three columns. I would like to check file statistics to see how the clustering column values are distributed along the files. How can I write a query that shows min and max values, etc. of the clustering columns for each of the files?

Re: check statistics of clustering columns per file to see how liquid clustering works

Louis_Frolio — Tue, 03 Mar 2026 12:20:18 GMT

Good Day @smoortema ,

Delta stores per-file statistics (min, max, null count, row count) for columns enabled for data skipping. You can read those directly from the transaction log.

Step 1: Get the table location

DESCRIBE DETAIL catalog.schema.my_table;

Copy the location value — you'll need it in the next query.

Step 2: Query file-level stats for your clustering columns

WITH raw_log AS (
  SELECT
    add.path  AS file_path,
    add.stats AS stats_json
  FROM json.`s3://bucket/path/my_table/_delta_log/*.json`
  WHERE add IS NOT NULL
),
parsed AS (
  SELECT
    file_path,
    from_json(
      stats_json,
      'numRecords LONG,
       minValues MAP<STRING,STRING>,
       maxValues MAP<STRING,STRING>,
       nullCount MAP<STRING,LONG>'
    ) AS s
  FROM raw_log
)
SELECT
  file_path,
  s.numRecords                          AS rows_in_file,
  CAST(s.minValues['c1'] AS <type>)    AS min_c1,
  CAST(s.maxValues['c1'] AS <type>)    AS max_c1,
  CAST(s.minValues['c2'] AS <type>)    AS min_c2,
  CAST(s.maxValues['c2'] AS <type>)    AS max_c2,
  CAST(s.minValues['c3'] AS <type>)    AS min_c3,
  CAST(s.maxValues['c3'] AS <type>)    AS max_c3
FROM parsed;

Replace s3://bucket/path/my_table with your actual location, and <type> with the real SQL types for each column (e.g. INT, DATE, TIMESTAMP).

You'll get one row per data file showing the min/max range for each clustering column.

Worth flagging: stats only exist for columns configured for data skipping — by default the first N columns, or those set explicitly via delta.dataSkippingStatsColumns. Columns with types like VARIANT won't have min/max stats.

Hope this helps, Louis.

Hi @smoortema, There are several approaches for inspectin...

SteveOstrowski — Mon, 09 Mar 2026 05:53:07 GMT

Hi @smoortema,

There are several approaches for inspecting per-file column statistics on a liquid-clustered Delta table. Here is a walkthrough from simplest to most detailed.

APPROACH 1: CONFIRM CLUSTERING CONFIGURATION

First, verify that clustering is set up correctly:

DESCRIBE DETAIL your_catalog.your_schema.your_table;

This returns a row with a clusteringColumns field showing the columns used for liquid clustering. You can also check properties:

SHOW TBLPROPERTIES your_catalog.your_schema.your_table;

Look for clusteringColumns and (if automatic clustering is enabled) clusterByAuto.

APPROACH 2: READ PER-FILE COLUMN STATISTICS FROM THE TRANSACTION LOG

Delta Lake stores min/max statistics for each data file in the _delta_log directory as JSON. You can parse this directly in a notebook to see per-file statistics for your clustering columns.

In Python:

from pyspark.sql.functions import col, from_json, input_file_name
from pyspark.sql.types import *

# Point to the Delta log directory
table_path = spark.sql(
  "DESCRIBE DETAIL your_catalog.your_schema.your_table"
).select("location").collect()[0][0]

log_path = f"{table_path}/_delta_log"

# Read the latest JSON log entries (add actions contain file-level stats)
log_df = (
  spark.read.json(f"{log_path}/*.json")
  .filter(col("add").isNotNull())
  .select(
      col("add.path").alias("file_path"),
      col("add.size").alias("size_bytes"),
      col("add.stats").alias("stats_json"),
      col("add.clusteringProvider").alias("clustering_provider")
  )
)

log_df.show(truncate=False)

The stats_json column contains a JSON string with numRecords, minValues, and maxValues for your indexed columns. To parse it into usable columns:

from pyspark.sql.functions import get_json_object

stats_df = log_df.select(
  "file_path",
  "size_bytes",
  get_json_object("stats_json", "$.numRecords").alias("num_records"),
  get_json_object("stats_json", "$.minValues.col_a").alias("col_a_min"),
  get_json_object("stats_json", "$.maxValues.col_a").alias("col_a_max"),
  get_json_object("stats_json", "$.minValues.col_b").alias("col_b_min"),
  get_json_object("stats_json", "$.maxValues.col_b").alias("col_b_max"),
  get_json_object("stats_json", "$.minValues.col_c").alias("col_c_min"),
  get_json_object("stats_json", "$.maxValues.col_c").alias("col_c_max")
)

stats_df.orderBy("col_a_min", "col_b_min", "col_c_min").show(100, truncate=False)

Replace col_a, col_b, col_c with your actual clustering column names.

This gives you a row per file with the min and max value for each clustering column, which is exactly what you need to see how tightly the data is clustered. After running OPTIMIZE, you should see the min/max ranges per file become narrower and more distinct (less overlap between files), which is how liquid clustering improves data skipping.

APPROACH 3: USE THE DELTALOG PYTHON API

You can also use the DeltaTable API to read the log more cleanly:

from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "your_catalog.your_schema.your_table")

# Get the detail (file count, size, clustering columns)
dt.detail().show(truncate=False)

# Get history to see OPTIMIZE operations and their metrics
dt.history().filter("operation = 'OPTIMIZE'").select(
  "version", "timestamp", "operationMetrics"
).show(truncate=False)

The operationMetrics map for OPTIMIZE operations includes numFilesRemoved, numFilesAdded, and file size distribution percentiles (minFileSize, p25FileSize, p50FileSize, p75FileSize, maxFileSize), which help you gauge how well the compaction and reclustering went.

APPROACH 4: CHECKPOINT-BASED INSPECTION FOR LARGE TABLES

For larger tables where reading all JSON log files is slow, Delta periodically writes Parquet checkpoint files (every 10 commits by default). You can read the latest checkpoint directly:

import os

# Find the latest checkpoint
checkpoint_df = spark.read.parquet(f"{log_path}/_last_checkpoint")
checkpoint_version = checkpoint_df.select("version").collect()[0][0]

# Read the checkpoint Parquet file
cp_df = spark.read.parquet(
  f"{log_path}/{str(checkpoint_version).zfill(20)}.checkpoint.parquet"
)

# Filter for active add actions and extract stats
cp_df.filter(col("add").isNotNull()).select(
  col("add.path").alias("file_path"),
  col("add.size").alias("size_bytes"),
  col("add.stats_parsed.numRecords").alias("num_records"),
  col("add.stats_parsed.minValues.*"),
  col("add.stats_parsed.maxValues.*")
).show(truncate=False)

Checkpoint files have the stats already parsed into a struct (stats_parsed) rather than a JSON string, which makes column extraction more straightforward.

INTERPRETING THE RESULTS

When liquid clustering is working well, you will see:
- Narrow min/max ranges per file for each clustering column (tight clustering)
- Minimal overlap between files on the clustering column ranges
- After OPTIMIZE, files that previously had wide or overlapping ranges get rewritten into tighter, non-overlapping segments

If you see wide, overlapping ranges across many files, it typically means OPTIMIZE has not yet been run (or not run recently enough). Running OPTIMIZE triggers incremental reclustering. On Databricks Runtime 16.0+, you can also run OPTIMIZE table_name FULL to force a complete reclustering when first enabling clustering or after changing clustering keys.

For reference:
https://docs.databricks.com/en/delta/clustering.html
https://docs.databricks.com/en/delta/data-skipping.html

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.

topic Re: check statistics of clustering columns per file to see how liquid clustering works in Data Engineering

check statistics of clustering columns per file to see how liquid clustering works

Re: check statistics of clustering columns per file to see how liquid clustering works

Hi @smoortema, There are several approaches for inspectin...