Here's a trivial example
Let's say you think that scores on a qualification exam (X) are correlated with the wage for certain city jobs (Y)
You check and find that wage and salary are perfectly correlated! Every person who got a particular score on the test has the exact same wage (and vice versa).
But actually what's going on is that you wage is determined by how much the mafia boss running the city likes you (Z).
He decides how much to pay each you based on his whim, and then rigs your test to give you a particular score based on your salary to make it look (sort of) legit.
So X and Y are perfectly correlated, but only because of a confounding variable Z, which is causing both of them.