What does equivalence of categories really tell us?
Solution 1:
I think this is a great question, and I don't pretend to have a full answer for it, but here are some of my thoughts:
Exactly how happy should I be when I prove that two seemingly different categories are equivalent?
This depends on exactly how "seemingly different" they are - or why you're trying to prove this in the first place. I'll illustrate this with respect to your examples:
the equivalence between classical varieties and finite-type integral schemes
You shouldn't be surprised that these the same thing: the latter was practically designed to be a new way of encoding the former! But from the perspective of a researcher who only knows the former, and is trying to invent a new formalism to encode it, you should certainly be happy about it, because it tells you that the formalism is in some sense "correct" - it doesn't gain or lose too much information categorically.
commutative $C^∗$-algebras and the opposite category of compact Hausdorff topological spaces ... To which $C^∗$-algebras do manifolds correspond?
This is really a question about manifolds inside the category of compact Hausdorff topological spaces, and not about the equivalence of categories. Is the property of being a manifold a categorical property? That is, suppose I hand you the abstract category of compact Hausdorff topological spaces, and I don't allow you to actually use any topological properties (e.g. I just give you a bunch of vertices and arrows with composition data, and I don't tell you which spaces the vertices represent or which morphisms the arrows represent). Can you pick out the manifolds from this?
A stupid example of my own: let C be the category of finite-dimensional vector spaces. It's easy to pick out the vector spaces of dimension 3, as follows. The spaces of dimension 0 will be initial objects, so you know which those are; then, inductively, the spaces of dimension n+1 will be the objects x that aren't spaces of dimension < n+1, and such that the only morphisms into x are those coming from spaces of dimension < n+1 or those coming from isomorphic objects.
Now, maybe you can pick out the manifolds categorically. I don't actually know. But my suspicion is, if you can, it's not in any nice way, and so when you translate it into the language of $C^*$-algebras, you probably don't get anything usable.
Here's an example closer to my own heart: Lazard's famous equivalence of categories between certain nice $\mathbb{Z}_p$-Lie algebras and certain nice pro-$p$ groups. The devil's in the detail: I've hidden, behind both instances of the word "certain", a bunch of technical conditions that are more or less designed to make this true. But it's still remarkable that it's true at all.
Moreover, the real-life usefulness of this fact is guaranteed by the fact that all closed subgroups of $GL_n(\mathbb{Z}_p)$ do actually contain one of these certain nice pro-$p$ groups as a closed subgroup of finite index. In other words, no matter how horrible your closed subgroup of $GL_n$ is, viewed from the right angle it's more or less just (a finite amount of complexity away from) a Lie algebra. Whereas you previously only had the tools of group theory to apply to this object, you now also have the tools of Lie theory. That's definitely a win: even just knowing that finite-rank $\mathbb{Z}_p$-modules have a basis tells you a lot about the group and its completed group algebra, e.g. a PBW-type theorem.
Solution 2:
Coming back to this question a couple of months on: how could I have neglected to mention Morita theory in my answer above? I'm posting again to include it, rather than editing my answer above, because it's of a rather different nature than the examples either of us has mentioned so far, but I think it's a very worthwhile example to see.
Let $R$ and $S$ be two rings, and consider the abelian categories of left modules $R\text{-Mod}$ and $S\text{-Mod}$. Suppose we have an equivalence of abstract categories $F: R\text{-Mod}\to S\text{-Mod}$. (Note: $F(R)$ isn't necessarily $S$, or even a free $S$-module. When I first learnt that fact, I thought "well, what good is the existence of such an $F$, if it doesn't even preserve such a basic property?".)
Firstly: if an $F$ like this exists, it's automatically an additive functor. (That is, $\mathrm{Hom}(A,B) \cong \mathrm{Hom}(FA,FB)$ is not just a bijection, it's actually a group isomorphism.)
Secondly: if the left-module categories are equivalent, then the right-module categories are equivalent too, and there are alternative characterisations of this property that make it easy(ish) to check.
Thirdly: such an $F$ actually preserves many properties of individual modules, as well as of the rings $R$ and $S$. Wikipedia has two nice lists of them, which I quote/paraphrase here:
the R module M has any of the following properties if and only if the S module F(M) does: injective, projective, flat, faithful, simple, semisimple, finitely generated, finitely presented, Artinian, and Noetherian
[but not: free, cyclic]
and
[R has any of the following properties if and only if S does:] simple, semisimple, von Neumann regular, right (or left) Noetherian, right (or left) Artinian, right (or left) self-injective, quasi-Frobenius, prime, right (or left) primitive, semiprime, semiprimitive, right (or left) (semi-)hereditary, right (or left) nonsingular, right (or left) coherent, semiprimary, right (or left) perfect, semiperfect, semilocal
[but not: commutative, local, reduced, domain, right (or left) Goldie, Frobenius, invariant basis number, and Dedekind finite]
Much like my example of "3-dimensionality" being a categorical property of vector spaces in my older answer, all of these properties are preserved precisely because they are categorical.