I want to understand the differences of LoROM and HiROM cartridges in detail.
On a theoretical level, it is clear to me that any cartridge could do whatever address mapping magic it wants to, e.g. banking to extend the physical address space. For that, there is no need for the SNES itself to know about such mapping (unless it uses a cache, which AFAIK it doesn't). The only limitation is that the cartridge cannot affect any mapping of bus peripherals that are already connected inside the SNES, unless special mechanisms are in place to allow that, such as the cartridge being able to suppress the chip-select for an SNES-internal chip. Again, I don't know of any such mechanism.
I found LoROM and HiROM memory maps here: https://en.wikibooks.org/wiki/Super_NES_Programming/SNES_memory_map#LoROM
Comparing those, the only differences I see are in addresses served by the cartridge, which does not require the SNES itself to know whether a LoROM or HiROM map is used. In particular, it does not seem that the LoROM vs. HiROM distinction causes any address block to be switched between being "in the SNES" vs. being "in the cartridge".
So far, I would have expected that LoROM and HiROM are concepts being used in the design of the cartridges, transparent to the SNES itself.
However, the same article linked above later describes the "internal" SNES header (note: NOT the SMC header, which they suggest to treat as unrealiable information). As far as I understood, the SMC header is external information provided by ROM reader devices, while the internal SNES header is actually stored in the ROM. They do mention that the internal SNES header may appear at different places in the memory map depending on the address mapping, which is obvious. They suggest to "find" the header by probing (for emulation purposes), but it is unclear to me what extent of probing is done by a real SNES.
The internal header contains a field that distinguishes LoROM vs. HiROM. That page seems emulation-oriented and is a little blurry with respect to emulation vs. real hardware, but the key point is that the LoROM vs. HiROM distinction is stored in the actual ROM chip on actual cartridges, to be readable by the SNES.
Given that it seems like LoROM and HiROM could be distinguished in the cartridge alone, transparent to the SNES, what effect does this flag in the header have on the SNES?
The SNES itself does not make use of the "internal" SNES header at all (except for the interrupt vectors, of course). It is purely for informational purposes. Technically, there is no need for the info in that header to be reliable, but in the handful of cases I have personally investigated it appears to be accurate.
So yes, the mapping is all done by the cartridge.