Analyze

[Rajasaiharish]

How many types of analyze do we have for a UC delta table ? And how to check which type of ANALY

[Rajasaiharish]

How many types of analyze do we have for a UC delta table ? And how to check which type of ANALYZE ran for what table? i think total questions was not printed correctly

[K_Anudeep]

Hello  @Rajasaiharish !

For a UC Delta table, there are three main ANALYZE command families:

1. COMPUTE STATISTICS — collects table/column statistics for the query optimiser.
2. COMPUTE DELTA STATISTICS — recomputes Delta file-level statistics used for data skipping.
3. COMPUTE STORAGE METRICS — calculates storage usage metrics.

There are three types of optimiser-stat collection sources, which you see on a UC delta table when ANALYZE is run:

1. MANUAL_ANALYZE - Someone (notebook, SQL editor, job, DLT that isn’t on the auto-stats write path) runs ANALYZE TABLE … COMPUTE STATISTICS.
2. AUTO_STATS - Stats are collected on write / ingest, not manually.
3. PREDICTIVE_ANALYZE  - Predictive Optimization (maintenance-auto-compute) schedules ANALYZE. 

You can run the query below to check the type of ANALYZE run on the table:

SHOW STATISTICS FROM catalog.schema.table AS JSON;
SHOW STATISTICS FROM catalog.schema.table FOR ALL COLUMNS AS JSON;

Look at statistics.collection_source and statistics.created_at in the output to see what type of ANALYZE was run on the table