Destination snowflake: remove raw table case-sensitivity

[edgao]

follow-on to https://github.com/airbytehq/airbyte/issues/30010

raw tables are still case-sensitive, i.e. must be queried as select * from "airbyte_internal"."foo_raw__stream_bar". we... probably should make those also case-insensitive, i.e. queryable as select * from airbyte_internal.foo_raw__stream_bar?

this is a bit trickier because we can't just do a soft reset, we'll need to actually do an alter table or create table ... select sort of thing.

for now, there's a relatively easy workaround to just create views on whatever raw tables:

create schema airbyte_internal;
create view airbyte_internal.foo_raw__stream_bar as
select
  "_airbyte_raw_id" as _airbyte_raw_id,
  "_airbyte_extracted_at" as _airbyte_extracted_at,
  "_airbyte_loaded_at" as _airbyte_loaded_at,
  "_airbyte_data" as _airbyte_data
from "airbyte_internal"."foo_raw__stream_bar"

[bibhutis007]

Please prioritize this issue. We run Permifrost to manage grants and privileges in our snowflake and the lowercase schema and table name for airbyte_internal is causing Permifrost runs to fail. I have tried explicitly defining lowercase names in permifrost yaml file and it fails.

[apd-bscally]

Also would like to see this resolved, we have seen multiple issues in downstream tools because of this

[cbuckle1]

Causing us issues as well

[bibhutis007]

Workaround:

• In Airbyte navigate to destination setting.

• Expand optional fields and under Raw Table Schema Name specify the schema name e.g. AIRBYTE_INTERNAL

• Rename quoted lowercase schema name to unquoted schema name so that schema name is case insensitive. alter schema DATALAKE_AIRBYTE."airbyte_internal" rename to DATALAKE_AIRBYTE.AIRBYTE_INTERNAL;

• Tested syncing data after above change and it works. Also tested running Permifrost for grants and privileges and it worked.