ð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 schemaAAA.BBB_CCC
and schemaAAA_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"
Last updated