I have product hierarchy as follows:
Category
Sub category
Product
When does it make sense to have all these 3 columns in 1 table called Prooduct, or to make 3 tables - 1 for each?
They should all be in a single table as they're logically related. Not only that, but data modelling is very different compared to OLTP. For instance, it is not uncommon to have a junk dimension which contains several columns which can all appear unrelated. Modelling is a very detailed subject and if you're after reading material, I can recommend Star Schema: The Complete Reference below.
https://www.amazon.co.uk/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320
The only time is makes sense to have these in separate tables and "snowflake" them is to take advantage of a little trick in PowerBI to suppress blanks in a slicer or matrix if you have a ragged hierarchy but this comes with drawbacks in having to write more complicated DAX down the line.