👓
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_CCCand 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