Snowflake destination issue: fail to execute multiple sql queries

[hengpor]

dlt version

0.4.10

Describe the problem

There bug in snowflake destination

dlt/dlt/destinations/impl/snowflake/sql_client.py

Line 105 in 1e0db2f

curr.execute(query, db_args, num_statements=0)

num_statements=0 is hardcoded into code when query can be more than one sql queries

Expected behavior

num_statements should the number queries

Steps to reproduce

Running pipeline using snowflake-connector-python 2.9.0

Operating system

macOS

Runtime environment

Local

Python version

3.9

dlt data source

No response

dlt destination

Snowflake

Other deployment details

No response

Additional information

No response

[rudolfix]

@hengpor do you have a stack trace with this bug?

num_statements=0 is a special value for multi-statement query:
https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/cursor.py#L901

we have plenty of tests that execute multiple queries, maybe you have problem with a specific snowflake python client version?

[hengpor]

I am using snowflake connector 2.9.0

[rudolfix]

@hengpor are you able to switch to "3.5.0" or newer? there are quite significant improvements from snowflake side ie. they removed pyarrow as dependency.

2.9.0 is from 2022. if you have hard requirements to use it ping me I'll try to run our tests on it. it is a first version that introduced the support for multi queries and I expect some bugs in it