Hartshorne remarks that is is something of a mystery as to why the algebraic condition of flatness on the structure sheaves gives a good definition of a family (see below). Are there any known enlightening explanations that help serve to unravel this mystery? Below is Hartshorne's introductory motivation to flat families containing said remark:
For many reasons it is important to have a good notion of an algebraic
family of varieties or schemes. The most naive definition would be just to
take the fibres of a morphism. To get a good notion, however, we should
require that certain numerical invariants remain constant in a family, such
as the dimension of the fibres. It turns out that if we are dealing with non-
singular (or even normal) varieties over a field, then the naive definition is
already a good one. Evidence for this is the theorem (9.13) that in such a
family, the arithmetic genus is constant.On the other hand, if we deal with nonnormal varieties, or more general
schemes, the naive definition will not do. So we consider a flat family of
schemes, which means the fibres of a flat morphism, and this is a very good
notion. Why the algebraic condition of flatness on the structure sheaves
should give a good definition of a family is something of a mystery. But
at least we will justify this choice by showing that flat families have many
good properties, and by giving necessary and sufficient conditions for
flatness in some special cases. In particular, we will show that a family
of closed subschemes of projective space (over an integral scheme) is flat if
and only if the Hilbert polynomials of the fibres are the same.
— Hartshorne, Algebraic Geometry, 1977, III.9.5, p. 256
Best Answer
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/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)$ (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).