The create table in Snowflake surrounds all columns with double quotes. This makes the column names in the database create as lower case. However Snowflake defaults everything to upper case.
The result of this is a simple select statement will not work without double quotes.
CREATE TABLE TEST1 ( "x1" NUMBER(38,0) NOT NULL)
The result means
select x1 from test1 fails with unknown identifier.
I need to do
select "x1" from test1;
How can I remove the quotes from the resulting create table step in Matillion. (I am having to avoid using the create table step and create all tables manually because of this)
7 Community Answers
Ian Funnell —
Matillion will always use double quotes in its DDL, because Snowflake is case sensitive and distinguishes for example between “col1” and “COL1”.
To remove the need for double quotes, you should use uppercase for the names in the column list of the Create/Replace Table component.
Snowflake identifiers which are not enclosed in double quotes get converted to uppercase. So if you’ve used uppercase names in the Create/Replace Table, you’ll be able to reference them later without double quotes.
So I'm now hitting the same problem with the MongoDB query. It seems to drop and recreate tables and it surrounds the columns with double quotes. So if the mogodb column is lowercase it recreates the table with lowercase columns.
Is there anyway around this that doesn't involve me having to query my snowflake table with every column surrounded in double quotes?
Thanks for raising this with us.
I think we need to discuss this internally and review why we ended up doing this in the first place and what could be done to resolve this.
In the meantime, snowflake does support a flag to ignore case-sensitivity with names- QUOTED_IDENTIFIERS_IGNORE_CASE. You may set this parameter to TRUE for the matillion-environment-user and any DDL by matillion will be created in uppercase. Is this something you are able to use to overcome this issue?
Please note that this setting applies to new objects only. Any previous objects with lowercase names cannot be queried once this is set to true and may give unexpected results if you have objects which share the same name but differ in case.
I had a discussion with the DEV team and (for the tool) using quotes around object-names is recommended. This is especially important from the tool’s perspective as we deal with a whole host of source-systems which behave differently around case-sensitivity, object-naming and various other nuances – using quotes around object-names is the safest option.
In your case, case-sensitivity of source-system is not a concern. We would recommended setting ‘QUOTED_IDENTIFIERS_IGNORE_CASE = true’ on your snowflake-Account or matillion-user. This would ensure snowflake would convert all DDL and SQL from matillion to uppercase and you no longer need to enclose in quotes.
Please note, once you set this parameter to true, you may not be able to query any objects in lowercase via the matillion user. You may need to re-run your etl or find some means of moving data into uppercase tables.
We will be updating our documentation to discuss more about this issue.
Please let us know if you have any further concerns.