The way I hopefully figured this out (am going to try it out in a couple of days), was first doing it manually, getting an alert out to a teams channel (configured as a destination) , using the default template. When it actually wrote on teams, I got a link to a job (which apparently isn’t visible on the workflows page). Looking at the yaml of the job, it seems whenever a schedule is added to an alert, it creates a workflow with a “sql_task”. 
Now, we have already been writing to teams channel, for failed jobs using email notifications and using the mail id of the channel (yes, there is an email link for a teams channel).

So, I would approach this as:

1. Create a saved query (through api, you can use terraform too).

2. Create an alert for the query. This only creates an alert with no schedule.

3. Create a workflow/job through api with a sql task pointing to an alertid and using email_notifications for the job (email notification can be used for both teams and to normal mailing groups).

I hope this should work, but I have to try it out and confirm in a few days.

This should however have to be simpler.