DAB dashboards variable substitution in dataset queries

[DaveRuijter]

Describe the issue

When developing a dashboard, currently it does not seem possible to parameterise the catalog and/or schema names used in the datasets queries of a dashboard.

I've tried to use the below formats but none of them seem to work:

• IDENTIFIER(:catalog)
• :catalog
• ':catalog'
• IDENTIFIER(':catalog')

Furthermore, when this would work I'd like to pass/overwrite the parameter value with a specific value per target in my databricks.yml configuration, to specify in my case the catalog to read from (which is different on each environment).
Not sure if that is supported already?

Configuration / Steps to reproduce the behavior

1 Create a new dashboard
2 Go to the data tab and use this query:
SELECT * FROM :catalog.information_schema.catalogs
3 See error:

[PARSE_SYNTAX_ERROR] Syntax error at or near ':'. SQLSTATE: 42601 (line 1, pos 23)

== SQL ==
DESCRIBE SELECT * FROM :catalog.information_schema.catalogs
-----------------------^^^

Expected Behavior

• I'd like to use IDENTIFIER(:catalog) or something similar in the queries.
• I'd like to be able to set such a parameter value via my databricks.yml configuration, maybe like this:

resources:
  dashboards:
    nyc_taxi_trip_analysis:
      display_name: "NYC Taxi Trip Analysis"
      file_path: ../src/nyc_taxi_trip_analysis.lvdash.json
      warehouse_id: ${var.warehouse_id}
      parameters:
         - name: catalog
           default: ${var.catalog}

Actual Behavior

See error message above

OS and CLI version

Databricks CLI v0.234.0

Is this a regression?

No

Debug Logs

N/A

[pietern]

Thanks for reporting this issue and feature requests.

W.r.t. the issue, this is a known problem. A fix is underway and is expected to be released in January. Once landed, you'll be able to use USE CATALOG IDENTIFIER(:catalog). In the meantime, you can work around this limitation with the SQL equivalent of an eval:

-- Default to catalog set in parameter
declare catalog_name string;
set var catalog_name = :catalog;
declare catalog_query string;
set var catalog_query = concat("use catalog ", catalog_name, ";");
execute immediate catalog_query;

-- Default to schema set in parameter
use schema identifier(:schema);

W.r.t. the feature request, we're working on sorting out how to best support this. Overriding parameters is not natively supported in the dashboard APIs, so we're figuring out the best course of action. I will post back here with updates to unblocking the pattern you're looking to achieve.