How to intuitively understand parallel transport
Solution 1:
I was also having trouble with this for a long time. The explanation which finally worked for me was the following:
For the purposes of parallel transport along a particular circle of latitude, the sphere can be replaced by the cone which is tangent to the sphere along that circle, since a “flatlander” living on the surface and travelling along the circle would experience the same “twisting of the tangent plane in the ambient space” regardless of whether the surface is a sphere or a cone.
And for the cone, there's an easy way of seeing that there is indeed a rotation of the transported vector with respect to the tangent vector of the curve: just cut the cone open and lay it flat on the table, so that parallel transport becomes simply ordinary parallel transport on the plane.
A picture says more than a thousand words, and I found a good one here: A simple discussion of the Berry Phase (N. P. Ong, Physics, Princeton Univ.).
Solution 2:
The red and blue vector fields in your picture are not parallel along the pink curve. One way to see this is to note that you can compute the covariant derivative of a vector field along a curve in the sphere by computing its ordinary derivative in $\mathbb R^3$, and then orthogonally projecting that onto the tangent plane. At any point on the pink circle, the ordinary derivative of the blue vector field points toward the center of the pink circle. Since that is not orthogonal to the tangent plane, its orthogonal projection onto the tangent plane is nonzero.
Solution 3:
One way to see that the blue vector field is not parallel is by noting that it is the vector field corresponding to velocity. If it were parallel, the red circle would be a geodesic. But geodesics in the sphere are the great circles.
(Actually, this is the same thing that Jack Lee is saying, but phrased differently.)