So basically what I am asking is how feasible is it to produce something with multiple sprites attached to it, then slapping a turret on it so it has an attack. More or less I have an idea for a race in a little mod I am toying with where I would like to have many different goliaths/tanks with different weapons, but I do not want them all to look similar or too much reliance on new GRPs/plugins. Like could I just throw a tank/missile turret onto a Goliath? What would it end up looking like out of curiosity? Or vice versa for the Goliath/Tank?

You can easily swap the Tank/Goliath turrets around by just changing the subunit of the base in units.dat. The top of the Missile Turret though is not actually a subunit, it's an image overlay. However, what you can do is make a subunit that has the graphics of the Missile Turret overlay. I think it may require a little bit of iscripting, but it shouldn't be too bad.

What about trying to give a turret to a unit that didn't previously have one?

There's no issue with that. You just need to make sure that whatever graphic that unit uses has a .lo? file assigned to it in images.dat.

you can give a unit 4 weapons at least by giving it a subunit