Delta table inspection cheat sheet: DESCRIBE HISTORY, DETAIL and who changed what

#When to use this

A pipeline failed, row counts look wrong or someone asks, "What happened to this table?" These read-only commands answer most first questions. They're safe to run on production tables.

Every example uses main.sales.orders. Replace it with your own table.

#What is this table?

#How big is it?

DESCRIBE DETAIL main.sales.orders;

Useful columns in the result:

Work out the average file size:

d = spark.sql("DESCRIBE DETAIL main.sales.orders").first()
print(f"{d.numFiles} files, {d.sizeInBytes/1024**3:.1f} GB, "
      f"{d.sizeInBytes/max(d.numFiles,1)/1024**2:.1f} MB per file")

Tens of thousands of files with an average of a few MB suggest a small-file problem.

sizeInBytes covers only the current version. Old versions kept for time travel use extra storage that isn't shown here.

#What changed, and who did it?

DESCRIBE HISTORY main.sales.orders LIMIT 20;

Key columns:

To query history, use a view of the output with Python:

from pyspark.sql.functions import col

h = spark.sql("DESCRIBE HISTORY main.sales.orders")
(h.select("version", "timestamp", "userName", "operation",
          col("operationParameters.mode").alias("mode"),
          col("operationMetrics.numOutputRows").alias("rows_out"),
          col("operationMetrics.numTargetRowsDeleted").alias("rows_deleted"),
          col("job.jobId").alias("job"))
   .orderBy(col("version").desc())
   .show(20, truncate=False))

Common questions:

#Compare two versions

SELECT 'now' AS v, count(*) FROM main.sales.orders
UNION ALL
SELECT 'v41', count(*) FROM main.sales.orders VERSION AS OF 41;

Find rows that exist in one version and not in the other:

SELECT * FROM main.sales.orders VERSION AS OF 41
EXCEPT
SELECT * FROM main.sales.orders;

Or go by time:

SELECT count(*) FROM main.sales.orders TIMESTAMP AS OF '2026-11-05T22:00:00';

If this fails with a missing version or file, retention may have removed the data. See time travel retention.

#Who can access it?

SHOW GRANTS ON TABLE main.sales.orders;
SHOW GRANTS `priya@contoso.com` ON TABLE main.sales.orders;
SHOW GRANTS ON SCHEMA main.sales;
SHOW GRANTS ON CATALOG main;

A user needs USE CATALOG and USE SCHEMA as well as SELECT. See the grants cheat sheet.

#What uses this table?

Unity Catalog records lineage in system tables, if they're enabled:

SELECT source_table_full_name, target_table_full_name, entity_type, max(event_time) AS last_seen
FROM system.access.table_lineage
WHERE source_table_full_name = 'main.sales.orders'
   OR target_table_full_name = 'main.sales.orders'
GROUP BY 1, 2, 3
ORDER BY last_seen DESC;

More in lineage and impact analysis.

#Check its properties

SHOW TBLPROPERTIES main.sales.orders;
SHOW TBLPROPERTIES main.sales.orders ('delta.logRetentionDuration');

#Quick data checks

SELECT count(*) AS total, count(DISTINCT order_id) AS distinct_ids,
       count(*) - count(order_id) AS null_ids,
       min(order_date) AS first_date, max(order_date) AS last_date
FROM main.sales.orders;

SELECT order_date, count(*)
FROM main.sales.orders
WHERE order_date >= current_date() - 14
GROUP BY 1 ORDER BY 1;

The first finds duplicates and nulls. The second shows gaps or dips by day.

#Careful: these commands change things

Inspection is read-only, but these look similar and aren't:

Caution

Don't run VACUUM while you're investigating a data problem. It can remove the old versions you may need for recovery.

#Prevent it next time

• Include job and table names in run logs, so history lines up with your scheduler.
• Save a dated copy of DESCRIBE DETAIL for key tables, to see trends in size and file count.
• Learn the basics of Delta tables if you're new to them.