How to enforce that metadata columns with Databse Query component (JDBC to SQL server) are created in Upper Case?

Matillion ETL
1

We are currently in a version of matillion where most of the jobs developed deal with loading data from sql to snowflake. And as part of this version the staging tables are created with UPPER CASE column names. And accordingly all target tables where SCD check is performed are also defined using UPPER CASE meata data (column names).

 

Now when we try upgrading the version to 1.68.12 and run these same existing jobs, the staging tables are getting created with meta data/column names as it in the SQL source which is Init Cap.

 

Due to this the transformation jobs are failing as matillion is looking up literal column names form staging to target tables (which have already been defined with UPPER CASE)

 

Are there any generic config options available to control how metadata should be created in the staging tables?

 

Observation: API Query component still creates the staging table with UPPER CASE column names in the upgraded version.

2

You can try setting up this parameter QUOTED_IDENTIFIERS_IGNORE_CASE inside Snowflake to FALSE. This will ignore InitCap case while creating the staging tables based on source table structures. This can be set as an option for your Database Query component too.

3

Yes @AnudeepK​ , Thank You! In fact with in Matillion, as part of environment confiiguration this parameter can be set to TRUE, which means snowflake ignores the case of any column in quoted identifiers (which is the matillion standard while generating SQL). This worked for me. Thanks for your reply.