Benefits of using External tables in Staging - Synapse Data Warehouse

kosmos 246 Reputation points
2022-10-24T08:44:53.167+00:00

Background: Data Warehouse Project. Different pipelines are ingesting data into Synapse Analytics.

This article mentions the usage of External Tables in the Staging Layer when loading the data into the Data Warehouse.

External tables allow you to query directly from Azure Storage, which is the main benefit I see.

However I created a pipeline that reads Azure Storage files and loads them directly to the SQL dedicated pool.

Why would external tables be here of benefit ?

Thanks in advance!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 31,731 Reputation points Microsoft Employee
    2022-10-25T16:53:35.797+00:00

    Hi @kosmos ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your query, you want to know the significance of External table in Azure Synapse analytics. Please let me know if my understanding about your query is incorrect.

    External table is a reference point to the files in Azure datalake storage. Benefit of using external table is you don't have to pay for the storage of data in a physical table , so the storage cost would be reduced . External tables are also useful if you want to use tools, such as Power BI, in conjunction with Synapse SQL pool.

    In case your requirement is to use physical table for creating views on top of it or creating dim or fact tables in the datawarehouse, there is no need to create external table as creating a physical table would avoid the dependency on the ADLS file. Even if the file is deleted , the data in the physical table would exist, however, that is not the case in external table.

    Few references: Design tables using dedicated SQL pool in Azure Synapse Analytics
    Use external tables with Synapse SQL
    External Tables with Synapse SQL in Azure Synapse Analytics

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful