Thank you for this excellent deep dive into EXECUTE IMMEDIATE!
I’m currently writing an article for data engineers migrating from traditional RDBMS environments to Databricks, and it would be great to to have your perspective on a specific architectural behavior regarding variable scope and session state.
I have observed that if you dynamically declare a variable using EXECUTE IMMEDIATE inside a BEGIN...END block, it bypasses the local script scope and creates a session variable, but I could not find a direct reference on Databricks documentation regarding this behavior. In fact, coming from a traditional RDBMS background my self, I am trying to understand the mechanics of the BEGIN...END statement.
For example:
BEGINEXECUTE IMMEDIATE 'DECLARE V_VAR INT = 1';
END;
SELECTV_VAR;
I have the following two questions:
Is this a deliberate architectural decision? And is my assumption correct, that EXECUTE IMMEDIATE hands the evaluated string directly to the global Catalyst parser as a standalone statement, meaning it evaluates the DECLARE keyword using "outside-the-block" rules rather than the local compound block memory.
If we are dynamically generating a TEMP VIEW inside a script that relies on a variable, is dynamically generating a session variable via EXECUTE IMMEDIATE the recommended approach? Or is it better to construct the TEMP VIEW statement using the EXECUTE IMMEDIATE command, passing local variables with the USING keyword? (Accepting the "difficulties" in creating the statement, e.g. string handling)
