This only addresses your first question, but I wanted to point out that cancellation does not have to be accidental. It can be the outcome of purposeful human behavior. This is a key point to me since that implies cancellation (and sign-flipping of the correlation) is the norm rather than a once-in-a-very-blue-moon event.
This example is taken from Scott Cunningham's Causal Inference: The Mixtape.

But weirdly enough, sometimes there are causal relationships between
two things and yet no observable correlation. Now that is definitely
strange. How can one thing cause another thing without any discernible
correlation between the two things? Consider this example, which is
illustrated in Figure 1.1. A sailor is sailing her boat across the
lake on a windy day. As the wind blows, she counters by turning the
rudder in such a way so as to exactly offset the force of the wind.
Back and forth she moves the rudder, yet the boat follows a straight
line across the lake. A kindhearted yet naive person with no knowledge
of wind or boats might look at this woman and say, “Someone get this
sailor a new rudder! Hers is broken!” He thinks this because he cannot
see any relationship between the movement of the rudder and the
direction of the boat.
But does the fact that he cannot see the relationship mean there isn’t
one? Just because there is no observable relationship does not mean
there is no causal one. Imagine that instead of perfectly countering
the wind by turning the rudder, she had instead flipped a coin—heads
she turns the rudder left, tails she turns the rudder right. What do
you think this man would have seen if she was sailing her boat
according to coin flips? If she randomly moved the rudder on a windy
day, then he would see a sailor zigzagging across the lake. Why would
he see the relationship if the movement were randomized but not be
able to see it otherwise? Because the sailor is endogenously moving
the rudder in response to the unobserved wind. And as such, the
relationship between the rudder and the boat’s direction is
canceled—even though there is a causal relationship between the two.
This sounds like a silly example, but in fact there are more serious
versions of it. Consider a central bank reading tea leaves to discern
when a recessionary wave is forming. Seeing evidence that a recession
is emerging, the bank enters into open-market operations, buying bonds
and pumping liquidity into the economy. Insofar as these actions are
done optimally, these open-market operations will show no relationship
whatsoever with actual output. In fact, in the ideal, banks may engage
in aggressive trading in order to stop a recession, and we would be
unable to see any evidence that it was working even though it was!
Human beings engaging in optimal behavior are the main reason
correlations almost never reveal causal relationships, because rarely
are human beings acting randomly. And as we will see, it is the
presence of randomness that is crucial for identifying causal effect.