Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 1–75

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

Take a mock examExam hub

Page 1 of 11

Page 2
1
Multi-Selectmedium

You are securing an Azure Data Lake Storage Gen2 account that contains sensitive data. Which TWO of the following should you implement to protect data from unauthorized access?

Select 2 answers
A.Configure ACLs to grant least privilege to users and groups
B.Use private endpoints to restrict access to the storage account
C.Set the default ACL to allow read access for all authenticated users
D.Enable CORS rules to allow only specific origins
E.Enable large file shares on the storage account
AnswersA, B

ACLs allow granular permissions at the file and directory level.

Why this answer

ACLs provide fine-grained access control at the directory/file level, allowing you to grant least privilege to users and groups. Option B is correct because private endpoints ensure data traffic does not traverse the public internet, reducing exposure. Option C is wrong because a default ACL of 'read' for all authenticated users would grant broad access.

Option D is wrong because CORS rules are for web applications, not security. Option E is wrong because large file shares are for Azure Files, not ADLS Gen2.

Full explanation →
2
MCQeasy

You are monitoring Azure Data Factory pipelines. You need to set up an alert when a pipeline run fails. What is the most efficient way to achieve this?

A.Add an activity in each pipeline to log failures to a custom table in Log Analytics.
B.Enable diagnostic settings to send pipeline runs to a Log Analytics workspace and create a log alert.
C.Create an Azure Monitor alert based on the 'Failed pipeline runs' metric.
D.Use Application Insights to monitor pipeline executions.
AnswerC

This is the simplest method using built-in metrics.

Why this answer

The most efficient way to alert on pipeline failures is to use the 'Failed pipeline runs' metric in Azure Monitor, as it is a native metric provided by Azure Data Factory without additional configuration. Option A (logging to custom table) requires extra activities and setup. Option B (diagnostic settings and log alerts) is possible but more complex than using the metric.

Option D (Application Insights) is not directly integrated for pipeline run monitoring.

Full explanation →
3
MCQhard

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

A.The output schema in Synapse does not match the Stream Analytics output.
B.The Event Hubs has a large number of late-arriving events.
C.The tumbling window size is too large.
D.The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).
AnswerD

Insufficient SUs cause backpressure and watermark delay.

Why this answer

A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Exam trap

The trap here is that candidates may confuse a watermark delay alert with late-arriving events (Option B), but the alert indicates the job is falling behind overall, not just handling late data, and the 30-minute delay points to insufficient compute resources rather than data timing issues.

How to eliminate wrong answers

Option A is wrong because a schema mismatch between Stream Analytics output and Synapse would cause data write errors or failures, not a watermark delay alert or a 30-minute output delay. Option B is wrong because a large number of late-arriving events would increase the watermark delay but the alert specifically indicates the job is falling behind overall processing, not just handling late data; late events are managed by the late arrival policy and do not inherently cause a 30-minute delay. Option C is wrong because a 1-minute tumbling window is small and appropriate for real-time analytics; a larger window would reduce processing frequency, not cause delay.

Full explanation →
4
Multi-Selecteasy

Which THREE methods can you use to authenticate an Azure Data Factory pipeline to access Azure Data Lake Storage Gen2? (Choose THREE)

Select 3 answers
A.Use a service principal with a client secret or certificate.
B.Use a shared access signature (SAS) token.
C.Use a managed identity assigned to the Data Factory.
D.Use a user account with password.
E.Use the storage account access key.
AnswersA, B, C

Service principal with client secret or certificate is a valid authentication method for ADLS Gen2.

Why this answer

The correct answers are A, B, and C. Azure Data Factory supports three authentication methods for ADLS Gen2: service principal with client secret or certificate (A), SAS token (B), and managed identity (C). Service principal uses an Azure AD application.

SAS tokens provide scoped access. Managed identity allows Data Factory to authenticate without storing credentials. User account (D) and storage account access key (E) are not valid for ADLS Gen2 authentication.

Exam trap

This question tests your knowledge of all supported authentication methods. There are three valid options: service principal, SAS token, and managed identity. Candidates may mistakenly think only two are correct.

Full explanation →
5
MCQhard

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data in Azure Databricks with encryption at rest and in transit, and must also support row-level security (RLS). Which combination of features should you use?

A.Use Azure SQL Database with row-level security and connect Databricks via JDBC
B.Use Azure Databricks with Azure Key Vault for encryption and implement RLS in application code
C.Use Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted
D.Use Azure Databricks with Delta Lake and enable row-level security on the Delta table
AnswerD

Delta Lake in Databricks supports RLS natively, and encryption is handled automatically.

Why this answer

Delta Lake in Azure Databricks now supports row-level security (RLS) natively through Delta Sharing and Spark SQL predicates, allowing you to enforce data access policies directly on the Delta table without external systems. Combined with Azure Databricks' built-in encryption at rest (using Azure Storage Service Encryption or customer-managed keys) and in transit (TLS 1.2+), this meets all requirements without introducing additional services.

Exam trap

The trap here is that candidates often assume row-level security is only available in traditional relational databases like SQL Server or Azure SQL Database, but Delta Lake in Azure Databricks has supported RLS since runtime 10.4 LTS, making it a fully native option for data lakehouse architectures.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with row-level security requires JDBC connectivity, which introduces a separate database service and does not leverage Databricks' native Delta Lake capabilities; it also adds latency and complexity for encryption management. Option B is wrong because implementing RLS in application code is not a native feature of Azure Databricks and violates the principle of defense in depth—RLS must be enforced at the storage layer, not in application logic, to prevent bypass. Option C is wrong because Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted does not provide row-level security; dynamic data masking only obfuscates data at query results, and always encrypted protects data at rest and in transit but does not filter rows based on user permissions.

Full explanation →
6
MCQhard

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 (ADLS Gen2) as its data lake. A team of data scientists needs to process a massive dataset (approximately 5 TB) stored in Parquet format in the data lake. The dataset contains sales transactions from the past 10 years. The data scientists run a Spark job daily using Azure Synapse Analytics (serverless Spark pool) to compute aggregated sales metrics by product category and region. The job reads the entire dataset each day, performs transformations, and writes the aggregated results back to the data lake. Over the past few weeks, the job has been taking longer to complete, and the data scientists have reported that the job now takes over 6 hours, exceeding the acceptable SLA of 4 hours. They suspect the issue is related to data skew or suboptimal partitioning. You need to optimize the job to reduce execution time. Which approach should you take?

A.Increase the executor memory and cores in the Spark pool configuration to handle larger shuffles.
B.Repartition the data on the 'product_category' column with a higher number of partitions (e.g., 2000).
C.Implement incremental processing using Auto Loader with 'directoryListing' mode to process only new files since the last run.
D.Use a broadcast join hint on the fact table to reduce shuffle operations.
AnswerC

Auto Loader incrementally ingests new files, avoiding a full scan of the 5 TB dataset daily. This directly reduces the data processed and speeds up the job.

Why this answer

The job reads the entire 5 TB dataset daily, which is inefficient when only new data needs processing. Auto Loader with 'directoryListing' mode incrementally identifies and processes only new files since the last run, drastically reducing the data volume and execution time. This directly addresses the root cause of the SLA breach—reading unchanged historical data repeatedly—rather than tuning resources or partitioning.

Exam trap

The trap here is that candidates focus on tuning Spark parameters (memory, partitions, joins) to handle the existing workload, but the real issue is the unnecessary reprocessing of unchanged data, which only incremental loading can solve.

How to eliminate wrong answers

Option A is wrong because increasing executor memory and cores only improves shuffle performance for existing data volumes; it does not reduce the amount of data read or processed, so the job would still read the full 5 TB daily and likely remain over 6 hours. Option B is wrong because repartitioning on 'product_category' with 2000 partitions may help with data skew but does not eliminate the need to read the entire dataset each day; it also introduces a costly shuffle operation that could worsen performance. Option D is wrong because a broadcast join hint is used to optimize joins by broadcasting a small table to all executors, but the fact table (sales transactions) is massive (5 TB) and cannot be broadcast; this approach would cause out-of-memory errors or be ignored by Spark.

Full explanation →
7
MCQhard

You are monitoring an Azure Data Lake Storage Gen2 account using Metrics and Audit logs. You notice that the 'Ingress' metric shows a sudden spike but the 'Egress' metric remains stable. There are no new storage events in the audit log. What is the most likely cause?

A.The storage account is configured with geo-redundant storage (GRS) and data is being replicated to the secondary region.
B.A Spark job is reading large amounts of data in parallel.
C.An Azure Data Factory pipeline is writing intermediate results to the storage account.
D.An Azure Function is triggered by blob creation events and writes logs to the same account.
AnswerC

Writes increase ingress, and if the pipeline is using staging or intermediate storage, it may not log each write as a separate storage event.

Why this answer

An Azure Data Factory pipeline writing intermediate results to the storage account would cause a spike in 'Ingress' (data written into the account) without a corresponding increase in 'Egress' (data read from the account). The absence of new storage events in the audit log suggests the writes are not triggering blob-level events (e.g., BlobCreated events), which is consistent with Data Factory writing intermediate files using the Azure Blob Storage REST API or SDK without enabling event grid notifications for those specific operations.

Exam trap

The trap here is that candidates confuse 'Ingress' with 'Egress' or assume any write operation must generate a storage event, but Azure Storage events are opt-in and not all write operations (e.g., Data Factory intermediate writes) are configured to emit them.

How to eliminate wrong answers

Option A is wrong because geo-redundant storage (GRS) replication is asynchronous and occurs at the storage system level, not as user-visible 'Ingress' or 'Egress' metrics; replication traffic is internal and does not appear in the account's ingress/egress metrics. Option B is wrong because a Spark job reading data in parallel would increase 'Egress' (data read from storage), not 'Ingress', and the question states Egress remains stable. Option D is wrong because an Azure Function triggered by blob creation events would generate new storage events in the audit log (e.g., BlobCreated events), but the question explicitly states there are no new storage events.

Full explanation →
8
MCQeasy

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

A.Service Principal with a client secret stored in the notebook
B.Azure Key Vault-backed secret scope
C.Shared access signature (SAS) token
D.Storage account access key
AnswerB

Secret scopes securely reference secrets from Key Vault.

Why this answer

Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure.

Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

Full explanation →
9
MCQmedium

Your company uses Azure Data Lake Storage Gen2 and needs to implement a data retention policy that automatically deletes files older than 90 days in a specific container. What should you use?

A.Azure Data Factory pipeline with a Delete activity scheduled to run daily.
B.Azure Policy with a deny effect for files older than 90 days.
C.Azure Storage lifecycle management rule with a filter for the container and a delete action after 90 days.
D.Azure Purview data lifecycle policy.
AnswerC

Lifecycle management can automatically delete blobs based on age, reducing manual effort.

Why this answer

Azure Storage lifecycle management rules can be applied at the container level to automatically delete blobs after a specified age (90 days), providing a native, cost-effective, and automated retention solution. Option A is incorrect because Azure Data Factory can be scheduled to delete files, but it requires manual pipeline setup and incurs additional costs compared to lifecycle management. Option B is incorrect because Azure Policy with a deny effect prevents the creation or modification of resources, not the automatic deletion of existing files.

Option D is incorrect because Azure Purview is a data governance service for cataloging and lineage, not for automated data deletion.

Full explanation →
10
MCQmedium

You are developing a streaming solution with Azure Stream Analytics that outputs results to Azure Synapse Analytics. The output requires exactly-once semantics to avoid duplicates. Which configuration should you use?

A.Use an Azure Function output to write data idempotently to Synapse.
B.Configure the output to Azure Synapse Analytics with 'Exactly once' output mode.
C.Set the event serialization format to Avro and enable 'Exactly once' event delivery.
D.Output to Azure Blob Storage and then use a COPY statement in Synapse.
AnswerB

Stream Analytics supports exactly-once output to Synapse dedicated SQL pool.

Why this answer

Azure Stream Analytics supports an 'Exactly once' output mode for Azure Synapse Analytics, which ensures that each event is written exactly once to the destination, preventing duplicates. This mode uses a combination of checkpointing and transactional writes to guarantee idempotent delivery without requiring custom logic.

Exam trap

The trap here is that candidates often confuse 'exactly-once' with 'at-least-once' and assume that idempotent writes via Azure Functions or staging in Blob Storage can achieve the same guarantee, but Stream Analytics' built-in output mode is the only supported way to guarantee exactly-once delivery to Synapse without custom complexity.

How to eliminate wrong answers

Option A is wrong because Azure Functions do not natively provide exactly-once semantics for Stream Analytics outputs; they are at-least-once by default, and implementing idempotency in a function would require custom state management and is not a built-in feature. Option C is wrong because Avro serialization and 'Exactly once event delivery' are not supported output configurations for Azure Synapse Analytics; Stream Analytics uses its own output mode for Synapse, not event delivery settings. Option D is wrong because writing to Blob Storage and then using a COPY statement introduces at-least-once semantics from Stream Analytics to Blob, and the COPY statement can still produce duplicates if the same file is loaded multiple times, failing to achieve end-to-end exactly-once semantics.

Full explanation →
11
MCQeasy

You need to secure data at rest for an Azure Data Lake Storage Gen2 account that contains sensitive financial data. Which configuration should you enable to ensure that data is encrypted using a customer-managed key stored in Azure Key Vault, and that access to the key is logged?

A.Enable Azure Storage encryption with Microsoft-managed keys
B.Implement client-side encryption using Azure Key Vault
C.Enable infrastructure encryption for double encryption
D.Configure Azure Storage encryption with customer-managed keys in Azure Key Vault and enable Key Vault logging
AnswerD

This allows you to control and rotate keys, and logging provides an audit trail.

Why this answer

Azure Storage encryption with customer-managed keys in Key Vault provides control and logging. Option A is wrong because Microsoft-managed keys are the default but do not provide customer control. Option B is wrong because client-side encryption requires managing keys on the client side.

Option C is wrong because infrastructure encryption adds a second layer but does not use customer-managed keys.

Full explanation →
12
MCQmedium

A company runs a mission-critical Azure Data Factory pipeline that ingests data every hour from Azure Blob Storage into Azure Synapse Dedicated SQL Pool. Recently, the pipeline has been failing with timeout errors during the copy activity. The source blob files are around 500 MB each. Which configuration change would MOST effectively reduce the likelihood of timeout errors?

A.Decrease the 'Batch size' for the copy activity.
B.Change the sink to use PolyBase with staging enabled.
C.Increase the Data Integration Unit (DIU) to 8.
D.Enable 'Enable staging' and set 'Degree of copy parallelism' to a higher value.
AnswerD

Increases parallelism, reducing copy time and timeout likelihood.

Why this answer

Enabling staging allows the copy activity to use Azure Blob Storage as an intermediate staging area, which breaks the 500 MB files into manageable chunks and uses parallel staging writes to the Dedicated SQL Pool. This reduces the load on the single copy session and prevents timeout errors by leveraging the staging engine's retry and parallelization capabilities.

Exam trap

The trap here is that candidates often assume increasing DIUs or decreasing batch size will solve timeout issues, but they fail to recognize that staging is specifically designed to handle large file transfers by breaking them into parallel chunks and providing built-in retry logic.

How to eliminate wrong answers

Option A is wrong because decreasing 'Batch size' reduces the number of rows per batch, which can increase the number of round trips and actually worsen timeout issues for large files. Option B is wrong because PolyBase with staging enabled is a valid approach for bulk loading, but the question specifically asks for a configuration change to reduce timeout errors; PolyBase itself does not inherently address timeout errors during the copy activity—it is a different loading method that may still encounter timeouts if not combined with staging. Option C is wrong because increasing Data Integration Units (DIU) to 8 only improves parallelism within the copy activity for file splits, but for a single 500 MB file, the copy activity still processes it as one unit unless staging is enabled to split it; DIU alone does not mitigate timeout errors caused by long-running single-file transfers.

Full explanation →
13
Multi-Selectmedium

Which THREE security features are available for Azure SQL Database? (Choose three.)

Select 3 answers
A.Always On availability groups
B.Auditing
C.Azure Active Directory authentication
D.Firewall rules
E.Transparent Data Encryption (TDE)
AnswersB, C, E

Correct. Auditing tracks database events.

Why this answer

Auditing is a built-in security feature for Azure SQL Database that tracks database events and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. It helps maintain regulatory compliance, understand database activity, and gain insights into discrepancies and anomalies that could indicate security concerns.

Exam trap

The trap here is that candidates often confuse high-availability features like Always On availability groups with security features, or they mistakenly think that firewall rules are a database-level security feature rather than a server-level network access control.

Full explanation →
14
MCQhard

Refer to the exhibit. You have an Azure Data Lake Storage Gen2 account with hierarchical namespace enabled. The exhibit shows an Azure CLI command output that lists access permissions. User user@contoso.com is a member of group-analysts@contoso.com. What level of access does user@contoso.com have to the /data/analytics/ directory?

A.Access denied because of the directory-level reader role
B.Read-only access
C.Read and write access
D.No access
AnswerC

The user's root-level contributor role provides read and write access to all directories.

Why this answer

RBAC permissions are additive. The user has Storage Blob Data Contributor at the root scope, which grants read/write access to all directories. The reader role at the directory scope does not override the broader contributor role.

Option A is wrong because the user has write access via the root scope. Option B is wrong because the user has read access from the root scope already. Option D is wrong because the user has explicit permissions.

Full explanation →
15
MCQhard

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

A.Add more partitions to the Event Hubs.
B.Increase the number of Streaming Units (SUs) for the Stream Analytics job.
C.Replace the output with Azure Functions for each event.
D.Change the input to a reference data input.
AnswerB

More SUs provide more compute power to process events faster.

Why this answer

Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help.

Using Azure Functions for output may add overhead.

Full explanation →
16
Multi-Selecthard

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Select 2 answers
A.Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.
B.Enable Transparent Data Encryption (TDE) on the database.
C.Create a security policy with filter predicates to restrict access to specific columns.
D.Use GRANT SELECT on specific columns to authorized users.
E.Implement Always Encrypted to encrypt columns at the application level.
AnswersA, D

Dynamic data masking hides sensitive data from non-privileged users.

Why this answer

Dynamic data masking obfuscates sensitive columns for unauthorized users at query time, providing column-level security without altering the underlying data. Option D is correct because column-level security in Azure Synapse dedicated SQL pools uses GRANT SELECT on specific columns to authorized users, restricting access to sensitive columns. Option B is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not at the column level.

Option C is incorrect because security policies with filter predicates are used for row-level security, not column-level security. Option E is incorrect because Always Encrypted encrypts columns at the application level, which is separate from column-level security in Synapse.

Full explanation →
17
MCQeasy

You are designing a data processing solution for a real-time dashboard. The source data is streaming from IoT devices at a rate of 10,000 events per second. Each event is under 1 KB. You need to store the raw data in a staging area before processing, and you must minimize storage costs. Which storage solution should you use?

A.Azure Data Lake Storage Gen2
B.Azure Blob Storage
C.Azure SQL Database
D.Azure Cosmos DB
AnswerB

Blob Storage provides low-cost object storage for large volumes of streaming data, ideal for staging before processing.

Why this answer

Azure Blob Storage is cost-effective for large volumes of streaming data that will be processed later. Azure Data Lake Storage Gen2 is built on Blob Storage but adds hierarchical namespace, which is not required for simple staging. Azure SQL Database and Azure Cosmos DB are more expensive and suited for transactional or real-time query workloads.

Full explanation →
18
MCQeasy

You are processing CSV files in Azure Synapse Analytics serverless SQL pool. The files contain newline characters within quoted fields, causing parsing errors. Which file format option should you specify to handle this correctly?

A.Use ROWTERMINATOR = '0x0a'
B.Use FORMAT = 'CSV'
C.Use FIELDQUOTE = '"'
D.Use PARSER_VERSION = '2.0'
AnswerD

Parser version 2.0 supports quoted fields with embedded newlines.

Why this answer

PARSER_VERSION = '2.0' in Azure Synapse serverless SQL pool supports quoted fields that contain embedded newline characters (CR/LF). The parser version 2.0 is designed to handle CSV files with complex quoting rules, including newlines within quoted fields, which the older parser version 1.0 cannot parse correctly.

Exam trap

The trap here is that candidates often assume FIELDQUOTE or ROWTERMINATOR can fix embedded newlines, but only the parser version upgrade (2.0) changes the underlying parsing logic to treat newlines inside quotes as part of the field value.

How to eliminate wrong answers

Option A is wrong because ROWTERMINATOR = '0x0a' only specifies the row delimiter (line feed) and does not address the issue of newline characters inside quoted fields; it would still cause parsing errors when newlines appear within quotes. Option B is wrong because FORMAT = 'CSV' is a general format specification that does not by itself enable handling of embedded newlines in quoted fields; it relies on the parser version for such behavior. Option C is wrong because FIELDQUOTE = '"' defines the quote character (default is double quote) but does not change how the parser treats newlines within quoted fields; the parser version determines that behavior.

Full explanation →
19
Multi-Selecteasy

Which TWO options are correct for configuring a copy activity in Azure Data Factory to load data from Azure Blob Storage to Azure SQL Database?

Select 2 answers
A.Use a staging table in Azure SQL Database before inserting into the final table.
B.Use staging via Azure Blob Storage when loading large volumes to improve performance.
C.Use Azure Data Lake Storage as the staging location for better throughput.
D.Use PolyBase to load directly from Blob Storage to Azure SQL Database.
E.Always skip staging to reduce latency.
AnswersA, B

Staging table allows data validation and transformation before final load.

Why this answer

Correct options are A and B. Option A (using a staging table in Azure SQL Database before inserting into the final table) is a best practice for large data loads, enabling efficient truncate-and-reload or upsert operations. Option B (using staging via Azure Blob Storage when loading large volumes) improves performance by batching and parallelizing the load, reducing timeouts and resource contention.

Option D is incorrect because PolyBase is not supported for Azure SQL Database; it is available only for Azure Synapse Analytics and SQL Server. Option C is incorrect because Azure Data Lake Storage is not the standard staging location for Azure SQL Database; Azure Blob Storage is used. Option E is incorrect because skipping staging can degrade performance for large data volumes.

Full explanation →
20
MCQeasy

You are monitoring an Azure Data Factory pipeline that runs daily to load data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline has been failing intermittently with timeout errors. You need to configure alerts to notify the team when the pipeline fails. What is the most efficient way to achieve this?

A.Enable diagnostic settings to send pipeline logs to a Log Analytics workspace and create a log search alert.
B.Use Azure Service Health alerts to notify when Data Factory is unhealthy.
C.Create an alert rule in Azure Monitor using the 'Pipeline failed runs' metric.
D.Configure Microsoft Defender XDR to monitor pipeline activities.
AnswerC

Azure Data Factory exposes pipeline run metrics in Azure Monitor, allowing direct alert creation based on failures.

Why this answer

The most efficient way to alert on pipeline failures is to create an alert rule in Azure Monitor using the 'Pipeline failed runs' metric (Option C). This metric is specific to Data Factory and allows direct alerting without additional logging setup. Option A is inefficient because it requires enabling diagnostic settings and creating a log search alert, adding latency and cost.

Option B, Azure Service Health alerts, only notify about Azure service health issues, not pipeline failures. Option D, Microsoft Defender XDR, is a security solution unrelated to pipeline monitoring.

Full explanation →
21
MCQmedium

Your organization uses Azure Data Lake Storage Gen2 for a data lake. You need to prevent accidental deletion of data by enabling a soft delete policy. Which configuration is required?

A.Apply an Azure Resource Manager lock.
B.Configure Azure Backup for the storage account.
C.Enable blob versioning.
D.Enable blob soft delete on the storage account.
AnswerD

Soft delete retains deleted blobs for a specified period.

Why this answer

Azure Data Lake Storage Gen2 supports blob soft delete, which protects against accidental deletion by retaining deleted blobs for a specified retention period. Option A is incorrect because Azure Resource Manager locks prevent the deletion or modification of the storage account itself, not the data within it. Option B is incorrect because Azure Backup is designed for backing up VMs, SQL databases, and other workloads, not for managing soft delete of blobs.

Option C is incorrect because blob versioning preserves previous versions of blobs, but it does not prevent deletion of the current version; soft delete is specifically for recovery from accidental deletion.

Full explanation →
22
Multi-Selecthard

You are designing a delta lake architecture in Azure Synapse Analytics. Which TWO practices should you follow to ensure ACID transactions and data consistency?

Select 2 answers
A.Disable schema evolution to prevent accidental schema changes
B.Run OPTIMIZE commands frequently to compact small files
C.Use the Delta Lake transaction log for all write operations
D.Store data in CSV format to simplify schema enforcement
E.Enable write-ahead logging to support concurrent reads and writes
AnswersC, E

The transaction log ensures ACID compliance by recording all operations.

Why this answer

The Delta Lake transaction log is the core mechanism that enables ACID transactions. Every write operation (insert, update, delete, merge) is recorded as an atomic commit in the transaction log, ensuring that concurrent readers see a consistent snapshot and that partial writes are never visible. Without this log, Delta Lake cannot guarantee atomicity or isolation.

Exam trap

The trap here is that candidates often confuse performance optimization (OPTIMIZE) or file format choice (CSV) with ACID transaction guarantees, but the exam specifically tests the understanding that the transaction log is the fundamental enabler of atomicity, consistency, isolation, and durability in Delta Lake.

Full explanation →
23
MCQeasy

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

A.Pivot transformation
B.Aggregate transformation
C.Lookup transformation
D.Derived Column transformation
AnswerD

Derived column can use expressions to split strings.

Why this answer

The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Exam trap

The trap here is that candidates often confuse the Derived Column transformation with the Split transformation (which does not exist in mapping data flows) or mistakenly think the Pivot transformation can re-arrange column data, when in fact Derived Column is the correct choice for column-level string operations.

How to eliminate wrong answers

Option A is wrong because the Pivot transformation is used to rotate data from rows into columns by aggregating values, not for splitting a single column into multiple columns. Option B is wrong because the Aggregate transformation is designed to perform calculations like sum, count, or average over groups of rows, not for row-level string manipulation. Option C is wrong because the Lookup transformation is used to join data from a reference dataset based on a key, not to parse or split column values.

Full explanation →
24
MCQhard

Your team uses Azure Databricks for data processing. You need to implement a cost-control strategy that automatically terminates idle clusters after 30 minutes of inactivity, but allows users to override this policy for specific workloads that require long-running clusters. What is the most efficient approach?

A.Instruct all users to set auto-termination to 30 minutes on each cluster they create.
B.Configure a global auto-termination setting in the Azure Databricks workspace that terminates all clusters after 30 minutes of inactivity.
C.Use Azure Policy to enforce a tag that triggers a function to terminate idle clusters.
D.Create a cluster policy that enforces auto-termination with a default of 30 minutes, but allows users to override the value for specific clusters.
AnswerD

Correct: Cluster policies provide a balance between governance and flexibility, allowing overrides as needed.

Why this answer

Cluster policies in Azure Databricks allow admins to define rules (e.g., auto-termination) while allowing users to choose different values within allowed limits. Option A is wrong because manual termination is not automatic. Option B is wrong because cluster policies are not enforced by Azure Policy.

Option C is wrong because auto-termination is a cluster-level setting, not a global policy.

Full explanation →
25
MCQhard

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

A.Use hash-distributed tables for all tables and clustered columnstore indexes.
B.Use replicated tables for all fact tables and hash-distributed tables for dimension tables.
C.Use round-robin tables for all tables to simplify the migration.
D.Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.
AnswerD

Round-robin minimizes data movement; hash distribution optimizes joins.

Why this answer

It uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often assume hash-distributed tables are always the best choice for all tables, overlooking the fact that round-robin tables reduce data movement during migration and that hash distribution should be reserved for large fact tables to avoid skew and unnecessary shuffling.

How to eliminate wrong answers

Option A is wrong because using hash-distributed tables for all tables, including small dimension tables, can cause unnecessary data shuffling and skew, and clustered columnstore indexes are not optimal for tables with frequent updates due to high overhead in maintaining columnstore segments. Option B is wrong because replicated tables are designed for small dimension tables (typically < 2 GB), not for large fact tables with up to 500 million rows, as replicating such large tables would cause excessive storage and data movement. Option C is wrong because round-robin tables distribute data randomly across distributions, leading to poor query performance due to data movement during joins and aggregations, and are not suitable for production fact tables in a dedicated SQL pool.

Full explanation →
26
MCQmedium

You have an Azure Databricks workspace that processes sensitive data. The security team requires that all access to the workspace be authenticated using Microsoft Entra ID and that all API calls be audited. Which configuration should you implement?

A.Configure workspace to use Microsoft Entra ID authentication and enable diagnostic settings for audit logs.
B.Enable VNet injection and configure network security groups.
C.Deploy Azure Private Link and disable public access.
D.Configure personal access tokens for API access and enable cluster logs.
AnswerA

Entra ID authentication ensures identity-based access, and diagnostic settings capture API call logs.

Why this answer

It directly addresses both requirements: Microsoft Entra ID authentication ensures all access is authenticated via Entra ID, and enabling diagnostic settings captures audit logs for all API calls. Option B (VNet injection) provides network isolation, not authentication or auditing. Option C (Private Link) ensures private connectivity, not authentication or auditing.

Option D (personal access tokens) bypasses Entra ID, and cluster logs do not capture API audit trails.

Full explanation →
27
MCQeasy

Your organization uses Azure Data Factory to orchestrate data pipelines. You need to ensure that sensitive data is not exposed in pipeline logs. What should you configure?

A.Store connection strings in Azure Key Vault.
B.Enable 'Secure output' on pipeline activities.
C.Set a retention policy for pipeline logs.
D.Use data flow debug logs with session logs.
AnswerB

Secure output masks sensitive data in activity logs.

Why this answer

Enabling 'Secure output' on pipeline activities prevents sensitive data from being written to Azure Data Factory logs. Option A is incorrect because while storing connection strings in Azure Key Vault is a security best practice, it does not prevent sensitive data from appearing in pipeline logs. Option C is incorrect because setting a retention policy for logs controls how long logs are kept, but does not prevent sensitive data exposure.

Option D is incorrect because data flow debug logs are for debugging and do not mask sensitive data in pipeline logs.

Full explanation →
28
MCQhard

Refer to the exhibit. A data engineer runs a pipeline copy activity and gets this error. What is the most likely cause of the failure?

A.The destination storage account is inaccessible.
B.The pipeline parameters are missing.
C.The self-hosted integration runtime is not running or unreachable.
D.The source database is offline.
AnswerC

The error code 'IntegrationRuntimeNotReady' directly indicates this.

Why this answer

The error indicates the self-hosted integration runtime is not ready. This typically happens when the IR is not running or has network issues.

Full explanation →
29
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Azure Stream Analytics is optimized for low-latency streaming and can directly ingest from Event Hubs and write to SQL pools.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Exam trap

The trap here is that candidates often confuse 'near real-time' with 'batch processing' and choose Azure Data Factory (option C) because it is a familiar data integration tool, overlooking that it lacks native streaming capabilities and introduces latency from scheduled pipeline runs.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces additional overhead from cluster startup time and micro-batch processing, which typically results in higher latency (seconds to minutes) compared to Stream Analytics' continuous processing model. Option C is wrong because Azure Data Factory is a batch-oriented ETL/ELT orchestration service that does not support native streaming ingestion; it polls sources on a schedule, introducing minutes of latency. Option D is wrong because Azure Functions with Event Hub trigger processes events one at a time in a serverless compute model, which can lead to cold-start delays and lacks built-in windowing, aggregation, and exactly-once semantics for streaming workloads.

Full explanation →
30
Multi-Selecthard

Which THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Select 3 answers
A.A Mapping Data Flow to transform the data before load.
B.A Lookup activity to retrieve the last processed watermark.
C.Azure Functions to process change data.
D.A Copy activity to transfer changed data to Synapse.
E.Change Data Capture (CDC) enabled on the source Azure SQL Database tables.
AnswersB, D, E

Needed to get the last high-water mark.

Why this answer

A Lookup activity is required to retrieve the last processed watermark (e.g., a timestamp or LSN) from a control table or source query. This watermark defines the incremental boundary for CDC, ensuring only new or changed rows are loaded in each pipeline run. Without it, the pipeline would have no way to track progress and would either reload all data or miss changes.

Exam trap

The trap here is that candidates often assume CDC requires a Mapping Data Flow or Azure Functions for transformation or processing, when in fact the core pattern uses only Lookup and Copy activities with change tracking metadata.

Full explanation →
31
MCQmedium

Your company uses Azure Purview for data governance. You need to ensure that sensitive data in Azure Data Lake Storage Gen2 is automatically detected and classified. What should you configure in Purview?

A.Apply sensitivity labels to the storage account using Microsoft Purview Information Protection.
B.Enable Microsoft Defender for Cloud's data sensitivity discovery.
C.Use Azure Policy to enforce tagging of resources containing sensitive data.
D.Create a scan rule set that includes built-in classification rules for sensitive data types.
AnswerD

Purview scanning with classification rules automatically detects sensitive data patterns.

Why this answer

In Microsoft Purview, you can create scan rule sets that include built-in classification rules to automatically detect sensitive data types during scanning. Option A is incorrect because sensitivity labels are applied after classification, not for detection. Option B is incorrect because Microsoft Defender for Cloud's data sensitivity discovery is a different feature; Purview itself handles classification.

Option C is incorrect because Azure Policy enforces compliance rules, not data classification at the file level.

Full explanation →
32
Multi-Selecteasy

Which TWO data processing activities in Azure Data Factory allow you to run custom code?

Select 2 answers
A.Data Flow activity
B.Copy activity
C.Custom activity
D.Azure Function activity
E.Stored Procedure activity
AnswersC, D

Runs custom code on Azure Batch.

Why this answer

The Custom activity in Azure Data Factory allows you to execute custom code by running a .NET or PowerShell script in an Azure Batch service. This enables you to implement complex transformation logic that is not natively supported by ADF's built-in activities, such as custom data validation or integration with third-party APIs.

Exam trap

The trap here is that candidates often confuse the Custom activity with the Azure Function activity, thinking both run custom code, but the question asks for 'custom code' in the context of ADF's native activities—Azure Function activity is also correct because it runs custom code via an Azure Function, making it a valid choice alongside Custom activity.

Full explanation →
33
Matchingmedium

Match each data storage format to its characteristic.

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

Concepts
Matches

Columnar storage format optimized for analytics

Row-based format with schema embedded

Columnar format with high compression

ACID transactions on data lakes

Why these pairings

In Azure data engineering, Parquet and ORC are columnar formats ideal for analytics, while Avro is row-based with embedded schema, and JSON is a semi-structured text format. Common confusions involve mixing columnar and row-oriented characteristics.

Full explanation →
34
MCQeasy

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

A.Use a staging table to load data before transformation
B.Increase the batch size in the data flow
C.Use PolyBase to load data into the dedicated SQL pool
D.Partition the data flow by a key column
AnswerD

Partitioning allows parallel execution across nodes, improving throughput.

Why this answer

Partitioning the data flow by a key column (Option D) enables parallel processing across multiple compute nodes, reducing data shuffling and improving throughput. In Azure Synapse data flows, partitioning distributes the workload evenly, which directly addresses the slow performance and high resource consumption observed in the pipeline.

Exam trap

The trap here is that candidates often confuse data flow optimization with data loading optimization, leading them to select PolyBase (Option C) or staging tables (Option A) instead of recognizing that partitioning directly addresses compute and parallelism issues in the transformation layer.

How to eliminate wrong answers

Option A is wrong because using a staging table adds an extra I/O step and does not inherently optimize the data flow's compute or parallelism; it is typically used for incremental loads or error handling, not performance tuning. Option B is wrong because increasing the batch size in a data flow can actually degrade performance by causing memory pressure and longer processing times per batch, as the default batch size is already optimized for most scenarios. Option C is wrong because PolyBase is a data loading technology for dedicated SQL pools, not a performance optimization for data flows; it addresses data ingestion speed, not the transformation logic within a data flow.

Full explanation →
35
Multi-Selectmedium

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Select 3 answers
A.Pivot
B.Derived Column
C.Union All
D.Aggregate
E.Merge Join
AnswersA, B, D

Used to rotate data from rows to columns.

Why this answer

The Pivot transformation in Mapping Data Flows allows you to rotate data from rows into columns, enabling aggregation and restructuring of datasets. It is a native transformation in Azure Data Factory's Mapping Data Flows, supporting group-by columns, pivot keys, and aggregated column values.

Exam trap

The trap here is that candidates confuse the 'Union All' and 'Merge Join' names from other tools (like SSIS or T-SQL) with the actual transformation names in Azure Data Factory Mapping Data Flows, leading them to select options that sound familiar but are not available.

Full explanation →
36
MCQmedium

You need to design a near-real-time data processing solution that ingests IoT telemetry data from millions of devices. The data must be aggregated per minute and stored in Azure Cosmos DB for low-latency queries. Which Azure service combination should you use?

A.Azure Event Hubs -> Azure HDInsight (Kafka) -> Azure Cosmos DB
B.Azure Event Hubs -> Azure Stream Analytics -> Azure Cosmos DB
C.Azure IoT Hub -> Azure Databricks (Structured Streaming) -> Azure Cosmos DB
D.Azure Event Hubs -> Azure Data Factory -> Azure Cosmos DB
AnswerB

Stream Analytics provides near-real-time aggregation.

Why this answer

Azure Stream Analytics provides native, low-latency windowed aggregation (e.g., TumblingWindow for per-minute aggregates) directly on data ingested from Event Hubs, and it has a built-in output sink to Azure Cosmos DB. This combination meets the near-real-time requirement without needing an intermediate compute or storage layer, minimizing end-to-end latency.

Exam trap

The trap here is that candidates often over-engineer the solution by adding a big-data processing layer (like HDInsight or Databricks) when a simpler, fully managed stream analytics service (Azure Stream Analytics) is the correct choice for fixed-window aggregation and direct Cosmos DB output.

How to eliminate wrong answers

Option A is wrong because Azure HDInsight (Kafka) introduces unnecessary complexity and latency for simple per-minute aggregation; it requires manual stream processing setup and is not optimized for direct, low-latency output to Cosmos DB. Option C is wrong because Azure Databricks Structured Streaming, while capable, adds startup and cluster management overhead that is not ideal for sub-minute latency, and IoT Hub is typically used for device management and bi-directional communication, not purely for high-throughput telemetry ingestion. Option D is wrong because Azure Data Factory is a batch-oriented orchestration service, not designed for near-real-time stream processing or windowed aggregation.

Full explanation →
37
Multi-Selecthard

Your Azure Synapse Analytics dedicated SQL pool is experiencing high resource utilization. You need to identify queries that are consuming excessive memory. Which THREE DMVs should you query?

Select 3 answers
A.sys.dm_pdw_sql_requests
B.sys.dm_pdw_nodes_resource_usage
C.sys.dm_pdw_request_steps
D.sys.dm_pdw_exec_requests
E.sys.dm_pdw_waits
AnswersA, C, D

Correct – sys.dm_pdw_sql_requests shows distributed SQL requests, which include memory usage details per distribution.

Why this answer

To identify queries consuming excessive memory in Azure Synapse Dedicated SQL pool, you can query sys.dm_pdw_exec_requests to view all requests, sys.dm_pdw_request_steps to view the steps of requests, and sys.dm_pdw_sql_requests to view the distributed SQL requests. Option B (sys.dm_pdw_nodes_resource_usage) shows node-level resource usage, and Option E (sys.dm_pdw_waits) shows waiting queries, but neither directly identifies memory-intensive queries.

Full explanation →
38
MCQeasy

You need to monitor the performance of your Azure Synapse Analytics dedicated SQL pool. Which metric should you use to identify queued queries due to concurrency limits?

A.Queued queries
B.DWU percentage
C.Active queries
D.Memory percentage
AnswerA

This metric directly shows queries waiting for concurrency slots.

Why this answer

'Queued queries' metric directly shows the number of queries waiting due to concurrency limits. Option B is wrong because DWU percentage shows resource usage, not queuing. Option C is wrong because active queries show running queries.

Option D is wrong because memory percentage shows memory pressure.

Full explanation →
39
MCQmedium

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

A.The copy activity is not using staging and PolyBase
B.The source and sink are in different Azure regions
C.The Data Integration Unit (DIU) setting is too low
D.The source files are compressed
AnswerA

PolyBase dramatically improves load performance.

Why this answer

The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Exam trap

The trap here is that candidates often assume DIU settings are the primary performance lever, but for Synapse sinks, the staging/PolyBase mechanism is the critical factor that can improve performance by orders of magnitude.

How to eliminate wrong answers

Option B is wrong because the question explicitly states that the source and sink are in the same region, so cross-region latency is not the issue. Option C is wrong because Data Integration Units (DIUs) control parallelism within the Copy activity, but even with maximum DIUs, the row-by-row insert into Synapse is the bottleneck, not the DIU setting. Option D is wrong because compressed source files can actually improve performance by reducing network transfer time, and ADF can decompress them efficiently; compression is not inherently a cause of poor copy performance.

Full explanation →
40
MCQmedium

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that queries are experiencing excessive wait time due to concurrency slots being exhausted. What is the recommended approach to improve concurrency without increasing cost?

A.Create additional workload groups and assign queries to them.
B.Classify queries using workload classification and assign lower importance to reduce concurrency slot usage.
C.Scale up the dedicated SQL pool to a higher service level.
D.Change the distribution type of tables to round-robin.
AnswerB

Lower importance queries use fewer slots, allowing more concurrent queries.

Why this answer

By classifying queries with workload classification and assigning lower importance to background or non-critical queries, those queries consume fewer concurrency slots, freeing slots for higher-priority queries without additional cost. Option A is incorrect because workload groups manage resources but do not directly reduce per-query concurrency slot usage; they allocate resources among groups but total concurrency slots remain fixed. Option C is incorrect because scaling up increases cost without necessarily improving concurrency efficiency.

Option D is incorrect because table distribution type (e.g., round-robin) affects query performance and data movement, not concurrency slot consumption.

Full explanation →
41
MCQmedium

Your company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You need to monitor query performance and identify queries that are consuming excessive resources. Which Azure tool should you use?

A.Azure Monitor Metrics for the serverless SQL pool.
B.Synapse Studio monitoring hub.
C.Azure Log Analytics queries against diagnostic logs.
D.SQL Server Management Studio Query Performance Insight.
AnswerB

Synapse Studio provides detailed monitoring for serverless SQL pool.

Why this answer

Synapse Studio monitoring hub provides built-in dashboards for serverless SQL pool queries, showing execution details, resource consumption, and query performance. This is the most direct and integrated tool for monitoring serverless SQL pool queries in Azure Synapse Analytics. Azure Monitor Metrics provides pool-level metrics but not query-level details.

Azure Log Analytics can be used with diagnostic settings to analyze logs, but it requires additional configuration and is not the primary tool for ad-hoc query monitoring. SQL Server Management Studio Query Performance Insight is designed for dedicated SQL pools, not serverless.

Full explanation →
42
Multi-Selecthard

Which THREE of the following are valid ways to authenticate to Azure Data Lake Storage Gen2 from Azure Synapse serverless SQL pool?

Select 3 answers
A.Storage account key
B.Managed identity of the Synapse workspace
C.Service principal with certificate
D.Microsoft Entra ID (Azure AD) pass-through authentication
E.Shared access signature (SAS) token
AnswersB, D, E

Serverless SQL pool can use the workspace's managed identity to access storage.

Why this answer

Managed identity of the Synapse workspace (Option B) is correct because Azure Synapse serverless SQL pool can use the workspace's system-assigned managed identity to authenticate to Azure Data Lake Storage Gen2 without storing any credentials. This is enabled by granting the managed identity the appropriate RBAC role (e.g., Storage Blob Data Contributor) on the storage account, and then using the `IDENTITY='Managed Identity'` parameter in the `OPENROWSET` or `CREATE EXTERNAL TABLE AS SELECT` statements. It provides a secure, credential-free authentication method that is fully supported by serverless SQL pool.

Exam trap

The trap here is that candidates often assume storage account keys (Option A) are universally supported across all Azure Synapse components, but serverless SQL pool explicitly does not support key-based authentication, requiring token-based methods instead.

Full explanation →
43
MCQhard

Refer to the exhibit. You are reviewing an Azure Policy definition that was created to audit diagnostic settings for Azure Data Lake Storage Gen2. The policy is assigned to a management group containing several storage accounts. You notice that the policy is not flagging storage accounts that lack diagnostic settings. What is the most likely cause?

A.The existence condition incorrectly checks for a specific log category 'StorageRead' instead of any diagnostic setting.
B.The 'field' property in the 'if' condition should be 'type' equals 'Microsoft.Storage/storageAccounts/blobServices' for Data Lake.
C.The effect parameter is not assigned a value; the default 'AuditIfNotExists' is not applied.
D.The policy type is 'Custom', which requires special permissions to evaluate.
AnswerA

The policy should audit if no diagnostic setting exists, not check for a specific category.

Why this answer

The existence condition checks for a specific log category 'StorageRead' rather than checking for any diagnostic setting. Azure Policy for auditing diagnostic settings should verify that at least one diagnostic setting exists, not that a particular category is configured. This causes storage accounts with diagnostic settings that do not include 'StorageRead' (or with no settings at all) to not be flagged.

Option B is incorrect because the 'field' property 'type' with value 'Microsoft.Storage/storageAccounts' is appropriate for auditing diagnostic settings on the storage account level, not blobServices. Option C is incorrect because the effect parameter defaults to 'AuditIfNotExists' even without explicit assignment, so that is not the issue. Option D is incorrect because custom policy type does not require special permissions beyond those needed to assign policies; evaluation occurs normally.

Full explanation →
44
Multi-Selecthard

Which TWO options are correct about using Azure Stream Analytics for real-time data processing?

Select 2 answers
A.You can join streaming data with static reference data for enrichment.
B.Stream Analytics supports windowing functions like Tumbling, Hopping, and Sliding windows natively.
C.Stream Analytics can output to any Azure data store without custom code.
D.Stream Analytics guarantees exactly-once processing semantics.
E.You can use custom JavaScript or C# functions for complex transformations.
AnswersA, B

Reference data joins are a key feature of Stream Analytics.

Why this answer

Correct: A (reference data joins for enrichment) and B (windowing functions for time-based aggregations). Option C is wrong because while Stream Analytics supports many output sinks, not all Azure data stores are supported out-of-the-box; some require custom code or connectors. Option D is wrong because Stream Analytics provides at-least-once processing semantics, not exactly-once; exactly-once depends on the sink configuration and output adapter.

Option E is incorrect because Stream Analytics natively supports custom JavaScript functions for complex transformations, but it does not natively support C# functions; C# functions require integration with Azure Functions, making this option misleading.

Full explanation →
45
MCQeasy

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

A.The job is using a different runtime that does not support Python.
B.The file is too large for DBFS.
C.The job cluster does not have permission to access DBFS.
D.The file was uploaded to the workspace filesystem, not to DBFS.
AnswerD

Workspace files are not automatically in DBFS.

Why this answer

The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path.

Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue.

Option C is incorrect because the job cluster automatically has permission to access DBFS.

Full explanation →
46
MCQhard

A healthcare organization stores patient data in Azure SQL Database. They need to encrypt sensitive columns (e.g., SSN) such that only authorized users can decrypt the data at query time. Which feature should they use?

A.Transparent Data Encryption (TDE)
B.Azure Information Protection labels
C.Always Encrypted (with secure enclaves)
D.Dynamic Data Masking
AnswerC

Column-level encryption, decrypted on the client.

Why this answer

Always Encrypted with secure enclaves is the correct choice because it allows sensitive columns (e.g., SSN) to be encrypted at rest and in memory, with decryption occurring only on the client side using keys that are never exposed to the database engine. This ensures that only authorized users with the proper encryption keys can decrypt the data at query time, meeting the requirement for column-level encryption with client-side key management.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with column-level encryption, mistakenly thinking TDE protects individual columns at query time, when in fact TDE only encrypts data at rest and does not control who can decrypt data during queries.

How to eliminate wrong answers

Option A is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (pages and backups) but does not protect data in memory or during query execution, and it does not allow column-level granularity or client-side key control. Option B is wrong because Azure Information Protection labels are used for classifying and protecting documents and emails via sensitivity labels, not for encrypting specific database columns at query time. Option D is wrong because Dynamic Data Masking obfuscates data in query results for unauthorized users but does not encrypt the underlying data; it can be bypassed by users with direct database access or by using certain query patterns.

Full explanation →
47
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by date. As data grows, query performance on recent data degrades. You need to improve performance for queries filtering on the current month without affecting queries on older data. What should you do?

A.Implement partition switching to move older partitions to a different filegroup with slower storage
B.Increase the service level objective (SLO) of the dedicated SQL pool
C.Change the table distribution to round-robin
D.Update statistics on the table for the current month partition
AnswerA

Partition switching is correct because it allows moving older partitions to a different filegroup, reducing the data scanned for recent queries without affecting older data. It is a metadata-only operation.

Why this answer

Partition switching allows you to efficiently move older partitions to a different filegroup (e.g., slower or compressed storage) without affecting queries on recent data. This reduces the active data volume in the fact table, improving scan performance for queries filtering on the current month. The operation is metadata-only and does not require data movement, so it does not impact ongoing queries on the remaining partitions.

Exam trap

The trap here is that candidates often confuse partition switching with partition elimination or index maintenance, thinking that updating statistics or scaling resources will solve the performance issue, when the real bottleneck is the sheer volume of data in the table that must be scanned for queries on recent data.

How to eliminate wrong answers

Option B is wrong because increasing the SLO (service level objective) scales up resources for the entire dedicated SQL pool, which is costly and does not specifically target the performance degradation on recent data; it also affects queries on older data unnecessarily. Option C is wrong because changing the table distribution to round-robin would distribute data evenly across distributions, which can improve load performance but does not address the performance issue caused by scanning a large fact table partitioned by date; it may even worsen query performance for date-filtered queries by spreading data across all distributions. Option D is wrong because updating statistics on the current month partition alone does not reduce the amount of data scanned; while fresh statistics help the optimizer choose better plans, the fundamental problem is the volume of data, not the statistics quality.

Full explanation →
48
MCQmedium

You are designing a data pipeline in Azure Data Factory that reads data from an Azure SQL Database and writes to Azure Synapse Analytics dedicated SQL pool. The pipeline will run hourly and process incremental changes. You need to ensure that the pipeline handles retries for transient failures and logs all activities for audit purposes. What should you configure?

A.Enable auditing on the Azure SQL Database and Azure Synapse SQL pool.
B.Configure a tumbling window trigger to rerun the pipeline on failure.
C.Enable pipeline logging to an Azure Storage account and set retry policy on copy activity.
D.Use an Azure Monitor alert to notify on failures.
AnswerC

Logs capture all events; retry handles transient errors.

Why this answer

Enabling pipeline logging to an Azure Storage account captures all activity for audit purposes, and setting a retry policy on the copy activity handles transient failures. Option A is incorrect because Azure SQL Database and Synapse SQL pool auditing logs database access, not pipeline activity. Option B is incorrect because a tumbling window trigger schedules runs at intervals but does not log activities or handle retries by itself; retries must be configured separately.

Option D is incorrect because an Azure Monitor alert notifies on failures but does not log pipeline activities or provide automated retries.

Full explanation →
49
MCQhard

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

A.Create a nonclustered index on the date column.
B.Create a clustered columnstore index on the table.
C.Change the distribution to round-robin.
D.Partition the table on the date column.
AnswerD

Enables partition pruning, reducing data scanned.

Why this answer

Partitioning the table on the date column enables partition elimination, which allows the query to scan only the relevant partitions instead of the entire table. Option A is incorrect because a nonclustered index on the date column can help with point lookups but is less effective for large scans and filtering on a range of dates. Option B is incorrect because a clustered columnstore index improves compression and scan performance but does not provide partition elimination for date filtering.

Option C is incorrect because round-robin distribution distributes data evenly but does not optimize for filtering on a specific column.

Full explanation →
50
Multi-Selectmedium

Which TWO Azure services can be used to monitor and analyze query performance in Azure Synapse Analytics dedicated SQL pool?

Select 2 answers
A.SQL Data Sync
B.Azure Policy
C.Azure Advisor
D.Azure Monitor with Log Analytics
E.Dynamic Management Views (DMVs)
AnswersD, E

Can collect and analyze query performance logs.

Why this answer

The correct answers are D and E. Azure Monitor with Log Analytics collects and analyzes telemetry data, including query performance metrics from Azure Synapse Analytics. Dynamic Management Views (DMVs) provide detailed insights into query execution statistics, such as wait types and resource usage.

Option A (SQL Data Sync) is for data synchronization across databases, not monitoring. Option B (Azure Policy) is for governance and compliance, not performance analysis. Option C (Azure Advisor) offers optimization recommendations but not real-time monitoring.

Full explanation →
51
MCQmedium

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to implement a least-privilege security model. Which authorization mechanism should you use for granular control?

A.Use storage account keys for access.
B.Use Azure RBAC roles at the storage account level.
C.Use POSIX-like access control lists (ACLs).
D.Use shared access signatures (SAS) with stored access policies.
AnswerC

ACLs provide granular permissions on files and directories.

Why this answer

POSIX-like ACLs provide granular permissions at the file and directory level, enabling least-privilege access control. Option A is incorrect because storage account keys grant full access to the entire storage account, violating least privilege. Option B is incorrect because Azure RBAC roles at the storage account level offer coarse-grained control, not granular file/directory permissions.

Option D is incorrect because shared access signatures with stored access policies provide time-limited access but lack the detailed ACL capabilities for individual files and directories.

Full explanation →
52
MCQhard

You are a data engineer for a gaming company that uses Azure Data Lake Storage Gen2. The data lake stores player event data in JSON format. The data is organized by date and event type. The analytics team frequently runs queries that filter by player ID to analyze individual player behavior. These queries are slow because they scan entire daily partitions. You need to improve the performance of queries that filter by player ID without restructuring the entire data lake. The data is stored as JSON files. What should you do?

A.Compress the JSON files using gzip to reduce I/O
B.Convert the data from JSON to Parquet format and partition by player ID
C.Create indexes on the player ID field in the data lake
D.Repartition the data by hour to reduce the data scanned per partition
AnswerB

Parquet allows predicate pushdown and column pruning, speeding up player ID queries.

Why this answer

Converting JSON to Parquet enables columnar storage, which significantly reduces I/O by reading only the columns needed for queries. Partitioning by player ID further improves performance by allowing partition elimination, so queries filter only the relevant partitions instead of scanning entire daily partitions. This approach directly addresses the slow queries without restructuring the entire data lake.

Exam trap

The trap here is that candidates often think compression alone (Option A) or indexing (Option C) can solve performance issues in a data lake, but Azure Data Lake Storage Gen2 does not support file-level indexes, and compression does not change the fundamental row-scanning nature of JSON queries.

How to eliminate wrong answers

Option A is wrong because compressing JSON with gzip reduces file size but does not change the row-oriented storage format; queries still must read and decompress entire files, and filtering by player ID still requires scanning all rows in each partition. Option C is wrong because Azure Data Lake Storage Gen2 does not support indexing on data files; indexes are a database concept and cannot be applied to files in a data lake. Option D is wrong because repartitioning by hour would create more partitions but does not help queries that filter by player ID; the queries would still scan all partitions unless they also filter by hour, and the player ID is the primary filter, not the time granularity.

Full explanation →
53
MCQhard

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

A.Check the input data schema and ensure it matches the query
B.Change the output to a different sink
C.Increase the number of Streaming Units (SUs)
D.Set the watermark delay threshold higher
AnswerA

Deserialization errors are often due to schema mismatch; fixing the data or query resolves the root cause.

Why this answer

Input deserialization errors indicate that the incoming data format does not match the schema defined in the Stream Analytics query. Increasing watermark delay is a symptom of this mismatch, as the job cannot parse events correctly and falls behind. Checking and aligning the input schema with the query is the first and most direct troubleshooting step.

Exam trap

The trap here is that candidates often assume increasing resources (SUs) or adjusting thresholds will fix performance issues, when the real cause is a data format mismatch that prevents any processing from succeeding.

How to eliminate wrong answers

Option B is wrong because changing the output sink does not address the root cause of deserialization errors or watermark delay; the issue is with input parsing, not output destination. Option C is wrong because increasing Streaming Units (SUs) can improve throughput but will not fix schema mismatches that cause deserialization errors; it may even mask the underlying problem. Option D is wrong because raising the watermark delay threshold only hides the symptom by allowing more lateness, but does not resolve the input parsing failures that are generating the errors.

Full explanation →
54
MCQhard

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

A.Increase the maximum query concurrency limit
B.Provision a dedicated SQL pool with more DTUs
C.Create a clustered columnstore index on the date column
D.Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*)
AnswerD

Partition elimination reduces data scanned.

Why this answer

Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Exam trap

The trap here is that candidates often confuse serverless SQL pool with dedicated SQL pool and incorrectly choose to create indexes or scale resources, not realizing that serverless SQL pool relies on external data partitioning and file-skipping techniques rather than internal indexing or provisioning.

How to eliminate wrong answers

Option A is wrong because increasing the maximum query concurrency limit does not improve the performance of a single query; it only allows more concurrent queries to run, which could even degrade individual query performance due to resource contention. Option B is wrong because provisioning a dedicated SQL pool with more DTUs increases costs and is not a serverless SQL pool feature; serverless SQL pool scales automatically and does not use DTUs, so this would be an expensive and incorrect solution. Option C is wrong because clustered columnstore indexes are not supported in serverless SQL pool; they are a feature of dedicated SQL pools, and creating one on a date column in a serverless context is not possible.

Full explanation →
55
MCQhard

You are reviewing a Mapping Data Flow in Azure Data Factory that copies data from a CSV file to an Azure Synapse table. The data flow uses 'allowSchemaDrift: true' and 'validateSchema: false'. After running the pipeline, you notice that the target table does not have the expected columns. The CSV file sometimes has extra columns. What is the most likely reason?

A.Schema drift is not enabled on the source.
B.The sink mapping is explicitly defined and does not include auto-mapping for drifted columns.
C.The sink dataset has a fixed schema that does not allow drift.
D.The source dataset has a fixed schema that does not include extra columns.
AnswerB

The sink script defines input columns, so extra columns are not mapped.

Why this answer

When 'allowSchemaDrift' is enabled on the source, drifted columns are detected but will only be written to the sink if auto-mapping is used. If the sink mapping is explicitly defined (e.g., column-by-column mappings), it overrides auto-mapping and drifted columns are ignored. Since the target table is missing expected columns, the explicit mapping likely excludes the drifted columns.

Exam trap

The trap here is that candidates assume enabling 'allowSchemaDrift' on the source automatically writes all columns to the sink, but they overlook that explicit sink mappings override auto-mapping and exclude drifted columns.

How to eliminate wrong answers

Option A is wrong because the question states 'allowSchemaDrift: true' on the source, so schema drift is enabled. Option C is wrong because the sink dataset's schema does not control drift behavior at runtime; the sink's 'allowSchemaDrift' and mapping settings in the data flow determine whether drifted columns are written. Option D is wrong because the source dataset's schema does not prevent drift detection; the 'allowSchemaDrift' setting on the source overrides the dataset schema, and the CSV file's extra columns are still read as drifted columns.

Full explanation →
56
Multi-Selecthard

You are developing a data processing pipeline in Azure Data Factory. The pipeline uses a mapping data flow to transform data. You need to ensure that the data flow can handle schema drift from the source. Which THREE of the following actions should you take? (Select THREE.)

Select 3 answers
A.Use a 'Select' transformation to explicitly choose only the known columns.
B.Enable 'Allow schema drift' in the source settings.
C.Set 'Ignore drift column types' in the sink settings to avoid type mismatch errors.
D.Use 'Column pattern' transformations to apply transformations to drifted columns.
E.Add a 'Derived Column' transformation for every possible column that might be added.
AnswersB, C, D

This allows the data flow to accept additional columns.

Why this answer

Enabling 'Allow schema drift' in the source settings of a mapping data flow tells Azure Data Factory to accept incoming columns that are not defined in the source schema. This is essential for handling schema drift, as it prevents the pipeline from failing when new columns appear in the source data.

Exam trap

The trap here is that candidates often think they must explicitly define every column or use static transformations to handle schema drift, when in fact Azure Data Factory's mapping data flow provides dynamic mechanisms like 'Allow schema drift', column patterns, and drift type handling to automatically accommodate unknown columns.

Full explanation →
57
Multi-Selecthard

A company uses Azure Databricks to process streaming data from Event Hubs. The data is written to a Delta table. The job occasionally fails due to checkpoint corruption. Which THREE measures should you implement to improve reliability?

Select 3 answers
A.Configure checkpointing to a durable storage like Azure Data Lake Storage.
B.Increase the batch interval to reduce load.
C.Increase the cluster size to handle spikes.
D.Use Structured Streaming with `failOnDataLoss` set to false.
E.Implement a retry policy with exponential backoff for transient failures.
AnswersA, D, E

Durable storage prevents corruption.

Why this answer

Checkpointing to durable storage like Azure Data Lake Storage (ADLS) ensures that streaming progress metadata is persisted across cluster restarts and failures. ADLS provides high durability and availability, preventing checkpoint corruption that can occur with local or ephemeral storage, thereby enabling exactly-once processing guarantees in Structured Streaming.

Exam trap

The trap here is that candidates may confuse scaling solutions (increasing cluster size or batch interval) with reliability mechanisms, failing to recognize that checkpoint durability and data loss tolerance are the core mitigations for corruption and streaming failures.

Full explanation →
58
MCQhard

A company uses Azure Data Lake Storage Gen2 with Azure Databricks. They notice that the job to write data into Delta Lake tables takes too long. The data is coming from a streaming source with a high velocity of small writes. Which approach should be taken to optimize write performance?

A.Configure the streaming to write in micro-batches with a higher trigger interval.
B.Increase the cluster size to 16 nodes.
C.Enable 'auto optimize' and 'optimized writes' on the Delta table.
D.Change the output format from Delta to Parquet.
AnswerA

Batching reduces the number of small file writes.

Why this answer

Increasing the trigger interval for micro-batches reduces the frequency of writes, allowing more data to accumulate per batch. This minimizes the overhead of small file commits and metadata operations in Delta Lake, which is the primary bottleneck for high-velocity streaming writes. By batching more records together, the job writes fewer, larger files, improving overall throughput.

Exam trap

The trap here is that candidates often choose 'auto optimize' and 'optimized writes' (Option C) thinking they solve small file problems proactively, but these features are reactive compaction mechanisms that add overhead and do not reduce the frequency of log commits during streaming.

How to eliminate wrong answers

Option B is wrong because simply increasing cluster size to 16 nodes does not address the root cause of small file overhead; it may even exacerbate the problem by creating more concurrent writers producing even smaller files. Option C is wrong because 'auto optimize' and 'optimized writes' are designed to compact small files after they are written, but they do not prevent the initial overhead of many small writes during streaming; they add post-processing cost and latency. Option D is wrong because changing the output format from Delta to Parquet removes ACID transactions, schema enforcement, and time travel capabilities, and does not solve the small file problem—Parquet still suffers from the same small file overhead without the benefits of Delta Lake.

Full explanation →
59
MCQmedium

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source has a fact table with 500 million rows and several dimension tables. You need to choose the best distribution strategy for the fact table to minimize data movement during joins. Which distribution type should you use?

A.Hash distribution on the foreign key column used in joins
B.No distribution (single distribution)
C.Replicated distribution
D.Round-robin distribution
AnswerA

Hash distribution on the join key ensures rows with the same key are on the same distribution.

Why this answer

Hash distribution on the foreign key column used in joins ensures that rows with the same join key are co-located on the same distribution node. This minimizes data movement because the join can be performed locally on each node without shuffling data across the compute nodes, which is critical for a 500-million-row fact table.

Exam trap

The trap here is that candidates often confuse replicated distribution as a general performance booster, but they fail to recognize that replicating a large fact table is impractical and that hash distribution on the join key is the correct strategy to minimize data movement for large fact tables.

How to eliminate wrong answers

Option B is wrong because single distribution (no distribution) places all data on one node, causing a bottleneck and eliminating the parallelism benefits of Azure Synapse Analytics, leading to poor performance for large fact tables. Option C is wrong because replicated distribution copies the entire table to each node, which is impractical for a 500-million-row fact table due to excessive storage and maintenance overhead; it is suitable only for smaller dimension tables. Option D is wrong because round-robin distribution distributes rows evenly without considering join keys, so joins require data to be shuffled across nodes, causing significant data movement and slower query performance.

Full explanation →
60
MCQmedium

You are designing a data pipeline that ingests JSON files from Azure Blob Storage into Azure Synapse Analytics using PolyBase. The files contain nested JSON arrays. What should you do to ensure that the data is loaded correctly?

A.Flatten the JSON arrays into a tabular format using Azure Data Factory or Databricks before loading.
B.Create an external table with the JSON file type and use a schema definition.
C.Use the OPENJSON function in T-SQL to parse the JSON during the load.
D.Use PolyBase with a JSON format file specifying the schema.
AnswerA

PolyBase requires tabular data, so flattening is necessary.

Why this answer

PolyBase in Azure Synapse Analytics cannot directly handle nested JSON arrays; it requires a flat, tabular structure. Azure Data Factory or Databricks can flatten the nested arrays into rows and columns before loading, ensuring compatibility with PolyBase's external table format.

Exam trap

The trap here is that candidates assume PolyBase can handle any JSON structure natively, but it only supports flat JSON files, and they overlook the need for pre-processing nested arrays with tools like Data Factory or Databricks.

How to eliminate wrong answers

Option B is wrong because creating an external table with a JSON file type in PolyBase only works for simple, flat JSON files, not nested arrays; it will fail or produce incorrect results. Option C is wrong because OPENJSON is a T-SQL function used for parsing JSON within a query, but it cannot be used directly in a PolyBase load operation; it would require loading the entire file first, defeating PolyBase's purpose. Option D is wrong because PolyBase does not support a JSON format file for schema specification; it uses format files only for delimited text files (e.g., CSV), not JSON.

Full explanation →
61
MCQhard

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

A.storageAccountType
B.maxSizeBytes
C.collation
D.sku.name
AnswerD

The sku name specifies the Data Warehouse Unit (DWU) level, e.g., DW100c or DW200c.

Why this answer

Sku.name. In an Azure Synapse Analytics dedicated SQL pool, the performance level (e.g., DW200c) is defined by the 'sku.name' parameter in the ARM template. Option A (storageAccountType) controls data redundancy, not performance.

Option B (maxSizeBytes) sets the maximum storage size. Option C (collation) determines the default collation for the database, which affects language support. Therefore, to adjust performance to DW200c, modify the sku.name parameter.

Full explanation →
62
Multi-Selecthard

You are designing a data processing solution that must handle both batch and stream processing in a unified manner. You need to choose a technology that supports a single code base for both processing modes. Which TWO technologies meet this requirement?

Select 2 answers
A.Azure Databricks with Delta Live Tables
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure SQL Database with Change Tracking
E.Apache Spark Structured Streaming in Azure Synapse
AnswersA, E

Delta Live Tables supports both batch and streaming with a unified declarative pipeline.

Why this answer

Azure Databricks with Delta Live Tables (DLT) supports both batch and stream processing using a single code base by allowing you to define streaming and batch data sources declaratively in the same pipeline. DLT automatically handles incremental processing for streaming and full refreshes for batch, enabling unified logic without separate code paths.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics as a unified solution because it handles streaming, but they overlook that it lacks native batch processing support, whereas Apache Spark Structured Streaming (in Databricks or Synapse) truly enables a single code base for both modes.

Full explanation →
63
Multi-Selecthard

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Select 3 answers
A.Avoid using clustered columnstore indexes on large tables.
B.Avoid data skew by choosing a good distribution key.
C.Use round-robin distribution for all large fact tables.
D.Use replicated tables for small dimension tables (less than 1 GB).
E.Use hash distribution on a column with high cardinality for large fact tables.
AnswersB, D, E

Data skew can degrade performance.

Why this answer

A good distribution key minimizes data skew, ensuring that data is evenly distributed across all distributions. This prevents performance bottlenecks where some distributions handle a disproportionate amount of data or queries, which is critical for parallel processing in a dedicated SQL pool.

Exam trap

The trap here is that candidates often assume clustered columnstore indexes are unsuitable for large tables due to memory constraints, but they are actually the default and recommended index type for fact tables in Synapse dedicated SQL pools.

Full explanation →
64
MCQmedium

You are monitoring an Azure Synapse Analytics dedicated SQL pool using dynamic management views (DMVs). You want to identify queries that are experiencing excessive memory grants causing concurrency issues. Which DMV should you query?

A.sys.dm_pdw_waits
B.sys.dm_pdw_exec_requests
C.sys.dm_pdw_resource_waits
D.sys.dm_pdw_workload_management_workload_groups_details
AnswerB

Correct. sys.dm_pdw_exec_requests includes columns for memory grants, allowing identification of memory-heavy queries.

Why this answer

(sys.dm_pdw_exec_requests) is correct because it contains the total_request_memory_granted column, which can be used to identify queries with excessive memory grants contributing to concurrency issues. Option A (sys.dm_pdw_waits) only shows wait types, not memory grants. Option C (sys.dm_pdw_resource_waits) shows resource wait statistics but not actual grant amounts.

Option D (sys.dm_pdw_workload_management_workload_groups_details) provides workload group configuration and metrics, not per-query memory grant details.

Full explanation →
65
Multi-Selecteasy

Which THREE best practices should be followed when designing a data lake in Azure Data Lake Storage Gen2 for optimal performance?

Select 3 answers
A.Disable hierarchical namespace to improve performance.
B.Use a deep directory structure with many subfolders.
C.Use Parquet file format for analytics workloads.
D.Use a naming convention that avoids special characters and high cardinality.
E.Partition data by date to enable partition elimination.
AnswersC, D, E

Parquet is columnar and efficient for analytics.

Why this answer

Options C, D, and E are correct. Parquet format is columnar and provides efficient compression and query performance. Partitioning by date allows partition elimination, reducing data scanned.

A naming convention that avoids special characters and high cardinality prevents throttling and improves parallelism. Option A is incorrect because disabling hierarchical namespace is not a best practice; hierarchical namespace enables folder-level security and is required for ADLS Gen2. Option B is incorrect because a deep directory structure can cause performance issues due to metadata overhead; a flat or shallow structure is recommended.

Full explanation →
66
MCQmedium

You are using Azure Data Factory to load data from an on-premises SQL Server to Azure Synapse Analytics. The data size is 500 GB, and you need to minimize the load time. The network bandwidth is limited. Which approach should you use?

A.Use a self-hosted integration runtime and copy directly to Synapse.
B.Use a self-hosted integration runtime and stage the data in Azure Blob Storage before loading to Synapse.
C.Use Azure ExpressRoute to improve network bandwidth.
D.Use an Azure integration runtime and copy to Azure Data Lake Storage, then to Synapse.
AnswerB

Staging improves performance by splitting the copy into two phases.

Why this answer

Using a self-hosted integration runtime with staging via Azure Blob Storage allows you to stage the data in Azure before loading to Synapse, which is faster over limited bandwidth. Option A (copy directly) is slower due to the bandwidth limitation. Option C (Azure ExpressRoute) requires additional setup and cost, but the question asks for the approach that minimizes load time with limited bandwidth, and staging is the most efficient.

Option D (Azure Data Lake Storage) is similar to staging but not as optimized for the scenario.

Full explanation →
67
Multi-Selectmedium

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Select 2 answers
A.Delta Lake provides time travel capabilities for accessing historical data versions.
B.Parquet is easier to implement for schema evolution than Delta Lake.
C.Delta Lake reduces storage costs by automatically compressing data.
D.Delta Lake supports ACID transactions, ensuring data consistency during concurrent writes.
E.Parquet files are not natively supported by Azure Databricks.
AnswersA, D

Delta Lake's time travel feature allows querying previous versions of data.

Why this answer

Delta Lake's time travel feature allows querying previous versions of data using a timestamp or version number, which is essential for auditing, rollback, and reproducing historical reports. This capability is built on the transaction log that tracks every change, making it a key differentiator from plain Parquet files.

Exam trap

The trap here is that candidates assume Parquet is not natively supported in Databricks, but in reality, Parquet is the default storage format for Delta Lake and is fully supported; the key differentiators are ACID transactions and time travel, not format compatibility.

Full explanation →
68
MCQeasy

You are running an Azure Stream Analytics job that reads from an Event Hub and writes to a Power BI dataset. The job is falling behind and processing latency is increasing. What should you do to improve performance?

A.Increase the number of Streaming Units (SUs) allocated to the job.
B.Use a reference data input to filter events.
C.Change the output to Azure Blob Storage instead of Power BI.
D.Decrease the size of events sent to the Event Hub.
AnswerA

More SUs increase processing capacity.

Why this answer

Increasing the number of Streaming Units (SUs) allocates more compute resources to the Azure Stream Analytics job, directly improving throughput and reducing processing latency. Option B (use reference data) does not address throughput; it is used for enriching streams with static data. Option C (change output to Blob Storage) avoids Power BI throughput limits but does not fix the job's compute performance.

Option D (decrease event size) is not typically under the control of the job configuration and may not be feasible.

Full explanation →
69
Multi-Selecteasy

Which TWO actions should you take to optimize a Spark job in Azure Synapse Analytics that is experiencing excessive shuffling and long execution times?

Select 2 answers
A.Coalesce the number of partitions to reduce overhead.
B.Enable checkpointing to persist intermediate results.
C.Broadcast one of the tables if it is small enough to fit in memory.
D.Disable dynamic resource allocation.
E.Increase the number of shuffle partitions using 'spark.sql.shuffle.partitions'.
AnswersC, E

Broadcast join eliminates shuffle by replicating the small table to all executors.

Why this answer

Broadcasting a small table avoids shuffle operations during joins, as the small table is sent to all executors. Option E is correct because increasing the number of shuffle partitions (via spark.sql.shuffle.partitions) can distribute data more evenly, reducing the size of each shuffle block and improving parallelism. Option A is incorrect: coalescing reduces the number of partitions, which may increase the amount of data shuffled per task and worsen skew.

Option B is incorrect: checkpointing improves fault tolerance but does not directly reduce shuffling. Option D is incorrect: disabling dynamic resource allocation can lead to resource contention or waste, not optimized shuffling.

Full explanation →
70
MCQmedium

A data engineer needs to store JSON documents that are frequently updated by multiple users concurrently. The solution must support optimistic concurrency control and have built-in indexing on all fields. Which Azure data store should be used?

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

Cosmos DB supports JSON documents, optimistic concurrency, and automatic indexing.

Why this answer

Azure Cosmos DB (SQL API) is the correct choice because it natively supports optimistic concurrency control via ETags (HTTP entity tags) and provides automatic indexing of all fields without requiring manual index management. This makes it ideal for storing JSON documents that are frequently updated by multiple concurrent users, as it ensures conflict detection and resolution while maintaining high performance.

Exam trap

The trap here is that candidates often choose Azure SQL Database because they associate concurrency control with relational databases, overlooking that Cosmos DB is purpose-built for JSON documents with automatic indexing and native optimistic concurrency via ETags, which is more aligned with the requirements than a relational store.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it does not support optimistic concurrency control; it uses lease-based locking for blobs, which is not designed for fine-grained concurrent updates on JSON documents and lacks built-in indexing on all fields. Option C (Azure SQL Database) is wrong because while it supports optimistic concurrency via snapshot isolation or row versioning, it requires manual index creation and is not optimized for storing and querying JSON documents natively; it is a relational store, not a document store. Option D (Azure Table Storage) is wrong because it does not support optimistic concurrency control (it uses ETags but only for individual entities, not for complex JSON documents) and its indexing is limited to partition and row keys, not all fields.

Full explanation →
71
Multi-Selecthard

You are designing a data storage solution for a financial services company. The solution must meet the following requirements: store transaction data for 7 years for regulatory compliance, support point-in-time restore (PITR) for the last 30 days, and minimize storage costs for historical data. Which THREE actions should you take?

Select 3 answers
A.Store historical data in Azure Data Lake Storage Gen2
B.Use Azure Blob Storage with cool access tier for data older than 30 days
C.Use Azure SQL Database with automated backups and PITR retention of 30 days
D.Implement a data export process using Azure Data Factory to move data older than 30 days to Blob Storage
E.Use Azure Table Storage for archival
AnswersB, C, D

Cool tier is cost-effective for infrequently accessed data.

Why this answer

Azure Blob Storage's cool access tier is designed for infrequently accessed data with lower storage costs, making it ideal for historical transaction data older than 30 days. This tier provides cost-effective storage while still allowing retrieval when needed, aligning with the requirement to minimize storage costs for historical data.

Exam trap

The trap here is that candidates often confuse Azure Data Lake Storage Gen2 with a cost-effective archival solution, when in fact it is designed for high-throughput analytics and lacks the tiered pricing that Blob Storage offers for long-term retention.

Full explanation →
72
MCQeasy

You need to ensure that data in an Azure Data Lake Storage Gen2 account is encrypted at rest using a customer-managed key. Which feature should you configure?

A.Azure Key Vault integration with Storage Service Encryption
B.Azure Information Protection
C.Azure Storage Service Encryption with Microsoft-managed keys
D.Azure Disk Encryption
AnswerA

Azure Storage Service Encryption supports customer-managed keys stored in Azure Key Vault.

Why this answer

Azure Key Vault integration with Storage Service Encryption. This feature allows ADLS Gen2 to use customer-managed keys stored in Azure Key Vault for encryption at rest. Option B is incorrect because Azure Information Protection is for data classification and labeling, not encryption.

Option C is incorrect because Storage Service Encryption with Microsoft-managed keys is the default, but the requirement specifies customer-managed keys. Option D is incorrect because Azure Disk Encryption applies to VM disks, not ADLS Gen2 storage.

Full explanation →
73
MCQeasy

Your organization uses Azure Data Lake Storage Gen2 and needs to prevent accidental deletion of data by enabling soft delete. You also need to ensure that deleted blobs are recoverable for 30 days. What should you configure?

A.Enable blob snapshots and set them to expire after 30 days.
B.Use Azure Backup to create daily backups of the storage account.
C.Enable container soft delete with a retention period of 30 days.
D.Enable blob soft delete and set retention period to 30 days.
AnswerD

Correct. Enabling blob soft delete with a retention period of 30 days allows recovery of deleted blobs within that timeframe, meeting both requirements.

Why this answer

Blob soft delete enables recovery of deleted blobs within a specified retention period (30 days in this case). Option A is incorrect because blob snapshots are point-in-time copies that require manual management and do not provide automatic recovery of deleted blobs. Option B is incorrect because Azure Backup is designed for virtual machines and other Azure resources, not for blob-level recovery in Data Lake Storage Gen2.

Option C is incorrect because container soft delete deletes entire containers, not individual blobs.

Exam trap

A common trap is confusing blob soft delete with container soft delete. Container soft delete protects entire containers, while blob soft delete protects individual blobs. For this question, blob soft delete is required.

Full explanation →
74
MCQmedium

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully but you notice that the data transfer is taking longer than expected. You need to improve the transfer performance without increasing the cost significantly. Which configuration change should you make?

A.Enable staged copy with an Azure Blob Storage staging location.
B.Use PolyBase to load the data into Blob Storage.
C.Increase the number of concurrent connections on the SQL Server.
D.Increase the Data Integration Units (DIU) for the copy activity.
AnswerA

Enabling staged copy with an Azure Blob Storage staging location allows data to be buffered, enabling parallel uploads and retries, which improves transfer performance.

Why this answer

Enabling staged copy with an Azure Blob Storage staging location improves performance by allowing parallel uploads and retries. Option B is wrong because PolyBase is used for loading data into Azure Synapse Analytics or SQL Data Warehouse, not Blob Storage. Option C is wrong because increasing concurrent connections on SQL Server may not address the bottleneck and could impact source performance.

Option D is wrong because increasing Data Integration Units (DIU) would increase cost without necessarily improving performance if the bottleneck is elsewhere (e.g., network or source).

Full explanation →
75
MCQhard

You are designing a data processing solution using Azure Databricks with Delta Live Tables (DLT). The solution must handle late-arriving data and ensure that updates to the delta table are processed exactly once. Which DLT pipeline setting should you configure?

A.Set the pipeline mode to 'Trigger once'.
B.Set the pipeline mode to 'Continuous' and enable 'Exactly once' delivery.
C.Use Auto Loader with 'file arrival' mode.
D.Configure the DLT pipeline with 'Delta Live Tables' and use 'APPLY CHANGES INTO' with a watermark.
AnswerD

APPLY CHANGES INTO handles late-arriving data with exactly-once semantics.

Why this answer

DLT pipelines support exactly-once processing and handle late data via watermarking and append-only or update modes. Option A is wrong because 'Trigger once' processes data one time only, not continuous. Option B is wrong because 'Continuous' mode processes data as it arrives but may not guarantee exactly-once without proper configuration.

Option C is wrong because 'File arrival' is not a DLT setting.

Full explanation →

Page 1 of 11

Page 2

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 →