For your consideration:
Migrating from `${param}` to Named Parameter Markers in Databricks SQL
Background:
Databricks is deprecating the `${param}` syntax for parameter substitution in SQL cells and recommends using the new named parameter marker syntax (e.g., `:parameter_name`) for better compatibility, security, and maintainability. This affects workflows where Python notebooks set parameters using `dbutils.widgets` and `spark.conf.set`, and SQL notebooks reference them using `${param}`.
---
Minimal Change Migration Strategy
1. Update Parameter References in SQL Notebooks
Replace all instances of `${catalog.name}` and `${schema.name}` with the new syntax:
- Instead of `${catalog.name}`, use `:catalog_name`
- Instead of `${schema.name}`, use `:schema_name`
Example Migration:
Before:
```sql
select '${catalog.name}' as catalog, '${schema.name}' as schema
use ${catalog.name};
select * from ${schema.name}.emp_details;
```
After:
```sql
select :catalog_name as catalog, :schema_name as schema
use identifier(:catalog_name);
select * from identifier(:schema_name).emp_details;
```
- Use `identifier(:param)` to safely substitute schema or table names.
---
2. Update Parameter Passing from Python Notebook
Previously, you set parameters using `dbutils.widgets` and `spark.conf.set`. With the new parameter marker syntax, you should ensure that parameters are exposed as notebook widgets, as Databricks will automatically create widgets for any `:parameter_name` used in SQL cells.
You can still use `dbutils.widgets.text` in your config notebook:
```python
dbutils.widgets.text("catalog_name", "catalog_de")
dbutils.widgets.text("schema_name", "emp")
```
- The widgets will appear in the UI when the SQL notebook is run, allowing users to set values interactively.
You do not need to use `spark.conf.set` for parameter passing in SQL anymore.
---
3. No Change Needed for `%run` Imports
Continue using `%run ../../config/param_notebook` at the top of your SQL notebooks to initialize widgets.
---
4. Summary Table: Migration Comparison
| Old Syntax (Deprecated) | New Syntax (Recommended) |
|-------------------------------|----------------------------|
| `${catalog.name}` | `:catalog_name` |
| `${schema.name}` | `:schema_name` |
| `${param}` in SQL | `:param` in SQL |
| `spark.conf.set(...)` for SQL | Use widgets only |
---
5. Additional Notes
- Identifier Wrapping: Use `identifier(:param)` when substituting schema, table, or column names to avoid SQL injection and syntax errors.
- Widget UI: When you use `:catalog_name` in SQL, Databricks automatically provides a widget for user input in the notebook or dashboard UI.
- Automation: Databricks has announced an assistant action to help automate this migration in the future.
---
6. Example: Full Minimal-Change Workflow
Python config notebook (`../../config/param_notebook`):
```python
dbutils.widgets.text("catalog_name", "catalog_de")
dbutils.widgets.text("schema_name", "emp")
```
SQL notebook:
```sql
%run ../../config/param_notebook
select :catalog_name as catalog, :schema_name as schema;
use identifier(:catalog_name);
select * from identifier(:schema_name).emp_details;
```
---
Summarizing:
To accommodate the deprecation of `${param}` in Databricks SQL, replace `${param}` with `:param` in your SQL notebooks, use `identifier(:param)` for dynamic object names, and continue using widgets for parameter definition. This approach requires minimal changes and aligns with Databricks' unified parameter handling.
Cheers, Lou.
