Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 451–525

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

Take a mock examExam hub
Page 6

Page 7 of 11

Page 8
451
MCQeasy

You need to ensure that sensitive data stored in Azure SQL Database is encrypted at rest. Which feature should you enable?

A.Always Encrypted
B.Azure Information Protection
C.Dynamic Data Masking
D.Transparent Data Encryption (TDE)
AnswerD

TDE performs real-time encryption and decryption of the database, backups, and transaction log files at rest.

Why this answer

Transparent Data Encryption (TDE) is correct because it encrypts the entire database at rest, including backups and log files. Option A (Always Encrypted) is incorrect because it encrypts data in use and in transit (client-side encryption), not at rest. Option B (Azure Information Protection) is incorrect because it is a classification and labeling service, not an encryption mechanism.

Option C (Dynamic Data Masking) is incorrect because it masks data in query results to unauthorized users, but does not encrypt the underlying data.

Full explanation →
452
MCQhard

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to ensure that only the 'data-scientists' group can read files in the 'processed' container, while denying access to all other users. You have already configured the storage account firewall to allow access only from your corporate network. What should you do next?

A.Create a private endpoint for the storage account and assign the data-scientists group to the private endpoint's access policy
B.Assign the Storage Blob Data Reader role to the data-scientists group at the storage account level and add a deny assignment for all other users
C.Use a managed identity for the data-scientists group and assign the Storage Blob Data Contributor role to the managed identity
D.Configure access control lists (ACLs) on the 'processed' container to grant read and execute permissions to the data-scientists group and set the default ACL to deny all
AnswerD

ACLs in ADLS Gen2 allow you to set fine-grained permissions at the file and directory level. By granting read and execute permissions to the data-scientists group on the 'processed' container and setting the default ACL to deny all others, only that group can read files.

Why this answer

In Azure Data Lake Storage Gen2 with hierarchical namespace, access control lists (ACLs) provide fine-grained permissions at the directory and file level. By granting read and execute permissions to the 'data-scientists' group on the 'processed' container and setting the default ACL to deny all, you ensure only that group can read files. Option A is incorrect because private endpoints control network access, not identity-based permissions.

Option B is incorrect because RBAC roles (like Storage Blob Data Reader) grant permissions at the storage account or container level, and Azure RBAC does not support deny assignments that would block specific users while allowing others at the same scope; a deny assignment would block everyone. Option C is incorrect because managed identities are used for authenticating Azure services, not for granting permissions to a security group.

Full explanation →
453
MCQeasy

You have an Azure Data Lake Storage Gen2 account that stores log files. You need to implement a data retention policy so that logs older than 90 days are automatically deleted. What should you use?

A.Azure Policy
B.Lifecycle management policy
C.Azure Blob Storage inventory
D.Microsoft Purview
AnswerB

A lifecycle management policy can automatically delete blobs based on age.

Why this answer

A lifecycle management policy can automatically delete blobs based on age. Option A is wrong because Azure Policy enforces compliance but does not delete. Option C is wrong because Azure Blob Storage inventory provides reports but does not delete.

Option D is wrong because Azure Purview scopes metadata and data discovery, not lifecycle management.

Full explanation →
454
MCQeasy

In Azure Synapse Analytics serverless SQL pool, you query Parquet files stored in Azure Data Lake Storage Gen2. You notice that queries are slow. Which configuration change is most likely to improve performance?

A.Partition the data in Azure Data Lake Storage Gen2
B.Create a pipeline in Azure Synapse to preprocess the data
C.Use OPENROWSET with a properly defined schema and file format
D.Increase the DWU setting of the serverless SQL pool
AnswerC

Specifying schema and file format helps the query optimizer generate efficient execution plans.

Why this answer

OPENROWSET with an explicitly defined schema and file format (e.g., FORMAT='PARQUET') enables the serverless SQL pool to bypass schema inference, which is a costly runtime operation. By providing a proper schema and file format, the query engine can directly read the Parquet metadata and column statistics, significantly reducing I/O and CPU overhead. This is the most direct and effective performance tuning change for querying Parquet files in a serverless SQL pool.

Exam trap

The trap here is that candidates confuse serverless SQL pool with dedicated SQL pool and assume that increasing DWU (a dedicated pool concept) will improve performance, or they think data partitioning alone is sufficient without addressing the schema inference overhead.

How to eliminate wrong answers

Option A is wrong because partitioning data in Azure Data Lake Storage Gen2 helps with file pruning and parallel reads, but it does not address the primary bottleneck of schema inference and metadata parsing that slows down serverless SQL pool queries. Option B is wrong because creating a preprocessing pipeline adds latency and complexity without fixing the root cause; it may even degrade performance if the pipeline introduces additional data movement or transformation overhead. Option D is wrong because serverless SQL pools do not have a DWU setting; DWU is a dedicated SQL pool (formerly SQL DW) concept, and serverless SQL pools scale automatically based on workload, so increasing a non-existent setting has no effect.

Full explanation →
455
MCQmedium

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Serverless SQL. Which statement about this table is correct?

A.The table supports indexing for performance
B.The external data source TYPE must be 'HADOOP' for Azure Data Lake Storage Gen2
C.The table references a single Parquet file named 'Sales.parquet'
D.The table is read-only
AnswerD

External tables are read-only; modifications must be done to underlying files.

Why this answer

External tables in Azure Synapse Serverless SQL are read-only because they provide a relational abstraction over data stored externally (e.g., in Azure Data Lake Storage Gen2 or Blob Storage). You cannot perform INSERT, UPDATE, DELETE, or DDL modifications on the underlying data through the external table; it is designed solely for querying with T-SQL. This is a fundamental constraint of the serverless SQL pool architecture, which uses the OPENROWSET or CREATE EXTERNAL TABLE syntax to read files in place without a storage engine.

Exam trap

The trap here is that candidates confuse external tables in Synapse Serverless SQL with external tables in dedicated SQL pools (which also support PolyBase with Hadoop connectors) and mistakenly think they can write to or index the table, or they misremember the required data source TYPE for ADLS Gen2.

How to eliminate wrong answers

Option A is wrong because external tables in Synapse Serverless SQL do not support indexing; indexing is a feature of dedicated SQL pools where you can create clustered columnstore indexes, but serverless SQL pool relies on file-level statistics and predicate pushdown to Parquet/CSV files. Option B is wrong because for Azure Data Lake Storage Gen2, the external data source TYPE must be 'HDFS' (not 'HADOOP') when using the abfss:// protocol; 'HADOOP' is used for legacy WASB or on-premises HDFS, and Synapse Serverless SQL requires the 'HDFS' type for ADLS Gen2. Option C is wrong because the CREATE EXTERNAL TABLE statement can reference a folder path containing multiple Parquet files, a glob pattern (e.g., 'Sales*.parquet'), or a single file, but the statement in the exhibit (not shown) typically points to a folder or pattern, and the question does not specify a single file; the table definition uses a LOCATION that can include wildcards, so it is not limited to one file.

Full explanation →
456
MCQeasy

You need to monitor the performance of Azure Stream Analytics jobs. Which Azure Monitor metric can be used to detect if the job is falling behind in processing input data?

A.WatermarkDelay
B.InputEventsBacklog
C.OutputEvents
D.RuntimeErrors
AnswerB

This metric shows the backlog of unprocessed input events.

Why this answer

InputEventsBacklog tracks the number of input events that remain unprocessed, directly indicating whether the job is falling behind. WatermarkDelay (option A) measures the time difference between event occurrence and processing, which can indicate latency but not the backlog count. OutputEvents (option C) counts events sent to output, unrelated to backlog.

RuntimeErrors (option D) counts errors, not backlog.

Full explanation →
457
MCQmedium

You are designing a data storage solution for a global e-commerce company. The company needs to store clickstream data from millions of users with high write throughput and low-latency reads for real-time analytics. The data is semi-structured and includes nested JSON objects. Which Azure data store should you recommend?

A.Azure Cosmos DB
B.Azure Table Storage
C.Azure SQL Database
D.Azure Blob Storage
AnswerA

Azure Cosmos DB provides high throughput, low-latency, and native JSON support.

Why this answer

Azure Cosmos DB is the correct choice because it provides a multi-model, globally distributed database service with guaranteed single-digit-millisecond read and write latencies at the 99th percentile, making it ideal for high-throughput clickstream ingestion and real-time analytics. Its native support for semi-structured data and nested JSON objects via the SQL API (or MongoDB API) allows direct storage and querying of complex event payloads without schema flattening. Additionally, Cosmos DB offers automatic indexing and tunable consistency levels to balance performance and data freshness for global e-commerce scenarios.

Exam trap

The trap here is that candidates often choose Azure Table Storage because it is a NoSQL store, but they overlook its lack of native JSON support and sub-10ms latency guarantees, confusing its simple key-value model with the richer document capabilities of Cosmos DB.

How to eliminate wrong answers

Option B (Azure Table Storage) is wrong because it is a NoSQL key-value store that does not natively support nested JSON objects; it requires flattening complex structures into flat key-value pairs, which adds overhead and complicates real-time analytics on clickstream data. Option C (Azure SQL Database) is wrong because it is a relational database that enforces a fixed schema, making it poorly suited for semi-structured, schema-on-read clickstream data with varying nested JSON fields; it also cannot match Cosmos DB's sub-10ms write throughput at scale. Option D (Azure Blob Storage) is wrong because it is an object store designed for large, unstructured binary data (e.g., images, logs) and does not provide low-latency, indexed query capabilities for real-time analytics on individual clickstream events; it lacks native support for querying nested JSON without additional compute layers like Azure Data Lake or Synapse.

Full explanation →
458
MCQeasy

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

A.No, you need an Azure integration runtime that is configured for on-premises access.
B.Yes, because the integration runtime is self-hosted and can access on-premises data sources.
C.No, you need a managed virtual network integration runtime for on-premises sources.
D.Yes, but only if the integration runtime is installed on a domain-joined machine.
AnswerB

Self-hosted IR is used for on-premises/private network data sources.

Why this answer

The ARM template snippet defines a self-hosted integration runtime, which is designed to be installed on a local machine inside the corporate network. This allows it to directly connect to on-premises SQL Server databases using native drivers (e.g., ADO.NET) and then copy data to Azure Blob Storage via the Azure cloud. Self-hosted IRs are the correct choice for hybrid data movement scenarios where the source is on-premises and the destination is cloud-based.

Exam trap

The trap here is that candidates often confuse the self-hosted IR with the Azure IR, assuming any IR can access on-premises data, but only the self-hosted IR (installed locally) can bridge the on-premises-to-cloud gap.

How to eliminate wrong answers

Option A is wrong because an Azure integration runtime (managed in the cloud) cannot directly access on-premises networks without a gateway or a self-hosted IR; the correct approach for on-premises sources is a self-hosted IR, not an Azure IR. Option C is wrong because a managed virtual network integration runtime is used for accessing Azure data sources within a managed VNet, not for on-premises sources that require a local installation. Option D is wrong because domain-joined machines are not a requirement for self-hosted IRs; they can be installed on any Windows machine with internet connectivity, though domain join may be used for security policies but is not mandatory.

Full explanation →
459
Multi-Selecthard

Which THREE actions improve the performance of a Delta table in Azure Databricks? (Choose three.)

Select 3 answers
A.Run VACUUM to remove old versions and compact small files
B.Disable auto-optimize to reduce overhead
C.Run OPTIMIZE with ZORDER BY on frequently filtered columns
D.Increase the number of shuffle partitions
E.Partition the table by high-cardinality columns
AnswersA, C, E

Compaction reduces file count.

Why this answer

Running VACUUM on a Delta table removes old file versions that are no longer referenced by the Delta transaction log, freeing up storage and reducing the number of small files that must be read during queries. This compaction of small files improves I/O efficiency and query performance, especially in tables with frequent updates or deletes.

Exam trap

The trap here is that candidates often confuse 'increasing shuffle partitions' (a Spark tuning parameter) with a direct performance improvement for Delta tables, when in fact it can worsen performance and is not a recommended action for Delta-specific optimization.

Full explanation →
460
MCQhard

Refer to the exhibit. You are creating an Azure Storage account using an ARM template with the above snippet. After deployment, a security auditor reviews the configuration and notes that the storage account is not using a customer-managed key for encryption. What is the most likely reason?

A.The 'keyVersion' is missing a specific version, so Azure Storage defaults to Microsoft-managed key.
B.The 'keySource' should be 'Microsoft.Storage' for customer-managed key.
C.The storage account requires double encryption to use customer-managed key.
D.The 'infrastructureEncryption' setting is enabled, which overrides customer-managed key.
AnswerA

For customer-managed key, a specific key version is required; an empty version may cause Azure to use the latest but if the key is not accessible, it falls back to Microsoft-managed key.

Why this answer

In the ARM template snippet, the 'keyVersion' property is empty. Azure Storage requires a specific key version to use a customer-managed key; if omitted, Azure defaults to Microsoft-managed keys if the key is not found or the vault is inaccessible. Option B is incorrect because 'keySource' should be 'Microsoft.Keyvault' for customer-managed keys, not 'Microsoft.Storage'.

Option C is incorrect because infrastructure encryption (double encryption) is independent of the key source. Option D is incorrect because enabling 'infrastructureEncryption' does not override the key source; it can be used with customer-managed keys.

Full explanation →
461
MCQmedium

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

A.Use Azure SQL Database with clustered columnstore index on date and device ID.
B.Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.
C.Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.
D.Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.
AnswerB

This folder structure enables efficient partition pruning based on date and device ID.

Why this answer

ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Exam trap

The trap here is that candidates confuse storage services (ADLS Gen2) with database or NoSQL solutions (SQL Database, Table Storage, Cosmos DB), failing to recognize that the question explicitly requires a data lake with a hierarchical namespace, which only ADLS Gen2 provides.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with a clustered columnstore index is a relational store, not a data lake solution, and it does not support a hierarchical namespace or folder-based partitioning as required. Option C is wrong because Azure Table Storage is a NoSQL key-value store that lacks a hierarchical namespace and folder organization; its PartitionKey/RowKey model does not provide the folder-based partitioning by date and device ID needed for ADLS Gen2. Option D is wrong because Azure Cosmos DB is a globally distributed NoSQL database, not a data lake storage service, and its partition key on (date, device ID) does not create a hierarchical folder structure in ADLS Gen2.

Full explanation →
462
MCQmedium

You are designing a streaming solution in Azure Synapse Analytics using the serverless SQL pool to query streaming data in real-time. The data is ingested via Azure Event Hubs and processed using Azure Stream Analytics. The output of Stream Analytics is written to Azure Data Lake Storage Gen2 in Delta Lake format. You need to ensure that the serverless SQL pool can query the latest data with minimal latency. Which approach should you use?

A.Ingest data directly from Event Hubs into serverless SQL pool using CETAS (CREATE EXTERNAL TABLE AS SELECT).
B.Use a materialized view in serverless SQL pool that refreshes every minute.
C.Load the streaming data into a dedicated SQL pool using a scheduled pipeline and then query it from serverless SQL pool.
D.Create an external table in serverless SQL pool that points to the Delta Lake folder and query it directly.
AnswerD

Serverless SQL pool supports Delta Lake format and can query it as soon as data is written.

Why this answer

Serverless SQL pool can directly query Delta Lake format files stored in Azure Data Lake Storage Gen2 by creating an external table with LOCATION pointing to the Delta folder. This allows real-time querying of the latest streaming data without any data movement or transformation, achieving minimal latency since Stream Analytics writes continuously to Delta Lake.

Exam trap

The trap here is that candidates may confuse serverless SQL pool with dedicated SQL pool and assume features like materialized views or scheduled pipelines are available, or they may think CETAS can directly ingest from Event Hubs, which is not supported.

How to eliminate wrong answers

Option A is wrong because CETAS creates a new external table by selecting data from a source, but it cannot ingest data directly from Event Hubs; it requires a source like an existing external table or file set, and it does not support real-time streaming ingestion. Option B is wrong because serverless SQL pool does not support materialized views; materialized views are a feature of dedicated SQL pool, not serverless. Option C is wrong because loading data into a dedicated SQL pool via a scheduled pipeline introduces batch latency (scheduled intervals), which contradicts the requirement for minimal latency in a streaming solution.

Full explanation →
463
Matchingmedium

Match each Azure service tier to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Hierarchical namespace for Azure Data Lake Storage

Optimized for frequent data access

Optimized for infrequent access with lower cost

Lowest cost for rarely accessed data

Why these pairings

In Azure Blob Storage and Data Lake Storage, access tiers are optimized for different access patterns. Hot is for frequent access, Cool for infrequent access (30 days), Cold for less frequent access (90 days), and Archive for rarely accessed data (180 days). Common confusions include swapping Cool and Cold descriptions or misattributing Archive characteristics.

Full explanation →
464
MCQmedium

You are troubleshooting a slow-running Azure Data Factory pipeline that copies data from an Azure SQL Database to ADLS Gen2. The pipeline uses a copy activity with the default settings. The source table has 10 million rows. Which optimization should you apply first?

A.Set the 'parallel copies' property to 10.
B.Replace the copy activity with a mapping data flow.
C.Increase the data integration unit (DIU) to maximum.
D.Enable staged copy using an Azure Blob Storage staging location.
AnswerD

Staging allows data to be transferred via Blob Storage, which improves throughput for SQL to ADLS copies.

Why this answer

Enabling staged copy via Azure Blob Storage improves performance for large datasets by allowing the copy activity to use blob storage as an intermediate staging location, reducing load on source and destination. Option A (parallel copies) may help but default auto parallelism already handles it. Option B (mapping data flow) is heavier and not needed for simple copy.

Option C (increase DIU) is a more expensive option and should not be the first optimization.

Full explanation →
465
MCQhard

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

A.Hash distribution on the most joined dimension key with clustered columnstore index
B.Round-robin distribution with clustered columnstore index
C.Replicated distribution with clustered columnstore index
D.Hash distribution on a dimension key with heap index
AnswerA

Hash distribution colocates join data and columnstore index optimizes analytics.

Why this answer

Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Exam trap

The trap here is that candidates often choose round-robin distribution thinking it balances load evenly, but they overlook the severe join performance penalty caused by data movement across distributions in star schema queries.

How to eliminate wrong answers

Option B is wrong because round-robin distribution distributes rows evenly without considering join keys, causing excessive data shuffling across distributions during joins, which increases query latency. Option C is wrong because replicated distribution copies the entire table to each distribution node, which is impractical for large fact tables due to storage overhead and data movement during updates. Option D is wrong because a heap index lacks ordering and compression, leading to full table scans and poor query performance for star schema queries.

Full explanation →
466
MCQeasy

A company runs a streaming pipeline using Azure Stream Analytics to ingest IoT data and output to Azure SQL Database. They notice that the output latency increases over time and eventually the job fails with a timeout error. What is the most likely cause?

A.The Stream Analytics job has a high late arrival tolerance.
B.The event hub is not partitioned correctly.
C.The event hub consumer group is misconfigured.
D.The Azure SQL Database target table lacks proper indexes.
AnswerD

Missing indexes slow down write operations, causing backpressure and eventual timeout.

Why this answer

The most likely cause is that the Azure SQL Database target table lacks proper indexes. Without indexes, each batch of output from Stream Analytics triggers full table scans for inserts or updates, causing cumulative latency. Over time, the backlog exceeds the job's timeout threshold (default 5 minutes for output), leading to failure.

Exam trap

The trap here is that candidates often attribute output latency to input-side issues like partitioning or consumer groups, but the symptom of increasing latency over time points to a downstream bottleneck, specifically missing indexes on the SQL target table.

How to eliminate wrong answers

Option A is wrong because high late arrival tolerance delays watermark advancement but does not cause progressive output latency or timeouts; it affects event ordering, not throughput. Option B is wrong because incorrect event hub partitioning affects input ingestion parallelism, not output latency to SQL Database; the job would show high input backlog, not output timeout. Option C is wrong because a misconfigured consumer group (e.g., multiple readers) causes checkpoint conflicts or duplicate reads, not a gradual increase in output latency; the job would fail with partition-related errors, not timeout.

Full explanation →
467
MCQmedium

You are developing a real-time data processing solution using Azure Stream Analytics. The input is from an Azure Event Hub, and the output is to an Azure Synapse Analytics dedicated SQL pool. You need to ensure exactly-once delivery semantics to the SQL pool. What should you configure?

A.Enable checkpointing in the query.
B.Use the Azure Synapse Analytics output adapter with exactly-once semantics.
C.Use a custom deserializer.
D.Configure event ordering by timestamp.
AnswerB

Stream Analytics supports exactly-once output to Synapse when configured correctly.

Why this answer

The Azure Synapse Analytics output adapter for Stream Analytics supports exactly-once delivery semantics to a dedicated SQL pool. This is achieved through built-in mechanisms that ensure each event is written exactly once, even in failure scenarios. Option A is incorrect because checkpointing is used for job recovery and state management, not for guaranteeing exactly-once output delivery.

Option C is incorrect because custom deserializers handle data format conversion, not delivery guarantees. Option D is incorrect because event ordering by timestamp helps with time-based processing but does not provide delivery semantics like exactly-once.

Full explanation →
468
MCQeasy

You need to store historical sales data for 10 years with infrequent queries. The storage cost must be minimized while retaining the ability to query using Azure Synapse serverless SQL pool. Which storage tier should you use?

A.Azure Storage Archive tier.
B.Azure Storage Premium tier.
C.Azure Storage Hot tier.
D.Azure Storage Cool tier.
AnswerD

Cool tier provides low-cost storage for infrequent access and is online for queries.

Why this answer

The Cool tier is the correct choice because it provides low-cost storage for data that is infrequently accessed (e.g., historical sales data spanning 10 years) while still supporting immediate read access via Azure Synapse serverless SQL pool. Unlike the Archive tier, Cool tier data is online and can be queried without the need for time-consuming rehydration, making it suitable for infrequent but on-demand analytical queries.

Exam trap

The trap here is that candidates often confuse 'infrequent queries' with 'no queries' and incorrectly choose the Archive tier, forgetting that Azure Synapse serverless SQL pool cannot directly query archived data without a time-consuming rehydration process.

How to eliminate wrong answers

Option A is wrong because the Archive tier is designed for long-term backup and rarely accessed data, requiring a rehydration step (which can take up to 15 hours) before data can be queried by Azure Synapse serverless SQL pool, making it unsuitable for even infrequent queries. Option B is wrong because the Premium tier is optimized for low-latency, high-transaction workloads and is significantly more expensive, which contradicts the requirement to minimize storage cost. Option C is wrong because the Hot tier is intended for frequently accessed data and has higher storage costs than the Cool tier, so it does not meet the cost-minimization goal for infrequently queried historical data.

Full explanation →
469
MCQhard

You run the PowerShell command shown in the exhibit for an Azure Synapse Analytics dedicated SQL pool. Which configuration will be applied?

A.The SQL pool is configured with transactional replication and auto-pause.
B.The command fails because dedicated SQL pools do not support auto-pause.
C.The SQL pool is set to auto-pause after 15 minutes of inactivity.
D.The SQL pool is partitioned into 10 partitions with automatic cleanup.
AnswerC

The AutoPauseDelayInMinutes parameter sets auto-pause; other properties are ignored.

Why this answer

The PowerShell command Set-AzSynapseSqlPool with the -AutoPauseDelayInMinutes parameter configures the dedicated SQL pool to automatically pause after 15 minutes of inactivity. This feature is supported for dedicated SQL pools in Azure Synapse Analytics. Therefore, option C is correct.

Option A is incorrect because transactional replication is not configured by this command. Option B is incorrect because dedicated SQL pools do support auto-pause. Option D is incorrect because partitioning is not involved.

Exam trap

Candidates often confuse dedicated and serverless SQL pool capabilities. While auto-pause was historically only for serverless, dedicated SQL pools now support it via PowerShell, leading many to incorrectly choose option B.

Full explanation →
470
MCQmedium

You are reviewing an ARM template snippet for an Azure Blob Storage container. What is the effect of this configuration?

A.It enables legal hold on the container for 2555 days.
B.It blocks append writes to blobs in the container.
C.It enforces a 7-year immutable retention policy allowing appends.
D.It sets a 3-year retention policy blocking any modifications.
AnswerC

2555 days ≈ 7 years; allowProtectedAppendWrites allows log appends.

Why this answer

The ARM template snippet configures a time-based retention policy on the container with a retention period of 2555 days (7 years) and sets `allowProtectedAppendWrites` to true. This enables an immutable storage policy that prevents deletion or modification of existing blobs but allows new append blocks to be added to append blobs, making option C correct.

Exam trap

The trap here is that candidates confuse the 2555-day value with a 3-year period (1095 days) or misinterpret `allowProtectedAppendWrites` as blocking appends, when in fact it enables them under immutable storage.

How to eliminate wrong answers

Option A is wrong because legal hold is a separate immutable policy that does not have a time limit; it remains in effect until explicitly removed, and the snippet specifies a time-based retention period of 2555 days, not legal hold. Option B is wrong because the snippet sets `allowProtectedAppendWrites` to true, which explicitly permits append writes to append blobs, not blocks them. Option D is wrong because the retention period is 2555 days (7 years), not 3 years, and the policy allows appends rather than blocking all modifications.

Full explanation →
471
MCQhard

A company uses Azure Data Lake Storage Gen2 with a hierarchical namespace. They need to secure access to specific directories using RBAC roles. Which RBAC role should be assigned to a user to grant read and write access to a specific folder without giving access to other folders in the same container?

A.Storage Account Contributor
B.Storage Blob Data Contributor with ACLs at folder level
C.Storage Blob Data Reader
D.Storage Blob Data Owner
AnswerB

Grants read/write to specific folder.

Why this answer

Azure Data Lake Storage Gen2 supports POSIX-like access control lists (ACLs) at the directory and file level. Assigning the Storage Blob Data Contributor role at the storage account level grants broad data-plane access, but when combined with ACL entries on a specific folder, you can restrict read and write permissions to only that folder. This allows granular security without affecting other folders in the same container.

Exam trap

The trap here is that candidates often assume RBAC roles alone can be scoped to a folder level, but in Azure Data Lake Storage Gen2, RBAC roles apply to the entire storage account or container, and folder-level security requires ACLs.

How to eliminate wrong answers

Option A is wrong because Storage Account Contributor is an Azure RBAC role that grants management-plane access (e.g., creating storage accounts, managing keys) but does not grant any data-plane permissions to read or write blob data. Option C is wrong because Storage Blob Data Reader provides read-only access to all blob data in the storage account; it cannot be scoped to a specific folder and does not grant write access. Option D is wrong because Storage Blob Data Owner grants full data-plane control (read, write, delete, and ACL management) over all blobs in the storage account, which is far broader than the required folder-level restriction.

Full explanation →
472
MCQmedium

You are designing a security strategy for Azure Synapse Analytics. The solution must prevent users from accessing sensitive columns in a dedicated SQL pool, such as Social Security numbers, unless they have explicit permission. Which feature should you use?

A.Column-level security.
B.Azure Purview data classification.
C.Dynamic data masking.
D.Row-level security (RLS).
AnswerA

Column-level security (CLS) restricts access to specific columns by granting or denying SELECT permissions on individual columns.

Why this answer

Column-level security (CLS) restricts access to specific columns by granting or denying SELECT permissions on individual columns. Option C (Dynamic data masking) obfuscates data at query time but does not prevent access, as users can still see the data if they bypass masking. Option B (Azure Purview) is a data governance service for cataloging and classifying data, not for access control.

Option D (Row-level security) filters rows based on user context, not columns.

Full explanation →
473
Multi-Selecteasy

Which TWO Azure services can be used to ingest streaming data into Azure Synapse Analytics?

Select 2 answers
A.Azure Databricks Auto Loader.
B.Azure Stream Analytics.
C.Azure Data Factory.
D.Azure Event Hubs.
E.Azure Logic Apps.
AnswersB, D

Stream Analytics can output to Synapse SQL pool or ADLS Gen2.

Why this answer

Azure Stream Analytics is correct because it is a fully managed stream processing engine that can ingest streaming data from sources like Azure Event Hubs and output directly to Azure Synapse Analytics (via SQL pool or dedicated SQL pool). It enables real-time analytics on streaming data before landing it in Synapse for further analysis.

Exam trap

The trap here is that candidates often confuse batch ingestion tools (like Azure Data Factory or Auto Loader) with real-time streaming services, or mistakenly think Event Hubs alone can ingest into Synapse without a processing layer like Stream Analytics.

Full explanation →
474
Multi-Selecthard

Which THREE Azure services can you use together to build a serverless, event-driven data processing pipeline that ingests data from Azure Blob Storage, transforms it using custom code, and loads it into Azure Cosmos DB?

Select 3 answers
A.Azure Functions
B.Azure Cosmos DB output binding (with Azure Functions)
C.Azure Batch
D.Azure Event Grid
E.Azure Logic Apps
AnswersA, B, D

Functions can be triggered by Blob events and run custom code.

Why this answer

Options A, B, and D are correct. Azure Functions can be triggered by Blob Storage events to run custom code, and the output can be written to Cosmos DB via the Cosmos DB output binding (option B). Azure Event Grid (option D) enables event-driven triggers for the pipeline.

Option C (Azure Batch) is wrong because it is designed for large-scale parallel computing, not event-driven triggers. Option E (Azure Logic Apps) is wrong because it is more for workflow orchestration with connectors, not custom code execution.

Full explanation →
475
MCQhard

Refer to the exhibit. You are reviewing the workload classifier configuration for an Azure Synapse Analytics dedicated SQL pool. You notice that the 'HeavyLoader' classifier has a queryExecutionTimeoutSeconds of 0. What is the implication of this setting?

A.Queries classified as 'HeavyLoader' will wait indefinitely for resources.
B.The configuration is invalid; queryExecutionTimeoutSeconds must be greater than 0.
C.Queries classified as 'HeavyLoader' will not have a timeout.
D.Queries classified as 'HeavyLoader' will timeout immediately.
AnswerC

A value of 0 disables the query execution timeout.

Why this answer

When queryExecutionTimeoutSeconds is set to 0, it means no timeout is enforced. Queries classified as 'HeavyLoader' can run indefinitely without being terminated by the timeout mechanism. Option A is incorrect because a value of 0 does not mean indefinite waiting for resources; it refers to the timeout duration.

Option B is incorrect; 0 is a valid configuration that disables the timeout. Option D is incorrect; a timeout of 0 does not cause immediate timeout but rather no timeout.

Full explanation →
476
MCQmedium

Your company runs a critical data pipeline using Azure Data Factory (ADF) that ingests data from multiple sources into an Azure Synapse Analytics dedicated SQL pool. Recently, you have observed that the pipeline frequently fails with the error: 'Operation for target table failed: 'Cannot insert duplicate key row in object 'dbo.FactSales' with unique index 'PK_FactSales'. The duplicate key value is (20241001, 12345).'' The pipeline uses a Copy activity with a stored procedure sink that merges data into the fact table. The fact table has a clustered columnstore index and a unique constraint on (DateKey, ProductKey). You need to modify the pipeline to handle duplicates without losing data and without impacting performance significantly. What should you do?

A.Configure the Copy activity sink to use 'upsert' behavior with the unique key columns.
B.Change the distribution of the fact table to round-robin and remove the unique constraint.
C.Use a staging table and then execute a T-SQL MERGE statement to update or insert.
D.Add a pre-copy script to delete existing rows that match the incoming data before the copy.
AnswerA

ADF's upsert uses the source to update matching rows and insert new ones, avoiding duplicate key violations.

Why this answer

Azure Data Factory's Copy activity supports native upsert behavior when using a stored procedure sink, allowing it to handle duplicate key violations by updating existing rows instead of failing. By specifying the unique key columns (DateKey, ProductKey) in the upsert configuration, the pipeline can merge incoming data into the fact table without requiring manual staging or pre-cleanup, minimizing performance impact by leveraging the existing clustered columnstore index and unique constraint.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing a manual staging table approach (Option C) or a destructive pre-copy script (Option D), not realizing that ADF's native upsert feature is designed specifically to handle duplicate key violations in a performant and atomic manner.

How to eliminate wrong answers

Option B is wrong because changing the distribution to round-robin and removing the unique constraint would eliminate the duplicate detection mechanism, potentially allowing data integrity issues and degrading query performance due to data movement during joins. Option C is wrong because using a staging table and a T-SQL MERGE statement introduces additional latency and complexity, and while it can handle duplicates, it is less efficient than the native upsert feature in ADF, which is optimized for such scenarios. Option D is wrong because adding a pre-copy script to delete existing rows before the copy is a workaround that can cause data loss (deleting legitimate rows) and does not handle concurrent inserts or updates gracefully, leading to potential race conditions and performance overhead.

Full explanation →
477
Multi-Selecthard

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Select 3 answers
A.Align distribution keys on tables that are frequently joined together
B.Minimize data skew by choosing a distribution key with many unique values
C.Use round-robin distribution for large fact tables to distribute data evenly
D.Use replicated tables for large tables to avoid data movement
E.Consider the size of the table and the frequency of joins
AnswersA, B, E

Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance.

Why this answer

Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often confuse round-robin distribution as a good choice for large fact tables because it distributes data evenly, but they overlook the severe performance penalty from data movement during joins and aggregations.

Full explanation →
478
Matchingmedium

Match each performance optimization technique to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Dividing data into smaller manageable segments

Creating structures to speed up data retrieval

Pre-computed and stored query results

Column-based storage for analytics queries

Why these pairings

Correct matches: Columnstore indexes improve scan performance via columnar storage; partition switching enables fast data segment movement; materialized views pre-compute aggregations; result set caching caches query results. Common confusions include swapping partition switching with columnstore indexes or materialized views with result set caching.

Full explanation →
479
MCQeasy

A data engineer needs to store log data from multiple applications in Azure. The data is append-only, heavily compressed, and queried infrequently. Cost minimization is critical. Which storage solution is best?

A.Azure Table Storage
B.Azure Cosmos DB with analytical store
C.Azure Blob Storage with cool or archive access tier
D.Azure Data Lake Storage Gen2 with hot tier
AnswerC

Low cost for infrequent access.

Why this answer

Azure Blob Storage with cool or archive access tier is the best choice because the data is append-only, heavily compressed, and infrequently queried, making cost minimization the top priority. The cool tier offers low storage costs with higher access charges, while the archive tier provides the lowest storage cost for data that is rarely accessed and can tolerate hours of retrieval latency. This aligns perfectly with the append-only, infrequently queried nature of the log data.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage's access tiers with Data Lake Storage Gen2's tiers, assuming the hot tier is always the default for log data, but the question's emphasis on 'cost minimization' and 'infrequently queried' explicitly points to cool or archive tiers, not the hot tier.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for structured, semi-structured data with frequent point queries, not for large, append-only, compressed log blobs; it lacks the cost-optimized access tiers needed for infrequent access. Option B is wrong because Azure Cosmos DB with analytical store is a globally distributed, multi-model database optimized for low-latency transactional and analytical workloads, which is over-engineered and costly for append-only, infrequently queried log data; its analytical store is designed for near-real-time analytics, not cold storage. Option D is wrong because Azure Data Lake Storage Gen2 with hot tier is optimized for high-frequency access and big data analytics, with higher storage costs than cool or archive tiers, making it unsuitable for cost minimization when data is infrequently queried.

Full explanation →
480
MCQhard

Refer to the exhibit. A data engineer receives this error when running a copy activity that loads data into a SQL staging table. The source is a CSV file. What is the most likely cause?

A.The 'ID' column in the source CSV file contains empty strings or missing values.
B.The SQL database is out of space.
C.The CSV file is missing the header row.
D.The staging table schema does not match the source schema.
AnswerA

Empty strings or missing values are interpreted as NULL, violating the NOT NULL constraint.

Why this answer

The error indicates that a NULL value is being inserted into a NOT NULL column 'ID' in the staging table. This usually happens when the source data has missing or null values for that column, or the column mapping is incorrect.

Full explanation →
481
MCQmedium

You are using Azure Synapse Analytics serverless SQL pool to query data in Parquet files stored in Azure Data Lake Storage Gen2. The queries are slow when filtering on a date column. You need to improve query performance without changing the data structure. What should you do?

A.Create materialized views in the serverless SQL pool
B.Increase the service level objective (SLO) of the serverless SQL pool
C.Convert the Parquet files to CSV format
D.Partition the Parquet files into folders by date
AnswerD

Partition elimination allows the serverless SQL pool to read only relevant folders, improving performance.

Why this answer

D is correct because partitioning Parquet files into folders by date enables partition elimination in Azure Synapse serverless SQL pool. When queries filter on the date column, the engine can prune entire folders from the scan, reading only the relevant Parquet files. This reduces I/O and improves performance without altering the data structure or format.

Exam trap

The trap here is that candidates may assume serverless SQL pool supports materialized views or SLO adjustments like dedicated SQL pool, but serverless SQL pool lacks these features and relies on data layout optimizations such as partitioning for performance.

How to eliminate wrong answers

Option A is wrong because materialized views in serverless SQL pool are not supported; they are only available in dedicated SQL pool. Option B is wrong because serverless SQL pool does not have a configurable service level objective (SLO); it scales automatically based on workload and cannot be manually increased. Option C is wrong because converting Parquet to CSV would increase file size and degrade performance due to the lack of columnar compression and predicate pushdown capabilities.

Full explanation →
482
MCQhard

You are a data engineer for a retail company. The company uses Azure Data Lake Storage Gen2 to store raw transaction data partitioned by date. Each day, a folder is created with the format 'YYYY/MM/DD' containing thousands of small JSON files (each ~10 KB). An Azure Databricks job runs daily to read the previous day's folder, transform the data, and write to a Delta table for reporting. Over time, the job's execution time has increased from 15 minutes to over 2 hours. The job uses a cluster with 4 nodes (each 16 GB memory). Monitoring shows that the job spends most of its time in the 'listing files' stage. Which optimization should you implement to reduce the job duration?

A.Increase the number of nodes in the cluster to 16.
B.Change the output format from JSON to Delta and enable Delta caching.
C.Pre-process the raw data to coalesce small JSON files into larger parquet files (e.g., 256 MB each).
D.Use Azure Data Factory instead of Databricks to copy the raw data.
AnswerC

Reduces the number of files, drastically cutting listing time.

Why this answer

The job spends most of its time in the 'listing files' stage because reading thousands of small JSON files (each ~10 KB) from Azure Data Lake Storage Gen2 incurs high metadata operation overhead. Coalescing these small files into larger Parquet files (e.g., 256 MB each) reduces the number of files that Spark must list and process, dramatically cutting down the listing stage time and improving overall throughput.

Exam trap

The trap here is that candidates often assume scaling the cluster (Option A) will solve any performance issue, but they fail to recognize that metadata operations like file listing are not parallelized across nodes and are limited by the storage account's API limits, not compute resources.

How to eliminate wrong answers

Option A is wrong because increasing the number of nodes to 16 does not address the root cause of high metadata overhead from listing thousands of small files; it would only add more parallelism to a bottleneck that is I/O and metadata-bound, not CPU-bound. Option B is wrong because changing the output format to Delta and enabling Delta caching optimizes the write/read side of the Delta table, but the bottleneck is in the input stage (listing and reading raw JSON files), not in the output stage. Option D is wrong because using Azure Data Factory to copy the raw data does not solve the file listing problem; it would still need to list the same small files and would not transform the data, and it introduces an unnecessary extra service without addressing the core issue of small file overhead.

Full explanation →
483
MCQeasy

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

A.Use Azure Data Lake Storage Gen2 for both transactional and historical data
B.Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data
C.Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data
D.Use Azure SQL Database with Hyperscale tier for both transactional and historical data
AnswerC

Cosmos DB offers low-latency reads/writes, and Blob Storage is cheap for bulk historical data.

Why this answer

Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Exam trap

The trap here is that candidates may assume a single service like Azure SQL Database or Data Lake Storage can handle both transactional and analytical workloads efficiently, overlooking the cost and performance trade-offs that make a hybrid approach optimal.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is optimized for big data analytics, not low-latency transactional queries, and cannot guarantee under 10ms response times. Option B is wrong because Azure Cache for Redis is an in-memory cache, not a durable transactional store, and Azure SQL Database for historical data incurs higher storage costs compared to Blob Storage. Option D is wrong because Azure SQL Database Hyperscale, while scalable, is more expensive for large historical data storage and does not minimize costs as effectively as Blob Storage.

Full explanation →
484
Multi-Selecthard

You are designing a stream processing solution using Azure Stream Analytics. The job must reference a static lookup table (product catalog) stored in Azure Blob Storage. The catalog is updated once daily. The job should automatically pick up the latest version without restarting. Which two configurations are required? (Choose two.)

Select 2 answers
A.Configure the reference input with a static blob path
B.Set the reference input's 'Path pattern' to include date and time placeholders
C.Enable 'Automatic refresh' and set the refresh rate to 1 day
D.Use Azure Event Grid to trigger job restart on blob update
E.Store the reference data in Azure SQL Database instead of Blob Storage
AnswersB, C

Why this answer

Azure Stream Analytics reference data inputs support path pattern placeholders like {date} and {time} to dynamically resolve the latest blob file. This allows the job to automatically load a new version of the static lookup table when the blob is updated, without requiring a job restart. The path pattern must be structured to match the naming convention of the uploaded file, such as 'catalog/{date}/{time}/products.csv'.

Exam trap

The trap here is that candidates often think a static path or Event Grid restart is needed, but the question specifically tests the combination of dynamic path patterns and automatic refresh to achieve zero-downtime updates in Azure Stream Analytics.

Why the other options are wrong

A

Static path does not trigger auto-refresh; you need a pattern to detect new blobs.

D

Restarting the job is not required; auto-refresh avoids restart.

E

While SQL Database is an option, the question specifies Blob Storage; auto-refresh works with Blob Storage.

Full explanation →
485
MCQmedium

You have an Azure Databricks workspace with a cluster that uses a Standard_LRS managed disk. You need to ensure that data at rest is encrypted using a customer-managed key (CMK). What should you configure?

A.Configure Azure Storage Service Encryption with a customer-managed key
B.Enable double encryption with a customer-managed key in Azure Disk Encryption
C.Enable Transparent Data Encryption (TDE) in Azure SQL Database
D.Use Azure Purview to classify and encrypt data
AnswerB

Azure Databricks clusters can use Azure Disk Encryption with CMK.

Why this answer

Azure Databricks managed disks support double encryption with a customer-managed key (CMK) using Azure Disk Encryption. This provides two layers of encryption: server-side encryption with a platform-managed key and additional encryption with a customer-managed key. Option A (Azure Storage Service Encryption) applies to Azure Storage accounts, not to Azure Databricks managed disks.

Option C (TDE) is for Azure SQL Database. Option D (Azure Purview) is a data governance service, not an encryption solution.

Full explanation →
486
MCQhard

You are designing a batch processing solution in Azure Databricks that reads Parquet files from Azure Data Lake Storage Gen2, performs aggregations, and writes results to a Delta table. The data volume is expected to grow to 10 TB per batch. You need to minimize shuffle operations during the aggregation step. Which approach should you recommend?

A.Partition the Delta table by the aggregation key and use the same partitioning when reading.
B.Use bucketing on a different column to reduce partition skew.
C.Use a broadcast join to avoid shuffle on the aggregation.
D.Use coalesce to reduce the number of partitions before aggregation.
AnswerA

Partitioning on the aggregation key ensures data is already grouped, minimizing shuffle.

Why this answer

Partitioning the Delta table by the aggregation key and aligning the read partitioning ensures that data with the same key value is co-located in the same Spark partition. This allows the aggregation to be performed within each partition without needing to shuffle data across the network, which is the primary cause of performance bottlenecks in large-scale batch processing.

Exam trap

The trap here is that candidates confuse partitioning (which co-locates data by key) with bucketing or coalesce, thinking any partition reduction will minimize shuffle, but only alignment of read and aggregation keys avoids the shuffle entirely.

How to eliminate wrong answers

Option B is wrong because bucketing on a different column does not reduce shuffle for the aggregation key; it only helps with join or lookup operations on the bucketed column. Option C is wrong because broadcast join is used to avoid shuffle during joins, not during aggregations; it cannot eliminate the shuffle required for grouping by a key. Option D is wrong because coalesce reduces the number of partitions but does not prevent shuffle; it may even increase shuffle overhead if applied before aggregation.

Full explanation →
487
MCQhard

You are a data engineer at a financial services company. Your Azure Synapse Analytics dedicated SQL pool contains a fact table named 'Transactions' with 10 billion rows. The table is hash-distributed on 'AccountID' and partitioned by month. You notice that queries filtering on 'TransactionDate' (a date column) are performing slowly despite partition elimination. You also observe that the 'Transactions' table is frequently joined with a 'DimAccount' dimension table on 'AccountID'. You need to optimize query performance for the most common workload: monthly reports that aggregate transaction amounts by account for the last 12 months. Additionally, you need to ensure that the solution minimizes maintenance overhead. What should you do?

A.Create a clustered columnstore index on the table
B.Redistribute the table on TransactionDate using hash distribution
C.Change distribution to round-robin to evenly distribute data
D.Use table replication for the Transactions table
AnswerA

Improves compression and scan performance for aggregations

Why this answer

Creating a clustered columnstore index on the 'Transactions' fact table provides column-level compression and significantly improves scan performance for aggregation queries, which is ideal for monthly reports aggregating transaction amounts. Option B is wrong because redistributing on TransactionDate using hash distribution would not improve join performance with DimAccount (which joins on AccountID) and may cause data skew if many rows share the same date. Option C is wrong because changing distribution to round-robin would eliminate collocation benefits for joins on AccountID, hurting query performance.

Option D is wrong because table replication is designed for small dimension tables, not large fact tables like 'Transactions' with billions of rows.

Full explanation →
488
Matchingmedium

Match each Azure data integration tool to its typical use case.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Query external data in Azure Storage using T-SQL

High-throughput data ingestion into Synapse SQL

Orchestrate data movement and transformation

Complex data engineering with notebooks

Why these pairings

In this matching exercise, the correct pairs are: Azure Data Factory for orchestration, Azure Synapse Analytics for data warehousing, Azure Databricks for data engineering/ML, and Azure Stream Analytics for real-time streaming. Common confusions involve swapping orchestration and streaming roles.

Full explanation →
489
Multi-Selecthard

You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which TWO actions can help reduce data movement during query execution?

Select 2 answers
A.Use hash distribution on a column that is not used in joins.
B.Use replicated tables for small dimension tables.
C.Use round-robin distribution for large fact tables.
D.Increase the resource class for the loading user.
E.Distribute fact tables on the join key columns.
AnswersB, E

Replicated tables copy data to all nodes, avoiding movement for joins.

Why this answer

Options B and E are correct. Using replicated tables for small dimension tables avoids data movement during joins because the table is copied to all distributions. Distributing fact tables on the join key columns ensures that matching rows are co-located, reducing shuffling.

Option A is incorrect: using hash distribution on a column not used in joins increases data movement because data is redistributed unnecessarily. Option C is incorrect: round-robin distribution distributes rows evenly but often requires data movement for joins. Option D is incorrect: increasing the resource class allocates more resources but does not directly reduce data movement.

Full explanation →
490
MCQeasy

You need to ensure that data stored in Azure Data Lake Storage Gen2 is encrypted at rest using customer-managed keys. Which Azure service should you use to manage the keys?

A.Azure Key Vault
B.Microsoft Purview
C.Azure Confidential Computing
D.Microsoft Entra ID
AnswerA

Azure Key Vault stores customer-managed encryption keys.

Why this answer

Azure Key Vault is used to store customer-managed keys for Azure Storage encryption. Option B is wrong because Microsoft Purview is for data governance. Option C is wrong because Azure Confidential Computing is for compute.

Option D is wrong because Microsoft Entra ID is for identity.

Full explanation →
491
MCQmedium

Your organization has an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only authorized users can access the data, and auditing must be enabled to track all access attempts. What should you do first?

A.Implement column-level security to restrict sensitive columns.
B.Enable auditing on the SQL pool and configure a storage account for audit logs.
C.Configure Microsoft Entra ID authentication and use RBAC to grant only necessary permissions.
D.Apply dynamic data masking to the sensitive columns.
AnswerC

Configuring Microsoft Entra ID authentication and RBAC is the foundational step to control who can access the SQL pool, ensuring only authorized users have access.

Why this answer

The first step to secure access to the Azure Synapse Analytics dedicated SQL pool is to configure authentication and authorization using Microsoft Entra ID and RBAC. This establishes who can access the pool and what they can do. Once that is in place, you can then implement additional security measures like auditing (option B), column-level security (option A), or dynamic data masking (option D), but these are secondary steps.

Option B is not the first step because auditing tracks access but does not control it. Option A is too granular for initial access control. Option D obfuscates data but does not prevent unauthorized access.

Full explanation →
492
MCQhard

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

A.Azure SQL Database firewall rules blocking the IP address of the integration runtime.
B.Transient network connectivity issues between the services.
C.The Azure SQL Database DTU limit has been exceeded, causing throttling.
D.The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.
AnswerD

SSL/TLS handshake failure often stems from TLS version mismatch.

Why this answer

The error 'Could not create SSL/TLS secure channel' indicates a TLS version mismatch. Azure SQL Database and Azure Blob Storage require at least TLS 1.2 for secure connections. If the self-hosted integration runtime (SHIR) is configured to use TLS 1.0, the handshake fails because the services reject the older protocol.

This is the most likely cause given the intermittent nature and the specific error message.

Exam trap

The trap here is that candidates confuse SSL/TLS errors with network connectivity or throttling issues, overlooking the specific protocol version mismatch that Azure services now enforce.

How to eliminate wrong answers

Option A is wrong because firewall rules blocking the SHIR IP would produce a different error (e.g., 'Cannot open server '...' requested by the login') and would be consistent, not intermittent. Option B is wrong because transient network issues typically result in timeouts or retryable errors, not a specific SSL/TLS channel creation failure. Option C is wrong because DTU throttling would cause performance degradation or 'Request limit exceeded' errors, not an SSL/TLS handshake failure.

Full explanation →
493
MCQeasy

You are monitoring Azure Stream Analytics job performance. The job is falling behind in processing real-time data. You notice that the SU (Streaming Unit) utilization is consistently at 90% or higher. What is the most appropriate action to improve throughput?

A.Change the output to use a partition scheme
B.Reduce the window duration in the query
C.Increase the number of Streaming Units (SUs)
D.Decrease the event ordering tolerance
AnswerC

Scaling out increases processing capacity

Why this answer

When SU utilization is consistently at 90% or higher, the job is resource-constrained, which leads to backpressure and falling behind. The most direct way to improve throughput is to increase the number of Streaming Units (SUs), as this adds more compute capacity. Option A (changing output to use a partition scheme) can improve performance in some cases but is not the immediate step and may not resolve high SU utilization.

Option B (reducing window duration) can cause data loss or inaccurate results, and does not address the resource bottleneck. Option D (decreasing event ordering tolerance) also risks data loss and is not a throughput improvement measure.

Full explanation →
494
MCQeasy

You need to monitor the performance of Azure Synapse Analytics dedicated SQL pool queries. Which Azure service should you use to identify long-running queries and resource bottlenecks?

A.Microsoft Purview Data Map.
B.Azure Synapse Studio monitoring hub and dynamic management views (DMVs).
C.Azure Log Analytics queries.
D.Azure Monitor Workbooks.
AnswerB

Monitoring hub and DMVs are designed for real-time query performance analysis.

Why this answer

Azure Synapse Studio monitoring hub provides a centralized view of dedicated SQL pool activity, including running and completed queries, and dynamic management views (DMVs) can be queried to identify long-running queries and resource bottlenecks. Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a performance monitoring tool. Option C is wrong because Azure Log Analytics queries can be used for historical analysis but require configuration and are not the primary tool for real-time live query monitoring in Synapse dedicated SQL pools.

Option D is wrong because Azure Monitor Workbooks provide customizable dashboards for various Azure services, but they are not specifically designed for direct query monitoring in Synapse SQL pools.

Full explanation →
495
MCQeasy

You need to store semi-structured JSON data from a web application that requires low-latency reads and writes at a global scale. The data must be indexed automatically and support SQL-like queries. Which Azure data store should you use?

A.Azure SQL Database
B.Azure Blob Storage
C.Azure Cosmos DB (NoSQL API)
D.Azure Table Storage
AnswerC

Cosmos DB provides global distribution, auto-indexing, and SQL API for JSON.

Why this answer

Azure Cosmos DB with the NoSQL API is the correct choice because it natively stores semi-structured JSON documents, provides automatic indexing of all properties, supports SQL-like queries via its query engine, and offers low-latency reads and writes at global scale through multi-region replication and configurable consistency levels. This combination directly matches the requirements for a globally distributed web application needing fast, queryable JSON storage.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's key-value capabilities with Cosmos DB's document model, mistakenly thinking Table Storage supports SQL-like queries and automatic indexing, when in fact it only supports OData queries and requires explicit partition and row keys for efficient access.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database that requires a fixed schema and is not optimized for semi-structured JSON data without manual schema management or JSON functions, nor does it provide automatic indexing of all JSON properties. Option B is wrong because Azure Blob Storage is an object store for unstructured data that does not support SQL-like queries or automatic indexing; it requires a separate compute layer (e.g., Azure Data Lake Analytics) for querying. Option D is wrong because Azure Table Storage is a NoSQL key-value store that does not support SQL-like queries, automatic indexing of all fields, or native JSON document storage; it uses OData queries and a flat schema.

Full explanation →
496
MCQeasy

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

A.Denies storage accounts that do not have any IP rules defined
B.Denies storage accounts that have firewall rules configured
C.Denies storage accounts that have public network access disabled
D.Denies storage accounts that allow public network access from all networks
AnswerD

defaultAction=Allow means public access from all networks.

Why this answer

The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Exam trap

The trap here is that candidates confuse the 'defaultAction' property with the presence of IP rules or firewall settings, leading them to think the policy denies accounts with any firewall rules rather than those that allow all networks.

How to eliminate wrong answers

Option A is wrong because the policy does not evaluate the presence or absence of IP rules; it only checks the 'defaultAction' property. Option B is wrong because the policy denies accounts that allow all networks, not those with firewall rules configured; firewall rules are a separate mechanism. Option C is wrong because the policy denies accounts where public network access is enabled (defaultAction = 'Allow'), not disabled; disabling public access would set defaultAction to 'Deny', which the policy does not target.

Full explanation →
497
MCQeasy

You are designing data security for Azure Data Lake Storage Gen2. The requirement is to prevent data from being accessed by anyone outside the corporate network. Which feature should you enable?

A.Use Azure Private Endpoint or service endpoint with a VNet.
B.Assign RBAC roles to deny access to all except corporate users.
C.Configure IP firewall rules to allow only corporate IP ranges.
D.Enable encryption at rest using customer-managed keys.
AnswerA

Private endpoint ensures data is accessed only from within the VNet.

Why this answer

Azure Private Endpoint or service endpoint with a VNet ensures that all traffic to the storage account stays within the corporate network and never traverses the public internet. Private Endpoint assigns a private IP from the VNet to the storage account, effectively isolating it from public access. This meets the requirement to prevent access from outside the corporate network by enforcing network-level isolation.

Exam trap

The trap here is that candidates often confuse network-level security (Private Endpoint) with access control (RBAC) or data protection (encryption), thinking that denying RBAC roles or enabling encryption alone can prevent external access, when only network isolation truly blocks traffic from outside the corporate network.

How to eliminate wrong answers

Option B is wrong because RBAC roles control authorization (who can access data) but do not enforce network boundaries; a user with the correct role could still access data from outside the corporate network. Option C is wrong because IP firewall rules can be bypassed if an attacker spoofs an allowed IP address or if the corporate network uses dynamic public IPs, and they do not provide the same level of isolation as Private Endpoint. Option D is wrong because encryption at rest protects data at the storage layer but does not control network access; data could still be accessed from outside the corporate network if other security measures are not in place.

Full explanation →
498
MCQhard

You are building a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hubs input with 10 partitions. The query performs a GROUP BY on a column that is not the partition key. To ensure consistency, which partitioning scheme should you use?

A.Use 'Passthrough' partitioning
B.Use 'PartitionBy' with the GROUP BY column
C.Increase the number of SUs to handle skew
D.Use 'INTO' with a 'PARTITION BY' clause
AnswerB

Why this answer

When performing a GROUP BY on a column that is not the partition key, you must use the PARTITION BY clause in the query to ensure that all rows with the same grouping value are processed by the same Stream Analytics node. This guarantees consistency and correctness of the aggregation, as it avoids data being split across multiple nodes without proper alignment.

Exam trap

The trap here is that candidates often confuse 'Passthrough' partitioning with automatic handling of GROUP BY, or they think increasing SUs can fix data skew, but the core requirement is explicit repartitioning via PARTITION BY to align the data with the grouping key.

Why the other options are wrong

A

Passthrough keeps the original partition scheme, which may not align with the GROUP BY column.

C

Scaling SUs does not fix partitioning alignment issues.

D

INTO is for output, not for repartitioning within the query.

Full explanation →
499
MCQhard

Your company uses Azure Data Factory to orchestrate data pipelines that ingest data from on-premises SQL Server to Azure Data Lake Storage Gen2. The network team has implemented a firewall that only allows outbound traffic on port 443. The on-premises SQL Server is not accessible via public endpoint. You need to configure a secure connection that complies with the firewall rules and uses managed identity for authentication. What should you use?

A.Use Azure ExpressRoute to connect the on-premises network to Azure, then use an Azure Integration Runtime with a VNet injection.
B.Set up a point-to-site VPN from Azure to on-premises and use an Azure Integration Runtime with a VNet integration.
C.Install a Self-hosted Integration Runtime on an on-premises VM, register it with Azure Data Factory using managed identity, and configure the pipeline to use this IR for the SQL Server connection.
D.Use an Azure Integration Runtime with a public endpoint and configure a firewall rule to allow the Azure IR IP addresses.
AnswerC

Uses private network and port 443 for communication.

Why this answer

A self-hosted integration runtime (IR) installed on an on-premises VM can connect to SQL Server over the private network, then communicate with Azure Data Factory over port 443. Managed identity can be used for authentication to Azure Data Factory. Option A is incorrect: Azure IR with VNet injection still requires a public endpoint for on-premises SQL Server, which is not accessible.

Option B is incorrect: Point-to-site VPN provides connectivity but does not directly support managed identity for ADF authentication, and additional configuration is needed. Option D is incorrect: Using an Azure IR with a public endpoint requires the on-premises SQL Server to have a public endpoint, which it does not, and it does not utilize managed identity for authentication.

Full explanation →
500
MCQeasy

You are building a data transformation in Azure Databricks using PySpark. The data includes a column 'timestamp' in string format 'yyyy-MM-dd HH:mm:ss'. You need to convert this to a timestamp type and extract the date part for partitioning. Which code snippet should you use?

A.df.withColumn('date', col('timestamp').cast('date'))
B.df.withColumn('date', to_timestamp('timestamp', 'yyyy-MM-dd HH:mm:ss'))
C.df.withColumn('date', to_date('timestamp', 'yyyy-MM-dd HH:mm:ss'))
D.df.withColumn('date', to_date('timestamp'))
AnswerC

Correctly converts string to date with format.

Why this answer

`to_date` with the format string 'yyyy-MM-dd HH:mm:ss' converts the string column to a date type, extracting only the date part (year, month, day) as required for partitioning. This matches the requirement to convert the timestamp string to a date for partitioning, not a full timestamp.

Exam trap

The trap here is that candidates often confuse `to_date` and `to_timestamp`, assuming both extract only the date, or they forget that `cast('date')` does not accept a custom format string, leading to runtime errors or null values.

How to eliminate wrong answers

Option A is wrong because `cast('date')` on a string column will fail or produce null if the string is not in a default date format (e.g., 'yyyy-MM-dd'), and it does not accept a custom format pattern. Option B is wrong because `to_timestamp` converts the string to a full timestamp type (including time), not just the date part, which is not suitable for partitioning by date. Option D is wrong because `to_date('timestamp')` without a format string relies on the default date format (typically 'yyyy-MM-dd'), which will fail or produce incorrect results for strings with time components like 'yyyy-MM-dd HH:mm:ss'.

Full explanation →
501
MCQmedium

You are designing a data pipeline in Azure Synapse Analytics that ingests streaming taxi trip data from Azure Event Hubs. The data must be processed in near real-time and stored in a dedicated SQL pool. The pipeline should handle late-arriving data (up to 30 minutes late) without reprocessing the entire stream. Which Azure service should you use to process the streaming data?

A.Azure Databricks Structured Streaming
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Functions with Event Hubs trigger
AnswerB

Azure Stream Analytics provides built-in support for late-arriving events and can output directly to Synapse SQL pool.

Why this answer

Azure Stream Analytics is the correct choice because it is designed for real-time stream processing with native support for Event Hubs as an input and dedicated SQL pool as an output. It can handle late-arriving data via its built-in 'late arrival' window (configurable up to 30 minutes) using event time processing, without requiring reprocessing of the entire stream.

Exam trap

The trap here is that candidates often confuse Azure Data Factory's 'real-time' monitoring or Azure Functions' 'event-driven' nature with true stream processing, overlooking the need for built-in windowing and late-arrival handling that only Azure Stream Analytics provides.

How to eliminate wrong answers

Option A is wrong because Azure Databricks Structured Streaming is a batch-micro-batch engine that, while capable of streaming, requires manual management of late-arriving data via watermarking and checkpointing, and does not natively integrate with dedicated SQL pool as a sink without additional complexity. Option C is wrong because Azure Data Factory is an orchestration and ETL service for batch data movement, not a real-time stream processing engine; it cannot process streaming data from Event Hubs in near real-time. Option D is wrong because Azure Functions with Event Hubs trigger processes events one at a time in a serverless compute model, which lacks the built-in windowing, aggregation, and late-arrival handling capabilities needed for near real-time stream processing at scale.

Full explanation →
502
MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?

A.Change the distribution to round-robin
B.Replicate the table to all distributions
C.Create a columnstore index on OrderDate
D.Change the distribution to hash on OrderDate
AnswerD

Aligns distribution with filter column, minimizing data movement.

Why this answer

Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.

Exam trap

The trap here is that candidates often confuse indexing (columnstore) with distribution strategy, assuming a non-clustered index on the filter column is sufficient, when in fact the distribution key must match the most frequent filter predicate to avoid full distribution scans.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without any logical grouping, which forces full table scans and data shuffling for all queries, making performance worse for filtered queries. Option B is wrong because replicating a large fact table to all distributions would consume excessive storage and cause significant overhead during data loading, and is typically reserved for small dimension tables. Option C is wrong because a columnstore index on OrderDate improves compression and scan efficiency but does not address the distribution mismatch; queries would still need to scan all distributions, missing the benefit of distribution elimination.

Full explanation →
503
MCQhard

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

A.Broadcast the smaller table (1 million rows) to all worker nodes.
B.Increase the cluster size to reduce shuffle overhead.
C.Create bucketed tables on the join key for both datasets.
D.Use Delta Lake and optimize file layout with OPTIMIZE command.
AnswerA

Broadcasting the smaller table avoids shuffling the large table, significantly reducing data movement.

Why this answer

Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Exam trap

The trap here is that candidates often assume increasing cluster size (Option B) is a universal performance fix, but the DP-203 exam specifically tests the understanding that shuffle reduction techniques like broadcast joins are more impactful than simply adding more nodes, especially when one dataset is small enough to fit in executor memory.

How to eliminate wrong answers

Option B is wrong because increasing cluster size does not reduce shuffle overhead; it only adds more parallelism, which can actually increase shuffle traffic and does not address the fundamental need to avoid shuffling large datasets. Option C is wrong because creating bucketed tables on the join key requires both datasets to be bucketed with the same number of buckets and a compatible bucketing scheme; while this can reduce shuffle, it involves significant upfront data reorganization and is not as immediate or lightweight as broadcasting the small table. Option D is wrong because using Delta Lake and the OPTIMIZE command improves file layout and read performance (e.g., bin-packing small files) but does not directly reduce shuffle during a join operation; shuffle reduction requires join-specific optimizations like broadcast or bucketing.

Full explanation →
504
MCQmedium

You have an Azure Databricks workspace that uses a managed resource group. The security team requires that all cluster nodes use no public IP addresses and that all outbound traffic goes through a firewall. What should you configure?

A.Configure service endpoints for Azure Storage and Azure Data Lake Storage.
B.Deploy the workspace in a VNet with forced tunneling enabled and a firewall.
C.Apply network security groups (NSGs) to the subnet that restrict outbound traffic.
D.Enable Azure Private Link for the Databricks workspace.
AnswerB

VNet injection with forced tunneling ensures cluster nodes have no public IPs and all outbound traffic goes through the firewall.

Why this answer

Deploying the Azure Databricks workspace in a VNet with forced tunneling enabled and a firewall ensures that cluster nodes have no public IP addresses and all outbound traffic is routed through the firewall. Option A is incorrect because service endpoints do not prevent public IPs on cluster nodes. Option C is incorrect because NSGs alone do not force traffic through a firewall; forced tunneling requires a route table with default route to the firewall.

Option D is incorrect because Azure Private Link only provides private connectivity to the workspace, but does not control outbound traffic from cluster nodes.

Full explanation →
505
Multi-Selectmedium

You are designing an Azure Stream Analytics job to process real-time IoT data from thousands of devices. The job must handle late-arriving events (up to 1 hour late) and out-of-order events (up to 5 minutes). Which two temporal policies should you configure?

Select 1 answer
A.Out of order tolerance window: 5 minutes; Late arrival tolerance window: 1 hour
B.Out of order tolerance window: 1 hour; Late arrival tolerance window: 5 minutes
C.Watermark delay: 1 hour; Out of order tolerance: 5 minutes
D.Use Event Hubs capture to handle late events; no additional configuration needed
AnswersA

Ly sets the out-of-order tolerance window to 5 minutes and the late arrival tolerance window to 1 hour, matching the scenario requirements.

Why this answer

Azure Stream Analytics uses two temporal policies to handle event timing: the late arrival tolerance window and the out-of-order tolerance window. The late arrival tolerance window defines how long the system waits for events that arrive after their timestamp. The out-of-order tolerance window specifies the maximum time difference allowed for events that arrive out of sequence.

In this scenario, you need a late arrival tolerance of 1 hour and an out-of-order tolerance of 5 minutes. Option A directly configures these values correctly. Option C is incorrect because 'watermark delay' is not a configurable temporal policy in Azure Stream Analytics; it is a concept used in Spark Structured Streaming.

Therefore, only Option A is correct.

Exam trap

The trap is to think that 'watermark delay' is a configurable policy in Azure Stream Analytics. In ASA, the equivalent is 'late arrival tolerance', not watermark delay. Option C uses Spark terminology and is therefore incorrect.

Why the other options are wrong

B

This swaps the policies; late arrival should be larger than out-of-order.

C

Watermark delay is not directly configurable; it's derived from the two tolerance windows.

D

Event Hubs capture is for storing raw events, not for handling out-of-order or late arrival in Stream Analytics.

Full explanation →
506
MCQhard

Your Azure Databricks workspace contains sensitive customer data. You need to ensure that only users with a specific Microsoft Entra ID role can access the workspace, and all access must be logged and monitored. You also need to audit data access at the table level. What should you implement?

A.Configure SCIM provisioning to sync the Entra ID group to Databricks and assign the group to the workspace
B.Set up IP access lists to restrict workspace access to the corporate network and enable diagnostic logs
C.Enable Unity Catalog and assign the Databricks workspace to use Entra ID as the identity provider. Configure audit logs for the workspace
D.Use Microsoft Defender XDR to monitor access to the Databricks workspace
AnswerC

Unity Catalog supports fine-grained access control and audit logging, integrated with Entra ID.

Why this answer

Azure Databricks with Unity Catalog provides fine-grained access control at the table level and integrates with Entra ID for authentication, and audit logs capture access events. Option A is wrong because SCIM provisioning only syncs users, not access control. Option B is wrong because IP access lists control network access, not data access.

Option D is wrong because Microsoft Defender XDR is for security monitoring across Microsoft 365, not specifically for Databricks table-level auditing.

Full explanation →
507
MCQmedium

You are a data engineer at Northwind Traders. You have an Azure Synapse Analytics workspace with dedicated SQL pools. You need to monitor query performance to identify slow-running queries and understand resource consumption. The solution must provide historical data for the last 30 days and allow alerting when queries exceed a certain duration. You also need to export the data to a Log Analytics workspace for correlation with other metrics. What should you use?

A.Enable Diagnostic Settings for the dedicated SQL pool to send logs to Azure Storage, and query DMVs for historical data.
B.Use Azure Storage Analytics to analyze logs from the storage account backing the SQL pool.
C.Enable Diagnostic Settings to stream SQL pool metrics and logs to a Log Analytics workspace, then create alert rules and use KQL queries for historical analysis.
D.Configure SQL Server Query Store and export data to Azure Blob Storage using elastic query.
AnswerC

Provides historical data and alerting.

Why this answer

Enabling Diagnostic Settings for the dedicated SQL pool to stream logs and metrics to Log Analytics allows historical data retention (up to 30 days or more) and enables alerting and KQL queries. Option A is incorrect: DMVs provide current state, not historical data for 30 days. Option B is incorrect: Azure Storage Analytics is for storage accounts, not Synapse SQL.

Option D is incorrect: Query Store is for SQL Server, not for monitoring resource consumption and doesn't integrate with Log Analytics.

Full explanation →
508
Multi-Selecteasy

Which TWO are benefits of using Azure Databricks Auto Loader for incremental data ingestion?

Select 2 answers
A.It can process new files as they arrive in cloud storage.
B.It can handle large volumes of data without manual checkpointing.
C.It automatically evolves the schema without any configuration.
D.It provides sub-second latency for real-time streaming.
E.It provides built-in deduplication of records.
AnswersA, B

Auto Loader incrementally processes new files.

Why this answer

Azure Databricks Auto Loader is designed to incrementally ingest new data files as they arrive in cloud storage (e.g., Azure Data Lake Storage Gen2 or Blob Storage) by using a notification-based or directory-listing approach. It automatically tracks which files have already been processed using a structured streaming checkpoint mechanism, eliminating the need for manual checkpoint management even at high data volumes. This makes options A and B correct because Auto Loader's core value is its ability to discover and process new files without manual intervention and to handle large-scale ingestion reliably.

Exam trap

The trap here is that candidates often confuse Auto Loader's schema inference (which is automatic on first read) with automatic schema evolution (which requires explicit configuration), and they also mistakenly assume file-based ingestion can achieve sub-second latency or provide built-in deduplication, which are not features of this service.

Full explanation →
509
Multi-Selecteasy

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Select two.)

Select 2 answers
A.Azure Data Factory
B.Azure Monitor
C.Azure Storage
D.Azure Event Hubs
E.Azure Databricks
AnswersA, E

Data Factory offers mapping data flows and compute activities for transformation.

Why this answer

Azure Data Factory is a cloud-based ETL service that provides a code-free visual interface for orchestrating data movement and transformation at scale. It supports data flows, which allow you to perform transformations like aggregations, joins, and filtering without writing code, making it a correct choice for data transformation in a pipeline.

Exam trap

The trap here is that candidates often confuse data ingestion services (like Event Hubs) or storage services (like Azure Storage) with transformation services, forgetting that transformation requires compute engines like Data Factory or Databricks.

Full explanation →
510
MCQmedium

You are designing a near real-time analytics solution for a retail company. The company has a transactional database in Azure SQL Database that records sales transactions. The data must be available in Azure Synapse Analytics dedicated SQL pool for reporting with less than 15 minutes of latency. The data volume is about 10 GB per day. You need to design the data ingestion pipeline. You also need to ensure that the pipeline can handle schema changes (e.g., new columns added to the source table) without manual intervention. Which approach should you use?

A.Use Azure Databricks with Auto Loader to read from SQL Database and write to Synapse using Delta Lake.
B.Use Azure Data Factory with a change data capture (CDC) mapping data flow to read changes from SQL Database and write to Synapse dedicated SQL pool. Enable schema drift to handle new columns.
C.Use Azure Data Share to share the SQL Database tables with Synapse and schedule snapshots every 15 minutes.
D.Use Azure Synapse Pipelines with a copy activity to perform a full load every 15 minutes.
AnswerB

CDC provides incremental changes; schema drift allows automatic handling of new columns.

Why this answer

Azure Data Factory's mapping data flows support Change Data Capture (CDC) to incrementally load only changed rows from Azure SQL Database, meeting the <15-minute latency requirement for 10 GB/day. The 'Enable schema drift' option in mapping data flows automatically handles new columns added to the source table without manual intervention, which is essential for schema evolution.

Exam trap

The trap here is that candidates may choose Azure Databricks with Auto Loader (Option A) because it is associated with handling schema evolution, but they overlook that Auto Loader is file-based and not designed for direct CDC from Azure SQL Database, making it unsuitable for this transactional source.

How to eliminate wrong answers

Option A is wrong because Azure Databricks Auto Loader is designed for ingesting files (e.g., from cloud storage) and does not natively connect to Azure SQL Database for CDC; it would require additional connectors and manual schema handling, adding complexity without meeting the near-real-time requirement efficiently. Option C is wrong because Azure Data Share provides snapshot-based sharing with scheduled refreshes, but it does not support CDC or schema drift; it would require full or incremental snapshots that may not handle new columns automatically and could exceed latency if schema changes occur. Option D is wrong because using a full load every 15 minutes for 10 GB/day is inefficient and could cause high resource consumption and potential timeouts; it does not handle schema changes automatically and is not a best practice for near-real-time ingestion.

Full explanation →
511
Multi-Selecthard

You are building a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow to perform a lookup transformation. The lookup source is a dimension table with 10 million rows. You need to optimize the lookup performance. Which THREE actions should you take?

Select 3 answers
A.Increase the batch size in the lookup transformation settings.
B.Partition the dimension table on the lookup key before reading.
C.Enable the 'Broadcast' option on the lookup source transformation if the dimension table is less than 100 MB.
D.Select only the necessary columns in the lookup source transformation.
E.Ensure the dimension table has an index on the columns used for the lookup.
AnswersC, D, E

Broadcasting avoids shuffle for small dimension tables.

Why this answer

To optimize lookup performance in a mapping data flow, the recommended actions are: C. Enable the 'Broadcast' option on the lookup source if the dimension table is less than 100 MB. This avoids shuffling the large fact table across nodes.

D. Select only the necessary columns in the lookup source transformation to reduce data transfer and memory usage. E.

Ensure the dimension table has an index on the columns used for the lookup to speed up the join operation. Options A and B are not optimal: Increasing batch size (A) does not improve lookup performance; it affects sink writes. Partitioning the dimension table on the lookup key before reading (B) can be helpful, but in a data flow, partitioning is typically applied to the source or within the data flow itself, and it is not one of the top three recommended actions for lookup optimization in Synapse mapping data flows.

Full explanation →
512
Multi-Selecthard

Which THREE components are required to implement a real-time data processing solution using Azure Stream Analytics?

Select 3 answers
A.Power BI as the output sink
B.Azure Data Factory pipeline for orchestration
C.An input source such as Azure Event Hubs or IoT Hub
D.An output sink such as Azure Synapse Analytics or Blob Storage
E.A Stream Analytics job with a defined query
AnswersC, D, E

Streaming input is required for real-time processing.

Why this answer

Azure Stream Analytics requires a streaming input source to ingest real-time data. Azure Event Hubs and IoT Hub are the primary services that provide high-throughput, low-latency event ingestion, which Stream Analytics can consume via its built-in connector. Without a streaming input, the job cannot process real-time data.

Exam trap

The trap here is that candidates often assume Power BI is a required output for real-time dashboards, but Stream Analytics can function without any visualization sink, and the exam focuses on the minimal required components: input, job with query, and output sink.

Full explanation →
513
Multi-Selectmedium

Which of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2? (Choose two.)

Select 2 answers
A.Azure Storage Service Encryption (SSE) with Microsoft-managed keys
B.Azure Active Directory (Azure AD) authentication for storage accounts
C.Customer-managed keys stored in Azure Key Vault
D.Configure firewall rules to restrict IP access
AnswersA, C

Why this answer

Azure Storage Service Encryption (SSE) with Microsoft-managed keys encrypts data at rest automatically for Azure Data Lake Storage Gen2 using 256-bit AES encryption. This is enabled by default for all storage accounts, ensuring data written to disk is encrypted before being persisted, with no additional configuration required.

Exam trap

The trap here is confusing network security controls (like firewalls or Azure AD authentication) with data-at-rest encryption methods, leading candidates to select options that protect access rather than the stored data itself.

Why the other options are wrong

B

Azure AD authentication controls access, not encryption at rest.

D

Firewall rules control network access, not encryption at rest.

Full explanation →
514
MCQmedium

You are designing a data processing solution for an e-commerce company. The company receives millions of clickstream events per hour from their website and needs to aggregate the data by product category and windowed time intervals for real-time dashboards. You need to minimize latency and cost. Which service should you use?

A.Azure Databricks Structured Streaming
B.Azure Data Factory
C.Azure Stream Analytics
D.Azure Synapse Pipelines
AnswerC

Provides real-time stream processing with windowed aggregations.

Why this answer

(Azure Stream Analytics) is the best choice because it is purpose-built for real-time stream processing, supports windowed aggregations, and integrates with Power BI for dashboards. Option A (Azure Databricks Structured Streaming) can handle streaming but is more complex and typically more expensive for simple aggregations. Option B (Azure Data Factory) is for batch data movement, not real-time.

Option D (Azure Synapse Pipelines) is for orchestrating data movement, not real-time processing.

Full explanation →
515
MCQhard

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

A.The SQL Server is experiencing resource contention or timeout due to heavy load.
B.The Azure Blob Storage account is throttling requests.
C.The authentication method to SQL Server is incorrect.
D.The self-hosted integration runtime is not connected to the network.
AnswerA

Intermittent failures often due to resource pressure.

Why this answer

The error 'SqlOperationFailed' on the source side indicates that the SQL Server itself is failing to complete the query or data extraction operation. Under heavy load, the SQL Server may experience resource contention (CPU, memory, I/O) or reach query timeout thresholds, causing the copy activity to fail intermittently. This is consistent with the described scenario of heavy load during business hours.

Exam trap

The trap here is that candidates may confuse a source-side error with a sink-side error, or assume that any intermittent failure must be a network or connectivity issue, rather than recognizing that SQL Server resource contention under heavy load is a classic cause of intermittent 'SqlOperationFailed' errors.

How to eliminate wrong answers

Option B is wrong because Azure Blob Storage throttling would produce an error on the 'Sink' side (e.g., 'StorageError' or 'BlobOperationFailed'), not on the 'Source' side. Option C is wrong because an incorrect authentication method would cause a persistent authentication failure (e.g., 'Login failed for user') on every attempt, not intermittent failures. Option D is wrong because if the self-hosted integration runtime were not connected to the network, the pipeline would fail consistently with a connectivity error (e.g., 'Unable to connect to Integration Runtime'), not an intermittent SQL operation error.

Full explanation →
516
Drag & Dropmedium

Drag and drop the steps to configure Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2 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, set up the storage account and data. Then create the serverless SQL pool, a database, external data source referencing the storage, and finally external file format and external table to query.

Full explanation →
517
MCQeasy

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

A.Aggregate data by grouping rows.
B.Create new columns or modify existing columns using expressions.
C.Sort data in ascending or descending order.
D.Rename or drop columns.
AnswerB

Derived Column allows expression-based column creation and modification.

Why this answer

The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Exam trap

The trap here is that candidates confuse the Derived Column transformation with the Select transformation, assuming it is used for renaming or dropping columns, when in fact Derived Column is specifically for creating or modifying column values via expressions.

How to eliminate wrong answers

Option A is wrong because aggregating data by grouping rows is the purpose of the Aggregate transformation, not the Derived Column transformation. Option C is wrong because sorting data is performed by the Sort transformation, which reorders rows based on column values. Option D is wrong because renaming or dropping columns is handled by the Select transformation, which allows you to include, exclude, or alias columns.

Full explanation →
518
MCQhard

You are implementing a streaming solution using Azure Stream Analytics. The input is from an IoT Hub receiving telemetry from thousands of devices. The output is to Azure Synapse Analytics dedicated SQL pool. The requirement is to compute rolling averages over a 5-minute tumbling window and write results every minute. Which windowing function and output configuration should you use?

A.Use a TumblingWindow with duration of 5 minutes and output every 5 minutes.
B.Use a SlidingWindow with duration 5 minutes and output every 1 minute.
C.Use a HoppingWindow with size 5 minutes and hop 1 minute.
D.Use a SessionWindow with timeout 5 minutes and maximum duration 10 minutes.
AnswerC

Hopping windows with a 1-minute hop produce results every minute, each covering the last 5 minutes.

Why this answer

A HoppingWindow with a size of 5 minutes and a hop of 1 minute allows you to compute rolling averages over a 5-minute window while producing results every minute. This satisfies the requirement of outputting results at a higher frequency than the window duration, which is not possible with a TumblingWindow (which only outputs at the end of the window) or a SlidingWindow (which outputs on each event, not at fixed intervals).

Exam trap

The trap here is that candidates confuse the output frequency with the window duration, assuming a TumblingWindow can produce results more frequently by adjusting the duration, but only a HoppingWindow with a hop smaller than the size can achieve that.

How to eliminate wrong answers

Option A is wrong because a TumblingWindow with a duration of 5 minutes only produces output at the end of each 5-minute window, not every minute. Option B is wrong because a SlidingWindow with a duration of 5 minutes outputs results for every event (or at the end of each sliding interval), not at a fixed 1-minute interval; it also does not align with the requirement to output every minute on a schedule. Option D is wrong because a SessionWindow is designed for event-based sessions with gaps and timeouts, not for fixed-duration rolling averages with periodic output.

Full explanation →
519
MCQhard

You are optimizing a batch processing job in Azure Databricks that reads data from Azure Data Lake Storage Gen2 and writes aggregated results back. The job currently runs slowly due to high shuffle writes. You plan to use Delta Lake and optimize the table layout. Which two actions should you take to reduce shuffle writes? (Select two.)

A.Enable Delta Lake auto-optimize to coalesce small files.
B.Partition the Delta table by the most frequently used filter column.
C.Use a broadcast hash join hint for all joins.
D.Increase the number of shuffle partitions to 400.
E.Use the OPTIMIZE command with Z-Ordering on join keys.
AnswerB, E

Partitioning reduces the amount of data shuffled during queries that filter on that column.

Why this answer

To reduce shuffle writes in a Databricks batch job using Delta Lake, you should partition the Delta table by the most frequently used filter column (B) and use the OPTIMIZE command with Z-Ordering on join keys (E). Partitioning limits data shuffling by filtering out irrelevant partitions, and Z-Ordering colocates related data, reducing shuffle size during joins. Option A (auto-optimize) helps compaction but does not directly reduce shuffle writes.

Option C (broadcast hash join) only helps if one table is small, not for large tables. Option D (increasing shuffle partitions) often increases shuffle writes due to more tasks.

Full explanation →
520
MCQeasy

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

A.Notebook activity configured to use a Python kernel
B.Web activity
C.Stored Procedure activity
D.HDInsight Hive activity
AnswerA

A Notebook activity can run Python code in Synapse Spark notebooks.

Why this answer

A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Exam trap

The trap here is that candidates may confuse a Notebook activity with a Web activity or a Stored Procedure activity, thinking they can execute arbitrary code, but only the Notebook activity supports Python execution natively in Synapse pipelines.

How to eliminate wrong answers

Option B is wrong because a Web activity calls an HTTP/S endpoint (e.g., a REST API) and cannot run a Python script directly; it is used for invoking external services, not for executing code within Synapse. Option C is wrong because a Stored Procedure activity executes SQL stored procedures in a database, which is not designed for running Python scripts. Option D is wrong because an HDInsight Hive activity runs Hive queries on an HDInsight cluster, not Python scripts; it is meant for HiveQL, not Python execution.

Full explanation →
521
MCQeasy

You are reviewing an ARM template that assigns a role. What role is being assigned, and at what scope?

A.Storage Blob Data Owner at the subscription scope
B.Storage Blob Data Reader at the resource group scope
C.Storage Blob Data Contributor at the resource group scope
D.Storage Blob Data Contributor at the storage account scope
AnswerC

The roleDefinitionId is for 'Storage Blob Data Contributor' and scope is resourceGroup().id.

Why this answer

The roleDefinitionId in the ARM template corresponds to 'Storage Blob Data Contributor' and the scope is set to the resource group. Option A is wrong because the scope is not subscription level. Option B is wrong because the role is not 'Storage Blob Data Reader' (the GUID for that role is different).

Option D is wrong because the scope is the resource group, not the storage account.

Full explanation →
522
MCQhard

You are reviewing an Azure PowerShell script that sets permissions on a directory in Azure Data Lake Storage Gen2. The script sets a default ACL for a user on the path 'sales/2024/01/'. What is the effect of the -DefaultScope parameter?

A.The ACL replaces the existing access ACL on the directory.
B.The ACL is inherited by all new child items created under this directory.
C.The ACL is applied to all existing files and subdirectories recursively.
D.The ACL is applied only to files, not subdirectories.
AnswerB

Default ACLs set permissions that are inherited by new items.

Why this answer

The -DefaultScope parameter in Azure Data Lake Storage Gen2 sets a default ACL entry. Default ACLs do not set permissions on the current directory; instead, they define permissions that are inherited by new child items (files and subdirectories) created under that directory. Therefore, option B is correct.

Option A is incorrect because default ACLs do not replace the access ACL; access ACLs are set separately without -DefaultScope. Option C is incorrect because default ACLs do not apply to existing items; they only affect future items. Option D is incorrect because default ACLs apply to both new files and new subdirectories.

Full explanation →
523
MCQeasy

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

A.The storage account name is correct
B.The storage account firewall is configured to allow Azure services
C.A private endpoint is configured between Databricks and the storage account
D.The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account
AnswerD

RBAC role is required for write access

Why this answer

The 403 Forbidden error indicates that authentication succeeded but authorization failed. For a managed identity to write data to Azure Data Lake Storage Gen2, it must be assigned the 'Storage Blob Data Contributor' RBAC role on the storage account. This role grants read, write, and delete permissions for blobs and directories.

Without this role assignment, the managed identity cannot write data, resulting in a 403 error. Even if the storage account name is correct (A), the firewall is configured (B), or a private endpoint exists (C), the missing RBAC role assignment will cause the failure.

Exam trap

A common trap is to confuse 403 Forbidden (authorization failure) with 404 Not Found (resource not found). Ensure you check RBAC role assignments for managed identities rather than network configurations or resource existence.

Full explanation →
524
MCQeasy

Your team is developing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs. The output must be written to Azure SQL Database. You need to ensure that the processing guarantees exactly-once semantics for the output. Which output configuration should you use?

A.Set output mode to 'When possible'
B.Set output mode to 'Exactly once'
C.Set output mode to 'At least once'
D.Set output mode to 'Best effort'
AnswerB

Ensures no duplicate writes to SQL Database.

Why this answer

Azure Stream Analytics supports exactly-once semantics for output to Azure SQL Database when the output mode is set to 'Exactly once'. This ensures that each event is processed and written exactly once, preventing duplicates even in the event of failures or retries. The 'Exactly once' mode uses internal checkpointing and transactional writes to guarantee this behavior.

Exam trap

The trap here is that candidates may confuse 'Exactly once' with 'At least once', assuming that 'At least once' is sufficient for guaranteed delivery, but they overlook that 'At least once' can produce duplicates, which violates exactly-once requirements.

How to eliminate wrong answers

Option A is wrong because 'When possible' is not a valid output mode in Azure Stream Analytics; the correct modes are 'Exactly once' and 'At least once'. Option C is wrong because 'At least once' guarantees that events are delivered at least once, which can result in duplicate records in the output if failures occur. Option D is wrong because 'Best effort' is not a supported output mode in Azure Stream Analytics; it implies no delivery guarantee, which is not applicable to this service.

Full explanation →
525
MCQmedium

A data engineer is designing a monitoring solution for Azure Data Factory pipelines. They need to be alerted when a pipeline run fails or when the duration exceeds a threshold. The solution must minimize cost and operational overhead. Which approach should they use?

A.Configure Azure Event Grid to send pipeline run events to Azure Functions for alerting.
B.Use Azure Monitor metrics and activity logs to create alert rules for pipeline failures and duration.
C.Send all pipeline run logs to Log Analytics and create alert rules based on custom log searches.
D.Create an Azure Logic App that runs every minute to check pipeline run status via REST API.
AnswerB

Azure Monitor provides built-in metrics and alerts for Azure Data Factory with minimal cost.

Why this answer

Azure Monitor provides native, cost-effective alerting for Azure Data Factory pipelines using metrics (e.g., pipeline run duration) and activity logs (e.g., pipeline run failures). This approach requires no additional compute or log ingestion costs, as alerts are configured directly on the resource's monitoring data, minimizing both cost and operational overhead.

Exam trap

The trap here is that candidates over-engineer the solution by choosing event-driven or log-based approaches (A, C, D) when the simplest, most cost-effective native monitoring (Azure Monitor alerts) is available, often forgetting that Data Factory emits metrics and activity logs by default without additional setup.

How to eliminate wrong answers

Option A is wrong because Azure Event Grid with Azure Functions introduces unnecessary complexity and cost (function execution time) for a scenario that can be handled natively by Azure Monitor alerts without custom code. Option C is wrong because sending all pipeline run logs to Log Analytics incurs ingestion and retention costs, and custom log search alerts are more expensive and operationally heavier than using built-in metrics and activity log alerts. Option D is wrong because running a Logic App every minute to poll the REST API creates recurring execution costs and latency, and is an inefficient polling pattern compared to the event-driven, push-based alerting provided by Azure Monitor.

Full explanation →
Page 6

Page 7 of 11

Page 8

All pages

1234567891011

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

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