If I compression test my engine and the results are fine, could there ever be any need to leak-down test it as well? IOW, can a leak down test ever fail if the compression test is fine.
Yes it can. The reason is, these are two different tests, done differently, reading different things. To understand this, you need to understand how the tool works for each test.
For the compression test, the test apparatus captures and holds the compression as it builds through several, but counted revolutions of the crankshaft. You will get so much pressure built up on the first revolution, but this will not be the complete pressure. You need to turn the engine over for four or more compression cycles on that cylinder in order to get your reading. It is important you run the compression stroke the same amount of revolutions for each cylinder to get an accurate reading between cylinders. There are two main purposes of the compression test: 1) to get a final compression pressure reading; 2) to see the variance between the cylinders after all cylinders are tested.
For the leak down test, a separate air source is used. You put the apparatus into the spark plug hole, ensure the piston is at top dead center (TDC) of the compression stroke (to ensure all valves are closed), then apply the air to cylinder. You then take the reading and see what your leak down % is. (Here is a good video of the procedure) Again, you are looking for overall leakage as well as the difference between cylinders.
The major difference here is, even if you have a cylinder which has a leaking valve, it will still continue to build pressure. The pressure may be less than it should be using a compression tester, but you'll still build pressure because the gauge itself will capture the pressure as it builds.