Non-coordinate basis in GR

This transformation is done locally, i.e. so that $g_{\alpha\beta} = \eta_{\alpha\beta}$ in a neighbourhood rather than a single point. I believe that due to topological effects, we cannot in general do it globally. However, even though the new basis is orthonormal, we have to remember that it is (in general) non-holonomic, i.e that there is no set of functions $y^\alpha$ satisfying $$ e^\mu_\alpha = \frac{\partial x^\mu}{\partial y^\alpha}. $$ This means that the curvature does not (in general) vanish. Indeed, it holds that $$T_{\alpha\beta\ldots\gamma} = e^\mu_\alpha e^\nu_\beta e^\sigma_\gamma T_{\mu\nu\ldots\sigma},$$ for all tensors $T_{\mu\nu\ldots\sigma}$, as is obvious by the linearity of $e^\mu_\alpha$, and thus in particular for $R_{\mu\nu\sigma\tau}$. You may be confused if you have learned that the connection coefficients are given by $$ \Gamma_{\mu\nu\sigma} = \frac{1}{2}\left(g_{\mu\nu,\sigma} + g_{\mu\sigma,\nu} - g_{\nu\sigma,\mu}\right), $$ but this is only valid in a holonomic frame. More generally $$ \Gamma_{\alpha\beta\gamma} = \frac{1}{2}\left(g_{\alpha\beta|\gamma} + g_{\alpha\gamma|\beta} - g_{\beta\gamma|\alpha} + C_{\gamma\alpha\beta} + C_{\beta\alpha\gamma} - C_{\alpha\beta\gamma}\right), $$ where $f_{|\alpha} \equiv e_\alpha(f)$, $[e_\alpha,e_\beta] = C^\gamma{}_{\alpha\beta}e_{\gamma}$, and $C_{\gamma\alpha\beta} \equiv g_{\gamma\delta}C^\delta{}_{\alpha\beta}$. In particular, in an orthonormal frame: $$ \Gamma_{\alpha\beta\gamma} = \frac{1}{2}\left(C_{\gamma\alpha\beta} + C_{\beta\alpha\gamma} - C_{\alpha\beta\gamma}\right). $$ Unless I misunderstand you, the rotations you refer to are local Lorentz transformations, and we are interested in them because each orthonormal frame corresponds to the instantaneous rest frame of an observer (with velocity field equal to $e_0$), and thus these rotations transform between different rest frames.