Compatibility Mode for AWS Redshift

[HZ]

Hello,

I have several tables loaded from S3 using Lakeflow pipelines which generates streaming table and materialized views. Want to be able to read these tables in AWS redshift.

Based on the Databricks documentation for compatibility mode:

Databricks Compatibility Mode

my understanding is that compatibility mode enables external systems, including Amazon Redshift, to read Unity Catalog managed Delta tables either directly from storage or through the Unity Catalog REST API.

However, I am unclear on how this works with Redshift. My understanding is that Redshift Spectrum traditionally requires Delta Lake manifest files to query Delta tables, whereas compatibility mode appears to generate only the data files and metadata files.

Could you clarify how Redshift is expected to access these tables when compatibility mode is enabled? Is there a recommended setup for Redshift?

Thank you

[Satyasai]

Redshift Reads Iceberg Metadata directly

Amazon Redshift can query Apache Iceberg tables natively, Redshift does not need symlink_manifest files. It reads the Iceberg metadata that is generated by UniForm to determine exactly which Parquet files make up the latest version of your Delta table.

Redshift Access Patterns: Step-by-Step
1. Enable UniForm on the Databricks Table:
Make sure that UniForm (Iceberg) is enabled on your Delta table:

ALTER TABLE my_catalog.my_schema.my_streaming_table
SET TBLPROPERTIES (
'delta.enableIcebergCompatV2' = 'true',
'delta.universalFormat.enabledFormats' = 'iceberg'
);

2. Sync Unity Catalog to AWS Glue:
Set up Unity Catalog Glue Sync so that you can have table metadata mapped to the AWS Glue Data Catalog in your AWS account.

3. Create External Schema in Redshift:
In Amazon Redshift, create an external schema pointing to your Glue database:

SQL
CREATE EXTERNAL SCHEMA uc_glue_db
FROM DATA CATALOG
DATABASE 'my_uc_schema'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

4.Query Directly in Redshift:
SELECT * FROM uc_glue_db.my_streaming_table WHERE event_date = CURRENT_DATE;

[ThiamLee]

I’ve been wondering about this too. The gap between Compatibility Mode and how Redshift Spectrum actually discovers/queries the Delta data isn’t very clear from the docs. Would love to see a concrete Redshift setup example or recommended architecture.

[ivanvyd]

@HZ your understanding of Spectrum's Delta reader is correct: it requires manifests. The alternative is Redshift's Iceberg access through Glue catalog federation, which AWS documents for Unity Catalog. These are separate integration paths.

For your Lakeflow tables, the setup to validate is:

1. Enable Compatibility Mode in the table definition.

For streaming tables and materialized views, the Databricks guide specifies these properties at creation time:

TBLPROPERTIES (
  'delta.universalFormat.enabledFormats' = 'compatibility',
  'delta.universalFormat.compatibility.location' = 's3://<bucket>/<table-path>'
)

The destination must be empty and covered by a registered external location where you have CREATE EXTERNAL TABLE. Compatibility Mode generates a read-only version with Delta and Iceberg metadata; it does not configure Redshift access for you.

2. Federate Unity Catalog into Glue.

Follow the Lake Formation setup. It uses a Databricks service principal for catalog access and an IAM role for access to the underlying S3 data. For this setup, that storage access needs to cover the compatibility data.

3. Connect Redshift to the federated database.

The Redshift guide covers creating a Glue database resource link, granting Lake Formation permissions, and querying through an external schema:

CREATE EXTERNAL SCHEMA uc_lakeflow
FROM DATA CATALOG DATABASE 'your_resource_link_database'
IAM_ROLE 'arn:aws:iam::<account-id>:role/<redshift-role>';

For this external-schema route, the catalog, Redshift compute and S3 bucket must be in the same Region.

[ivanvyd]

One qualification: AWS's guide covers Delta tables exposed through UniForm, but does not explicitly confirm Compatibility Mode streaming tables or materialized views. Databricks documents their availability through Iceberg REST, so this is a route to validate, not a confirmed end-to-end recipe for your exact table types. Test discovery and a subsequent update for one of each, allowing the asynchronous compatibility refresh to complete before checking Redshift. See AWS Documentation

[HZ]

Thanks Ivanvyd

For this part

Follow the Lake Formation setup. It uses a Databricks service principal for catalog access and an IAM role for access to the underlying S3 data. For this setup, that storage access needs to cover the compatibility data.

Yes, I can create a federated catalog in AWS Lake Formation using Databricks Unity Catalog through the Iceberg REST Catalog integration.

When creating the federated catalog in AWS, I can then create an AWS Glue resource link that references the Databricks Unity Catalog. This allows the Databricks tables to appear in AWS Glue and Lake Formation, including streaming tables and materialized views, i.e. i can see table's metadata

The tricky part is that Unity Catalog tables are stored in one S3 location (for example, s3-uc), while the compatibility mode data copies are stored in a different S3 location (for example, s3-compat).

Based on my testing, when Redshift queries the federated Databricks tables, it seems to reference the metadata returned by Unity Catalog, which points to the underlying data in s3-uc rather than the compatibility mode data in s3-compat. 

I want to confirm whether my understanding is correct.

For compatibility mode, when I federate Unity Catalog into AWS Glue/Lake Formation, it is reading the Unity Catalog tables and their associated metadata directly from Unity Catalog, rather than using the compatibility mode data copies?

If that's the case, then even though compatibility mode creates data copies, the federated catalog would still point to the original Unity Catalog table metadata and storage locations.

Thanks

Helen Zhang

[ivanvyd]

Hey @HZ, Unity Catalog supplies the metadata, but that does not necessarily mean Redshift reads the original files. Databricks documents automatic access to compatibility versions through the Iceberg REST catalog. 

Could you confirm whether s3-uc appears only in Glue's displayed table location, or in the current snapshot's data-file paths? Iceberg's manifests identify the files to read, so those references are stronger evidence than the displayed location alone.

Also compare Last Refreshed Version in DESCRIBE EXTENDED with the latest source version in DESCRIBE HISTORY to confirm the compatibility refresh is current.

If it is current and those data-file paths still reference s3-uc, comparing direct Iceberg REST access using the same Databricks principal would help investigate the difference. The returned metadata, referenced paths and Redshift query ID would give Support a concrete case.

This remains the qualification from my earlier reply: the Iceberg REST behavior is documented, but this specific Glue/Redshift setup still needs verification.

Feel free to share with me your contacts in pm, if you want to hop on a quick call to figure it out.

[HZ]

Thanks @ivanvyd , from describe extended table, i can see metadata location as s3-compat, detailed table information has streaming table, location s3-uc. In theory, the databricks unity catalog connector with AWS should read metadata from the s3-compat , i will check again my setting at AWS side and get back to you.

Thanks

Helen