Since we are talking about motivation, assume that $X$ is smooth over $Y$. Also, by slight abuse of notation let $\Delta$ denote the image of the diagonal map in $X\times_Y X$.

Now in general one has a short exact sequence connecting the tangent bundle of a
manifold, the tangent bundle of a submanifold and the normal bundle of that
submanifold. The geometric explanation to why this definition is the right one is that the normal bundle of the diagonal is isomorphic to its tangent bundle. The normal bundle can be defined without the tangent bundle, so the tangent bundle may be defined as the normal bundle for this particular embedding.

In algebraic geometry we usually prefer the dual version involving the cotangent
bundles (sheaves), so we can do it that way as well. Actually, for this we don't even need that $X$ is smooth over $Y$, so it explains what you want in the general case. (However, one might argue that the motivation comes from the "classical" case of manifolds). Write down the relevant short
exact sequence for $\Delta\subset X\times_Y X$:

$$ 0 \to \mathcal I/\mathcal I^2 \to \Omega_{X\times_Y X/Y}\otimes \mathcal
O_{\Delta} \to \Omega_{\Delta} \to 0.  $$


Observe that $\Omega_{X\times_Y X/Y}\simeq p_1^*\Omega_{X/Y}\oplus p_2^*\Omega_{X/Y}$
and hence $\Omega_{X\times_Y X/Y}\otimes \mathcal O_{\Delta} \simeq
\Omega_{\Delta}\oplus \Omega_{\Delta}$. In fact, the natural morphism in the above
short exact sequence is the projection to one of the direct summands. It follows that
$\mathcal I/\mathcal I^2\simeq \Omega_{\Delta}$. SInce the diagonal morphism is an
isomorphism between $X$ and $\Delta$, it is clear that whatever way we define
$\Omega_X$, it has to be isomorphic to the pull-back of $\mathcal I/\mathcal I^2$.