So I've spent a few weeks lightly comparing my very prosaic (and obviously sub-par by the standards of this forum!) Roon streaming setup:
ROCK on Intel NUC8i3
PS Audio Bridge card
rPi4 running RoPieee -> USB
Unifi network gear
... with the 2 transports I got, the Cambridge CXC and PS Audio PerfectWave SACD, connected via SPDIF, AES/EBU or I2S in the case of the PSA (just coax for the Cambridge).
To cut to the chase, CD does sound better than streaming, and the PSA transport does sound better than the Cambridge, so no huge surprises there. For me though, none of it has been as game-changing as some people have expressed. All things being equal I certainly prefer the sound of CDs to files/streams, but the flip side is I find myself listening to significantly less music overall when I think about the medium too much. So in no world do I see CD as a replacement for streaming but as a nice-to-have optimization of my audio experience when I have the software on hand. Obviously there's no way for CD to compete with the constant stream of interesting new releases I'm able to access on Qobuz, which still sound very good (hi-res does help make up some, though not all, of the SQ difference). Like others I also don't see the point in spending $10k+ on streaming hardware given the immediate obsolescence and relatively nascent state of the sector. Since this is ultimately about music, I am totally fine with the current setup and enjoying as much music as possible, but perhaps will try some network tweaks and streamer upgrades at some point.
The primary improvements I hear with CD over streaming jive with what others have said - less noise, better bass, better soundstaging, more organic and natural. The improvement in any one particular area is fairly subtle, perhaps even marginal, but in combination make for a nice step up in the overall enjoyability. The noise is obviously not audible hiss or anything of that sort, but more like timing uncertainty - it reminds me of playing a violin without having enough rosin on your bow, so you can't grip the string consistently and your bow articulations become a little indistinct. There's a random fuzz that's added to every leading edge and decay that makes everything feel a bit less pure, a bit more electronic and synthetic. The bass is also certainly better on the transports in terms of power, pitch and articulation. And on most recordings, the soundstage is wider, deeper and better formed with more tangible images.
The other thing about CDs is they're more consistently good-sounding than streaming. The whole "sounds better at night" phenomenon still applies to the entire system, but streaming seems more variable, which makes sense given it has so many more vectors for noise to enter the system. The times I felt like the gap in sound quality was widest were also the times where I felt streaming sounded the crappiest, usually middle of the day. At night when things quiet down, both sounded good and the gap narrowed. Which perhaps justifies the insane lengths some of the server/streaming solutions go to w.r.t. power supply quality and noise isolation. What I have trouble stomaching is the cost of those efforts - I think I'll wait for some cleverer, less expensive ways to make the system more resilient to noise, which I'm sure will come.
Re: a $600 transport vs. a $6k one - interestingly, the sonic signature of the Cambridge is pretty much like every other Cambridge piece I've tried. It's got good punch (PRAT I suppose?) especially in the mid bass, a very slightly forward and pinched perspective, a tiny bit more grain in the upper frequencies and a little less dimensionality - basically good SQ, but with some mid-fi limitations. The PSA is more unforced, more organic, more relaxing to listen to. I have switched rapidly between the coax, AES/EBU and I2S connections between the PSA transport and DAC using both generic and decent cables. The differences are quite subtle and while I2S does seem best, I'm doubtful I'd be able to identify them accurately in an A/B test. The PSA transport fundamentally sounds the same regardless of which interface you use, the I2S having marginally better clarity, soundstaging and bass. Perhaps I just need a better I2S cable but I was expecting much more differentiation regardless.
Incidentally, I'm less convinced of the value of the PSA as a SACD transport vs. a DSD file on the Roon server. I'm actually not sure it sounds better - the sonic differences between a CD on the transport and a WAV/FLAC on the server do not hold when comparing a SACD to a DSD file. I haven't listened to enough SACDs to say for sure, but so far I'm finding the SACD transport value prop less compelling than with CD.
So to sum up where I stand now: Sure, I think CDs sound better than streaming and given the choice I'd opt to play a CD over a file/stream. It also sounds like it would be quite expensive and difficult to improve streaming in the areas where CD excels. But it's not as big a factor to my overall enjoyment of the system/music as I was initially expecting and the easier solution is to not worry about it too much.
I'm debating if I should also give another transport e.g. the ProJect RS2-T a try - maybe something just a little better would take CD over the top more clearly? Open to suggestions there. Otherwise, I may hang onto the PSA just for the optionality.