First of all, please read through the mods' descriptions—a lot of the information you seem to be looking for can be found there. For example, mod authors might tell you they used a specific lighting mod while creating and tweaking their creation, and recommend using that same mod. But it will also give you an idea of what a mod changes, and how, and if there are incompatibilities. I usually quickly go through the mod's discussion page as well, as the most obvious mistakes, incompatibilities, and unannounced changes a mod introduces are usually repeated. Knowing what you're getting yourself into is even more important when it comes to weather mods, as they rely heavily on lighting (of exterior and interior alike!).
ELFX changes Skyrim's lighting "by adding lights to all light sources and removing any light without a source, as well as the addition [of] many more effects like smoke, volum[etr]ic lights, drip[p]les, etc." The author of ELFX basically went through every location in Skyrim and tweaked the presence, position, effects, and/or brightness of every light source, the settings of the weather system and every environment to accommodate those changes, and added additional effects if they so desired. This means it is really targeting vanilla Skyrim, and additional locales will very likely feel out of place, or be entirely too dark or bright.
There is however a long list of ELFX compatibility mods (see the Requirements dropdown menu at the top of the description).
Realistic Lighting Overhaul is less intrusive, and focuses more on improving vanilla lighting. Unlike ELFX, "changes made to this mod only utilize the game engine and changes are only made via the Creation Kit", "this mod does not aim to make the game fantasy like, or add lights for the purpose of it looking beautiful on screenshots", and "this mod adds a vast amount of ambient lighting, more shadow casting lights and bounced lighting. The lighting in RLO is very subtle and very balanced". It will lend itself better for a more seamless transition into new areas.
An even less intrusive lighting mod is Skyrim is Luminous (which is only available for the Special Edition). It "preserves the existing vanilla bulbs unlike ELFX or Relighting Skyrim which delete them". More importantly, "Skyrim is Luminous edits existing vanilla light records and templates, so it will be compatible with most mod added locations unless the author intended for their locations to have unique lighting".
In the latter case there is very little you can do anyway, apart from using the mods the author recommends, which might ruin the lighting of other locations. The best way to deal with that is to, again, thoroughly read the description and try to find out how the mod author dealt with lighting in the added environments.
The same mod author of Skyrim is Luminous, DrJacopo, also created the Luminosity Lighting Overhaul (also only available for SSE), which makes even fewer changes (Skyrim is Luminous incorporates this mod).
I mention it not only because its compatibility (in the sense of consistency with other mods' lighting) will be even higher, but also because it falls under the Cathedral Concept umbrella, which I happen to really like and certainly recommend.
That all being said, lighting overhauls do dramatically improve the appearance of Skyrim, and I find them quite indispensable. Vanilla Skyrim has a lot of frankly ridiculous light placements and properties, some of which cast shadows at strange angles, whereas others don't cast shadows at all (which is something you will notice once you start paying attention to it). Moreover, I have come across scenes lit by ELFX or RLO that had much more atmosphere than the vanilla lighting could ever conjure, making the game even more enjoyable.
In short: using lighting overhauls is a great idea, if you're willing to go through all the documentation of every mod you intend to use.