Case Sensitivity in Snowflake
Understanding Case Sensitivity when creating and querying snowflake objects
Snowflake is case-sensitive and let us understand how it behaves through demonstrations below:
Demo1 — Creating an object in upper case without double quotes
a) Selecting the object in any case without double quotes will return the table.
b) Selecting the object in upper case with double quotes will return the table.
C) Selecting the object in lower or mixed case with double quotes will return an error.
Demo 2 — Creating an object in lower or mixed case without double quotes
a) Selecting the object in any case without double quotes will return the table.
b) Selecting the object in upper case with double quotes will return the table.
C) Selecting the object in lower or mixed case with double quotes will return an error.
Demo3 — Creating an object in upper case with double quotes
a) Selecting the object in any case without double quotes will return the table.
b) Selecting the object in upper case with double quotes will return the table.
C) Selecting the object in lower or mixed case with double quotes will return an error.
Demo 4— Creating an object in mixed case with double quotes
a) Selecting the object in any case without double quotes will return an error
b) Selecting the object in upper case with double quotes will return an error.
C) Selecting the object in mixed case with double quotes will return the table.
d) Selecting the object in lower case with double quotes will return an error.
Observation:
- When create an object without double quotes, the object is upper cased and object can be referenced by any case.
- When create an object with double quotes, have to use same case when referencing with double quotes.
Demo5 — Creating columns in different cases
The observations stated above holds true when referencing columns as well.
QUOTED_IDENTIFIERS_IGNORE_CASE
The default setting of QUOTED_IDENTIFIERS_IGNORE_CASE is false.
If the setting is changed to TRUE, snowflake will automatically upper case all the objects and it makes no difference weather a double quote is used or not
Repeat Demo 4 and observe the difference -
a) Selecting the object in any case without double quotes will return the table.
b) Selecting the object in upper case with double quotes will return the table.
C) Selecting the object in mixed case with double quotes will return the table.
d) Selecting the object in lower case with double quotes will return the table.
References: https://www.snowflake.com/
Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.