Hi @ManojkMohan 

Thanks for pointing out the issues. I now get the pain points of writing schema dynamically. The issue with schema read while concurrent writes are happening. And in streaming / DLT tables the schema reads for each write would add to a huge cost.

But, the solution you provided also has its own drawbacks:

• Insert statement doesn't support schema evolution. Insert statement only allows new columns to be present at the end, which I can't control.
• Introducing two writes doesn't have a single point of failure. What if one statement ran while the other failed? Like DELETE ran but INSERT failed? We would have an intermediate state of data which is not usable
• Also, introducing two writes doubles the time of the operation.

Also, what is the benefit of column mapping? It focuses on renaming and deleting columns, neither of which I'm doing here.

Keeping in mind your points, I have these solutions in mind

• Use merge to insert data. But this also has the issue of not having single point of failure.

MERGE WITH SCHEMA EVOLUTION INTO test_table target
USING v_final source
ON target.id = source.id
WHEN MATCHED THEN DELETE;

MERGE WITH SCHEMA EVOLUTION INTO test_table target
USING v_final source
ON target.id = source.id
WHEN NOT MATCHED THEN INSERT *;​

• Double the data, using one set for deletes and another for writes. This helps in building a single point of failure. But doubling of data would not be suitable when we are dealing with huge volumes of data.

MERGE WITH SCHEMA EVOLUTION INTO test_table target
USING (
  SELECT *, 'delete' AS mode FROM test_view
  UNION
  SELECT *, 'insert' AS mode FROM test_view
) AS source
ON target.id = source.id AND source.mode = 'delete'
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT * EXCEPT (mode);​

If you have any other approaches in mind, would like to hear that.

As of now, I have a batch setup and can ensure there are no concurrent writes happening on the table. So, breaking down the merge into two parts will just double the time.