Using a second SIM on an Android phone really is a hit and miss affair, Google would do well to improve what it offers, but work profiles are exactly the sort of separation you've been longing for.

Physical SIM Card vs. eSIM: What Sets Them Apart and Which Should You Choose? These two ways of connecting your phone to a cellular network are very different.