Why do you see error DBMS error [SQL compilation error: syntax error line 1 at position 60 unexpected 'and'] on the merge statement in Snowflake Target when using burst mode
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 a channel is defined with TableProperties action for replicating the same table with different column layout and into different schemas in Snowflake but the user logs in to Snowflake target as the 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 rows into the base table.
This can cause other channels to fail because multiple channels are simultaneously accessing the same table on Target.
To resolve this error make sure that either the
Use different users to log in to the same database and have different default schemas for all users.
The table name is different in different channels