An architect is reviewing query profile statistics. Which TWO indicators are signs that a table is poorly clustered for a specific query? (Choose two.)
Trap 1: Query profile shows excessive 'Local Disk Spilling'.
Local disk spilling is typically related to memory pressure during complex operations like joins or aggregations, not data clustering. This indicator suggests the need for a larger warehouse size to provide more RAM for data processing, rather than a need to adjust the physical storage layout of tables.
Trap 2: The query profile indicates high 'Network Transfer' time.
Network transfer time relates to data movement between nodes or from remote storage to compute resources. While this can impact performance, it is generally indicative of data distribution or multi-cluster overhead, not the specific physical storage layout or clustering efficiency of a single table used in a query.
Trap 3: The query profile shows high 'Compilation Time'.
Compilation time is the overhead incurred when the Snowflake optimizer generates the query plan. This is unrelated to how the data is physically stored in micro-partitions. High compilation time is usually caused by complex SQL structures or large numbers of tables in a single join, not clustering effectiveness.
- A
Percentage of partitions scanned is close to 100%.
A high percentage of partitions scanned indicates that the engine cannot prune effectively. This occurs when data is not physically ordered by the filter criteria, forcing the query to scan the entire table. This is a primary indicator that the existing clustering strategy does not support the query filters.
- B
Query profile shows excessive 'Local Disk Spilling'.
Why it fails: Local disk spilling is typically related to memory pressure during complex operations like joins or aggregations, not data clustering. This indicator suggests the need for a larger warehouse size to provide more RAM for data processing, rather than a need to adjust the physical storage layout of tables.
- C
Query profile shows high 'Partition Scan' time relative to total execution.
High partition scan time relative to total time suggests that the engine is spending most of its effort reading data that is not useful. This is a direct consequence of poor clustering, where the query must read vast amounts of data to find the few rows that match the filter.
- D
The query profile indicates high 'Network Transfer' time.
Why it fails: Network transfer time relates to data movement between nodes or from remote storage to compute resources. While this can impact performance, it is generally indicative of data distribution or multi-cluster overhead, not the specific physical storage layout or clustering efficiency of a single table used in a query.
- E
The query profile shows high 'Compilation Time'.
Why it fails: Compilation time is the overhead incurred when the Snowflake optimizer generates the query plan. This is unrelated to how the data is physically stored in micro-partitions. High compilation time is usually caused by complex SQL structures or large numbers of tables in a single join, not clustering effectiveness.