My prediction is basically once Excel can handle larger data sizes and do basic ML algorithms (like boosting / random forest / etc), and data manipulation becomes more the work of a BA (say, via Alteryx), and they start teaching what basic algorithms do (like Random Forest, Boosting, etc.) in a business analytics course (which right now is basic hypothesis testing / linear modeling) then we will see the death of a lot of data science roles and people will transition into more specialized technical roles. I could see this moving into that direction in say 2-5 years.
I've worked with a lot of Business Analysts and they usually end up fighting over p-values and R-squares, even when the linear models they are creating are total garbage due to overfitting and multicollinearity.
I agree with you that easier to use and ubiquitous data prep+ML will eliminate a certain type of data science role. However, most good data scientists I know work on taking a business problem from no/bad/inappropriate data to the right data to address the use case. It's not just about data manipulation and ML.
