I am trying to read data from s3 to snowflake . Data is in Avro format and is in YYYY/MM/DD folder structure. I am trying to query this staging table in snowflake and I am getting an error

  select 
  $1 as data,
  substr(METADATA$FILENAME,31,10) as rpt_date
  ,METADATA$FILENAME FILENAME , 
         METADATA$FILE_LAST_MODIFIED ,
          METADATA$START_SCAN_TIME ,
$1:"App Selected Coverage"::varchar as APP_SELECTED_COVERAGE,
$1:"Prior_Term_POLICY_NUMBER"::varchar as PRIOR_TERM_POLICY_NUMBER
  
  
  FROM @finance_analytics_stage  limit 10

Error: 100084 (22P02): Error parsing AVRO: bad record field: "App Selected Coverage" contains a character which is not alphanumeric or _

How to query this stage?

I am trying to read data from s3 to snowflake . Data is in Avro format and is in YYYY/MM/DD folder structure. I am trying to query this staging table in snowflake and I am getting an error

  select 
  $1 as data,
  substr(METADATA$FILENAME,31,10) as rpt_date
  ,METADATA$FILENAME FILENAME , 
         METADATA$FILE_LAST_MODIFIED ,
          METADATA$START_SCAN_TIME ,
$1:"App Selected Coverage"::varchar as APP_SELECTED_COVERAGE,
$1:"Prior_Term_POLICY_NUMBER"::varchar as PRIOR_TERM_POLICY_NUMBER
  
  
  FROM @finance_analytics_stage  limit 10

Error: 100084 (22P02): Error parsing AVRO: bad record field: "App Selected Coverage" contains a character which is not alphanumeric or _

How to query this stage?

• snowflake-cloud-data-platform
• snowflake-schema

• What happens if you don't cast the "App Selected Coverage" as a VARCHAR? – Bryan Crystal-Thurston Commented Mar 14 at 13:10

1 Answer 1

Snowflake's Avro parser complies with the Avro specications:

https://avro.apache./docs/1.12.0/specification/#names

The name portion of the fullname of named types, record field names, and enum symbols must:

start with [A-Za-z_] subsequently contain only [A-Za-z0-9_]

Snowflake's Avro parser checks if the field name consists of digits, alphanumeric characters, or underscores (_). Your field name contains space characters, which don't match any of the above. This is why you're encountering the error.