To expand upon this:

in essence the SCA projects are just a set of sql files in defined folders, and holding some additional settings related to build order, how to locate release details in target database etc. All actions required for building, deploying are for all intents and purposes available as powershell cmdlets.

Triggering powershell cmdlets within an VS code extension is quite possible, though I imagine the current cmdlets for build have some expectations that would have to be gracefully handled when triggered from within VS Code.

Of course there is the whole thing about reverse-engineering for changes, sql compare options etc, but to be honest I worry less about that in a larger team and DWH setup, and more about ensuring everyone can quickly work together on the same project. our migration scripts tends to be 60% hand-crafted/altered any way. I'd happily accept a VS code approach that did not give you the reverse engineering option directly.

Provisioning options: sql or clone(s) should most certainly be a separate yml configuration file (which we could easily swap during CI/CD pipelines)