Hello @Nitin Goyal ,
Thanks for the question and using MS Q&A platform.
I think the initial ask is how to make sure that when the data load is in progress other process ( e.g PowerBI refresh) does not get impacted. I know you are inclined to have READ ONLY replica as we had with in-premise SQL Server and as correctly pointed we do not have that in the current dedicated SQL.
I wanted to know your thoughts if you want to scale-up the compute of dedicated sql just before the load starts and scale down once the ingestion is over. Since you are already using a ADF you can call a procedure and add the logic called out here https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/quickstart-scale-compute-tsql and scale down once the load is done with the current values.
If this approach works, it will be cost effective then the below approach.
Since you are already using the ADF to pump data in dedicated SQL maybe you can use ADF to pump data to a new SQL Azure also.
Thanks
Himanshu
Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.