OK, after getting my bookmarks fixed and syncing correctly (an issue in its own right), I really like how this works. It's not perfect and hopefully it will get some tweaks going forward, but it's pretty good stuff.
You may find that you'll want to re-organize your bookmarks into folders differently; doing so helps a lot with this feature.
That is, I used to have somewhat related Favorite folders, but they really weren't organized as sharply as they could have been. Now they're organized more distinctly by my browsing activity - shopping, travel, hobby 1, hobby 2, side hustle business, corporate business and so on.
Overall, I've mapped Favorite bookmark sub-folders to functions which now fit into a profile, as above: shopping, travel, hobby 1, hobby 2, side hustle, corporate business...
I also re-named the overall profile as "Base" from "Personal" - this reminds me that everything is in that profile. It's a more descriptive name and reminds me to get bookmarks moved into the right profile subfolder.
Then, I never use Base as a window - I'm in one of the profiles all the time, or at least I mean to be. So my Hobby 1 profile favorites are right there in the menu bar. Yes, you can set up completely separate bookmarks/favorites for a profile but re-creating them (especially in bulk) seemed to be too much work.
At this point I'm happy to let iCloud bookmark syncing do its thing, and the iCloud profile syncing seems to be working well.
I like that the profile management will send extensions you have turned on for that profile to devices that don't already have that extension - that's a nice touch.
You can also manage cookies and such by profile, which is pretty much expected as that's how this feature works - but still nice to have that ability.
Bottom line, profiles works pretty well and you get the favorites for the profile you're in, as long as you're willing to tweak your bookmarks appropriately.
One tweak I'd like to see is that if I happen to navigate to a site that's in a profile that it uses that profile to open the site, but I could see some issues with doing so (session cookies being one). But having the option would be great. Heck, I'd even like to specify which VPN or network connection to use on each profile (ideally, every bookmark but that's likely too much).
Also letting me know if I have duplicate bookmarks in more than one profile would be nice.