Problem Summary: Why do you see error DBMS error [SQL compilation error: syntax error line 1 at position 60 unexpected 'and'] on merge statement in Snowflake Target when using burst mode
Description:
This article describes the possible conditions when this error can be encountered.
This error is seen when the burst table is shared between 2 tables. This situation can arise when channel is defined with TableProperties action for replicating the same table with different column layout and into different schemas in Snowflake but user logs in to Snowflake target as same username for all the channels.
As the same snowflake location is used for all the channels the burst tables are created in the default schema and hence the same burst table is shared throughout the channels.
This causes the SQL statements to be created incorrectly to insert row into base table.
This can cause other channels to fail because multiple channels are simultaneously accessing the same table on Target.
Answer:
To resolve this error make sure that either the
Use different users to log in to same database and have different default schemas for all users.
Table name is different in different channels