Microsoft Azure Data Engineer Associate DP-203 DP-203 Questions 751–760 | Page 11/11

751

MCQhard

Refer to the exhibit. You created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files. Queries return no rows even though the files exist. What is the most likely issue?

A.The CREDENTIAL is missing

B.The FILE_FORMAT is incorrectly specified

C.The DATA_COMPRESSION is not supported for Parquet

D.The LOCATION path in the external table is relative to the data source, but the data source points to the wrong container or folder

AnswerD

The data source points to 'sales' container, table location adds 'parquet/sales', likely the files are not there.

Why this answer

The most likely issue is that the LOCATION path in the external table is relative to the data source. In the exhibit, the external data source points to the root container 'sales', and the external table LOCATION is 'parquet/sales/'. The combined path becomes 'sales/parquet/sales/', which is likely incorrect.

The correct data source should point to the container containing the 'parquet' folder, or the LOCATION should be adjusted. Option A is incorrect because CREDENTIAL is already defined. Option B is incorrect because FILE_FORMAT is specified and valid for Parquet.

Option C is incorrect because DATA_COMPRESSION is supported for Parquet.

Full explanation →

752

MCQhard

You are using Azure Stream Analytics to process real-time temperature data from IoT devices. The output must be written to Azure SQL Database. The job has been running successfully for weeks, but recently you notice that the output data has duplicate rows. The input events are unique. The job uses a windowed aggregation (TumblingWindow). What is the most likely cause of duplicates?

A.The job is not handling late-arriving events.

B.The job is being restarted and reprocessing data.

C.The input event hub is receiving duplicate events.

D.The tumbling window size is too small.

AnswerB

Restart can cause reprocessing and duplicate output without idempotent writes.

Why this answer

When an Azure Stream Analytics job is restarted, it may reprocess data from the last checkpoint or from the beginning of the input stream, depending on the configured start time and output policy. This reprocessing can cause duplicate rows in the output, especially when using windowed aggregations like TumblingWindow, because the same events are aggregated again and written to Azure SQL Database without deduplication logic.

Exam trap

The trap here is that candidates often assume duplicates are caused by late-arriving events or input issues, but the core concept is that job restarts in Stream Analytics can reprocess data, and without idempotent output, duplicates are introduced.

How to eliminate wrong answers

Option A is wrong because late-arriving events are handled by the 'late arrival policy' in Stream Analytics, which can adjust window boundaries but does not inherently cause duplicates; duplicates arise from reprocessing, not from late data. Option C is wrong because the question explicitly states that input events are unique, so the input event hub is not receiving duplicate events. Option D is wrong because the tumbling window size being too small would affect the granularity of aggregations, not cause duplicate rows; duplicates are a result of reprocessing, not window size.

Full explanation →

753

MCQmedium

Your organization is implementing a data lake using Azure Data Lake Storage Gen2. You have a folder structure like '/data/landing/' for raw data and '/data/curated/' for cleaned data. The data is ingested daily from various sources. You need to ensure that data in the curated zone is immutable and cannot be modified or deleted by anyone, including administrators, for compliance reasons. However, data in the landing zone should be modifiable. What should you do?

A.Enable immutable storage with a time-based retention policy on the curated zone container

B.Remove the 'Delete' permission from the storage account key

C.Set ACLs on the curated zone folder to deny write and delete for all users

D.Use Azure RBAC to deny delete and write permissions for all users on the curated zone folder

AnswerA

Immutable storage prevents any modification or deletion until the retention period expires.

Why this answer

Azure Data Lake Storage Gen2 supports immutable storage at the container level, which enforces a time-based retention policy that prevents any data from being modified or deleted—even by administrators—until the retention period expires. This directly meets the compliance requirement for the curated zone, while leaving the landing zone container unaffected and modifiable.

Exam trap

The trap here is that candidates often assume ACLs or RBAC alone can enforce immutability, but they fail to recognize that only container-level immutable storage provides the WORM guarantee that cannot be overridden by administrators or privileged accounts.

How to eliminate wrong answers

Option B is wrong because removing the 'Delete' permission from the storage account key does not prevent modifications (overwrites) and does not block privileged users like administrators who have other access methods (e.g., Azure RBAC, managed identities). Option C is wrong because ACLs on a folder can be overridden by users with higher-level permissions (e.g., storage account key, RBAC roles) and do not provide the legal hold or compliance-grade immutability required. Option D is wrong because Azure RBAC deny assignments can be bypassed by users with Owner or Contributor roles at a higher scope, and they do not enforce the same write-once-read-many (WORM) behavior as immutable storage; RBAC alone cannot prevent deletion by storage account key holders or service administrators.

Full explanation →

754

MCQeasy

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

A.Azure SQL Database

B.Azure Blob Storage

C.Azure Event Hubs

D.Azure Data Lake Storage

AnswerC

Event Hubs is designed for high-throughput data ingestion from IoT devices.

Why this answer

Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Exam trap

The trap here is that candidates confuse the ingestion layer with the storage layer, choosing Azure Blob Storage or Data Lake Storage because they think 'store data' means persistent storage, but the question specifically asks for the ingestion layer where real-time, low-latency streaming is required, which Event Hubs uniquely provides.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational OLTP store optimized for structured queries and ACID transactions, not for high-velocity, schema-less JSON ingestion at millions of events per second, and it would introduce latency and cost bottlenecks. Option B is wrong because Azure Blob Storage is an object store designed for batch and large-file storage, not for real-time, per-second event ingestion; it lacks native streaming ingestion, pub-sub semantics, and sub-second latency for dashboards. Option D is wrong because Azure Data Lake Storage is a hierarchical file system optimized for analytics on large datasets, not for real-time event ingestion; it is typically used as a destination for data after it has been processed or captured from a streaming source like Event Hubs.

Full explanation →

755

Multi-Selecthard

Which THREE metrics from Azure Monitor should be used to diagnose performance bottlenecks in an Azure Data Factory pipeline?

Select 3 answers

A.Pipeline Succeeded Rerun Count

B.Blob Capacity

C.Activity Duration

D.SQL Pool DWU Used

E.Data Integration Unit (DIU) Consumption

AnswersA, C, E

High rerun count indicates failures and potential bottlenecks.

Why this answer

Pipeline Succeeded Rerun Count (A) is correct because a high number of reruns indicates that the pipeline is repeatedly failing and retrying, which directly points to a performance bottleneck such as resource contention or throttling. This metric helps identify pipelines that are not completing successfully on the first attempt, signaling underlying issues that degrade throughput.

Exam trap

The trap here is that candidates often confuse storage-level metrics (like Blob Capacity) or data warehouse metrics (like DWU Used) with pipeline-specific performance indicators, but the question explicitly asks for metrics that diagnose bottlenecks in the pipeline execution itself, not in downstream storage or compute services.

Full explanation →

756

MCQhard

You are a data engineer for a large e-commerce company. You have an Azure Synapse Analytics dedicated SQL pool that stores transactional data. The pool is currently at DWU1000c. You have a critical dashboard that runs a complex query every 5 minutes. The query scans a large fact table partitioned by date. The query performance is degrading over time as data accumulates. You need to improve performance without increasing DWUs or changing the dashboard query. You also need to minimize data movement overhead. You have the following options: A. Create a columnstore index on the fact table with a partition alignment. B. Create a materialized view that aggregates the data at the partition level. C. Implement result-set caching and set the cache to expire every 5 minutes. D. Redistribute the fact table using hash distribution on the date column. Which option should you choose?

A.Create a columnstore index on the fact table with a partition alignment.

B.Create a materialized view that aggregates the data at the partition level.

C.Implement result-set caching and set the cache to expire every 5 minutes.

D.Redistribute the fact table using hash distribution on the date column.

AnswerC

The table likely already has a columnstore index; this may not provide additional benefit.

Why this answer

Result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A (columnstore index) is wrong because the table likely already has a columnstore index (the default in Synapse).

Option B (materialized view) is wrong because materialized views require maintenance and may not match the exact query. Option D (hash distribution on date) is wrong because it can cause data skew and does not reduce scan overhead as effectively as caching.

Full explanation →

757

MCQeasy

You need to transform data in Azure Synapse Analytics using a language that supports procedural logic and error handling. Which option should you use?

A.T-SQL stored procedures

B.CREATE VIEW

C.PolyBase

D.CREATE EXTERNAL TABLE

AnswerA

Supports procedural logic and error handling.

Why this answer

T-SQL stored procedures are the correct choice because they support procedural logic (e.g., IF/ELSE, loops, TRY/CATCH) and error handling within Azure Synapse Analytics dedicated SQL pools. This allows you to encapsulate complex data transformation logic, handle runtime errors gracefully, and manage transactions, which is not possible with declarative objects like views or external tables.

Exam trap

The trap here is that candidates confuse PolyBase's ability to query external data with the ability to perform procedural transformations, overlooking that PolyBase is a query engine, not a programming construct for logic and error handling.

How to eliminate wrong answers

Option B is wrong because CREATE VIEW creates a read-only virtual table that cannot contain procedural logic or error handling; it is purely declarative. Option C is wrong because PolyBase is a data virtualization technology for querying external data sources (e.g., Azure Blob Storage) using T-SQL, but it does not support procedural logic or error handling itself. Option D is wrong because CREATE EXTERNAL TABLE defines a schema for external data but provides no procedural capabilities or error handling; it is a metadata object for PolyBase queries.

Full explanation →

758

MCQeasy

A company ingests streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time to detect anomalies and stored in Azure Data Lake Storage Gen2 for historical analysis. The solution must minimize latency and avoid duplicate processing. Which Azure service should be used for processing?

A.Azure Data Factory

B.Azure Databricks with Structured Streaming

C.Azure Functions with Event Hubs trigger

D.Azure Stream Analytics

AnswerD

Azure Stream Analytics provides low-latency stream processing with exactly-once semantics and integrates with Event Hubs and Data Lake Storage.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Event Hubs as input and Data Lake Storage Gen2 as output, and provides built-in exactly-once delivery semantics to avoid duplicate processing. It also supports temporal windowing and anomaly detection functions natively, making it ideal for this IoT anomaly detection scenario.

Exam trap

The trap here is that candidates often choose Azure Databricks with Structured Streaming because of its flexibility and popularity, but they overlook the specific requirement for minimal latency and built-in exactly-once processing, which Azure Stream Analytics handles more efficiently without the overhead of a Spark cluster.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is a batch-oriented ETL orchestration service, not designed for near real-time streaming or sub-second latency processing. Option B is wrong because Azure Databricks with Structured Streaming introduces higher latency due to Spark job initialization and micro-batch processing, and requires additional configuration for exactly-once semantics, making it less optimal for minimal latency and duplicate avoidance. Option C is wrong because Azure Functions with Event Hubs trigger processes events one at a time or in small batches, lacks native windowing and anomaly detection operators, and can lead to duplicate processing if not carefully managed with checkpointing and idempotent logic.

Full explanation →

759

MCQhard

You have an Azure Synapse Analytics workspace with Apache Spark pools. You need to monitor Spark application performance and identify stages that are taking the longest time. Which tool should you use?

A.Use the Spark UI available in Synapse Studio.

B.Run KQL queries in Log Analytics against Spark logs.

C.Query Azure Monitor metrics for the Spark pool.

D.Use the Synapse Pipeline monitoring view.

AnswerA

Spark UI provides detailed stage-level performance metrics.

Why this answer

The Spark UI provides detailed information about stages, tasks, and executors. Option B is wrong because Azure Monitor metrics provide aggregate metrics but not stage-level details. Option C is wrong because Log Analytics queries can analyze logs but not as directly as Spark UI.

Option D is wrong because Synapse Studio provides a job view but not as granular as Spark UI.

Full explanation →

760

MCQeasy

You need to audit all data access to an Azure Storage account. Which Azure service should you enable?

A.Azure Storage analytics logs and send to Log Analytics workspace

B.Azure Policy to audit storage account access

C.Azure Monitor metrics

D.Azure Security Center

AnswerA

Storage logs capture access details; Log Analytics enables querying.

Why this answer

Azure Storage analytics logs capture detailed information about successful and failed requests to a storage account, including authentication details, IP addresses, and operation types. By sending these logs to a Log Analytics workspace, you can query and analyze them using KQL, enabling comprehensive auditing of all data access. This is the correct service for auditing because it provides the granular, queryable logs required for security and compliance audits.

Exam trap

The trap here is that candidates confuse Azure Policy (which audits resource configurations) with actual data access auditing, or assume Azure Monitor metrics provide sufficient detail, when only Storage analytics logs sent to Log Analytics offer the per-request, queryable audit trail required.

How to eliminate wrong answers

Option B is wrong because Azure Policy is used to enforce compliance rules on resource configurations (e.g., requiring HTTPS), not to audit individual data access events. Option C is wrong because Azure Monitor metrics provide aggregated performance and error counts (e.g., transactions, latency), not detailed per-request audit logs. Option D is wrong because Azure Security Center (now Microsoft Defender for Cloud) provides security recommendations and threat detection, but it does not natively capture or store granular data access logs for auditing purposes.

Full explanation →

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 751–760