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

Options
info-rchitect
info-rchitect Registered Posts: 169 ✭✭✭✭✭✭
edited July 16 in Using Dataiku

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:

dataiku_dataset_connection.jpg

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

Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,727 Neuron
    Options

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

  • info-rchitect
    info-rchitect Registered Posts: 169 ✭✭✭✭✭✭
    Options

    @Turribeach

    Here is what I mean by overriding the project variables:

    dataiku_python_recipe_override_vars.jpg

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,727 Neuron
    edited July 17
    Options

    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
    info-rchitect Registered Posts: 169 ✭✭✭✭✭✭
    Options

    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?

  • June
    June Dataiku DSS Core Designer, Registered Posts: 19 ✭✭✭✭
    Options

    @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
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,727 Neuron
    Options

    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

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,727 Neuron
    Options

    See above link to the documentation.

  • info-rchitect
    info-rchitect Registered Posts: 169 ✭✭✭✭✭✭
    Options

    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.

Setup Info
    Tags
      Help me…