Understanding Upper and Lower Case Identifiers in Snowflake

New users to Snowflake, especially those with MySQL or Postgres experience, are often surprised with the case sensitivity of identifiers. If we dig a little deeper, this behavior is actually quite familiar but with a twist.

The Problem #

Usually it looks something like:

create table foo (my_column text);

-- possibly far away in your code ...
-- maybe in a library function
select "my_column" from foo;

-- SQL compilation error: 
-- error line 1 at position 7 invalid identifier '"my_column"

What’s happening?

• Snowflake matches identifiers in a case-sensitive way
• Unquoted identifiers are converted to UPPERCASE
• Surrounding an identifier in double quotes preserves the case

In SQL, single quotes surround string literals. All the quotes we’re talking about in this post are the double quote symbol ". In Postgres, Redshift, and Snowflake, double quotes can be used to surround an identifier.

See the Snowflake Identifier Requirements for more details.

Let’s look at some examples.

Examples #

select SAM from (select 1 as SAM);

+---+
|SAM|
+---+
|1  |
+---+

Cool, works fine. Let’s try lower case…

select sam from (select 1 as sam);

+---+
|SAM|
+---+
|1  |
+---+

Still works. Same return value. Notice that the column name returned is SAM not sam. Snowflake turned the unquoted sam into SAM.

Now it’s getting late, we’re tired, and the casing gets inconsistent:

select SAM from (select 1 as sam);

+---+
|SAM|
+---+
|1  |
+---+

select sam from (select 1 as SAM);

+---+
|SAM|
+---+
|1  |
+---+

Still works. Still returns SAM.

Then somebody (or maybe a library we’re using) decides to quote an identifier:

select sam from (select 1 as "sam");

-- SQL compilation error: 
-- error line 1 at position 7 invalid identifier 'SAM'

🤯

What happened? The first unquoted sam becomes SAM and then the select goes looking for sam, which doesn’t exist. 💔

The same thing can happen in reverse:

select "sam" from (select 1 as sam);

-- SQL compilation error:
-- error line 1 at position 7 invalid identifier '"sam"

If we want the identifiers to be lower case, we need to quote them consistently:

select "sam" from (select 1 as "sam");

+---+
|sam|
+---+
|1  |
+---+

Notice the returned column name is now lower case sam.

Applies to all identifiers #

This applies to any identifier, including table names, view names, and CTE clauses. In some cases Snowflake returns the error Object does not exist or not authorized. When your queries have gotten complex, it’s easy to mistake this for a permissions issue, but it’s just a mistake in case or quoting.

select my_column from "foo";

-- SQL compilation error: 
-- Object '"foo"' does not exist or not authorized.

with "sam" as (select 1)
select * from sam;

-- SQL compilation error: 
-- Object 'SAM' does not exist or not authorized.

Not actually weird! #

While this might feel surprising at first, most SQL databases have similar identifier matching rules. Postgres, MySQL and others convert unquoted identifiers to lower case. It’s just opposite what most of us are familiar with in those open-source databases.

I’ve read that Snowflake follows the convention of Oracle databases, so if you come from Oracle-land, you’ll be right at home.

Recommendation #

I’d recommend writing all lower case, unquoted identifiers.

I don’t like writing UPPER CASE because my fingers get tired and it’s harder for me to read.

Snowflake will turn these into upper case identifiers but for the most part you’ll be able to ignore that. The only exception is any code that reads column names from Snowflake will have to accept upper case identifiers.

If you need a lower or mixed case identifier, you need to surround it with double quotes on every use.