Max retries exceeded with url (NewConnectionError) - Upgrading from 2.7.0 to 2.9.3

The report names no repository file, test, or entry point. Start by reproducing a sufficiently large dbt run with Python 3.11.5 and databricks-sql-connector 2.9.3, then compare it with 2.7.0 and review the supplied connection and retry settings. Done means the connector no longer reaches the memory-allocation failure under the reported workload, with coverage for the regression.

Description

I encountered this very consistent error when using the dbt-databricks adapter when upgrading from dbt-databricks==1.5.5 to dbt-databricks==1.6.6. For a dbt run with sufficiently enough models, this error always occurs eventually - sometimes immediately and sometimes after many models have successfully built:

Runtime Error
  Runtime Error
    Database Error
      HTTPSConnectionPool(host='<HOST>', port=443): Max retries exceeded with url: /sql/1.0/warehouses/<URL> 
      (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x16d7d5390>: 
      Failed to establish a new connection: [Errno 12] Cannot allocate memory'))

I isolated the issue to be with this databricks-sql-python package when upgrading from databricks-sql-connector==2.7.0 to databricks-sql-connector==2.9.3.

The fact that it's a NewConnectionError isn't surprising but that it's failing because of "memory allocation" is very odd.

What Works

Using dbt-databricks==1.5.5 and databricks-sql-connector==2.7.0 works perfectly. Even dbt-databricks==1.6.6 then downgrading to databricks-sql-connector==2.7.0, forcing an incompatible package version, results in an environment that still works fine.

What Does Not Work

An environment with dbt-databricks==1.6.6 and databricks-sql-connector==2.9.3.

Other Troubleshooting

$ python --version
Python 3.11.5

$ dbt --version
Core:
  - installed: 1.6.6
  - latest:    1.6.6 - Up to date!

Plugins:
  - databricks: 1.6.6 - Up to date!
  - spark:      1.6.0 - Up to date!

I have attempted to adjust all of the profiles.yml parameters for retries and connections, with no success in getting 2.9.3 to work properly, ex:

      threads: 4
      retry_all: True
      connect_retries: 5
      connect_timeout: 180

I will add the full list of packages in my python environments below in the comments.

Perhaps these issues will be resolved in the 3.0.0 release, but I wanted to raise this issue just in case!