Support for SQLAlchemy 2.X.X to be installed alongside dbt-databricks

[ewengillies]

Describe the feature

We've been having trouble making pip happy with SQLAlchemy 2.X.X installed in the same python environment as dbt-databricks. This is because dbt-databricks pins to databricks-sql-python < 3.0.0 which implicitly pins to SQLAlchemy < 2.0.0

I see great progress has been made on the SQLAlchemy 2.X.X front in the dependent databricks-sql-python library: https://github.com/databricks/databricks-sql-python/releases/tag/v3.0.0. It is pinned to above 2.0.0 now and optional, which are both excellent!

Describe alternatives you've considered

In my experience, SQLAlchemy was always optional in nature to databricks-sql-python (or databricks-sql-connector as it used to be called).

You can get the needed state through several pip installs like this:

pip install dbt-databricks==1.7.3
pip install --no-warn-conflicts 'SQLAlchemy>=2.0.0`

This logics is repeated over a few of our CI/CD environments, its not great.

Additional context

None.

Who will this benefit?

Anyone like me who needs dbt-databricks and sqlalchemy > 2.0.0 to co-exist in a python environment.

Are you interested in contributing this feature?

No, just curious as to the roadmap for loosening the databricks-sql-python < 3.0.0 pin in dbt-databricks.

@susodapop any thoughts on this? Thanks! --Ewen

[benc-db]

This is top of mind for me, just need to finish the feature push on MV/ST. We will update to being compatible with databricks-sql-python soon, but its a breaking change, so requires a little bit of effort. If you want to speed up the process, please feel free to send out a PR that accomplishes the upgrade. The breaking change bit is around query parameters, which I believe comes up only with seeds.

[benc-db]

We had to roll back to 2.9.3 due to retry behavior regression. If you want, you can try 1.8.0b1 (where we upgrade to 3.1.0 for databricks-sql-connector).

[ewengillies]

Hi @benc-db, thanks for the update here. I've been watching the releases and saw the move back to 2.9.3. Good to know 1.8.0 is right around the corner! Thanks for the update here.