Is Rogue Trader fun if I know nothing about Warhammer 40k?

Yes, it's honestly one of the easier ways into 40k. Nearly every lore term in the dialogue is a hover link with a short explanation, so when someone mentions a Navigator or the Inquisition you can check it without leaving the conversation. You're also out on the edge of the galaxy running your own dynasty, so the story doesn't lean on knowing the big wars. The bit that catches newcomers is the combat, not the lore. It's crunchy turn-based with a lot of stats, so drop the difficulty if you mainly want the story.