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 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 (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 in the above short exact sequence is the projection to one of the direct summands. It follows that $\mathscr I/\mathscr I^2\simeq \Omega_{\Delta/Y}$. SInce 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$.