Initializing Session Variables for use in row filtering/column masking in Every Session

AntonioR — Tue, 11 Jun 2024 20:36:36 GMT

I just got through reading the great technical blog post from @SergeRielau about session variables

https://community.databricks.com/t5/technical-blog/sql-session-variables-stash-your-state-and-use-it-too/bc-p/72453#M193

Super useful facility and well explained, thanks.

Here's my question, though:

I have a need for a session variable (containing a Map) to be initialized in every session based on the results of a database query. It will be used in row filtering and column masking functions in Unity Catalog, so it's necessary that there's basically no way ever to sidestep this initialization for reasons of needing to protect sensitive data.

Where is the best place to put that initialization code to set such session variables at the very beginning of every session?

My first thought had been to do a kind of lazy call to initialize the variables in the row filtering/column masking functions but that's not feasible since SQL UDFs we're using for those can't have side effects such as setting session variables.

I'm in the dark on how to get this done. Any suggestions appreciated.

topic Initializing Session Variables for use in row filtering/column masking in Every Session in Data Governance

Initializing Session Variables for use in row filtering/column masking in Every Session