topic Re: Case insensitive data in Data Engineering

Case insensitive data

dpc — Thu, 15 Jan 2026 12:39:15 GMT

For all it's positives, one of the first general issues we had with databricks was case sensitivity.

We have a lot of data specific filters in our code

Problem is, we land and view data from lots of different case insensitive source systems e.g. SQL Server

As such, we have to be very careful with our code and convert columns to UPPER when making a comparison.

Most of our code is written in SQL.

About 18 months ago I asked whether there was going to be a catalog, schema or table setting for this i.e. make the object case insensitive.

I was told it was on its way.

Not heard anything since and cannot find anything.

Does anybody know whether this is in place or expected?

Thanks

Re: Case insensitive data

szymon_dybczak — Thu, 15 Jan 2026 13:13:03 GMT

Hi @dpc ,

I think you can try to use a collation for that purpose. A collation is a set of rules that determines how string comparisons are performed. Collations are used to compare strings in a case-insensitive, accent-insensitive, or trailing space insensitive manner, or to sort strings in a specific language-aware order.

Collation | Databricks on AWS

Re: Case insensitive data

dpc — Fri, 16 Jan 2026 16:25:25 GMT

Thanks.

Collation is table specific though isn't it? and you have to apply it to each columns.

Is there a was to just say, this schema, catalog or table is case insensitive or can you only do it by column?

Re: Case insensitive data

emma_s — Fri, 16 Jan 2026 17:30:32 GMT

Hi, You can set the default collation at Catalog level or schema level and the tables in the catalog will inherit the collation. This is supported from DBR 17.1 and above.

Re: Case insensitive data

szymon_dybczak — Fri, 16 Jan 2026 17:37:35 GMT

Hi @dpc ,

Like @emma_s mentioned - you can set it at catalog/schema. Table will inherit collation. But you can also define it explicitly for a specific column within a table using collate modifier

Re: Case insensitive data

dpc — Tue, 20 Jan 2026 08:59:28 GMT

Thanks.

I'll test collation at catalog, sschema and table level using 17.1

Re: Case insensitive data

dpc — Thu, 22 Jan 2026 15:06:43 GMT

Thanks @emma_s and @szymon_dybczak

Looks positive.

Some datatype issues moving to 17.1 but that's a separate issue.

I've run some simple tests and it works well so will trial it on out full data set

Re: Case insensitive data

szymon_dybczak — Thu, 22 Jan 2026 15:27:01 GMT

Great @dpc , good to hear that 🙂

Re: Case insensitive data

dpc — Fri, 23 Jan 2026 09:45:26 GMT

It works but there's a scenario that causes an issue.

If I create a schema with defaultcollation UTF8_LCASE

Then create a table, it marks all the string columns as UTF8_LCASE

Which is fine and works

If I create the table, in the newly created UTF8_LCASE schema from an existing table using:

CREATE TABLE <destination> AS

SELECT *

FROM <source>

WHERE 1 = 2;

The table is UTF8_LCASE but all the columns aren't

So, when I use it, it remains case sensitive

Any thoughts?

Am I doing something wrong here as I really want to create tables that are structurally the same with the exception of case

I am doing this as I land the data and the initial table is derived from the landed structure, before moving it.

Thanks