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(whatsever). gets replaced by just _!

As a result, the above query is converted to

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