CODEWITHSUNDEEP
azuresnowflake-cloud-data-platformazure-blob-storageazure-data-factorysnowflake-schema
rakesh.data
rakesh.data

Reputation: 119

azure blob storage to snowflake table data loading error invalid identifier

this is the most asked question but i didn't find any clear answer yet.

i'm trying to load data from azure data blob storage to snowflake table by doing some transformations in azure data factory activities.

my job is failing with this error:

Job failed due to reason: at Sink 'sink1': SQL compilation error: error line 1 at position 22 invalid identifier '"Employee_ID"'

my dataset is a CSV placed in blob storage with this schema.

enter image description here

enter image description here

my table in snowflake has this schema

enter image description here

my sink in data flow has these settings

enter image description here

enter image description here]5

This is longer going on issue I couldn't fix it, if there is any help on this i can stop following copy command in cmd which is manual, mentioned below.

COPY INTO COPY_DB.PUBLIC.ORDERS
    FROM @aws_stage_copy
    file_format= (type = csv field_delimiter=',' skip_header=1)

every time my pipeline copying file from blob storage to snowflake table, this error is poping `

"Job failed due to reason: at Sink 'sink1': SQL compilation error: error line 1 at position 22 invalid identifier '"Employee_ID"'

all the scenario I've mentioned in the above I've followed as process of correctly setting up my datasets, table creation step in snowflake, still I'm not able to load, I'm curious whether snowflake is including header of the datasets, while inserting?

Upvotes: 0

Views: 500

Answers (1)

Luis Arteaga
Luis Arteaga

Reputation: 843

just want to understand why snowflake is considering header as input data while inserting into any table, anything with detailed explanation.

I think it is useful feature to define if your source csv file have a header or not. Different data sources produce different kind of csv files some with header, some without, some with semicolon as delimiter, some with comma as delimeter etc.

It is a good design to include this parameters to make the data loading process flexible otherwise you need an additional step to prepare the file for uploading.

skip_header just means skip the first row. Snowflake doesn’t know if the file have a header.There is no auto-detect.

Upvotes: 0

Related Questions