### 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)

#
#
#
from __future__ import annotations
import warnings
from typing import Any, Iterable, Mapping, Sequence, SupportsAbs
from airflow.providers.common.sql.operators.sql import (
    SQLCheckOperator,
    SQLExecuteQueryOperator,
    SQLIntervalCheckOperator,
    SQLValueCheckOperator,
)
class SnowflakeOperator(SQLExecuteQueryOperator):