SingleDB: UDTF that references objects from other schema of same DB encounters failure

[sindhuthirugnanam]

While deploying UDTF in Single-DB mode, functions that references objects from other schema (not fully qualified with db name) of same database is failed during deployment (apply) .

Eg: I want to deploy a function in DBO schema that references object from RAW schema (see sample UDTF below). I don't want to hard code the database name in the function body as it takes away the reusability of code in various environments like DEV, QA, STAGE, etc.. Is there any way you can include USE DATABASE <db_name> before deploying functions or part of Snowflake connection engine itself (by repurposing --target-db option)

Error:
Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name

SingleDB Command:

snowddl-singledb --show-sql --exclude-object-types SCHEMA --apply-replace-table --apply-unsafe --target-db TEST_DB apply
UDTF Definition:

CREATE OR REPLACE FUNCTION "TEST_DB"."DBO"."TEST_FUNCTION" ("AS_OF_DATE" DATE)
RETURNS TABLE (
      "SALES_DATE" DATE
    , "FIRST_NAME" VARCHAR(16777216)
    , "LAST_NAME" VARCHAR(16777216)
    , "AVG_SALES" FLOAT
)
LANGUAGE SQL
AS 
'SELECT  sales_date,
              first_name,
              last_name,
             AVG(sales)
FROM  RAW.SALES
WHERE SALES_DATE = as_of_date
GROUP BY sales_date, first_name, last_name'

[littleK0i]

It is a bit odd that Snowflake does not support it natively. I'll take a look in the next few days.

As a quick fix on the current version, you may define a placeholder and use it in function body.

For example:

body: |-
  SELECT  sales_date,
          first_name,
          last_name,
          AVG(sales)
  FROM  ${{ database }}.RAW.SALES
  WHERE SALES_DATE = as_of_date
  GROUP BY sales_date, first_name, last_name

And set placeholder using extra CLI option:

--placeholder-values="{'database': 'QA'}"

Docs: https://docs.snowddl.com/basic/yaml-placeholders

[littleK0i]

I'll look into it next week when I get back from travel.

Explicit USE DATABASE ... or USE SCHEMA ... commands are definitely not an option due to parallel execution. However, it should be possible to extend placeholders system to add $context.<key> and $env.<key>. With $context holding current database and current schema for specific object which is currently being resolved.

[littleK0i]

@sindhuthirugnanam , please check version 0.11.0.

I've added automatic placeholder called ${{ target_db }}, which holds identifier of target database specified in --target-db CLI option.

Example usage:

body: |-
  SELECT  sales_date,
          first_name,
          last_name,
          AVG(sales)
  FROM  ${{ target_db }}.RAW.SALES
  WHERE SALES_DATE = as_of_date
  GROUP BY sales_date, first_name, last_name

Also, I did some extra tests. It seems, only SQL FUNCTIONs are affected by this problem. VIEWs support short syntax for identifiers with only schema name and object name.