For example, in a project where there are many individual web sites, some with SQL databases that must be upkept, each of those projects currently has define and upkeep a separate version of a database deploy script, which being replicated in so many different steps creates a maintenance hazard.

I'm imagining a scenario where the you have a group and some child sites, where the parent group/project defines a database deploy step and some variables that can be overwritten in the child projects. So the parent supplies the powershell script step that accepts some variables and the child project is responsible for providing those variables.

And of course the child projects can then have follow-on steps to deploy nuget packages/deploy to azure, etc.