Access schema is not showing in user catalogs and only showing in system schema

Question

Access schema is not showing in user catalogs and only showing in system schema

Sri Lakshman Velugubantla 20

Hi Team,

I have enabled the access schema, which contains lineage tables, in Unity Catalog and attempted to scan the catalog. However, the lineage information was not captured. Upon checking in Unity Catalog, I found that the access schema is only available in the system schema and not under other user catalogs (e.g., p_eus is the user catalog in the screenshot). In contrast, the information_schema is present in both the system and user catalogs. I believe this might be the issue: Purview is only scanning the information_schema and capturing metadata but not capturing lineage because the access schema is not visible under the user catalogs. Please refer to the attached screenshot for reference. If this is the cause of the issue, could you please help me resolve it?
If not, why purview is not capturing the lineage from Unity Catalog

I have verified the data in the lineage tables (specifically, the table_lineage table). In this data, only the source_path and target_path fields are populated, while other fields such as source_table_name, source_table_schema, source_table_catalog, target_table_name, target_table_schema, and target_table_catalog contain null values. This is because we are using external tables created on top of ADLS files.

Could this be causing an issue for Purview when capturing lineage information?

If the issue lies with point number 2, could we create a view with lineage data in the information_schema, insert the lineage data into it, and then scan Unity Catalog using Purview? Would Purview treat this as a metadata table and capture the lineage information in Azure Purview?
If point 3 works, what would the column names, data types, and data format need to be for the view in order for Purview to correctly capture lineage in Azure Purview?

I have asked you four questions so can you please answer it one by one so, it will be easy to understand.

Thanks in Advance

Venkat Reddy Navari 1,030 Reputation points Microsoft External Staff

2025-03-18T15:07:12.26+00:00
Hi Sri Lakshman Velugubantla

Why is the access schema only visible in the system schema and not under user catalogs like p_eus, while the information_schema appears in both

The information_schema is a system schema designed to be accessible in both system and user catalogs, as it provides metadata about tables and schemas.

The access schema, which contains lineage data, is likely treated more as a system schema and may not be shown in user catalogs for a couple of reasons:

It’s intended for tracking system-level access or lineage, and may not be meant for direct user access.

Unity Catalog might restrict its visibility in user catalogs for security or organizational purposes.

So, it’s expected to see the access schema only under the system schemas.

Why isn't Purview capturing lineage from Unity Catalog?

There are a few reasons for this:

Access Schema Visibility: Purview might only be scanning the information_schema and not the system schemas (where your access schema is), it’s likely missing the lineage data from there.

External Tables: Since you're using external tables over ADLS, they might not have all the necessary metadata to track lineage accurately. If fields like source_table_name and target_table_name are null, it could indicate that the external tables are lacking crucial information, which makes it difficult for Purview to capture complete lineage data.

It seems Purview is missing the lineage either because it's not scanning the correct schemas or because the external tables don't contain the required metadata.

Can we create a view with lineage data in the information_schema and have Purview capture it?

If you create a view in the information_schema and insert the appropriate lineage data, Purview should be able to scan it and capture the lineage. Since the information_schema is already part of the system schemas that Purview scans, this approach should work, as long as the view is structured correctly. Just ensure that the data in the view is complete and formatted in a way that Purview expects for lineage tracking.

What columns and data types should the view have for Purview to capture lineage correctly?

Columns:

source_path (String): The location of the source table. target_path (String): The location of the target table. source_table_name (String): The name of the source table. source_table_schema (String): The schema of the source table. source_table_catalog (String): The catalog of the source table. target_table_name (String): The name of the target table. target_table_schema (String): The schema of the target table. target_table_catalog (String): The catalog of the target table. lineage_id (String or UUID): A unique ID for each lineage entry (optional but useful for tracking).

Data Types:

Use String or VARCHAR for the path, table, and schema/catalog columns.

For lineage_id, you can use UUID or String depending on your setup.

Data Format:

The format should be something that Unity Catalog and Purview can read—Parquet or Delta are usually good choices, but CSV or JSON might work too.

Make sure the lineage data is complete and doesn’t have null values in key columns like source_table_name or target_table_name.

Example SQL:

CREATE VIEW information_schema.lineage_view AS SELECT source_path, target_path, source_table_name, source_table_schema, source_table_catalog, target_table_name, target_table_schema, target_table_catalog FROM some_lineage_table WHERE lineage_id IS NOT NULL;

Once the view is created, Purview should be able to scan it and capture the lineage data

For more details, please refer to the following documents:

Unity Catalog Overview

Azure Purview - Data Lineage

I hope this helps. Please let us know if you have any further questions.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Sri Lakshman Velugubantla 20 Reputation points

2025-03-18T16:08:30.6266667+00:00

Hi @Venkat Reddy Navari ,

I tried to create a table/view in the information_schema, but I was unable to do so. I understand that we cannot create tables or views in system catalogs, as those tables are managed by Databricks.

is it correct? since tables are managed by Databricks in system schema i'm not able create or any permissions needed to do that?

If that’s the case, how can I achieve automated lineage integration into Azure Purview?
Venkat Reddy Navari 1,030 Reputation points Microsoft External Staff

2025-03-19T14:34:04.9566667+00:00
Hi Sri Lakshman Velugubantla
You're absolutely correct that system schemas, including information_schema, are managed by Databricks, and as such, direct modifications like creating tables or views within these schemas are not permitted. This is a common characteristic of system catalogs, as they are designed to store and manage metadata without direct user modifications.

If that’s the case, how can I achieve automated lineage integration into Azure Purview?

To achieve automated lineage integration into Azure Purview, here are a few alternative approaches you might consider:

Custom Metadata Table: Instead of trying to create a view in information_schema, you can create a custom metadata table within one of your user catalogs. This table can be populated with lineage information from your lineage tables. Once you have this data in your user catalog, you can configure Purview to scan it and capture the lineage.

Data Pipeline: If you have a data pipeline or ETL process, you could automate the extraction of lineage data from your lineage tables and load it into your custom metadata table. This ensures that the lineage information is always up-to-date and available for Purview to scan.

External Tools: If your organization uses any data governance or lineage tools that integrate with Azure Purview, you might explore those as an alternative. Some of these tools can automate the lineage capture process and ensure that the necessary metadata is available for Purview.

APIs and SDKs: If your team has development resources available, consider using Azure Purview’s REST APIs or SDKs to programmatically submit lineage data into Purview. This gives you more flexibility and can be customized your specific requirements.

For more details, please refer to the following documents: https://learn.microsoft.com/en-us/purview/concept-data-lineage

I hope this helps. Please let us know if you have any further questions.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Sri Lakshman Velugubantla 20 Reputation points

2025-03-20T04:49:56.4966667+00:00

Hi @Venkat Reddy Navari ,

I tried to create a lineage table under my user catalog, but Purview is not treating it as a metadata table. Instead, it is being recognized as a normal table. While scanning, Purview is only pulling the lineage table metadata like table name and its schema.

So, can you please suggest any solution other than this.
Venkat Reddy Navari 1,030 Reputation points Microsoft External Staff

2025-03-20T11:09:03.26+00:00
@Sri Lakshman Velugubantla

Since Purview’s default scanning doesn’t capture the required lineage data, an alternative approach is to extract the lineage information from Unity Catalog’s access schema and restructure it to match Purview’s expected format.

Developing a Custom Lineage Extraction Process:

This can be done by leveraging Azure Data Factory, Databricks, or Azure Functions to process the data and store it in an external table that Purview can scan. These tools allow for automated extraction, transformation, and storage, ensuring that the lineage data is available in a format compatible with Purview.

Azure Purview supports the Open Lineage standard, a widely used format for tracking data lineage. By exporting lineage data from Unity Catalog and converting it into this format, it can be seamlessly integrated into Purview using the Purview Custom Lineage Connector.

Lineage tables in Unity Catalog’s system schema may not be scanned by Purview, but the data can still be extracted using Databricks system queries. Storing this extracted lineage information in a separate table within a user-accessible catalog allows Purview to detect and process it. Example Query:
SELECT * FROM system.access.lineage_table WHERE event_time >= current_date - INTERVAL '7' DAY;
Store the result in a Delta table outside the system schema and configure Purview to scan it.

When Purview does not automatically detect lineage data, it can be manually ingested using Azure Purview’s Apache Atlas API. This requires structuring the lineage data as Atlas Lineage Entities and submitting it through the Atlas REST API for proper integration. Example API Call to Ingest Lineage Data:
curl -X POST "https://<purview-account-name>.catalog.purview.azure.com/api/atlas/v2/lineage" \ -H "Authorization: Bearer <token>" \ -H "Content-Type: application/json" \ -d @lineage_payload.json

This method enables Purview to accurately detect and incorporate the lineage data.

I hope this helps. Please let us know if you have any further questions.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Venkat Reddy Navari 1,030 Reputation points Microsoft External Staff

2025-03-21T11:21:11.3266667+00:00

Sri Lakshman Velugubantla We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Sri Lakshman Velugubantla 20 Reputation points

2025-03-18T16:08:30.6266667+00:00

Hi @Venkat Reddy Navari ,

I tried to create a table/view in the information_schema, but I was unable to do so. I understand that we cannot create tables or views in system catalogs, as those tables are managed by Databricks.

is it correct? since tables are managed by Databricks in system schema i'm not able create or any permissions needed to do that?

If that’s the case, how can I achieve automated lineage integration into Azure Purview?
Sri Lakshman Velugubantla 20 Reputation points

2025-03-20T04:49:56.4966667+00:00

Hi @Venkat Reddy Navari ,

I tried to create a lineage table under my user catalog, but Purview is not treating it as a metadata table. Instead, it is being recognized as a normal table. While scanning, Purview is only pulling the lineage table metadata like table name and its schema.

So, can you please suggest any solution other than this.
Venkat Reddy Navari 1,030 Reputation points Microsoft External Staff

2025-03-21T11:21:11.3266667+00:00

Sri Lakshman Velugubantla We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Access schema is not showing in user catalogs and only showing in system schema

Your answer