Workday Data Connect catalog federation

Beta

This feature is in Beta. To use it, a workspace admin must turn on Workday Data Connect Connector from the Previews page. See Manage Databricks previews.

Use Workday Data Connect catalog federation to read Workday Data Connect tables directly from cloud storage and add them to Unity Catalog as foreign tables. Queries run entirely on Databricks compute, so data teams can discover, govern, and query these tables from Databricks without building ETL pipelines. Data access is read-only.

Databricks authenticates to the Workday Data Connect catalog as a Workday Integration System User (ISU) that you register as a principal, using OAuth with a private key.

Workday calls this product Workday Data Lake. Partner documentation, including this page, calls it Workday Data Connect.

To learn more about catalog federation, see What is catalog federation?.

note

This connector doesn't ingest or copy data into Databricks, and it isn't related to the Lakeflow Connect ingestion connectors for Workday. To ingest Workday data into Databricks instead, see Workday Reports connector or Workday HCM connector.

Before you begin

Review the following requirements before you set up Workday Data Connect catalog federation.

Workspace requirements:

• The workspace must be enabled for Unity Catalog. See Get started with Unity Catalog.

Compute requirements:

• Databricks compute must use Databricks Runtime 19.8 or above and standard access mode. Dedicated access mode isn't supported.

Networking requirements:

• Network connectivity from Databricks to your Workday Data Connect catalog endpoint at https://<workday-host>/api/catalog. You don't need to allowlist Databricks IP addresses in Workday.

Permissions required:

• To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
• To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Workday requirements:

• Workday Data Connect enabled for your Workday tenant, with the tables that you want to query shared through its Iceberg REST catalog. See Get Started with Workday Data Lake.
• A Workday Integration System User (ISU) registered as a principal on the Workday Data Connect catalog, a principal role for that ISU that grants read access to the shared tables, and an RSA key pair for that principal. To register the API client, see Register API Client for Data Lake (JWT Bearer Grant).

Step 1: Create a connection

A connection specifies the endpoint and credentials that Databricks uses to communicate with the Workday Data Connect catalog.

To create a connection, use Catalog Explorer or the CREATE CONNECTION SQL command:

Catalog Explorer

To create the connection:

1. In your Databricks workspace, click Catalog.
2. At the top of the Catalog pane, click the plus icon and select Create a connection from the menu.
3. On the Connection basics page, enter a Connection name.
4. Select a Connection type of Workday Data Connect.
5. Click Next.
6. On the Connection details page, enter the following values:
   • Host: The hostname of your Workday Data Connect endpoint, for example mycompany.myworkday.com.
   • Tenant: Your Workday tenant.
   • Client ID: Client ID of the Workday API client.
   • Private key: An RSA private key in PEM format.
   • Principal name: The name of the ISU that you registered as the catalog principal.
   • Principal role: The Workday principal role that scopes read access to the shared tables. The default is ALL. To scope access down, set a specific role, for example all-reads-role.

SQL

Run the following command in a notebook or the Databricks SQL query editor:

SQL

CREATE CONNECTION <connection-name> TYPE WORKDAY_DATA_CONNECT
OPTIONS (
  host '<workday-host>',
  tenant '<workday-tenant>',
  client_id '<client-id>',
  private_key secret('<secret-scope>','<secret-key>'),
  principal_name '<isu-name>',
  principal_role '<principal-role>'
);

• host: The hostname of your Workday Data Connect endpoint, for example mycompany.myworkday.com.
• tenant: Your Workday tenant.
• client_id: Client ID of the Workday API client.
• private_key: An RSA private key in PEM format. Databricks recommends that you store it as a secret.
• principal_name: The name of the ISU that you registered as the catalog principal.
• principal_role: The Workday principal role that scopes read access to the shared tables. The default is ALL. To scope access down, set a specific role, for example all-reads-role.

Databricks recommends that you use secrets instead of plaintext strings for sensitive values like the private key. For information about creating secrets, see Secret management.

Step 2: Create a foreign catalog

A foreign catalog mirrors the Workday Data Connect catalog in Unity Catalog so that you can use Unity Catalog to manage access to the Workday tables and query them from Databricks compute.

To create a foreign catalog, you can use Catalog Explorer or the CREATE FOREIGN CATALOG SQL command in a notebook or the Databricks SQL query editor.

Catalog Explorer

1. In your Databricks workspace, click Catalog.
2. At the top of the Catalog pane, click the plus icon and select Create a catalog from the menu.
3. On the Create a new catalog dialog, enter a name for the catalog.
4. Select a Type of Foreign.
5. Select the Connection you created in Step 1.
6. For Storage location, enter the cloud storage path where Databricks stores metadata for the federated tables.
7. Click Create.

SQL

Run the following command in a notebook or the Databricks SQL query editor. Items in brackets are optional.

SQL

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name>
USING CONNECTION <connection-name>
OPTIONS (storage_root '<storage-root-path>');

• storage_root: Required. An external location path that Databricks can write to. Databricks uses this location to store metadata for the federated tables. Workday table data isn't copied to this location.

The foreign catalog always mirrors the entire Workday Data Connect catalog that your credentials grant access to. To scope down what's visible, set a principal_role that grants read access to only the tables you want when you create the connection.

Step 3: Grant permissions and query the catalog

After you create the foreign catalog, users need the appropriate Unity Catalog permissions to access the federated tables:

• All users need USE CATALOG and USE SCHEMA permissions on the catalog and schema respectively.
• To read from a federated table, users need the SELECT permission.

For more information about Unity Catalog privileges and how to grant them, see Manage privileges in Unity Catalog.

For example, run the following commands in a notebook or the Databricks SQL query editor:

SQL

GRANT USE CATALOG ON CATALOG <catalog-name> TO `<principal>`;
GRANT USE SCHEMA ON SCHEMA <catalog-name>.<schema-name> TO `<principal>`;
GRANT SELECT ON TABLE <catalog-name>.<schema-name>.<table-name> TO `<principal>`;

Users with these permissions can then query the federated tables using three-level namespace notation:

SQL

SELECT * FROM <catalog-name>.<schema-name>.<table-name>;

Namespace mapping

Unity Catalog mirrors the Workday Data Connect object hierarchy as follows:

Workday Data Connect object	Unity Catalog object
Catalog	Foreign catalog
Namespace	Schema
Table	Foreign table

Workday Data Connect object	Unity Catalog object
Catalog	Foreign catalog
Namespace	Schema
Table	Foreign table

Workday Data Connect exposes flat, three-level names that map one-to-one to the Unity Catalog catalog.schema.table hierarchy. There's no additional namespace nesting.

Limitations

Workday Data Connect catalog federation has the following limitations:

• The connector can access only Iceberg tables shared through the Workday Data Connect catalog.
• Schema and table names follow standard Unity Catalog naming limitations. Databricks does not support names that contain a period (.), space ( ), or forward slash (/). See Securable object naming requirements.