DEA-C02: SnowPro Advanced — Data Engineer

Question

A Query Profile indicates that Snowflake scans 80% of the partitions on a very large standard table when a particular filter is applied. The query runs for 30 minutes.

To investigate, the Engineer executes:

SELECT SYSTEM$CLUSTERING_INFORMATION ('LARGE_TABLE', '(COL_A)');

The JSON output shows a total_partition_count of 1,000, while the partition_depth_histogram indicates that 900 partitions have an average_overlap and average_depth of 1.

What conclusion can be drawn from this metadata?

Explanation

Snowflake reports clustering information for the specified column expression. For populated data, an average depth of 1 is the best possible result, and low overlap/depth indicates that the table is well-clustered with respect to COL_A. Thus, scanning 80% of partitions is more likely caused by a filter that does not use COL_A effectively, or by a value whose rows occur in the relatively overlapping partitions.

Learn more

DEA-C02: SnowPro Advanced — Data Engineer

AstroTutor

Notes

Readiness

QuestionQ9

Community Discussion