There are several backgrounds and subraces that give characters extended spell lists. Examples are the Guilds of Ravnica, the Dragon-Marked Houses of Eberron and Colleges of Strixhaven. They often have quite useful spells, but only if you choose to play a class that is not typically associated with the background or sub-race. E.g. if you play an artificer from House Cannith, your extended spell lists gives you little benefit, as artificers already have these spells. The same goes for a Druid of the Selesnya Enclave in Ravnica or to a lesser extent for a Bard from the Silverquill College. If you chose classes that are not typical for the background or subrace however, the extened spell lists can be really powerful.

Are these backgrounds intended for characters that are played against an archetype, or are the spells chosen for a lack of better options from other classes? What are your opinions on extended spell lists from backgrounds and subraces?

This isn't an issue confined to backgrounds. I made a character intended to be a fire genie. I chose Fire Genasi as a race for the appearance, and Warlock for their class an Efreeti Genie Patron for the powers. I needed the Genie pact so I could have a vessel and it made no sense to pick the other types of Genie as a patron. It meant that I had similar problems - two lots of fire resistance, for instance.

I don't think these options are built with the specific intent of having characters play against their archetype...I think they just build what makes sense. It makes sense that Fire Genasi would be resistant against fire, it also makes sense that Efreeti would grant fire resistance to their patron. Unfortunately, in specific areas, there isn't a whole lot of variety and so there tends to be a significant overlap. Since things don't stack very often in 5e, that creates a lot of redundancy and therefore you see the problems that you had with those backgrounds.