CI CD in Azure Synapse Analytics Part 4 - The Release Pipeline

Hey Brad - I have also created a process for doing CD for SQL scripts, releasing these to higher environments via an Azure Powershell script.  This enables me to do continuous deployment on SQL Scripts stored in the Develop part of Synapse Studio with releases happening automatically when the "Publish" button is hit. It's a little fragile, but can easily be adapted to specific circumstances.  When you say "*More to come on database migrations, a database project build, and release is still needed." - this isn't currently possible as Visual Studio 2019 doesn't support Synapse Serverless pools yet, https://developercommunity.visualstudio.com/t/ssdt-doesnt-support-openrowset-provider-syntax/1470331?from=email&viewtype=all#T-ND1485607.

For us, we have Cosmos databases storing event streams in Dev/QA/Prod.  We develop views over the Cosmos data and store these views as SQL Scripts executed against the Dev instance.  To release to the QA/PROD environment we:

1. Create scripts in Dev Synapse Studio and commit/publish
2. On publish, run an ADO build then release pipeline
3. Run the Synapse release process as you describe
4. Run a Powershell script which:
   1. Extracts the SQL Scripts from our Repo
   2. Makes modifications to the SQL Scripts depending on the environment
   3. Executes the SQL Scripts on the target environments serverless SQL DB

There are a few special things I picked up with running Powershell scripts along the way, including having to escape key powershell characters (like "$") with "`" and special cases where you need to use double quotes and escape single quotes.  But as a result we can run full CD processes for Synapse.  If anyone wants the script, I am happy to share.