Dwh.dev documentation
  • What is Dwh.dev?
  • Features
    • Data catalog
      • PIPEs
      • Fancy SQL Highlight
    • Data Lineage
      • Object-level lineage
      • Column-level Lineage
      • In-query lineage
      • Navigation
      • JOIN and WHERE
      • Equals column lineage
      • Strong and Weak dependencies
      • Default and Virtual Columns
      • Implicit Types casting
      • Circle dependencies
      • Argument forwarding
    • TASKs
  • Integrations
    • Snowflake
      • Offline mode
      • Secured online mode
      • Snowflake Marketplace
    • DBT
    • ETL
    • BI
      • Looker
      • Other
    • API
  • Snowflake SQL Syntax and Behavior
    • Identifiers
    • Reusing column aliases
    • SELECT * ILIKE EXCLUDE REPLACE RENAME
    • Scalar functions name resolution special behavior
    • Functions overloading
    • CTE as an expression alias
    • ASOF Join
    • UDF named arguments
    • Objects auto renaming
    • Columns auto renaming
Powered by GitBook
On this page
  • Basic Syntax (Youtube)
  • Special syntax (Youtube)

Was this helpful?

  1. Snowflake SQL Syntax and Behavior

Identifiers

PreviousSnowflake SQL Syntax and BehaviorNextReusing column aliases

Last updated 8 months ago

Was this helpful?

Basic Syntax ()

Snowflake provides an extensive toolkit for working with object and column identifiers. Let's start with the basics: identifiers with and without quotes ()

Even at this stage, we won't achieve full compatibility with the syntax of other databases. However, two nuances deserve special attention:

  • Identifiers without quotes are converted to uppercase.

  • Identifiers within backticks behave similarly.

CREATE TABLE `Myidentifier` (
  f5 INT
);

CREATE TABLE "quote""andunquote""" (
  f6 INT
);

We collected all the varieties of the basic syntax

Here is the result:

In addition to the basic syntax, Snowflake has special functionality:

CREATE VIEW V1 AS
  SELECT * 
  FROM
    identifier($table_var1)
;

-- Column Identifier
CREATE VIEW V4 AS
  SELECT identifier('DEMO_DB.SCH1.T1.V1'):json_prop as prop 
  FROM 
    DEMO_DB.SCH1.T1
;

-- Double-Dot Notation
CREATE VIEW V8 AS
  SELECT * 
  FROM
    DEMO_DB..T3
;

This syntax is often found in the description of transformations because it helps to use the same source code with different object names.

Special syntax ()

. A special IDENTIFIER() function to get a reference to an object or column from a session variable or string.

Special function TABLE() to get a reference to an object from a session variable or string.

A special syntax that allows the PUBLIC scheme to be omitted when addressing an object.

We have collected all kinds of special syntax .

Here is the result:

Youtube
Literals and Variables as Identifiers
Table Literals
Double-Dot Notation
in 1.identifiers.2.sql
Dwh.dev
Youtube
documentation
in 1.identifiers.1.sql
Dwh.dev