Is the ratings system really that useful even at lower levels? Is there not a lot of non-transitivity, where three players would consistently beat the next like A>B>C>A?

Yes, I agree with dmurray that it shouldn't happen consistently. I haven't seen it, and I've found the ratings system to work really well in practice. I'm roughly an intermediate level player, so can't speak to lower levels in particular, but I've never heard anybody complain about not being able to find competitive games against others with comparable ratings.

The only times that happens as far as I know are when the rating really doesn't reflect the current skill, which can happen over the board with juniors that are improving so quickly that their rating hasn't caught up yet, or people who haven't yet played enough games for the rating to be accurate (e.g., a brand new account on a site that starts with a rating of 1200, which is solved by not playing people who haven't played enough games yet).

Not at all. I'd say it's practically impossible if you need each of those relationships to be a 70%+ favourite.