RedShift's big con was it uses a new skill set so the team would all have to learn it, we would then have to change all of our code (app and DB) to make them work. That's over 1000 stored procedures, across 10 core servers for what benefit.
Azure (including DW) - We like AzureDW a lot but you couldn't do linked server queries or even cross database ones (at the time we tried - perhaps you can now). This would have meant a massive change to us.
In both cases the cost in time just outweighed the benefits, so for now we are going down the SQL2017 running on a VM route and we will port over to Azure(DW) in the future when we feel both fit our needs a little better.
At the end of the day its largely down to your environment, needs and problems. If we had the time and the appetite to do months of work or we were building from the ground up we possibly would have gone down the Azure road, but sadly right now we don't.
My advice would be don't do something just for the sake of doing it.
Hope that helps.