Inquiry on Purview Lineage Limitations for Synapse Dedicated SQL and Private Endpoint SQL Server

Question

Hi,

I have a question regarding lineage support in Microsoft Purview for our current BI environment.

Our BI reports are fed from both Synapse Analytics and an Azure SQL Database. We have identified two limitations in Purview:

1. Purview does not currently link lineage back to Synapse Dedicated SQL Pools, so we cannot see end-to-end lineage from BI back through the dedicated SQL layer.

Purview is unable to extract lineage from an Azure SQL Server that is deployed behind a private endpoint, meaning that SQL Server lineage is not captured automatically.

Could you please clarify:

Is there any recommended workaround or best practice to enable lineage capture for these scenarios?

Are there plans or a roadmap for supporting Synapse Dedicated SQL Pool lineage and private endpoint SQL Server lineage extraction in Purview in the near future?

If so, could you share expected timelines or preview availability?

Thanks in advance.

Answer 1

Hi Pratik Pokharel,

you’re right on both counts: today Purview doesn’t do end-to-end lineage through Synapse Dedicated SQL Pools, nor will it automatically pull lineage from an Azure SQL Database that’s locked down behind a private endpoint. Here’s what we know today and a couple of things you can try:

Synapse Dedicated SQL Pool lineage • Current state: Purview’s built-in connectors only capture table-and-view lineage for Dedicated SQL Pools. Stored procedures, queries and the “compute” layer in Synapse Dedicated SQL Pools aren’t surfaced today, so you won’t see your BI report → Synapse SQL DW steps. • Workaround: – If you’re using Synapse pipelines or Azure Data Factory copy activities to move data out of your SQL DW into a staging store (e.g. ADLS Gen2, Blob), those activities do push lineage into Purview. You can lean on that: model your ETL as copy activities or data flows that target a supported sink. You’ll get “pipeline-level” lineage in Purview, even if you can’t see the actual SQL DW compute. – Another option is to use the Purview Lineage REST API and post custom lineage events for your SQL Pool jobs. It takes more plumbing but lets you stitch any arbitrary step into the Purview graph.

Azure SQL DB behind a private endpoint • Current state: the default “Azure-managed” integration runtime can’t scan SQL DBs that disallow public network access or sit purely behind a private endpoint. That also blocks lineage extraction. • Workaround: – Deploy a Self-Hosted Integration Runtime (SHIR) in the same VNet/subnet as your SQL DB private endpoint and your Purview ingestion private endpoint. – In Purview, configure both the account & ingestion private endpoints. – Use SQL-authentication (or a service principal + key vault) rather than MSI. Store the secret in Key Vault, give Purview get/list on that secret, and reference it in your scan credential. – Make sure your SHIR can resolve the private endpoint FQDN via your DNS (Azure Private DNS or custom). This pattern will let you scan the SQL DB and pull in table-and-view lineage. Stored-procedure lineage is still only supported when “Allow Azure services and resources to access this server” is enabled (i.e. public access on), so SP lineage behind a private endpoint isn’t supported yet.

Roadmap / timelines Microsoft hasn’t published a firm public preview date for Dedicated SQL Pool or private-endpoint SP lineage. Both are on the product backlog, but no ETA is available today.
I’d recommend:
• Bookmark the Purview “what’s new” release notes page (https://learn.microsoft.com/azure/purview/release-notes)
• Submit upvotes or feedback in the Purview feedback forum – it helps prioritize these features

Hope this helps you unblock your BI lineage needs. Let me know if you give one of these workarounds a try or have more questions!

Reference docs

1. How to get lineage from Azure Synapse Analytics into Purview (limitations section): https://learn.microsoft.com/purview/data-map-lineage-azure-synapse-analytics
2. Register & scan Azure SQL Database (lineage extraction preview + private-endpoint limitations): https://learn.microsoft.com/azure/purview/register-scan-azure-sql-database
3. Private Link in Purview (account, portal & ingestion endpoints): https://learn.microsoft.com/azure/purview/catalog-private-link-end-to-end
4. Scanning issues & SHIR troubleshooting: https://learn.microsoft.com/azure/purview/catalog-private-link-troubleshoot 

Answer 2

For Synapse Dedicated SQL and Azure SQL behind private endpoints, Microsoft Purview has important lineage limitations and there is no documented product roadmap or timeline in the provided material.

What is supported today

1. Synapse pipelines and activities
   • When an Azure Synapse workspace is connected to Microsoft Purview, Purview captures runtime lineage from supported Synapse pipeline activities:
     • Copy Data
     • Data Flow
   • For these activities, lineage is captured between supported data stores such as Azure SQL Database, Azure Synapse Analytics, Azure Dedicated SQL pool (formerly SQL DW), Azure Data Lake Storage, Azure Blob, and others, but only when used as table/view sources and sinks.
   • Lineage is dropped if the source or destination uses an unsupported data storage system.
2. Scope and limitations for Synapse / SQL lineage
   • For Copy/Data Flow activities, lineage is limited to table and view sources only; queries and stored procedures are not supported for lineage or scanning.
   • For Synapse tables specifically: “Currently Purview doesn't support lineage reporting for Synapse tables (LakeHouse DB/Workspace DB).”
   • In the general lineage user guide, database views used as sources are captured as tables for lineage purposes, which can result in duplicate assets (one with lineage as a table, one discovered as a view) when the database is also scanned.
3. Azure Data Factory parity
   • Azure Data Factory has the same lineage model as Synapse pipelines for Copy/Data Flow (same supported data stores and limitations), which is relevant if Data Factory is used instead of or alongside Synapse for ETL.

Workarounds / best practices from the documentation

Within the provided material, only the following patterns are explicitly supported and recommended:

1. Use supported Synapse pipeline activities and supported data stores
   • Ensure data movement into and out of Synapse Dedicated SQL pools and Azure SQL uses supported Copy/Data Flow activities with supported data stores (for example, Azure SQL Database, Azure Synapse Analytics, Azure Dedicated SQL pool, ADLS Gen1/Gen2, Blob, etc.).
   • Avoid using queries or stored procedures as lineage sources/sinks, because lineage is limited to table and view sources only.
2. Rely on execution-time lineage from processing systems
   • Treat Synapse pipelines (and Azure Data Factory, if used) as the primary lineage providers. When these run, Purview automatically ingests metadata about source data, output data, and the activity into the Data Map and stitches it with scanned data sources.
3. Accept current gaps for Synapse tables and detailed transformations
   • For Data Flows, Purview only shows source and sink; detailed transformation lineage is not supported.
   • Lineage for Synapse LakeHouse/Workspace DB tables is not supported.

Regarding private endpoint Azure SQL Server

The provided content does not describe any specific support, workaround, or limitation related to scanning or lineage extraction for Azure SQL Server or Azure SQL Database when accessed via private endpoints. It only states, in general, that Purview drops lineage if the source or destination uses an unsupported data storage system, and that queries/stored procedures are not supported for lineage.

Roadmap and timelines

The context does not contain any roadmap, preview announcements, or timelines for:

• Extending lineage support specifically for Synapse Dedicated SQL Pools beyond the current Copy/Data Flow activity model.
• Adding lineage extraction support for Azure SQL Server or Azure SQL Database behind private endpoints.

No official dates, previews, or future commitments are provided in the referenced documentation.

---

References:

• How to get lineage from Azure Synapse Analytics into Microsoft Purview
• Data lineage user guide for classic Data Catalog
• How to connect Azure Data Factory and Microsoft Purview
• Data Governance for Azure Synapse Analytics Using Azure Purview: Resources and Best Practices - Microsoft Q&A