Choosing the Right Case Insensitive Collation in Snowflake: A Deep Dive into Upper and Lower Collations
Collations play a critical role in comparing and sorting string data, enabling Snowflake to handle strings based on customized rules of locale, case sensitivity, accent sensitivity, and punctuation sensitivity. Snowflake’s recent full support for the upper and lower collation specifications offers more efficient options for case-insensitive string handling. This blog walks through these new additions to help you make informed decisions so that you can pick the right collation.
Understanding Upper and Lower Collations
Unlike locale-aware collations such as en-ci, Snowflake’s upper and lower collations are locale-agnostic, transforming strings to uppercase or lowercase, respectively, before performing comparison and sorting. Locale-agnostic collations bypass the need to fetch collation keys, thus eliminating lookup time and improving performance.
With the full support of upper and lower collations, Snowflake now offers three primary specifications for case-insensitive collations:
- locale-ci: Utilizes locale-aware collation keys for language-specific comparisons.
- upper: Converts strings to uppercase internally, eliminating the need for collation key lookups.
- lower: Similar to
upper, but converts strings to lowercase.
Choosing a Case Insensitive Collation
Generally speaking, applying collations to string columns almost always results in additional computation. Therefore, if you can manage without specifying a collation, that would be the most performant design.
If a case-insensitive collation is necessary, we generally recommend the upper and lower collations over the ci collation due to the performance improvement mentioned earlier. We understand that switching collations is not a simple task, but we encourage you to test the upper and lower collations with your slow-running queries to determine if the switch is beneficial. However, it is important to note that having consistent collations across the database is recommended for consistent join, comparison and sorting behaviors.
When deciding between upper and lower collations, consider the distribution of your data, although the difference is usually marginal. If your string data is predominantly uppercase, then upper should be your choice to align with the existing case format. The same applies to lower.
Key Considerations and Caveats
Behavioral Differences: Although ci, upper, and lower can all be used as case-insensitive collations, they do not behave identically in all scenarios. The differences fall into the following categories (refer to our documentations for more details):
- Differences in Comparisons of Widths, Spaces, and Scripts
- Differences in Handling Ignorable Code Points
- Differences When Characters Are Represented by Different Code Points
- Differences With Sequences of Code Points Representing a Single Character
- Differences When Changes to Case Results in Multiple Code Points
- Differences in Sort Order
If your data might contain these characters, it is crucial to understand these differences to avoid unexpected results in data comparison and sorting.
Mixed Use of Locale-aware and Locale-agnostic Collations: It is not recommended to combine locale-aware and locale-agnostic collations, such as ‘en-upper’. Although upper and lower are designed to improve performance, adding a language specifier like ‘en’ introduces unnecessary overhead by involving collation key lookup processes. However, combining upper or lower with other specifications, such as trim, is perfectly valid and can further refine your collation behavior.
Conclusion
The introduction of upper and lower collations in Snowflake provides more performant options for case-insensitive string data handling. Visit our documentation for more information, particularly regarding the behavioral differences between these collations.
Happy Collating!
