How exactly does this mod works and how does it affect tiles? Does it just change tiles into ones that are edited to looks snowy based off of the Aquilo tiles?

And does that mean that with mods that add other tiles and biomes those mods will have their tiles overwritten? Or will they be unaffected or maybe even fail all together?

The mod does not add or edit tiles. It temporarily replaces them with Aquilo tiles and restores the originals.

There is a mapping table for some vanilla tiles. If a tile is not present in the mapping, the mod falls back to a generic snowy Aquilo tile.

Frankly speaking, I haven't tested it with other mods yet, but I suppose it should work in most cases