👓Object identifiers

Identifier rules

SnowDDL takes a slightly different approach to resolution of Snowflake identifiers.

• Identifiers are always upper case.

• Identifiers are always enclosed in " (double-quotes) when formatted for SQL queries.

• Only ascii_letters(a-z), digits (0-9), _ (underscore) and $ (dollar) characters are allowed.

• For complex identifiers (with env prefix or role suffix), the delimiter is always __ (double underscore).

Rationale

• Lower case identifiers are usually created by accident. Unlike upper case identifiers, lower case identifiers must be enclosed in " to access. Mix of lower case and lower case identifiers cause a lot of confusion. SnowDDL does not allow lower case identifiers to prevent this confusion from happening.

• Some words in Snowflake are reserved (e.g. CREATE, TABLE, WHEN). Such words cannot be used as identifiers without being enclosed in ". However, the list of reserved words is being updated all the time, and a word which is not reserved today may become reserved tomorrow. SnowDDL makes sure all identifiers will be valid in DDL queries regardless of current state of "reserved words".

• Snowflake has a session parameter which is called QUOTED_IDENTIFIERS_IGNORE_CASE. It applies upper case transformation to all identifiers, including identifiers enclosed in ". SnowDDL will work the same way regardless of value of this parameter.

• For complex identifiers a single _ underscore is not good enough to identify multiple parts of identifier. Collisions are possible. For example, if you have schema AAA.BBB_CCC and schema AAA_BBB.CCC, generation of schema role name with _ single underscore as glue will return exactly the same identifier for both. SnowDDL uses __ (double underscore) in order to mitigate this and improve readability.

Examples

Env prefix in examples is ALICE.

• Warehouse name in config: analytics_wh

  • Identifier in SQL query: "ANALYTICS_WH"

  • Identifier with env prefix: "ALICE__ANALYTICS_WH"

• Table name in config: my_db.bookings.airports

  • Identifier in SQL query: "MY_DB"."BOOKINGS"."AIRPORTS"

  • Identifier with env prefix: "ALICE__MY_DB"."BOOKINGS"."AIRPORTS"

• Function name in config: my_db.bookings.lang(varchar)

  • Identifier in SQL query: "MY_DB"."BOOKINGS"."LANG"(VARCHAR)

  • Identifier with env prefix: "ALICE__MY_DB"."BOOKINGS"."LANG"(VARCHAR)

• Business role name in config: bookings_analyst

  • Identifier in SQL query: "BOOKINGS_ANALYST__B_ROLE"

  • Identifier with env prefix: "ALICE__BOOKINGS_ANALYST__B_ROLE"

• Owner schema role for schema: MY_DB.BOOKINGS

  • Identifier in SQL query: "MY_DB__BOOKINGS__OWNER__S_ROLE"

  • Identifier with env prefix: "ALICE__MY_DB__BOOKINGS__OWNER__S_ROLE"