Question: I have created new dataset using snowflake connector and used the same as source dataset in lookup activity.

Then I am trying to INSERT the record into snowflake using following query.
'INSERT INTO SAMPLE\_TABLE VALUES('TEST',1,1,CURRENT\_TIMESTAMP,'TEST'-- (all values are passed)

Result: **The row getting inserted into snowflake but my pipeline got failed stating the below error.**

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid: 'INSERT INTO SAMPLE\_TABLE VALUES('TEST',1,1,CURRENT\_TIMESTAMP,'TEST');'

Could you please share you advise or anylead to solve this problem.

Thanks.
Rajesh

Answer: 
Lookup, as the name suggests, is for searching and retrieving data, not for inserting. However, you can enclose your INSERT code in a procedure and execute it using the Lookup activity.

[![enter image description here](https://i.stack.imgur.com/ufsgk.png)](https://i.stack.imgur.com/ufsgk.png)

However, I strongly **do not recommend such an action**, remember that when inserting data into Snowflake, you create at least one micro-partition with a size of 16MB, if you insert one line at a time, the performance will be terrible and the data will take up a disproportionate amount of space. Remember Snowlfake is not a transaction database (! OLTP).

Instead, it's better to save all the records in an intermediate file and then import the entire file in one move.
You *can* use the lookup activity to perform operations other than selects, it just HAS to have an output. I've gotten around it with a postgres database doing create tables, truncates, one off inserts by just concatenating a

select current\_date;

after the main query.

Note, the sql script activity will definitely be better for this, we are waiting on postgres support in that though.