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.

Like hypothesis testing in general, all you can do is propose a null hypothesis and calculate the probability of seeing the data given that null hypothesis. There is no point at which the data "definitely" comes from correlated sources, only some line in the sand where you decide that the data is "unlikely enough" to reject the null.
If you want to know how to calculate the p-value, you need to know the correlation coefficient and degrees of freedom (which is number of data points minus two). The formula generally given is $p = \frac{r \sqrt{n-2}}{1-r^2}$. There are many online calculators that give you $p$ given $n$ and $r$.
However, this formula is for the null hypothesis that the data is coming from normal IID. Just because this null is rejected, does not mean that there isn't some other hypothesis that doesn't involve correlation between $X$ and $Y$; if there is correlation within $X$ and $Y$, rather than between them, that increases the probability of seeing large sample correlation.
