External Table Lineage Extraction

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by Alation.

Important

This feature is available from Azure SQL DB connector version 1.5.0 or higher.

Overview

Azure SQL Database supports external tables that reference tables from other Azure SQL databases using elastic queries. The Azure SQL DB OCF connector can extract lineage information for these external tables, showing the relationship between local external tables and their remote source tables.

When enabled, the connector extracts both table-level and column-level lineage, allowing you to trace data flow between Azure SQL databases in your Alation catalog.

  • Source: The remote table in another Azure SQL database (identified by the external data source location)

  • Target: The local external table definition in your Azure SQL database

Prerequisites

To extract external table lineage, the service account requires the VIEW DEFINITION permission on the schemas that contain external tables. This permission grants access to the required system views (sys.external_tables, sys.external_data_sources, and sys.columns).

For details on granting permissions, see Prerequisites.

Enable External Table Lineage Extraction

External table lineage extraction is disabled by default. To enable it:

  1. On the Settings page of your Azure SQL DB data source, go to the Metadata Extraction tab.

  2. Under the Customize extraction scope section, enable the Extract external table lineage toggle.

  3. Run metadata extraction.

When enabled, lineage extraction runs automatically as part of the metadata extraction job. The connector extracts lineage for external tables in the schemas selected for extraction within the configured database.

Schema Filtering

The connector supports lineage extraction for external tables with schema filtering, which involves selecting schemas in the Select schemas for extraction section of the metadata extraction tab. For more information, see Select Schemas for Extraction. When you select specific schemas for extraction (manually or using filters), the connector will only extract lineage for external tables in those schemas.

Lineage

After metadata extraction, lineage appears on the Lineage tab of the external table catalog pages. This includes:

  • Table-level lineage: Lineage between the remote table (source) and the local external table (target)

  • Column-level lineage: Lineage between corresponding columns in the remote table (source) and local external table (target)

Note

For complete lineage, ensure both the source Azure SQL database and the database containing the external table are cataloged in Alation, irrespective of whether the source and target tables are in the same or different Alation data sources.

Limitations

Lineage is extracted only for external tables whose external data source location (URL) contains .database.windows.net (that is, external tables that reference other Azure SQL databases).