Dynamic Snowflake schema not working with recipe variables that override project variables

[info-rchitect]

Hi,

We have a Snowflake connection that allows users to setup project variables to indicate the details:

{
  "snowflake_db": "MYDB",
  "snowflake_role": "MYUSER",
  "snowflake_wh": "MYWH",
  "snowflake_schema": "MYSCHEMA"
}

When we create a dataset in Dataiku, it looks like this:

This works great, unless I override the Snowflake variables in the recipe as so:

{
   "snowflake_schema": "OVERRIDE_SCHMEA"
}

The schema, in this case, will not use the python recipe variable value, it will always use the project variable.  IMO, this is a bug and it should use the recipe variable value instead.  Is there a workaround besides hardcoding the schema value?

thx

---

Operating system used: Windows 10

[Turribeach]

Hi, when you say "override the Snowflake variables in the recipe" what do you actually mean? Can you share the code please?

[info-rchitect]

@Turribeach 

Here is what I mean by overriding the project variables:

[Turribeach]

Well I am not convinced this is a bug. Variable overrides only work one way, it just not working in the way you want.

I think your solution is no to override at both levels: project and recipe. If you do still want to override at both levels then you could override the project variables from your recipe code:

import dataiku
client = dataiku.api_client()

project = client.get_default_project()
project_variables = project.get_variables()
project_variables['standard']['your_var'] = "new var value"
project.set_variables(project_variables)

Obviously this override would be permanent so you may need to revert the variables values after your recipe finishes writting the output.

[info-rchitect]

Whether this is a bug or not is subjective but typically scoped overrides like this should apply consistently within the ecosystem.

Having to write and then reset project variables to get the desired behavior indicates to me this is a bug, otherwise why bother having recipe level variables at all?

[Turribeach]

Looks like you might be right, raise a Support Ticket:

https://doc.dataiku.com/dss/latest/connecting/sql/snowflake.html

3. Override the variables where needed

This can be recursively done, overriding parameters in the order shown here (where left overrides right): Recipe > Scenario > Project > User > Global

[June]

@Turribeach I cannot find any documentation on recipe variables. What is the desired behavior?  After reviewing @info-rchitect 's flow, it appears that the recipe variable is not working at all. 

[Turribeach]

See above link to the documentation.

[info-rchitect]

So the recipe variable override is working in the recipe and writing the output dataset to the correct place in Snowflake.  The issue is the output dataset UI, which is not part of the recipe itself, uses the project scope for the schema variable.

IMO, the dataset UI should hardcode the schema value and not use the variable syntax, if it is not going to evaluate the scope correctly.

The current behavior results in a broken UI, which cannot be a desired result.