Develop data processing →mediumMultiple ChoiceObjective-mapped

DP-203 Develop data processing Practice Question

Exhibit

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    LOCATION = 'abfss://container@storageaccount.dfs.core.windows.net',
    CREDENTIAL = MyCredential
);

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

CREATE EXTERNAL TABLE dbo.ExternalSales
(
    ProductID INT,
    SaleDate DATE,
    Quantity INT,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/sales/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = ParquetFormat
);

Refer to the exhibit. You have created an external table in Azure Synapse serverless SQL pool as shown. You run a query: SELECT ProductID, SUM(Amount) FROM dbo.ExternalSales WHERE SaleDate > '2024-01-01' GROUP BY ProductID. The query is slow and scans all files in the /sales/ folder, which contains data from 2023 and 2024. The files are partitioned by year and month in the folder structure, e.g., /sales/year=2023/month=01/. What should you do to improve query performance?

⚠ Common exam trap

Many exam-takers confuse creating statistics (which helps cardinality estimation but not data skipping) with partition elimination (which physically reduces data scanned), or they assume any column partition will work without matching the folder structure.

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

✓

Recreate the external table with a partition definition on SaleDate column using the folder structure

The query performance is slow due to full file scanning. By recreating the external table with a partition definition on the SaleDate column that maps to the folder structure (e.g., /sales/year=2023/month=01/), Azure Synapse serverless SQL pool can perform partition elimination, reading only the relevant partitions for the WHERE clause filter (SaleDate > '2024-01-01'). This drastically reduces the amount of data scanned, improving query speed.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Recreate the external table with a partition definition on SaleDate column using the folder structure
Why this is correct
By defining partitions using the folder structure, serverless SQL can skip partitions that don't match the filter.
✗
Recreate the external table with a partition on ProductID
Why it's wrong here
The folder structure is by date, not ProductID; partition must align with folder structure.
✗
Create statistics on the SaleDate column
Why it's wrong here
Statistics improve query plans but do not enable partition elimination.
✗
Change the file format to CSV to improve read performance
Why it's wrong here
Parquet is typically faster than CSV; format is not the issue.

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 Develop data processing Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Cloud Service Model Comparison

Related to this question