Databricks sql variables and if/else workflow

JKR — Fri, 12 Jan 2024 22:39:39 GMT

I have 2 tasks in databricks job workflow first task is of type SQL and SQL task is query.In that query I've declared 2 variables and SET the values by running query.e.g:

DECLARE VARIABLE max_timestamp TIMESTAMP DEFAULT '1970-01-01';
SET VARIABLE max_timestamp = (select max(timestamp) from Table);
select max_timestamp ;

Can I pass this max_timestamp variable to the next if/else task to check if it is null or is there any value in this variable and pass that value of max_timestamp to another task. or is there any workaround to do this? I want to use SQL warehouse for this.

I created a SQL task and assign max timestamp value from subquery to the SQL variable and also created if/else task and in condition I've added {{tasks. [task_name]. values.[value_name]}}, but the job throw below error on the if/else task.

run failed with error message

Failed to resolve references: Task value referenced by 'tasks.task_name.values.max_timestamp' was not found.

Query based dropdown list option in databricks sql editor will execute the query multiple times if I use the variable at multiple places in a big sql query ? If yes then it will be executing query to max_timestamp everytime I refer in the query.

Re: Databricks sql variables and if/else workflow

NandiniN — Wed, 11 Dec 2024 09:57:43 GMT

Please try with

max_timestamp = dbutils.jobs.taskValues("sql_task_1")["max_timestamp"]

dbutils.jobs.taskValues("python_task_1", {"max_timestamp": max_timestamp})

Reference- https://docs.databricks.com/en/jobs/task-values.html

topic Databricks sql variables and if/else workflow in Data Engineering

Databricks sql variables and if/else workflow

Re: Databricks sql variables and if/else workflow