Azure Synapse CI/CD Pipeline Spark Pool Parameterization Issue

Question

Azure Synapse CI/CD Pipeline Spark Pool Parameterization Issue

Debbie Edwards 521

A Synapse Project with three notebooks is set up, each utilizing a Spark cluster, e.g., sparkuksdev. The notebooks are part of a pipeline where the Spark cluster is parameterized through a SQL Data Warehouse's Parameters table using @activity('LookupGetParameters').output.firstRow.sparkpool. A stored procedure retrieves the parameters for each pipeline instance, which updates correctly across dev, tst, and prod environments.

However, an issue arises during a YAML pipeline execution that transfers Synapse to different environments, resulting in the following error:

##[error]Encountered with exception:Error: Failed to fetch the deployment status {"code":"BadRequest","message":"The document creation or update failed because of invalid reference 'sparkuskdev'."}

Several JSON and YAML files are in use to support the process, and any insights into the problem or overlooked elements would be appreciated.

Main Branch template-parameters-definition.json

The template-parameters-definition.json file is used to define parameters for template customization across environments. However, a parameters section for the spark pool (bigDataPool) has not been added, leading to uncertainty about its functionality.

User's image

I believe this is the only section that has been added that possibly relates to the spark pool

Synapse Artifacts Deploy Pipeline YAML

There is an expectation that the above file would automatically update the YAML for the Synapse Artifacts Deploy Pipeline with a variable like:

-name sparkpool
value 'projsp${(parameters.environment)}'

But no changes have occurred, suggesting that there may be a misconfiguration.

TemplateForWorkspace.JSON

This template was assumed to automatically include parameters, thereby parameterizing hardcoded spark pool values. However, the spark pool reference remains hardcoded as sparkdev, indicating a lack of automatic updates.

TemplateParametersForWorkspace_Dev.JSON

These parameters appear to have been manually updated per environment, showing numerous references to the spark pool (e.g., "read_json_properties_bigDataPool_referenceName": { "value": "sparkdev"}). It is unclear if these were generated from TemplateParametersForWorkspace.JSON or manually inputted.

Comprehensive documentation has been reviewed, but the understanding of how to achieve a fully functional setup remains elusive. Any guidance on resolving these issues would be greatly appreciated.

2 answers

Your answer

Answer 1

Venkat Reddy Navari 1,585 Microsoft External Staff

Hi @Debbie Edwards You're running into issue with Synapse CI/CD deployment where Spark pool references aren’t properly parameterized in your ARM templates. The key error — "The document creation or update failed because of invalid reference 'sparkuskdev'" — suggests that during deployment, the Spark pool name is still hardcoded rather than dynamically resolved via your environment-specific parameters.

Here are a few things to double-check:

Parameter Definition in template-parameters-definition.json It’s crucial that you explicitly define a parameter for the Spark pool (e.g., bigDataPool) in this file. Without it, your deployment won’t know to expect a value for substitution.
templateForWorkspace.json: Within this template, all references to the Spark pool should use a parameter reference lik
```
   "bigDataPool": {
   "referenceName": "[parameters('bigDataPool')]",
   "type": "BigDataPoolReference"
   }
```
If referenceName is hardcoded to sparkdev, it won’t dynamically update during deployment. This is likely the root cause of the BadRequest you're seeing.
TemplateParametersForWorkspace_{env}.json: These environment-specific parameter files should include entries like:
```
   "bigDataPool": {
   "value": "sparkuksdev"
   }
```
Make sure these are consistent and match the pool names for each environment.

YAML pipeline: It won’t auto-update values unless the pipeline explicitly passes parameters into the deployment template. Ensure you’re passing the right environment-specific parameters file using something like

   parameters:
   - name: environment
   default: 'dev'
   ...
   - task: AzureResourceManagerTemplateDeployment@3
   inputs:
   csmFile: 'templateForWorkspace.json'
   csmParametersFile: 'TemplateParametersForWorkspace_$(environment).json'

Regenerate Synapse templates after changes: Any manual edits should be followed by re-exporting or regenerating your Synapse workspace templates to maintain integrity.

If you fix the parameterization in both the template and the parameters files and ensure your pipeline is wired up to pass those correctly, your deployment should succeed without hardcoded values.

I hope this information helps. Please do let us know if you have any further queries. Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Debbie Edwards 521 Reputation points

2025-04-24T14:31:24.7833333+00:00

Thank you so much. I will pass this across to the team and go through each step ASAP.
Venkat Reddy Navari 1,585 Reputation points Microsoft External Staff

2025-04-25T10:57:41.25+00:00

Hi @Debbie Edwards Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Venkat Reddy Navari 1,585 Reputation points Microsoft External Staff

2025-04-28T09:42:44.7833333+00:00

@Debbie Edwards Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Debbie Edwards 521 Reputation points

2025-04-28T11:56:46.17+00:00

I think we added everything above. And actually found some old Pipelines that hadn't yet been parameterised so they were hardcoded to sparkuksdev and this was the issue.

However we have now hit another issue with the SQL Pool Stored Procedure activity

If you look at sqlPool. You attach the SQL Pool in this case sqldwuksdev but you can't parameterise it like you can the spark pool.

we are having a really hard time figuring out how to resolve this. The error is

##[error]Encountered with exception:Error: Failed to fetch the deployment status {"code":"BadRequest","message":"The document creation or update failed because of invalid reference 'sqldwuksdev'."}
Venkat Reddy Navari 1,585 Reputation points Microsoft External Staff

2025-04-28T12:31:33.82+00:00
@Debbie Edwards Regarding the new problem with the SQL pool (sqldwuksdev), you're right: SQL Pools need to be parameterized similarly to how you handled the Spark pools during Synapse workspace deployments. If the SQL pool reference is hardcoded in your ARM templates (e.g., templateForWorkspace.json), the deployment will fail in non-dev environments because it tries to find a pool by that exact name.

Here’s a possible explanation and some steps to address it:

Add a Parameter for SQL Pool: In your template-parameters-definition.json, you need to define a parameter for the SQL pool, for example:

{ "name": "defaultSqlPool", "type": "string", "defaultValue": "sqldwuksdev" }

Update References in templateForWorkspace.json: Find where the SQL pool is attached (for example in a Notebook activity, Linked Service, or Stored Procedure activity) and replace the hardcoded reference with a parameterized one:

"defaultSqlPool": { "referenceName": "[parameters('defaultSqlPool')]", "type": "SqlPoolReference" }

Update Your Environment-Specific Parameters: In your TemplateParametersForWorkspace_Dev.json, TemplateParametersForWorkspace_Test.json, etc., set the correct pool name for each environment:

"defaultSqlPool": { "value": "sqldwdev" }

(And similarly for test/prod.)

Confirm Pipeline and Deployment Steps: Make sure your YAML (or ARM deployment step) points to the correct parameters file for the environment. Otherwise, even if parameterized properly, the wrong (or missing) value will cause a BadRequest.

Note: Stored Procedure activities in Synapse Pipelines reference the SQL Pool directly via Linked Services or inline settings. If the Linked Service itself isn't parameterized for the SQL Pool, you may also need to adjust those artifacts!

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Debbie Edwards 521 Reputation points

2025-04-30T08:24:30.79+00:00

this is the problem though. We understand and tried that above and it didnt work. Because in Synapse, in the pipeline you can't parameterise the sqlpool in the stored procedure activity.

So none of the above works. The sql pool has lots of properties that we can parameterise but we think the big issue is you cant parameterise in the pipeline activity of the sql pool name

"Note: Stored Procedure activities in Synapse Pipelines reference the SQL Pool directly via Linked Services or inline settings. If the Linked Service itself isn't parameterized for the SQL Pool, you may also need to adjust those artifacts!"

I don't quite understand this though because you just select the sql pool. There is no other way to do this. And the underlying sql pool can be found under manage?
Venkat Reddy Navari 1,585 Reputation points Microsoft External Staff

2025-04-30T11:40:18.18+00:00

@Debbie Edwards Thanks for clarifying, you're absolutely right, and this is a known limitation in Synapse Pipelines today.

Unfortunately, unlike Spark pools, you cannot parameterize the SQL pool name directly within a Stored Procedure activity. The SQL pool is selected from a dropdown in the UI, and that selection gets embedded as a static reference in the generated ARM template. There's currently no binding to a dynamic parameter the way Spark pool references can be swapped out at deploy time.

"Note: Stored Procedure activities in Synapse Pipelines reference the SQL Pool directly via Linked Services or inline settings..."

You're absolutely right to question that. To clarify: Stored Procedure activities do not use Linked Services — they directly bind to the SQL pool from the workspace itself. So unlike datasets or script activities that can be linked to parameterized connections, this activity is rigidly tied to the selected SQL pool at authoring time.

This is part of the platform's current behavior, and yes — there’s no way to “swap” that SQL pool out via a parameter at deploy time.

Using a consistently named SQL pool across environments (e.g., sqldwgeneric) is a practical and widely used workaround:

You create one SQL pool per environment with the same name.

Your pipeline always points to sqldwgeneric, avoiding the deployment errors tied to environment-specific pool names.

Configuration differences between environments (DWUs, data, etc.) are managed within the pool itself — not in the pipeline.

Optional Workaround:

If you'd like a more flexible alternative, consider avoiding the Stored Procedure activity entirely and:

Use a Script activity, Web activity, or Notebook to execute the stored procedure.

Call the SQL pool via a parameterized Linked Service.

This lets you fully control the target SQL pool at runtime using dynamic inputs.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Venkat Reddy Navari 1,585 Reputation points Microsoft External Staff

2025-05-02T10:58:03.94+00:00

@Debbie EdwardsWe haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 2

Debbie Edwards 521

We have got round this by creating a generically named spark pool. the underlying properties change via the above. Still we don't think thats really what we wanted to do but couldn't see another way round it

Share via

Azure Synapse CI/CD Pipeline Spark Pool Parameterization Issue

Main Branch template-parameters-definition.json

Synapse Artifacts Deploy Pipeline YAML

TemplateForWorkspace.JSON

TemplateParametersForWorkspace_Dev.JSON

2 answers

Your answer