Using initcap function in materialized view fails

BillBishop — Thu, 27 Feb 2025 21:21:21 GMT

This query works:

select order_date, initcap(customer_name), count(*) AS number_of_orders

from ...

The initcap does as advertised and capitalizes the customer_name column.

However, if I wrap the same exact select in a create materialized view I get an error:

create or replace materialized view <my-view-name>

select order_date, initcap(customer_name), count(*) AS number_of_orders

from...

[DELTA_INVALID_CHARACTERS_IN_COLUMN_NAMES] Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema. Invalid column names: initcap(customer_name). Please use other characters and try again. Alternatively, enable Column Mapping to keep using these characters.

Can you explain the difference?

Re: Using initcap function in materialized view fails

BillBishop — Thu, 27 Feb 2025 21:29:54 GMT

NOTE: I got it to work by aliasing the customer_name column, it's documented here: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view#limitations

However, it wasn't clear that "Non-column reference expressions require an alias" applies to ALL functions, not just aggregate functions like the example.

topic Using initcap function in materialized view fails in Data Engineering

Using initcap function in materialized view fails

Re: Using initcap function in materialized view fails