Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 226–300

760 questions total · 11pages · All types, answers revealed

Take a mock examExam hub
Page 3

Page 4 of 11

Page 5
226
MCQmedium

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

A.Use the Azure Databricks dbutils.fs.mv() to move the file.
B.Use Azure Data Factory with a Copy activity to move the file, then delete the source.
C.Change the file's metadata to update its directory path.
D.Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.
AnswerA

The move operation in ADLS Gen2 is a metadata rename, which is instantaneous.

Why this answer

`dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Exam trap

The trap here is that candidates often assume moving large files requires copying, but Azure Data Lake Storage Gen2's hierarchical namespace enables instant metadata-only renames, making `dbutils.fs.mv()` the optimal choice.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory Copy activity physically copies the file data across folders, which is slower and incurs additional read/write costs, even though it can delete the source afterward. Option C is wrong because Azure Data Lake Storage Gen2 does not support moving files by changing metadata; directory paths are part of the file's hierarchical namespace and cannot be updated via metadata alone. Option D is wrong because `dbutils.fs.cp()` performs a full data copy, which is time-consuming for large files, and then requires an explicit delete, adding unnecessary overhead compared to a rename.

Full explanation →
227
MCQhard

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that reduces storage costs for historical data that is rarely accessed but must be available for querying within minutes. The solution should not require application changes. What should you do?

A.Create external tables pointing to data stored in Azure Data Lake Storage Gen2 with appropriate tiering
B.Use Azure Blob Storage with PolyBase and configure lifecycle management to archive data
C.Drop older partitions and reload data when needed
D.Change the distribution to round-robin for the fact table
AnswerA

External tables allow querying data in Data Lake Storage without moving it, and tiering reduces cost.

Why this answer

Creating external tables in Azure Synapse Analytics dedicated SQL pool that point to data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) allows you to query historical data directly from low-cost storage tiers (e.g., cool or archive) without moving it into the pool. This reduces storage costs for rarely accessed data while keeping it queryable within minutes, and requires no application changes since the external tables are accessed via standard T-SQL queries.

Exam trap

The trap here is that candidates often confuse PolyBase with external tables, assuming PolyBase requires data to be in hot tier or that lifecycle management alone solves the query latency requirement, but they overlook the rehydration delay of archive tier and the need for zero application changes.

How to eliminate wrong answers

Option B is wrong because Azure Blob Storage with PolyBase and lifecycle management to archive data would require application changes to switch query targets and does not natively support querying archived data within minutes (archive tier has a rehydration delay of up to 15 hours). Option C is wrong because dropping older partitions and reloading data when needed is a manual, time-consuming process that violates the requirement of being available for querying within minutes and would require application changes to manage the reload logic. Option D is wrong because changing the distribution to round-robin for the fact table improves query performance for certain workloads but does not reduce storage costs for historical data or address the requirement of tiering rarely accessed data.

Full explanation →
228
MCQmedium

Your company has an Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that automatically moves data between the 'PRIMARY' filegroup and a secondary filegroup based on data age, without manual intervention. Which feature should you use?

A.Materialized views
B.Azure Data Lake Storage tiering
C.PolyBase
D.Partition switching
AnswerD

Partition switching enables efficient data movement between partitions, which can be automated with partition management.

Why this answer

Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to efficiently move data between filegroups by switching partitions between tables. By aligning partitions with data age, you can automatically transfer older data to a secondary filegroup without manual intervention, using a scheduled process like a stored procedure or Azure Data Factory.

Exam trap

The trap here is that candidates confuse data movement between filegroups with storage tiering or external data access, leading them to choose PolyBase or Azure Data Lake Storage tiering instead of recognizing that partition switching is the native mechanism for intra-database data relocation in Synapse dedicated SQL pools.

How to eliminate wrong answers

Option A is wrong because materialized views improve query performance by pre-computing and storing results, but they do not move data between filegroups or manage data lifecycle. Option B is wrong because Azure Data Lake Storage tiering manages data across hot, cool, and archive storage tiers at the storage account level, not within a dedicated SQL pool's filegroups. Option C is wrong because PolyBase is used for querying external data sources (e.g., Azure Blob Storage) using T-SQL, not for moving data between filegroups within the same SQL pool.

Full explanation →
229
MCQmedium

You are optimizing cost for an Azure Data Lake Storage Gen2 account that stores historical data. The data is accessed infrequently after 30 days and must be retained for 7 years. Which lifecycle management rule should you apply?

A.Move blobs to archive tier immediately after 30 days.
B.Delete blobs after 30 days.
C.Move blobs to premium tier after 30 days.
D.Move blobs to cool tier after 30 days, then to archive tier after 1 year.
AnswerD

This is correct: cool tier after 30 days reduces costs for infrequent access, and archive after 1 year maximizes long-term savings while retaining data for 7 years.

Why this answer

It moves blobs to the cool tier after 30 days (saving costs for infrequent access) and then to the archive tier after 1 year (maximizing savings for long-term retention), meeting both the access pattern and 7-year retention requirement. Option A is wrong because moving directly to archive after 30 days makes data unavailable for immediate access and incurs high retrieval costs if needed. Option B is wrong because deleting after 30 days violates the 7-year retention policy.

Option C is wrong because the premium tier is designed for high performance, not cost savings on cold data.

Full explanation →
230
MCQmedium

Your team needs to provide near-real-time analytics on IoT sensor data streaming into Azure Event Hubs. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and device ID. Which architecture should you implement?

A.Use Azure Stream Analytics with output to Data Lake Storage Gen2, using partitioning by date and device ID.
B.Use Azure Data Factory with a tumbling window trigger to copy data from Event Hubs to Data Lake Storage.
C.Use Azure Functions to read from Event Hubs and write to Data Lake Storage.
D.Use Azure Databricks with Structured Streaming to read from Event Hubs and write to Data Lake Storage.
AnswerA

Stream Analytics provides native partitioning and Parquet output.

Why this answer

Azure Stream Analytics provides native, low-latency processing of streaming data from Event Hubs with direct output to Azure Data Lake Storage Gen2. It supports automatic partitioning by specifying a partition key (e.g., date and device ID) in the output configuration, enabling efficient, near-real-time writes in Parquet format without additional orchestration.

Exam trap

Microsoft often tests the misconception that any service capable of reading from Event Hubs is suitable for near-real-time analytics, ignoring the critical requirements for native partitioning, low latency, and managed checkpointing that Stream Analytics uniquely provides.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory with a tumbling window trigger operates on a batch schedule (minimum 1 minute), not near-real-time, and cannot natively read from Event Hubs as a streaming source. Option C is wrong because Azure Functions, while capable of event-driven processing, lack native support for checkpointing and exactly-once semantics for high-throughput streaming, leading to potential data loss or duplication. Option D is wrong because Azure Databricks Structured Streaming can achieve near-real-time processing but introduces significant overhead (cluster startup, cost, complexity) compared to the simpler, fully managed Stream Analytics solution for this specific use case.

Full explanation →
231
Drag & Dropmedium

Drag and drop the steps to set up Azure Purview for data cataloging and lineage tracking into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

First create the Purview account, then register sources, scan them, set classifications, and finally explore the catalog.

Full explanation →
232
MCQhard

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?

A.Reduce the file size to 100 MB to increase parallelism
B.Use PolyBase instead of COPY INTO
C.Increase the number of files to match the number of distributions
D.Disable parallel processing in the COPY command
AnswerC

Correct. With 1 GB files, there are too few files to fully utilize all 60 distributions. Increasing the number of files to at least 60 ensures each distribution receives data, maximizing parallelism and improving performance.

Why this answer

COPY INTO in Azure Synapse dedicated SQL pool distributes data across 60 distributions. To maximize parallelism, the number of input files should match or exceed the number of distributions. With 1 GB files, you have too few files to fully utilize all distributions, causing some distributions to remain idle.

Increasing the number of files to at least 60 ensures each distribution gets work, improving throughput.

Exam trap

The trap here is that candidates focus on file size reduction (Option A) as a general optimization, but the specific requirement in Synapse dedicated SQL pool is to match the number of files to the number of distributions (60) to avoid distribution skew and maximize parallelism.

How to eliminate wrong answers

Option A is wrong because reducing file size to 100 MB increases the number of files but does not guarantee they match the distribution count; the key is file count, not size, and 100 MB files may still result in fewer than 60 files. Option B is wrong because PolyBase is an older technology that uses external tables and has additional overhead; COPY INTO is the recommended, optimized method for loading Parquet files and is generally faster. Option D is wrong because disabling parallel processing would force sequential loading, drastically reducing performance; COPY INTO inherently uses parallel processing to leverage all distributions.

Full explanation →
233
MCQmedium

A data engineer is designing a solution that uses Azure Data Factory to copy data from an on-premises SQL Server to Azure Synapse Analytics. The data transfer must be encrypted in transit. Which property should be configured in the linked service?

A.ConnectionString with Integrated Security
B.AuthenticateVia
C.EncryptedConnection
D.UseSystemTrustStore
AnswerC

Enables TLS encryption for data transfer.

Why this answer

The EncryptedConnection property in an Azure Data Factory linked service enforces encryption for data in transit between the on-premises SQL Server and Azure Synapse Analytics. When set to true, it uses TLS/SSL to encrypt the connection, ensuring that data transferred over the network is protected from interception or tampering.

Exam trap

The trap here is that candidates often confuse 'encryption in transit' with authentication methods (like Integrated Security) or certificate validation settings (like UseSystemTrustStore), leading them to select options that address identity or trust rather than the actual encryption of the data channel.

How to eliminate wrong answers

Option A is wrong because ConnectionString with Integrated Security specifies Windows authentication credentials but does not control encryption of the data in transit; it is unrelated to TLS/SSL enforcement. Option B is wrong because AuthenticateVia defines the authentication method (e.g., Managed Identity, Service Principal) for the linked service, not the encryption of the data channel. Option D is wrong because UseSystemTrustStore determines whether to use the system's certificate trust store for validating the server's TLS certificate, but it does not enable or disable encryption itself; encryption must be explicitly set via EncryptedConnection.

Full explanation →
234
MCQhard

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

A.Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.
B.Create a non-clustered index on OrderDate.
C.Add ProductKey as a distribution column alongside the existing hash.
D.Change the distribution to round-robin to spread data evenly.
AnswerA

Partition elimination reduces the data scanned by skipping partitions not matching the filter.

Why this answer

Partitioning the Sales table on OrderDate enables partition elimination, which allows the query optimizer to scan only the relevant partitions when filtering by date ranges. This reduces I/O and improves performance significantly, as the fact table is already hash-distributed on ProductKey, and partitioning on a different column used in WHERE clauses is a best practice in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often confuse indexing strategies from traditional SQL Server with Synapse dedicated SQL pool limitations, assuming non-clustered indexes are available, or they think partitioning must match the distribution column, when in fact they serve different purposes.

How to eliminate wrong answers

Option B is wrong because non-clustered indexes are not supported on dedicated SQL pool tables; only clustered columnstore indexes and clustered indexes are allowed, and adding a non-clustered index would fail or be ignored. Option C is wrong because a table can have only one distribution column (hash-distributed), and adding ProductKey as an additional distribution column is not possible; the table is already hash-distributed on ProductKey, which is fine for joins but does not help date-range filtering. Option D is wrong because changing to round-robin distribution would eliminate hash-based join benefits and lead to data movement during queries, worsening performance for typical fact table workloads.

Full explanation →
235
MCQhard

You are reviewing an Azure Data Factory pipeline JSON. Based on the exhibit, what will be the behavior of the Copy activity when copying files from a source folder that contains subfolders?

A.The copy will use staging to improve performance.
B.Only files in the root folder will be copied.
C.Files will be copied preserving the source folder structure.
D.All files from all subfolders will be copied into a single folder in the sink.
AnswerD

FlattenHierarchy merges all files into one folder.

Why this answer

The Copy activity is configured with recursive: true, which copies all files from all subfolders, and the sink is set to FlattenHierarchy, which places all files into a single folder without preserving the source folder structure. Option A is incorrect because staging is disabled. Option B is incorrect because recursive: true causes files from subfolders to be copied.

Option C is incorrect because FlattenHierarchy does not preserve folder structure.

Full explanation →
236
MCQmedium

You are reviewing a script to create an external data source in Azure Synapse Analytics serverless SQL pool. Based on the exhibit, what is the purpose of the SAS token?

A.To provide read access to the container for querying data.
B.To provide write access to the container for storing query results.
C.To encrypt the connection between the serverless pool and storage.
D.To authenticate the user to the serverless SQL pool.
AnswerA

The SAS includes 'sp=rl' which grants read and list permissions.

Why this answer

The SAS token grants read and list permissions (sp=rl) to the container, which allows the serverless SQL pool to read data from the container. Option B is incorrect because the SAS token does not have write permission (sp=rl, no write). Option C is incorrect because the SAS token does not encrypt the connection; it provides delegated access.

Option D is incorrect because the SAS token is used in the credential to access storage, not to authenticate to the serverless SQL pool.

Full explanation →
237
MCQmedium

You are a data engineer at a healthcare analytics company. The company uses Azure Data Factory (ADF) to orchestrate data pipelines that ingest patient data from on-premises SQL Server databases into Azure Synapse Analytics. Recently, the pipeline has been failing intermittently with the following error: 'Failure happened on 'Sink' side. ErrorCode=SqlFailedToConnect, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=Cannot connect to SQL Server Database. The TCP connection to the host <server_name>, port 1433 has failed. Error: 'Connection timed out.'.' The on-premises SQL Server is behind a corporate firewall. The ADF self-hosted integration runtime (SHIR) is installed on a VM inside the corporate network. You have verified that the SHIR is running and that the SQL Server is accessible from the SHIR VM using SQL Server Management Studio (SSMS). The error occurs sporadically, not consistently. What is the most likely cause of the intermittent connection timeout?

A.The data being transferred is skewed, causing the sink to be overwhelmed.
B.The corporate firewall or network device is closing idle TCP connections to the SQL Server database.
C.The SQL Server database is experiencing high CPU utilization during the pipeline execution window.
D.The self-hosted integration runtime is running out of memory during peak loads.
AnswerB

Firewalls often drop idle connections after a timeout period. When the pipeline uses a connection from the pool that has been idle, the connection is no longer valid, causing a timeout. This explains the intermittent nature.

Why this answer

The intermittent nature of the timeout, combined with the fact that the SHIR VM can connect to SQL Server via SSMS, strongly suggests that the corporate firewall or a network intermediary (such as a load balancer or NAT device) is closing idle TCP connections. ADF pipelines may hold connections open between activities or during long-running data transfers, and if no keep-alive packets are sent within the firewall's idle timeout window (commonly 4–30 minutes), the firewall drops the TCP session. When ADF attempts to reuse that connection, it receives a 'Connection timed out' error because the socket is no longer valid.

Exam trap

The trap here is that candidates assume the error is due to resource exhaustion (CPU, memory, or data skew) because those are common causes of intermittent failures, but the specific 'Connection timed out' error points to a network-layer issue, not a server-side performance bottleneck.

How to eliminate wrong answers

Option A is wrong because data skew would cause performance issues like slow writes or out-of-memory errors on the sink, not a TCP connection timeout to the source SQL Server. Option C is wrong because high CPU utilization on SQL Server would manifest as query timeouts or slow performance, not a TCP-level connection timeout (which occurs before any query is sent). Option D is wrong because SHIR running out of memory would produce out-of-memory exceptions or pipeline failures with different error codes, not a TCP connection timeout to the database.

Full explanation →
238
Multi-Selectmedium

You are monitoring the performance of an Azure Data Factory pipeline that uses a Copy activity to load data into Azure Synapse Analytics. Which THREE metrics should you monitor to identify potential performance bottlenecks?

Select 3 answers
A.Throughput (data read/written per second).
B.Integration runtime CPU utilization.
C.Pipeline run duration.
D.Copy activity duration.
E.Data read and written metrics.
AnswersA, D, E

Throughput indicates the speed of data transfer.

Why this answer

Options A, D, and E are correct. Throughput (data read/written per second), Copy activity duration, and Data read and written metrics are direct indicators of performance bottlenecks in the Copy activity. Option B is incorrect because Integration runtime CPU utilization is not a standard metric exposed by Azure Data Factory for monitoring Copy activity performance; it reflects runtime resource usage but not directly the data transfer performance.

Option C is incorrect because Pipeline run duration includes overhead from orchestration, such as pipeline activity scheduling and coordination, and is not a precise measure of the Copy activity's data transfer performance.

Full explanation →
239
MCQhard

Contoso Ltd. runs a real-time analytics solution on Azure Databricks with data streaming from Event Hubs. They need to ensure that all data in transit between Event Hubs and Databricks is encrypted using TLS 1.2 or higher. Currently, the Event Hubs namespace is configured with the default TLS version (1.0). The Databricks cluster uses a public endpoint. Compliance requires that only TLS 1.2 is accepted. You need to configure the environment to enforce TLS 1.2 without disrupting ongoing streaming. What should you do?

A.Update the Event Hubs namespace to require TLS 1.2, then modify the Databricks streaming job's connection string to include 'TransportType=AmqpTls' and restart the streaming job.
B.Change the Event Hubs namespace minimum TLS version to 1.2 in the Azure portal, then reboot the Databricks cluster.
C.In the Event Hubs namespace, set 'Minimum TLS version' to 1.2 and redeploy the Databricks cluster with a new init script that forces TLS 1.2.
D.Use Azure CLI to set the Event Hubs namespace TLS version to 1.2 and update the Databricks cluster's Spark configuration to use TLS 1.2.
AnswerA

Enforces TLS 1.2 with minimal disruption.

Why this answer

To enforce TLS 1.2 without disrupting ongoing streaming, you must first update the Event Hubs namespace to require TLS 1.2 via the 'Minimum TLS version' setting. Then, configure the Databricks streaming job's connection string to include 'TransportType=AmqpTls' to ensure the client uses TLS 1.2. Finally, restart the streaming job to apply the new connection settings.

Option A correctly describes these steps. Option B incorrectly suggests rebooting the cluster, which is unnecessary and would disrupt streaming. Option C incorrectly suggests redeploying the cluster with an init script, which is not needed and causes disruption.

Option D includes an unnecessary Spark configuration change; the connection string parameter (TransportType=AmqpTls) is sufficient.

Full explanation →
240
Multi-Selecthard

Which TWO strategies can be used to optimize storage costs for historical data in Azure Data Lake Storage Gen2?

Select 2 answers
A.Enable soft delete to recover data
B.Use geo-redundant storage (GRS) for durability
C.Implement lifecycle management policies to move data to archive tier
D.Store data in compressed columnar format like Parquet
E.Encrypt data with Azure Storage Service Encryption
AnswersC, D

Archive tier is cheapest.

Why this answer

Azure Blob Storage lifecycle management policies allow you to automatically transition data from hot to cool to archive tiers based on age or last modification time. Moving historical data to the archive tier significantly reduces storage costs, as archive is the lowest-cost storage tier, though it incurs higher retrieval latency and costs.

Exam trap

The trap here is that candidates confuse data protection features (soft delete, encryption, replication) with cost optimization strategies, but only tiering and compression directly reduce the amount or cost of stored data.

Full explanation →
241
Multi-Selecthard

Which THREE methods can you use to monitor and optimize the performance of an Azure Data Lake Storage Gen2 account?

Select 3 answers
A.Use Azure Advisor to get performance recommendations.
B.Enable Azure Monitor metrics for the storage account.
C.Implement lifecycle management policies to move data to cooler tiers.
D.Use Azure SQL Analytics to query storage logs.
E.Configure Storage Analytics logs for read and write requests.
AnswersB, C, E

Metrics like latency and throughput help monitor performance.

Why this answer

Options B, C, and E are correct. Azure Monitor metrics (B) provide performance data such as latency and throughput for the storage account. Lifecycle management policies (C) optimize costs by automatically moving data to cooler tiers based on access patterns, which can improve performance for frequently accessed data.

Storage Analytics logs (E) capture detailed information about read and write requests, helping to identify performance bottlenecks. Option A (Azure Advisor) offers recommendations but is not a direct monitoring method; it is an advisory tool. Option D (Azure SQL Analytics) is designed for monitoring Azure SQL Database, not Azure Data Lake Storage Gen2.

Full explanation →
242
Multi-Selecthard

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Select 3 answers
A.Use materialized views for complex aggregations
B.Use the largest resource class for all queries
C.Create clustered columnstore indexes
D.Use hash distribution on columns used in JOINs
E.Use round-robin distribution for large fact tables
AnswersA, C, D

Pre-computes results.

Why this answer

Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Exam trap

The trap here is that candidates often confuse resource class with performance optimization, assuming larger resource classes always speed up queries, when in fact they reduce concurrency and can cause resource contention, making them a poor general-purpose best practice.

Full explanation →
243
MCQhard

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

A.The join with the Products table forces a shuffle that bypasses the materialized result
B.The query uses summarize, which already materializes results internally
C.The datetime range filter is not sargable, causing full table scan
D.The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead
AnswerD

materialize() caches the result; if used once, caching is wasted.

Why this answer

Materialize() only provides performance benefit when the materialized result is referenced multiple times. In this query, the materialized result is used only once, so the overhead of materialization (storing the result in memory) outweighs any benefit, potentially making the query slower. Option A is incorrect because there is no join with a Products table; the query uses a single table.

Option B is incorrect because summarize does not inherently materialize results; it computes aggregations on the fly. Option C is incorrect because datetime range filters in Kusto are sargable and do not cause full table scans.

Full explanation →
244
Multi-Selecteasy

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Select 2 answers
A.Use coalesce() to reduce the number of partitions without a shuffle.
B.Enable caching on the DataFrame before writing.
C.Apply bucketing on a column to group data.
D.Increase the number of partitions using repartition() with a larger number.
E.Use repartition() with a smaller number of partitions.
AnswersA, E

Coalesce reduces partitions and thus output files, minimizing shuffle.

Why this answer

`coalesce()` reduces the number of partitions without triggering a full shuffle, which minimizes the number of output files while preserving performance. Since the DataFrame already has 100 million rows and 20 columns, coalescing to fewer partitions directly reduces the number of files written, addressing the small-file problem efficiently.

Exam trap

The trap here is that candidates often confuse `coalesce()` with `repartition()`, assuming both cause a shuffle, or they mistakenly think increasing partitions (Option D) will improve performance when it actually exacerbates the small-file issue.

Full explanation →
245
MCQhard

You are a data engineer at a healthcare analytics company. The company stores patient records in an Azure Data Lake Storage Gen2 account organized by /patient/{patientId}/year={yyyy}/month={MM}/day={dd}/*.parquet. There are 10,000 patients, and each patient has about 1 GB of data per year. The data is used by data scientists who run ad-hoc queries using Azure Synapse Serverless SQL. They complain that queries scanning multiple patients over the last year take too long and consume too much data. They often need to filter by patientId and a date range. You need to improve query performance and reduce the amount of data scanned. You cannot change the folder structure because it is used by other processes. What should you do?

A.Reorganize the folder structure to /year={yyyy}/month={MM}/day={dd}/patientId={patientId}/*.parquet.
B.Convert the Parquet files to CSV format to improve compression and reduce file size.
C.Create views that aggregate data by patient and date, and instruct data scientists to query the views.
D.Create external tables in Synapse Serverless SQL that use the folder structure as partitions, and ensure queries filter on year, month, and day.
AnswerD

External tables with partition elimination reduce scanned data.

Why this answer

Creating external tables in Synapse Serverless SQL with the existing folder structure as partitions allows the query engine to perform partition elimination. When queries filter on year, month, and day, Synapse Serverless SQL will only scan the relevant folders, drastically reducing data scanned and improving performance. This approach does not require changing the folder structure, which is used by other processes.

Exam trap

The trap here is that candidates may think views can improve performance, but in Synapse Serverless SQL, views are non-materialized and do not reduce data scanned unless the underlying data is partitioned and queries filter on partition columns.

How to eliminate wrong answers

Option A is wrong because it suggests reorganizing the folder structure, which is explicitly prohibited by the requirement that the folder structure cannot be changed. Option B is wrong because converting Parquet to CSV would increase file size (CSV is not compressed by default and lacks columnar storage benefits), worsening performance and data scanned. Option C is wrong because creating views does not change the underlying data layout or partition elimination; views are just saved queries and do not reduce the amount of data scanned unless they are materialized, which Synapse Serverless SQL does not support.

Full explanation →
246
MCQmedium

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

A.Use Azure Functions to process each file and write to Synapse via REST API
B.Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation
C.Use Azure Databricks with Python notebooks to process the files and write to Synapse
D.Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool
AnswerD

Mapping Data Flows provide a visual interface for transformations, are serverless, and have rich monitoring via ADF.

Why this answer

Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Exam trap

The trap here is that candidates often overestimate the simplicity of Azure Functions for batch ETL or assume PolyBase alone handles transformations, when in fact ADF Mapping Data Flows are purpose-built for visual, scalable, and monitorable ETL with minimal overhead.

How to eliminate wrong answers

Option A is wrong because Azure Functions are stateless, event-driven compute units that lack native connectors for SFTP and Synapse, requiring custom code for file parsing, state management, and batch loading, which increases administrative overhead and complexity. Option B is wrong because PolyBase external tables can only load raw data into staging tables, but the transformation logic (cleaning, validation, aggregation) would need to be implemented in T-SQL stored procedures, which are harder to monitor, scale, and maintain compared to a visual ETL tool like ADF. Option C is wrong because Azure Databricks with Python notebooks introduces significant administrative overhead for cluster management, notebook orchestration, and monitoring, and requires more specialized skills than ADF’s low-code Mapping Data Flows, making it less suitable for minimizing overhead.

Full explanation →
247
Multi-Selecteasy

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Select 2 answers
A.Hierarchical namespace
B.Immutable storage
C.Soft delete for blobs
D.Lifecycle management policies
E.POSIX-compliant access control lists
AnswersA, E

Hierarchical namespace is a core feature of ADLS Gen2 that organizes objects into directories, which is not available in Blob Storage's flat namespace.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) extends Azure Blob Storage by adding a hierarchical namespace, which organizes objects into a directory structure similar to a file system. This enables efficient directory-level operations (e.g., renaming or deleting a directory in O(1) time) and supports POSIX-compliant access control lists (ACLs) for fine-grained permissions. These two features are not available in standard Azure Blob Storage, which uses a flat namespace and only supports container-level access policies.

Exam trap

The trap here is that candidates often assume features like soft delete or lifecycle management are exclusive to ADLS Gen2, when in fact they are shared with Blob Storage, while the hierarchical namespace and POSIX ACLs are the true differentiators.

Full explanation →
248
MCQhard

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

A.Parquet files require special permissions that are not granted by RBAC roles
B.The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'
C.The storage account firewall does not have a 'Resource instances' exception for the managed identity
D.The notebook is using an incorrect connection string with account key
AnswerC

Firewall rules require explicit addition of the managed identity as a resource instance to allow access when the firewall is enabled.

Why this answer

When a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Exam trap

The trap here is that candidates assume 'Allow Azure services on the trusted services list' automatically includes all Azure resources with managed identities, but it only covers specific Azure platform services, not custom managed identities from services like Synapse.

How to eliminate wrong answers

Option A is wrong because Parquet files do not require special permissions beyond what RBAC roles provide; RBAC roles like 'Storage Blob Data Contributor' grant sufficient permissions to read and write Parquet files. Option B is wrong because 'Storage Blob Data Contributor' already includes all permissions of 'Storage Blob Data Reader' (read, write, delete), so adding the reader role is redundant and not the cause of the failure. Option D is wrong because the notebook uses a managed identity for authentication, not a connection string with an account key; the error is about permission denied, not an incorrect connection string or key.

Full explanation →
249
MCQeasy

You need to monitor the performance of an Azure Stream Analytics job that processes real-time IoT data. Which metric indicates the number of events that are being dropped or delayed due to insufficient processing capacity?

A.Watermark delay.
B.Output events.
C.Backlogged input events.
D.Input events.
AnswerC

Backlogged input events shows the number of events that are queued but not yet processed, indicating capacity issues.

Why this answer

Backlogged input events (C) measures the number of events that are queued awaiting processing, indicating that the job is unable to keep up with the input rate. High backlog suggests insufficient processing capacity, leading to dropped or delayed events. Watermark delay (A) measures the time lag in processing, but does not directly count events dropped.

Input events (D) is the total received, not dropped/delayed. Output events (B) is the total sent.

Full explanation →
250
MCQhard

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

A.Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.
B.Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.
C.Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.
D.Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.
AnswerB

CDC captures only changed rows, and ADF can run frequently to meet latency target.

Why this answer

Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Exam trap

The trap here is that candidates often confuse Change Tracking (which only tracks that a row changed, not the actual changes) with Change Data Capture (which captures the before-and-after values), leading them to choose Option A without realizing the missing push mechanism and the need for additional services to achieve near-real-time replication.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Change Tracking does not natively push changes to Event Hubs; it requires custom logic or additional services (e.g., Azure Functions) to bridge the gap, adding complexity and potential latency that may not guarantee sub-5-minute replication. Option C is wrong because PolyBase in Synapse is designed for batch querying of external data sources, not for near-real-time incremental replication; querying the source SQL database every 5 minutes would perform full table scans on 50 million rows, causing high source database load and failing to meet latency requirements. Option D is wrong because scheduling a full copy of the entire 50-million-row table every 5 minutes is extremely inefficient, consumes excessive bandwidth and Synapse storage resources, and would likely exceed the latency window due to the time required for a full data transfer.

Full explanation →
251
MCQeasy

A manufacturing company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled and Azure Databricks for analytics. The security team requires that all data stored in the 'raw' container be encrypted at rest using customer-managed keys. The data is ingested via Azure Data Factory. What should the data engineer configure to meet the requirement?

A.Assign an Azure Policy that requires encryption at rest.
B.Enable Azure Information Protection on the storage account.
C.Configure the storage account to use Azure Key Vault for customer-managed key encryption.
D.Enable the 'require secure transfer' setting on the storage account.
AnswerC

This enables encryption at rest with a customer-managed key.

Why this answer

Azure Data Lake Storage Gen2 with hierarchical namespace supports encryption at rest using customer-managed keys (CMK) via Azure Key Vault. To meet the security requirement, the data engineer must configure the storage account's encryption settings to use a key from Azure Key Vault, which allows the organization to control and rotate the encryption keys independently of Azure.

Exam trap

The trap here is that candidates may confuse encryption at rest (which is always enabled by default) with the specific requirement for customer-managed keys, leading them to pick Azure Policy or 'require secure transfer' as a catch-all security measure.

How to eliminate wrong answers

Option A is wrong because an Azure Policy can enforce encryption at rest, but it does not specify the use of customer-managed keys; it only ensures that encryption is enabled (which is already default with Microsoft-managed keys). Option B is wrong because Azure Information Protection is a classification and labeling service for data, not an encryption-at-rest mechanism for storage accounts. Option D is wrong because 'require secure transfer' enforces HTTPS for data in transit, not encryption at rest, and does not involve customer-managed keys.

Full explanation →
252
Multi-Selectmedium

You are designing a data storage solution for a manufacturing company that collects sensor data from machines. The data is stored in Azure Data Lake Storage Gen2. You need to ensure that the solution can handle large volumes of streaming data (up to 100 MB/s) and provide real-time dashboards. Which TWO services should you include?

Select 2 answers
A.Azure Analysis Services
B.Azure Data Factory
C.Azure Databricks
D.Azure Stream Analytics
E.Azure Event Hubs
AnswersD, E

Stream Analytics processes streaming data and can output to real-time dashboards.

Why this answer

Azure Stream Analytics is correct because it is a real-time analytics service designed to process high-velocity streaming data (up to 100 MB/s) from sources like Event Hubs and output to dashboards and storage. It provides low-latency, SQL-based querying for real-time dashboards, making it ideal for manufacturing sensor data scenarios.

Exam trap

Microsoft often tests the misconception that Azure Databricks is a real-time dashboard service, but it is primarily a processing engine that requires additional integration for dashboard output, whereas Stream Analytics is purpose-built for direct, low-latency dashboarding.

Full explanation →
253
MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected. After reviewing the query plans, you see that some queries are spilling to tempdb. What should you do to reduce tempdb spills?

A.Increase the resource class for the user executing the queries.
B.Redistribute the tables using hash distribution.
C.Rebuild all columnstore indexes.
D.Add partitioning to the tables.
AnswerA

Larger resource class allocates more memory, reducing tempdb spills.

Why this answer

Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Exam trap

The trap here is that candidates often confuse performance tuning techniques like indexing or partitioning with memory management, assuming any optimization will fix spills, when only increasing memory allocation (via resource class) directly addresses the root cause.

How to eliminate wrong answers

Option B is wrong because redistributing tables using hash distribution improves data movement and join performance but does not directly increase per-query memory allocation to prevent tempdb spills. Option C is wrong because rebuilding columnstore indexes improves compression and scan performance but does not address the memory grant issue that causes spills. Option D is wrong because adding partitioning can improve partition elimination and manageability but does not increase the memory available to individual queries, so it will not reduce tempdb spills.

Full explanation →
254
Multi-Selectmedium

You are implementing a data processing solution using Azure Data Factory. You have a pipeline that copies data from Azure Blob Storage to Azure Data Lake Storage Gen2. You need to ensure that the copy activity uses managed identity for authentication and that the data is transferred securely. Which TWO configurations should you apply?

Select 2 answers
A.Set the copy activity's 'enableDataIntegrityValidation' to true.
B.Set the copy activity's 'UseManagedIdentity' property to true.
C.Configure the source and sink linked services to use managed identity authentication.
D.Set the integration runtime to 'AutoResolveIntegrationRuntime' to ensure data stays within Azure.
E.Enable staged copy with a temporary blob store to improve security.
AnswersC, D

Linked services support managed identity for authentication.

Why this answer

Options C and D are correct. Option C is correct because managed identity authentication is configured in the linked service, not in the copy activity itself. Option D is correct because setting the integration runtime to 'AutoResolveIntegrationRuntime' uses Azure's network, ensuring data transfer remains within Azure and does not go over the public internet if using managed virtual network.

Option A is wrong because 'enableDataIntegrityValidation' is for integrity checks, not authentication. Option B is wrong because the copy activity does not have a 'UseManagedIdentity' property; managed identity is configured in the linked service. Option E is wrong because staging is not required for secure transfer; it's used for performance or polybase.

Full explanation →
255
MCQmedium

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

A.Hot tier
B.Archive tier
C.Cool tier
D.Premium tier
AnswerC

Cost-effective for infrequently accessed data with retrieval within hours.

Why this answer

The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Exam trap

The trap here is that candidates often choose the Archive tier because it has the lowest storage cost, overlooking the rehydration latency and the fact that retrieval within 24 hours is not guaranteed with standard priority rehydration, especially under heavy demand.

How to eliminate wrong answers

Option A is wrong because the Hot tier is designed for frequently accessed data and has higher storage costs, which would unnecessarily increase expenses for infrequently accessed compliance data. Option B is wrong because the Archive tier has the lowest storage cost but requires a rehydration process that can take up to 15 hours (and often longer), which may not guarantee retrieval within 24 hours and incurs significant read and data retrieval costs. Option D is wrong because the Premium tier is for high-performance, low-latency access (e.g., for transactional or real-time workloads) and is the most expensive option, making it unsuitable for cost-minimized, infrequently accessed historical data.

Full explanation →
256
Multi-Selecthard

Which THREE metrics should you monitor to evaluate the performance of an Azure Stream Analytics job?

Select 3 answers
A.Input Events Backlogged
B.Output Events
C.Conversion Errors
D.SU (Memory) Utilization
E.Watermark Delay (seconds)
AnswersA, B, E

Shows backlog of unprocessed events.

Why this answer

Watermark Delay (indicates latency), Input Events Backlogged (backlog of unprocessed events), and Output Events (throughput) are key performance metrics for an Azure Stream Analytics job. Option C (Conversion Errors) and Option D (SU (Memory) Utilization) are not performance metrics; Conversion Errors is an error metric, and SU Utilization is a resource metric.

Full explanation →
257
Multi-Selecteasy

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Select 2 answers
A.Power BI Dataset
B.Azure DevOps
C.Amazon S3
D.Azure Blob Storage
E.Azure Analysis Services
AnswersC, D

Amazon S3 is supported via the Amazon S3 connector.

Why this answer

Amazon S3 is a supported source for Azure Data Factory Copy activity because ADF includes a built-in Amazon S3 connector that allows data ingestion from S3 into Azure. This connector uses the S3 REST API to read objects, supporting both public and private buckets via access keys or IAM roles.

Exam trap

The trap here is that candidates often confuse Azure Analysis Services (a semantic model) with Azure SQL Database or Azure Synapse, which are valid sources, leading them to incorrectly select it as a supported source for Copy activity.

Full explanation →
258
MCQmedium

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

A.Statistics on the table are outdated.
B.The table is not properly partitioned.
C.Data compression is not working efficiently.
D.Data is unevenly distributed across distributions, causing some distributions to be overloaded.
AnswerD

Duplicate values in hash column cause skew.

Why this answer

D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Exam trap

The trap here is that candidates often confuse distribution skew with partitioning or statistics issues, but the key clue is the mention of 'many duplicate values' in the hash-distributed column, which directly points to data skew as the root cause.

How to eliminate wrong answers

Option A is wrong because outdated statistics can cause suboptimal query plans, but the primary issue described is data skew due to hash distribution on a column with many duplicates, not statistics freshness. Option B is wrong because partitioning is a separate concept from distribution; while partitioning can help with partition elimination, it does not address the fundamental data skew caused by hash distribution on a high-duplicate column. Option C is wrong because data compression efficiency is affected by data patterns and storage, not directly by distribution skew; compression works at the page level and is not the root cause of query performance degradation from uneven distribution.

Full explanation →
259
MCQhard

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

A.Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.
B.Drop and recreate the clustered columnstore index.
C.Re-cluster the table using a different distribution key.
D.Run ALTER INDEX REBUILD on the clustered columnstore index.
AnswerA

Online operation that improves columnstore quality.

Why this answer

ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON is an online operation that compresses rowgroups with deleted rows without blocking writes. This directly addresses the poor columnstore segment quality. Option B is wrong because dropping and recreating the clustered columnstore index is an offline operation that blocks writes.

Option C is wrong because changing the distribution key would require recreating the table, which is offline and does not specifically fix columnstore segment quality. Option D is wrong because ALTER INDEX REBUILD is also an offline operation that blocks writes.

Full explanation →
260
MCQeasy

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

A.A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.
B.The Azure Blob Storage account has been moved to a different subscription.
C.The self-hosted integration runtime node has been de-registered from Azure Data Factory.
D.The blob container has reached its maximum capacity.
AnswerA

Network security updates often change firewall rules, blocking the IR.

Why this answer

The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Exam trap

The trap here is that candidates may confuse a source-side connectivity failure with a sink-side or authentication issue, but the question explicitly states a 'connection error to the on-premises SQL Server,' which points directly to network or firewall blocking at the source, not to storage capacity or SHIR registration status.

How to eliminate wrong answers

Option B is wrong because moving an Azure Blob Storage account to a different subscription does not affect the connectivity between the on-premises SHIR and the on-premises SQL Server; it would only change the storage account's resource ID and require updating linked service credentials, not cause a connection error to SQL Server. Option C is wrong because if the SHIR node were de-registered, the pipeline would fail with an integration runtime not found error, not a connection error to the on-premises SQL Server; the error message would reference the SHIR status, not a network-level timeout or refused connection. Option D is wrong because a blob container reaching maximum capacity (5 TB per container) would result in a storage write error (e.g., 403 or 409) when copying data, not a connection error to the on-premises SQL Server; the error would occur at the sink, not the source.

Full explanation →
261
MCQmedium

You are reviewing a copy job configuration in Azure Data Factory that copies Parquet files from Azure Data Lake Storage Gen2 to Azure Synapse Analytics. The exhibit shows the job settings. If the source folder contains a file that is not in Parquet format (e.g., a CSV file), what will happen?

A.The copy job will skip the CSV file and stop.
B.The copy job will fail with an error.
C.The copy job will skip the CSV file and continue copying other Parquet files.
D.The copy job will attempt to read the CSV file as Parquet and may produce corrupt data.
AnswerC

skipIncompatibleFiles=true causes skipping non-Parquet files.

Why this answer

When using Azure Data Factory's Copy Activity with a wildcard file path or a dataset that filters for Parquet files (e.g., *.parquet), the service evaluates the file pattern before attempting to read the file. If a CSV file is present in the same folder but does not match the Parquet filter, ADF simply ignores it and continues processing only the matching Parquet files. This behavior is by design to allow flexible file selection without causing failures.

Exam trap

The trap here is that candidates assume ADF will attempt to read all files in a folder regardless of extension, leading them to choose Option D (corrupt data) or Option B (failure), when in fact ADF respects the file pattern filter and silently skips non-matching files.

How to eliminate wrong answers

Option A is wrong because the copy job does not stop after skipping a non-matching file; it continues processing remaining files that match the filter. Option B is wrong because the copy job does not fail with an error when encountering a non-Parquet file; it only fails if the file matches the pattern but cannot be parsed as Parquet. Option D is wrong because ADF does not attempt to read a CSV file as Parquet when the file pattern explicitly excludes it; the file is simply not processed.

Full explanation →
262
MCQmedium

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

A.Implement Azure ExpressRoute to provide dedicated bandwidth.
B.Increase the 'Polling Interval' setting in the copy activity.
C.Scale out the self-hosted IR by adding more nodes to the cluster.
D.Migrate the self-hosted IR to Azure-SSIS IR.
AnswerC

Distributes load and improves throughput.

Why this answer

Scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because Azure ExpressRoute provides dedicated bandwidth but is costly and overkill for this scenario. Option B is wrong because increasing the polling interval does not fix network timeouts; it only changes how often the activity checks for data.

Option D is wrong because migrating to Azure-SSIS IR is expensive and unnecessary for this pipeline.

Full explanation →
263
Multi-Selecteasy

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. You notice frequent failures due to transient network errors. Which TWO actions should you take to improve reliability?

Select 2 answers
A.Deploy a self-hosted integration runtime on a VM in Azure.
B.Use staged copy with Azure Data Lake as intermediate storage.
C.Enable fault tolerance in the copy activity to skip incompatible rows.
D.Configure a retry policy on the copy activity.
E.Increase the degree of copy parallelism.
AnswersC, D

Fault tolerance allows pipeline to continue despite errors.

Why this answer

Options C and D are correct. Enabling fault tolerance allows the copy activity to skip incompatible rows and continue, while configuring a retry policy automatically retries the activity on failure due to transient errors. Option A is incorrect because deploying a self-hosted IR in Azure does not address transient network errors; it is used for connectivity to on-prem data stores.

Option B is incorrect because staged copy is for copying large datasets efficiently, not for handling transient errors. Option E is incorrect because increasing parallelism improves throughput but does not improve reliability against transient failures.

Full explanation →
264
MCQmedium

Your organization uses Azure Synapse Analytics to run large-scale ETL jobs. A pipeline that loads data from Azure Data Lake Storage Gen2 into a dedicated SQL pool is failing with 'Out of memory' errors during the staging step. The source data is 500 GB in size, and the SQL pool is currently set to DW500c. What should you do to resolve the issue without changing the pipeline logic?

A.Use PolyBase instead of the Copy activity.
B.Switch the source to Azure Data Lake Storage Gen1.
C.Increase the DWU of the dedicated SQL pool to a higher tier (e.g., DW1000c).
D.Partition the source data into smaller files.
AnswerC

Higher DWU provides more memory per distribution, resolving OOM errors.

Why this answer

The 'Out of memory' error during the staging step indicates that the dedicated SQL pool lacks sufficient memory resources to handle the 500 GB data load within the current DWU setting. Increasing the DWU (Data Warehouse Units) from DW500c to a higher tier, such as DW1000c, scales up the memory and compute resources available to the pool, resolving the memory pressure without altering the pipeline logic or source data.

Exam trap

The trap here is that candidates may assume partitioning the source data is a 'logical' fix without realizing it requires pipeline logic changes, or they may confuse PolyBase as a memory-saving alternative rather than a loading protocol that still relies on the pool's resources.

How to eliminate wrong answers

Option A is wrong because PolyBase is not a separate activity but a technology used within the Copy activity or other loading methods; switching to PolyBase does not inherently increase memory and would still fail under the same resource constraints. Option B is wrong because switching the source to Azure Data Lake Storage Gen1 does not affect the memory allocation of the dedicated SQL pool and introduces no performance benefit for the staging step. Option D is wrong because partitioning the source data into smaller files would require changing the pipeline logic (e.g., adding loops or multiple activities) to process files sequentially, which violates the constraint of not changing pipeline logic.

Full explanation →
265
MCQmedium

A company uses Azure Key Vault to store secrets for data pipelines. They need to rotate the secrets automatically every 90 days. What should they implement?

A.Use Azure Policy to enforce secret expiration.
B.Assign RBAC roles to a service principal to update the secret.
C.Create a manual process to update the secret in Key Vault.
D.Configure Key Vault secret rotation with an expiration date of 90 days.
AnswerD

Key Vault can automatically rotate secrets based on expiration.

Why this answer

Azure Key Vault supports automatic secret rotation by configuring an expiration date and enabling rotation. Option A is incorrect because Azure Policy can enforce expiration but does not automatically rotate secrets. Option B is incorrect because RBAC roles manage access permissions, not secret rotation.

Option C is incorrect because a manual process is not automatic and does not meet the requirement for automatic rotation every 90 days.

Full explanation →
266
MCQhard

Refer to the exhibit. The pipeline fails with a 'Type mismatch' error. The source file has a column 'Name' of type String, and the destination table expects 'FullName' of type String. What is the most likely cause of the failure?

A.The recursive setting on the source store is causing duplicate reads.
B.The source file does not contain a column named 'Name'.
C.The source file has leading/trailing whitespace in the column names.
D.The sink column 'FullName' is expecting a different data type than String.
AnswerB

The column mapping references a source column 'Name', but if the actual file has a different column header, the copy activity fails with type mismatch.

Why this answer

The pipeline fails with a 'Type mismatch' error because the source file does not contain a column named 'Name'. When the source dataset is configured to read a 'Name' column but the actual file lacks that column, Azure Data Factory or Synapse Pipelines cannot map it to the sink column 'FullName', resulting in a type mismatch error. The error message is misleading because the mismatch is not about data types but about missing source columns, which the service treats as a type incompatibility.

Exam trap

The trap here is that candidates assume 'Type mismatch' always refers to incompatible data types (e.g., String vs. Int), when in fact it can also be triggered by a missing source column that the pipeline expects to map, causing a schema-level mismatch.

How to eliminate wrong answers

Option A is wrong because the recursive setting on the source store controls whether subfolders are included in the read operation; it does not cause duplicate reads that would lead to a type mismatch error. Option C is wrong because leading/trailing whitespace in column names would cause a different error (e.g., column not found) but not a 'Type mismatch' error, and the pipeline would still attempt to map the column if the name matched after trimming. Option D is wrong because the sink column 'FullName' is of type String, matching the source column 'Name' type String, so there is no data type mismatch between the two; the error arises from the missing source column, not from incompatible types.

Full explanation →
267
MCQhard

You are designing a solution to store telemetry data from millions of devices. Each device sends a JSON payload every 5 seconds. The data must be partitioned by device ID and time for efficient querying and must support real-time streaming ingestion. Which Azure storage solution should you recommend?

A.Azure SQL Database
B.Azure Blob Storage with Azure Event Hubs
C.Azure Cosmos DB
D.Azure Table Storage
AnswerC

Correct. Cosmos DB supports real-time ingestion, automatic partitioning, and low-latency queries.

Why this answer

Azure Cosmos DB is the correct choice because it offers a multi-model, globally distributed database service with native support for real-time streaming ingestion via the Change Feed and automatic indexing. Its partition key design (device ID + time) enables efficient querying across millions of devices, and it guarantees single-digit millisecond read/write latencies essential for telemetry data arriving every 5 seconds.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with Event Hubs as a streaming solution, but Blob Storage is not designed for real-time, low-latency writes from millions of devices, and the combination adds unnecessary complexity and latency compared to Cosmos DB's native streaming support.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database that does not natively support real-time streaming ingestion at the scale of millions of devices every 5 seconds, and its partitioning capabilities are limited compared to Cosmos DB's horizontal scaling. Option B is wrong because Azure Blob Storage is an object store optimized for large, unstructured data, not for low-latency, high-frequency writes from millions of devices, and while Event Hubs can ingest streams, the combination requires additional processing to write to Blob Storage, adding latency and complexity. Option D is wrong because Azure Table Storage is a NoSQL key-value store that lacks native support for real-time streaming ingestion, automatic indexing, and the flexible querying capabilities needed for time-based and device-ID-based queries at this scale.

Full explanation →
268
MCQhard

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

A.Increase the service level (DWU) of the dedicated SQL pool.
B.Change the file format from CSV to Avro.
C.Combine the CSV files into fewer, larger files before loading.
D.Use Azure Data Factory to stage the data in Azure Blob Storage before loading.
AnswerC

PolyBase performs more efficiently with fewer, larger files due to reduced metadata operations.

Why this answer

Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Exam trap

The trap here is that candidates assume scaling up (DWU) or changing file formats always improves performance, but the exam specifically tests the understanding that PolyBase's parallel processing is most efficient when file sizes align with distribution boundaries, making file consolidation the most effective optimization.

How to eliminate wrong answers

Option A is wrong because increasing DWU scales resources but does not address the root cause of slow PolyBase loads—file fragmentation and metadata overhead—and may incur unnecessary cost. Option B is wrong because changing to Avro improves compression and schema evolution but does not reduce the number of file operations; the performance gain from file count reduction is more direct. Option D is wrong because staging in Azure Blob Storage adds an extra copy step without reducing the number of files PolyBase must process; the bottleneck remains the file enumeration and split overhead.

Full explanation →
269
Multi-Selectmedium

Your organization uses Azure Data Lake Storage Gen2 to store parquet files. You need to secure the data at rest and control access. Which THREE methods should you implement?

Select 3 answers
A.Set POSIX-like ACLs on directories and files.
B.Configure RBAC roles to control access to storage accounts.
C.Configure Azure Storage Firewall to allow only trusted IPs.
D.Enable Azure Storage Service Encryption (SSE) for data at rest.
E.Enable soft delete for blobs.
AnswersA, B, D

ACLs provide fine-grained access control.

Why this answer

Options A, B, and D are correct. Encryption at rest is done by Azure Storage Service Encryption. Access control is via RBAC and ACLs.

Option C is wrong because firewall restricts network access, not data at rest. Option E is wrong because soft delete is for data recovery, not security.

Full explanation →
270
Multi-Selectmedium

Which TWO actions should you take to secure data in transit between an Azure Synapse Analytics serverless SQL pool and a client application?

Select 2 answers
A.Use Azure RBAC to restrict access to the SQL pool.
B.Configure the serverless SQL pool to enforce TLS 1.2 connections.
C.Use Azure Virtual Network service endpoints for the SQL pool.
D.Disable SSL encryption to reduce latency.
E.Use Azure ExpressRoute to connect to the SQL pool.
AnswersB, C

TLS 1.2 is the minimum recommended protocol.

Why this answer

The correct answers are B and C. Enforcing TLS 1.2 ensures encryption in transit using a modern, secure protocol (B). Using Azure Virtual Network service endpoints for the SQL pool keeps traffic within the Azure backbone network, adding a layer of network security (C).

Option A (Azure RBAC) controls authentication and authorization, not encryption. Option D (disable SSL) would expose data in transit. Option E (ExpressRoute) provides private connectivity but does not inherently encrypt data; it's more for network isolation.

Full explanation →
271
MCQeasy

Your team has deployed an Azure Stream Analytics job that writes output to Azure Cosmos DB. You need to monitor the job for data latency and ensure it meets a service-level agreement (SLA) of under 10 seconds from input to output. Which metric should you track in Azure Monitor?

A.Output events.
B.Runtime errors.
C.Watermark delay.
D.Input events.
AnswerC

Watermark delay measures the maximum time difference between the input and output, indicating end-to-end latency.

Why this answer

Watermark delay is the correct metric to monitor for data latency because it measures the maximum time between an input event being received and the corresponding output being produced. A watermark delay consistently under 10 seconds ensures the SLA is met. Output events (A) track the number of output events, not latency.

Runtime errors (B) indicate failures, not latency. Input events (D) track the number of input events, not latency.

Full explanation →
272
Multi-Selectmedium

Which THREE metrics should you monitor to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Select 3 answers
A.Storage space used
B.Queued queries
C.DWU (Data Warehouse Unit) usage
D.Login failures
E.TempDB usage
AnswersB, C, E

Queries waiting for resources indicate concurrency issues.

Why this answer

To optimize the performance of an Azure Synapse Analytics dedicated SQL pool, monitor Queued queries (B) to detect concurrency throttling, DWU usage (C) to gauge overall resource utilization, and TempDB usage (E) as high usage can degrade performance. Storage space used (A) is a capacity metric, not a performance metric, and Login failures (D) are security-related, not performance-related.

Full explanation →
273
MCQmedium

You need to partition a large Azure SQL Database table by date to improve query performance and manageability. Which partitioning strategy should you use?

A.CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', ...)
B.CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE LEFT FOR VALUES ('2023-01-01', '2023-02-01', ...)
C.Use Azure SQL Database automatic partitioning feature
D.Apply a clustered columnstore index with partitioning
AnswerA

Why this answer

`RANGE RIGHT` ensures that each boundary value belongs to the right partition, which is the standard approach for date-based partitioning. This means values less than '2023-01-01' go into the first partition, values >= '2023-01-01' and < '2023-02-01' go into the second, and so on, aligning with typical date range queries and simplifying partition management (e.g., switching out old partitions).

Exam trap

The trap here is that candidates often confuse `RANGE LEFT` and `RANGE RIGHT`, mistakenly thinking `LEFT` is the default or more natural for date ranges, when in fact `RANGE RIGHT` is the standard for non-overlapping, sliding-window date partitions.

Why the other options are wrong

B

RANGE LEFT includes the boundary value in the left partition, which can lead to uneven data distribution.

C

Azure SQL Database does not have automatic partitioning; you must create it manually.

D

Columnstore indexes are for analytics, not the primary partitioning mechanism.

Full explanation →
274
MCQeasy

Your organization uses Microsoft Purview to catalog data assets. You need to ensure that sensitive data such as credit card numbers are automatically detected and labeled. Which Purview feature should you configure?

A.Create an Azure Policy to enforce tagging.
B.Configure a scan rule set with built-in classification rules for sensitive data types.
C.Enable the Data Catalog self-service search.
D.Enable Microsoft Information Protection for the data sources.
AnswerB

Scan rule sets enable automatic detection of sensitive data.

Why this answer

Microsoft Purview can automatically detect sensitive data like credit card numbers by configuring a scan rule set that includes built-in classification rules. Option A is incorrect because Azure Policy enforces compliance rules but does not perform data scanning or classification. Option C is incorrect because Data Catalog self-service search is for discovering and searching data assets, not for automatic sensitive data detection.

Option D is incorrect because Microsoft Information Protection is primarily for labeling and protection in Microsoft 365, not for scanning data in Purview.

Full explanation →
275
MCQhard

You are responsible for securing an Azure Synapse Analytics workspace. The workspace contains dedicated SQL pools and serverless SQL pools. You need to ensure that only users with specific Microsoft Entra ID roles can query serverless SQL pools, while dedicated SQL pools use SQL authentication. What should you do?

A.Create SQL logins for serverless SQL pools and assign permissions to Microsoft Entra ID groups.
B.Configure serverless SQL pools to use Microsoft Entra ID authentication only, and dedicated SQL pools to allow SQL authentication.
C.Use a managed identity for serverless SQL pools and assign it to Microsoft Entra ID roles.
D.Disable SQL authentication for all pools and enforce Microsoft Entra ID authentication only.
AnswerB

Serverless SQL pools support only Entra ID; dedicated SQL pools can use both.

Why this answer

Serverless SQL pools in Azure Synapse Analytics natively support Microsoft Entra ID authentication, allowing you to restrict access to specific Entra ID roles by disabling SQL authentication for those pools. Dedicated SQL pools can independently be configured to allow SQL authentication, enabling coexistence of both authentication methods as required. This separation ensures that only users with the designated Entra ID roles can query serverless SQL pools, while dedicated SQL pools remain accessible via SQL logins.

Exam trap

The trap here is that candidates may assume SQL logins can be created for serverless SQL pools (Option A) or that managed identities can be used to control user access (Option C), when in fact serverless pools only support Microsoft Entra ID authentication and managed identities are for service principals, not user permissions.

How to eliminate wrong answers

Option A is wrong because serverless SQL pools do not support SQL logins; they rely exclusively on Microsoft Entra ID authentication, so creating SQL logins for them is not possible. Option C is wrong because managed identities are used for service-to-service authentication (e.g., connecting to Azure Storage), not for granting user access to serverless SQL pools via Entra ID roles. Option D is wrong because it would disable SQL authentication entirely, contradicting the requirement that dedicated SQL pools must use SQL authentication.

Full explanation →
276
MCQhard

You are designing a data processing solution for a financial services company. They need to process sensitive customer data in Azure Databricks while complying with GDPR. The data must be encrypted at rest and in transit, and access must be audited. You need to recommend a configuration that meets these requirements. Which combination of actions should you take?

A.Enable Azure Storage Service Encryption (SSE) with platform-managed keys and configure Azure Databricks to use a VNet injection.
B.Apply an Azure Policy to require encryption and assign a built-in GDPR blueprint.
C.Use Azure Security Center to monitor for data exposure and enable Azure Defender for Storage.
D.Enable double encryption using Azure Key Vault with customer-managed keys and enable Azure Databricks audit logs.
AnswerD

Double encryption provides encryption at rest and in transit; customer-managed keys give control; audit logs track access.

Why this answer

Enabling double encryption (via Azure Disk Encryption and Azure Storage Service Encryption with customer-managed keys) ensures encryption at rest and in transit; Azure Key Vault manages the keys, and Azure Databricks audit logs capture access for auditing. Option A (SSE with platform-managed keys) provides encryption at rest but not with customer-managed keys and does not include audit logs. Option B (Azure Policy with GDPR blueprint) enforces compliance but does not directly implement encryption or auditing.

Option C (Azure Security Center and Defender for Storage) provides monitoring but does not encrypt data or provide access auditing.

Full explanation →
277
MCQmedium

You are building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

A.Use a 'Column pattern' to detect new columns and filter rows.
B.Configure 'Row sampling' settings to take only new rows.
C.Set 'Source' to 'New rows only' in the sink transformation.
D.Enable 'Incremental extraction' and specify the timestamp column for filtering.
AnswerD

Incremental extraction filters rows based on a watermark column like LastModified.

Why this answer

Mapping data flows in Azure Synapse Analytics support incremental extraction by enabling the 'Incremental extraction' option in the source transformation and specifying a timestamp column (e.g., LastModified) to filter only new or updated rows. This allows the pipeline to load only changed data since the last run, which is essential for efficient incremental loads into a dedicated SQL pool.

Exam trap

The trap here is that candidates confuse 'incremental extraction' in the source transformation with sink-level settings or sampling options, mistakenly thinking that row sampling or sink properties can achieve incremental loads, when only the source's incremental extraction with a timestamp column provides the correct row-level filtering.

How to eliminate wrong answers

Option A is wrong because 'Column pattern' is used to dynamically detect and handle new columns in the data, not to filter rows based on a timestamp for incremental loads. Option B is wrong because 'Row sampling' is a debugging or testing feature that takes a random subset of rows, not a mechanism to select only new rows based on a timestamp. Option C is wrong because 'New rows only' is not a valid configuration in the sink transformation; the sink transformation writes data to the destination but does not control which rows are read from the source.

Full explanation →
278
MCQeasy

You are a data engineer at a retail company. You have designed a near real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs, which receives clickstream events from the company's e-commerce website. The output is written to an Azure SQL Database table for reporting. Each event includes fields: UserId, ProductId, EventType (e.g., 'click', 'purchase'), and Timestamp. The requirement is to calculate the number of purchases per product in a 5-minute tumbling window and update a SQL table. The Stream Analytics job has been running for a week, but the reporting team notices that the purchase counts in SQL are consistently lower than expected compared to a direct count from Event Hubs. You suspect that late-arriving events are being dropped. The job's configuration includes a 5-minute tumbling window with no late arrival policy. What should you do to fix the issue without losing data?

A.Modify the query to use a larger tumbling window (e.g., 10 minutes) and add a late arrival policy with a 5-minute grace period to allow late events to be included.
B.Modify the query to use TIMESTAMP BY on the EventHubs enqueued time instead of the event's Timestamp field.
C.Change the tumbling window to a hopping window with a 1-minute hop size to increase the frequency of output updates.
D.Add a second Stream Analytics job to process late-arriving events separately and union the results.
AnswerA

A larger window with a late arrival policy captures late-arriving events.

Why this answer

The current 5-minute tumbling window has no late arrival policy, so any event that arrives after the window ends is dropped. By increasing the window size to 10 minutes and adding a 5-minute late arrival grace period, you allow events that arrive up to 5 minutes late to still be included in the correct window, matching the actual purchase count from Event Hubs.

Exam trap

The trap here is that candidates may think increasing the window size or changing the window type (hopping) will fix the issue, but the core problem is the lack of a late arrival policy to handle events that arrive after the window closes.

How to eliminate wrong answers

Option B is wrong because using TIMESTAMP BY on the Event Hubs enqueued time does not solve the late arrival issue; it only changes the timestamp used for windowing, but late events still arrive after the window closes and would be dropped without a late arrival policy. Option C is wrong because changing to a hopping window with a 1-minute hop size increases output frequency but does not address late-arriving events; late events would still be dropped if they arrive after the window end time. Option D is wrong because adding a second Stream Analytics job to process late events separately is unnecessarily complex and introduces data duplication and reconciliation challenges; the correct approach is to use a late arrival policy within a single job.

Full explanation →
279
Multi-Selectmedium

Which TWO actions can you take to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Select 2 answers
A.Scale up the SQL pool to a higher DWU.
B.Replicate small dimension tables.
C.Use heap indexes for fact tables.
D.Use round-robin distribution for all large fact tables.
E.Use hash distribution on a column used in joins and aggregations.
AnswersB, E

Replication reduces data movement for joins with fact tables.

Why this answer

Replicating small dimension tables across all distributions reduces data movement during joins and improves query performance. Option E is correct because hash distribution on a column used in joins and aggregations ensures that rows with the same key are colocated on the same distribution, minimizing data shuffling. Option A is incorrect: scaling up increases resources but is not a targeted performance optimization and may not address underlying distribution or indexing issues.

Option C is incorrect: heap indexes are generally not optimal for fact tables because they lack compression and indexing benefits; clustered columnstore indexes are recommended. Option D is incorrect: round-robin distribution distributes data evenly but does not reduce data movement for joins and aggregations, often leading to poor query performance.

Full explanation →
280
Multi-Selecteasy

You are designing a data storage solution for a real-time dashboard that displays streaming data from Azure Event Hubs. The data must be stored in a format that supports both real-time and batch analytics with minimal latency. Which TWO technologies should you use?

Select 2 answers
A.Azure Stream Analytics
B.Azure Data Factory
C.Azure Synapse Analytics
D.Azure Analysis Services
E.Azure SQL Database
AnswersA, C

Stream Analytics processes streaming data in real-time.

Why this answer

Azure Stream Analytics is correct because it provides real-time stream processing with low latency, directly ingesting data from Event Hubs and outputting to storage or analytics services. It enables both real-time dashboard queries and batch analytics by writing to a staging store like Azure Data Lake Storage, which can then be queried by Azure Synapse Analytics for historical analysis.

Exam trap

The trap here is that candidates often confuse Azure Data Factory as a real-time processing tool, but it is strictly a batch orchestration service with no native stream processing capability.

Full explanation →
281
MCQhard

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

A.Increase the MAXDOP setting in the query
B.Create statistics on the columns used in joins and filters
C.Move the data to a dedicated SQL pool
D.Convert the Parquet files to CSV format
AnswerB

Statistics help the optimizer generate efficient plans.

Why this answer

(Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance.

Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Full explanation →
282
Multi-Selecthard

A company uses Azure Synapse Analytics with a dedicated SQL pool. Data engineers need to implement column-level security so that only users with the 'Manager' role can see salary columns. Which TWO actions should they take?

Select 1 answer
A.Create a stored procedure that checks the user's role and returns the appropriate columns.
B.Grant the 'Manager' role SELECT permission on the security policy.
C.Create a security policy with a filter predicate on the salary column using the function, and set the state to ON with BLOCK predicate.
D.Create an inline table-valued function that returns 1 if the user is a member of the 'Manager' role, else 0.
E.Use GRANT SELECT ON OBJECT::[dbo].[Employee](Salary) TO [Manager] to grant access to the salary column.
AnswersA

Correct. Creating a stored procedure that checks the user's role and returns only the allowed columns is a valid way to implement column-level security at the application level.

Why this answer

To implement column-level security in Azure Synapse Analytics dedicated SQL pool, a valid approach is to create a stored procedure that checks the user's role and returns only permitted columns, as dedicated SQL pool does not support column-level GRANT permissions or native column-level security. GRANT SELECT on a specific column (Option E) is not supported in dedicated SQL pool and will result in an error. Options C and D describe components of row-level security, not column-level security.

Exam trap

Candidates often confuse column-level security with row-level security. Column-level security in Azure Synapse dedicated SQL pool can be implemented via stored procedures (Option A), while security policies with filter predicates are for row-level security. GRANT on columns is not a valid approach in dedicated SQL pool.

Full explanation →
283
MCQhard

Refer to the exhibit. You have created the custom RBAC role shown and assigned it to a security group. Members of the group report that they can read blobs in the storage account but cannot list the contents of the container. What is the most likely reason for this issue?

A.Custom roles are not supported for Azure Data Lake Storage Gen2.
B.The role is scoped to the storage account but not to the container.
C.The role does not include the permission to list blobs in a container.
D.The role lacks the 'read' data action for blobs.
AnswerC

To list blobs, the role needs 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read' permission, but that only reads individual blobs. The 'list' action requires 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read' with the 'list' permission, or the role must include 'Microsoft.Storage/storageAccounts/blobServices/containers/read' which allows listing container contents.

Why this answer

The custom RBAC role includes the 'read' data action for blobs, which allows reading blob data, but it does not include the 'list' action for blobs. Without the 'list' permission, users cannot list the blobs within a container. In Azure RBAC for storage, the 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read' action permits reading blob content and properties, but listing blobs requires the 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/list' action (or equivalent at the container level). Therefore, the role lacks the necessary permission to list blobs.

Option A is incorrect because custom RBAC roles are fully supported for Azure Data Lake Storage Gen2. Option B is incorrect because the scope of the role at the storage account level does not prevent listing blobs; the issue is missing data actions. Option D is incorrect because the role already includes the 'read' data action for blobs; the missing action is 'list'.

Full explanation →
284
MCQeasy

A data engineer needs to process a large dataset stored in Azure Blob Storage using Azure Databricks. The dataset consists of millions of small CSV files. The processing job is slow due to the overhead of reading many small files. Which technique should be used to improve performance?

A.Increase the number of worker nodes in the cluster
B.Convert the CSV files to Parquet format
C.Coalesce the small files into larger files using a Databricks notebook
D.Use Delta Lake caching to store the data in memory
AnswerC

Reduces file count and improves read performance.

Why this answer

Coalescing the millions of small CSV files into larger files reduces the metadata overhead and I/O operations when reading from Azure Blob Storage. Databricks can then process fewer, larger files more efficiently, as each task handles a substantial data chunk rather than incurring the cost of opening and closing many small files.

Exam trap

The trap here is that candidates often assume performance issues are always solved by scaling out (Option A) or by switching formats (Option B), but the DP-203 exam specifically tests the understanding that small file overhead is a distinct problem requiring file consolidation.

How to eliminate wrong answers

Option A is wrong because simply adding more worker nodes does not address the root cause of small file overhead; it may even exacerbate the problem by increasing the number of tasks that each try to read a small file, leading to more scheduler and I/O contention. Option B is wrong because converting CSV to Parquet improves compression and columnar read performance but does not reduce the number of files; the overhead of opening millions of small Parquet files remains similar to CSV. Option D is wrong because Delta Lake caching stores data in memory after it is read, but it does not reduce the initial read overhead of millions of small files; the first read still suffers from the same small file penalty.

Full explanation →
285
MCQhard

You are migrating an on-premises SQL Server database to Azure. The database has a large fact table (500 GB) and several dimension tables (10 GB total). Reporting queries join the fact table with dimension tables and aggregate by date. Which Azure service and table design should you recommend to minimize query latency?

A.Azure Synapse SQL Pool with replicated tables for both fact and dimension tables
B.Azure SQL Database Hyperscale with columnstore indexes
C.Azure Synapse SQL Pool with hash distribution on the fact table's foreign key and round-robin for dimension tables
D.Azure SQL Database with rowstore indexes and a single database
AnswerC

Hash distribution enables co-location joins, improving query performance.

Why this answer

Azure Synapse SQL Pool with hash distribution on the fact table's foreign key ensures that related rows from the fact and dimension tables are co-located on the same compute node, minimizing data movement during joins. Round-robin distribution for the small dimension tables is appropriate since they are under 1 GB each and can be broadcast to all nodes, further reducing shuffle overhead. This design optimizes parallel query execution for large fact table aggregations by date.

Exam trap

The trap here is that candidates often confuse replicated tables as a universal performance booster, not realizing that replicating a large fact table is impractical and that hash distribution on the join key is the correct pattern for large fact tables in a distributed MPP environment.

How to eliminate wrong answers

Option A is wrong because replicated tables are designed for small dimension tables (typically under 1 GB), but replicating a 500 GB fact table would consume excessive storage and cause high replication overhead, defeating the purpose of minimizing query latency. Option B is wrong because Azure SQL Database Hyperscale is optimized for transactional workloads with high concurrency and large database sizes, not for large-scale analytical aggregations across a massive fact table; columnstore indexes help but the single-node architecture cannot match the distributed parallel processing of Synapse SQL Pool. Option D is wrong because rowstore indexes and a single database instance lack the distributed compute and storage needed to efficiently join and aggregate a 500 GB fact table, leading to high I/O and long query times.

Full explanation →
286
MCQhard

A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?

A.Rebuild the columnstore indexes on the table
B.Convert the table to a rowstore heap
C.Create statistics on the date column
D.Increase the number of partitions for the table
AnswerA

Rebuilding reduces fragmentation and improves partition pruning.

Why this answer

Rebuilding columnstore indexes reorganizes and compresses the data, which can improve partition elimination by ensuring that each partition's columnstore segments are well-structured and aligned with the partition boundaries. Over time, as data is inserted, updated, or deleted, columnstore indexes can become fragmented, leading to inefficient partition pruning and slower query performance on date-range filters.

Exam trap

The trap here is that candidates often assume poor partition pruning is always due to incorrect partitioning strategy or missing statistics, overlooking the fact that columnstore index fragmentation can prevent effective partition elimination even when the partition scheme is correct.

How to eliminate wrong answers

Option B is wrong because converting the table to a rowstore heap would eliminate the benefits of columnar storage and compression, likely worsening query performance for analytical workloads. Option C is wrong because statistics on the date column help the optimizer estimate cardinality but do not directly fix partition pruning issues caused by fragmented columnstore indexes. Option D is wrong because increasing the number of partitions can actually degrade performance by creating too many small partitions, leading to overhead and inefficient partition elimination.

Full explanation →
287
Multi-Selecthard

You are optimizing an Azure Synapse Analytics dedicated SQL pool that runs a mix of reporting and ETL workloads. The ETL jobs often encounter resource wait times due to concurrent reporting queries. You need to ensure that ETL jobs always get the resources they need. Which two actions should you take? (Choose two.)

Select 2 answers
A.Increase the DWU (Data Warehouse Units) to provide more overall resources.
B.Assign HIGH importance to the ETL workload classifier.
C.Enable result set caching for reporting queries.
D.Create materialized views for common reporting aggregations.
E.Create a workload group for ETL with a minimum resource percentage and assign it to a dedicated resource pool.
AnswersB, E

HIGH importance ensures ETL queries are prioritized over lower importance reporting queries.

Why this answer

The correct actions are B and E. Assigning HIGH importance to the ETL workload classifier (B) ensures that ETL queries are prioritized over lower-importance reporting queries. Creating a workload group for ETL with a minimum resource percentage and assigning it to a dedicated resource pool (E) guarantees a baseline of resources for ETL, preventing resource starvation.

Option A (increasing DWU) adds overall resources but does not guarantee ETL gets priority. Option C (result set caching) and Option D (materialized views) improve reporting performance but do not ensure ETL resource allocation.

Full explanation →
288
MCQmedium

You are developing a data processing pipeline in Azure Data Factory. The pipeline must copy data from an on-premises SQL Server to Azure Blob Storage. The data volumes are large, and the network connection is unreliable. Which configuration should you use to ensure resilience and resume capability?

A.Use a staging copy with Azure Blob Storage as an intermediate store.
B.Configure the copy activity with fault tolerance and enable 'Skip incompatible rows'. Use a self-hosted integration runtime for the source.
C.Increase the 'parallelCopies' setting to 10 and use a staging table.
D.Use PolyBase to load data directly into Azure Synapse Analytics.
AnswerB

Fault tolerance allows the copy to continue despite errors, and self-hosted IR supports checkpointing for resume.

Why this answer

Using a self-hosted integration runtime enables connectivity to on-premises SQL Server, and enabling fault tolerance with 'Skip incompatible rows' allows the copy activity to continue despite transient failures or data inconsistencies. This configuration ensures resilience by automatically retrying failed operations and skipping rows that cause errors, which is critical for large data volumes over an unreliable network.

Exam trap

Microsoft often tests the misconception that staging or parallelization alone provides resilience, but the key is that fault tolerance with row-level skipping is the only option that directly handles data errors and network interruptions without failing the entire copy.

How to eliminate wrong answers

Option A is wrong because using Azure Blob Storage as an intermediate store (staging copy) adds unnecessary complexity and cost without addressing the core issue of network unreliability; it is typically used for cross-region or cross-account copies, not for resilience against transient failures. Option C is wrong because increasing 'parallelCopies' to 10 can improve throughput but does not provide fault tolerance or resume capability; it may even exacerbate failures by creating more concurrent connections that can fail independently. Option D is wrong because PolyBase is a technology for loading data into Azure Synapse Analytics, not for copying data to Azure Blob Storage, and it does not address the on-premises source or network resilience requirements.

Full explanation →
289
MCQeasy

You are monitoring an Azure Data Factory pipeline that runs hourly. The pipeline executes a stored procedure in an Azure SQL Database. Recently, you have observed that the pipeline occasionally fails with a 'Deadlock' error when the stored procedure runs. The Azure SQL Database is configured with the 'Read Committed Snapshot' isolation level enabled. You need to resolve the deadlock issue with minimal impact on performance. The stored procedure updates multiple tables in a single transaction and is critical for reporting. What should you do?

A.Change the stored procedure to use NOLOCK hints
B.Remove the transaction from the stored procedure
C.Add retry logic in the Data Factory pipeline for the stored procedure activity
D.Disable the 'Read Committed Snapshot' isolation level
AnswerC

Retries handle transient deadlocks gracefully

Why this answer

Adding retry logic in the Data Factory pipeline allows the pipeline to automatically retry the stored procedure activity when a deadlock error occurs. Deadlocks are transient and often resolve on retry, minimizing impact on performance without changing the database isolation level or transaction integrity. Option A is wrong because using NOLOCK hints can lead to dirty reads and data inconsistency, which is unacceptable for a critical reporting procedure.

Option B is wrong because removing the transaction would break the atomicity of the multiple table updates, potentially leaving data in an inconsistent state. Option D is wrong because disabling Read Committed Snapshot (RCSI) would likely increase blocking and contention, potentially making deadlocks worse or causing other performance issues.

Full explanation →
290
Multi-Selectmedium

Which TWO Azure services can be used to implement a data lakehouse architecture with Delta Lake?

Select 2 answers
A.Azure Databricks
B.Azure Data Factory
C.Azure Cosmos DB
D.Azure Synapse Analytics serverless SQL pool
E.Azure SQL Database
AnswersA, D

Databricks is the primary platform for Delta Lake.

Why this answer

Azure Databricks is correct because it provides a unified analytics platform with native Delta Lake support, enabling ACID transactions, schema enforcement, and time travel on data lakes. Delta Lake is an open-source storage layer that brings reliability to data lakes, and Azure Databricks is the primary service for running Delta Lake workloads at scale.

Exam trap

The trap here is that candidates often assume Azure Data Factory can implement a data lakehouse because it can copy data to ADLS, but they miss that a data lakehouse requires a compute engine (like Spark or serverless SQL) and a transactional storage layer (Delta Lake), not just data movement.

Full explanation →
291
MCQeasy

You have an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to implement security controls to prevent data exfiltration by a malicious insider who has Contributor role access. Which Azure feature should you use?

A.Enable diagnostic settings to log all access to the storage account.
B.Remove the Contributor role and assign a custom role with read-only permissions.
C.Configure network firewall rules to allow only trusted IP addresses.
D.Apply an Azure Policy that denies data access from unapproved locations.
AnswerD

Azure Policy can enforce network restrictions to prevent data exfiltration.

Why this answer

Azure Policy can enforce a deny effect on data access requests originating from unapproved locations, effectively preventing data exfiltration even if the user has Contributor role. This is a preventive control, not just monitoring. Option A (diagnostic settings) is detective only.

Option B (removing Contributor role) might reduce permissions but does not prevent the insider from using their current role. Option C (network firewall rules) can be bypassed if the insider accesses from within the trusted network. Therefore, Azure Policy with location-based deny is the most effective prevention.

Full explanation →
292
Drag & Dropmedium

Drag and drop the steps to implement Azure Data Lake Storage Gen2 lifecycle management to move data to cool and archive tiers into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

After creating the account, define a lifecycle rule with conditions and actions, then apply it.

Full explanation →
293
MCQhard

Refer to the exhibit. A data engineer notices that Spark jobs on this cluster are running slower than expected. The cluster is using spot instances with fallback. Which factor is most likely causing the performance degradation?

A.The spark.sql.adaptive settings are misconfigured
B.The cluster is using spot instances which may be frequently reclaimed
C.The node type Standard_DS3_v2 is too small
D.The autoscale configuration limits max workers to 8
AnswerB

Spot instances are cheaper but can be terminated at any time, causing job failures or delays due to recomputation.

Why this answer

Spot instances can be preempted, causing delays. The configuration sets 'first_on_demand' to 1, meaning only 1 node is on-demand, and the rest are spot. Spot instances can be reclaimed, leading to recomputation and slower performance.

The adaptive query execution settings are generally beneficial, not harmful.

Full explanation →
294
MCQhard

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

A.Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database
B.Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly
C.Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database
D.Use Azure Functions to process events and write to SQL Database
AnswerC

Minimal latency and operational overhead.

Why this answer

Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Exam trap

The trap here is that candidates often over-engineer the solution by choosing Databricks (Option A) for its flexibility, overlooking that Stream Analytics is purpose-built for low-latency, windowed aggregations with minimal operational overhead, while Databricks adds unnecessary complexity for simple time-based aggregations.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces significant operational overhead for cluster management and is overkill for simple minute-level aggregation, plus it adds latency from Spark job initialization and checkpointing. Option B is wrong because Event Hubs Capture stores raw data in blob storage, and using Azure Data Factory to load hourly into SQL Database introduces at least 60 minutes of latency, failing the real-time requirement. Option D is wrong because Azure Functions are stateless and event-driven, lacking built-in windowing capabilities for time-based aggregation, so you would need to implement custom state management (e.g., using Durable Functions or external storage), increasing complexity and latency.

Full explanation →
295
Multi-Selectmedium

You are building a real-time processing solution using Azure Stream Analytics. The solution must handle out-of-order events and late arrivals. Which THREE mechanisms should you configure in the Stream Analytics job?

Select 3 answers
A.Set an 'Out-of-order tolerance' window in the event ordering settings.
B.Adjust the 'Streaming units' to handle higher throughput.
C.Configure a 'Late-arrival tolerance' window.
D.Enable 'Event hub capture' to store raw events for reprocessing.
E.Choose an output adapter that supports exactly-once delivery.
AnswersA, C, E

This defines how late events can be reordered.

Why this answer

Azure Stream Analytics allows you to configure an 'Out-of-order tolerance' window in the event ordering settings. This window defines the maximum time difference that out-of-order events can be reordered before being considered late. By setting this tolerance, you ensure that events arriving slightly out of sequence are still processed correctly, which is critical for real-time analytics where event order matters.

Exam trap

The trap here is that candidates confuse scaling mechanisms (like Streaming units) or storage features (like Event Hubs Capture) with event ordering controls, which are specifically designed to manage temporal anomalies in streaming data.

Full explanation →
296
MCQmedium

You are designing a data pipeline in Azure Data Factory that processes sensitive customer data. The pipeline must use a copy activity to move data from Azure Blob Storage to Azure Synapse Analytics. You need to ensure that data is encrypted in transit and at rest, and that the pipeline uses the most secure authentication method. Which authentication method should you use for the sink dataset?

A.Managed Identity
B.Storage account key
C.Service principal
D.SQL authentication
AnswerA

Managed Identity eliminates the need for secrets and provides secure, seamless authentication.

Why this answer

Managed Identity is the most secure authentication method because it uses Azure Active Directory (Azure AD) to authenticate to Azure Synapse Analytics without storing any credentials in the pipeline. This eliminates the risk of secret exposure and provides automatic credential rotation. SQL authentication (Option D) requires embedded credentials, service principal (Option C) requires secret management, and storage account key (Option B) is a shared secret that cannot be used to authenticate to Synapse as a sink.

Full explanation →
297
MCQeasy

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

A.Querying the external table automatically imports data into a round-robin distribution.
B.The table cannot be queried until the data is imported into the dedicated SQL pool.
C.You can query the external table using standard T-SQL SELECT statements.
D.You must first create a PolyBase external table before querying.
AnswerC

External tables support SELECT queries.

Why this answer

An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Exam trap

The trap here is that candidates often assume external tables require an explicit import step before querying, but in reality, PolyBase allows direct querying of external data without any data movement into the dedicated SQL pool.

How to eliminate wrong answers

Option A is wrong because querying an external table does not automatically import data into a round-robin distribution; external tables remain external and data is not stored in the pool unless you explicitly use CREATE TABLE AS SELECT (CTAS) to import it. Option B is wrong because the external table can be queried immediately after creation without importing data; the data stays in external storage and is accessed on-the-fly by PolyBase. Option D is wrong because the T-SQL statements in the exhibit already create a PolyBase external table (using CREATE EXTERNAL TABLE with an external data source and file format), so no additional PolyBase external table creation is needed before querying.

Full explanation →
298
Multi-Selectmedium

You are designing a data storage solution for a healthcare company that must comply with HIPAA. The solution needs to store structured patient records and unstructured medical images. Data must be encrypted at rest and in transit. Which TWO storage solutions meet these requirements?

Select 2 answers
A.Azure Redis Cache
B.Azure Blob Storage
C.Azure Table Storage
D.Azure Cosmos DB
E.Azure SQL Database
AnswersB, E

Blob Storage supports encryption at rest and in transit, and is suitable for images.

Why this answer

Azure Blob Storage supports storing unstructured data like medical images and offers encryption at rest via Storage Service Encryption (SSE) and in transit via HTTPS/TLS. It is HIPAA-eligible when configured with appropriate access controls and logging, making it suitable for the unstructured image component of the solution.

Exam trap

The trap here is that candidates may choose Cosmos DB or Table Storage for structured data, overlooking that Azure SQL Database is the preferred HIPAA-compliant relational store for structured patient records, while Blob Storage is the correct choice for large unstructured images.

Full explanation →
299
Multi-Selectmedium

Which TWO actions should you take to secure data at rest in Azure Data Lake Storage Gen2? (Choose TWO)

Select 2 answers
A.Use Azure RBAC to grant least-privilege access to the storage account.
B.Apply dynamic data masking to sensitive columns.
C.Enable Azure Storage Service Encryption (SSE) for data at rest.
D.Configure firewall rules to restrict network access.
E.Enable audit logging for the storage account.
AnswersA, C

RBAC controls access, a security measure for data at rest.

Why this answer

Correct answers: A and C. A: Use Azure RBAC to grant least-privilege access to the storage account—this ensures only authorized users can access data. C: Enable Azure Storage Service Encryption (SSE) for data at rest—this encrypts data automatically at the storage level.

B is incorrect because dynamic data masking is used for databases, not for Azure Data Lake Storage Gen2. D is incorrect because firewall rules control network access, not data at rest. E is incorrect because audit logging is for monitoring, not securing data at rest.

Full explanation →
300
MCQmedium

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

A.The statistics on the tables are outdated or missing.
B.The tables are distributed using round-robin distribution.
C.Result-set caching is disabled.
D.The resource class for the user is set to smallrc.
AnswerA

Outdated or missing statistics can cause the optimizer to choose suboptimal access methods like table scans.

Why this answer

Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Exam trap

The trap here is that candidates often confuse performance issues caused by distribution type or resource class with the optimizer's reliance on statistics, overlooking that even with optimal distribution and sufficient resources, stale statistics force scans instead of seeks.

How to eliminate wrong answers

Option B is wrong because round-robin distribution evenly distributes data across distributions without considering join keys, which can cause data movement but does not directly cause table scans instead of seeks; scans are a symptom of missing statistics or poor index usage. Option C is wrong because result-set caching only affects repeated execution of the same query by storing results, not the initial query plan choice between scan and seek. Option D is wrong because the resource class (e.g., smallrc) controls memory and concurrency slots for the user, not the query optimizer's decision to use scans versus seeks; scans occur regardless of resource class if statistics are stale.

Full explanation →
Page 3

Page 4 of 11

Page 5

All pages

1234567891011

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

Secure, monitor, and optimize data storage and data processingDesign and implement data storageDevelop data processing
See all domains with question counts →