topic Re: Migrating to Unity Catalog: Read-Only Connections to SQL Server and Snowflake in Data Engineering

Migrating to Unity Catalog: Read-Only Connections to SQL Server and Snowflake

Direo — Fri, 23 Aug 2024 09:00:43 GMT

We are in the process of migrating to Unity Catalog, establishing connections to SQL Server and Snowflake, and creating foreign catalogs that mirror your SQL Server and Snowflake databases. This allows us to leverage Unity Catalog’s query syntax and data governance tools to manage Databricks user access.

However, these features are read-only. This raises an important question: What solutions are people using when there’s a need to write back to SQL Server or Snowflake?

So far, we’ve used JDBC for writing, but this approach lacks the governance provided by Unity Catalog for reads and still requires using Key Vaults for credential management. And if you have different user groups with different access levels, that creates issues as you need a keyvault for each access group or in some casess even single user.

Is there a better way to write to SQL Server from Databricks that offers the same level of governance and control as Unity Catalog provides for reads or writes to data lakes?

Do you know whether this feature is in the Databricks plans?

We’d love to hear your thoughts!

Re: Migrating to Unity Catalog: Read-Only Connections to SQL Server and Snowflake

brian999 — Fri, 20 Sep 2024 15:53:01 GMT

We just use SQLAlchemy for connection to Snowflake, which, you're right, does not enable databricks governance.

Re: Migrating to Unity Catalog: Read-Only Connections to SQL Server and Snowflake

goldenmountain — Tue, 03 Dec 2024 14:58:33 GMT

I’m also trying to figure out if this is a limitation in Unity Catalog. I recently used a JDBC URL to write data to an Amazon Aurora PostgreSQL database, but noticed that no entries appeared in the `system.access.table_lineage` table. Has anyone else encountered this issue? If so, how are you handling lineage tracking for JDBC-based writes?