Design and implement data storage →hardMultiple ChoiceObjective-mapped

DP-203 Design and implement data storage Practice Question

Exhibit

Refer to the exhibit.

```
CREATE EXTERNAL DATA SOURCE SalesData
WITH (
    LOCATION = 'https://datalakegen2.dfs.core.windows.net/sales',
    CREDENTIAL = StorageCred
);

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,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/year=2024/',
    DATA_SOURCE = SalesData,
    FILE_FORMAT = ParquetFormat
);
```

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?

⚠ Common exam trap

Candidates often assume a missing or misconfigured schema parameter (like SCHEMA_NAME) would cause zero rows, but in reality, permission issues are the primary cause of empty results when the data path is correct and the file format is supported.

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 credential used to access ADLS Gen2 does not have sufficient permissions.

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.

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 credential used to access ADLS Gen2 does not have sufficient permissions.
Why this is correct
Insufficient permissions (e.g., missing Storage Blob Data Reader role) cause zero rows.
✗
The serverless SQL pool does not support reading Parquet files.
Why it's wrong here
Serverless SQL pool supports Parquet.
✗
The external table definition is missing the SCHEMA_NAME parameter.
Why it's wrong here
SCHEMA_NAME is optional.
✗
The DATA_COMPRESSION setting is incompatible with Parquet files.
Why it's wrong here
Snappy compression is standard for Parquet.

Visual reference

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

DP-203 Design and implement data storage Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Cloud Service Model Comparison

Related to this question