Snowflake's string min/max is truncated to ~40 chars, and it's undocumented
5 hours ago
- Snowflake prunes micro-partitions based on per-column min/max metadata, but for VARCHAR values, only the first ~40–41 characters are tracked, leading to potential full table scans if distinguishing bytes lie beyond this limit.
- The undocumented 40-41 character limit for VARCHAR min/max pruning was measured via black-box testing in Snowflake 10.25.101, distinct from the documented 5-6 byte limit for cluster-key metadata.
- Pruning effectiveness degrades when the distinguishing part of a string starts past ~41 characters, causing point lookups to silently become full table scans.
- Workarounds include clustering on a substring that includes the distinguishing bytes, storing the unique part first, or using alternative encoding strategies.
- Snowflake's approach contrasts with Databend, which allows users to configure the truncation length per-column via STATS_TRUNCATE_LEN.