https://linen.dev logo
c

Chris (deprecated profile)

02/19/2021, 6:28 PM
Do you think all SQL identifiers (at least columns?) should be always quoted (and forced to be case sensitive) or we should try to avoid quotes/case-sensitive as the destination (here snowflake) allows the users to… In Snowflake: • if you quote your columns, they are case-sensitive • if you don’t quote, then they are not case-sensitive (they actually become automatically UPPERCASED) WDYT? https://github.com/airbytehq/airbyte/issues/2128
u

user

02/19/2021, 6:32 PM
How many of our corner-case naming issues would go away if we just quoted everything?
u

user

02/19/2021, 6:32 PM
Probably all of them?
u

user

02/19/2021, 6:53 PM
not all of them.
u

user

02/19/2021, 6:54 PM
redshift, even when quoted forces everything to lower case, for example. DBT gets confused if subsequent queries aren't then lower cased.
u

user

02/19/2021, 6:54 PM
ah
u

user

02/19/2021, 6:54 PM
how does quoting everything affect how users would query their data?
u

user

02/19/2021, 6:57 PM
most people (i think?) are not super familiar with quoted querying. generally people use unquoted functionality within their own db. when we move data from one db to another though, some things that used to not need quoting might need quoting. i think part of the question is for a user who is not super familiar with the quoting stuff, would they prefer in the destination to have the names of their data be slightly different but not need quoting or the names of their data are exactly the same (where possible anyway), but they now need to execute queries using quoted syntax.
u

user

02/19/2021, 6:57 PM
in general using quoted syntax internally seems very reasonable, but we should be a little careful about unintentionally forcing users into using quoted syntax when querying their data.
u

user

02/19/2021, 7:16 PM
Yes, case sensitive is great to enforce your sql code style etc for example in your code base… But when you are a business users that is just querying a table in the metabase/looker/mode BI tool etc. They don’t really care about what casing the table is using… (“what does camel or snake have to do with how i type my query?“) they just want to count how many rows are doing something So if they can refer to fields in unquoted/case-insensitive, they have a much better user experience as non-technical that don’t know what SQL is
u

user

03/09/2021, 6:30 PM
Can we make it so that all table and field names are in UPPERCASE? It sucks to have to use the quoting identifier in DBT for the sync'd sources.
u

user

03/09/2021, 6:34 PM
i believe it is now the case with latest version?
u

user

03/09/2021, 6:43 PM
@Nate Can you see if this is working in the latest version you have deployed?
u

user

03/09/2021, 6:46 PM
I think nested table names are not fully UPPERCASE
u

user

03/09/2021, 7:06 PM
There’s not an option for this in the UI, it’s just default behaviour to uppercase unnested field/table names?
u

user

03/09/2021, 8:08 PM
that’s just the default behavior of snowflake too:
Copy code
By default, Snowflake applies the following rules for storing identifiers (at creation/definition time) and resolving them (in queries and other SQL statements):

    When an identifier is unquoted, it is stored and resolved in uppercase.

    When an identifier is double-quoted, it is stored and resolved exactly as entered, including case.
https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html#label-identifier-casing