ensure_db function SSH connectivity issues

[ramkumar233]

Tried running the 'to_snowflake' module which in turn invokes 'ensure_db' module to check object existence. SQL Alchemy create engine doesn't execute successfully when we use SSH Keypair authentication for login to Snowflake. Is there a space for enhancement to add SSH keypair authentication than specifying the direct password for Snowflake account? Error is returned as a SQL Alchemy Database error which says 'Failed to connect to DB' Incorrect username or password was specified.

Read snowflake works as it doesn't use the create_engine specific module

[jrbourbeau]

Thanks for raising an issue @ramkumar233! The use of pd_writer + SQL Alchemy engine was so we could both conveniently create the table if it doesn't exist and make sure the table has the correct DataFrame schema. That said, we should be able to accomplish the same thing using snowflake.connector.connect + write_pandas like is done here

https://github.com/coiled/dask-snowflake/blob/e7977e5a7c73361ab705b9f14eb4a74cb0307a30/dask_snowflake/core.py#L21-L44

Just to clarify, would moving to snowflake.connector.connect + write_pandas, instead of the SQL Alchemy engine approach, help resolve the SSH Keypair authentication issue you're running into?

[sfc-gh-cginther]

@jrbourbeau Thanks James - I believe this will resolve the issue as I have no issues using read_snowflake with key pair auth as read_snowflake only uses Snowflake native connector.