### Apache Airflow Provider(s)

snowflake

### Versions of Apache Airflow Providers

This started with apache-airflow-providers-snowflake==4.0.0 and is still an issue with 4.0.4


### Apache Airflow version

2.5.1

### Operating System

Debian GNU/Linux 11 (bullseye)

### Deployment

Astronomer

### Deployment details

This is affecting both local and hosted deployments

### What happened

We are testing out several updated packages, and one thing that broke was the SnowflakeOperator when it was executing a stored procedure. The specific error points to autocommit being set to False:
`Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.`



Whereas this used to work in version 3.2.0:

```
    copy_data_snowflake = SnowflakeOperator(
        task_id=f'copy_{table_name}_snowflake',
        sql=query,
    )
```

In order for it to work now, we have to specify autocommit=True:
```
    copy_data_snowflake = SnowflakeOperator(
        task_id=f'copy_{table_name}_snowflake',
        sql=query,
        autocommit=True,
    )
```

[The code](https://github.com/apache/airflow/blob/599c587e26d5e0b8fa0a0967f3dc4fa92d257ed0/airflow/providers/snowflake/operators/snowflake.py#L45) still indicates that the default is True, but I believe [this commit](https://github.com/apache/airflow/commit/ecd4d6654ff8e0da4a7b8f29fd23c37c9c219076#diff-e9f45fcabfaa0f3ed0c604e3bf2215fed1c9d3746e9c684b89717f9cd75f1754L98) broke it.


### What you think should happen instead

The default for autocommit should revert to the previous behavior, matching the documentation.

### How to reproduce

In Snowflake:
```
CREATE OR REPLACE TABLE PUBLIC.FOO (BAR VARCHAR);
CREATE OR REPLACE PROCEDURE PUBLIC.FOO()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
    INSERT INTO PUBLIC.FOO VALUES('bar');
$$
;
```

In Airflow, this fails:
```
    copy_data_snowflake = SnowflakeOperator(
        task_id='call_foo',
        sql="call public.foo()",
    )
```

But this succeeds:
```
    copy_data_snowflake = SnowflakeOperator(
        task_id='call_foo',
        sql="call public.foo()",
        autocommit=True,
    )
```

### Anything else

It looks like this may be an issue with stored procedures specifically. If I instead do this:

```
    copy_data_snowflake = SnowflakeOperator(
        task_id='call_foo',
        sql="INSERT INTO PUBLIC.FOO VALUES('bar');",
    )
```

The logs show that although autocommit is confusingly set to False, a `COMMIT` statement is executed:
```
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [ALTER SESSION SET autocommit=False]
[2023-03-09, 18:43:09 CST] {cursor.py:740} INFO - query execution done
[2023-03-09, 18:43:09 CST] {cursor.py:878} INFO - Number of results in first chunk: 1
[2023-03-09, 18:43:09 CST] {sql.py:375} INFO - Running statement: INSERT INTO PUBLIC.FOO VALUES('bar');, parameters: None
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [INSERT INTO PUBLIC.FOO VALUES('bar');]
[2023-03-09, 18:43:09 CST] {cursor.py:740} INFO - query execution done
[2023-03-09, 18:43:09 CST] {sql.py:384} INFO - Rows affected: 1
[2023-03-09, 18:43:09 CST] {snowflake.py:380} INFO - Rows affected: 1
[2023-03-09, 18:43:09 CST] {snowflake.py:381} INFO - Snowflake query id: 01aad76b-0606-feb5-0000-26b511d0ba02
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [COMMIT]
```

### Are you willing to submit PR?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

"""
    Performs a check against Snowflake. The ``SnowflakeCheckOperator`` expects
    a sql query that will return a single row. Each value on that
    first row is evaluated using python ``bool`` casting. If any of the
    values return ``False`` the check is failed and errors out.
    Note that Python bool casting evals the following as ``False``:
    * ``False``
    * ``0``
    * Empty string (``""``)
    * Empty list (``[]``)
    * Empty dictionary or set (``{}``)
    Given a query like ``SELECT COUNT(*) FROM foo``, it will fail only if
    the count ``== 0``. You can craft much more complex query that could,
    for instance, check that the table has the same number of rows as
    the source table upstream, or that the count of today's partition is
    greater than yesterday's partition, or that a set of metrics are less
    than 3 standard deviation for the 7 day average.
    This operator can be used as a data quality check in your pipeline, and
    depending on where you put it in your DAG, you have the choice to
    stop the critical path, preventing from
    publishing dubious data, or on the side and receive email alerts
    without stopping the progress of the DAG.