Understanding External Table Read-Only Behavior in Azure Synapse Serverless SQL
Exhibit
Refer to the exhibit. CREATE EXTERNAL DATA SOURCE MyDataSource WITH ( LOCATION = 'abfss://container@storageaccount.dfs.core.windows.net', TYPE = HADOOP, CREDENTIAL = MyCredential ); CREATE EXTERNAL FILE FORMAT ParquetFormat WITH ( FORMAT_TYPE = PARQUET, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' ); CREATE EXTERNAL TABLE dbo.Sales ( SaleID INT, ProductID INT, Quantity INT, SaleDate DATE ) WITH ( LOCATION = '/sales/', DATA_SOURCE = MyDataSource, FILE_FORMAT = ParquetFormat );
Refer to the exhibit. A data engineer creates an external table in Azure Synapse Serverless SQL. Which statement about this table is correct?
Quick Answer
External tables are read-only because of what they actually are under the hood: a relational, queryable view laid on top of files that live somewhere else, such as Azure Data Lake Storage Gen2 or Blob Storage. Serverless SQL pool has no storage engine of its own; it does not own or manage the underlying data files the way a traditional database owns its tables, so it has no mechanism to write changes back into them. That is why you can query an external table with ordinary T-SQL and get relational results, but cannot INSERT, UPDATE, DELETE, or run DDL against it, since any of those operations would require modifying data serverless SQL pool does not control. The table definition itself, built with CREATE EXTERNAL TABLE or queried ad hoc via OPENROWSET, essentially tells the engine how to interpret the files at a given path; it is metadata and a schema mapping, not a data store. If the underlying data needs to change, that has to happen at the source, by modifying the files directly, not through the external table. Any exam question describing an external table in a serverless SQL pool context should point you toward this read-only, files-in-place nature as the defining constraint, since it is the trade-off accepted in exchange for querying data without ever loading or duplicating it.
⚠ Common exam trap
A common mix-up: candidates confuse external tables in Synapse Serverless SQL with external tables in dedicated SQL pools (which also support PolyBase with Hadoop connectors) and mistakenly think they can write to or index the table, or they misremember the required data source TYPE for ADLS Gen2.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
The table is read-only
External tables in Azure Synapse Serverless SQL are read-only because they provide a relational abstraction over data stored externally (e.g., in Azure Data Lake Storage Gen2 or Blob Storage). You cannot perform INSERT, UPDATE, DELETE, or DDL modifications on the underlying data through the external table; it is designed solely for querying with T-SQL. This is a fundamental constraint of the serverless SQL pool architecture, which uses the OPENROWSET or CREATE EXTERNAL TABLE syntax to read files in place without a storage engine.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The table supports indexing for performance
Why it's wrong here
External tables in serverless SQL do not support indexes.
- ✗
The external data source TYPE must be 'HADOOP' for Azure Data Lake Storage Gen2
Why it's wrong here
TYPE=HADOOP is acceptable; it's not incorrect.
- ✗
The table references a single Parquet file named 'Sales.parquet'
Why it's wrong here
LOCATION='/sales/' is a folder, not a single file.
- ✓
The table is read-only
Why this is correct
External tables are read-only; modifications must be done to underlying files.
Quick reference
Cloud Service Model Comparison
| Model | You Manage | Provider Manages | Examples |
|---|---|---|---|
| IaaS | OS, runtime, apps, data | Hardware, hypervisor, networking | EC2, Azure VMs, GCP Compute Engine |
| PaaS | Apps and data | OS, runtime, middleware, hardware | Elastic Beanstalk, Azure App Service |
| SaaS | Data and settings only | Everything else | Microsoft 365, Salesforce, Workday |
| FaaS / Serverless | Function code only | Infra, scaling, runtime | Lambda, Azure Functions, Cloud Run |
| CaaS | Containers and apps | Kubernetes, OS, hardware | EKS, AKS, GKE |
Go deeper
Related to this question
About these practice questions
One of 760 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-203
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?
hard- ✓ A.The credential used to access ADLS Gen2 does not have sufficient permissions.
- B.The serverless SQL pool does not support reading Parquet files.
- C.The external table definition is missing the SCHEMA_NAME parameter.
- D.The DATA_COMPRESSION setting is incompatible with Parquet files.
Why A: The most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.