Yeah, generating convincing fluid motion procedurally can be really tricky. The curlNoise VOP is really just using the vector curl of a smooth noise field to generate a divergence-free turbulence field, but that doesn't necessarily looks like fluid motion by itself; it just gives you turbulence with no clumping and no "gutters" in the velocity field. In the original Bridson paper, he procedurally generates potential fields that mimic fluid motion, vortex rings and flows and such, then adds the noise on top as turbulence and takes the curl of the summed potential field. Doable in VEX, but certainly not trivial. He also uses a variant of Perlin Noise called Flow Noise as the potential function, that tries to give the turbulence a more swirling-fluid motion. Houdini's implementation doesn't use FlowNoise, FWIW.
Some recent research seems to focus on adding divergence-free turbulence on top of an existing low resolution fluid sim to boost the apparent resolution of the sim - since the sum of divergence-free vector fields is also divergence-free. You can do this using the CurlNoise VOP to add turbulence to particles being advected by a lo-res fluid sim. The trick is to apply the turbulence in a way that looks like its naturally occurring and evolving. That's what this paper and this thread are about.
Anyway, unnecessarily long way of saying, you're absolutely right CurlNoise by itself is not a magic bullet for convincing procedural fluid motion.