Parametrizing queries in DAB deployments

[malterializedvw]

Hi folks,
I would like to ask for best practises concerning the topic of parametrizing queries in Databricks Asset Bundle deployments.This topic is relevant to differentiate between deployments on different environments as well as [dev]-deployments vs. production deployments.
A well working example are notebook tasks or DLT jobs: I can pass all sorts of parameters. I can not only parametrize the environment (dev/test/prod) but also database objects (e.g. tables) with an identifier for [dev]-deployments (to not interfere with the regular database objects).
The problem now arises with Mat Views. For those I must use a SQL warehouse, so I cant use the existing tricks. Neither can I use a (parametrizable) Databricks Query object, as those are not commitable into repos. I am currently using a sql_task on a flat file which contains a hard coded query (s. below).
Using the power of sed in the deployment process, I am indeed able to handle the environment. But [dev] deployments are not handled by that and I secretly hope for a better solution than using sed.
Is there?
Thanks and best regards
```

        - task_key: task_example

          sql_task:

            file:

              path: /Workspace/${var.path}/files/query.sql

              source: WORKSPACE

            warehouse_id: abc123
```

[Ashwin_DSA]

Hi @malterializedvw,

You don’t need Databricks Query objects for this. Have you considered using bundle variables + SQL task parameters on the .sql file? Check this documentation.

The good thing is that sql_task supports a parameters map. Any key you define there can be referenced in your SQL file as {{parameter_key}} and will be substituted by Databricks at run time.

As an example, you do something like this in your query.sql.. 

CREATE OR REPLACE MATERIALIZED VIEW
  {{catalog}}.{{schema}}.orders_mv{{table_suffix}}
AS
SELECT *
FROM {{catalog}}.{{schema}}.orders{{table_suffix}};

That way, you can use the same sql in multiple environments without havng to use sed. 

Environment differences can handled via targets + variable overrides.

Does this help?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[malterializedvw]

Hi Ashwin, this approach wouldn't work for me due to the apostrophe injected. Sorry for the bad formatting, I am running into errors trying to send it properly.

- task_key: example 
sql_task: 
file: 
path: query.sql 
source: WORKSPACE 
warehouse_id: abc 
parameters: 
    catalog: data_catalog_${var.environment}
    schema: source_schema 
    table: mat_view

CREATE MATERIALIZED VIEW IF NOT EXISTS {{catalog}}.{{schema}}.{{table}}​

[PARSE_SYNTAX_ERROR] Syntax error 
CREATE MATERIALIZED VIEW IF NOT EXISTS
'data_catalog_PROD'.'source_schema'.'mat_view'

[Pat]

Not sure if I understand correctly, but the issue is that you are using .sql file that have hardcoded env?

Use bundle substitutions/variables inside the SQL file itself

Databricks Asset Bundles support variable substitution not only in YAML but also in SQL files referenced by tasks, via the same {{var.name}} syntax. The pattern:
databricks.yml:

bundle:
  name: my-bundle

variables:
  env:
    default: dev
  table_suffix:
    default: _dev

targets:
  dev:
    default: true
    variables:
      env: dev
      table_suffix: _dev
  prod:
    variables:
      env: prod
      table_suffix: ""

SQL:

CREATE OR REPLACE MATERIALIZED VIEW IDENTIFIER(
  :env || '_gold.schema_name.mv_name'
) AS
SELECT *
FROM IDENTIFIER(
  :env || '_silver.schema_name.src_table_name'
);

job.yml:

resources:
  jobs:
    mv-job:
      name: mv-job
      tasks:
        - task_key: mv_task
          sql_task:
            file:
              path: ./sql/mv_example.sql   # committed in repo
              source: WORKSPACE
            warehouse_id: abc123
            parameters:
              env: dev

[malterializedvw]

Hey @Pat , thanks for the answer, I didnt know about the IDENTIFIER-clause and it makes me hopeful.
Yes, you got me right. So, I tried to apply your suggestion to my problem, defining catalog, schema and table in the job.yml and then updating my query.sql as follows:

CREATE MATERIALIZED VIEW IF NOT EXISTS IDENTIFIER(
  :catalog || :schema || :table
)

Also I tried this:

:catalog || '.' || :schema || '.' || :table

Neither worked though.
Exactly one CREATE [ LIVE TABLE | MATERIALIZED VIEW | STREAMING TABLE ] statement expected, but 0 found

[Ashwin_DSA]

Hi @malterializedvw,

Try this... Build your .sql file like the below,

CREATE MATERIALIZED VIEW IF NOT EXISTS
  IDENTIFIER('{{catalog}}.{{schema}}.{{table}}')
AS
SELECT
  ...
FROM
  ...;

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[malterializedvw]

Hi @Ashwin_DSA  , this does not work either. 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'catalog'. SQLSTATE: 42601 (line 1, pos 52) == SQL == CREATE MATERIALIZED VIEW IF NOT EXISTS IDENTIFIER(''catalog.schema.table'')

[malterializedvw]

The best I could come up if after some back and forth with Claude is this:

DECLARE target_fqn STRING;
DECLARE source_fqn STRING;
DECLARE q STRING;
DECLARE sql_stmt STRING;
SET VAR target_fqn = :target_fqn;
SET VAR source_fqn = :source_fqn;
SET VAR q = char(39);
SET VAR sql_stmt = CONCAT(
  'CREATE MATERIALIZED VIEW IF NOT EXISTS ', target_fqn,
  ' TBLPROPERTIES (', q, 'delta.columnMapping.mode', q, ' = ', q, 'name', q, ')',
  ' TRIGGER ON UPDATE',
  ' AS',
  ' select',
  '   cast(abc as TIMESTAMP) as `A B C`,',
  '   cast(xyz as TIMESTAMP) as `X Y Z`,',

  ' from ', source_fqn
);
EXECUTE IMMEDIATE sql_stmt

There must be a better way in Databricks?🤔

[-werners-]

Hm, the IDENTIFIER({{var}} || string) should work for create statements with DAB.
I also spent way too much time on AI giving me wrong answers (Jinja templating format on the first place).
Mind that there are no spaces in {{var}}.

BUT there are some limitations, f.e. in defining FKs f.e. (REFERENCES). That does not work (yet).
Dunno if this helps

[abohlin]

Came across this thread as I was facing the same exact issue as @malterializedvw and I want to comment my fix in case anyone else tears their hair out on this problem.

In my databricks.yml file I put in a gold_catalog and a silver_catalog variable and then passed those to the sql_task in a job.yml similar to how Pat showed.  The only difference is I use ${var.variable_name} call in the job.yml instead of doing a hard-coded redefine.

To solve the Exactly one CREATE [ LIVE TABLE | MATERIALIZED VIEW | STREAMING TABLE ] statement expected, but 0 found issue, I had to use the USE CATALOG IDENTIFIER({{gold_catalog}}); and then you can put the create statement without using a variable or running into the quote injection issue that {{var}} calls cause.

I ran into this [UNBOUND_SQL_PARAMETER] error when I was using FROM IDENTIFIER(:silver_catalog || '.schema_example.table_example') and got around that by using {{silver_catalog}} instead of :silver_catalog.

Just seems odd that some ways of calling variables seem to persist in MV's while others don't. I do wonder why {{var}} is fine, but :var causes it to choke. I would expect them both to resolve the same way, but it might be that {{var}} gets subbed out before the MV SQL gets stored, but :var doesn't so databricks is trying to store SQL with a variable that isn't defined anymore.