You’re right that the way we draw our maps with North pointing up and South pointing down is largely arbitrary, and indeed there are a number of maps with the Southerly direction at the top rather than at the bottom, and they’re good fun to look at. However, there are good reasons to say that a Northerly or Southerly direction should be “up”, and these reasons extend beyond just the rotation of the Earth.
The rotation of the Earth is a good starting place, though - the rotation axis of the Earth goes more or less through the North and South magnetic poles of the Earth. The magnetic North & South poles wander a little, so some years they’re closer to the rotation axis than others. Fixing the rotation of the Earth as a cardinal direction makes good sense, and is what we’ve done - East and West point 90 degrees from North and South.
There’s one more reason to put North as up, and it’s a physics convention. Most of the time, when we’re talking about rotation, we say that the direction of the rotation axis is actually just in one direction, rather than having to indicate both North and South. If we do this, it allows us to encode both the axis of rotation, and the direction of rotation at the same time. The way we determine which of North or South should be “the direction”, we use what’s called the “right hand rule”. You curl your fingers in the direction of rotation, and your thumb points in the direction of the rotation axis. In the Earth’s case, we rotate towards the East, so your thumb will point in the direction of North.
However, if you’re thinking of orientations beyond just the Earth’s own rotation, while it’s true that there’s no way to set an entirely objective zero point from which to measure other positions, and a sphere doesn’t have much intrinsic orientation to it, we can still do relative positions pretty well. And on the scale of our solar system, we have a pretty solid alignment going on. All the major planets in our solar system trace oval paths around the Sun as they go about their respective years. Not only do they orbit around the Sun in the same direction, they all tend to point their rotation axes in the same direction (notable exceptions here are Venus and Uranus). On top of all that, the ovals are almost perfectly aligned in a flat plane. If we take our same physics convention and use the rotation of the planets around the Sun to tell us which direction we’re going to point up, our Planet Earth based North is more or less pointing in the right direction. Our planet’s spin is not perfectly aligned with the “up” out of the solar system, but tilted by 23 degrees, a feature of our planet responsible for our seasons. This tilt is why many globes are set at an angle - they’re mimicking the tilt of our planet relative to the “up” defined by our solar system.
So the North is up convention is partially mapmakers, partially the spin of our Earth, and partially physics notation, but there are definite ties between all of them.