Enable external data access to streaming tables and materialized views

If you have enabled external data access to Unity Catalog, you can add external data access to pipeline-managed and standalone materialized views and streaming tables. This enables external Delta and Iceberg clients to access your datasets through the Unity Catalog and Iceberg catalog REST APIs, without requiring a full data copy.

External data access works for datasets managed by Lakeflow pipelines and for standalone materialized views and streaming tables.

Capabilities

Using external data access exposes the same data available in Azure Databricks for pipeline-managed and standalone materialized views and streaming tables, without creating a duplicate of the data. This gives the following characteristics for performance and functionality:

  • No data copy required: External access is enabled without duplicating the full dataset.
  • External access via APIs: Read materialized views and streaming tables using Delta Lake or Iceberg APIs.
  • Read-after-write consistency: External readers can access up-to-date data after an update to the dataset, ensuring no staleness. Updates are available immediately upon refresh.
  • Single table object: Datasets appear externally as managed tables with the same name as the source dataset within Unity Catalog APIs.
  • Low cost: Because the full dataset is not copied, the overhead for providing external access is low.

Requirements

The requirements for your datasets are:

  • Unity Catalog: Your streaming tables and materialized views must be using Unity Catalog.
  • Databricks Runtime version: You must be using Databricks Runtime 17.3 and above.
  • Default publishing mode: External readability is only supported in default publishing mode. To use external readability, migrate to the default publishing mode. Features that depend on external metadata, such as materialized view CDF, will work in legacy publishing mode.

The requirements for your clients are:

  • Delta API version: The client must support Delta Lake APIs 4.0.0 or above, including deletion vectors, and must use the Unity Catalog catalog APIs for access.
  • Iceberg API version: Alternatively, the client can access using Iceberg catalog APIs that support the Iceberg v3 specification.
  • Unity Catalog privileges: The principal reading the datasets externally must have the EXTERNAL USE SCHEMA privilege on the schema, and SELECT privilege on the table.

Note

If your client does not support these requirements, you can also use compatibility mode, which supports all Delta and Iceberg clients, but requires creating a full copy of the dataset.

How to enable access for a dataset

There are two steps to enable external access for a dataset.

  1. Enable external metadata using either the pipeline configuration or a table property. The table-level setting takes precedence over the pipeline configuration when both are set and is supported for both pipeline-managed and standalone streaming table and materialized view.

    • Pipeline configuration: Set pipelines.externalMetadata.enabled to true to enable external metadata for all datasets in the pipeline. Standalone materialized views and streaming tables created with Databricks SQL do not have a pipeline configuration; use a table property instead.

      Pipeline settings UI

      In the pipeline settings, complete the following steps:

      1. Open your pipeline and click Settings.
      2. Under Configuration, add a key-value pair: Key pipelines.externalMetadata.enabled, Value true.
      3. Click Save.

      Pipeline configuration JSON

      In the configuration section of your pipeline JSON, add:

      {
  "configuration": {
    "pipelines.externalMetadata.enabled": "true"
  }
}

    • Table property: Add the following property to the streaming table or materialized view definition. For Lakeflow Connect Pipelines, see Set Delta table properties.

      CREATE OR REFRESH [MATERIALIZED VIEW | STREAMING TABLE] tbl_name
TBLPROPERTIES('pipelines.externalMetadata.enabled' = 'true')

    After saving the configuration, run or restart the pipeline to apply the changes:

    • Triggered pipelines: Run the pipeline once.
    • Continuous pipelines: Stop and restart the pipeline.

    For standalone Databricks SQL objects, use CREATE OR REPLACE MATERIALIZED VIEW or CREATE OR REFRESH STREAMING TABLE with the table property. The create or refresh statement applies the property.

  2. If you plan to read the dataset with a modern Iceberg client, add the following UniForm Iceberg V3 properties in addition to the external metadata property. For Lakeflow Connect Pipelines, see Set Delta table properties.

    Property Use
    'pipelines.externalMetadata.enabled' = 'true' Enable external access for the table. This table-level setting takes precedence over the pipeline configuration when both are set.
    'delta.columnMapping.mode' = 'name' Column mapping is required for Iceberg.
    'delta.enableRowTracking' = 'true' Enable row tracking for Iceberg reads.
    'delta.universalFormat.enabledFormats' = 'iceberg' Enable Iceberg reads.
    'delta.enableIcebergCompatV3' = 'true' Use Iceberg V3 for Iceberg reads. 
    CREATE OR REFRESH [MATERIALIZED VIEW | STREAMING TABLE] tbl_name
TBLPROPERTIES(
  'delta.columnMapping.mode' = 'name',
  'delta.enableRowTracking' = 'true',
  'delta.enableIcebergCompatV3' = 'true',
  'delta.universalFormat.enabledFormats' = 'iceberg',
  'pipelines.externalMetadata.enabled' = 'true')

    For materialized views, you can use the equivalent USING ICEBERG syntax instead.

    CREATE OR REFRESH MATERIALIZED VIEW tbl_name USING ICEBERG

    For pipeline-managed datasets, use the pipeline update instructions above to apply the Iceberg properties. For standalone Databricks SQL objects, rerun the object definition with the updated properties. Use CREATE OR REPLACE MATERIALIZED VIEW for a materialized view or CREATE OR REFRESH STREAMING TABLE for a streaming table. To see the properties of your dataset, use the DESCRIBE DETAIL or DESCRIBE EXTENDED SQL statements.

Troubleshooting external data access

If you think the external metadata is stale, a principal with the MODIFY privilege on the table can manually trigger the metadata update on shared cluster compute using Databricks Runtime 17.3 or above:

REPAIR TABLE <catalog>.<schema>.<table-name> SYNC METADATA;

You can check the presence of the Iceberg metadata in the Catalog Explorer UI on the table details page. Alternatively, run the following commands in the SQL editor or a Azure Databricks notebook:

DESCRIBE DETAIL <catalog>.<schema>.<table-name>;
DESCRIBE EXTENDED <catalog>.<schema>.<table-name>;

For a streaming table, compare the Iceberg metadata version with the latest streaming table version. Version comparison for materialized views is not available yet.

Reading data from external clients

The following sections provide examples of how to read your dataset from different clients and environments.

For setup details, see Delta client access and Iceberg client access.

Use Unity REST API with the Spark Delta Reader

Use Apache Sparkâ„Ē version 4.0 or later. You can download from https://spark.apache.org/downloads.html.

  1. Based on your cloud provider, run the following command to start a Spark SQL shell with Delta 4.0 and Unity Catalog.

    AWS

    bin/spark-sql \
    --packages org.apache.spark:spark-hadoop-cloud_2.13:4.0.0,io.unitycatalog:unitycatalog-spark_2.13:0.3.1 \
    --conf spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension \
    --conf spark.sql.catalog.spark_catalog=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.hadoop.fs.s3.impl=org.apache.hadoop.fs.s3a.S3AFileSystem \
    --conf spark.sql.catalog.<uc-catalog-name>=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.sql.catalog.<uc-catalog-name>.uri=<workspace_url> \
    --conf spark.sql.catalog.<uc-catalog-name>.token=<PAT> \
    --conf spark.sql.defaultCatalog=<uc-catalog-name>

    Azure

    bin/spark-sql \
    --packages org.apache.hadoop:hadoop-azure:3.3.6,io.unitycatalog:unitycatalog-spark_2.13:0.3.1 \
    --conf spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension \
    --conf spark.sql.catalog.spark_catalog=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.sql.catalog.<uc-catalog-name>=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.sql.catalog.<uc-catalog-name>.uri=<workspace_url> \
    --conf spark.sql.catalog.<uc-catalog-name>.token=<PAT> \
    --conf spark.sql.defaultCatalog=<uc-catalog-name>

    GCP

    bin/spark-sql \
    --packages io.unitycatalog:unitycatalog-spark_2.13:0.3.1  \
    --conf spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension \
    --conf spark.sql.catalog.spark_catalog=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.hadoop.fs.gs.impl=com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystem \
    --conf spark.hadoop.fs.AbstractFileSystem.gs.impl=com.google.cloud.hadoop.fs.gcs.GoogleHadoopFS \
    --conf spark.sql.catalog.<uc-catalog-name>=io.unitycatalog.spark.UCSingleCatalog \
    --conf spark.sql.catalog.<uc-catalog-name>.uri=<workspace_url> \
    --conf spark.sql.catalog.<uc-catalog-name>.token=<PAT> \
    --conf spark.sql.defaultCatalog=<uc-catalog-name>

  2. From the SQL shell, you can now access your dataset with Spark SQL. For example:

    spark-sql ()> SELECT * FROM <uc-catalog>.<uc-schema>.<uc-table-name>;

Use the Snowflake Iceberg Reader

Within Snowflake, you can use the Iceberg Reader. This requires Iceberg v3 support in Snowflake.

  1. Set up the Iceberg REST catalog in Snowflake.

    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-table-name>';

  2. Access your dataset from Snowflake SQL.

    ALTER ICEBERG TABLE my_table REFRESH;
SELECT * FROM my_table;

Use the Iceberg REST catalog with Spark Iceberg reader

Use Apache Sparkâ„Ē version 4.0 or later. You can download from https://spark.apache.org/downloads.html.

  1. In AWS, run the following command to start a Spark SQL shell with Iceberg v3.

    bin/spark-sql \
  --packages org.apache.iceberg:iceberg-spark-runtime-4.0_2.13:1.10.0,org.apache.iceberg:iceberg-aws-bundle:1.10.0 \
  --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions \
  --conf spark.sql.catalog.<uc-catalog-name>=org.apache.iceberg.spark.SparkCatalog \
  --conf spark.sql.catalog.<uc-catalog-name>.io-impl=org.apache.iceberg.aws.s3.S3FileIO \
  --conf spark.sql.catalog.<uc-catalog-name>.type=rest \
  --conf spark.sql.catalog.<uc-catalog-name>.uri=<workspace_url>/api/2.1/unity-catalog/iceberg-rest \
  --conf spark.sql.catalog.<uc-catalog-name>.token='<PAT>' \
  --conf spark.sql.catalog.<uc-catalog-name>.warehouse=<uc-catalog-name> \
  --conf spark.sql.iceberg.vectorization.enabled=false

  2. Access your dataset from Spark SQL.

    spark-sql ()> SELECT * FROM <uc-catalog>.<uc-schema>.<uc-table-name>;

Migrate from compatibility mode

If you are currently sharing a dataset using compatibility mode, you can migrate to using external data access.

  1. Enable this feature following the steps in How to enable access for a dataset.
  2. Disable compatibility mode. See Disable Compatibility Mode

Limitations

The following are known limitations with external data access for streaming tables and materialized views.

  • External Writes: External writes to pipeline datasets are not supported.
  • Path-Based Access: External readers that require path-based access (reading directly through a storage location instead of the UC API interface) are not supported. To support path-based access, you can use compatibility mode, which does support path-based access, but requires a full copy of the dataset.
  • Security Features: Supporting row-level security or column level masking from external reads is not supported.
  • Time travel: Time travel via this feature is not supported.
  • Catalog commits (beta): Catalog commits are not compatible with external data access. To use external data access on a streaming table or materialized view, you must first disable catalog commits.
  • Fabric: Reading from Microsoft Fabric is not supported.
  • Last updated on