Knowledge + Practice

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 301–375

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

Take a mock exam Exam hub

Page 5 of 11

301

Multi-Selecteasy

Which TWO methods can you use to optimize the cost of storing data in Azure Data Lake Storage Gen2?

Select 2 answers

A.Use customer-managed keys for encryption.

B.Configure lifecycle management policies to move older data to the cool or archive tier.

C.Enable soft delete for blobs.

D.Use Azure Blob Storage access tiers: hot, cool, and archive.

E.Enable geo-redundant storage (GRS) for disaster recovery.

AnswersB, D

Reduces storage cost by moving data to cheaper tiers.

Why this answer

Options B and D are correct. Option B: Configure lifecycle management policies to automatically move data to cooler tiers (cool or archive) based on age, reducing storage costs. Option D: Azure Blob Storage access tiers (hot, cool, archive) allow you to choose the appropriate tier for data based on access frequency, optimizing cost.

Option A is incorrect because using customer-managed keys for encryption does not affect storage costs; encryption is free. Option C is incorrect: enabling soft delete adds storage overhead for deleted versions, increasing cost. Option E is incorrect: geo-redundant storage (GRS) replicates data to another region, increasing storage cost.

Full explanation →

302

Multi-Selecthard

You are a Data Engineer at Contoso Ltd. The company operates an e-commerce platform that generates streaming data from user interactions (clicks, page views, purchases) at a rate of 50,000 events per second. The data is ingested into Azure Event Hubs. You need to design a data processing solution that meets the following requirements: 1. Real-time dashboards must display aggregated metrics (e.g., total sales, active users) with a latency of less than 2 seconds. 2. Historical data must be stored in Azure Data Lake Storage Gen2 in Delta Lake format for batch analytics. 3. The solution must support exactly-once semantics for the streaming output to both the dashboard and the data lake. 4. You must use Azure Stream Analytics as the primary processing engine. 5. The output to the dashboard should use Azure Power BI, while the output to the data lake should use Azure Synapse Analytics serverless SQL pool for querying. You have configured an Azure Stream Analytics job with Event Hubs as input. For output, you added a Power BI dataset and an Azure Data Lake Storage Gen2 output. However, you discover that the Power BI dataset is being updated with duplicate records, and the data lake output sometimes misses small windows of data during job restarts. Which two actions should you take to meet the requirements? (Choose two.) A. Change the Power BI output to use the 'Exactly once' output mode. B. Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once. C. Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output. D. Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Select 2 answers

A.Change the Power BI output to use the 'Exactly once' output mode. [CORRECT]

B.Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once. [wrong]

C.Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output. [CORRECT]

D.Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode. [wrong]

AnswersA, C

Correct: Power BI output can be configured with 'Exactly once' output mode, which eliminates duplicate records by ensuring each event is written only once to the dataset.

Why this answer

Power BI output supports 'Exactly once' output mode, which prevents duplicate records in the dashboard. Option C is correct because enabling checkpointing and using 'Exactly once' output mode for Azure Data Lake Storage Gen2 ensures exactly-once delivery to the data lake and prevents data loss during job restarts. Option B is incorrect because changing Event Hubs compatibility level to 1.0 does not guarantee exactly-once semantics; it only affects the serialization format.

Option D is incorrect because using a second Stream Analytics job adds unnecessary complexity, and 'At least once' mode for Power BI would still allow duplicates.

Full explanation →

303

MCQmedium

A company uses Azure Databricks for data processing. They want to monitor the performance of Spark jobs and set up alerts for job failures. Which Azure service should they use?

A.Azure Advisor

B.Azure Sentinel

C.Azure Log Analytics

D.Azure Monitor

AnswerD

Azure Monitor collects metrics and logs from Azure Databricks, allows performance analysis of Spark jobs, and supports alert rules for job failures.

Why this answer

Azure Monitor is the central service for collecting metrics and logs from Azure Databricks, enabling performance monitoring and alerting on Spark jobs. Option A is incorrect because Azure Advisor provides recommendations but not real-time monitoring or alerts. Option B is incorrect because Azure Sentinel is a SIEM solution for security incidents, not for job performance monitoring.

Option C is incorrect because Azure Log Analytics is a component of Azure Monitor used for log analysis, but the overarching service for monitoring and alerts is Azure Monitor.

Exam trap

Candidates often confuse Azure Monitor with Azure Log Analytics, but Log Analytics is a subset of Monitor. The question asks for the service to use, which is Azure Monitor.

Full explanation →

304

MCQmedium

Your organization uses Azure Synapse Analytics with serverless SQL pools. You need to ensure that only users with specific Microsoft Entra ID roles can query external tables referencing Azure Data Lake Storage Gen2. What should you configure?

A.Assign a managed identity to the serverless SQL pool and grant it Storage Blob Data Reader on the storage account.

B.Use Azure RBAC to assign Storage Blob Data Reader role to the users on the storage account.

C.Configure a storage account firewall to allow only the Synapse workspace IP range.

D.Grant SELECT permission on the external table to specific Microsoft Entra ID users or groups.

AnswerD

This restricts query access based on user identity.

Why this answer

Granting SELECT permission on the external table directly controls which Microsoft Entra ID users or groups can query the data via the serverless SQL pool. This approach uses Azure AD authentication to restrict access at the query level, not at the storage layer. Option A is incorrect because assigning a managed identity to the serverless pool provides a service identity, but it does not restrict access per user.

Option B is incorrect because Azure RBAC on the storage account grants data access at the storage level, not through the Synapse SQL engine, and does not limit querying via external tables. Option C is incorrect because the storage account firewall controls network access, not user identity, and does not prevent authorized users from other networks from querying.

Full explanation →

305

MCQmedium

You are reviewing an Azure Resource Manager template for an Azure SQL Database auditing policy. Based on the exhibit, which of the following is true?

A.Audit logs will be retained indefinitely.

B.The audit policy will use default audit actions and groups.

C.Audit logs will be sent to Azure Log Analytics.

D.Audit logs will be written to Azure Blob Storage.

AnswerD

The storageEndpoint property specifies the blob storage account for audit logs.

Why this answer

The template shows the 'state' property set to 'Enabled' and includes a 'storageEndpoint' property, which indicates that audit logs are configured to be written to Azure Blob Storage. Option A is incorrect because 'retentionDays' is set to 90 days, so logs are not retained indefinitely. Option B is incorrect because 'auditActionsAndGroups' is specified in the template, meaning default audit actions and groups are not used.

Option C is incorrect because the destination is Blob Storage, not Azure Log Analytics, as indicated by the 'storageEndpoint' property.

Full explanation →

306

MCQhard

You are reviewing an Azure Policy assignment that uses the above JSON to define a role-based access control (RBAC) action. What is the primary purpose of this policy?

A.To assign RBAC roles to users for the storage account.

B.To enable delegation of access to a specific blob.

C.To allow users to set permissions on storage account containers.

D.To authorize generation of a shared access signature (SAS) token for the storage account.

AnswerD

The 'listAccountSas' action generates an account-level SAS token.

Why this answer

The policy JSON defines a role-based access control (RBAC) action that grants the 'Microsoft.Storage/storageAccounts/listAccountSas/action' permission. This specific action authorizes the generation of a shared access signature (SAS) token at the storage account level, not at the container or blob level. Therefore, the primary purpose is to allow the generation of an account SAS token, which provides delegated access to storage services.

Exam trap

The trap here is that candidates confuse the account-level SAS generation action with container or blob-level delegation, or mistakenly think the policy itself assigns roles rather than defining a permission that can be used in a custom role.

How to eliminate wrong answers

Option A is wrong because the policy does not assign RBAC roles to users; it defines a permission action that can be included in a role definition, not the assignment of roles themselves. Option B is wrong because delegation of access to a specific blob requires a service SAS or user delegation SAS, which uses different actions (e.g., 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/...'), not the account-level listAccountSas action. Option C is wrong because setting permissions on storage account containers is managed via container-level RBAC actions (e.g., 'Microsoft.Storage/storageAccounts/blobServices/containers/write') or container ACLs, not the account-level SAS generation action.

Full explanation →

307

MCQeasy

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

A.Increase the provisioned RU/s on the container to handle both transactional and analytical queries.

B.Change the partition key to /date to optimize for analytical queries.

C.Create a materialized view using the change feed and store aggregated data in a separate container.

D.Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.

AnswerD

Analytical store provides a separate columnar store for efficient analytical queries.

Why this answer

Enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Exam trap

The trap here is that candidates may think increasing RU/s or changing the partition key is a simpler fix, but the DP-203 exam specifically tests the understanding that analytical workloads must be isolated from transactional workloads using a dedicated analytical store like Synapse Link.

How to eliminate wrong answers

Option A is wrong because simply increasing provisioned RU/s does not separate the analytical workload from the transactional workload; both would still compete for the same throughput, and analytical queries would continue to consume high RUs, potentially throttling transactions. Option B is wrong because changing the partition key to /date would destroy the existing container's partitioning strategy, causing hot partitions for high-volume dates and severely degrading transactional performance for customer-based lookups; partition keys cannot be changed after creation without data migration. Option C is wrong because creating a materialized view using the change feed requires custom code to maintain the view, adds operational complexity, and still consumes RUs on the source container for change feed processing, whereas the analytical store provides a fully managed, zero-ETL solution.

Full explanation →

308

MCQmedium

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slower than expected. The files are large (500 MB each) and not partitioned. You need to improve query performance without moving data. What should you do?

A.Convert the Parquet files to Delta format using Azure Databricks.

B.Change the files to CSV format with a header row.

C.Use OPENROWSET with BULK and specify ROWSET_OPTIONS for better performance.

D.Create external tables with explicit file format and partition elimination hints.

AnswerD

External tables with file format optimization can improve query performance by enabling metadata-based pruning.

Why this answer

Creating external tables with file format options allows the serverless SQL pool to use metadata for optimization. Changing the file format to CSV or using OPENROWSET without external tables does not improve performance as much. Converting to Delta format requires data movement.

Partitioning the files would be best, but that requires reorganizing files.

Full explanation →

309

MCQmedium

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

A.Azure Table Storage.

B.Azure SQL Database with in-memory OLTP.

C.Azure Cosmos DB with analytical store.

D.Azure Data Explorer (ADX).

AnswerD

ADX is designed for real-time analytics on large volumes of streaming data with sub-second query latency.

Why this answer

Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and log analytics, ingesting 10,000+ events per second with sub-second query latency via its columnar storage and distributed query engine. It supports real-time analytics on streaming IoT data without requiring pre-defined schemas or indexing, making it the optimal choice for this scenario.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's analytical store with a real-time analytics solution, but it is designed for hybrid transactional/analytical processing (HTAP) on operational data, not for high-velocity streaming telemetry analytics where ADX excels.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for simple lookups and batch operations, not for real-time analytics with sub-second query latency on high-velocity streaming data. Option B is wrong because Azure SQL Database with in-memory OLTP is optimized for transactional workloads (OLTP) with high concurrency, not for analytical queries on high-volume telemetry; it cannot sustain 10,000 events per second with sub-second analytical latency. Option C is wrong because Azure Cosmos DB with analytical store is designed for globally distributed operational data with eventual consistency for analytics, but its analytical store uses a separate columnar format optimized for read-heavy workloads, not real-time sub-second queries on streaming IoT telemetry; it introduces higher latency for ingestion and query compared to ADX.

Full explanation →

310

MCQeasy

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

A.Use a temporal window function with a 'late arrival' policy specified in the query.

B.Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.

C.Define a watermark in the query to specify a maximum out-of-order tolerance.

D.Set the event ordering policy to 'Adjust' to reorder events within a certain time window.

AnswerB

Input Order policy allows handling late events, and Stream Analytics ensures exactly-once delivery to SQL Database.

Why this answer

The Input Order section in Azure Stream Analytics job configuration allows you to set a late arrival tolerance window, which handles late-arriving events by buffering them for a specified duration. This ensures that events arriving after their timestamp are still included in the correct window for aggregation, supporting exactly-once semantics when combined with checkpointing and idempotent output to Azure SQL Database.

Exam trap

The trap here is that candidates confuse the late arrival tolerance window (set in job configuration) with window functions or watermarks used in other streaming systems, leading them to incorrectly select query-level options like temporal window functions or watermarks.

How to eliminate wrong answers

Option A is wrong because temporal window functions (e.g., TumblingWindow, HoppingWindow) define the aggregation window but do not include a 'late arrival' policy parameter; late arrival handling is configured at the job level, not in the query. Option C is wrong because watermarks are a concept in stream processing frameworks like Apache Flink, not a feature of Azure Stream Analytics; Stream Analytics uses event ordering policies instead. Option D is wrong because the 'Adjust' event ordering policy reorders events based on their timestamp but does not provide a configurable late arrival tolerance window; it only adjusts timestamps for out-of-order events within a fixed tolerance.

Full explanation →

311

MCQhard

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

A.Azure Synapse Spark with Structured Streaming

B.Azure Stream Analytics

C.Azure Data Factory mapping data flows

D.Azure Synapse dedicated SQL pool with T-SQL queries

AnswerB

Sub-second latency, native Event Hubs and Cosmos DB connectors.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Exam trap

The trap here is that candidates often confuse Azure Synapse Spark Structured Streaming (which is micro-batch, not true streaming) with a real-time engine, or they assume Azure Data Factory can handle streaming data because it supports 'real-time' triggers, but it cannot perform in-flight aggregations with sub-second latency.

How to eliminate wrong answers

Option A is wrong because Azure Synapse Spark with Structured Streaming is a batch-micro-batch engine with higher latency (typically seconds to minutes) and is not optimized for sub-5-second real-time aggregation; it is better suited for complex transformations on large datasets. Option C is wrong because Azure Data Factory mapping data flows are designed for batch ETL/ELT operations on data at rest, not for real-time streaming ingestion or low-latency aggregations. Option D is wrong because Azure Synapse dedicated SQL pool with T-SQL queries is a massively parallel processing (MPP) data warehouse for analytical queries on stored data, not for real-time stream processing; it cannot directly ingest streaming data from Event Hubs and perform sub-5-second aggregations.

Full explanation →

312

Multi-Selectmedium

Which THREE factors should you consider when designing a real-time streaming solution using Azure Stream Analytics to process IoT data from thousands of devices?

Select 3 answers

A.The need to join input data with reference data that changes every few seconds.

B.The batch size for output to Azure Synapse Analytics to minimize write transactions.

C.The windowing function and late arrival policy for handling out-of-order events.

D.The latency requirements for writing output to Azure Cosmos DB for NoSQL.

E.The number of streaming units and partitioning scheme for the input Event Hubs.

AnswersB, C, E

Batching reduces transaction costs and improves throughput.

Why this answer

Batching output to Azure Synapse Analytics reduces write transactions and improves cost efficiency. Option C is correct because windowing functions and late arrival policies are essential for handling out-of-order events in real-time streaming. Option E is correct because streaming units and partitioning determine throughput and scalability for processing IoT data.

Option A is incorrect because reference data that changes every few seconds would require frequent reloads, defeating the purpose of static reference data in Stream Analytics. Option D is incorrect because latency requirements for Cosmos DB are not a primary design factor; Stream Analytics can write to Cosmos DB, but the main considerations are throughput and consistency, not latency.

Full explanation →

313

MCQeasy

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

A.Set a cache size for the serverless SQL pool

B.Configure a dedicated SQL pool with auto-scaling

C.Use workload classification to assign resources

D.Enable auto-resume and auto-pause on the serverless SQL pool endpoint

AnswerD

Incorrect. Auto-resume and auto-pause do not scale compute resources; they only manage when the pool is active. Serverless SQL pools scale automatically without this feature.

Why this answer

Serverless SQL pools in Azure Synapse Analytics automatically scale compute resources based on workload demand without requiring any configuration. None of the provided options enable this automatic scaling. Auto-resume and auto-pause only control the pool's active state, not its compute size.

Dedicated SQL pool features like auto-scaling, cache sizing, and workload classification do not apply to serverless pools.

Exam trap

Candidates often assume that serverless SQL pools require a scaling configuration or that auto-resume/auto-pause scales compute resources. In reality, scaling is automatic and not configurable; auto-resume/auto-pause only manage availability.

How to eliminate wrong answers

Option A is wrong because serverless SQL pools do not have a configurable cache size; caching is managed automatically by the service and cannot be set by the user. Option B is wrong because a dedicated SQL pool with auto-scaling is a separate resource type that scales compute by adding or removing Data Warehouse Units (DWUs), but the question specifically asks about serverless SQL pools, which do not use dedicated compute resources. Option C is wrong because workload classification is a feature for dedicated SQL pools (formerly SQL Data Warehouse) to assign resources and priorities to different workloads; serverless SQL pools do not support workload classification as they automatically manage resource allocation.

Full explanation →

314

MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only users with a specific Microsoft Entra ID role can access the data, and all access must be logged for auditing. What should you implement?

A.Dynamic data masking

B.Azure RBAC at the SQL pool level

C.Row-level security (RLS) with a security policy

D.Column-level security

AnswerC

RLS restricts row access based on user context, and can be tied to Microsoft Entra ID roles.

Why this answer

Row-level security (RLS) is the correct choice because it restricts data access at the row level based on user context, such as Microsoft Entra ID role membership, and all access can be logged via auditing. Dynamic data masking (A) obfuscates data but does not restrict access. Azure RBAC (B) controls management plane access, not data access within the SQL pool.

Column-level security (D) restricts access to specific columns, not rows.

Full explanation →

315

MCQmedium

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with timeout errors. You need to improve reliability. What should you do?

A.Use a self-hosted integration runtime with high availability

B.Enable fault tolerance and use staging

C.Change the source to Azure SQL Database

D.Increase the degree of copy parallelism

AnswerB

Fault tolerance allows the copy activity to retry on transient errors, and staging improves performance and reliability.

Why this answer

Enabling fault tolerance with staging in Azure Data Factory allows the copy activity to automatically retry transient failures (such as timeout errors) by staging intermediate data in Azure Blob Storage. This mechanism uses a two-phase commit approach: data is first written to a staging location, and then committed to the final sink only after successful validation, which isolates the pipeline from intermittent source or sink failures.

Exam trap

The trap here is that candidates often confuse high availability of the integration runtime (Option A) with fault tolerance of the copy activity, not realizing that HA only protects the IR nodes, not the data transfer itself.

How to eliminate wrong answers

Option A is wrong because using a self-hosted integration runtime with high availability improves the reliability of the integration runtime itself (e.g., node failures), but does not address timeout errors caused by the copy activity's interaction with the source or sink; it does not provide retry logic for data transfer failures. Option C is wrong because changing the source to Azure SQL Database does not resolve timeout errors in the copy activity; it merely shifts the source location, and the pipeline could still experience timeouts due to network latency or throttling. Option D is wrong because increasing the degree of copy parallelism can actually exacerbate timeout issues by overwhelming the source or sink with concurrent requests, and it does not provide any fault tolerance or retry mechanism for transient failures.

Full explanation →

316

MCQmedium

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a security strategy that meets the following requirements: 1) All data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. 2) Access to the storage account must be restricted to specific virtual networks. 3) Users must authenticate using Microsoft Entra ID and be granted read-only access to the 'landing' container. You have configured the storage account with Azure Storage Service Encryption (SSE) using customer-managed keys. You have also configured firewall rules to allow access only from the required virtual network. However, users cannot access the 'landing' container even though they have the Storage Blob Data Reader role. What is the most likely issue?

A.The users have not been granted access to the Key Vault

B.The users do not have the Storage Blob Data Reader role assigned at the container scope

C.The firewall is blocking the users' IP addresses even though they are in the virtual network

D.The Key Vault firewall is blocking access from the storage account

AnswerD

Key Vault firewall must allow Azure services or specific storage account.

Why this answer

The most likely issue is that the Key Vault firewall is blocking access from the storage account. When using Azure Storage Service Encryption (SSE) with customer-managed keys, the storage account must be able to access the Key Vault to wrap and unwrap the encryption key. Even though the users have the Storage Blob Data Reader role and the storage account firewall allows the virtual network, if the Key Vault firewall does not permit access from the storage account (e.g., via 'Allow trusted Microsoft services' or a specific network rule), the storage service cannot decrypt the data, resulting in access failures.

Option D correctly identifies this. Options A and B are incorrect because the users have the correct RBAC role and the role is effective at the storage account level; the issue is not with user permissions but with the storage account's ability to reach the Key Vault. Option C is incorrect because the firewall rules are correctly configured for the virtual network.

Full explanation →

317

MCQeasy

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

A.Alter Row transformation

B.Incremental copy (watermark) pattern using a Lookup activity and a Copy activity

C.Schedule trigger

D.Lookup activity alone

AnswerB

The watermark pattern uses a lookup to get the last watermark value and a copy activity to copy data changed after that watermark.

Why this answer

The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Exam trap

The trap here is that candidates often confuse a scheduling mechanism (Schedule trigger) with the actual data processing logic required for incremental loads, or they mistakenly think a single activity like Lookup or Alter Row can handle the entire incremental copy workflow without understanding the need for a watermark pattern.

How to eliminate wrong answers

Option A is wrong because Alter Row transformation is a data flow transformation used to mark rows for insert, update, upsert, or delete in a sink, but it does not provide the incremental loading logic or watermark mechanism needed to identify new/updated rows from a source. Option C is wrong because a Schedule trigger only defines when a pipeline runs (e.g., every hour), but it does not implement the incremental copy logic itself; you still need the watermark pattern inside the pipeline. Option D is wrong because a Lookup activity alone can retrieve the watermark value but cannot copy data; it must be combined with a Copy activity to actually move the incremental rows.

Full explanation →

318

MCQmedium

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

A.Avro with Deflate compression

B.CSV with Gzip compression

C.Parquet with Snappy compression

D.ORC with Zlib compression

AnswerC

Parquet is columnar and Snappy provides fast compression/decompression, ideal for Synapse dedicated SQL pool.

Why this answer

Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Exam trap

Microsoft often tests the misconception that row-based formats like Avro or CSV are suitable for analytical workloads, but the trap here is that columnar formats (Parquet/ORC) are required for optimal read performance in Synapse dedicated SQL pools, and Snappy is preferred over Zlib for speed-critical pipelines.

How to eliminate wrong answers

Option A is wrong because Avro is a row-based format that does not support columnar pruning, leading to higher I/O for analytical queries on dedicated SQL pools. Option B is wrong because CSV with Gzip compression is row-oriented and not splittable at the row level, causing poor parallelism and slower read performance in Synapse. Option D is wrong because ORC with Zlib compression offers higher compression ratios but significantly slower decompression compared to Snappy, which can bottleneck read performance in Synapse's MPP engine.

Full explanation →

319

MCQmedium

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You suspect memory pressure and concurrency issues. What should you first review to diagnose the problem?

A.sys.dm_pdw_resource_waits

B.sys.dm_pdw_waits

C.sys.dm_pdw_query_stats_xe

D.sys.dm_pdw_exec_requests

AnswerD

Shows currently running queries with resource consumption

Why this answer

Sys.dm_pdw_exec_requests provides a list of all currently executing or recent requests in the dedicated SQL pool, including their status, resource class, and elapsed time. This is the best first step to identify queries that may be causing memory pressure or concurrency issues. Option A (sys.dm_pdw_resource_waits) shows only resource waits but does not show the actual requests causing the waits.

Option B (sys.dm_pdw_waits) shows wait information but lacks the direct request context needed for diagnosis. Option C (sys.dm_pdw_query_stats_xe) provides query execution statistics but not real-time active requests, so it is less suitable for immediate troubleshooting.

Full explanation →

320

Multi-Selecthard

Which TWO are required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Select 2 answers

A.A linked service to Azure SQL Database.

B.A dataset that references the stored procedure.

C.A self-hosted integration runtime.

D.A staging blob storage account.

E.A stored procedure activity in the pipeline.

AnswersA, E

The linked service provides connection details.

Why this answer

To run a stored procedure in Azure SQL Database from Azure Data Factory, only two components are required: a linked service to Azure SQL Database, which defines the connection and authentication, and a stored procedure activity in the pipeline that specifies the stored procedure name and parameters directly. A dataset is not required because the stored procedure activity can define the stored procedure reference inline. Options like a self-hosted integration runtime or staging storage are unnecessary for this task.

Exam trap

A common trap is thinking that a self-hosted integration runtime or staging storage is required. For Azure SQL Database (without a firewall blocking public access), the default auto-resolve IR works fine. Staging is only needed for large data movements, not for running a stored procedure.

Also, some may mistakenly believe a dataset is optional, but it is required for defining the stored procedure reference.

Full explanation →

321

MCQhard

You are designing a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow with Azure Integration Runtime (IR). The pipeline runs slowly and you notice that the IR's data movement is limited by the number of cores. Which configuration should you adjust to improve performance?

A.Enable staging for the copy activity within the data flow.

B.Increase the 'Data Flow Compute Type' and 'Core Count' in the Azure IR settings.

C.Use a Self-Hosted IR instead of Azure IR for data flows.

D.Increase the 'Number of partitions' in the source transformation.

AnswerB

These settings directly allocate more resources to mapping data flows.

Why this answer

The Azure Integration Runtime (IR) for mapping data flows uses a Spark cluster, and its performance is directly tied to the compute resources allocated. By increasing the 'Data Flow Compute Type' (e.g., from General Purpose to Memory Optimized) and the 'Core Count' (e.g., from 4 to 8 or 16 cores), you provide more parallel processing power, which directly addresses the core-limited data movement bottleneck.

Exam trap

The trap here is that candidates confuse the 'Number of partitions' setting (which controls data parallelism within the flow) with the Azure IR's core count (which controls the Spark cluster's overall compute capacity), leading them to pick D instead of B.

How to eliminate wrong answers

Option A is wrong because enabling staging for the copy activity is used to offload data movement to a staging blob store for PolyBase or COPY statement scenarios, not to increase the core count of the Azure IR for mapping data flows. Option C is wrong because Self-Hosted IR is designed for on-premises or private network data sources and does not improve the Spark-based compute performance of a mapping data flow; in fact, it adds network latency. Option D is wrong because increasing the 'Number of partitions' in the source transformation can improve parallelism within the data flow, but it does not change the underlying Azure IR's core count or compute type, which is the root cause of the core-limited bottleneck.

Full explanation →

322

MCQeasy

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

A.Azure HDInsight with Hive

B.Azure Analysis Services

C.Azure Data Factory with Mapping Data Flows

D.Azure Databricks with PySpark

AnswerC

Mapping Data Flows provide code-free data transformation at scale.

Why this answer

Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Full explanation →

323

MCQmedium

You need to ensure that an Azure Synapse Analytics serverless SQL pool automatically pauses after 2 hours of inactivity to save costs. Which feature should you configure?

A.Maintenance window

B.Data Exfiltration Prevention

C.Auto-pause feature

D.Workload Management

AnswerC

Auto-pause feature is available only for serverless SQL pools, not for dedicated SQL pools.

Why this answer

The Auto-pause feature in Azure Synapse Analytics serverless SQL pools automatically suspends the pool after a period of inactivity (default 1 hour, configurable up to 8 hours), reducing costs when not in use. Option C is correct because it directly addresses the requirement to pause after 2 hours of inactivity.

Full explanation →

324

MCQmedium

You are reviewing an Azure Data Factory JSON definition for a linked service. The linked service uses a service principal to connect to Azure Data Lake Storage Gen1. What is a security concern with this configuration?

A.The subscription ID and resource group are specified.

B.The linked service uses a service principal instead of a managed identity.

C.Using Azure Data Lake Storage Gen1 instead of Gen2.

D.The service principal key is stored as a SecureString but is visible in the JSON definition.

AnswerD

Storing secrets in linked service JSON is insecure; should use Azure Key Vault.

Why this answer

The service principal key is stored as a SecureString, but in the JSON definition it is visible as plain text, which is a security concern. Option A is incorrect because specifying the subscription ID and resource group is necessary for resource management and not a security issue. Option B is incorrect because using a service principal is a valid and secure authentication method when the key is properly stored.

Option C is incorrect because the choice between Data Lake Storage Gen1 and Gen2 is a performance or feature decision, not a security concern.

Full explanation →

325

MCQmedium

You are designing a data storage solution for a retail company that expects high volumes of small, time-series sensor data from thousands of IoT devices. The data must be stored cost-effectively and queried by time range with low latency. Which Azure data store should you recommend?

A.Azure Cosmos DB with analytical store

B.Azure SQL Database with columnstore indexes

C.Azure Blob Storage with Azure Data Lake Storage Gen2

D.Azure Data Explorer (ADX)

AnswerD

ADX is designed for high-speed ingestion and real-time analytics on time-series data with built-in optimization.

Why this answer

Azure Data Explorer (ADX) is optimized for high-volume, time-series data ingestion and low-latency queries over time ranges. It uses a columnar storage engine and automatic indexing, making it cost-effective for sensor data from thousands of IoT devices.

Exam trap

The trap here is that candidates often choose Azure Blob Storage with Data Lake Storage Gen2 because it is cheap for storage, but they overlook that it lacks native query capabilities for low-latency time-series queries, requiring additional compute layers.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB with analytical store is designed for globally distributed, multi-model data with operational and analytical workloads, not specifically for high-throughput time-series ingestion and time-range queries at low cost. Option B is wrong because Azure SQL Database with columnstore indexes is a relational database optimized for transactional workloads and analytical queries on structured data, but it cannot efficiently handle the high ingestion rates and time-series query patterns of thousands of IoT devices without significant cost and performance overhead. Option C is wrong because Azure Blob Storage with Azure Data Lake Storage Gen2 is a hierarchical file storage for big data analytics, not a query engine; it requires additional compute services like Azure Synapse or Spark to query time-series data, adding latency and complexity.

Full explanation →

326

MCQhard

Your Azure Data Factory pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Blob Storage. You notice that the pipeline is running slower than expected. You need to identify the bottleneck. Which Data Factory monitoring metric should you analyze first?

A.Source queue length

B.Pipeline duration

C.Activity run count

D.Data Integration Unit (DIU) consumption

AnswerD

High DIU consumption indicates the Copy activity is resource-constrained.

Why this answer

Data Integration Unit (DIU) consumption indicates whether the Copy activity is resource-bound and helps identify performance bottlenecks. Option A is wrong because source queue length reflects integration runtime queue depth, not a direct metric for Copy activity throughput. Option B is wrong because pipeline duration is a result of performance issues, not a metric to pinpoint the bottleneck first.

Option C is wrong because activity run count is unrelated to performance analysis.

Full explanation →

327

MCQmedium

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to ensure that all data at rest in the SQL pool is encrypted using a customer-managed key stored in Azure Key Vault. What should you configure?

A.Implement Always Encrypted with column encryption keys stored in Azure Key Vault.

B.Configure Dynamic Data Masking to obfuscate sensitive data.

C.Enable Azure Storage Service Encryption with a customer-managed key.

D.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.

AnswerD

TDE with customer-managed key provides encryption at rest for the entire database, meeting the requirement.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault provides the required encryption for data at rest in Azure Synapse Analytics dedicated SQL pool. Option A is wrong because Always Encrypted protects data in transit and at rest in application logic, not at rest in the database. Option B is wrong because Dynamic Data Masking does not encrypt data; it obfuscates it for users.

Option C is wrong because Azure Storage Service Encryption is for Azure Storage accounts, not for SQL pools.

Full explanation →

328

MCQmedium

You have a Synapse Analytics dedicated SQL pool. You need to load 100 GB of CSV data from Azure Data Lake Storage Gen2 into a fact table. The table has a hash-distributed column. Which pattern is most efficient for loading with minimal impact on concurrent queries?

A.Use PolyBase INSERT...SELECT with rowstore table

B.Use CREATE TABLE AS SELECT (CTAS) with the hash-distributed column

C.Use COPY INTO command with a round-robin distribution

D.Use Azure Data Factory Copy activity with staging enabled

AnswerB

Why this answer

CTAS with a hash-distributed column loads data directly into the target table with the same distribution scheme, avoiding data movement and minimizing resource contention. This pattern is optimized for bulk loading large datasets into a hash-distributed fact table, as it leverages the Synapse SQL pool's MPP architecture to parallelize the operation without blocking concurrent queries.

Exam trap

The trap here is that candidates often assume PolyBase or COPY INTO are always the fastest for bulk loading, but they overlook that CTAS with the correct distribution key avoids the costly data redistribution step required by other methods.

Why the other options are wrong

A

INSERT...SELECT logs each row and may cause concurrency issues; not as efficient as CTAS for large loads.

C

COPY INTO is efficient but round-robin distribution may cause data movement later; CTAS with hash distribution avoids extra steps.

D

Copy activity with staging uses PolyBase under the hood but adds overhead; CTAS is more direct.

Full explanation →

329

MCQmedium

Your team has deployed an Azure Stream Analytics job that reads from an Event Hubs input and writes to Azure Synapse Analytics. The job is falling behind, causing a growing backlog in Event Hubs. You have already scaled the Stream Analytics job to maximum streaming units. What should you do to improve throughput?

A.Increase the streaming units further

B.Configure a late arrival window to drop late events

C.Increase the throughput units of the Event Hubs namespace

D.Partition the input Event Hubs and the output Synapse table, and adjust the Stream Analytics query to use PARTITION BY

AnswerD

Partitioning allows Stream Analytics to process data in parallel, increasing throughput.

Why this answer

Partitioning the input Event Hubs and the output Synapse table, and using PARTITION BY in the query, increases parallelism and allows the Stream Analytics job to process more data simultaneously. Option A is incorrect because the job is already at maximum streaming units. Option B is incorrect because a late arrival window handles out-of-order events, not throughput issues.

Option C is incorrect because while increasing Event Hubs throughput units could help if Event Hubs is the bottleneck, the most likely bottleneck is the output sink (Azure Synapse Analytics), and partitioning the output is a more direct solution.

Full explanation →

330

MCQmedium

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You suspect that the workload is generating excessive data movement due to suboptimal distribution. Which dynamic management view (DMV) should you query to identify queries that are causing significant data movement?

A.sys.dm_pdw_node_status

B.sys.dm_pdw_exec_requests

C.sys.dm_pdw_errors

D.sys.dm_pdw_waits

AnswerB

This DMV shows the execution steps of requests, including data movement operations (shuffle moves, broadcast moves) that can degrade performance.

Why this answer

Sys.dm_pdw_exec_requests shows query steps including data movement operations. Option A is wrong because sys.dm_pdw_node_status shows node health. Option C is wrong because sys.dm_pdw_errors shows error details.

Option D is wrong because sys.dm_pdw_waits shows wait states.

Full explanation →

331

MCQeasy

Your company uses Azure Data Lake Storage Gen2 as the central data lake. You need to process batch data using serverless Spark jobs that can be scheduled daily. Which Azure service should you use?

A.Azure Batch with custom Spark containers.

B.Azure Synapse Analytics serverless Spark pool with pipelines.

C.Azure Databricks with a job cluster.

D.Azure Machine Learning with Spark compute.

AnswerB

Synapse provides serverless Spark pools with automatic scaling and built-in scheduling via pipelines.

Why this answer

Azure Synapse Analytics provides serverless Spark pools with built-in scheduling via pipelines, allowing you to run daily batch jobs without managing clusters. Option A (Azure Batch) is for custom compute workloads, not Spark jobs. Option C (Azure Databricks) requires a job cluster that is not serverless.

Option D (Azure Machine Learning) is designed for ML workflows, not general batch data processing.

Full explanation →

332

MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool that handles both high-priority real-time queries and low-priority batch jobs. You need to ensure that high-priority queries always get the resources they need, while batch jobs do not starve. What should you configure?

A.Enable result-set caching for the high-priority queries

B.Enable data compression on the tables used by batch jobs

C.Create workload groups for high-priority and low-priority queries, assigning appropriate importance and resource percentages

D.Create materialized views for the batch job queries

AnswerC

Workload groups allow you to control resource allocation and query importance.

Why this answer

Workload management with workload groups allows you to set importance and resource allocation. Option A is wrong because result-set caching does not prioritize queries. Option B is wrong because data compression reduces storage but does not affect prioritization.

Option D is wrong because materialized views improve performance but do not prioritize.

Full explanation →

333

Multi-Selecteasy

Which TWO of the following are required components to set up a data pipeline that uses Change Data Capture (CDC) to incrementally load data from SQL Server to Azure Synapse using Azure Data Factory?

Select 2 answers

A.CDC enabled on the source SQL Server database and tables

B.A staging Azure Blob Storage account

C.A Lookup activity to get the last watermark

D.A stored procedure in the source database to capture changes

E.A linked service to the Azure Synapse dedicated SQL pool

AnswersA, E

CDC must be enabled on the source to track changes.

Why this answer

Change Data Capture (CDC) must be enabled on the source SQL Server database and the specific tables you intend to track. Without CDC enabled, SQL Server will not generate the change tracking tables (e.g., cdc.<capture_instance>_CT) that Azure Data Factory’s CDC connector reads to identify inserts, updates, and deletes. This is a prerequisite for any incremental load using the native CDC mechanism in ADF.

Exam trap

The trap here is that candidates often confuse CDC-based incremental loading with watermark-based incremental loading, leading them to incorrectly select a Lookup activity (Option C) or a staging storage account (Option B) as required components.

Full explanation →

334

MCQhard

You are designing a data pipeline using Azure Synapse Pipelines. The pipeline ingests data from multiple sources, performs transformations using a notebook, and loads the results into a dedicated SQL pool. You need to ensure that if the notebook fails, the entire pipeline stops and sends an alert. What is the most efficient way to configure this?

A.Set the notebook activity's error path to a webhook activity that sends an alert, and then set a wildcard error path for the pipeline.

B.Set the notebook activity's retry count to 0, and configure an alert on the pipeline run failure.

C.Add a 'Fail' activity after the notebook activity and connect the notebook's failure output to it. Configure the Fail activity to send an alert.

D.No configuration needed; by default, a failed activity stops the entire pipeline.

AnswerC

Correct: The Fail activity terminates the pipeline with an error, and you can trigger alerts based on pipeline failure.

Why this answer

The most efficient: In Azure Synapse Pipelines (and Azure Data Factory), you can connect the failure output of the notebook activity to a 'Fail' activity. The Fail activity can be configured to send an alert (e.g., via webhook) and it stops the pipeline run immediately. Option A is incorrect because setting a wildcard error path would allow other activities to run; instead, you should explicitly route the failure to a Fail activity.

Option B is incorrect because setting retry to 0 only prevents retries, but does not automatically stop the pipeline or send an alert. Option D is incorrect because the default behavior of a failed activity is to continue pipeline execution along the default error path, not to stop entirely; you must define an explicit failure path.

Full explanation →

335

MCQmedium

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

A.Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.

B.Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.

C.Azure Databricks with structured streaming and a sliding window.

D.Azure Analysis Services to process streaming data directly from Event Hubs.

AnswerA

Stream Analytics provides built-in windowing functions and late-arrival handling, perfect for this scenario.

Why this answer

Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Exam trap

The trap here is that candidates often confuse tumbling windows (fixed, non-overlapping) with sliding windows (continuous, overlapping) or assume that any compute service (like Functions or Databricks) can easily replicate Stream Analytics' built-in windowing and late-arrival handling, ignoring the complexity of state management and exactly-once semantics.

How to eliminate wrong answers

Option B is wrong because Azure Functions triggered by Event Hubs do not provide built-in windowing or late-arrival policy support; you would have to manually implement stateful aggregation, which is complex and error-prone. Option C is wrong because Azure Databricks with structured streaming uses a sliding window, not a tumbling window, and does not offer a native late-arrival policy configuration as simple as Stream Analytics; it also introduces unnecessary overhead for this real-time dashboard scenario. Option D is wrong because Azure Analysis Services is an OLAP engine for analytical queries on pre-aggregated data, not a real-time stream processing service; it cannot directly process streaming data from Event Hubs.

Full explanation →

336

Multi-Selecthard

Which THREE factors should be considered when designing a partitioning strategy for a large fact table in Azure Synapse Analytics dedicated SQL pool?

Select 3 answers

A.Index type

B.Data compression

C.Distribution key

D.Partition column

E.Number of partitions

AnswersB, D, E

Partition size affects compression efficiency.

Why this answer

Data compression is a key factor in partitioning strategy because it directly impacts storage efficiency and query performance. In Azure Synapse Analytics dedicated SQL pool, columnstore compression is applied per partition, and choosing the right compression level (e.g., COLUMNSTORE vs. COLUMNSTORE_ARCHIVE) can significantly reduce I/O and improve scan speeds for large fact tables.

Exam trap

The trap here is that candidates confuse distribution key (which controls data placement across nodes) with partition column (which controls data organization within a node), leading them to incorrectly select distribution key as a partitioning factor.

Full explanation →

337

Multi-Selecteasy

You are designing a data processing solution in Azure Data Factory that uses mapping data flows. You need to perform type conversions on incoming data. Which two transformations can be used to change data types? (Choose two.)

Select 2 answers

A.Conditional Split

B.Derived Column

C.Assert

D.Sort

E.Select

AnswersB, E

Derived Column allows type conversion via expressions.

Why this answer

Options B and E are correct. Derived Column can change data types through expressions, and Select can cast types during column projection. Option C (Assert) is incorrect because it only validates and routes rows based on conditions; it does not convert data types.

Option A (Conditional Split) and Option D (Sort) are also incorrect.

Full explanation →

338

MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must support incremental loading of data from an Azure SQL Database to a dedicated SQL pool using PolyBase. Which approach should you use to minimize data movement and maximize performance?

A.Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.

B.Create external tables in the dedicated SQL pool that reference the source data, then use CREATE TABLE AS SELECT (CTAS) to load incrementally.

C.Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.

D.Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.

AnswerB

PolyBase external tables enable direct query of source data, and CTAS allows efficient incremental loading with minimal data movement.

Why this answer

Using external tables with PolyBase in Azure Synapse Analytics allows you to directly query the source Azure SQL Database without moving the data first. The CREATE TABLE AS SELECT (CTAS) statement then loads only the incremental data into the dedicated SQL pool, minimizing data movement by leveraging PolyBase's parallel streaming capability for maximum performance.

Exam trap

The trap here is that candidates often assume external tables are only for static data or Hadoop, but PolyBase in Synapse supports external tables against Azure SQL Database for efficient incremental loading, making options that introduce extra hops (like Data Factory or bcp) seem more familiar but less optimal.

How to eliminate wrong answers

Option A is wrong because the bcp utility exports data to a text file, which introduces an intermediate storage step and additional I/O overhead, increasing data movement and latency compared to direct PolyBase access. Option C is wrong because Azure Data Factory copy activity moves data through an intermediate staging area (e.g., Azure Blob Storage), which adds extra data transfer and storage costs, whereas PolyBase can read directly from the source without staging. Option D is wrong because Azure Databricks with the Spark connector requires moving data out of Azure SQL Database into a Spark cluster for processing, then writing back to the dedicated SQL pool, which increases data movement and complexity compared to the native PolyBase approach.

Full explanation →

339

MCQhard

An Azure Data Factory pipeline runs multiple times daily, loading data from an on-premises SQL Server to Azure Blob Storage. You notice that the pipeline sometimes fails due to transient network errors. You need to implement a retry policy with exponential backoff. Which configuration should you apply?

A.Set the pipeline's retry property to 3 and retry interval to 60 seconds.

B.Set the activity's retry property to 3 and enable exponential backoff.

C.Set the activity's retry property to 3 and retry secs to 60.

D.Set the trigger's retry policy to 3 with exponential backoff.

AnswerB

Incorrect because Azure Data Factory activity retry does not support enabling exponential backoff; the retry interval is fixed. The option describes a non-existent configuration.

Why this answer

Azure Data Factory activity retry now supports exponential backoff. You can enable it by setting the retry property and enabling exponential backoff. Option B correctly configures 3 retries with exponential backoff.

Option C only sets a fixed interval without exponential backoff. Options A and D are incorrect because retry is configured at the activity level, not pipeline or trigger level.

Exam trap

Candidates may assume that exponential backoff is not supported, but it is now available as a configuration option for activity retries in Azure Data Factory.

Full explanation →

340

Multi-Selecteasy

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Select 3 answers

A.Use a path pattern that includes {date} and {time} tokens.

B.Configure the event ordering policy to adjust late events.

C.Set the output serialization format to Avro or Parquet.

D.Set the compatibility level to 1.2 or higher.

E.Enable 'Write to blob storage partitioned by time' in the output settings.

AnswersA, C, E

Tokens in the path pattern create folder structure based on date and time.

Why this answer

Azure Stream Analytics supports custom path patterns for Blob Storage output, where {date} and {time} tokens automatically resolve to the processing date and hour (in UTC). This allows partitioning output files into a folder structure like 'YYYY/MM/DD/HH', enabling efficient querying by date and hour without post-processing.

Exam trap

The trap here is that candidates confuse event ordering policies or compatibility levels with output partitioning, but only the path pattern tokens and the 'Write to blob storage partitioned by time' toggle (which enables the {date}/{time} tokens) directly control folder structure.

Full explanation →

341

MCQhard

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

A.Row-level security (RLS)

B.Column-level security

C.Dynamic data masking

D.Microsoft Defender for Cloud

AnswerB

Column-level security restricts access to specific columns.

Why this answer

Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Exam trap

The trap here is that candidates often confuse Dynamic data masking with access control, but masking only hides data from the UI while still allowing underlying access, whereas column-level security actually prevents unauthorized users from reading the column data at all.

How to eliminate wrong answers

Option A is wrong because Row-level security (RLS) restricts access to rows based on user identity or context, not columns, so it cannot limit visibility of specific columns like credit card numbers. Option C is wrong because Dynamic data masking obfuscates data at query time but does not prevent authorized users from viewing the original data if they have direct access; it is not a permission-based access control. Option D is wrong because Microsoft Defender for Cloud is a security monitoring and threat protection service, not a data access control feature for restricting column visibility in Synapse Analytics.

Full explanation →

342

Drag & Dropmedium

Drag and drop the steps to configure Azure Databricks auto-scaling cluster for ETL workloads 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 workspace, then the cluster with auto-scaling settings, choose runtime, attach libraries, and set policies.

Full explanation →

343

MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

A.Round-robin distribution.

B.Partitioned table with a partition key.

C.Hash distribution on a column that is frequently used in joins and aggregations.

D.Replicated distribution.

AnswerC

Hash distribution collocates rows with the same key, reducing data movement.

Why this answer

Hash distribution on a column frequently used in joins and aggregations is the best choice for a fact table with billions of rows in a dedicated SQL pool. It distributes rows across distributions based on a hash of the distribution column, ensuring that rows with the same key value are co-located on the same distribution. This minimizes data movement during joins and aggregations, as the data required for these operations is already local to each distribution, significantly improving query performance.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning alone can optimize data movement across nodes, but partitioning operates within a distribution and does not affect how data is distributed across compute resources.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering data relationships, which leads to excessive data movement during joins and aggregations, degrading performance for large fact tables. Option B is wrong because partitioning is a data organization technique within a distribution, not a distribution strategy; it helps with data management and partition elimination but does not control how data is distributed across compute nodes, so it cannot minimize data movement across distributions. Option D is wrong because replicated distribution copies the entire table to each compute node, which is impractical for a fact table with billions of rows due to massive storage overhead and write performance penalties; it is intended for small dimension tables, not large fact tables.

Full explanation →

344

MCQmedium

Refer to the exhibit. A custom RBAC role is defined as shown. A user is assigned this role at the resource group scope. Which operation can the user perform?

A.Delete containers

B.Write blob data to containers

C.List containers in a storage account within DataRG

D.Read blob data from containers

AnswerC

The action permits reading container properties and listing containers.

Why this answer

The custom RBAC role includes the 'Microsoft.Storage/storageAccounts/blobServices/containers/read' action, which allows listing containers. Since the user is assigned this role at the resource group scope (DataRG), they can list containers in any storage account within that resource group. The role does not include any data plane actions (e.g., read/write/delete blob data) or container deletion permissions, so only the list operation is permitted.

Exam trap

The trap here is that candidates often confuse control plane container listing permissions with data plane blob read permissions, assuming that 'read' on containers implies access to blob content, whereas Azure RBAC strictly separates these scopes.

How to eliminate wrong answers

Option A is wrong because deleting containers requires the 'Microsoft.Storage/storageAccounts/blobServices/containers/delete' action, which is not included in the role. Option B is wrong because writing blob data requires the 'Microsoft.Storage/storageAccounts/blobServices/containers/write' action (or a data plane permission like 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write'), which is absent. Option D is wrong because reading blob data requires the 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read' action, which is not granted; the role only grants read access to container metadata (listing), not to blob content.

Full explanation →

345

MCQmedium

Refer to the exhibit. An ARM template deploys an Azure Synapse Analytics workspace. What is the purpose of the 'managedVirtualNetwork' property set to 'default'?

A.It configures the workspace to use a user-assigned managed identity

B.It disables public network access to the workspace

C.It creates the workspace in a private endpoint configuration

D.It enables the workspace to use a managed virtual network for network isolation

AnswerD

Managed VNet provides isolation.

Why this answer

Setting 'managedVirtualNetwork' to 'default' in an ARM template for Azure Synapse Analytics enables a managed virtual network that provides network isolation for the workspace. This allows the workspace to use private endpoints and managed private endpoints for secure data integration without exposing traffic to the public internet. It is a foundational setting for implementing a secure, network-isolated Synapse environment.

Exam trap

The trap here is that candidates confuse 'managedVirtualNetwork' with directly creating private endpoints or disabling public access, when in fact it is the prerequisite that enables the workspace to use a managed virtual network for network isolation, with private endpoints and public network access controls being separate configurations.

How to eliminate wrong answers

Option A is wrong because the 'managedVirtualNetwork' property controls network isolation, not identity configuration; user-assigned managed identities are configured via the 'identity' property in the ARM template. Option B is wrong because disabling public network access is a separate setting (e.g., 'publicNetworkAccess' property), not the purpose of 'managedVirtualNetwork'. Option C is wrong because setting 'managedVirtualNetwork' to 'default' does not directly create the workspace in a private endpoint configuration; it enables the managed virtual network, and private endpoints must be explicitly created within that network for specific resources.

Full explanation →

346

MCQhard

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

A.The Parquet files are not using the .parquet extension.

B.The derivedModel option is set to false, which disables schema inference.

C.The serverless SQL pool infers schema from the first file and expects all files to have the same schema.

D.The recursive option is causing the table to include files from subfolders that have different schemas.

AnswerC

Serverless SQL uses schema inference from the first file; subsequent files with different schemas cause errors.

Why this answer

Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised.

This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Exam trap

The trap here is that candidates assume serverless SQL can automatically handle heterogeneous schemas (like Spark does with mergeSchema), but in reality it requires all files to share the exact same schema as the first file it reads.

How to eliminate wrong answers

Option A is wrong because the .parquet extension is not required; serverless SQL can infer Parquet format from the file's binary header, not the file extension. Option B is wrong because the derivedModel option does not exist in serverless SQL table creation; schema inference is always enabled and cannot be disabled via such an option. Option D is wrong because the recursive option controls whether subfolders are scanned, but schema mismatch errors occur even without recursion if files in the same folder have different schemas; recursion is not the root cause.

Full explanation →

347

MCQeasy

You are designing a data processing pipeline using Azure Data Factory. The pipeline must ingest data from an HTTP endpoint that returns a JSON array. The data must be transformed by flattening nested arrays and then loaded into an Azure SQL Database table. The pipeline should be triggered daily. You need to choose the appropriate activities and transformations. The solution must be cost-effective and easy to maintain. Which combination of activities should you use?

A.Use a Lookup activity to read the JSON, then a ForEach activity to iterate and insert rows into SQL Database.

B.Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage, then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database.

C.Use a Data Flow activity directly from HTTP source with a Flatten transformation and sink to SQL Database.

D.Use two Copy activities: one to copy JSON to Blob Storage, and another to copy from Blob Storage to SQL Database without transformation.

AnswerB

This is the standard pattern: ingest, transform, load.

Why this answer

The correct approach is Option B: Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage (staging), then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database. This is cost-effective and maintainable because it separates ingestion and transformation, allows for schema drift, and uses serverless Data Flows. Option A is wrong because Lookup is for reading a single row/value, not for bulk data ingestion; ForEach with insert would be inefficient and costly.

Option C is wrong because Data Flows cannot directly read from HTTP sources; they require a dataset that is staged in a supported store like Blob Storage. Option D is wrong because it performs no transformation, so the nested JSON would not be flattened for loading into SQL.

Full explanation →

348

MCQmedium

You are reviewing an Azure Data Factory pipeline JSON that copies data from Azure Blob Storage to Azure SQL Database using a stored procedure. The pipeline fails with a 'Parameter supplied for object is not valid' error. What is the most likely cause?

A.The source type 'BlobSource' is not compatible with Azure Blob Storage.

B.The SQL table type 'dbo.InsertType' does not exist.

C.The stored procedure parameters are not mapped to source columns.

D.The dataset references are incorrect.

AnswerC

When using a stored procedure as a sink, source columns must be explicitly mapped to the stored procedure parameters. If this mapping is missing or incorrect, the 'Parameter supplied for object is not valid' error occurs.

Why this answer

The error 'Parameter supplied for object is not valid' in a copy activity using a stored procedure typically indicates that the stored procedure is expecting parameters that are not being provided correctly. The most common cause is that the source columns in the copy activity are not mapped to the stored procedure parameters. Option C is correct because the pipeline JSON likely lacks the necessary parameter mapping from source columns to stored procedure input parameters.

Option A is incorrect because BlobSource is valid for Azure Blob Storage. Option B is incorrect because the error is about parameter supply, not the existence of a table type. Option D is incorrect because the error relates to parameter mapping, not dataset references.

Full explanation →

349

MCQmedium

A company is ingesting streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time and stored in Azure Synapse Analytics for reporting. The solution must handle late-arriving data and ensure exactly-once semantics. Which Azure service should you use for stream processing?

A.Azure Data Factory with Event Hubs source

B.Azure Synapse Spark Structured Streaming

C.Azure Stream Analytics

D.Azure Event Hubs with Capture

AnswerC

Provides exactly-once delivery and can handle late arrivals.

Why this answer

(Azure Stream Analytics) is correct because it supports exactly-once semantics for output to Azure Synapse Analytics, handles late-arriving data via adjustable event ordering policies, and is purpose-built for real-time stream processing. Option A (Azure Data Factory) is incorrect as it is primarily a batch ETL service and not designed for near-real-time streaming. Option B (Azure Synapse Spark Structured Streaming) can process streaming data but does not provide exactly-once semantics to Synapse out of the box without additional configuration.

Option D (Azure Event Hubs with Capture) is incorrect because it only captures raw event data to storage and does not perform stream processing or guarantee exactly-once delivery to Synapse.

Full explanation →

350

MCQeasy

You are designing a data pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure SQL Database. The data contains personally identifiable information (PII). What should you use to protect the data during transit?

A.Azure Information Protection

B.Encryption over HTTPS/TLS

C.Azure Disk Encryption

D.Azure Storage Service Encryption

AnswerB

Azure Data Factory uses TLS to encrypt data in transit between endpoints.

Why this answer

Azure Data Factory always encrypts data in transit using TLS. Option A is wrong because Azure Information Protection is for labeling, not transit encryption. Option C is wrong because Azure Disk Encryption is for at-rest encryption of disks.

Option D is wrong because Azure Storage Service Encryption is for at-rest encryption.

Full explanation →

351

MCQmedium

A company uses Azure Synapse Analytics with dedicated SQL pools. They notice that query performance degrades significantly during peak hours. They have already scaled up the Data Warehouse Units (DWU) to the maximum. Which action should they take next to improve performance?

A.Enable result-set caching.

B.Rebuild all clustered columnstore indexes.

C.Increase the number of concurrency slots.

D.Move the data to Azure Data Lake Storage Gen2.

AnswerA

Result-set caching stores query results in the SSD cache, reducing compute resource usage and improving performance for repeated queries.

Why this answer

When a dedicated SQL pool is already at maximum DWU, further scaling is not possible. Enabling result-set caching stores query results in the SSD-based cache of the SQL pool, allowing repeated queries to be served directly from cache without re-scanning data or re-computing aggregations. This reduces I/O and CPU pressure during peak hours, improving performance for recurring queries without requiring additional compute resources.

Exam trap

The trap here is that candidates often confuse result-set caching with materialized views or index maintenance, assuming that only index rebuilds or scaling can fix performance, but result-set caching is a lightweight, no-cost configuration change that directly addresses repeated query patterns during peak load.

How to eliminate wrong answers

Option B is wrong because rebuilding clustered columnstore indexes is a maintenance task that can improve compression and scan performance, but it does not address the root cause of peak-hour degradation when the pool is already at maximum DWU; it also consumes significant resources during rebuild. Option C is wrong because concurrency slots are a resource governance mechanism that limits the number of concurrent queries, not a performance-tuning feature; increasing concurrency slots would actually reduce the resources available per query, potentially worsening performance. Option D is wrong because moving data to Azure Data Lake Storage Gen2 changes the storage layer but does not directly improve query performance in a dedicated SQL pool; the pool still reads data through its compute nodes, and the bottleneck is compute, not storage location.

Full explanation →

352

MCQmedium

Your team uses Azure Data Factory to orchestrate data movement. You need to monitor pipeline runs and set up alerts when a pipeline fails more than three times in an hour. What is the most efficient approach?

A.Create an alert rule in Azure Data Factory based on the 'Failed pipeline runs' metric.

B.Configure diagnostic settings to send logs to Log Analytics and create a log alert.

C.Use a Logic App to periodically check the pipeline run status and send notifications.

D.Create an Azure Monitor metric alert for the 'Failed pipeline runs' metric with a threshold of 3 in 1 hour.

AnswerD

Azure Monitor metric alerts are efficient for monitoring pipeline failures.

Why this answer

Azure Monitor alerts can be configured based on metrics like Failed pipeline runs with a threshold of 3 in 1 hour. Option A is wrong because Alert rules in Data Factory are limited. Option B is wrong because diagnostic settings send logs to Log Analytics, but you would need to create a log alert, which is less efficient than a metric alert.

Option C is wrong because a logic app is not the most efficient for simple threshold alerts.

Full explanation →

353

MCQhard

You are reviewing a mapping data flow in Azure Data Factory that reads a CSV file from ADLS Gen2 and writes to an Azure Synapse Analytics dedicated SQL pool. The data flow includes a Derived Column transformation with the expression: `column1 == "Error" ? toString(column1) : column1`. The pipeline fails with an error indicating that the sink table could not be created. What is the most likely cause?

A.The source file does not have a header row.

B.The Derived Column expression has a syntax error.

C.Using allowCopyCommand with autoCreate is not supported.

D.The sink dataset is configured for JSON format.

AnswerD

If the sink dataset is configured for JSON format, but the sink is an Azure Synapse dedicated SQL pool table, there will be a format mismatch. The data flow expects a table format for the sink, and attempting to create a table with JSON format settings could cause the error.

Why this answer

In a mapping data flow, the sink dataset must match the destination format. For an Azure Synapse dedicated SQL pool, the dataset should be of type 'Azure Synapse Analytics' (table), not JSON. If the dataset is set to JSON, the pipeline will fail when trying to create the sink table because the format is incompatible.

Option C is incorrect because allowCopyCommand is a feature of the Copy activity, not mapping data flows.

Full explanation →

354

Multi-Selectmedium

You are using Azure Data Factory to ingest data from a REST API into Azure Synapse Analytics. The API has a rate limit of 100 requests per minute. You need to ensure that the pipeline respects the rate limit and retries on failure. Which two settings should you configure in the copy activity? (Choose two.)

Select 2 answers

A.Enable 'Enable staging' to use a staging blob.

B.Configure the 'Batch size' to 100.

C.Set the 'Throttle' property to limit the number of concurrent connections.

D.Set the 'Retry' property to a value greater than 0.

E.Increase the 'Timeout' value to 10 minutes.

AnswersC, D

Throttling concurrent connections helps stay within the rate limit.

Why this answer

Options C and D are correct. Setting the 'Throttle' property (or 'maxConcurrentConnections') limits the number of concurrent connections to the REST API, which helps respect the rate limit of 100 requests per minute. Setting the 'Retry' property to a value greater than 0 enables the activity to retry on transient failures.

Option A is wrong because 'Enable staging' is used for large data transfers or to enable PolyBase, not for rate limiting. Option B is wrong because 'Batch size' controls the number of rows per batch for bulk operations, not the request rate. Option E is wrong because 'Timeout' specifies the maximum duration of the activity, not retry behavior.

Full explanation →

355

Multi-Selecteasy

You are monitoring an Azure Stream Analytics job that processes streaming data from Event Hubs to Azure Synapse Analytics. Which TWO Azure Monitor metrics should you set alerts on to detect data loss or processing delays?

Select 2 answers

A.InputEvents and OutputEvents metrics

B.Duration metric

C.Data read and data written metrics

D.Pipeline run count metric

E.Backlogged input events metric

AnswersA, E

InputEvents and OutputEvents (Option A) are correct. By comparing these two metrics, you can see if every event entering the Stream Analytics job is being output, indicating potential data loss if they diverge.

Why this answer

The correct metrics for detecting data loss or processing delays in an Azure Stream Analytics job are InputEvents and OutputEvents (Option A). By comparing these, you can determine if every event entering the job is being output; a mismatch indicates potential data loss. Backlogged input events (Option E) directly measures the number of events waiting to be processed, and a high or growing value signals processing delays.

Option B (Duration) is not a direct indicator of data loss or processing delays in Stream Analytics. Option C (Data read/written) is not a metric for Stream Analytics. Option D (Pipeline run count) is not applicable to Stream Analytics streaming jobs.

Full explanation →

356

MCQhard

You are monitoring an Azure Synapse Pipeline that uses a Mapping Data Flow. The data flow processes 2 GB of data from a CSV source and writes to a Delta sink. The pipeline fails with a 'DataFlowException: Operation aborted' error after running for 45 minutes. The cluster is configured with 8 cores. What is the most likely cause?

A.The cluster size is too small for the data volume.

B.The CSV source contains malformed rows that cause parsing errors.

C.The data flow cluster's time-to-live (TTL) is set to 45 minutes and the job exceeded it.

D.The data flow is using the Spark cluster's default timeout setting.

AnswerC

The default TTL for data flow clusters is 60 minutes, but if custom set to 45 minutes, the cluster may be terminated during long-running jobs.

Why this answer

The error 'Operation aborted' after exactly 45 minutes aligns with the default time-to-live (TTL) setting for Azure Synapse Mapping Data Flow clusters. When the TTL expires, the cluster is terminated, and any running job is aborted. The 8-core cluster and 2 GB data volume are not inherently problematic for a 45-minute window, but the TTL default of 45 minutes causes the abort if the job runs longer than that.

Exam trap

The trap here is that candidates confuse the TTL (a Synapse cluster lifecycle setting) with a Spark job timeout or a data volume issue, leading them to incorrectly select cluster size or malformed data as the cause.

How to eliminate wrong answers

Option A is wrong because 8 cores can process 2 GB of data within 45 minutes under normal conditions; the error is not due to insufficient cluster size but rather a timeout. Option B is wrong because malformed rows would cause a parsing error (e.g., 'MalformedRecordException'), not a generic 'Operation aborted' error after a fixed duration. Option D is wrong because the Spark cluster's default timeout is not a configurable setting that causes this specific error; the TTL is a Synapse-specific cluster lifecycle setting, not a Spark-level timeout.

Full explanation →

357

MCQmedium

You are designing a data processing pipeline in Azure Data Factory that ingests data from an on-premises SQL Server database to Azure Data Lake Storage Gen2. The data volume is large (500 GB). The network connection between on-premises and Azure is limited to 100 Mbps. You need to minimize the time to transfer the initial full load while ensuring data integrity. Which approach should you recommend?

A.Use Azure Data Factory copy activity with parallel connections

B.Use Azure ExpressRoute to increase bandwidth

C.Compress the data using GZip and use copy activity

D.Use Azure Data Box to copy the data offline

AnswerD

Data Box transfers data physically, bypassing network limitations.

Why this answer

Azure Data Box physically ships the data, bypassing network bandwidth limitations for large initial loads. Option A is wrong because it would take over 11 hours even at full bandwidth, and network may not be stable. Option B is wrong because it compresses but still uses network.

Option C is wrong because VPN adds overhead.

Full explanation →

358

Multi-Selectmedium

Which TWO actions should you take to secure access to an Azure Data Lake Storage Gen2 account using Microsoft Entra ID?

Select 2 answers

A.Generate a shared access signature (SAS) token with limited permissions.

B.Assign Azure RBAC roles such as Storage Blob Data Contributor to users or groups.

C.Configure a storage firewall to allow only specific IP addresses.

D.Use storage account access keys for authentication.

E.Enable hierarchical namespace on the storage account.

AnswersB, E

RBAC provides role-based access control integrated with Entra ID.

Why this answer

Correct options are B and E. Option B: Assigning Azure RBAC roles such as Storage Blob Data Contributor uses Microsoft Entra ID to grant coarse-grained access to the storage account. Option E: Enabling hierarchical namespace is necessary to use ACLs (access control lists) for fine-grained permissions, which can be integrated with Entra ID.

Option A is wrong because SAS tokens bypass Entra ID and provide access via shared key. Option C is wrong because firewall rules restrict network access, not identity. Option D is wrong because storage account access keys bypass Entra ID entirely.

Full explanation →

359

Multi-Selecthard

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 used percentage

B.DWU (Data Warehouse Unit) usage percentage

C.Active queries count

D.Buffer cache hit ratio

E.Memory grant waiters count

AnswersB, C, E

DWU usage percentage indicates if the pool is under- or over-provisioned, helping to optimize performance by adjusting resources.

Why this answer

Options B, C, and E are correct. Option B: DWU usage percentage indicates whether the pool is under- or over-provisioned, helping optimize performance. Option C: Active queries count monitors concurrency and workload.

Option E: Memory grant waiters count shows queries waiting for memory, which can indicate memory pressure and affect query performance. Option A: Storage used percentage is about capacity, not performance. Option D: Buffer cache hit ratio is more relevant for SQL Server, not directly for Synapse dedicated SQL pool performance optimization.

Full explanation →

360

MCQeasy

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

A.FTP

B.Azure Blob Storage

C.HTTP

D.Rest service

AnswerA

FTP linked service supports username and password authentication.

Why this answer

Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Exam trap

The trap here is that candidates often confuse the FTP connector with the HTTP or REST connector because they think all file transfers can be handled by generic web protocols, but FTP has its own distinct authentication and command set that requires a dedicated connector.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it is a destination or source for Azure's blob storage service, not a connector for external FTP servers; it cannot authenticate against an FTP server. Option C (HTTP) is wrong because the HTTP connector uses HTTP/HTTPS protocols and does not support FTP-specific authentication or directory listing commands. Option D (Rest service) is wrong because REST connectors are designed for RESTful APIs using JSON/XML payloads, not for FTP protocol operations like LIST or RETR.

Full explanation →

361

MCQeasy

Your company uses Azure Databricks for data processing. You need to ensure that spark jobs cannot access certain storage accounts. What is the most secure approach?

A.Store storage account keys in Azure Key Vault and retrieve them in notebooks.

B.Use shared access keys and restrict their usage.

C.Use Azure RBAC to grant specific storage account permissions to the Azure Databricks managed identity.

D.Disable public network access on storage accounts.

AnswerC

RBAC provides fine-grained access control using managed identities.

Why this answer

The most secure approach is to use Azure RBAC to grant specific storage account permissions to the Azure Databricks managed identity. This follows the principle of least privilege by granting only the required permissions to the identity used by the spark jobs. Option A is less secure because storing keys in Key Vault still requires managing secrets and the keys provide broad access.

Option B (shared access keys) also provides broad access and is not recommended. Option D (disabling public network access) controls network-level access but does not prevent access from authorized networks or services that are allowed; it does not specifically restrict spark job access within Databricks. Therefore, option C is the most secure and best practice.

Full explanation →

362

MCQeasy

Your Azure Data Factory pipeline is failing with the error: 'Operation on target Copy data1 failed: The remote server returned an error: (403) Forbidden.' The source is Azure Blob Storage and the sink is Azure SQL Database. You have verified the SQL Database firewall rules allow Azure services. What is the most likely cause?

A.The SQL Database firewall is blocking the Data Factory IP

B.The storage account is behind a private endpoint

C.The Data Factory managed identity lacks Storage Blob Data Contributor role on the storage account

D.The SQL Database is throttling the write operations

AnswerC

403 Forbidden indicates authentication/authorization failure

Why this answer

A 403 Forbidden error when reading from Azure Blob Storage typically indicates that the Data Factory does not have permission to access the storage. The managed identity used by Data Factory must have the Storage Blob Data Contributor role (or equivalent) on the storage account. Option A is incorrect because the error is 403, not a connectivity issue.

Option B is incorrect because a private endpoint alone does not cause a 403; it would affect network connectivity. Option D is incorrect because throttling from Azure SQL Database would produce a different error (e.g., 429 Too Many Requests).

Full explanation →

363

MCQmedium

A company uses Azure Synapse Analytics with dedicated SQL pools. They need to allow a data scientist to read all tables in the 'sales' schema but prevent access to columns containing personally identifiable information (PII). Which feature should be used?

A.Dynamic data masking

B.Row-level security

C.Column-level security

D.Azure Active Directory authentication

AnswerC

Column-level security restricts access to specific columns based on user or role.

Why this answer

Column-level security (C) is the correct choice because it allows you to restrict access to specific columns in a table, such as PII columns, while granting read access to all other columns in the 'sales' schema. This is achieved by defining a GRANT SELECT statement on the table with a column list, or by using a security policy with a filter predicate that blocks access to sensitive columns. Unlike Dynamic Data Masking, which obfuscates data at query time but does not prevent the user from seeing the masked values in certain scenarios, Column-level security actually denies access to the specified columns entirely.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with column-level security, assuming that masking PII is sufficient, but the exam tests the distinction that masking does not prevent data access—it only obfuscates the output, whereas column-level security actually denies read permission on the column.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking (DDM) obfuscates PII data at query time but does not prevent the user from reading the underlying data; a user with sufficient privileges can still see the original values by using techniques like casting or applying functions. Option B is wrong because Row-level security (RLS) restricts access to rows based on a predicate, not columns; it cannot hide specific columns within a row. Option D is wrong because Azure Active Directory authentication controls who can connect to the SQL pool but does not provide granular column-level access control within tables.

Full explanation →

364

MCQmedium

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

A.Set the 'Staging' option to 'Use staging'

B.Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'

C.Enable staging for the sink and use PolyBase

D.Set the 'Partition option' to 'Round robin' on the source

AnswerB

More compute resources speed up data flow execution.

Why this answer

Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Exam trap

The trap here is that candidates confuse Mapping Data Flow performance tuning with Copy Activity optimizations, such as PolyBase or staging, which are irrelevant to Spark-based data flows.

How to eliminate wrong answers

Option A is wrong because setting 'Staging' to 'Use staging' in a Mapping Data Flow is not a valid configuration; staging is used for copy activities, not for data flows. Option C is wrong because enabling staging for the sink and using PolyBase is a performance optimization for Copy Activity, not for Mapping Data Flow, which uses Spark-native connectors. Option D is wrong because setting the 'Partition option' to 'Round robin' on the source distributes data evenly but does not address the root cause of slow performance, which is insufficient compute resources for the Spark cluster.

Full explanation →

365

MCQhard

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options: Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase. Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase. Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse. Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source. Which option should you choose?

A.Option C

B.Option A

C.Option D

D.Option B

AnswerD

Mapping data flow supports schema drift and uses staging for PolyBase.

Why this answer

It handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency.

Option D requires additional log-shipping, increasing complexity and latency.

Full explanation →

366

MCQhard

You are designing a data processing solution for a retail company. The solution must ingest streaming sales data from point-of-sale (POS) systems and batch uploads from stores that are offline. The total data volume is 5 TB daily. The solution must allow real-time dashboards and periodic batch processing. Which combination of services and ingestion patterns is most cost-effective and scalable?

A.Use Azure IoT Hub for POS streaming and Azure Blob Storage for offline store uploads, then process with Stream Analytics and Data Factory

B.Use Azure Event Hubs with Kafka protocol for all incoming data. Use Stream Analytics for real-time dashboards and Event Hubs Capture to land data in ADLS for batch processing

C.Use Azure Data Lake Storage for all data, then use Azure Databricks structured streaming for real-time and batch

D.Use Azure Stream Analytics directly on POS data and store offline uploads in Blob Storage, then batch process with U-SQL

AnswerB

Why this answer

Azure Event Hubs with Kafka protocol provides a unified ingestion endpoint for both streaming POS data and batch offline uploads, eliminating the need for separate services. Stream Analytics enables real-time dashboards, while Event Hubs Capture automatically lands data into Azure Data Lake Storage for cost-effective batch processing, making this the most scalable and cost-effective solution for 5 TB daily.

Exam trap

The trap here is that candidates often assume IoT Hub is required for streaming data, but Event Hubs with Kafka protocol is more cost-effective and scalable for high-volume POS data, and they overlook Event Hubs Capture as a built-in mechanism for batch landing.

Why the other options are wrong

A

Two separate ingestion services increase management overhead and cost; IoT Hub is designed for device-to-cloud, not necessarily POS systems.

C

ADLS is storage, not an ingestion service; Databricks structured streaming can read from ADLS but not directly ingest streaming data without a queue.

D

Stream Analytics cannot directly ingest from POS systems without a messaging layer; offline store uploads still need ingestion.

Full explanation →

367

MCQmedium

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

A.Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

B.Use OPENROWSET with the DATA_SOURCE parameter.

C.Create views that filter on partition columns.

D.Increase the number of files per partition.

AnswerC

Allows partition elimination when querying.

Why this answer

Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Exam trap

The trap here is that candidates often assume that simply using external tables or OPENROWSET automatically provides partition pruning, but in serverless SQL pools, partition elimination only occurs when the query explicitly references the partition columns in the WHERE clause, typically through a view or inline filter.

How to eliminate wrong answers

Option A is wrong because CETAS creates external tables that store query results as new files, but it does not inherently optimize query performance or reduce data scanned for existing partitioned Parquet files; it is a data movement operation, not a query optimization technique. Option B is wrong because OPENROWSET with the DATA_SOURCE parameter allows querying files directly, but without explicit partition column filters in the WHERE clause, the serverless pool cannot perform partition elimination and will scan all files. Option D is wrong because increasing the number of files per partition increases metadata overhead and can degrade query performance due to more file open/read operations, and it does not reduce the amount of data scanned.

Full explanation →

368

MCQhard

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

A.Store all data in Azure SQL Database with partitioning and drop older partitions

B.Use Azure Data Lake Storage Gen2 with a single storage tier

C.Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

D.Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers

AnswerD

Lifecycle management automates tier transitions, minimizing cost while retaining data.

Why this answer

Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Exam trap

The trap here is that candidates may choose Option C thinking TTL in Cosmos DB can handle retention, but TTL deletes data automatically, which violates regulatory retention requirements, not just cost optimization.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with partitioning and dropping older partitions permanently deletes data, violating the 10-year regulatory retention requirement. Option B is wrong because Azure Data Lake Storage Gen2 with a single storage tier (e.g., Hot) does not provide automatic cost optimization for rarely accessed data over 10 years, leading to higher costs. Option C is wrong because Azure Cosmos DB with TTL automatically deletes expired data, which cannot be used for long-term retention, and Azure Blob Storage for backups does not replace a tiering strategy for the primary data store.

Full explanation →

369

Multi-Selectmedium

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Select 2 answers

A.Wrap the data flow in a Try-Catch activity in the pipeline.

B.Set the data flow's error handling to 'Abort on error' to stop processing on first failure.

C.Enable schema drift on the source to automatically handle data type mismatches.

D.Configure the sink transformation to allow errors and log error rows to a separate file.

E.Use a Conditional Split transformation to separate rows that cause errors based on a condition.

AnswersD, E

Sink can be configured to continue on error and write error rows to a file.

Why this answer

Configuring the sink transformation to allow errors and log error rows to a separate file enables graceful error handling in Mapping Data Flows. This approach captures rows that fail during transformation (e.g., type conversion errors) and writes them to a designated error output, allowing the pipeline to continue processing valid rows. Option E is correct because a Conditional Split transformation can proactively identify rows that are likely to cause errors based on a condition (e.g., checking for null or invalid data types) and route them to a separate path for logging or remediation, preventing them from reaching the sink and causing failures.

Exam trap

The trap here is that candidates often confuse pipeline-level error handling (like Try-Catch) with data flow-level error handling, or they assume schema drift can fix data type mismatches, when in fact it only handles structural changes at the source.

Full explanation →

370

MCQhard

You are a data engineer working for a logistics company. You have an existing Azure Data Factory pipeline that ingests data from a REST API to Azure Data Lake Storage Gen2. The API has rate limiting that can cause failures. You need to implement a solution that can handle rate limiting by retrying with exponential backoff. The pipeline should also log the number of retries for each API call. What should you do?

A.Configure the Copy activity with retry policy using exponential backoff by setting the retry count and retry interval. Enable diagnostic logs to capture retry details.

B.Use a Web activity with a Until loop to implement custom retry logic.

C.Use an Azure Function as a custom activity in Azure Data Factory to implement retry logic with exponential backoff.

D.Use Azure Logic Apps to call the API and then copy the response to Azure Data Lake Storage Gen2.

AnswerB

Correct. Using a Web activity with an Until loop allows you to implement custom retry logic with exponential backoff, and you can log retry counts.

Why this answer

Azure Data Factory Copy activity does not support exponential backoff; the retry interval is fixed. Using a Web activity inside an Until loop allows you to implement custom retry logic with exponential backoff by adjusting the wait time dynamically. This also enables logging the number of retries.

Option A is incorrect because the Copy activity's retry uses a fixed interval, not exponential backoff. Option C is more complex than necessary. Option D introduces an additional service without leveraging ADF's orchestration capabilities.

Full explanation →

371

MCQmedium

Refer to the exhibit. You are querying the sys.external_tables view in an Azure Synapse Analytics serverless SQL pool. The query returns no rows, but you believe that external tables have been created. What is the most likely reason?

A.The external tables are using PolyBase, which is not supported in serverless SQL pool.

B.Serverless SQL pool does not support external tables; you must use a dedicated SQL pool.

C.The external tables were created using OPENROWSET, not CREATE EXTERNAL TABLE, so they do not appear in sys.external_tables.

D.The user does not have permission to view the sys.external_tables view.

AnswerC

OPENROWSET queries do not create external table metadata; they are ad-hoc queries.

Why this answer

The user believes external tables have been created, but if they were created using CREATE EXTERNAL TABLE, they would appear in sys.external_tables. Since no rows are returned, it indicates that the external tables were not created via CREATE EXTERNAL TABLE; instead, the user likely used OPENROWSET to query data directly, which does not create a catalog entry. Therefore, the correct answer is C.

Exam trap

The trap here is that candidates may assume any external data access creates a catalog entry, but the exam tests the specific difference between DDL-based external tables and ad-hoc OPENROWSET queries in serverless SQL pool.

How to eliminate wrong answers

Option A is wrong because PolyBase is fully supported in serverless SQL pool for reading external data; it is not unsupported. Option B is wrong because serverless SQL pool does support external tables via CREATE EXTERNAL TABLE, and they are visible in sys.external_tables. Option D is wrong because if the user lacked permission to view sys.external_tables, the query would typically return an error or no rows, but the question states the user knows external tables exist, making a permission issue less likely than the metadata not being populated due to using OPENROWSET.

Full explanation →

372

MCQmedium

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

A.The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version

B.The resource path is malformed; it should include the blob path

C.The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'

D.The principal is a managed identity, but the policy requires a user-assigned identity

AnswerA

The condition requires API version 2019-12-12, which may not be used.

Why this answer

The policy condition requires the request to include the API version '2021-06-08' via the `acs:RequestVersion` condition key. Managed identity requests to Azure Data Lake Storage Gen2 use a default API version that may not match this specific version, causing the deny. The condition explicitly checks the request's API version, and if it does not match, access is denied regardless of other permissions.

Exam trap

The trap here is that candidates often overlook condition keys in Azure RBAC policies and focus only on the action or scope, assuming the deny is due to an incorrect role assignment or resource path, rather than a version-matching condition that blocks the request.

How to eliminate wrong answers

Option B is wrong because the resource path in the policy is correctly scoped to the storage account and container level; Azure RBAC policies for containers do not require the blob path in the resource path, as the action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is for listing containers, not reading blobs. Option C is wrong because the action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is correct for reading container properties or listing blobs within a container; the action for reading blob data itself is 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read', but the question states the managed identity needs to read from the 'data' container, which could include listing blobs, and the policy's action is not the cause of the deny. Option D is wrong because Azure RBAC policies do not distinguish between system-assigned and user-assigned managed identities; both are treated as service principals and can be assigned roles without any policy restriction on identity type.

Full explanation →

373

MCQmedium

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

A.Use an auto-scaling cluster with spot instances.

B.Use a fixed-size cluster with premium tier.

C.Use a Photon-accelerated cluster with premium tier.

D.Use an interactive cluster with a large number of workers.

AnswerA

Auto-scaling and spot instances provide cost-effectiveness and scalability.

Why this answer

Auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Exam trap

The trap here is that candidates often assume premium tier or Photon acceleration automatically improves cost-effectiveness, but these features address performance or governance, not the core requirement of scaling with variable workloads and minimizing cost via spot pricing.

How to eliminate wrong answers

Option B is wrong because a fixed-size cluster cannot scale to handle variable workloads, leading to either over-provisioning (higher costs) or under-provisioning (performance degradation). Option C is wrong because Photon-accelerated clusters are optimized for high-performance SQL and DataFrame workloads, but they do not inherently address cost-effectiveness for variable workloads; the premium tier adds features like role-based access control but does not enable scaling or spot pricing. Option D is wrong because an interactive cluster with a large number of workers is designed for ad-hoc analysis and collaboration, not for cost-effective batch processing; it lacks auto-scaling and spot instance support, leading to higher costs during idle periods.

Full explanation →

374

MCQeasy

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

A.Azure SQL Database

B.Azure Event Hubs

C.Azure Data Lake Storage Gen2

D.Azure Blob Storage

AnswerB

Optimized for high-throughput streaming ingestion.

Why this answer

Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage or Data Lake Storage as ingestion endpoints because they are cost-effective for storage, but they lack the real-time streaming capabilities and event-ordering guarantees that Event Hubs provides for immediate querying.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database designed for OLTP workloads, not for high-volume streaming ingestion; it lacks native support for event streaming protocols like AMQP or Kafka and would create bottlenecks and high costs for real-time IoT data. Option C is wrong because Azure Data Lake Storage Gen2 is a hierarchical file storage optimized for big data analytics and batch processing, not a real-time ingestion endpoint; it cannot natively accept streaming events or provide sub-second querying without an intermediary ingestion service. Option D is wrong because Azure Blob Storage is an object storage service for unstructured data, not designed for real-time event ingestion; it does not support streaming protocols or provide the low-latency, ordered event delivery required for immediate querying.

Full explanation →

375

MCQmedium

You are designing a data pipeline in Azure Data Factory (ADF) that copies data from an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The pipeline must run daily and handle incremental loads efficiently. Which sink dataset type and copy method should you use?

A.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use the Copy activity with PolyBase enabled.

B.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and enable the built-in Upsert option.

C.Use Azure Blob Storage as the sink dataset, then use PolyBase to load into the dedicated SQL pool.

D.Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use Stored Procedure with staging table and PolyBase.

AnswerD

This combination enables high-throughput ingestion and supports incremental loading via merge logic in the stored procedure.

Why this answer

It uses a staging table and PolyBase to efficiently load incremental data into Azure Synapse Analytics dedicated SQL pool. PolyBase provides high-throughput parallel loading, and the stored procedure handles the merge logic (upsert) to manage incremental changes. This approach is recommended for large-scale, daily incremental loads to Synapse.

Exam trap

The trap here is that candidates assume the built-in Upsert option works for all Azure SQL targets, but it is not supported for Azure Synapse Analytics dedicated SQL pool, requiring a custom staging-and-merge pattern instead.

How to eliminate wrong answers

Option A is wrong because the Copy activity with PolyBase enabled does not natively support incremental upsert logic; it only supports bulk insert or append, not merge operations. Option B is wrong because the built-in Upsert option is not available for Azure Synapse Analytics dedicated SQL pool as a sink in ADF Copy activity; it is only supported for Azure SQL Database and SQL Server. Option C is wrong because using Azure Blob Storage as an intermediate sink adds unnecessary complexity and latency; PolyBase can load directly from ADF into Synapse without an intermediate Blob Storage hop.

Full explanation →

Page 5 of 11

All pages

1 2 3 4 5 6 7 8 9 10 11

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

Secure, monitor, and optimize data storage and data processing Design and implement data storage Develop data processing

See all domains with question counts →