Can I create external views in synapse from multiple data sources?

Question

Hi, I have data from different azure regions and my idea to decrease overhead is to use just synapse workspace with multiple external data sources and create the external tables for each of them, since I can't put all the data into a same location for compliance rules

After that step, create a serverless view with the union of each of the data sources to allow an easy data consumption in Power BI and avoid having multiple connections

Is this plan feasible or is there a better approach?

Answer 1

@Gustavo Adolfo Alemán Sánchez

Thanks for using MS Q&A platform and posting your query.

Your plan to use Azure Synapse Workspace with multiple external data sources and create serverless views for Power BI consumption is absolutely feasible and a good approach for your scenario with compliance restrictions. Here's a breakdown of the pros and cons:

Pros:

• Reduced overhead: By keeping data in its original location, you avoid unnecessary data movement and associated costs.
• Compliance adherence: You can maintain compliance with regional data residency requirements.
• Centralized management: Synapse Workspace provides a central platform to manage and query data from different sources.

Simplified Power BI consumption: Serverless views offer a single connection point for Power BI, improving data accessibility.

Cons:

• Potential latency: Querying data across regions might introduce slight latency compared to co-located data.

Increased complexity: Managing linked services and external tables for various sources adds some complexity.

Alternative approaches (consider if latency is a major concern):

Data federation: Azure Data Factory can virtually integrate data without physically moving it, offering a lower latency option. Hybrid approach: Combine Synapse with Azure Data Share to securely share data across regions while keeping it in its original location.

Overall, your plan is a solid solution for your specific needs. If latency is a critical factor, explore the alternative approaches as well.

Here are some additional resources that you might find helpful:

• Use external tables with Synapse SQL: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables
• Create serverless views in Synapse Workspace: (Search for "serverless views in synapse workspace" on Microsoft docs.microsoft.com)pen_spark
• Azure Data Factory: https://learn.microsoft.com/en-us/azure/data-factory/
• Azure Data Share: https://azure.microsoft.com/en-us/products/data-share

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.