i am trying to create partitions for External Tables of type Delta Lake. Unfortunately the Delta Lake file system does not have a partition implemented neither, which means no partition is mentioned in metadata. Also the file name does not contain date or any other indicator to implement partition.

So i tried implementing my own partition based on last modified data metadata

metadata$file_last_modified

But then Snowflake is throwing an error when i do so.

Defining expression for partition column part1 is invalid.

Here is my table definition

create or replace external table  DPA_PCLOG_TEST(
    file_name STRING as metadata$filename,
    **part1 DATE as DATE(metadata$file_last_modified),**
    part2 CHAR(5) as SPLIT_PART(SPLIT_PART(metadata$filename, '/', 3),'-',2),
    part3 timestamp_tz as (parse_json(metadata$external_table_partition):local_log_time::timestamp_tz),
    application_id NUMBER  AS  (VALUE:application_id::NUMBER) ,
    computer_id NUMBER  AS  (VALUE:computer_id::NUMBER) ,
    duration NUMBER  AS  (VALUE:duration::NUMBER) ,
    employee_id NUMBER  AS  (VALUE:employee_id::NUMBER) ,
    id NUMBER  AS  (VALUE:id::NUMBER) ,
    key_presses NUMBER  AS  (VALUE:key_presses::NUMBER) ,
    local_log_time TIMESTAMP  AS  (VALUE:local_log_time::TIMESTAMP) ,
    log_time TIMESTAMP  AS  (VALUE:log_time::TIMESTAMP) ,
    mouse_clicks NUMBER  AS  (VALUE:mouse_clicks::NUMBER) ,
    productivity BOOLEAN  AS  (VALUE:productivity::BOOLEAN) ,
    tenantid NUMBER  AS  (VALUE:tenantid::NUMBER) ,
    updated_time TIMESTAMP  AS  (VALUE:updated_time::TIMESTAMP) ,
    url STRING  AS  (VALUE:url::STRING) ,
    url_domain STRING  AS  (VALUE:url_domain::STRING) ,
    web_page BOOLEAN  AS  (VALUE:web_page::BOOLEAN) ,
    window_title STRING  AS  (VALUE:window_title::STRING) ,
    year_week  NUMBER  AS  (VALUE:year_week::NUMBER) 
)
partition by (part1)
with location = @VERINT_EDH_STAGE/Verint/dpa_pclog/ 
refresh_on_create = false 
auto_refresh = false 
file_format = (type = parquet) 
table_format = delta
;

Is there anyway i can implement this partition without errors ?