Use Case: Round Trip Between Databricks and Nexadata

The quickest way to prove a Databricks connection end to end is a round trip: read a table from Databricks into Nexadata, run it through a Workflow, write the result back to Databricks, and check that the totals match. This use case walks through that round trip with Beverage Sales, a sample planning fact table that Nexadata provides, but any table of your own works the same way.

By the end, you will have:

• A Nexadata dataset that reads from Databricks with a SQL statement

• A Workflow that writes its output to a new Databricks table

• Proof that every row arrived and the amounts tie to the cent

• Proof that rerunning the Workflow does not duplicate rows

---

Part 1: Set Up Databricks

This example uses one catalog with two schemas: one Nexadata reads from, and one it writes to. Your names can differ.

Object	Purpose	Privileges for the service principal
finance	Catalog	USE CATALOG
finance.actuals	Source data, read by Nexadata	USE SCHEMA, SELECT
finance.planning	Results, written by Nexadata	USE SCHEMA, SELECT, MODIFY, READ VOLUME, WRITE VOLUME, CREATE TABLE, CREATE VOLUME

Splitting read and write this way keeps Nexadata’s access to your source data read-only. See Setting up a Databricks Connection for how to create the service principal and grant these privileges.

Step 1: Create the Catalog and Schemas

In the Databricks SQL Editor, run:

CREATE CATALOG IF NOT EXISTS finance;
CREATE SCHEMA IF NOT EXISTS finance.actuals;
CREATE SCHEMA IF NOT EXISTS finance.planning;

Step 2: Load the Source Table

1. Download beverage-sales-fact.csv (1.4 MB). It holds the Beverage Sales fact table, generated sample data for a beverage company: 28,800 rows with the columns month, measures, product, market, scenario, pkg_type, and data.

2. In Catalog, open finance → actuals, then click Create → Table.

3. Upload beverage-sales-fact.csv and name the table beverage_sales_raw. Click Create table.

4. In the SQL Editor, copy the table and store the amount as an exact decimal:

CREATE OR REPLACE TABLE finance.actuals.beverage_sales AS
SELECT month, measures, product, market, scenario, pkg_type,
       CAST(data AS DECIMAL(18,2)) AS data
FROM finance.actuals.beverage_sales_raw;

DROP TABLE finance.actuals.beverage_sales_raw;

Note

Store amounts as DECIMAL, not as a floating-point type such as DOUBLE. Floating-point values cannot hold amounts like 100.50 exactly, so totals drift by fractions of a cent.

Step 3: Record the Source Totals

SELECT COUNT(*) AS row_count, SUM(data) AS total
FROM finance.actuals.beverage_sales;

For Beverage Sales, this returns 28,800 rows totalling 5,613,357.84. Keep these numbers; you will compare against them at the end.

---

Part 2: Read the Table into Nexadata

1. Create a Databricks connection using Service Principal. See Setting up a Databricks Connection.

2. Create a dataset from that connection. Choose your SQL Warehouse and enter this SQL Statement:

SELECT month, measures, product, market, scenario, pkg_type, data
FROM finance.actuals.beverage_sales

3. Click Submit. On Define Columns, you should see the seven columns above. Save the dataset.

See Using a Databricks Connection for the dataset options in detail.

---

Part 3: Write the Results Back to Databricks

1. Create a Workflow that uses the dataset. See Creating Nexadata Workflows. For this round trip, the data can pass through unchanged; in practice, this is where your transformations and mappings go.

2. Add an output and select your Databricks connection. Set:

   • SQL Warehouse: The same warehouse as the dataset.

   • Target Table: finance.planning.beverage_sales

   • Write Mode: Upsert, with Business Key Columns month, measures, product, market, scenario, and pkg_type.

   • Create the table if it doesn’t exist: On, with account users as the Read Access Group so that others in your Databricks account can see the result.

3. Save the output and run the Workflow.

---

Part 4: Confirm the Round Trip

Check the Totals

In the Databricks SQL Editor, run:

SELECT COUNT(*) AS row_count, SUM(data) AS total
FROM finance.planning.beverage_sales;

The result should match the source totals from Part 1 exactly: 28,800 rows totalling 5,613,357.84. Matching to the cent confirms that every amount was written as an exact decimal.

Check the Run ID

In Catalog, open finance → planning → beverage_sales. Alongside the seven source columns, the table has a nexadata_run_id column holding the ID of the Workflow run that wrote each row.

Run It Again

Run the Workflow a second time, then repeat the totals query. The row count stays at 28,800: Upsert matched every row on the business key and updated it rather than inserting a duplicate. The nexadata_run_id on each row now holds the second run’s ID.

Note

With Append instead of Upsert, every run adds its rows again, so a second run doubles the row count. Use Append for data that only ever grows, such as a daily log, and Upsert for data that is restated, such as plans and allocations.

---

Where to Go Next

Replace Beverage Sales with your own tables, add transformations and mappings to the Workflow, and write the results to the schemas your planning teams use. If anything in the round trip fails, see Troubleshooting the Databricks Connector.

Next: Learn about Databricks connector troubleshooting tips