Here is an elementary and intuitive explanation. The fiber of a map is locally a tensor product: if $X=\text{Spec} S$ and $Y=\text{Spec} R$ and the ring map is $R \to S$, then the fiber at a point $p \in Y$ is the Spec of $R/p R_p/pR_p \otimes_R S$.
Flatness is exactly the condition that makes tensor products behave like a dream (almost by definition), it preserves a lot of useful structures. Many algebraic results with geometric consequences go like this: let $(P)$ be a reasonable property and $f: R\to S$ a flat local homomorphism. Then $S$ satisfies $(P)$ if and only if $R$ and the fiber at the closed point satisfy $(P)$.(P)$ (these are called Grothendieck localization problem).
I am not a historian, but I suspect that was how flatness arised: people wanted certain nice things to be true, and were naturally lead to flatness (see BCnrd's comment below for the precise history).
