Special Behavior of identifiers

  • Anton Revyako
    Anton Revyako

    Founder of dwh.dev

Special Behavior of identifiers

New week, new "special behavior" in Snowflake :) In Snowflake, there is a special function IDENTIFIER() that allows referencing objects using strings and session variables:

SELECT
  * 
FROM
  IDENTIFIER($my_table)

Documentation can be found here: https://docs.snowflake.com/en/sql-reference/identifier-literal

Additionally, you can reference an object in the column list:

SELECT 
  IDENTIFIER($my_table).* 
FROM 
  IDENTIFIER($my_table)

Wait, that's not the "special behavior" yet…

1)

IDENTIFIER($my_table).*

works, but

IDENTIFIER($my_table).column_name 

does not

This looks very strange… But until you try this:

2)

SELECT 
  IDENTIFIER($my_table1).*, 
  IDENTIFIER($my_table2).* 
FROM 
  IDENTIFIER($my_table1),
  IDENTIFIER($my_table2)

… and you'll get twice of all the columns :)

Because IDENTIFIER(whats_ever).* gets replaced by just *!

As a result, the above query is converted to

SELECT 
  *,
  * 
FROM 
  IDENTIFIER($my_table1),
  IDENTIFIER($my_table2);