Query federation for Azure Synapse in Databricks SQL (Experimental)

Experimental

The configurations described in this article are Experimental. Experimental features are provided as-is and are not supported by Databricks through customer technical support. To get full query federation support, you should instead use Lakehouse Federation, which enables your Databricks users to take advantage of Unity Catalog syntax and data governance tools.

This article describes how to configure read-only query federation to Azure Synapse (SQL Data Warehouse) on Serverless and Pro SQL warehouses.

For information about configuring Synapse Azure Data Lake Storage Gen2 credentials, see Query data in Azure Synapse Analytics.

You configure connections to Synapse at the table level. You can use secrets to store and access text credentials without displaying them in plaintext. See the following example:

DROP TABLE IF EXISTS synapse_table;
CREATE TABLE synapse_table
USING sqldw
OPTIONS (
  dbtable '<table-name>',
  tempdir 'abfss://<your-container-name>@<your-storage-account-name>.dfs.core.windows.net/<your-directory-name>',
  url 'jdbc:sqlserver://<database-host-url>',
  user secret('synapse_creds', 'my_username'),
  password secret('synapse_creds', 'my_password'),
  forwardSparkAzureStorageCredentials 'true'
);