Invalid Identifier Error in Snowflake When Performing a Join

Let’s look at the Invalid Identifier error in Snowflake when performing a Join. Snowflake will return an “invalid identifier” error if you reference an object that does not exist in the context of your query. The following article goes over identifiers, and some use cases where an identifier is necessary.

Double quotes vs. Single Quotes

In some programming languages, single quotes and double quotes can be used interchangeably. In Snowflake, that it’s not the case. Single quotes and double quotes mean two different things in Snowflake. It’s important to understand the difference between single quotes and double quotes in Snowflake.

Double Quotes

Double quotes are used for identifiers in Snowflake. An identifier is the name of an object. For example, a table or a field. Identifiers don’t need to be double-quoted. But in the case where there is a space, or the case sensitivity is required, then double quoted identifiers of the way to go.

The following example demonstrates using double quotes for identifiers.

select
"field1" as "field 1",
"Another Field" as "field 2"
from
"TEMP.PUBLIC.TEMP_TABLE"

To use numbers, special characters, extended ASCII or non-ASCII characters, blank spaces, or if you want to preserve case sensitivity in an object name, then you need to use double quotes for your identifier. If an object is created using a double-quoted identifier, the object can only be queried using the exact double-quoted identifier for that particular field, including case sensitivity.

select
field_with_spaces as "Field with Spaces"
from
test.table." WithCaseSensitiveIdentifier"

Single Quotes

Single quotes in Snowflake are used to create strings. You cannot use single quotes for the names of objects. Single quotes are used to create strings in the contents of a table. Single quotes are also commonly used to create strings for filtering, such as in a where clause.

select
'sales' as table_name,
*
from
logs
where
record_type = 'charge'

Aliases

Aliases allow you to rename a field or a table right within your query.

Aliases follow the name of an object in a select query and optionally can be proceeded within AS for readability. Aliases can be used to rename fields, as shown in the previous queries. An alias can also be used to name objects such as a subquery, which can be thought of as a temporary table object. You need to use an alias if you want to join a subquery. You cannot join to a subquery outside of the subquery without an alias. Here’s an example of a subquery with an alias and a join outside of itself:

select
t.*,
lookup.name
from
transactions as t
left join (select distinct id, name from duplicate_data) as lookup
on t.attribute_id = lookup.id

Common Table Expressions (CTEs)

An alternative to subqueries are common table expressions, commonly referred to in the abbreviated form: CTE. CTEs are another way of giving a temporary name to a temporary table object. CTEs behave much like subqueries, but CTE syntax makes your SQL much more readable, and a CTE can be reused more than once in the same query. Here’s the same query as above, written with CTEs instead of subqueries:

with lookup as (
select distinct
id,
name
from duplicate_data
)
select
t.*,
lookup.name
from
transactions as t
left join lookup
on t.attribute_id = lookup.id

Wrap-up

We just looked at the Invalid Identifier Error in Snowflake when performing a Join. It’s easy to overlook something as simple as different quote types. Subqueries and joins can be complex. With Datameer, you don’t need to worry about the details of Snowflake SQL syntax. Datameer’s drag and drop interface means you can focus on the bigger picture.