Inserting data into stages of tables in Snowflake having mixedcase naming ,edge case should be considered for better user experience

[010Soham]

Flow

id: snowflake-upload-demo
namespace: qa.test


tasks:
  - id: stage-csv
    type: io.kestra.plugin.scripts.shell.Script
    taskRunner:
      type: io.kestra.plugin.core.runner.Process
    outputFiles:
      - data.csv
    script: |
      cp /tmp/data.csv data.csv


  - id: upload-stub
    type: io.kestra.plugin.jdbc.snowflake.Upload
    url:       jdbc:snowflake://{{ secret("SNOWFLAKE_ACCOUNT_P") }}.snowflakecomputing.com
    username:  "{{ secret('SNOWFLAKE_USER_P') }}"
    password:  "{{ secret('SNOWFLAKE_PASSWORD_P') }}"
    role:      "{{ secret('SNOWFLAKE_ROLE_P') }}"
    database:  "{{ secret('SNOWFLAKE_DATABASE_P') }}"
    schema:    "{{ secret('SNOWFLAKE_SCHEMA') }}"
    warehouse: "{{ secret('SNOWFLAKE_WAREHOUSE') }}"


    from:      "{{ outputs['stage-csv'].outputFiles['data.csv'] }}"
    fileName:  "data.csv"
    prefix:    "raw"
    stageName: '@{{ secret("SNOWFLAKE_DATABASE_P") }}."{{ secret("SNOWFLAKE_SCHEMA") }}".%"{{ secret("SNOWFLAKE_TABLE") }}"'

Actual Behaviour

assume the below in snowflake

database = DEMO_DB
schema =Sohampp95MySchema
table = My_table

so the above flow would deposit data.csv to a implicit stage named row/My_table in Sohampp95MySchema in DEMO_DB

However the schema created has a mixedcase naming

thus If a user forgets to enclose {{ secret("SNOWFLAKE_SCHEMA") }} and secret("SNOWFLAKE_TABLE") }} within double quotes and goes with the below value in the stagename field

stageName: '@{{ secret("SNOWFLAKE_DATABASE_P") }}.{{ secret("SNOWFLAKE_SCHEMA") }}.%{{ secret("SNOWFLAKE_TABLE") }}'

both the schema and table name will be uppercased when being accessed in snowflake leading to a situation as below in which snowflake wont be able to recognize the schema and table in which the upload should take place leading to a failure as shown below

So in order to preserve the mixedcase naming ,the fields in flow should be enclcosed in double quotes

Hence taking care of this edge case

a entry in the docs of the plugin for the stagename field if documented would provide better user experience

docs link -> https://kestra.io/plugins/plugin-jdbc-snowflake/io.kestra.plugin.jdbc.snowflake.upload#properties_stageName-body

StageName field Definition

How to reproduce

In your kestra container create a data.csv in /tmp directory
create a database in snowflake
eg - DEMO_DB
In snowflake create a schema in this database having mixedcase naming
eg - Sohampp95MySchema
In snowflake create a table in this schema having mixed case naming
eg - My_table
provide snowflake credentials in the flow
run the flow
you will see data.csv has been upload to stage
now again run the flow but witht below stagename value in which schema and table name are not inside double quotes
stageName: '@{{ secret("SNOWFLAKE_DATABASE_P") }}.{{ secret("SNOWFLAKE_SCHEMA") }}.%{{ secret("SNOWFLAKE_TABLE") }}'
you will get a upload failed error

Environment

• Kestra Version: 0.23.4
• Operating System: Docker