topic Re: bug with using parameters in a sql task in Data Engineering

bug with using parameters in a sql task

kenmyers-8451 — Mon, 19 May 2025 18:00:59 GMT

I am trying to make a sql task that runs using a serverless sql warehouse that takes a variable and uses that in the sql file that it is running in a serverless warehouse, however I am getting errors because databricks keeps formatting it first with quotation marks. For example let's say the file I'm trying to run just has USE CATALOG my_catalog_{{env}} and my task has an env parameter with the value dev. This sql ends up getting formatted as USE CATALOG my_catalog_'dev' which clearly isn't valid sql. This seems like a bug, I don't think databricks should be adding quotes to these values because there are many cases where a user would want to format something without quotes around it. I can provide a workflow sample if this helps.

Re: bug with using parameters in a sql task

kenmyers-8451 — Mon, 19 May 2025 18:35:35 GMT

Here is another reason I believe this is bugged. I'm looking at the documentation on how to format sql using parameters and I see almost this exact example for formatting the catalog. It gives two options, you can format with curly braces or with the IDENTIFIER function. I've gotten the IDENTIFIER function to work for us, but the curly brace version is adding extra quotation marks.

Re: bug with using parameters in a sql task

Renu_ — Tue, 20 May 2025 10:42:37 GMT

Hi @kenmyers-8451, when you use the curly bracket syntax like {{env}}, it treats the value as a string and automatically adds quotes, that’s why you’re seeing something like my_catalog_'dev'. If you’re trying to use dynamic SQL object names, it’s better to avoid the curly brackets and instead use the colon syntax with IDENTIFIER() to reference the parameter correctly.

Re: bug with using parameters in a sql task

kenmyers-8451 — Tue, 20 May 2025 14:30:06 GMT

@Renu_ consider this example

ALTER TABLE some.table SET OWNER TO IDENTIFIER('`' || :owner_name || '`')

You can't use IDENTIFIER here, it throws an error (it is not a supported use case). If you try to do

ALTER TABLE some.table SET OWNER TO `{{owner_name}}`

this gets formatted to

ALTER TABLE some.table SET OWNER TO `'my_owners_name'`

Which errors because those quotes it adds are invalid here. There is NO way to format this sql with a task parameter.

Re: bug with using parameters in a sql task

asuvorkin — Thu, 09 Oct 2025 13:49:45 GMT

I have been trying to use templates as well and got the following string:

LOCATION 's3://s3-company-data-' dev '-' 1122334455 '-eu-central-1/path_to_churn/main/'