Snowflake connection, boolean value of extra__snowflake__insecure_mode interpreted as string

[ThomasGlas-Hopmann]

Apache Airflow version

2.2.3 (latest released)

What happened

Error thrown when using SnowflakeOperator with a Snowflake Connection.

After creating a Snowflake Connection, the "Extra" field was automatically filled with a dictionary containing the values entered in the other input fields. Note: the value for the key "extra__snowflake__insecure_mode" is a boolean.

A task using SnowflakeOperator fails, throwing following error:

[2022-01-25, 14:39:54 UTC] {taskinstance.py:1700} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1511, in _execute_task
    result = execute_callable(context=context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py", line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 293, in run
    with closing(self.get_conn()) as conn:
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 236, in get_conn
    conn_config = self._get_conn_params()
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py", line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:54 UTC] {taskinstance.py:1267} INFO - Marking task as FAILED. dag_id=test, task_id=snowflake_task, execution_date=20220123T000000, start_date=20220125T133954, end_date=20220125T133954
[2022-01-25, 14:39:55 UTC] {standard_task_runner.py:89} ERROR - Failed to execute job 4 for task snowflake_task
Traceback (most recent call last):
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/task/task_runner/standard_task_runner.py", line 85, in _start_by_fork
    args.func(args, dag=self.dag)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/cli_parser.py", line 48, in command
    return func(*args, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/cli.py", line 92, in wrapper
    return f(*args, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 298, in task_run
    _run_task_by_selected_method(args, dag, ti)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 107, in _run_task_by_selected_method
    _run_raw_task(args, ti)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/cli/commands/task_command.py", line 180, in _run_raw_task
    ti._run_raw_task(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/session.py", line 70, in wrapper
    return func(*args, session=session, **kwargs)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1329, in _run_raw_task
    self._execute_task_with_callbacks(context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1455, in _execute_task_with_callbacks
    result = self._execute_task(context, self.task)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/models/taskinstance.py", line 1511, in _execute_task
    result = execute_callable(context=context)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/operators/snowflake.py", line 129, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 293, in run
    with closing(self.get_conn()) as conn:
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 236, in get_conn
    conn_config = self._get_conn_params()
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 170, in _get_conn_params
    insecure_mode = to_boolean(
  File "/home/test_airflow2/.local/lib/python3.8/site-packages/airflow/utils/strings.py", line 30, in to_boolean
    return False if astring is None else astring.lower() in ['true', 't', 'y', 'yes', '1']
AttributeError: 'bool' object has no attribute 'lower'
[2022-01-25, 14:39:55 UTC] {local_task_job.py:154} INFO - Task exited with return code 1
[2022-01-25, 14:39:55 UTC] {local_task_job.py:264} INFO - 0 downstream tasks scheduled from follow-on schedule check

This error seems to arise because the boolean value of extra__snowflake__insecure_mode is interpreted as a string.
Manually modifying the boolean extra__snowflake__insecure_mode value to be string in the "Extra" dictionary solves this problem:
false->"false"

What you expected to happen

Be able to create a usable Snowflake Connection, by filling in fields other than "Extra". The "Extra" field should be automatically filled with a correct/usable connection dictionary.
I can then use this Snowflake Connection for SnowflakeOperators in DAGs.

How to reproduce

Create a new Connection of type Snowflake, set an arbitrary Connection ID. The rest of the fields can be left empty (doesn't affect error).

Create a DAG with a SnowflakeOperator task, which uses the created Snowflake Connection:

from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from airflow.utils.dates import days_ago

with DAG('test', start_date=days_ago(2)) as dag:
    
    snowflake_task = SnowflakeOperator(task_id='snowflake_task',
                                            sql='select 1;',
                                            snowflake_conn_id='snowflake_conn')

Execute the DAG, the task will fail and throw the above error.

Operating System

Ubuntu 20.04.2 LTS

Versions of Apache Airflow Providers

apache-airflow-providers-snowflake==2.4.0

Deployment

Virtualenv installation

Deployment details

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template!

[dazedavid]

We have had similar experience with the issue @ThomasGlas-Hopmann. We switched back to previous version for a fix 2.3.1.

Here is our screenshot which replicates the same issue..
Operating System

Debian 11 (bullseye)

Versions of Apache Airflow Providers

apache-airflow-providers-snowflake==2.4.0

Deployment

Azure Kubernetes Service (AKS)

Deployment details

Deployed with bitnami charts with requirements.txt

Anything else

No response

[mik-laj]

Thanks for reporting the issue. I will take care of it urgently, but we will have to wait a while for the fix to be released.