topic Re: dynamically create file path for sql_task in Data Engineering

dynamically create file path for sql_task

kenmyers-8451 — Tue, 20 May 2025 04:52:38 GMT

I am trying to make a reusable workflow where I can run a merge script for any number of tables. The idea is I tell the workflow the table name and/or path to it and it can reference that in the file path field. The simplified idea is below:

resources: jobs: test_workflow: name: test_workflow description: some workflow tasks: - task_key: run_sql_file sql_task: file: path: ${workspace.file_path}/{{job.parameters.sql_directory}}/{{job.parameters.table_name}}.sql source: WORKSPACE warehouse_id: ${var.serverless_sql_warehouse_ID} queue: enabled: true parameters: - name: sql_directory default: "path/to/sql" - name: table_name default: "" # this one I change when running the task or when calling this workflow from another workflow

However this doesn't work. ${workspace.file_path} is able to be formatted as expected but {{job.parameters.sql_directory}} and {{job.parameters.table_name}} are not able to be formatted, they don't dynamically update and they stay written exactly as is with the curly braces.

I'm wondering if there is anyway around this or to make this happen?

Re: dynamically create file path for sql_task

Bhaskar27 — Tue, 20 May 2025 10:04:42 GMT

Hi @kenmyers-8451

From what I know, you can't mix ${} and {{}} variable styles in the SQL file path. A better approach would be to pass the full SQL file path as a single job parameter, and update its value when triggering the job.

Re: dynamically create file path for sql_task

kenmyers-8451 — Tue, 20 May 2025 13:59:58 GMT

Thanks @Bhaskar27 , unfortunately this doesn't work

path: {{job.parameters.merge_file_path}}

I get a yaml error when I try to deploy because "key is not a scalar". So it seems like it is not possible to use this kind of dynamic variable for the sql_task file path.

Edit: just in case this issue was fixed in a newer version, I upgraded my CLI to 0.252.0 (latest version right now) and I get the same error.

Re: dynamically create file path for sql_task

jtirila — Wed, 18 Jun 2025 06:35:06 GMT

@kenmyers-8451 were you able to solve this? I'm having a similar issue.

Re: dynamically create file path for sql_task

jtirila — Wed, 18 Jun 2025 06:56:39 GMT

Oh, never mind, I got it working. Just using single quotes around the {{ }} part solves it (I guess double quotes would work as well.)

I think I tried this yesterday but probably run into another isssue with dashes in task names: https://community.databricks.com/t5/data-engineering/dab-for-each-task-passing-task-values/td-p/121072