superset: Snowflake: Inconsistent column name case

@villebro thanks a lot for giving this issue some love 😃.

The suggested approach seems like a simple yet effective solution to me.

Although using the cursor to get the column names is a likely fix I do feel strongly that the database driver should not be applying any case transformations on the payload returned from the db.

Since @villebro did propose that as a solution to the issue, I agree, it would be good to his thoughts on the matter

Here was the fix for anyone who encountered this: https://status.preset.io/pages/incident/5e83b80830610b04c39e898b/64b5823a267e5c053c6ed085

Hi all, this change has broken all of our charts and filters due to the change from lowercase naming to uppercase. This impacted our existing old datasets when I clicked “Sync Columns From Source.”

Is there any guidance on how to make this backward compatible with existing assets? @betodealmeida @Vitor-Avila

I second @rumbin very much here, this is a simple and effective solution. Thinking about our Superset instance, this will definitely address the problems we have with dashboard filtering.

Thank you a million @villebro @rusackas, very much appreciate this.

We’ve been throwing around ideas about this with @rusackas for the last few weeks, and my proposal is to change the behavior going forward as follows:

• virtual dataset logic remains unchanged
• physical tables call denormalize_name on the column name. See example below:

>>> from snowflake.sqlalchemy.snowdialect import SnowflakeDialect
>>> dialect = SnowflakeDialect()
>>> dialect.requires_name_normalize
True
>>> dialect.denormalize_name('foo')
'FOO'
>>> dialect.denormalize_name('Foo')
'Foo'
>>> dialect.denormalize_name('FOO')
'FOO'

This means that going forward, caseless physical column names will be stored in UPPERCASE, rather than lowercase. We’ll also do the same for Oracle and the others that need to call the standard denormalize_name method:

>>> from sqlalchemy.dialects.oracle.base import OracleDialect
>>> dialect = OracleDialect()
>>> dialect.requires_name_normalize
True
>>> dialect.denormalize_name('foo')
'FOO'
>>> dialect.denormalize_name("Foo")
'Foo'
>>> dialect.denormalize_name("FOO")
'FOO'

This is in contrast to MSSQL and other dialects that don’t require name normalization:

>>> from sqlalchemy.dialects.mssql.base import MSDialect
>>> dialect = MSDialect()
>>> ms.requires_name_normalize
False
>>> ms.denormalize_name('foo')
'FOO'
>>> # this ☝️ surprised me!

So calling dialect.denormalize_name(col_name) if dialect.requires_name_normalize is True will ensure that physical and virtual datasets have the same case. Furthermore, I propose not doing any migrations to existing datasets, as the migration would be extremely risky, avoiding breaking any existing dashboards or charts. This means, that this change would only affect physical datasets created after the change. Therefore, anyone who wants to migrate old charts/dashboards to the new behavior will need to edit the dataset and click “Sync columns from source”:

After this the lowercase column names will become UPPERCASE:

Keep in mind, however, that this might break existing charts, as column references in both chart and dashboard native filter metadata might be pointing to the old normalized column name.

Thoughts @agusfigueroa-htg @rumbin @nytai ?

I can sadly confirm that the issue remains 😦

This issue should not be closed by the stale bot, in my eyes.