Benefits of using External tables in Staging - Synapse Data Warehouse

Question

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!

Answer 1

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 or upvote 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