What is an example of a scheme $X$ for which there does not exist any morphism $f : Y \to X$ which is faithfully flat and locally of finite presentation and where $\operatorname{Pic} Y = 0$?
Remark: If I remove the "finitely presented" condition, then we can take $Y = \coprod_{x \in X} \operatorname{Spec} \mathcal{O}_{X,x}$.
Edit. I decided to add a little more explanation to make the result "sharp". For a smooth, projective scheme $X$ over an algebraically closed field $k$, for every finitely presented, flat, dominant morphism $p:Y\to X$, the kernel of the pullback homomorphism, $$ p^*:\text{Pic}(X)\to \text{Pic}(Y), $$ is a finitely generated subgroup. This result is "sharp" in the sense that every finitely generated subgroup is of the form $\text{Ker}(p^*)$ for a smooth, surjective morphism $p$. One step in finite generatedness of $\text{Ker}(p^*)$ is finite generatedness of the quotient group, $\text{Pic}(X)/\text{Pic}^0(X)$. The result, the "Theorem of the Base" of Lang and Néron, is quite involved, whereas the consequence to the OP's question follows already from finite generatedness of the intersection $\text{Pic}^0(X)[p]$ of $\text{Ker}(p^*)$ and the subgroup $\text{Pic}^0(X)$. This is much easier.
To prove that $\text{Pic}^0(X)[p]$ is finitely generated, the main reduction is the proposition that every fppf cover of a quasi-compact scheme is "refined" by an fppf cover that is quasi-finite. This is a Bertini hyperplane theorem that holds for arbitrary Noetherian schemes, not just smooth, projective $k$-schemes. Thus, let $X$ be a Noetherian scheme, and let $p:Y\to X$ be a flat, finitely presented morphism.
Proposition 1. There exists a morphism $i:Z\to Y$ that is a disjoint union of locally closed immersions such that the composition $q=p\circ i$ is flat, quasi-finite, and has image equal to the image of $p$.
Proof. Since $p$ is flat and finitely presented, the image of $p$ is open. Up to replacing $X$ by the open image of $p$, assume that $p$ is surjective. Edit. Also, since $X$ is quasi-compact, it has a finite cover by open affines, $X_\beta$. For every collection $i_\beta:Z_\beta \to Y\times_X X_\beta$ as in the proposition, for the disjoint union $Z=\sqcup_\beta Z_\beta$ and for the unique morphism $i:Z\to Y$ whose restriction to each $Z_\beta$ equals $i_\beta$, also $i:Z\to Y$ satisfies the conditions in the proposition. Thus, it suffices to prove the result when $X$ is affine.
The argument is essentially a Bertini hyperplane argument. First of all, up to replacing $Y$ by a disjoint union of open affine subschemes, assume that $Y$ is affine. Denote by $Y_{\text{equi}}\subset Y$ the maximal open subset on which $p$ is (locally) equidimensional, i.e., at every point of every fiber of $p$, the irreducible components of the fiber containing the point all have equal dimensions. Edit. This open subset contains the open complement $U$ of the closed union $C$ of the finitely many intersections $Y_i\cap Y_j$ of irreducible components $Y_i$ and $Y_j$ of $Y$ such that the (constant) relative dimensions of $Y_i$ and $Y_j$ over $X$ are not equal. For every point $x\in X$, there exists an irreducible component $Y_i$ such that the fiber $(Y_i)_x$ has maximal dimension. Since $p$ is flat, $U\cap (Y_i)_x$ is dense in $(Y_i)_x$: if $(Y_j)_x\cap (Y_i)_x$ is nonempty, and if $\text{dim}(Y_j)_x\neq\text{dim}(Y_i)_x$, then $(Y_j)_x$ has smaller relative dimension, so $(Y_i)_x\cap (Y_j)_x$ is nowhere dense in $(Y_i)_x$.
Thus, the restriction of $p$ to $Y_{\text{equi}}$ is still surjective. Thus, without loss of generality, assume that $p$ is (locally) equidimensional. Then on every connected component $Y_\alpha$ of $Y$, the fiber dimension of $p$ is constant. We construct $Z$ as a disjoint union of schemes $Z_\alpha$ where $i_\alpha:Z_\alpha\to Y_\alpha$ is a disjoint union of locally closed immersions such that $p_\alpha\circ i_\alpha$ is a quasi-finite, flat morphism with image equal to the image of $p_\alpha$.
Thus, without loss of generality, assume that $Y$ is equidimensional of dimension $d$. The proof of the existence of $i:Z\to Y$ as above is by induction on $d$. If $d$ equals $0$, then define $Z$ to equal $Y$. Thus, by way of induction, assume that $d>0$ and assume the result is proved for $d-1$.
Let $e:Y\hookrightarrow \mathbb{A}^n_X$ be a closed immersion of $X$-schemes. For every point in the image of $p$, $x:\text{Spec}\ k \to X,$ the fiber $Y_x=p^{-1}(x)$ has only finitely many associated points.
Edit. Since $Y_x$ has dimension $d>0$, there exist points $y\in Y$ that specialize to none of these finitely many associated points. By prime avoidance, there exists an element $t_x\in k[\mathbb{A}^n_k]=k[t_1,\dots,t_n]$ that is in the prime ideal of $y$ yet in none of the associated primes.
For every linear polynomial $t_x$ in the coordinate ring $k[\mathbb{A}^n_k]=k[t_1,\dots,t_n]$ that vanishes at none of these finitely many associated points, Multiplication by $t_x$ is injective on $\mathcal{O}_{Y_x}$. Let $t\in\mathcal{O}_{X,x}[t_1,\dots,t_n]$ be an element mapping to $t_x$. Denote by $H \subseteq \mathbb{A}^{n}_{\mathcal{O}_{X,x}}$ the zero scheme of $t$.
By the local flatness criterion, the intersection $H\cap Y$ is flat over $\mathcal{O}_{X,x}$, cf. Theorem 22.5, p. 176, "Commutative ring theory", H. Matsumura, Cambridge studies in mathematics, vol 8. By the usual arguments, there exists an open affine neighborhood $W \subset X$ of $x$ such that $t$ lifts to a section $t_{W}$ of the structure sheaf of $\mathbb{A}^n_W$. Define $H_{W} \subset \mathbb{A}^{n}_{W}$ to be the zero scheme of $t_{W}$. By openness of the flat locus, etc., there exists an open subscheme $U$ of $Y$ containing $H\cap Y_x$ such that $H_{W}\cap U$ is flat over $X$. Choosing $t_x$ so that $H\cap Y_x$ is nonempty, $U\cap H_{W} \to X$ has image containing $p$, and it is equidimensional of dimension $d-1$ (this last by Krull's Hauptidealsatz).
Thus, by the induction hypothesis applied to $U\cap H_{W} \to X$, there exists a locally closed immersion $i_x:Z_x\to U\cap H_{W}$ such that $p\circ i_x$ is quasi-finite and flat with image $V_x$ containing $x$. As we vary $x$, the open images $V_x$ cover $X$. Since we assumed that $X$ is quasi-compact, finitely many of these opens suffice to cover $X$. Define $i:Z\to Y$ to be the disjoint union of these finitely many locally closed immersions $i_x$. Since the composition $p\circ i$ is quasi-finite and flat on each of the finitely many connected components, it is quasi-finite and flat. By construction, the image equals the image of $p$. Thus, the lemma is proved by induction on $d$. QED
Lemma 2. Let $q:Z\to X$ be a quasi-finite, flat morphism that is dominant. There exists a dense open subscheme $j:V\hookrightarrow X$ and a positive integer $n$ such that the kernel of the pullback homomorphism $q^*:\text{Pic}(X)\to \text{Pic}(Z)$ is contained in the kernel of the homomorphism $\text{Pic}(X)\xrightarrow{j^*}\text{Pic}(V)\xrightarrow{n\cdot -} \text{Pic}(V).$
Proof. For every finitely presented, quasi-finite morphism $q:Z\to X$, there exists a dense open subset $V\subset X$ such that $q^{-1}(V)\to V$ is finite. Since $q$ is dominant and flat, the morphism $q^{-1}(V)\to V$ is surjective, finite, and flat.
There exists a norm map, $$\text{Nm}_q : \text{Pic}(q^{-1}(V))\to \text{Pic}(V), \ \ \mathcal{L} \mapsto \text{det}(q_*\mathcal{L})\otimes \text{det}(q_*\mathcal{O})^{\vee}.$$ This is described in detail in Mumford's Lectures on curves on an algebraic surface. For every connected component $V_\alpha$ of $V$, the morphism $q$ has constant finite degree $m_{\alpha}$ over that component. For every invertible sheaf $\mathcal{M}_\alpha$ on $V_\alpha$, $\text{Nm}_q(q^*\mathcal{M}_\alpha)$ equals $\mathcal{M}_\alpha^{\otimes m_{\alpha}}$. Thus, defining $n$ to equal the least common multiple of these integers $m_{\alpha}$, for every invertible sheaf $\mathcal{M}$ on $V$, if $q^*\mathcal{M}$ is trivial, then also $\mathcal{M}^{\otimes n}$ is trivial. Thus, for an invertible sheaf $\mathcal{M}$ on $X$, if $q^*\mathcal{M}$ is trivial, then also $j^*\mathcal{M}^{\otimes n}$ is trivial. QED
Now let $k$ be an algebraically closed field. Let $X$ be a smooth, projective $k$-scheme. The relative Picard functor $\text{Pic}_{X/k}$ is represented by a $k$-scheme whose connected components are projective. In particular, the identity component $\text{Pic}^0_{X/k}$ is an Abelian variety over $k$. The group of $k$-points, $\text{Pic}^0(X)$, is divisible and infinitely generated with finite $n$-torsion for every integer $n$.
For every finitely presented, flat, dominant morphism $p:Y\to X$, denote by $\text{Pic}^0(X)[p]$ the kernel of the homomorphism, $$ \text{Pic}^0(X) \hookrightarrow \text{Pic}(X) \xrightarrow{p^*} \text{Pic}(Y). $$
Proposition 3. For every finitely presented, flat, dominant morphism $p:Y\to X$, the kernel $\text{Pic}^0(X)[p]$ is a finitely generated Abelian subgroup of $\text{Pic}^0(X)$.
Proof. By Proposition 1, there exists a morphism $i:Z\to Y$ such that $q=p\circ i:Z\to X$ is flat, quasi-finite, and dominant. The subgroup $\text{Pic}^0(X)[p]$ is contained in the subgroup $\text{Pic}^0_{X/k}[q]$. Thus, to prove that $\text{Pic}^0(X)[p]$ is finitely generated, it suffices to prove that $\text{Pic}^0(X)[q]$ is finitely generated.
By Lemma 2, there exists a dense open subset $j:V\hookrightarrow X$ and an integer $n$ such that the kernel of $q^*$ is contained in the kernel of $$\text{Pic}^0(X)\xrightarrow{j^*}\text{Pic}(V)\xrightarrow{n\cdot -}\text{Pic}(V).$$ The kernel $K$ of the restriction map $j^*:\text{Pic}(X)\to \text{Pic}(V)$ is generated by the finitely many irreducible components of $X\setminus V$ that have codimension $1$ in $X$. The intersection $K^0$ of $K$ with the subgroup $\text{Pic}^0(X)$ is a subgroup of the finitely generated group $K$, hence also $K^0$ is finitely generated. The quotient of $\text{Pic}^0(X)$ by $K^0$ still has finite $n$-torsion: since $\text{Pic}^0(X)$ is divisible, the inverse image $I$ in $\text{Pic}^0(X)$ of this $n$-torsion is an extension of $K^0$ by the $n$-torsion of $\text{Pic}^0(X)$. As both of these groups are finitely generated, also $I$ is finitely generated. Since $\text{Pic}^0(X)[q]$ is contained in the finitely generated group $I$, also $\text{Pic}^0(X)[q]$ is finitely generated. QED
Corollary 4. For $p:Y\to X$ as in the previous proposition, the kernel of $p^*:\text{Pic}(X)\to \text{Pic}(Y)$ is a finitely generated Abelian group.
Proof. The quotient of $\text{Ker}(p^*)$ by $\text{Pic}^0(X)[p]$ is a subgroup of the Néron-Severi group $\text{Pic}(X)/\text{Pic}^0(X)$. By the "Theorem of the Base" of Lang and Néron, the Néron-Severi group is finitely generated. Thus, the subgroup is also finitely generated. By the proposition, $\text{Pic}^0(X)[p]$ is finitely generated. Thus, the extension group $\text{Ker}(p^*)$ is also finitely generated. QED
As mentioned at the top, the result is sharp in the following sense.
Proposition 5. For every integral, normal, locally factorial Noetherian scheme $X$, for every finitely generated Abelian subgroup $K$ of $\text{Pic}(X)$, there exists a smooth, surjective morphism $p:Y\to X$ such that $p^*:\text{Pic}(X)\to \text{Pic}(Y)$ is surjective with kernel equal to $K$.
Proof. This is proved by induction on the minimum number of generators of $K$. If $K$ is zero, then define $p$ to be the identity morphism. Thus, by way of induction, assume that $K$ is nonzero, and assume that the result is proved whenever the number of generators is strictly smaller than the minimal number of generators of $K$.
Let $L$ be an invertible sheaf on $X$ whose class gives one of the generators in a minimal set of generators of $K$. Denote by $\overline{p}:\overline{Y}\to X$ the projective space bundle that admits a universal invertible quotient, $\overline{p}^*(\mathcal{O}_X \oplus L) \to \mathcal{O}(1)$. The two projections, $$a_1:\mathcal{O}_X\oplus L \to \mathcal{O}_X, \ \ a_2:\mathcal{O}_X\oplus L \to L,$$ are invertible quotients. These define sections of $\overline{p}$, $$s_1,s_2:X\to \overline{Y},$$ whose images, $D_1$, resp. $D_2$, are disjoint, integral Cartier divisors. Define $Y$ to be the open complement of $D_1\cup D_2$.
The pullback homomorphism and the class of $\mathcal{O}(1)$ define a splitting of Abelian groups, $$\text{Pic}(X)\oplus \mathbb{Z}[\mathcal{O}(1)] \xrightarrow{\cong} \text{Pic}(\overline{Y}).$$ Since $Y$ is the open complement of a union of two disjoint Cartier divisors, the usual open-closed exact sequence gives, $$\mathbb{Z}[\mathcal{O}(D_1)] \oplus \mathbb{Z}[\mathcal{O}(D_2)] \to \text{Pic}(\overline{Y}) \to \text{Pic}(Y) \to 0. $$ Combined with the splitting, $\text{Pic}(Y)$ is isomorphic to the quotient of $\text{Pic}(X)$ by the class of $\mathcal{O}(D_1-D_2)$, i.e., the class of $L$. Thus, $p^*$ is surjective and the kernel equals $\mathbb{Z}[L]$. By the induction hypothesis, there exists a further smooth, surjective homomorphism to $Y$ such that the pullback map is surjective with kernel equal to $K/\mathbb{Z}[L]$. Thus, the result is proved by induction on the minimal number of generators of $K$. QED
