-- ============================================================================
-- data-skipping-diagnostics.sql
-- Find out why a selective filter on a Delta table is still reading far more
-- files/rows than it should -- missing statistics, poor clustering, or stale
-- config, versus a filter shape the optimizer can't push down at all.
-- Source: Sqlism (sqlism.com) -- "Why Is Databricks Reading Billions of Rows
-- to Return 10?"
--
-- Requires: a Delta Lake table you can run DESCRIBE DETAIL / SHOW TBLPROPERTIES
-- against. Replace 'catalog.schema.table_name' with your own table.
-- ============================================================================

-- 1) Table-level size and file count -- start here. Also relevant to whether
--    dynamic file pruning can apply on the probe side of a join (see the
--    companion join-strategy-diagnostics.sql script).
DESCRIBE DETAIL catalog.schema.table_name;

-- 2) Stats-related table properties currently set. An empty result for
--    delta.dataSkippingNumIndexedCols / delta.dataSkippingStatsColumns means
--    the table is relying on the default (first 32 columns get statistics).
SHOW TBLPROPERTIES catalog.schema.table_name;

-- 3) Does the table have a clustering definition at all (Z-order via OPTIMIZE,
--    or liquid clustering)? Delta table history shows past OPTIMIZE operations
--    and their ZOrderBy columns, if any were run.
DESCRIBE HISTORY catalog.schema.table_name;
-- Look for operation = 'OPTIMIZE' rows and check operationParameters.zOrderBy.

-- 4) Liquid clustering columns, if the table uses it instead of Z-order
--    (Databricks Runtime 14.3 LTS and above).
DESCRIBE DETAIL catalog.schema.table_name;
-- Check the clusteringColumns field in the result.

-- 5) After changing a stats configuration property, backfill statistics for
--    existing data (Databricks Runtime 14.3 LTS and above) -- otherwise only
--    newly written files pick up the new setting.
-- ANALYZE TABLE catalog.schema.table_name COMPUTE DELTA STATISTICS;