With the pixel6 (and most android phones) you can set it up to do both. So you have the "nice" version generated by the phone and a dng raw file to work with. I have that set up along with syncthing to deliver the raw photos to my laptop (pro tip, this is super handy).
The new iphone and the pixel6 both use the same trick where they have a 50 megapixel sensor (probably the same and likely a Sony sensor) that produces 12.5 megapixel raw photos with four pixels combined information. So the dng I get from my phone has already had some processing done to it but not a lot. Also worth noting that both phones have multiple lenses with different focal lengths and sensors. So, it matters a lot which one you use. You'd control this via the camera app typically with its different modes and zoom levels. I'm not sure if it uses exposures from all sensors to calculate a better raw but that would not surprise me.
In terms of noise, the image quality is actually very good. I've done some night time photography with both the pixel6 and my Fuji XT-30, which is an entry level mirror less camera. The Fuji has better dynamic range and it shows in the dark. But the noise levels are actually pretty good for a phone camera. Very usable results with some simple post processing. Especially compared to my previous Android phone (a Nokia 7 plus) which was noisy even in day light. Mostly doing raw edits is not worth doing that but it's nice to have the option. The phone does a decent job of processing and mostly gets it right when it comes to tone mapping and noise reduction. When it matters, I prefer the Fuji. But sometimes the phone is all you have and you just take a quick photo and it's fine.
A high end full frame camera will get you more and better pixels and more detail. Even an older entry level dslr will generally run circles around smart phone sensors. And that's just the sensor and camera. The real reason to use these is the wide variety of lenses and level of control over the optics that those provide. In phone bokeh is a nice gimmick. But it's a fake effect compared to a nice quality lens. Likewise you can't really fake the look you get with a good portrait lens (the effect that things in the background seem bigger). Phone lenses have a fixed focal range and generally not that much aperture range. There's a reason people pay large amounts of money to own good lenses. They are really nice to use and deliver a great photo without a lot of digital trickery. And they are optimized for different types of photography. There is no one size fits all lens for all photography.