Databricks SQL Warehouse connections

A Databricks SQL Warehouse is a SQL analytics endpoint backed by Unity Catalog. DataMasque connects to it with the Databricks SQL connector and masks Delta tables in place.

• Masking process
• Required privileges
  • Databricks SQL Warehouse Database Privileges
• Connection parameters
  • Databricks SQL Warehouse Authentication
• Encrypted connections
• Limitations
  • Databricks SQL Warehouse Post-masking Data Persistence
  • Databricks SQL Warehouse Troubleshooting
• Supported data types

Masking process

DataMasque masks Databricks SQL Warehouse Delta tables in place, within the target's own Unity Catalog catalog and schema: no external staging bucket and no cross-cloud or cross-region storage is used. Both managed and external tables are supported (for an external table, the masked values are written back to its existing storage location).

DataMasque reads rows in batches, masks them, and stages the masked rows inside the target's own Unity Catalog schema (a managed UC Volume and a temporary Delta table) before applying them to the target. The masked Parquet is streamed from memory and never written to the DataMasque host's disk; the staging holds masked output only, never pre-masking data, stays within Unity Catalog governance, and is removed automatically when the run completes.

After a successful run, DataMasque VACUUMs the target to remove pre-masking files. See Databricks SQL Warehouse Post-masking Data Persistence for the VACUUM/time-travel behaviour and the actions required after a failed or cancelled run.

Required privileges

A DataMasque Databricks SQL Warehouse connection targets a SQL Warehouse endpoint via the Databricks SQL connector. It uses Unity Catalog for namespace resolution (catalog.schema.table).

For connection setup, see Databricks SQL Warehouse specific parameters.

Databricks SQL Warehouse Database Privileges

The Databricks user or service principal associated with the PAT must hold the following Unity Catalog privileges on the target catalog and schema:

-- On the catalog
GRANT USE CATALOG ON CATALOG <catalog> TO <principal>;

-- On each target schema
GRANT USE SCHEMA ON SCHEMA <catalog>.<schema> TO <principal>;

-- On each target table
GRANT SELECT ON TABLE <catalog>.<schema>.<table> TO <principal>;
GRANT MODIFY ON TABLE <catalog>.<schema>.<table> TO <principal>;

-- To create DataMasque masking history tables and the temporary masking staging table
GRANT CREATE TABLE ON SCHEMA <catalog>.<schema> TO <principal>;

-- To create the temporary UC Volume used to bulk-load masked rows during a mask run
GRANT CREATE VOLUME ON SCHEMA <catalog>.<schema> TO <principal>;

Alternatively, grant ALL PRIVILEGES on the schema:

GRANT ALL PRIVILEGES ON SCHEMA <catalog>.<schema> TO <principal>;

Connection parameters

For Databricks SQL Warehouse connections, the following additional fields are required:

Server hostname	The Databricks workspace hostname, without https://. For example, dbc-1514b142-1c6c.cloud.databricks.com. Found under SQL Warehouses → \ → Connection details.
HTTP path	The HTTP path for the SQL Warehouse. For example, /sql/1.0/warehouses/ea7d918dd5f236f9. Found in the same Connection details panel.
Personal access token	A Databricks personal access token (PAT). See Authentication below.
Catalog	The Unity Catalog catalog name. DataMasque operates within this catalog.

The following field is optional:

Schema	The default schema within the catalog. If omitted, the connection uses the catalog's default schema.

Databricks SQL Warehouse Authentication

Databricks SQL Warehouse connections use a personal access token (PAT) for authentication. OAuth and service principal authentication are not supported in this version.

To generate a PAT:

1. In the Databricks workspace, click your username in the top-right corner and select Settings.
2. Navigate to Developer → Access tokens.
3. Click Generate new token, enter a description and expiry, and click Generate.
4. Copy the token immediately; it is only shown once.

Encrypted connections

All DataMasque connections to Databricks SQL Warehouse take place over HTTPS. No additional configuration is required in DataMasque.

Limitations

• Delta tables only. Masking writes in place with MERGE INTO, which Databricks supports only on Delta tables, so non-Delta tables (for example those created with USING PARQUET) cannot be masked. Schema discovery skips them (emitting a warning with the number skipped), so they do not appear in a generated ruleset. If a ruleset targets a non-Delta table directly, the masking run fails up front with an error that the table is not a Delta table.

• Non-transactional DDL. Databricks SQL Warehouse DDL statements (e.g. CREATE TABLE, DROP TABLE) are not transactional. If a masking run is interrupted mid-operation, manual cleanup of any partially created tables may be required.

• No enforced unique constraints or indexes. Databricks accepts UNIQUE and index syntax in DDL but does not enforce uniqueness. DataMasque uses primary key columns declared in Unity Catalog for key selection.

• mask_unique_key tasks are not supported. Use the imitate_unique mask instead.

• No triggers. Databricks SQL Warehouse does not support database triggers. DataMasque does not use or require triggers.

• Complex and generated columns cannot be masked directly. Columns of type ARRAY, MAP, or STRUCT, and columns defined as GENERATED ALWAYS (identity or computed-expression columns), cannot be written via the masking MERGE. A GENERATED ALWAYS column that is not targeted by a rule is handled automatically: an identity column keeps its generated value, and an expression column is recomputed by Databricks from its (masked) source columns, so you mask the source columns and leave the generated column alone. A rule that targets a complex or generated column directly is a ruleset error: DataMasque rejects it up front and the run fails with a clear error naming the column, rather than reporting success while leaving the column unmasked.

• Single catalog per connection. Each DataMasque connection operates within one Unity Catalog catalog. Cross-catalog masking within a single run is not supported.

Databricks SQL Warehouse Post-masking Data Persistence

Unlike most databases, a completed DataMasque mask run does not immediately destroy all copies of the original data. Databricks retains historical data through several platform features, covered below.

Handled automatically by DataMasque:

• Change Data Feed (CDF). If CDF (delta.enableChangeDataFeed) is enabled on a target table, DataMasque disables it for the run and re-enables it afterward, so unmasked pre-images are never written to the change feed. This needs the MODIFY privilege (see Databricks SQL Warehouse Database Privileges); if CDF cannot be disabled, the run is aborted rather than risk leaking unmasked values.

• VACUUM. After a successful mask run, DataMasque sets the table retention to zero and runs VACUUM to immediately remove all pre-masking Parquet files:

  ALTER TABLE t SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 0 hours');
  VACUUM t;

Once VACUUM completes, pre-masking data is no longer recoverable by any of the mechanisms below. DataMasque leaves delta.deletedFileRetentionDuration at 0; to restore the default retention window, run ALTER TABLE t SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 7 days').

User actions required:

Several items below involve a brief residual window between the MERGE INTO and the VACUUM above (seconds to minutes, since VACUUM runs immediately after masking) during which pre-masking versions remain queryable. VACUUM permanently closes it; afterward, time travel and RESTORE to pre-masking versions both fail.

• Delta Time Travel. During the residual window, pre-masking versions are queryable via:

  SELECT * FROM table VERSION AS OF N;

• Temporary masking staging. The temporary Delta staging table and UC Volume DataMasque uses during a run (see the Masking Process above) are normally removed when the run completes. However, if a run fails or is cancelled (or the masking process is killed mid-run), these dm_stage_* artifacts may be left behind in the target schema.

  Action required: after an unsuccessful run, check the target schema for leftover dm_stage_* staging tables and volumes and remove them. Because they contain masked data, confine access to the target schema to trusted users/principals.

• Shallow clones. A shallow clone of the target table is an independent table that references the source's pre-masking data files. Masking the source does not mask the clone; the clone stays a fully queryable copy of the original unmasked data.

  Action required: treat any shallow clone as its own masking target: mask it or drop it as part of the same masking plan, the same as you would any other copy of the data.

• Materialized views. A materialized view defined over the masked table stores a snapshot of the data at the time it was last refreshed. If the view was last refreshed before the mask run, it will continue to return pre-masking data.

  Action required: after a mask run, manually refresh any materialized views built on the masked table:

  REFRESH MATERIALIZED VIEW view_name;

REFRESH overwrites the view's current contents with masked data. The view is itself a Delta table, so its own version history may still retain the pre-refresh (unmasked) snapshot, so VACUUM the materialized view as well to reclaim those files.

• Delta Sharing WITH HISTORY. If the target table is shared via Delta Sharing with history enabled (WITH HISTORY, the default on Databricks Runtime 16.2+), recipients can time-travel to pre-masking versions during the residual window.

  Action required: review share configuration for masked tables. Any of the following closes the exposure, depending on whether recipients still need the table:

  • Re-add the table to the share WITHOUT HISTORY (keeps current data shared, drops time travel).
  • Remove the table from the share: ALTER SHARE <share> REMOVE TABLE <table>.
  • Revoke the recipient's access: REVOKE SELECT ON SHARE <share> FROM RECIPIENT <recipient>.

  Revoking stops future access only; it does not retract data a recipient has already queried.

• RESTORE. RESTORE TABLE <table> TO VERSION AS OF N rewrites the table back to an earlier version. Unlike time travel (a read), this makes the pre-masking data current again, effectively un-masking the table. It too only works during the residual window; afterward it fails. RESTORE requires MODIFY on the table (table owners and workspace admins), so it is not available to read-only consumers.

Databricks SQL Warehouse Troubleshooting

• Cannot reach workspace / connection refused. Verify the server hostname does not include https://, and that the HTTP path matches the target SQL Warehouse exactly. Confirm the warehouse is running and the PAT has not expired.

• Permission denied errors. Ensure the PAT principal holds USE CATALOG, USE SCHEMA, SELECT, MODIFY, and CREATE TABLE on the target catalog and schema.

• Warehouse cold-start delay. Serverless SQL Warehouses start on first query. Connection tests and the first masking operation may take 10–30 seconds longer than expected while the warehouse starts. This is normal behaviour; the warehouse auto-suspends when idle.

• Large table timeouts. For very large tables, consider increasing the SQL Warehouse query timeout settings in the Databricks workspace, or reduce the size of each write using the batch_size option.

• DELTA_METADATA_CHANGED errors. These occur when another process modifies a Delta table while DataMasque is masking it. Ensure no concurrent writers are active on the target table during a masking run.

Supported data types

The following Databricks SQL data types are supported:

Data type	Description
bigint	8-byte signed integer.
int / integer	4-byte signed integer.
smallint	2-byte signed integer.
tinyint	1-byte signed integer.
decimal(p,s) / numeric(p,s)	Fixed-precision decimal with precision p and scale s.
float / real	4-byte IEEE 754 floating-point number.
double	8-byte IEEE 754 floating-point number.
boolean	TRUE or FALSE.
string	Unlimited-length Unicode string.
varchar(n)	Variable-length Unicode string up to n characters.
char(n)	Fixed-length Unicode string of n characters.
date	Calendar date (year, month, day). No time component.
timestamp	Timestamp with time zone (stored in UTC).
timestamp_ntz	Timestamp without time zone.
binary	Variable-length binary data.

The following complex types cannot be masked. A column of one of these types is left unchanged when no rule targets it; a rule that targets one fails the run (see Databricks SQL Warehouse Limitations):

Data type	Note
array<type>	Nested array. Not modified during masking.
map<key, value>	Key-value map. Not modified during masking.
struct<fields>	Structured type. Not modified during masking.