How do we know if the correlation is significant? Suppose that we have continuous data $(X_1,Y_1),\dots,(X_n,Y_n)$. Suppose that $r_{x,y}$ is the Karl-Pearson correlation coefficient between $X_i$'s and $Y_i$'s. For what range of values of $r_{x,y}$, can we really decide that there may indeed be a linear relationship between $X_i$'s and $Y_i$' and proceed to predict $Y$ by using a linear regression?
I'm sure the topic concerning this question should be a well-studied one. I did a little search here; couldn't find relevant posts. Any answers to the above question or pointers to such a study is greatly appreciated.

There is a difference between a well-evidenced effect and a strong effect. For example, there is good evidence that eating bacon causes cancer, but the risk is low; and there is weak evidence that smoking marijuana leaf causes cancer, but the risk is probably high. (The reason for the gap is that the bacon eaters are subject to more medical surveillance than ganja smokers.)
So a useful statistical test of whether the correlation is well evidenced is not based on the correlation coefficient, but on the sample size.
Another feature of the situation that matters is how much of the variation is explained by the correlation: this is the R-squared statistic, coefficient of determination.
