CertKeen

SnowPro Advanced: Data Engineer · Free practice question 7 of 10

External table partition metadata refresh

An external table backed by S3 has partition columns for year, month, and day. New date-partitioned folders arrive daily. A query with WHERE year=2026 AND month=1 shows a full scan in the Query Profile instead of the expected partition pruning. What is the most likely cause and fix?

  1. A.Partition columns must be VARIANT; recreate the table with VARIANT partition columns to enable pruning.
  2. B.External tables do not support partition pruning; layer a materialized view on top to get pruning.
  3. C.The external table's partition metadata is stale — run ALTER EXTERNAL TABLE ... REFRESH (or enable AUTO_REFRESH with cloud event notifications) so Snowflake sees the new partitions.
  4. D.Partition pruning on external tables requires the query to reference METADATA$PARTITION_ID explicitly in the WHERE clause.
Show answer and explanation

Correct answer: C. The external table's partition metadata is stale — run ALTER EXTERNAL TABLE ... REFRESH (or enable AUTO_REFRESH with cloud event notifications) so Snowflake sees the new partitions.

Why: External tables prune only what their registered partition metadata knows about. Newly-added S3 folders are invisible until Snowflake refreshes — either manually via ALTER EXTERNAL TABLE ... REFRESH, or automatically if AUTO_REFRESH is enabled and cloud event notifications are wired. Once metadata is current, the WHERE clause filters normally with proper pruning.

More free SnowPro Advanced: Data Engineer questions