Identifier in Snowflake

Introduction

Identifier is also a nice feature of Snowflake. I forgot how many times I wanted to SELECT * FROM <dynamic table> IN SQL SERVER scripting. This is not possible without dynamic scripting dynamic SQL) in SQL Server. In Snowflake you can use the keyword IDENTIFIER.

IDENTIFIER

Let me give you an example with a variable.

DECLARE 
    table_name STRING;
    N INTEGER DEFAULT -4;
BEGIN

    SET table_name := 'Calendar';
    SELECT COUNT(*) INTO :N FROM IDENTIFIER(:table_name);
    RETURN N;
END

With this you can parameterize the query and dynamically reference a different table.

Final thoughts

A small example on how to work with IDENTIFIER.

Hennie