Are there best practices as it relates to table / field capitalization when using Snowflake and DBT? I’m coming from a postgres/redshift world where we’d typically use snake case (e.g. orders_items and product_id), but Stitch seems to be pushing data into Snowflake as all caps tables/fields (e.g. ORDERS and PRODUCT_ID). I’m trying to understand whether Snowflake prefers this format or Stitch is doing something odd. What are others here doing syntax wise with Snowflake? And, assuming we do use all uppercase tables/fields, how does that work with DBT, since it appears that tables get created based on file names?
Good q. We’ve found Snowflake’s behavior related to capitalization to be rather…cumbersome!..and have recently made some changes to dbt to deal with it more effectively.
Snowflake will by default uppercase identifier names which are unquoted. This is different than most databases which lowercase identifier names which are unquoted. Our 0.10.1 release makes it possible to control whether or not dbt quoted your identifier names.
Docs:
- https://docs.snowflake.net/manuals/sql-reference/identifiers-syntax.html#identifier-syntax
- https://docs.getdbt.com/docs/configuring-quoting
After 0.10.1, I personally prefer to lower-case all of my Snowflake identifiers and configure dbt to not quote them. I find this to be more readable, as dbt model names can be lower-cased, and SQL that references these models can also be lower-cased.
2 Likes
A related note - in talking with the folks at Stitch, they decided not to quote the tables/fields when loading into Snowflake, since that would require people to have to quote within Snowflake on the query side. That is, instead of loading data as "order_items" and then being forced to query by select * from myschema."order_items", they chose to load the data unquoted and let Snowflake upcase it, e.g. order_items => ORDER_ITEMS, which can then be accessed by doing select * from myschema.order_items
All in all, it’s still all a bit confusing, but Tristan’s advice is solid. Just wanted to give some context on the Stitch side of it since that was in my initial question.