How to define a cluster key based on a expression in Snowflake?

Question

As per Snowflake documentation (https://docs.snowflake.net/manuals/user-guide/tables-clustering-keys.html) a cluster key can be defined as one or more table columns/expressions. The example they bring is:

-- cluster by expressions
create or replace table t2 (c1 timestamp, c2 string, c3 number) cluster by (to_date(c1), substring(c2, 0, 10));

I want to extract from a date column the year,month and day and to create a cluster key based on those expressions, but didn't find a workaround. This is what i tried already:

 CREATE TABLE TBL_DATECREATED  (DATECREATED_UTC)  
 CLUSTER BY (
              TO_DATE(DATECREATED_UTC)
              )  
 AS
 SELECT DATECREATED_UTC FROM BASETABLE_CONTACTS

Result:

 SQL compilation error: invalid type [TO_DATE(TBL_DATECREATED.DATECREATED_UTC)] for parameter 'TO_DATE'

---

**Mention: SELECT TO_DATE(DATECREATED_UTC) FROM BASETABLE_CONTACTS works fine!

CREATE MATERIALIZED VIEW MV_DATECREATED (DATECREATED_UTC, EMAILADDRESS)
CLUSTER BY ( year(DATECREATED_UTC)
             -- extract(year from DATECREATED_UTC)
             ,EMAILADDRESS
            )
AS
SELECT DATECREATED_UTC, EMAILADDRESS FROM BASETABLE_CONTACTS 

Result:

SQL compilation error: Function EXTRACT does not support UNKNOWN argument type
(for commented expression i received the same error message)

---

CREATE MATERIALIZED VIEW MV_DATECREATED (DATECREATED_UTC, EMAILADDRESS)
CLUSTER BY (  DATECREATED_UTC
             ,substring(EMAILADDRESS, 1, 3)
            )
AS
SELECT DATECREATED_UTC, EMAILADDRESS FROM BASETABLE_CONTACTS 

Result:

SQL compilation error: error line 3 at position 14 Invalid argument types for function 'SUBSTRING': (UNKNOWN, NUMBER(1,0), NUMBER(1,0))

Thanks in advance for each suggestion/solution!

Answer 1

Try the following, when you define the clustering key at the same time as you create the table maybe Snowflake isn't able to determine the datatype of the column properly?

CREATE MATERIALIZED VIEW MV_DATECREATED (DATECREATED_UTC timestamp, EMAILADDRESS varchar)
CLUSTER BY ( year(DATECREATED_UTC)
             -- extract(year from DATECREATED_UTC)
             ,EMAILADDRESS
            )
AS
SELECT DATECREATED_UTC, EMAILADDRESS FROM BASETABLE_CONTACTS 

Answer 2

For the first error, try adding a datatype to the create table. For example:

CREATE TABLE TBL_DATECREATED  (DATECREATED_UTC timestamptz)

For the second and third issues, check that the data types are what you're expecting.

Answer 3

We have to use the below statements to define a cluster key based on the expression for a materialized view.

CREATE or replace MATERIALIZED VIEW MV_DATECREATED (DATECREATED_UTC,EMAILADDRESS) cluster by(DATECREATED_UTC,EMAILADDRESS ) AS SELECT to_date(DATECREATED_UTC), EMAILADDRESS FROM BASETABLE_CONTACTS;

CREATE or replace MATERIALIZED VIEW MV_DATECREATED (DATECREATED_UTC,EMAILADDRESS) cluster by(DATECREATED_UTC,EMAILADDRESS ) AS SELECT DATECREATED_UTC, EMAILADDRESS FROM BASETABLE_CONTACTS;

alter materialized view MV_DATECREATED cluster by(TO_DATE(DATECREATED_UTC),EMAILADDRESS );