This is due to how sql and spark execute these functions differently.

• In SQL, the rand(42) function is deterministic for each row within that session/query. Hence it produces the same result every time you run that query.

• In a Python Notebook, the execution is distributed across Spark executors. Spark's rand(seed) is only deterministic per executor, not globally. Also see rand function's source code, they also mention there that this is non-deterministic in the general sense, and hence the behavior that you are seeing.