Since we are talking about motivation let's 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$.
For a submanifold of a manifold one has a short exact sequence connecting the tangent bundle of the ambient manifold restricted to the submanifold, the tangent bundle of the submanifold and the normal bundle of that submanifold in the ambient manifold. The geometric explanation to why the definition of the cotangent sheaf via the conormal bundle of the diagonal in the self-product is the right one is that the normal bundle of the diagonal is isomorphic to its tangent bundle and the (co)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 (and of course, in general cotangent sheaves), so let's work with that. Write down the relevant short exact sequence for $\Delta\subset X\times_Y X$:
$$ 0 \to \mathscr I/\mathscr I^2 \to \Omega_{X\times_Y X/Y}\otimes \mathscr O_{\Delta} \to \Omega_{\Delta/Y} \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 \mathscr O_{\Delta} \simeq \Omega_{\Delta/Y}\oplus \Omega_{\Delta/Y}$. In fact, the natural morphism $\Omega_{X\times_Y X/Y}\otimes \mathscr O_{\Delta} \to \Omega_{\Delta/Y}$ in the above short exact sequence is themay be identified with either projection to one of the direct summands since restricting either projections to the diagonal induces an isomorphism, or perhaps even better to say that the diagonal is a section of either projection. It follows that $\mathscr I/\mathscr I^2\simeq \Omega_{\Delta/Y}$. SInceSince the diagonal morphism is an isomorphism between $X$ and $\Delta$, it is clear that whatever way we define $\Omega_{X/Y}$, it has to be isomorphic to the pull-back of $\mathscr I/\mathscr I^2$.