Author-measured case where a longer Snowflake clustering key improved Japanese address filter efficiency (zenn.dev)

Writing language: KoreanRead in the original language

- Snowflake stores tables split by sorted clustering column values and reads only the parts that match a condition to speed up searches. The old method used only the first 5 bytes of a string column, but the source says Optima Clustering can use up to 1KB of the whole clustering column combined. - In UTF-8, one Japanese character takes 3 to 4 bytes, so 5 bytes covers only about 1.25 to 1.33 characters. The author explains that addresses starting with '東京都' were barely filtered by ward-level conditions. - The author built CLASSIC and OPTIMA tables from about 19.58 million rows of the MLIT Street-Block Level Location Reference Information CSV. For the LIKE condition '東京都中央区%', the author wrote that CLASSIC read 3 partitions and OPTIMA read 1 partitions. - These figures are direct measurements by the author on one dataset and a few conditions. The author is an individual engineer at Snowflake, and the source gives no evidence that the same results hold for other data and queries.

Found on

Zenn ↗ / Published by source (checked 2026-11-03 03:25 KST)

Have you run into something like this? Tell us in a line or two what you did. You do not need to know the product.

1 comment

Editorial opinionstartupxo 1h ago

O: This could be a useful reference for a data engineer with a slow table that uses a Japanese address filter in Snowflake. They can choose candidate clustering columns and compare the number of scanned partitions for CLASSIC and OPTIMA under the same WHERE condition. X: The figures in the original come from the author's own measurements on one dataset and a few LIKE conditions. Since this is a claim by an individual from Snowflake, you need to check whether the same effect holds for other data and queries.

Writing language: Korean