Setting aside the "debt is evil" argument, the downside is mostly the short term hit to your score. A credit inquiry stays on your report for 2 years, after that, it's gone.
Ideally, you would have cards that offer the largest reward for each category of purchase. For the fact that I like Amazon's ease of use, and decent prices, the 5% back card is just a bonus. In effect, it gives my free prime membership.
In the end, if your time and effort is worth it, just use common sense, and pace yourself. With all the ways to track your score, it's not tough to see if you are pushing your luck, getting too many cards. If so, just slow down.
I'm addressing more your specific desire to play the reward game. For the average person, a 2% cash back card might be all they need. But if you look at that and then look at your budget, you might think that one card is less than ideal. You've entered the world of multiple credit cards and deal chasing. The 5% back card at every store you frequent is 3% more than I get at those stores. The truth is, just like the extreme coupon crowd, as long as you know what you are doing, always pay in full, and never spend more than you were planning, you will be at an advantage.
5+ years back, I was offered a card that for the first 90 days gave 10% back at drug stores. Of course, the first thing I did was to drop $50K at CVS and buy 100 x $500 Visa gift cards. That was $5000 cash back to me less the $500 in fees ($5 per card). Responses were mixed. Many were convinced that this would result in wasteful spending. Others said this stunt wasn't worth the time. I maintained that a $4500 (tax free) profit can be converted into 'hours worked', and for the median earner, it was 200 hours, and then some. In the end, it's up to you whether this pursuit is worth it.