A weight 2 modular form which happens to be a normalised cuspidal eigenform with rational coefficients has a natural geometric avatar—namely an elliptic curve over the rationals. It seems to be a subtle question as to how this notion generalises—this question was raised by Tony Scholl in conversation with me the other day. For example I guess I wouldn't expect a weight 3 normalised cuspidal eigenform with rational coefficients to be the $H^2$ of a smooth projective surface, because any such surface worth its salt would have (1,1)-forms coming from a hyperplane section, whereas the Hodge numbers of the motive attached to a weight 3 form are 0 and 2.
But in weight 4 one can again dream. A rigid Calabi-Yau 3-fold defined over the rationals has 2-dimensional $H^3$ and the Hodge numbers match up. Indeed there are many explicit examples of pairs $(X,f)$ with $X$ a rigid Calabi-Yau 3-fold over $\mathbf{Q}$ and $f$ a weight 4 cuspidal modular eigenform, such that the $\ell$-adic Galois representation attached to $f$ is isomorphic to $H^3(X,\mathbf{Q}_\ell)$ for all $\ell$.
The question: Is it reasonable to expect that (the motive attached to) every weight 4 normalised cuspidal eigenform with rational coefficients is associated to the cohomology of a rigid Calabi-Yau 3-fold over $\mathbf{Q}$?
Best Answer
There is a recent preprint of Paranjape and Ramakrishnan where they discuss such matters. In particular, they realize Ramanujan's delta function in the middle-dimensional cohomology of an 11-dim. Calabi-Yau! Perhaps this is not so surprising - their variety is birational to a Kuga-Sato variety.
Let me also point out something what has mystified me greatly. (This may all be wildly incorrect) Suppose $X/ \mathbf{Q}$ is a rigid Calabi-Yau threefold. As you point out, the $(3,0)$-chunk of $H^3(X)$ gives a weight four modular form $f$. Now, people have conjectured the following various items:
The intermediate Jacobian $J(X)=H^{3,0}(X) / H_3(X,\mathbf{Z})$, an elliptic curve, is defined over $\mathbf{Q}$. It thus gives rise to a weight two form $g$.
The Abel-Jacobi map from the group $Ch(X)^2_0$ of homologically trivial one-cycles on $X$ / rat. equivalence to $J(X)$ is injective and defined over $\mathbf{Q}$.
The rank of $Ch(X)^2_0 / \mathbf{Q}$ is equal to the order of vanishing of $L(s,f)$ at its central point. (Bloch)
On the other hand, if the Abel-Jacobi map were injective and defined over $\mathbf{Q}$, then $J(X)(\mathbf{Q})$ should have rank at least the rank of $Ch(X)^2_0 / \mathbf{Q}$. At the level of L-functions, this should force the L-fn attached to the weight two form of $g$ to vanish to order $\geq$ the order of vanishing of the L-fn of the weight 4 form $f$. How could two modular forms of different weights know about each other in such a way as for the orders of vanishing of their L-functions to be entwined? The only thing that I can imagine is that they satisfy a congruence. Maybe they lie in the same Hida family? Wild speculation!