The introduction of the idea made up some garbage about scanning brains and translating into "universal concepts" such as (cough, cough) a strict gender binary, so in my head, there's literally just an office where they make up the rules. I like that explanation, because it also explains why characters almost always adhere to their racist stereotypes, like Klingons probably just sound constantly aggressive, because the translator always picks the most extreme word for everything, and Vulcans always get the most technical terms.

They've shown that it can bootstrap itself, listening to speech to generate a new "translation matrix" for an unfamiliar language. Presumably this can somehow magically identify utterances that are idiomatic or ritualistic enough not to translate cleanly?

For episodes with more language stuff: A big plot arc in Discovery Season 4 involves a species with a worldview and communication method so alien that an entire episode is spent doing archaeology to scrape together some point of intersection with humanoid communication.

(oops, accidentally commented with the show account. this is @gaw.)