If you are a Matillion user, chances are that you have been in the following situation before. You build an entire pipeline and meticulously design each and every aspect to transform your data in the perfect way. At the end of your job, a nice data source is created that is ready to provide some tangible benefit to your organization. Pleased with your work, you go and run that Matillion job. After the job is complete, you jump into the Snowflake UI and run a basic query against your new data source, only to be met with that awful error message:

After frantically trying to figure out what’s gone wrong, you realize it’s not a problem with your Snowflake permissions, the Matillion job itself or the location of the table you’ve created. The problem is that your table has been created in all lowercase, and now you can’t query it without putting quotation marks around every named identifier related to that table:

Suddenly, the cool thing you’ve built is abstracted behind a wall of error messages your end users are hitting. Case sensitivity was never your intention when you created the table, and now it is inhibiting your end users from making use of the data. Luckily, there is a very basic solution to removing this burden that we can use to solve this problem.

Why Are My Tables Created to Be Case Sensitive?

The reason we see the case sensitivity issue is because Matillion sends all our queries to Snowflake wrapped in quotation marks. This is intended to avoid errors downstream in the event that a user creates objects and separates the names with spaces, special characters or uses SQL-specific terms that could confuse Snowflake if they weren’t referred to explicitly as identifiers.

This is all fine and well, but in the context of our case sensitivity issue, it can cause frustration. When Matillion sends a query wrapped in these quotation marks, Snowflake by default thinks, “Okay, so this table is named Sales, not SALES, or sales. If you ask for anything other than Sales, I will tell you that it does not exist.” This behavior extends to all the identifiers. Column names, view names, sequences and any other identifiers are case sensitive by default.

This case sensitivity is where we constantly see customers get frustrated with Matillion. When using the forms within Matillion, it is human nature to follow standard grammatical rules like capitalizing the first letter of a word, but most end users don’t realize the impact that will have on using the objects in Snowflake. Fortunately, fixing this is pretty simple.

Connection Settings

At a very basic level, Matillion is just connecting to Snowflake from a JDBC driver. Matillion facilitates this connection through the use of their Environments object, and within our environment, we can specify different connection properties that we would like Matillion to use for the Snowflake session (a full list of properties is available from Snowflake). If you are unfamiliar with getting an environment up and running in Matillion, please check out this blog on getting started.

Quoted Identifiers Ignore Case

To solve our case sensitivity problem, Snowflake has a connection setting we can use that will turn off case sensitivity for every query sent from Matillion. All we have to do is modify our Matillion environment and add the connection setting QUOTED_IDENTIFIERS_IGNORE_CASE. By setting this parameter to TRUE, we tell Snowflake to ignore case on all queries sent within a quoted identifier. Now Snowflake will think, “Okay, so this table is named Sales. I’m going to create it as SALES and if you ask for SALES, Sales or sales without quotation marks, I will give you the SALES table.”

The Result

After making this minor change to our Matillion environment, we now don’t have to deal with the problems of case sensitivity and can query our objects without quotation marks. This tip is really useful for those who are just getting started with Matillion and Snowflake and are trying to make using the tools simple and intuitive.

A few warning messages about using this parameter:

• You should only set this parameter in a new environment or a new project. If you update this setting on an existing environment, it is possible Matillion will not be able to read tables that are case sensitive already.
• You should ensure your data sources do not contain objects with the same names but are different in case. Matillion will not be able to create these as separate objects if they are the same word, i.e. CUSTOMER and Customer.
• You should not set this parameter at the Snowflake account level; in this example, we are only modifying it at the session level from the Matillion side.
• The parameter will impact all identifiers.

If you have any questions about this tip or any others on the blog, please feel free to reach out to me here or through LinkedIn. Happy building!