SmartDiff on Databricks

You can use a Databricks Database data source as the source, the target, or both in a SmartDiff workflow. This page explains what is different about Databricks compared with other SQL sources: how to name a table, which permissions the connection needs, how a large table is split into batches, and the limits to plan around.

Databricks organises tables in Unity Catalog: a catalog holds schemas, and a schema holds tables and views. Every query runs on a SQL Warehouse, which is the compute that Databricks bills for.

To create the data source first, see the Databricks data source page.

What is supported

Both SmartDiff modes work with Databricks:

Mode	How DataDios reads Databricks
Query Diff	Runs your source and target queries sorted by the key column and streams the results in batches. The two sorted streams are merged and compared.
Data Source Diff	Counts the rows, reads the minimum and maximum of the key column, splits the key range into batches, and reads each batch with a `WHERE` clause on the key.

A Databricks connection appears in the Source and Target pickers in both modes as soon as it is created. There is no separate switch to enable.

Compare Type. All three settings work: Source And Target, Source and Target. In a one-sided compare (Source or Target), DataDios fetches the other side's rows by key. This works for a single key column and for a composite key (several key columns).

Objects you can compare. Tables, views, materialized views, streaming tables and foreign tables are all listed and can be compared. Streaming and foreign tables are listed with the type TABLE.

Table names: catalog.schema.table

A Databricks table always has a three-part name: catalog.schema.table. Always write all three parts. The connection does not bind a default catalog, and Query Diff runs your query as written, so a two-part or one-part name is resolved against whichever catalog the warehouse session is using at that moment: the warehouse's default catalog, or a catalog an earlier read on the same connection switched to. Instead of an error, you can end up comparing a table of the same name in a different catalog.

SELECT * FROM main.sales.orders

Names with hyphens or reserved words

A Unity Catalog name is often not a plain identifier. Catalog names in particular routinely contain hyphens, such as chip-mfg. In Databricks SQL, such a name must be wrapped in backticks, otherwise the hyphen is read as a minus sign:

SELECT * FROM `chip-mfg`.`sales`.`orders`

The same applies to a column whose name is a reserved word, such as date, order, user or group.

The Query Diff schema tree does this for you. When you click a table in the Source or Target tree on the Mapping tab, DataDios inserts a SELECT * FROM statement with every part of the name wrapped in backticks. Clicking a column adds it to the query in the same form. So a hyphenated catalog name works without any editing.

If you type or paste your own query, add the backticks yourself.

note

Databricks has no "current database" to switch between, so the Database picker that appears next to a multi-database PostgreSQL source does not appear for Databricks. The catalog is always part of the table name.

Databricks resolves a backtick-quoted name without regard to case, so `Orders` and `orders` are the same table.

Permissions the connection needs

The principal you connect with (the user behind a personal access token, or the service principal behind OAuth) needs the following Unity Catalog privileges for a read-only diff:

Privilege	On	Why
`USE CATALOG`	the catalog	To see the catalog and run `USE CATALOG`
`USE SCHEMA`	the schema	To see the schema
`SELECT`	every table or view being compared	To count rows, read the key range, and read the data

The principal also needs permission to use the SQL Warehouse whose HTTP Path is on the connection.

DataDios reads column names, types, length, scale and nullable from the catalog's information_schema. That needs no grant of its own: Databricks shows each principal only the objects it already has privileges on. A catalog with no information schema, such as hive_metastore or a Delta Sharing catalog like samples, returns nothing there, and DataDios falls back to DESCRIBE TABLE. The diff still works, but the length, scale and nullable values shown for the table's attributes are blank.

DataDios also runs DESCRIBE DETAIL on each table when it lists a catalog, to record the Delta table id. If that statement fails for a table, the table is still listed.

Creating tables and loading data needs more

The privileges above are enough to compare. If you also use DataDios to create a catalog, schema, table or column, or to load rows into a Databricks table, the principal needs the matching write privileges, for example CREATE SCHEMA on the catalog, CREATE TABLE on the schema, and MODIFY on the table for loading. The SQL editor for a Databricks source is read-only. A statement must start with SELECT, WITH, SHOW, DESCRIBE, DESC or EXPLAIN, and a statement that contains a write keyword outside quotes (INSERT, UPDATE, DELETE, MERGE, DROP, TRUNCATE, ALTER, CREATE, REPLACE, GRANT, REVOKE or COPY) is refused. That includes read-only statements such as SHOW CREATE TABLE and SELECT replace(name, '-', '_').

Column names and case

Databricks stores a column name in the case you created it with, and DataDios shows that stored case (for example order_id) in the table's attributes and in the Query Diff tree.

The diff itself matches column names without regard to case. A source column Order_Id maps to a target column ORDER_ID, and a key column you selected earlier still matches after the column is renamed to a different case. The SQL that DataDios builds wraps every column name in backticks, and Databricks resolves those without regard to case too.

One consequence: two columns in the same table whose names differ only by case, such as id and ID, are treated as one column by the diff.

Key columns and batches

Choose the key column yourself

DataDios does not read primary-key constraints from Databricks, so it cannot pre-select a key column for you.

In Data Source Diff, no key is pre-marked on the Mapping tab: toggle the key icon on the column yourself.
In Query Diff, mark the key columns after Analyze as you do for any source.

Pick a column that uniquely identifies a row. The type of the key also decides whether the diff can work in batches, as described next.

How Data Source Diff splits a table into batches

For a large table, Data Source Diff does not read the whole table at once. It plans key-range batches:

It counts the rows on both sides (SELECT count(*)).
It reads the minimum and maximum of the key column on both sides.
It splits that range into batches and reads each batch with WHERE key >= ? AND key < ?, ordered by the key. The last batch reads up to and including the maximum.
The first batch also reads the rows whose key is NULL (and, for a string key, empty), so they are not lost.

Batching needs a key column whose type is the same on both sides and is one of number, float, date, timestamp or string. A key that is a number on one side and a string on the other cannot be batched. Batching applies only when the smaller table has more rows than the Batch size. If there is no such key, or the tables are small, each table is read whole in a single batch. For a large table without a usable key, that means the whole table is held in memory on the DataDios server, so choose a batchable key for anything big.

Only one key column is used to plan the ranges. If you select several key columns, DataDios prefers a numeric one, then a float, string, timestamp or date column, in that order.

Batch size

Batch size is in the Engine card of the workflow's Configuration tab.

The default is 5,000 rows and the allowed range is 1 to 10,000.

In Query Diff, it is the number of rows fetched from the warehouse per streamed batch.
In Data Source Diff, it is the target number of rows per key range.

Sparse keys: batches can be uneven

A key does not have to be dense to work. For a number, float, date or timestamp key, the number of batches is bounded by the row count, never by the width of the key range:

maximum batches = ceil(rows on the larger side / Batch size)

A dense integer key (1, 2, 3, ...) is split into fixed steps of Batch size, so every batch holds about Batch size rows.
A sparse integer key, such as a surrogate id, a hash, or a snowflake-style id that spans a range near 10^18, is instead cut into that maximum number of equal-width ranges. The number of batches stays sensible, but the number of rows in each batch follows how the keys are spread. Keys spread evenly give even batches; keys clustered at the ends of the range give mostly empty batches and a few large ones.
A date or timestamp key steps in Batch size days, so a million rows over three years can land in only a couple of batches.
A string key is split by character ranges, and is not bounded this way.

If a batch turns out much larger than Batch size, the diff still completes, but it uses more memory for that batch. If you see this on a table keyed by a sparse id, prefer a denser key when one is available.

Type handling worth knowing

DECIMAL values are compared as double-precision numbers, the same as every other SQL source.
TIMESTAMP is an instant with a time zone; TIMESTAMP_NTZ is a wall-clock time without one. Each keeps its own type in the comparison.
ARRAY, MAP, STRUCT and VARIANT columns are shown with the generic type BINARY in the table's attributes.

Types you can create through the form

When you create a table in Databricks through the DataDios create form, the Column Type list offers only the types that DataDios knows how to write as valid CREATE TABLE statements:

TINYINT, BYTE, SMALLINT, SHORT, INT, INTEGER, BIGINT, LONG, FLOAT, REAL, DOUBLE, DECIMAL, DEC, NUMERIC, DATE, TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, CHAR, VARCHAR, STRING, BOOLEAN, BINARY

ARRAY, MAP, STRUCT, VARIANT and INTERVAL columns cannot be created through the form, because each needs an element type or a qualifier. Create such a table with your own SQL instead. CHAR and VARCHAR are always written with a length, because Databricks requires one. A table can have at most one column marked Primary, and that column must not be nullable.

Limits and cost

Every diff runs sorted queries on the SQL Warehouse. Query Diff adds an ORDER BY on the key column to each query, and Data Source Diff runs a count, a min/max, and one ordered SELECT per batch. This is warehouse compute that Databricks bills for, so schedule large diffs with that in mind.
A stopped warehouse is started by the first query. The first diff after the warehouse has been idle waits for it to start, so it is slower. DataDios waits up to 10 minutes for the warehouse to answer a request before it reports a connection error.
Query Diff statement timeout. Each streamed query runs under a Databricks STATEMENT_TIMEOUT. The DataDios server sets it from SMARTDIFF_STREAM_STATEMENT_TIMEOUT_MS, which defaults to 1,800,000 milliseconds (30 minutes). Databricks counts this setting in whole seconds, so the value is rounded up to the next second. If a query runs longer than that, Databricks cancels it, the batch fails, and the workflow reports the failure. For very large queries, narrow the query, or raise the setting: on an on-premises installation an administrator sets it on the DataDios server; on DataDios Cloud, contact DataDios support. Data Source Diff reads are not subject to this timeout.
Very large tables. Query Diff always streams and never loads a whole result into memory. Data Source Diff streams only when it has a batchable key column (see above). For a table with hundreds of millions of rows, use Query Diff with a WHERE clause that limits the range, or make sure the key column is batchable.
Views are compared by running the view, so a slow view makes a slow diff.

Troubleshooting

What you see	Likely cause	What to do
`[PARSE_SYNTAX_ERROR]` on a query you typed	A hyphenated catalog or schema name is not quoted	Wrap each part of the name in backticks
Table is missing from the schema tree	The principal lacks `USE CATALOG`, `USE SCHEMA` or `SELECT`, or the Catalog/Schema filters on the connection exclude it	Grant the privilege, or widen the filters on the data source
Column length, scale and nullable are blank in the table's attributes	The catalog has no information schema (`hive_metastore`, or a Delta Sharing catalog such as `samples`), so DataDios used `DESCRIBE TABLE`	Nothing to fix: this is expected for those catalogs, and the diff itself is unaffected
No key column is pre-selected for a Databricks table	DataDios does not read Databricks primary-key constraints	Mark the key column yourself
A batch fails after about 30 minutes in Query Diff	The `STATEMENT_TIMEOUT` was reached	Narrow the query. On-premises: raise `SMARTDIFF_STREAM_STATEMENT_TIMEOUT_MS` on the DataDios server. DataDios Cloud: contact DataDios support
The first diff after a pause is slow	The SQL Warehouse had stopped and was started by the first query	Keep the warehouse running before a scheduled diff, or accept the start-up delay

What is supported​

Table names: catalog.schema.table​

Names with hyphens or reserved words​

Permissions the connection needs​

Creating tables and loading data needs more​

Column names and case​

Key columns and batches​

Choose the key column yourself​

How Data Source Diff splits a table into batches​

Batch size​

Sparse keys: batches can be uneven​

Type handling worth knowing​

Types you can create through the form​

Limits and cost​

Troubleshooting​