Detect Changes component not working due to case sensitivity in column names

Data Productivity Cloud
1

I pull data using RDS Query from Postgres and Matillion DPC creates my stage tables with lower case column names, the same as postgres.

 

Snowflake is case sensitive and the general best practice is to ensure all object and column names are in upper case. Entering column names without quotes, Snowflake treats these as upper case. However Matillion encapsulates all object and column names so "id" is not the same as "ID", whereas id = ID when not quoted.

 

The issue is that our target tables are created without quoting and so Snowflake treats them as uppercase. The issue is that when using the Detect Changes component, I do not get a column match as "id" does not equal "ID" in Matillion's quoted world. These appears to be no way to override this.

 

The only option I see is to create a SQL component, post RDS Query, that uses a SQL script to rename all columns in my staged table to be uppercase. This seems to be a bit of a hack and Matillion needs to build in a switch for case insensitivity in the Detect Changes component or have the option to not quote table names and column names in the RDS Query when creating the staged object.

 

Am I missing anything. Is there a way to work around this?

2

On the RDS Query, you can set up one of the connection options, QUOTED_IDENTIFIERS_IGNORE_CASE to "True". When you set this option, Snowflake considers each column name in UPPER_CASE and then it will no longer be an issue.

3

That does not appear to be an option in Data Productivity Cloud. Perhaps it is in the ETL version. These are the only options in DPC that I have for RDS Query.

 

0694G00000Uu9AdQAJ_0D74G000008B6qvSAC
0694G00000Uu9AdQAJ_0D74G000008B6qvSAC1188×1552 41.7 KB

4

Following up on this, for now I am using a sql script to convert to upper case as part of a parameterized pipeline that I reuse for all Postgres to Snowflake movements.

 

0694G00000Uu9eYQAR_0D74G000008B6tBSAS
0694G00000Uu9eYQAR_0D74G000008B6tBSAS990×350 25.3 KB

 

With the script being:

 

se role ${data_ops_role};

use database ${snf_source_db};

use schema ${snf_schema};

 

execute immediate $$

– Snowflake scripting code

declare

sql_script varchar;

c1 cursor for

select

concat('ALTER TABLE ‘, TABLE_NAME, ’ RENAME COLUMN "’, COLUMN_NAME, '" to ', UPPER(COLUMN_NAME), ‘;’) as script

from

information_schema.columns

where

table_name ilike ‘${pgs_table_name}$’

AND COLUMN_NAME != UPPER(COLUMN_NAME);

begin

for col in c1 do

sql_script := col.script;

execute immediate sql_script;

end for;

end;

$$

5

Following up on this again, I have eliminated the need for the SQL Script at the end even though it worked. Anudeep K was on the right path. What confused me was that his answer made it sound like a RDW Query configuration. Turned out this was actually a Snowflake parameter setting at the account level to ignore quoted identifiers so everything is treated as uppercase.

 

I made that change at our account and now I do not have to worry about quoted identifier.

6

Yep, it is a Snowflake parameter. However, it can be configured at Connection level (if you are not happy setting it up at Account level). Haven't explored DPC much, but was answering from the context of Matillion ETL.

7

@knorman​ I'm running into the same DPC issue. When you see this would you post screenshots or directions on how to reach Snowflake parameters at the account level? are you on a Matillion Hosted Agent?

8

use role accountadmin;

show parameters in account;

 

-- Ignore quoted identifiers so that all fields are treated as uppercase

ALTER ACCOUNT SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;

 

 

This works but I would still prefer the option for the RDS Query component to create Snowflake's in uppercase rather than the source object case. Having ignore quoted identifiers case set to true does have some downsides in Snowflake though I have forgotten off the top of my head what those where.

9

Thank you! I thought this might apply to other components, but maybe it doesn't. I ran into the same issue with the SharePoint Query component.

10

Oh it probably exists with any component that is reading from a source and then creating an object as part of the load process. I see this as an issue with Progres and SQL server Where both tend to use mix case. Not so much with Oracle that tends to be in upper case like Snowflake.

 

The code I posted above is to configure Snowflake to ignore quoted identifiers so that

 

create table xyx as

( "Column1" number

, "MixCaseCol" varchar

);

 

would be created as:

 

COLUMN1

MIXCASECOL

 

For this this was mostly an issue as the detect changes is case sensitive in its column matching. This would be much better if Matillion had a check option to make this case insensitive. Until then all you can do it ensure your tables you are using have consistent column name case or you need to transform the column names within Designer before the detect changes with is a total pain if you have a large number of compare columns so I would not recommend that.