@Thushar R​  Yes you are right. As Delta table is keeping a transaction log and maintain version history of your data, it can easily roll back your transaction in case of a failure -> i.e. Once transaction in successfully committed, that is when the transaction log gets updated with the information of new data files. As delta engine reads the transaction log to identify the relevant files and not the underlying data store, it can easily identify the committed files alone and get you the proper outputs.

About the multi statements in a single transaction, right now Delta Lake supports only MERGE which by itself supports DELETE and UPSERT based on a condition. The condition can be set both at the source table and target table. If you are interested to deal with Snowflake data and write to Snowflake using Spark Connector, then you can achieve this. But if your source and target are within delta lake, only MERGE is supported as of now I believe.