Compatibility Mode

Preview

This feature is in Public Preview.

Using Compatibility Mode, you can read Unity Catalog managed tables, materialized views, and streaming tables from external systems while maintaining optimal performance on Databricks. This feature automatically generates read-only versions of your tables that can be accessed by any Delta Lake or Iceberg client.

Overview

When enabled on a managed table, streaming table, or materialized view, Compatibility Mode generates a read-only version of your table at a chosen location. This compatibility version includes v1 metadata for both Delta Lake and Iceberg formats, providing the following capabilities:

• Interoperability with any Delta Lake client: Read your managed tables, including streaming tables or materialized views, from clients like Amazon Athena, Microsoft Fabric, Snowflake, and Amazon Redshift directly from storage or through the Unity REST API
• Interoperability with any Iceberg client: Read your managed tables, including streaming tables or materialized views, from Iceberg clients like Apache Spark, Apache Trino, and Snowflake through the Iceberg REST catalog
• Set-and-forget automation: Automate data and metadata refreshes for compatibility versions, with the ability to configure refresh intervals to near real-time

Prerequisites

To enable Compatibility Mode on a table, you must be using Unity Catalog. Only Unity Catalog streaming tables, Unity Catalog materialized views, and Unity Catalog managed tables are supported. Unity Catalog external tables are not supported.

In addition, verify that you have an external location registered in Unity Catalog with proper settings and permissions:

• The target location must exist in your storage account and be empty.
• The target location or any one of its parent folders must be registered as an external location in Unity Catalog.
• You must have CREATE EXTERNAL TABLE privilege for the external location.
• The target location and any parent or child folders must not have been used as a Compatibility Mode location for another table within the last 7 days.

Enable Compatibility Mode on tables

For streaming tables, materialized views, and managed shallow clones, set the following table properties at table creation time:

SQL

CREATE [STREAMING TABLE | MATERIALIZED VIEW | TABLE] my_catalog.my_schema.my_table
TBLPROPERTIES(
  'delta.universalFormat.enabledFormats' = 'compatibility',
  'delta.universalFormat.compatibility.location' = '<location>'
)

For Unity Catalog managed tables only, you can also enable Compatibility Mode when you alter an existing table:

SQL

-- For existing managed tables
ALTER TABLE my_catalog.my_schema.my_table SET TBLPROPERTIES(
  'delta.universalFormat.enabledFormats' = 'compatibility',
  'delta.universalFormat.compatibility.location' = '<location>'
)

It takes up to one hour to generate a compatibility version for the first time. You can manually refresh the table immediately to confirm that it's working.

note

You must specify the full three-part table name (catalog.schema.table_name). For example, for users.john.my_table, usersis the catalog and john is the schema.

Check if Compatibility Mode is enabled

To verify that Compatibility Mode is enabled on your table, check that the delta.universalFormat.enabledFormats = 'compatibility' table property exists. You can view this property in the Catalog Explorer UI on the details tab for your table.

You can also run the following SQL commands in a notebook:

SQL

DESC DETAIL my_catalog.my_schema.my_table
DESC EXTENDED my_catalog.my_schema.my_table

Look for these properties in the output:

• delta.universalFormat.enabledFormats: "compatibility" – Indicates that Compatibility Mode is enabled
• delta.universalFormat.compatibility.location – Shows the location of the Compatibility Mode version

Configure refresh intervals

For Unity Catalog managed tables, you can configure how frequently the Compatibility Mode version is refreshed by setting the refresh interval:

SQL

-- Evaluate whether a refresh is needed after every commit (fastest)
ALTER TABLE my_catalog.my_schema.my_table SET TBLPROPERTIES(
  'delta.universalFormat.enabledFormats' = 'compatibility',
  'delta.universalFormat.compatibility.location' = '<location>',
  'delta.universalFormat.compatibility.targetRefreshInterval' = '0 MINUTES'
)

-- Refresh hourly (default)
ALTER TABLE my_catalog.my_schema.my_table SET TBLPROPERTIES(
  'delta.universalFormat.enabledFormats' = 'compatibility',
  'delta.universalFormat.compatibility.location' = '<location>',
  'delta.universalFormat.compatibility.targetRefreshInterval' = '1 HOUR'
)

The default refresh interval is 1 HOUR. Setting the refresh interval below 1 hour isn't recommended, and won't make refreshes occur more frequently. The exception is when you set the refresh interval to 0 MINUTES. In this case, Databricks checks for changes after every commit and triggers a refresh if needed.

For streaming tables and materialized views, a refresh interval is not required. 0 MINUTES is the default value.

note

Changes that significantly impact refresh times (such as column renaming or enabling type widening) are performed hourly regardless of the target refresh interval.

Manual refresh

To manually trigger a refresh of the compatibility version:

SQL

REFRESH [TABLE | STREAMING TABLE | MATERIALIZED VIEW] my_catalog.my_schema.my_table SYNC UNIFORM

Manual refresh is useful for testing that Compatibility Mode is working correctly, or to guarantee that your compatibility version is up-to-date before a subsequent read. However, waiting for the automatic refreshes can be more cost-effective.

Monitor data and metadata generation status

Compatibility Mode generates data and metadata automatically and asynchronously. For Unity Catalog managed tables, generation occurs hourly by default, or based on your configured refresh interval. For streaming tables and materialized views, generation occurs after table updates when there are new commits.

To check whether data and metadata has been successfully generated:

1. Use DESCRIBE HISTORY to find the latest version of your source table:

   SQL

   DESC HISTORY my_catalog.my_schema.my_table

   

   This command returns the history of refreshes to Compatibility Mode, including the Delta Lake version and timestamp. The top row contains the latest version and timestamp.

2. Use DESCRIBE EXTENDED to find the corresponding version of Compatibility Mode:

   SQL

   DESC EXTENDED my_catalog.my_schema.my_table

   

   Look for fields under Uniform Compatibility Information:

   • Last Refreshed Version: The Delta Lake version that was last updated with Compatibility Mode
   • Last Refreshed: The timestamp of the last refresh

   Compatibility Mode is up-to-date if the table version matches the version found in step 1.

3. Use DESC HISTORY on the compatibility version itself:

   SQL

   DESC HISTORY delta.\`<compatibility_location>\`

   

4. In Catalog Explorer, view the metadata fields for the compatibility version. Compatibility Mode is up-to-date if the Delta Lake version matches with the version found in step 3.

   

Monitor costs

Predictive Optimization handles the compute cluster that does automatic refreshes for Compatibility Mode. To view associated costs, query the system billing tables:

SQL

SELECT
  DATE_TRUNC('DAY', start_time) AS day,
  SUM(usage_quantity) AS dbus
FROM
  system.storage.predictive_optimization_operations_history
WHERE
  operation_type = "COMPATIBILITY_MODE_REFRESH"
GROUP BY 1
ORDER BY 1 DESC;

This query reports usage for automatic refreshes only. Costs for manual refreshes are associated with your compute, and there isn't a direct way to separately track those costs. Generally, the cost of a manual trigger is proportional to the cost of the initial write operation to the original table.

Remove unused data files

To remove unused data files on the compatibility version of your table, use VACUUM:

SQL

VACUUM delta.'<compatibility_mode_location_path>';

To find the Compatibility Mode location path, use the DESCRIBE EXTENDED command in Check if Compatibility Mode is enabled. In the output, the value of delta.universalFormat.compatibility.location is the location.

Read compatibility versions from external clients

You can use any Delta Lake or Iceberg client to read data from compatibility versions. See below for examples for Amazon Athena, Snowflake (Delta reader), and Fabric.

Amazon Athena

1. In the Athena Query Editor, create an external table with the specified location:

   SQL

   CREATE EXTERNAL TABLE <table_name>
   LOCATION '<compatibility_location>'
   TBLPROPERTIES ('table_type' = 'DELTA')

2. Read the table:

   SQL

   SELECT * FROM <table_name>

Snowflake Delta Lake reader

1. Create storage integration to access the storage location (see Snowflake documentation).

2. Create an external table with Delta Lake format:

   SQL

   CREATE OR REPLACE EXTERNAL TABLE <table_name>
   WITH LOCATION = @<my_location>
   FILE_FORMAT = (TYPE = PARQUET)
   TABLE_FORMAT = DELTA
   AUTO_REFRESH = false
   REFRESH_ON_CREATE = false;

3. Refresh the table (auto refresh is not supported for Delta Lake format in Snowflake):

   SQL

   ALTER EXTERNAL TABLE <table_name> REFRESH;

4. Read the table:

   SQL

   SELECT * FROM <table_name>;

Microsoft Fabric

1. Create a Fabric capacity, workspace, and Synapse notebook.

2. Create a lakehouse with data in the compatibility location.

3. Read the files as a Delta Lake table:

   Python

   spark.read.format("delta").load("Files/<path_to_data>")

Reading compatibility versions from the Unity REST API

Tables with Compatibility Mode enabled can be read by name through the Unity REST API with special parameters. For streaming tables, set the following API parameter:

GET /api/2.1/unity-catalog/tables/{full_name}?read_streaming_table_as_managed=true

For materialized views, set the following API parameter:

GET /api/2.1/unity-catalog/tables/{full_name}?read_materialized_view_as_managed=true

Reading compatibility versions from Iceberg REST catalog

Tables with Compatibility Mode enabled can be read from any Iceberg client using the Iceberg REST catalog. Compatibility Mode works automatically for both Delta Lake and Iceberg table formats.

Setup requirements

1. Enable external data access on the metastore.
2. Grant EXTERNAL USE SCHEMA privilege on the schema.
3. Create a Databricks Personal Access Token (PAT).

Apache Spark configuration

Bash

bin/spark-sql --packages org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.8.0,org.apache.iceberg:iceberg-gcp-bundle:1.8.0 \
  --conf "spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions" \
  --conf spark.sql.catalog.catalog_name=org.apache.iceberg.spark.SparkCatalog \
  --conf spark.sql.catalog.catalog_name.type=rest \
  --conf spark.sql.catalog.catalog_name.uri=<workspace-url>/api/2.1/unity-catalog/iceberg-rest \
  --conf spark.sql.catalog.catalog_name.token=<PAT> \
  --conf spark.sql.catalog.catalog_name.warehouse=<uc-catalog-name>

For more information, see Use Iceberg tables with Apache Spark.

Snowflake configuration

SQL

CREATE OR REPLACE CATALOG INTEGRATION my_uc_int
  CATALOG_SOURCE = ICEBERG_REST
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE = '<uc-schema-name>'
  REST_CONFIG = (
    CATALOG_URI = '<workspace-url>/api/2.1/unity-catalog/iceberg-rest'
    CATALOG_NAME = '<uc-catalog-name>'
    ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
  )
  REST_AUTHENTICATION = (
    TYPE = BEARER
    BEARER_TOKEN = '<PAT>'
  )
  ENABLED = TRUE;

CREATE OR REPLACE ICEBERG TABLE my_table
  CATALOG = 'my_uc_int'
  CATALOG_TABLE_NAME = '<uc-st/mv-name>';

Disable Compatibility Mode

To disable Compatibility Mode, unset the corresponding table property:

SQL

-- For UC managed tables
ALTER TABLE my_table UNSET TBLPROPERTIES('delta.universalFormat.enabledFormats')

-- For streaming tables and materialized views
CREATE OR REPLACE [STREAMING TABLE | MATERIALIZED VIEW] my_table
TBLPROPERTIES('delta.universalFormat.enabledFormats' = '')

warning

Unsetting Compatibility Mode immediately stops data and metadata generation. After 7 days, the associated data and metadata will be deleted. Within this 7-day period, you can restore the data and metadata by re-enabling Compatibility Mode on the same table.

Limitations

• Read-only access: The compatibility version is read-only. You cannot write to the compatibility version.
• No RLS/CLS support: You cannot enable Compatibility Mode on tables with Row-Level Security (RLS) or Column-Level Security (CLS).
• No partition column renaming: Partition column renaming is not supported on tables with Compatibility Mode enabled. Data column renaming is supported.
• Limited table features: The following capabilities are not available in the compatibility version:
  • Collation columns
  • Primary keys
  • Time Travel
  • Change data feed
  • Column names with special characters (will be renamed)