SnowPro Advanced: Data Engineer free question 6 / 10 · Domain 3: Storage and Data Protection
The table WEB_EVENTS is clustered with CLUSTER BY (event_date). You want to find the average depth of how well event_date is grouped, not for the whole table but only for the range from 2026-09-01 onward. Which call does that?
- SELECT SYSTEM$CLUSTERING_DEPTH('WEB_EVENTS', '(event_date)', 'WHERE event_date >= ''2026-09-01''');
- SELECT SYSTEM$CLUSTERING_DEPTH('WEB_EVENTS', '(event_date)', 'event_date >= ''2026-09-01''');
- SELECT SYSTEM$CLUSTERING_INFORMATION('WEB_EVENTS', '(event_date >= ''2026-09-01'')');
- SELECT SYSTEM$CLUSTERING_INFORMATION('WEB_EVENTS', '(event_date)', 'event_date >= ''2026-09-01''');
Show the answer and explanation
Answer: B
Why this is correct
SYSTEM$CLUSTERING_DEPTH takes a predicate as its third argument and can narrow the range of column values for which the depth is computed. The documentation specifies that the predicate must not begin with the WHERE keyword, that all arguments are strings enclosed in single quotes, and that string literals inside the predicate escape a single quote by doubling it. The column argument can be omitted for a table with a clustering key, but if you write it, the depth is computed for that column.
Why the other options are wrong
"SELECT SYSTEM$CLUSTERING_DEPTH('WEB_EVENTS', '(event_date)', 'WHERE event_date >= ''2026-09-01''');" … The predicate has WHERE attached. The documentation says the predicate must not start with the WHERE keyword.
"SELECT SYSTEM$CLUSTERING_INFORMATION('WEB_EVENTS', '(event_date)', 'event_date >= ''2026-09-01''');" … SYSTEM$CLUSTERING_INFORMATION has no argument to narrow the range. Its second argument is either columns or expressions, or the number of errors to return, and you cannot pass both at the same time.
"SELECT SYSTEM$CLUSTERING_INFORMATION('WEB_EVENTS', '(event_date >= ''2026-09-01'')');" … What is in the parentheses of the second argument is treated as an expression for which clustering information is computed, not as a filter condition. It would compute how well the data is grouped by the Boolean expression event_date >= ..., and does not narrow the range.
Key point
Even for a table without a clustering key, both functions can be used if you pass columns, and they can be used to compare candidate keys (omitting the column for a table without a key causes an error). Depth is a guide to how well the table is organized, and the documentation says query performance is ultimately the best measure. The depth of an empty table is 0, and for a table with data it is 1 or more.