[SOLVED] Snowflake external table that sits over csv files, not handling nulls as expected

Snowflake external table that sits over csv files, not handling nulls as expected

I have many external tables that sits over csv files in S3. I have string values in numeric columns that should be parsed as null, e.g. 'na'. when I query the stage with the file format, this works as expected. However when using in external table get an error "failed to case variant valie "na" to FIXED. anyone able to help me understand why this is the case?

create or replace file format csv_format
type = csv
field_delimiter = '||'
skip_header=1
encoding='ISO-8859-15'
null_if = 'na'
field_optionally_enclosed_by = '"'


create or replace external table ext_tbl(
    col1 data as (value:c1::date)
    ,col2 number(18,2) as (value:c2::number(18,2)
)
location=@stage
file_format = 'csv_format';

My tables are 100s of columns so I dont want to have to go down the nullif in the column definition.

Solution

Instead of trying to rely on NULL_IF option that may not work for NUMBER data type, the alternative is to explicitly cast using TRY_CAST:

create or replace external table ext_tbl(
     col1 date as (value:c1::date)
    ,col2 number(18,2) as (TRY_CAST(value:c2::TEXT AS number(18,2)))
)
location=@stage
file_format = 'csv_format';