Skip to content

Case Sensitivity in Snowflake

Understanding how Snowflake handles case sensitivity is essential when configuring Privacera policies and working with database objects.

Default Behavior: Unquoted Identifiers

By default, Snowflake interprets unquoted identifiers as uppercase, regardless of how they are typed.

SQL
1
2
3
4
5
6
7
8
-- Example 1: Unquoted Identifiers (Default)
CREATE TABLE my_table (id INT, customer_name VARCHAR(100));
-- Internally stored as: "MY_TABLE" with columns "ID" and "CUSTOMER_NAME"

-- All these queries refer to the same table:
SELECT * FROM my_table;
SELECT * FROM MY_TABLE;
SELECT * FROM My_Table;

Preserving Case: Quoted Identifiers

When you use double quotes ("), Snowflake preserves the exact case you specify:

SQL
1
2
3
4
5
6
7
8
9
-- Example 2: Quoted Identifiers
CREATE TABLE "My_Table" ("Id" INT, "Customer_Name" VARCHAR(100));
-- Internally stored as: "My_Table" with columns "Id" and "Customer_Name"

-- Only this exact case will work:
SELECT * FROM "My_Table";
-- These will fail:
-- SELECT * FROM My_Table;     -- Error!
-- SELECT * FROM "MY_TABLE";   -- Error!

Impact on Privacera Policies

When creating Privacera policies for Snowflake resources, consider:

Policy Resource Names

  • Unquoted Resources: Use uppercase names in policies (e.g., MY_TABLE, CUSTOMER_NAME)
  • Quoted Resources: Use exact case as defined in Snowflake (e.g., My_Table, Customer_Name)
  • Consistency: Ensure your naming convention is consistent across your organization

Best Practice

  • Stick to one naming convention throughout your organization
  • Most organizations use uppercase for simplicity with unquoted identifiers
  • Document your naming standards for policy creators

Comments