Lock a table for a CTAS upsert in Azure SQL DataWarehouse
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.

Other approaches to this such as sp_get_applock are also unavailable.  Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.