👓
Object 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).
- 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.
- 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 modified 1yr ago