I've recently learned about ultraproducts, but the source I learned from almost immediately after the definitions restricted to talking about countably incomplete ultrafilters. I know that the existence of countably complete ultrafilters is a large cardinal issue, but aside from this, is there a reason to focus on the countably incomplete case? Do ultraproducts (or even just ultrapowers) with a countably complete ultrafilter behave very differently from the countably incomplete ones?
It would be great to have an example of the kinds of differences that happen, ideally in a fairly down-to-earth setting (maybe groups, or fields, or graphs?). The few places I've seen talk about countably complete ultrafilters all seem to be taking ultrapowers of models of ZFC, which is a bit much for me to grasp at this point. Since I just want to see the differences, it's fine with me if some set-theoretic hypotheses are needed to make the examples work.
Taking an ultraproduct of well orders by a sigma complete ultrafilter results in a well order, whereas an incomplete filter results in a linear order which is not well founded. The proof is the same as the ZFC case.
The reason we don't really talk about this is model theory is that the consistency difference of a measurable cardinal assumption is quite huge. And one wants to develop model theory in a universe of set theory, without further assumptions.