If I fork someone else’s private Github repo into my account, is it going to appear in my account as a public repo?

No. You can fork it and it still remains private.

Private collaborators may fork any private repository you’ve added
them to without their own paid plan. Their forks do not count against
your private repository quota.

https://github.com/plans