This worked really well for us in Agile projects, are there good success stories of using this in waterfall methodology?

Yes, no matter the project metrology (we use agile, interactive, and waterfall), Azure Pipelines works well for both build and release automation to multiple environments (STG, UAT, PRD). It documents the release process, allows approval for releases, and lets developers know to not get the latest if there is a build problem with the checked in code. It also helps complex build and release steps run consistently the same, eliminating human error.