Databricks Integration

PlatformManager can load data from Databricks into a Pricefx partition and export Pricefx data to Databricks. An Automated Data Upload reads a table from Unity Catalog and loads its rows into a Pricefx entity. The upload runs at regular intervals, or when you click Run Now. A Data Download to Databricks writes the rows of a Pricefx entity into an existing Unity Catalog table. A manual or an automated Data Download can write to Databricks. Databricks stays the system of record.

This page explains how the integration works, what it needs, and what Pricefx reads and stores. For the setup steps, see Configure the Integration.

How Upload from Databricks Works

  1. A Databricks connection on the partition stores the workspace host, the SQL warehouse ID, and the OAuth credentials of a Databricks service principal.

  2. An Automated Data Upload uses the connection. It names one table and maps the table columns to fields of a Pricefx entity.

  3. At each run, PlatformManager sends the job to Integration As A Service (IaaS). An IntegrationManager job reads the table through the SQL warehouse and loads the rows into the entity.

Each Databricks upload has a load mode. The load mode sets how each run reads the table:

Load mode

What each run reads

Full snapshot

The whole table. At the start of the run, the job reads the latest version of the table and uses that version for the whole run. Changes written to the table during the run appear in the next run.

Delta — timestamp watermark

Only the rows newer than the last successful run. Pricefx does not remove rows deleted in Databricks.

You select the load mode when you create the upload. For the steps, see How to Create Automated Data Upload from Databricks.

How the Timestamp Watermark Load Works

A Delta — timestamp watermark upload uses a watermark. The watermark is the newest watermark column value up to which the upload has read the table. The watermark column is a timestamp column of the Databricks table.

At the start of a timestamp watermark run, the job finds the newest value in the watermark column. The run reads rows up to that value. Rows with a newer value appear in the next run.

No run loads a row where the watermark column is empty, not even the first run. To load every row, fill the column or set the NOT NULL constraint on it in Databricks.

What a timestamp watermark run reads, and what happens to the watermark:

Run condition

Rows read

Watermark result

First run of the upload

Every row that has a value in the watermark column, up to the newest value.

The watermark moves to the newest value.

Later run, newer rows exist

The rows newer than the stored watermark, and the 5 minutes before the stored watermark. This lookback loads rows that Databricks committed late with a slightly older timestamp.

The watermark moves to the newest value.

Later run, no newer rows

Nothing. The run does not use the lookback. A late row waits for the next run that finds a newer row.

The watermark stays where it was.

Failed run

Not applicable.

The watermark stays where it was. The next run reads the same rows again.

Run for Manual Price List items, price items, or users, with a failed batch

The window of the run.

The run fails. The watermark stays where it was, and the next run reads the whole window again.

Run finishes, but Pricefx rejects some rows

The window of the run.

The watermark advances. Pricefx reads a rejected row again only when its watermark column value is later than the stored watermark. A run that finds newer rows also reads rejected rows within its lookback again.

History Of Runs shows the Window and Failed Records of each run. For details, see Running and Monitoring in How to Create Automated Data Upload from Databricks.

How Export to Databricks Works

  1. A Data Download to Databricks uses a Databricks connection of the partition. The connection needs a Staging volume, which is a Unity Catalog volume.

  2. The Data Download names an existing table and maps fields of a Pricefx entity to the table columns. File options are not available.

  3. PlatformManager sends each run to IaaS, even when the account does not use IaaS for other Data Downloads. An IntegrationManager job stages the data in the staging volume, then loads it into the table.

Each Data Download to Databricks has a write mode:

Write mode

Effect of each run

Overwrite

Replaces the rows of the table with the exported rows. A run without rows to export writes nothing. Incremental export is not available.

Append

Inserts the exported rows. The existing rows stay. Append can insert the same rows twice: when Pricefx repeats a write because it did not receive the response, or when an incremental run fails after it writes. Where duplicates matter, use Merge. Incremental export is available.

Merge

Updates each row with the same merge key values and inserts the other rows. A repeated run does not duplicate rows. Needs a Delta Lake table and at least one merge key. Incremental export is available.

Before each run, PlatformManager reads the connection and the current table definition again. It checks the saved mapping against the table, for example for a missing column. If this check finds an error, the run fails before Pricefx sends any data. The run does not read the Pricefx field types, so a type finding never stops a run. The run does not check the privileges of the service principal again, so a write can still fail in Databricks.

When you save a Data Download to Databricks, PlatformManager checks each mapped field type against the column type. The check runs only when PlatformManager knows the source type and the mapping does not transform the value.

Result

Example

Effect on saving

Compatible

Any Pricefx field into a STRING column.

None.

Lossy

Text into a number column.

A warning. You can still save.

Incompatible

An integer into a BOOLEAN, DATE, or TIMESTAMP column. Any field into a BINARY, ARRAY, MAP, STRUCT, VARIANT, or INTERVAL column.

An error. You cannot save.

Not verified

A value that a converter or a formula transforms, or a field with an unknown source type.

A warning that PlatformManager cannot check the type. You can still save.

For the steps, see How to Create Data Download to Databricks.

Supported Databricks Environments

Item

Supported

Cloud

Databricks on AWS, Microsoft Azure, or Google Cloud. Pricefx communicates with Databricks only through the Databricks APIs, so the cloud that hosts the workspace does not matter.

Catalog

Unity Catalog. PlatformManager lists catalogs, schemas, and tables through the Unity Catalog API.

Source object

A table. Pricefx cannot load a view. For Full snapshot, the table must be in Delta Lake format, the default table format in Databricks. The Delta — timestamp watermark load mode reads the table without a Delta Lake table version. Pricefx has tested it only on Delta Lake tables.

Target object of an export

An existing managed or external table. Pricefx does not create tables. Pricefx cannot write to a view or a foreign table. The Merge write mode needs a Delta Lake table.

Compute

A Databricks SQL warehouse.

IntegrationManager

IntegrationManager 8.3.0 or newer in the IaaS configuration of the account, for uploads from Databricks and for exports to Databricks.

Feature flag

The account needs the use-databricks-source feature flag. The same flag enables uploads from Databricks and Data Downloads to Databricks. With the flag off, a new Data Download does not offer Databricks Table, and runs of existing Data Downloads to Databricks fail.

Authentication and Permissions

Pricefx uses OAuth machine-to-machine (M2M) authentication with a Databricks service principal. Pricefx gets short-lived access tokens with the client ID and OAuth secret of the service principal, and renews them automatically.

The service principal needs access to the SQL warehouse and to the tables you want to load. For the required permissions, see How to Create Databricks Connection.

For an export, the service principal also needs privileges on the target table and on the staging volume.

What Pricefx Reads and Stores

Data

What Pricefx does with it

Catalog, schema, and table names

PlatformManager reads them from Unity Catalog when you browse in the upload wizard. It keeps the table lists in memory only.

First 50 rows of the table

PlatformManager shows them in the Preview of the upload wizard. It does not store them.

Rows of the selected table

The IntegrationManager job loads them into the selected entity on the partition. Pricefx stores them like any other partition data.

Connection settings

PlatformManager stores the workspace host, the warehouse ID, the client ID, and the default catalog and schema, and the staging volume.

OAuth client secret

PlatformManager stores it encrypted and never shows it again. It sends the secret to the IaaS job in encrypted form.

Upload settings

PlatformManager stores the selected table, its column names and types, the column mapping, and the schedule. It uses the stored columns to detect schema changes.

Watermark of a Delta — timestamp watermark upload

Pricefx stores the newest read value of the watermark column in the partition configuration, separately for each upload. Pricefx does not remove the watermark when you delete the upload.

Export target and privileges

When you save a Data Download to Databricks, PlatformManager reads the columns, type, and format of the target table from Unity Catalog. It also reads the effective privileges of the service principal. Before each run, it reads the table again.

Exported rows

The IaaS job writes the exported rows to one CSV file on its own disk and uploads the file to the staging volume. The job then writes the data into the target table. After each run, successful or failed, the job deletes the staged files. If the deletion fails, staged files can remain in the staging volume.

Data Download settings

PlatformManager stores the target table, the write mode, the merge keys, the mapping, and a snapshot of the table columns. Each run checks the current table, not the snapshot.

An upload does not read system tables or information_schema. An export reads the column metadata of the target table from system.information_schema.columns. Pricefx does not read lineage, audit logs, or billing data.

Network Access

Two Pricefx components connect to your Databricks workspace over HTTPS:

Component

Connects for

PlatformManager

Test connection, browsing in the upload wizard, the Preview, and a column check through Unity Catalog before each run. For exports, the table and privilege checks when you save a Data Download to Databricks, and the table check before each export run.

IaaS job (IntegrationManager)

Each upload or export run: the access token and the SQL statements. An upload run also downloads the query result. An export run also uploads the staged files to the staging volume through the Files API, and deletes them after the run.

For an upload, Databricks returns the query result of a run as files in cloud storage, with a short-lived link to each file. The IaaS job downloads the files from these links.

If your workspace uses IP access lists, allow the Pricefx addresses listed in Public NAT Gateway IP Addresses. PlatformManager connects from the Platform addresses.

Pricefx sends the user agent pricefx_platform/1.0 with its requests to the workspace.

Compute Usage

Pricefx runs SQL statements on your SQL warehouse. Databricks charges this compute to your account like any other query.

Action

Statements on the SQL warehouse

Test connection

SELECT 1.

Preview in the upload wizard

One query for the first 50 rows.

Each Full snapshot run

DESCRIBE HISTORY on the table, then one query that reads the table.

Each Delta — timestamp watermark run

One query for the newest value of the watermark column, then one query that reads the newer rows. When no row is newer, the run sends only the first query.

Each export run that has rows to export

A query for the column metadata of the target table, then one statement that writes the staged file into the table with the selected write mode. A Merge export can also query the table history for row counts. An export without rows to export skips these statements and leaves the table untouched.

Browsing catalogs, schemas, and tables does not use the SQL warehouse.

An upload run waits until a stopped SQL warehouse is running. Test connection and Preview do not wait.

An export run waits about 30 seconds for the SQL warehouse. If the warehouse is not running by then, the export run fails before it uploads any data. Start the warehouse first, or schedule the export for a time when the warehouse runs.

Limitations

  • The catalog, schema, and table names can contain only the letters A to Z, digits, and underscores. A name that starts with a digit does not work.

  • An upload run from Databricks fails if the query result is larger than 100 GiB, the Databricks limit for one result.

  • A Delta — timestamp watermark upload does not detect rows deleted in Databricks. Deleted source rows stay in Pricefx.

  • An export to Databricks reads the Pricefx data in pages. If rows change during a long export, the export can miss them or write them twice.

  • An export to Databricks writes a Pricefx date into a DATE column as a UTC point in time. The calendar day in Databricks can differ by time zone.

  • An export to Databricks stages one CSV file, which must be 5 GiB or smaller. A larger file fails before upload. Maximum Data Batch Size does not split the file. To make the file smaller, map fewer fields, or narrow the filter and export the parts with Append or Merge. With Overwrite, each run replaces the rows that the previous run wrote.

  • The privilege check of an export to Databricks does not resolve nested groups. Grant the privileges directly to the service principal or to one of its groups.

Other limitations apply when you create or run an upload, for example with filters or schema changes. See How to Create Automated Data Upload from Databricks.

Configure the Integration

  1. Create a Databricks connection on the partition. See How to Create Databricks Connection.

  2. Create an Automated Data Upload with Databricks selected in Data source. See How to Create Automated Data Upload from Databricks.

  3. Check each run in History Of Runs. See History of Runs.

To export data to Databricks, check the prerequisites in How to Create Data Download to Databricks. Then add a Staging volume to the Databricks connection, and create a Data Download with Databricks Table selected in Export to.

See Also