I don't know how to characterize such morphisms, which I think is your first question. However, this can certainly happen, even if $f$ is not smooth. (By the way, your comment about absence of local monodromy, and purity of limit MHS isn't correct.)
Prop (Zucker). If $Y$ is a curve, then $$R f_*\mathbb{C} = \bigoplus_i R^if_* \mathbb{C}[-i]$$
Since Zucker in section of 15 of his 1979 Annals paper proves a slightly weaker statement. Let me sketch a proof using stuff that appeared since. I can flesh it out if needed.
Sketch. Let $D\subset Y$ be the discriminant, $j:U\to Y$ the complement. By the decomposition theorem of BBDG, the object above decomposes as a sum $\bigoplus L_i$, where $L_i$ are translates of pure perverse sheaves. We can assume the $L_j$ are translates of minimal extensions. By restricting to $Y-D$ and applying Deligne (Théoremes de Lefschetz...), we can identify $L_i|_U=R^if_*\mathbb{C}|_{U}[-i]$, after reindexing. It follows that $L_i=j_*R^if_*\mathbb{C}|_{U}[-i]$ for sheaves supported on $Y$. There may be other summands supported on $D$ which need to accounted for. Use the local invariant cycle theorem to get a surjection $R^if_*\mathbb{C}\to L_i$. By purity (in the sense of Hodge modules, say) we can split this. So that we can absorb all $L_k$ with support on $D$ into some $R^if_*\mathbb{C}$
Added Comment: Regarding the latest question, I think I was too hasty in my comment. The example I had in mind doesn't satisfy all your requirements, but it may still be interesting to describe. One has a pencil of genus 2 curves degenerating to a union of 2 elliptic curves at each singular fibre. Contracting one of the elliptic curves from each pair results in singular surface mapping to a curve such that the higher direct images are constant. This is probably similar to what Ulrich was suggesting.