After lots of reading and thinking, I think I figured it out. For posterity, the following is my reasoning. I am not claiming this to be any kind of formal proof or derivation, but rather a way of looking at the problem that gives me physical intuition for why the distance formula and metric tensor give what I intuitively think of as "length" in (Euclidean) space.
First, a physical intuition for why a dot product gives a projection is very well explained by 3Blue1Brown here. The idea of "projecting a vector onto itself" feels to me like a good intuitive procedure to measure its length, and this video shows why that procedure is produced mathematically by summing the products of two vector's components (by thinking of $\vec{V}^{T}$ as a linear transformation). Projecting a vector onto itself can be thought of as measuring how far it reaches along the 1D number line parallel to it (as depicted in the video), so I am ok accepting that as one intuitive sense of length.
Next we can imagine a non-orthogonal coordinate system for flat 2D Euclidean space. Lets say the normalized basis vectors are $\hat{e_{1}}$ and $\hat{e_{2}}$, which are not orthogonal to each other. We can construct a 2 x 2 matrix that is a linear transformation from the 2D cartesian plane to this new basis. Call this matrix A, and its columns are the transpose (i.e. column vectors) of the {e} basis vectors.
So let's say we now have a vector $\vec{V}$ written in the {e} basis (i.e. it has scalar contravariant components $V_{e_{1}}$ and $V_{e_{2}}$ such that $\vec{V} = V_{e_{1}}\hat{e_{1}} + V_{e_{2}}\hat{e_{2}}$), and we want to measure its length. We can first do a change of basis back to our orthognormal cartesian basis where we know how to measure length (using the "sum of components squared" formula intuitively given by the above video). The vector's length is the same before and after any basis change (since the length of a vector is independent of the basis we choose to describe it). We will write the vector $\vec{V}$ written in the cartesian basis as $\vec{V'}$. We can find $\vec{V'} = A\vec{V}$, again remembering its length is unchanged by change of basis.
So now we just take the dot product of $\vec{V'}$ with itself:
$\vec{V'} \cdot \vec{V'} = (\vec{V'})^{T}\vec{V'} = (A\vec{V})^{T}A\vec{V} = \vec{V}^{T}A^{T}A\vec{V}$
That middle part, $A^{T}A$ works out to be the metric tensor, if you go through the algebra. We also see that the metric tensor must be a symmetric matrix, as it is formed by the product of a square matrix with its transpose. This then recovers the distance formula for a vector in Euclidean space using non-orthogonal coordinates, in a (for me) somewhat physically intuitive way.
Again, I do not present this as a proof of any kind, but just a way of thinking about it that gave me a bit better physical intuition for "Why this formula?". I welcome any corrections or additions.