How long is Snowflake's string min/max? ~40 chars, undocumented (measured on 10.25.101) · GitHub

/" data-turbo-transient="true" />

Skip to content

-->

Search Gists

Search Gists

Sign in

Sign up

You signed in with another tab or window. Reload to refresh your session.<br>You signed out in another tab or window. Reload to refresh your session.<br>You switched accounts on another tab or window. Reload to refresh your session.

Dismiss alert

{{ message }}

Instantly share code, notes, and snippets.

wubx/gist-snowflake-minmax.md

Created<br>July 22, 2026 01:09

Show Gist options

Download ZIP

Star

(0)

You must be signed in to star a gist

Fork

(0)

You must be signed in to fork a gist

Embed

Select an option

Embed<br>Embed this gist in your website.

Share<br>Copy sharable link for this gist.

Clone via HTTPS<br>Clone using the web URL.

No results found

Learn more about clone URLs

Clone this repository at &lt;script src=&quot;https://gist.github.com/wubx/c7a7d754ec5ec7d84b3aa7945bafec3f.js&quot;&gt;&lt;/script&gt;

" readonly="readonly" data-autoselect="true" data-target="primer-text-field.inputElement " aria-describedby="validation-7c171eaf-c8b6-41a1-9b62-f13c45da385f" class="form-control FormControl-monospace FormControl-input FormControl-small rounded-left-0 rounded-right-0 border-right-0" type="text" name="gist-share-url-sized-down" />

Save wubx/c7a7d754ec5ec7d84b3aa7945bafec3f to your computer and use it in GitHub Desktop.

Embed

Select an option

Embed<br>Embed this gist in your website.

Share<br>Copy sharable link for this gist.

Clone via HTTPS<br>Clone using the web URL.

No results found

Learn more about clone URLs

Clone this repository at &lt;script src=&quot;https://gist.github.com/wubx/c7a7d754ec5ec7d84b3aa7945bafec3f.js&quot;&gt;&lt;/script&gt;

" readonly="readonly" data-autoselect="true" data-target="primer-text-field.inputElement " aria-describedby="validation-e65c9a2d-8127-4b16-b9a6-6bbd5539fcd1" class="form-control FormControl-monospace FormControl-input FormControl-small rounded-left-0 rounded-right-0 border-right-0" type="text" name="gist-share-url-original" />

Save wubx/c7a7d754ec5ec7d84b3aa7945bafec3f to your computer and use it in GitHub Desktop.

Download ZIP

How long is Snowflake's string min/max? ~40 chars, undocumented (measured on 10.25.101)

Raw

gist-snowflake-minmax.md

How long is Snowflake's string min/max? ~40 chars, and it's undocumented

TL;DR — Snowflake prunes micro-partitions using per-column min/max<br>metadata. For VARCHAR, that min/max only tracks roughly the first 40–41<br>characters . If the distinguishing bytes of your string live past ~41, range<br>pruning silently degrades to a full table scan. This length is not documented<br>anywhere I could find, so I measured it as a black box. Tested on Snowflake<br>10.25.101 .

This started while reproducing a Databend issue about the same class of bug<br>(databendlabs/databend#20185), then I got curious<br>where Snowflake's own limit sits.

Background: two different truncations

People conflate these two. They are not the same thing.

Mechanism<br>Governs<br>Length<br>Documented?

cluster-key metadata<br>physical ordering (clustering)<br>first ~5–6 bytes of VARCHAR<br>yes

column min/max metadata<br>micro-partition pruning<br>~40–41 chars (measured here)<br>no

The 5–6 byte figure for cluster keys is in the docs. The min/max length that<br>actually drives pruning is not. That's the gap this measures.

Pruning works like every columnar/lakehouse engine: data is split into chunks<br>(micro-partitions, ~16MB compressed), each chunk stores per-column min/max, and<br>the optimizer skips chunks whose [min,max] can't contain the predicate value.<br>If every chunk ends up with an identical [min,max] — because the bytes that<br>differ got truncated away — nothing can be skipped.

Method

Construct strings with a fixed identical prefix followed by a unique<br>tail , so the distinguishing bytes sit at a known position. Then push that<br>position further out and watch when pruning stops working.

Pruning is read from partitions_scanned / partitions_total (Snowflake's<br>analog of "how many blocks did we actually read").

-- N-char string, distinguishing bytes near the end, clustered by SUBSTRING(1, N)<br>CREATE OR REPLACE TABLE cmp_a40 (contract_no VARCHAR, notify_date INT)<br>CLUSTER BY (SUBSTRING(contract_no, 1, 40));

INSERT INTO cmp_a40<br>SELECT 'CONTRACT20240101' || LPAD(SEQ8()::STRING, 24, '0'),<br>UNIFORM(20240101, 20241231, RANDOM())::INT<br>FROM TABLE(GENERATOR(ROWCOUNT => 2000000000)); -- 2B rows

-- Clustering quality = proxy for min/max overlap across partitions<br>SELECT SYSTEM$CLUSTERING_INFORMATION('cmp_a40');

-- Point query, then read pruning<br>SET probe = (SELECT contract_no FROM cmp_a40 SAMPLE (1 ROWS));<br>SELECT COUNT(*) FROM cmp_a40 WHERE contract_no = $probe;<br>SELECT partitions_scanned, partitions_total<br>FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())<br>WHERE query_id = LAST_QUERY_ID();

Use INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION() (seconds of latency), not<br>SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY...