I am using the SQL transform component and trying to run a simple DELETE FROM table raw SQL in Snowflake, however, when I run the transformation job it runs successfully but no records are deleted from the table. Even when I write the query, it appears that DELETE is not being picked up as a SQL keyword because it does not change its coloring like SELECT and FROM keywords do. Can DELETE statements not be run?
In my experience, in a Transformation job, the SQL component can only contain a SELECT statement; and never a DELETE FROM statement. However, in an Orchestration job, it works to have a SQL component with a DELETE FROM command.
Hey @Singularity,
If you want to execute a delete through a transformation job, I believe the best approach is to use the Table Update component, setting the "When Matched" option to "Delete". My understanding is that a transformation job effectively serves as a string of subqueries until finally using one of the components in the "Write" folder, and a DELETE statement cannot be part of a subquery, which is why the process fails with the SQL Query component.
Cheers,
Chris
Thanks @KevinH I don't quite follow the logic of why a delete couldn't be run in a transformation but I'm assuming there is a reason. Good to know that I wasn't missing something in my transformation process - thanks for the feedback.