I'm pretty sure you can start, scale, and pause now with TSQL in ADW. I will check though later today. I say this because I'm pretty sure I redesigned one of our python scripts to do so. But I can't think right now as I just woke up and completely tired from a conference I was at all day yesterday.

For example, I am pretty positive the below query will actually resume your data warehouse if you execute the query in a paused state I do believe. The master is always online regardless of the ADW state.


ALTER DATABASE mySampleDataWarehouse
MODIFY (SERVICE_OBJECTIVE = 'DW300');


Outside of that, in case I'm totally wrong, we used the API to fully control the data warehouse with Python. We start and pause the data warehouse on a schedule each day from a simple python script on a Windows scheduler. Easy peasy.

To execute TSQL once resumed, we use pyodbc (python module we use for all our SQL Servers) to execute stored procedures as well load in data from Polybase via External Tables. The issue here is that Polybase does return rejected rows as the output to console, which causes pyodbc to rollback the transaction. Microsoft is working on ways to nullify this output or redirect it to a file or something to help people collect these failed records for logging/investigation purposes.