Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 601675

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

Page 8

Page 9 of 11

Page 10
601
MCQmedium

You are designing a data lake architecture using Azure Data Lake Storage Gen2. The data will be ingested from multiple sources with varying schemas. You need to organize the data in a way that supports both batch and streaming analytics while maintaining data lineage. Which folder structure convention should you use?

A.Organize by ingestion date only, with subfolders for each source.
B.Organize by source system, then by date.
C.Use a medallion architecture with three layers: bronze (raw), silver (cleaned), gold (aggregated).
D.Organize by file format (CSV, Parquet, JSON) and date.
AnswerC

Medallion architecture provides clear separation and lineage.

Why this answer

The medallion architecture (bronze, silver, gold) is the recommended pattern for Azure Data Lake Storage Gen2 when handling multiple sources with varying schemas. It supports both batch and streaming by storing raw data in bronze, applying incremental transformations in silver, and serving aggregated views in gold, while maintaining data lineage through clear layer boundaries and audit columns.

Exam trap

The trap here is that candidates often choose Option B (source then date) because it seems logical for organization, but they overlook the requirement to support both batch and streaming analytics while maintaining data lineage, which the medallion architecture explicitly addresses through layered transformations.

How to eliminate wrong answers

Option A is wrong because organizing by ingestion date only, with subfolders for each source, lacks schema evolution support and makes it difficult to trace data lineage across transformations. Option B is wrong because organizing by source system then by date does not provide a standardized processing pipeline for both batch and streaming, and it fails to separate raw, cleaned, and aggregated states. Option D is wrong because organizing by file format and date ignores the need for schema management and lineage tracking, and it does not facilitate incremental processing or data quality checks across layers.

602
MCQhard

Refer to the exhibit. You are running the KQL query in Azure Data Explorer. The query returns no results, but you know there is data in the table T. What is the most likely issue?

A.The bin function is used incorrectly; should be bin(Timestamp, 1h) but placed in the wrong clause
B.The between operator syntax is incorrect; should be 'between (startTime..endTime)' without spaces around the dots
C.The datetime format is incorrect; use ISO 8601
D.The table T does not exist in the database
AnswerB

Spaces around the '..' can cause the range to be misparsed.

Why this answer

The KQL `between` operator requires the range syntax `between(datetime1..datetime2)` with no spaces around the two dots. The query uses `between (startTime .. endTime)` with spaces, which is invalid syntax and causes the query to return no results even though data exists in table T.

Exam trap

Microsoft often tests the exact syntax of KQL operators like `between`, where candidates overlook the requirement for no spaces around the two dots, assuming whitespace is allowed as in other languages.

How to eliminate wrong answers

Option A is wrong because the `bin` function is used correctly in the `where` clause to round timestamps; the issue is not about `bin` placement. Option C is wrong because the datetime format in the query uses a valid format (e.g., '2023-01-01 00:00:00') and KQL accepts various datetime formats, including the one shown. Option D is wrong because the question explicitly states that table T exists and contains data, so the table not existing is not the issue.

603
MCQhard

A healthcare company stores sensitive patient data in Azure Data Lake Storage Gen2. They need to ensure that only authorized users can access data and that all access is audited. They also need to prevent data from being accessed by unauthorized Azure services. Which combination of security features should be used?

A.Use a private endpoint and Azure AD authentication, disable public access.
B.Use Azure RBAC and ACLs for authorization, enable firewall and virtual network service endpoints, and enable diagnostic settings for auditing.
C.Use managed identity for service access and disable public access.
D.Use Azure AD authentication and SAS tokens for access, enabling diagnostic logs for auditing.
AnswerB

Combination provides layered security and full audit.

Why this answer

It combines Azure RBAC and ACLs for fine-grained authorization, a firewall with virtual network service endpoints to restrict access to authorized networks, and diagnostic settings to capture audit logs. This layered approach ensures that only authorized users can access the data, all access is audited, and unauthorized Azure services are blocked by the firewall and service endpoints.

Exam trap

Microsoft often tests the misconception that disabling public access alone is sufficient to block unauthorized Azure services, when in fact service endpoints or private endpoints are required to prevent access from other Azure services within the same region.

How to eliminate wrong answers

Option A is wrong because while a private endpoint and Azure AD authentication secure access and disable public access, they do not provide the ability to block unauthorized Azure services (e.g., other Azure services outside the virtual network) unless combined with network rules like service endpoints or firewall rules. Option C is wrong because managed identity alone does not prevent unauthorized Azure services from accessing the data; it only authenticates the service, and disabling public access without network-level controls still allows other Azure services within the same region to access the storage account via the Azure backbone. Option D is wrong because SAS tokens can be leaked or misused, and they do not provide the same level of fine-grained authorization as RBAC and ACLs; additionally, enabling diagnostic logs alone does not prevent unauthorized Azure services from accessing the data.

604
MCQhard

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is partitioned by date and ingested daily. You notice that the Delta table has many small files, causing slow read performance. Which strategy should you recommend to optimize the table for faster queries?

A.Run OPTIMIZE on the table to compact small files.
B.Run ZORDER BY on the date column.
C.Run VACUUM to delete old files.
D.Increase the number of partitions by adding a new partition column.
AnswerA

OPTIMIZE merges small files into larger ones.

Why this answer

Running OPTIMIZE on a Delta Lake table compacts many small files into larger ones, reducing the number of files that need to be read during queries. This directly addresses the slow read performance caused by the small file problem, which is common in daily partitioned ingestion. OPTIMIZE uses bin-packing to merge files up to a target size (default 256 MB), improving scan efficiency without changing the data.

Exam trap

The trap here is that candidates may confuse ZORDER BY (which improves data skipping but not file count) with OPTIMIZE (which reduces file count), or mistakenly think VACUUM or adding partitions solves the small file problem, when in fact they either don't address it or make it worse.

How to eliminate wrong answers

Option B is wrong because ZORDER BY is used to colocate related information within files to improve data skipping, but it does not reduce the number of small files; it only reorganizes data within existing files. Option C is wrong because VACUUM removes old, unreferenced files for storage cleanup and compliance, but it does not compact small files or improve read performance. Option D is wrong because increasing the number of partitions (e.g., by adding a new partition column) would create even more small files, worsening the small file problem and degrading read performance further.

605
MCQhard

You are monitoring an Azure Synapse Analytics dedicated SQL pool that is experiencing performance degradation during peak hours. You notice that some queries are being queued due to resource contention. You need to optimize query performance without scaling the Data Warehouse Units (DWUs). Which action should you take?

A.Increase the DWU setting to allocate more resources.
B.Create materialized views for frequently joined tables.
C.Configure result-set caching for the dedicated SQL pool.
D.Implement workload classification and assign the queries to a higher importance level.
AnswerC

Result-set caching stores query results in SSD, reducing resource usage for repeated queries and alleviating contention.

Why this answer

Result-set caching can significantly reduce query time for repeated queries by storing results in SSD, reducing resource contention. Option A is wrong because increasing DWUs changes the scale, which is not allowed per the requirement. Option B is wrong because materialized views help but do not directly address contention from repeated queries.

Option D is wrong because workload classification manages concurrency but does not reduce resource usage for repeated queries.

606
Matchingmedium

Match each Azure security feature to its description.

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

Concepts
Matches

Role-based access control for Azure resources

Cloud-based identity and access management service

Manage cryptographic keys and secrets

Private connectivity to Azure services over VNet

Why these pairings

The correct matches are: Azure AD for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Azure AD with Managed Identity and Key Vault with RBAC.

607
Multi-Selectmedium

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Select 2 answers
A.Use a conditional split to route rows with different schemas to separate sinks.
B.Define a rigid schema in the source dataset and reject rows that don't match.
C.Disable schema drift to improve performance.
D.Enable 'Allow schema drift' in the source transformation.
E.Use a derived column transformation to provide default values for missing columns.
AnswersD, E

This allows the data flow to handle changing columns.

Why this answer

Enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Exam trap

The trap here is that candidates often confuse handling schema drift with data routing or error handling, and they overlook that enabling schema drift is the foundational step that must be taken before any other transformations can work with the drifted columns.

608
Multi-Selectmedium

A company is designing a data storage solution for a global application that requires low-latency reads and writes for user session data. The solution must support automatic failover across multiple Azure regions. Which TWO Azure services meet these requirements?

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

Supports geo-replication and automatic failover.

Why this answer

Azure Cache for Redis is correct because it provides an in-memory data store with sub-millisecond latency for both reads and writes, making it ideal for user session data. It supports automatic failover across Azure regions through geo-replication, where data from a primary cache is asynchronously replicated to a secondary cache in a paired region, ensuring high availability and disaster recovery.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's multi-region writes with the specific requirement for low-latency session data, but Cosmos DB, while supporting automatic failover, has higher latency than an in-memory cache like Redis for frequent, small reads and writes typical of session state.

609
MCQmedium

You have an Azure Data Factory pipeline that executes a stored procedure in Azure SQL Database. The pipeline fails with an error indicating that the stored procedure ran out of memory. What change should you make to the pipeline to resolve this?

A.Add a retry policy to the stored procedure activity.
B.Increase the pipeline activity timeout.
C.Use a Self-Hosted Integration Runtime instead of Azure IR.
D.Scale up the Azure SQL Database to a higher service tier.
AnswerD

Higher service tiers provide more memory for the database.

Why this answer

The error indicates that the stored procedure ran out of memory, which is a resource limitation at the database level, not a transient failure or timeout issue. Scaling up the Azure SQL Database to a higher service tier (e.g., from Standard to Premium or increasing DTU/vCore count) provides more memory and compute resources, directly resolving the out-of-memory condition.

Exam trap

The trap here is that candidates confuse pipeline-level retries or timeouts with database-level resource constraints, assuming that retrying or waiting longer will fix a memory exhaustion error, which is a hard resource limit that requires scaling the database.

How to eliminate wrong answers

Option A is wrong because a retry policy only re-executes the activity on transient failures (e.g., network blips), but an out-of-memory error is a persistent resource constraint that will recur on retry. Option B is wrong because increasing the pipeline activity timeout extends the duration the pipeline waits for completion, but does not address the underlying memory shortage in the database. Option C is wrong because using a Self-Hosted Integration Runtime shifts data movement or activity execution to an on-premises or VM-based runtime, but does not affect the memory allocation of the Azure SQL Database where the stored procedure runs.

610
MCQhard

You are optimizing an Azure Synapse serverless SQL pool query that queries Parquet files in Azure Data Lake Storage. The query takes longer than expected. You notice that the query reads more data than necessary. What is the most effective way to reduce the amount of data scanned?

A.Split large Parquet files into smaller files of 100 MB each
B.Create external tables with explicit schema and partition by a frequently filtered column
C.Use SELECT with column pruning to only retrieve necessary columns
D.Increase the query's resource allocation by using a larger service level objective
AnswerB

Creating external tables with explicit schema and partitioning allows the serverless SQL pool to perform partition elimination, reading only the relevant directories matching filter conditions, thereby significantly reducing data scanned.

Why this answer

Partitioning external tables in Azure Synapse serverless SQL allows the query engine to perform partition elimination, reading only the subdirectories that match the filter criteria. This directly reduces the amount of data scanned from Parquet files in ADLS, addressing the core issue of reading unnecessary data.

Exam trap

The trap here is that candidates often confuse column pruning (reducing columns) with partition pruning (reducing rows), or assume that file size optimization alone reduces data volume, when in fact partition elimination is the key technique for minimizing scanned data in serverless SQL pools.

How to eliminate wrong answers

Option A is wrong because splitting large Parquet files into smaller files does not reduce the total data scanned; it may even increase overhead due to more file open operations. Option C is wrong because column pruning reduces the columns read, not the rows; the query still scans all partitions and files, so it does not address reading more data than necessary when filtering is involved. Option D is wrong because increasing the service level objective (SLO) allocates more resources but does not change the amount of data scanned; it only speeds up the scan, which is not the most effective way to reduce data volume.

611
MCQeasy

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

A.Microsoft Entra ID pass-through authentication.
B.Storage account key.
C.Shared access signature (SAS) token.
D.Service principal with a secret.
AnswerA

Microsoft Entra ID pass-through uses the caller's identity and enforces RBAC permissions.

Why this answer

Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Exam trap

The trap here is that candidates often confuse 'service principal' (a fixed identity) with 'user identity' and select option D, not realizing that pass-through authentication is the only method that preserves the caller's individual RBAC permissions.

How to eliminate wrong answers

Option B (Storage account key) is wrong because it uses a shared secret that grants full administrative access to the storage account, bypassing RBAC and the caller's identity entirely. Option C (Shared access signature token) is wrong because it delegates access based on a pre-signed URI with fixed permissions and expiry, not the caller's identity, and does not enforce RBAC. Option D (Service principal with a secret) is wrong because it authenticates as a fixed application identity rather than the individual caller, so RBAC permissions are evaluated against the service principal, not the user who submitted the query.

612
MCQmedium

Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?

A.Use UPDATE for existing rows and INSERT for new rows.
B.Use DELETE and INSERT statements in a single transaction.
C.Use a MERGE statement to perform upserts.
D.Create a staging table, load data, then use CTAS and partition switching to replace the target partition.
AnswerD

CTAS rebuilds the partition, minimizing fragmentation.

Why this answer

Using a staging table with CREATE TABLE AS SELECT (CTAS) and then switching partitions replaces the entire partition without individual row modifications, which minimizes fragmentation in a clustered columnstore index. Option A (UPDATE and INSERT) is wrong because individual updates cause columnstore fragmentation. Option B (DELETE and INSERT in a single transaction) also involves row-level changes that fragment columnstore.

Option C (MERGE) is wrong because MERGE operations on columnstore indexes cause significant fragmentation due to row-by-row modifications.

613
MCQhard

You are troubleshooting a Synapse Pipeline that runs a Copy activity from an on-premises SQL Server to Azure Synapse Dedicated SQL Pool. The pipeline fails with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed.' The on-premises SQL Server has no firewall restrictions. What is the most likely cause?

A.Staging is not enabled for the Copy activity.
B.The destination table in Synapse has a different schema.
C.The SQL Server credentials in the linked service are incorrect.
D.The self-hosted integration runtime is not configured properly.
AnswerD

A self-hosted integration runtime is required to connect from Azure to on-premises networks; misconfiguration is a common cause of source-side failures.

Why this answer

The error 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed' indicates that the Copy activity cannot connect to the on-premises SQL Server. Since the question states there are no firewall restrictions, the most likely cause is that the self-hosted integration runtime (SHIR) is not properly configured, registered, or running.

The SHIR is required to bridge the on-premises network to Azure, and if it is not correctly set up, the source connection will fail.

Exam trap

The trap here is that candidates often assume the error is due to credentials or schema mismatches, but the source-side failure with 'SqlOperationFailed' in an on-premises scenario almost always points to the self-hosted integration runtime connectivity, not the SQL Server itself.

How to eliminate wrong answers

Option A is wrong because staging is not required for a direct copy from on-premises SQL Server to Azure Synapse; staging is used for large data volumes or to enable additional transformations, and its absence would not cause a source-side SQL operation failure. Option B is wrong because a schema mismatch between the source and destination would cause a 'Failure happened on 'Sink' side' error, not a source-side error. Option C is wrong because incorrect SQL Server credentials would result in an authentication error (e.g., 'Login failed for user'), not a generic 'SqlOperationFailed' error, and the error message does not indicate a credential issue.

614
Multi-Selecteasy

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Select 2 answers
A.SQL Server Integration Services (SSIS) package
B.Azure Data Factory with incremental copy
C.PolyBase from external table
AnswersA, B

SQL Server Integration Services is a valid method because it is a full-featured ETL tool that can connect to on-premises SQL Server and load data directly into Azure Synapse Analytics using connectors like the Synapse destination adapter.

Why this answer

Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion.

PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Exam trap

The trap is that candidates may view SSIS as outdated or legacy, but it remains a fully supported and effective method for loading data into Azure Synapse Analytics. Meanwhile, PolyBase is often mistakenly thought of as a loading method, but it is primarily a query engine for external data.

615
Multi-Selectmedium

You are a data engineer for a company that uses Azure Synapse Analytics dedicated SQL pool. You need to implement security best practices to protect sensitive data. Which TWO actions should you take? (Choose two.)

Select 2 answers
A.Configure a firewall rule to allow only specific IP addresses.
B.Enable Azure Storage encryption for the underlying storage.
C.Enable Transparent Data Encryption (TDE) on the dedicated SQL pool.
D.Use Dynamic Data Masking to obfuscate sensitive data from all users.
E.Implement column-level security to restrict access to sensitive columns.
AnswersC, E

Correct: TDE encrypts the database at rest, protecting data files from unauthorized access.

Why this answer

Options C and E are correct. C: Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, protecting against unauthorized access to the physical storage. E: Column-level security restricts access to sensitive columns based on user roles or identities, ensuring that only authorized users can view or modify sensitive data.

A is incorrect because firewall rules control network access, not data protection within the database. B is incorrect because Azure Storage encryption is enabled by default for all storage accounts and is not a security measure specific to Synapse SQL pool. D is incorrect because Dynamic Data Masking obfuscates data from all users, but it does not prevent access; users can still see the original data if they have permissions, unlike column-level security which can block access entirely.

616
Multi-Selectmedium

Which TWO actions should you take when monitoring Azure Data Lake Storage Gen2 to detect security threats?

Select 2 answers
A.Use Azure Security Center and Azure Defender for Storage.
B.Enable diagnostic settings for the storage account and send logs to Azure Sentinel.
C.Enable soft delete for blobs to recover from accidental deletions.
D.Configure firewall and virtual network service endpoints.
E.Set up alerting on the 'Transactions' metric.
AnswersA, B

Azure Defender provides threat detection and alerts for storage accounts.

Why this answer

Azure Security Center (now Microsoft Defender for Cloud) with Azure Defender for Storage provides built-in threat detection for Azure Data Lake Storage Gen2, including anomaly detection, malware scanning, and alerts for suspicious activities like unauthorized access or data exfiltration. This is a primary action for detecting security threats because it continuously monitors storage telemetry and applies machine learning to identify potential security incidents.

Exam trap

The trap here is that candidates often confuse data protection features (like soft delete) or network controls (like firewalls) with active threat detection, overlooking that only dedicated security monitoring tools (Azure Security Center/Defender and Sentinel) can identify and alert on security threats in real time.

617
MCQeasy

You are processing streaming data from IoT devices using Azure Stream Analytics. The data includes temperature readings and device IDs. You need to calculate the average temperature per device over a 5-minute window, sliding every 1 minute. Which window function should you use?

A.Hop window
B.Session window
C.Sliding window
D.Tumbling window
AnswerA

Hop windows overlap and advance every hop interval.

Why this answer

A Hop window in Azure Stream Analytics allows you to specify a window size (5 minutes) and a hop size (1 minute), creating overlapping windows that slide forward every minute. This matches the requirement to calculate the average temperature per device over a 5-minute period, recalculated every minute, as the hop window outputs results at each hop interval while retaining data across overlapping windows.

Exam trap

The trap here is that candidates confuse 'sliding' with 'hopping' — a Sliding window in Stream Analytics is event-driven and does not produce periodic outputs, whereas a Hop window is time-driven and explicitly supports overlapping fixed-size windows with a hop interval.

How to eliminate wrong answers

Option B is wrong because a Session window groups events based on inactivity gaps (session timeout), not fixed time intervals, and would not produce consistent 5-minute windows sliding every 1 minute. Option C is wrong because a Sliding window in Stream Analytics outputs results only when an event occurs (e.g., for each new event), not at fixed time intervals, and does not support a predefined hop size. Option D is wrong because a Tumbling window is a series of fixed-size, non-overlapping contiguous time windows (e.g., every 5 minutes), which cannot produce overlapping windows that slide every 1 minute.

618
MCQeasy

You are developing a real-time data processing solution using Azure Stream Analytics. The input is an Azure Event Hubs stream with JSON data containing a 'timestamp' field. You need to output the average temperature per device every minute using a tumbling window. Which query should you use?

A.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SlidingWindow(minute, 1)
B.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, TumblingWindow(minute, 1)
C.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SessionWindow(minute, 1, 1)
D.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, HopWindow(minute, 1, 1)
AnswerB

Tumbling window of 1 minute produces non-overlapping windows.

Why this answer

A tumbling window is a fixed, non-overlapping time window that groups events into distinct time segments. Using `TumblingWindow(minute, 1)` with `TIMESTAMP BY Timestamp` ensures that the average temperature per device is computed over each one-minute interval without overlap, which matches the requirement of 'every minute'.

Exam trap

The trap here is that candidates confuse `SlidingWindow` or `HopWindow` with `TumblingWindow`, not realizing that only `TumblingWindow` produces non-overlapping, fixed-interval outputs required for a simple per-minute average.

How to eliminate wrong answers

Option A is wrong because `SlidingWindow` produces a continuous output for every event, not fixed intervals, and would not give a single average per minute. Option C is wrong because `SessionWindow` groups events based on inactivity gaps, not fixed time boundaries, and would not produce a consistent per-minute result. Option D is wrong because `HopWindow` creates overlapping windows with a hop size smaller than the window size, leading to multiple outputs per minute and not a single non-overlapping aggregation.

619
MCQhard

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is ingested from multiple sources and needs to be deduplicated based on a composite key (source_id, record_id). New data may have duplicates within the same batch. Which write mode and table property should you use to handle this efficiently?

A.Use 'append' mode and perform a MERGE operation after write to deduplicate.
B.Use 'overwrite' mode and enable 'delta.autoOptimize.optimizeWrite' = true.
C.Use 'ignore' mode and set 'delta.autoCompact' = true.
D.Use 'error' mode and enable 'delta.merge.onSchemaMismatch' = true.
AnswerA

Append with a subsequent MERGE allows custom dedup logic on composite key.

Why this answer

Using 'append' mode writes all incoming data as new files, and then performing a MERGE operation (upsert) based on the composite key (source_id, record_id) allows you to efficiently deduplicate both within the batch and against existing data. This approach leverages Delta Lake's ACID transactions and avoids the cost of rewriting entire partitions, making it suitable for handling duplicates from multiple sources.

Exam trap

Microsoft often tests the misconception that 'overwrite' mode or table properties like 'autoOptimize' can handle deduplication, but the correct approach requires explicit deduplication logic (like MERGE) because Delta Lake does not enforce unique constraints natively.

How to eliminate wrong answers

Option B is wrong because 'overwrite' mode replaces the entire table or partition, which is inefficient for deduplication and would lose existing data not in the current batch; enabling 'delta.autoOptimize.optimizeWrite' only improves file layout, not deduplication logic. Option C is wrong because 'ignore' mode silently skips writes that would cause a duplicate based on the Delta table's schema or constraints, but Delta Lake does not enforce unique constraints natively, so duplicates would still be written; 'delta.autoCompact' only merges small files, not deduplicates. Option D is wrong because 'error' mode fails the write if any data conflicts (e.g., schema mismatch), which is not a deduplication strategy; 'delta.merge.onSchemaMismatch' is not a valid Delta Lake table property—the correct property for schema evolution is 'delta.autoMerge.enabled' or 'mergeSchema' in the DataFrame writer option.

620
Multi-Selecthard

You are designing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs, and the output is to Azure Synapse Analytics. The solution must guarantee exactly-once delivery to Synapse. Which THREE configurations are required? (Choose three.)

Select 3 answers
A.Configure the output to use batch mode for writing.
B.Define a watermark strategy in the query to handle late-arriving events.
C.Set the late arrival tolerance window to zero.
D.Use a job with a unique identifier column in the output to enable deduplication.
E.Ensure the output table in Synapse has a primary key to support upsert operations.
AnswersB, D, E

Ensures correct windowing.

Why this answer

For exactly-once delivery to Azure Synapse Analytics from Stream Analytics, you need: a unique identifier column in the output table to enable deduplication (D), a watermark strategy to handle late-arriving events (B), and the output table must have a primary key to support upsert operations (E). Batch mode (A) is not supported for exactly-once delivery; instead, the output should use row-level insert/upsert. Setting the late arrival tolerance to zero (C) is not required; it is used to discard events that arrive after the tolerance, but it does not contribute to exactly-once semantics.

621
MCQmedium

Refer to the exhibit. You have created an external table in Azure Synapse serverless SQL pool as shown. You run a query: SELECT ProductID, SUM(Amount) FROM dbo.ExternalSales WHERE SaleDate > '2024-01-01' GROUP BY ProductID. The query is slow and scans all files in the /sales/ folder, which contains data from 2023 and 2024. The files are partitioned by year and month in the folder structure, e.g., /sales/year=2023/month=01/. What should you do to improve query performance?

A.Recreate the external table with a partition definition on SaleDate column using the folder structure
B.Recreate the external table with a partition on ProductID
C.Create statistics on the SaleDate column
D.Change the file format to CSV to improve read performance
AnswerA

By defining partitions using the folder structure, serverless SQL can skip partitions that don't match the filter.

Why this answer

The query performance is slow due to full file scanning. By recreating the external table with a partition definition on the SaleDate column that maps to the folder structure (e.g., /sales/year=2023/month=01/), Azure Synapse serverless SQL pool can perform partition elimination, reading only the relevant partitions for the WHERE clause filter (SaleDate > '2024-01-01'). This drastically reduces the amount of data scanned, improving query speed.

Exam trap

The trap here is that candidates often confuse creating statistics (which helps cardinality estimation but not data skipping) with partition elimination (which physically reduces data scanned), or they assume any column partition will work without matching the folder structure.

How to eliminate wrong answers

Option B is wrong because partitioning on ProductID does not align with the folder structure (which is partitioned by year and month), so it would not enable partition elimination for the date filter; it would still scan all files. Option C is wrong because creating statistics on SaleDate helps the query optimizer estimate cardinality but does not reduce the amount of data scanned; the query would still read all files without partition pruning. Option D is wrong because CSV files are typically slower to read than Parquet due to lack of compression and columnar storage; changing to CSV would worsen performance, not improve it.

622
MCQeasy

You need to configure encryption for an Azure SQL Database to protect data at rest. Which Azure service or feature should you enable?

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

TDE encrypts the database at rest automatically.

Why this answer

Transparent Data Encryption (TDE) is the correct choice because it performs real-time I/O encryption and decryption of the data and log files at rest, protecting against unauthorized access to the physical storage media. TDE uses an AES-256 encryption algorithm and is fully transparent to the application, requiring no changes to the database schema or queries.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (DDM) with encryption, thinking it protects data at rest, when in fact it only masks output and does not encrypt the underlying storage.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking (DDM) is a data masking feature that obfuscates sensitive data in query results to unauthorized users, but it does not encrypt data at rest. Option B is wrong because Always Encrypted is a client-side encryption technology that protects sensitive data in transit and at rest by encrypting columns with keys stored on the client, but it is not a database-level encryption for all data at rest and requires application changes. Option C is wrong because Azure Information Protection (AIP) is a classification and labeling service for documents and emails, not a database encryption feature for Azure SQL Database.

623
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must prevent unauthorized access to data at rest and in transit. Which combination of features should you implement?

A.Enable Transparent Data Encryption (TDE) and enforce TLS 1.2.
B.Use Azure RBAC and firewall rules.
C.Use Always Encrypted and column-level security.
D.Store encryption keys in Azure Key Vault and enable double encryption.
AnswerA

TDE encrypts data at rest, and TLS 1.2 encrypts data in transit.

Why this answer

Transparent Data Encryption (TDE) encrypts data at rest in Azure Synapse Analytics, and enforcing TLS 1.2 ensures encryption of data in transit. Option B (Azure RBAC and firewall rules) controls access but does not provide encryption. Option C (Always Encrypted and column-level security) is primarily for client-side encryption and access control, not comprehensive at-rest encryption.

Option D (storing keys in Key Vault and enabling double encryption) relates to key management and infrastructure encryption, but the direct combination of TDE and TLS 1.2 is the required solution.

624
MCQhard

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

A.Increase the number of executors to 4.
B.Change executor size to 'Large' to increase memory per executor.
C.Increase spark.sql.shuffle.partitions to 800.
D.Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.
AnswerC

More partitions reduce the size of each partition, speeding up shuffle.

Why this answer

Increasing spark.sql.shuffle.partitions to 800 improves parallelism for a 200 GB shuffle-heavy workload, reducing partition sizes and shuffle time. Option A is incorrect because executor count alone does not directly address shuffle partitioning. Option B may help but is less impactful than partition tuning.

Option D would worsen the problem by making partitions larger.

625
MCQhard

You deploy the Azure Security Center automation shown in the exhibit. What is the purpose of this automation?

A.It configures Azure Monitor to log high-severity alerts.
B.It applies an Azure Policy to remediate high-severity alerts.
C.It sends high-severity security alerts to an Event Hub for further processing.
D.It creates incidents in Azure Sentinel for high-severity alerts.
AnswerC

The action type is EventHub, and source severity is High.

Why this answer

Azure Security Center automation can be configured to send high-severity security alerts to an Event Hub for further processing, enabling integration with SIEM systems or custom workflows. Option A is incorrect because Azure Monitor logging is not configured by this automation; it's about forwarding alerts. Option B is incorrect because Azure Policy is used for compliance enforcement, not for remediating alerts via automation.

Option D is incorrect because creating incidents in Azure Sentinel is a separate action, not directly triggered by this automation; it forwards alerts to Event Hub.

626
MCQhard

Your company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. You notice that queries on a large fact table are slow. The table is hash-distributed on CustomerID and has 60 distributions. After reviewing the query plan, you see that many queries filter on OrderDate. You want to improve query performance without redesigning the table. What should you do?

A.Change the distribution key to OrderDate
B.Recreate the table with round-robin distribution
C.Update statistics only on the OrderDate column
D.Create a clustered columnstore index on the table and order by OrderDate
AnswerD

Clustered columnstore index with ordering by OrderDate accelerates range queries.

Why this answer

Creating a clustered columnstore index with ORDER BY OrderDate on the fact table improves query performance by physically ordering the data by OrderDate within each columnstore segment. This allows segment elimination during queries that filter on OrderDate, reducing the amount of data scanned. Since the table is already hash-distributed on CustomerID, this approach does not require redesigning the table and directly addresses the slow queries without changing the distribution strategy.

Exam trap

The trap here is that candidates often assume changing the distribution key (Option A) is the only way to optimize filter-heavy queries, but they overlook that a clustered columnstore index with ORDER BY can provide similar I/O reduction without the risks of data skew or redesign.

How to eliminate wrong answers

Option A is wrong because changing the distribution key to OrderDate would cause data skew if OrderDate has many duplicate values (e.g., all rows for a single date), leading to uneven distribution and poor parallel query performance. Option B is wrong because round-robin distribution distributes rows evenly but eliminates data locality for joins and aggregations, which would degrade performance for queries that join on CustomerID or other keys. Option C is wrong because updating statistics on OrderDate alone does not reduce I/O; statistics help the optimizer choose better plans but do not physically reorganize data to minimize data scanned for filter predicates.

627
Matchingmedium

Match each Azure Synapse Analytics component to its function.

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

Concepts
Matches

Distributed query engine for relational data

Apache Spark runtime for big data processing

Data integration and orchestration

Web-based IDE for developing analytics solutions

Why these pairings

In Azure Synapse Analytics, Dedicated SQL pool provides provisioned compute for relational data, Serverless SQL pool queries data lake files on-demand, Apache Spark pool handles distributed processing with Spark, and Pipeline orchestrates workflows. Common confusions include mixing serverless and provisioned capabilities or attributing Spark functionality to SQL pools.

628
Multi-Selecteasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to support both batch and near-real-time data ingestion. Which TWO of the following methods can you use to ingest data into a dedicated SQL pool? (Select TWO.)

Select 2 answers
A.CREATE TABLE AS SELECT (CTAS) from external tables.
B.PolyBase with T-SQL commands.
C.COPY INTO command.
D.Azure Logic Apps with SQL connector.
E.Azure Data Factory with a copy activity using native sink.
AnswersA, B

CTAS can load data into a dedicated SQL pool from external tables.

Why this answer

CREATE TABLE AS SELECT (CTAS) from external tables is correct because it allows you to load data from external storage (e.g., Azure Blob Storage or Azure Data Lake Storage) into a dedicated SQL pool in a single, parallelized operation. This method leverages the MPP (Massively Parallel Processing) architecture of Synapse SQL pools to efficiently ingest large volumes of data for batch processing.

Exam trap

The trap here is that candidates often confuse the COPY INTO command (valid only for serverless SQL pools) with PolyBase or CTAS, or mistakenly think Azure Data Factory's native sink can directly write to a dedicated SQL pool without PolyBase or staging.

629
MCQeasy

Refer to the exhibit. You run the Kusto query in Azure Monitor Logs to analyze Data Factory pipeline runs. What is the purpose of this query?

A.List all pipeline runs regardless of status
B.Identify pipelines with the most failed activity runs per hour
C.Calculate the average duration of failed pipeline runs
D.Show the number of failed trigger runs per hour
AnswerB

The query counts failed runs per pipeline per hour and sorts descending.

Why this answer

The query filters pipeline runs with a status of 'Failed', groups them by pipeline name and hourly time bins, then counts the number of failed activity runs per hour. The results are sorted descending by count, making it easy to identify which pipelines have the most failed activity runs per hour. Therefore, option B is correct.

Option A is incorrect because the query only includes failed runs, not all runs. Option C is incorrect because the query counts runs, not calculates duration. Option D is incorrect because the query filters on pipeline runs, not trigger runs.

630
MCQeasy

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query returns fewer rows than expected. What should you check first?

A.Ensure the external table has the correct schema definition.
B.Check that the Azure AD identity has read permissions on the storage account.
C.Check the compression codec used in the Parquet files.
D.Verify the file path and pattern in the OPENROWSET query.
AnswerD

Incorrect file path or pattern can cause missing files or partitions.

Why this answer

When using OPENROWSET in Azure Synapse serverless SQL pool to query Parquet files, the most common reason for fewer rows than expected is an incorrect file path or pattern. If the path or pattern is too restrictive (e.g., missing a wildcard or pointing to a subfolder instead of the root), the query will only read a subset of the files, resulting in fewer rows. This is the first thing to verify before investigating schema or permissions issues.

Exam trap

The trap here is that candidates often jump to schema or permission issues first, but the most frequent cause of missing rows in serverless SQL pool queries is an overly restrictive file path or pattern in the OPENROWSET query.

How to eliminate wrong answers

Option A is wrong because an incorrect schema definition would typically cause data type conversion errors or NULL values, not a reduction in row count; the query would still read all rows but might fail to parse them. Option B is wrong because if the Azure AD identity lacked read permissions, the query would fail entirely with an authorization error, not return fewer rows. Option C is wrong because the compression codec (e.g., snappy, gzip) does not affect the number of rows returned; Parquet files are self-describing and the serverless SQL pool automatically handles decompression regardless of codec.

631
MCQmedium

Match the Azure service to its primary data processing use case. Drag each service on the left to the correct use case on the right. Services: Azure Databricks, Azure Stream Analytics, Azure Data Factory, Azure Synapse Analytics Use Cases: - Real-time event processing - Orchestration of ETL pipelines - Big data analytics with Spark - Enterprise data warehousing

A.Azure Databricks - Big data analytics with Spark
B.Azure Stream Analytics - Real-time event processing
C.Azure Data Factory - Orchestration of ETL pipelines
D.Azure Synapse Analytics - Enterprise data warehousing
AnswerA, B, C, D

Why this answer

All four matches are correct. Azure Databricks is used for big data analytics with Apache Spark. Azure Stream Analytics processes real-time event streams.

Azure Data Factory orchestrates and automates ETL pipelines. Azure Synapse Analytics serves as an enterprise data warehouse with integrated analytics.

Exam trap

Candidates might confuse Azure Databricks with Azure Synapse Analytics because both can run Spark. However, Databricks is primarily a collaborative Spark environment, while Synapse is a data warehouse with integrated analytics.

632
MCQhard

You are running a data transformation pipeline in Azure Synapse Spark that writes output to Delta tables. You notice that the job eventually slows down and then fails with an out-of-memory error. The input data size is 1 TB, and the cluster has 10 nodes with 16 GB memory each. What is the most likely cause?

A.The driver node does not have enough memory to collect the results
B.The data is not partitioned properly, leading to large partitions that exceed executor memory
C.The Delta table is being written in non-optimized format causing memory pressure
D.The transformation involves a wide dependency causing excessive shuffle
AnswerB

Unpartitioned data can result in a few large partitions that cause OOM. Increasing parallelism or repartitioning can help.

Why this answer

The most likely cause is that the data is not partitioned properly, leading to large partitions that exceed executor memory. In Azure Synapse Spark, each executor has a limited memory (16 GB per node in this cluster), and if a single partition is too large to fit in memory, the task processing that partition will fail with an out-of-memory error. Proper partitioning ensures that data is evenly distributed across executors, preventing any single partition from overwhelming available memory.

Exam trap

The trap here is that candidates often confuse out-of-memory errors with driver-side collection (Option A) or shuffle-related issues (Option D), but the specific context of writing to Delta tables points to executor memory exhaustion from oversized partitions, not driver memory or shuffle overhead.

How to eliminate wrong answers

Option A is wrong because the driver node collects results only for actions like `collect()` or `show()`, but writing to Delta tables does not require collecting results to the driver; the failure is on executor tasks, not the driver. Option C is wrong because Delta tables are inherently optimized (using Parquet format with ACID transactions), and writing in non-optimized format is not a concept; memory pressure is caused by partition size, not the table format. Option D is wrong because while wide dependencies (e.g., groupBy, join) can cause excessive shuffle, the question specifically describes a slowdown and out-of-memory error during writing, which is more directly tied to partition size rather than shuffle overhead.

633
Multi-Selectmedium

Which TWO of the following are valid ways to handle late-arriving data in a streaming solution with Azure Stream Analytics? (Choose two.)

Select 2 answers
A.Reprocess the entire stream from the beginning when late data is detected.
B.Implement a custom Azure Function as a 'LateDataHandler' in the query.
C.Use a reference data input to store late-arriving events.
D.Configure the 'late arrival tolerance' window in the event ordering settings up to 21 days.
E.Use a temporal join to combine the late-arriving event with the historical window.
AnswersD, E

Stream Analytics allows setting a late arrival tolerance window to handle events that arrive after the event time.

Why this answer

Azure Stream Analytics allows you to configure a 'late arrival tolerance' window in the event ordering settings, which can be set up to a maximum of 21 days. This window defines how long the service will wait to accommodate events that arrive after their timestamp, reordering them within that tolerance before processing. Option E is correct because a temporal join (e.g., using LATERAL or JOIN with DATEDIFF) can combine a late-arriving event with historical data from a reference or stream window, enabling you to retroactively correct aggregations or state.

Exam trap

The trap here is that candidates confuse the 'late arrival tolerance' with a simple delay setting, not realizing it is a reordering buffer up to 21 days, and they overlook temporal joins as a valid pattern for handling late data, instead assuming only external functions or full reprocessing are options.

634
MCQmedium

You have an Azure Stream Analytics job that reads from an Event Hub and writes to Azure SQL Database. The job processes high-velocity IoT sensor data. You notice that the output to SQL Database is slower than expected and the job's watermark delay is increasing. What should you do to improve throughput?

A.Partition the output by a column like DeviceId.
B.Disable late arrival and out-of-order event handling.
C.Increase the Streaming Units (SU) of the job.
D.Decrease the window size in the query.
AnswerA

Partitioning allows parallel writes to SQL.

Why this answer

Partitioning the output by a column like DeviceId allows Azure Stream Analytics to write to multiple SQL Database tables or use partitioned tables, enabling parallel writes. This reduces contention and improves throughput because the job can distribute the load across multiple write operations, directly addressing the bottleneck caused by high-velocity IoT sensor data overwhelming a single output stream.

Exam trap

The trap here is that candidates often assume increasing compute resources (Streaming Units) always solves performance issues, but they overlook that the bottleneck is frequently at the output sink, requiring architectural changes like partitioning rather than scaling.

How to eliminate wrong answers

Option B is wrong because disabling late arrival and out-of-order event handling does not improve output throughput; it only changes how events are timestamped and may cause data loss or inaccuracies without addressing the write bottleneck. Option C is wrong because increasing Streaming Units (SU) allocates more compute resources to the job, but if the bottleneck is at the SQL Database output (e.g., write limits or lack of partitioning), adding SUs will not improve throughput and may even increase backpressure. Option D is wrong because decreasing the window size in the query reduces the amount of data aggregated per window, but it does not affect the rate at which output rows are written to SQL Database; the bottleneck remains at the output sink.

635
MCQmedium

You are configuring security for an Azure Synapse Analytics workspace. You need to ensure that only users in the 'DataScientists' Microsoft Entra group can read data from the 'sales' schema in the serverless SQL pool. What should you configure?

A.Create a server-level login for the group and assign it to the 'public' role
B.Create a database user mapped to the Microsoft Entra group and grant SELECT ON SCHEMA::sales to the group
C.Assign the 'Synapse SQL Administrator' role to the group at workspace level
D.Create a contained database user with password and assign it to the 'db_datareader' role
AnswerB

Granular permissions at schema level

Why this answer

You create a database user mapped to the Microsoft Entra group in the serverless SQL pool database, then grant SELECT ON SCHEMA::sales to that user. This ensures only members of the 'DataScientists' group have permission to read data from the 'sales' schema. Option A is wrong because a server-level login for the group does not grant specific schema-level permissions; the 'public' role provides broad access.

Option C is wrong because the 'Synapse SQL Administrator' role grants too many permissions (admin-level access) and is not schema-specific. Option D is wrong because a contained database user with password does not leverage Microsoft Entra group membership, and the 'db_datareader' role grants read access to all tables, not just the 'sales' schema.

636
MCQhard

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to optimize costs for a large dataset that is accessed only once a month for reporting. The data must be retained for 7 years. Which storage tier and lifecycle management rule should you configure?

A.Hot tier with no lifecycle policy
B.Cool tier with lifecycle policy to Archive after 30 days
C.Premium tier with lifecycle policy to Cool after 30 days
D.Archive tier with no lifecycle policy
AnswerB

Cool tier balances cost and access; Archive after 30 days reduces cost further.

Why this answer

Cool tier is cost-effective for data accessed infrequently (once a month), and a lifecycle policy to move data to Archive after 30 days further reduces costs while meeting the 7-year retention requirement. Option A (Hot tier) is expensive for infrequent access. Option C (Premium tier) is designed for high-throughput workloads, not cost optimization.

Option D (Archive tier with no lifecycle) incurs high retrieval costs for monthly access and doesn't leverage tiering.

637
MCQeasy

You need to transform data in Azure Databricks using Apache Spark. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. Which method should you use to read the data into a Spark DataFrame?

A.spark.read.parquet('abfss://container@storage.dfs.core.windows.net/path')
B.spark.read.format('delta').load('abfss://container@storage.dfs.core.windows.net/path')
C.spark.read.csv('abfss://container@storage.dfs.core.windows.net/path')
D.spark.read.json('abfss://container@storage.dfs.core.windows.net/path')
AnswerB

Delta format correctly reads the table including transaction log.

Why this answer

The data is stored in Delta Lake format, which requires using the 'delta' format reader in Spark to properly read the transaction log and schema. The `spark.read.format('delta').load()` method is the standard way to read Delta tables, leveraging the Delta Lake protocol for ACID transactions and time travel capabilities.

Exam trap

The trap here is that candidates may assume Delta Lake files are just Parquet files and use `spark.read.parquet()`, missing the critical role of the Delta transaction log for consistency and ACID compliance.

How to eliminate wrong answers

Option A is wrong because `spark.read.parquet()` reads only Parquet files and ignores Delta Lake's transaction log, leading to stale or inconsistent data. Option C is wrong because `spark.read.csv()` is for CSV files, not Delta Lake format. Option D is wrong because `spark.read.json()` is for JSON files, not Delta Lake format.

638
Multi-Selectmedium

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Select 2 answers
A.Use hash distribution on a low-cardinality column
B.Use round-robin distribution for fact tables
C.Use replicated tables for small dimension tables
D.Create materialized views for common aggregations
E.Increase the DWU setting after every query
AnswersC, D

Replicated tables eliminate data shuffling for joins with fact tables.

Why this answer

Correct: C and D. Replicated tables are optimal for small dimension tables because they avoid data movement during joins. Materialized views pre-compute and store aggregation results, improving query performance for common aggregations.

A is incorrect because hash distribution on a low-cardinality column can lead to data skew and uneven distribution. B is incorrect because round-robin distribution is generally used for staging tables, not fact tables in a star schema. E is incorrect because increasing DWU is a scaling action, not a query optimization design choice.

639
Drag & Dropmedium

Drag and drop the steps to set up Azure Data Lake Storage Gen2 hierarchical namespace for a data lake into the correct order.

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

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

Why this order

The storage account must have hierarchical namespace enabled. Then create a container, directories, set permissions, and upload data.

640
MCQeasy

You are using Azure Data Factory to copy data from an Azure SQL Database to Azure Synapse dedicated SQL pool. The copy activity uses PolyBase as the copy method. The activity fails with the error 'Operation not supported: PolyBase cannot write to a table with clustered columnstore index'. What should you do to resolve this error?

A.Use an external table as the sink instead of a regular table
B.Create the target table as a heap or with clustered index
C.Enable staging with blob storage and use 'Allow PolyBase'
D.Change the copy method from PolyBase to Bulk Insert
AnswerB

PolyBase does not support writing to a clustered columnstore index directly. The table must be a heap or have a clustered index.

Why this answer

PolyBase in Azure Data Factory cannot write directly to a table that has a clustered columnstore index (CCI). The sink table must be a heap or have a clustered index for PolyBase to work. Option B correctly identifies this requirement, as creating the target table as a heap or with a clustered index resolves the error.

Exam trap

The trap here is that candidates often assume staging with blob storage (Option C) or switching to Bulk Insert (Option D) are the only workarounds, but the question specifically tests the PolyBase requirement that the sink table must not have a clustered columnstore index.

How to eliminate wrong answers

Option A is wrong because using an external table as the sink would require additional setup and is not a direct fix for the PolyBase CCI limitation; PolyBase can write to external tables, but the error specifically occurs when writing to a regular table with CCI. Option C is wrong because enabling staging with blob storage and 'Allow PolyBase' is used for staging-based PolyBase loads, but it does not bypass the requirement that the final sink table must not have a CCI. Option D is wrong because changing the copy method from PolyBase to Bulk Insert would work but is not the optimal or recommended fix; the question asks what should be done to resolve the error, and the correct approach is to adjust the table schema to support PolyBase, not to abandon PolyBase entirely.

641
MCQhard

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

A.Auto Loader
B.Delta Lake format
C.Photon engine
D.Unity Catalog
AnswerB

Delta Lake provides ACID transactions, schema enforcement, and time travel.

Why this answer

Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Exam trap

Microsoft often tests the distinction between features that provide data governance (Unity Catalog) versus features that provide data reliability at the storage layer (Delta Lake), leading candidates to confuse Unity Catalog's metadata management with Delta Lake's transactional guarantees.

How to eliminate wrong answers

Option A is wrong because Auto Loader is a feature for incrementally ingesting new files from cloud storage, not for providing ACID transactions or schema enforcement. Option C is wrong because the Photon engine is a high-performance vectorized query engine that accelerates query execution but does not manage ACID transactions or schema constraints. Option D is wrong because Unity Catalog is a centralized metadata and governance layer for managing data assets, permissions, and lineage, but it does not directly enforce ACID transactions or schema enforcement at the table level.

642
MCQhard

A company uses Azure Stream Analytics to process real-time data from IoT devices. They need to ensure that the output to Azure Synapse Analytics is optimized for high throughput and low latency. What should they configure in the Stream Analytics job?

A.Use Azure SQL Database output instead of Azure Synapse Analytics.
B.Partition the output by a key and use a columnstore index in the target table.
C.Use a single partition for the output to simplify processing.
D.Disable batching to reduce latency.
AnswerB

Partitioning parallelizes writes and columnstore indexes are optimized for analytics.

Why this answer

Partitioning the output by a key enables parallel writes to Azure Synapse Analytics, significantly improving throughput, and using a columnstore index optimizes the target table for large data loads, reducing latency. Option A is incorrect because the question specifically requires Azure Synapse Analytics output, not Azure SQL Database. Option C is wrong because a single partition serializes writes, reducing throughput.

Option D is incorrect because disabling batching increases the number of write transactions, leading to higher latency and reduced throughput.

643
MCQhard

You are working with a Delta Lake table in Azure Databricks. The table is updated frequently with new data and occasionally with updates to existing rows. You need to optimize read performance for queries that filter on a specific date column. The table is partitioned by date. Which optimization technique should you apply?

A.Run OPTIMIZE on the table.
B.Run ZORDER BY on the date column.
C.Run ANALYZE STATISTICS on the table.
D.Run VACUUM to clean up old versions.
AnswerB

Z-ordering co-locates column data, improving data skipping for filters on that column.

Why this answer

ZORDER BY on the date column co-locates related data within each partition, significantly reducing the amount of data scanned for queries that filter on that column. This is especially beneficial for a frequently updated Delta table where data is already partitioned by date, as ZORDER BY optimizes the layout within partitions without changing the partition scheme.

Exam trap

The trap here is that candidates often confuse OPTIMIZE (file compaction) with ZORDER BY (data clustering), assuming any performance optimization technique will improve filter queries, but only ZORDER BY physically reorders data to enable efficient data skipping.

How to eliminate wrong answers

Option A is wrong because OPTIMIZE only compacts small files into larger ones and does not reorder data within partitions to improve filter performance on a specific column. Option C is wrong because ANALYZE STATISTICS collects metadata for the query optimizer but does not physically reorganize data to accelerate date-based filtering. Option D is wrong because VACUUM removes old snapshots and files for storage cleanup, not for read performance optimization on current data.

644
Multi-Selectmedium

Which TWO components are required to set up a streaming data pipeline using Azure Synapse Analytics? (Select two.)

Select 2 answers
A.Azure Data Factory
B.Azure Event Hubs
C.Azure Analysis Services
D.Azure Blob Storage
E.Azure Synapse Pipelines (or Spark)
AnswersB, E

Event Hubs ingests streaming events.

Why this answer

To set up a streaming data pipeline in Azure Synapse Analytics, you need a streaming ingestion source and a processing engine. Azure Event Hubs (Option B) is the correct ingestion service for real-time streaming data. Azure Synapse Pipelines (or Spark) (Option E) provides the processing engine to transform and analyze the streaming data.

Azure Data Factory (Option A) is primarily for batch data integration, not streaming. Azure Analysis Services (Option C) is for OLAP modeling, not streaming. Azure Blob Storage (Option D) is a storage destination, not a required component for streaming ingestion or processing.

645
MCQhard

You are reviewing an ARM template for Azure SQL Database security alert policy. Based on the exhibit, which threats will trigger an alert?

A.All alerts except SQL Injection and Access Anomaly
B.No alerts will be triggered because the policy is disabled
C.SQL Injection and Access Anomaly
D.SQL Injection Vulnerability and Data Exfiltration
AnswerD

These alerts are not listed as disabled, so they are enabled.

Why this answer

The ARM template shows the security alert policy is enabled (state = Enabled). The disabledAlerts list includes 'Sql_Injection' and 'Access_Anomaly', meaning these two alert types are suppressed and will not trigger alerts. All other alert types (e.g., Sql_Injection_Vulnerability, Data_Exfiltration, Unsafe_Action) remain enabled and will trigger alerts.

Therefore, option D is correct because SQL Injection Vulnerability and Data Exfiltration are among the enabled threats.

646
MCQeasy

A data processing job in Azure Synapse Analytics writes results to a table in the dedicated SQL pool. After a failure, the job restarts from the beginning, causing duplicates. Which design pattern should you implement to ensure idempotent writes?

A.Use a TRUNCATE statement before each insert.
B.Use a MERGE statement with a unique key to upsert data.
C.Use a staging table and then swap partitions with the target table.
D.Use CREATE TABLE AS SELECT (CTAS) with a unique constraint.
AnswerC

Atomic swap ensures idempotency.

Why this answer

Using a staging table with partition swapping ensures idempotent writes by atomically replacing the target partition with a fully loaded staging partition. This avoids duplicates even if the job restarts, as the swap operation is transactional and the staging table can be truncated before each run. In Azure Synapse dedicated SQL pool, partition switching is a metadata-only operation that provides consistency without data movement.

Exam trap

The trap here is that candidates often choose MERGE (Option B) thinking it inherently provides idempotency, but they overlook that MERGE in Synapse dedicated SQL pool is not atomic across retries and can still cause duplicates if the job fails after partial execution, whereas partition switching provides true atomic replacement.

How to eliminate wrong answers

Option A is wrong because TRUNCATE before each insert would remove all existing data in the table, which is destructive and not suitable for incremental or partial loads; it also does not handle concurrent access or partial failures gracefully. Option B is wrong because MERGE with a unique key can still produce duplicates if the job restarts mid-operation (e.g., after inserts but before updates), and MERGE in Synapse dedicated SQL pool is not fully atomic for large-scale upserts due to potential deadlocks and transaction log overhead. Option D is wrong because CREATE TABLE AS SELECT (CTAS) with a unique constraint does not prevent duplicates on restart—CTAS creates a new table each time, and the unique constraint only enforces uniqueness within that single execution, not across retries; additionally, CTAS does not provide a mechanism to swap or replace existing data atomically.

647
Multi-Selecthard

A company ingests streaming data from multiple sources into Azure Event Hubs. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and hour. The solution must minimize cost and processing latency. Which THREE actions should be taken?

Select 3 answers
A.Enable Event Hubs Capture to automatically write data to Data Lake Storage in Avro format.
B.Use Azure Data Factory to copy data from Event Hubs to Data Lake Storage every 5 minutes.
C.Use Azure Stream Analytics to read from Event Hubs and write to Data Lake Storage in Parquet format.
D.Configure Stream Analytics output to partition by date and hour.
E.Use Azure Databricks to process the stream and write to Data Lake Storage.
AnswersC, D, E

Azure Stream Analytics provides real-time processing from Event Hubs and supports writing directly to Data Lake Storage in Parquet format with partitioning.

Why this answer

Azure Stream Analytics (C) is ideal for this scenario because it processes streaming data from Event Hubs in real time and can write directly to Azure Data Lake Storage Gen2 in Parquet format, which provides efficient compression and columnar storage for analytics. Additionally, configuring the output partitioning by date and hour (D) ensures the data is organized in the required structure without post-processing. Azure Databricks (E) can also process the stream from Event Hubs and write Parquet with partitioning, offering flexibility for complex transformations while still meeting latency requirements, though it may incur slightly higher cost than Stream Analytics for simple pipelines.

Options A and B are incorrect: Event Hubs Capture writes Avro, not Parquet, and Data Factory introduces batch latency.

Exam trap

Candidates often confuse Event Hubs Capture's Avro output with the ability to write Parquet directly, or they mistakenly choose batch-oriented tools like Data Factory when a real-time streaming service (Stream Analytics) meets all requirements.

648
Multi-Selecthard

You are optimizing an Azure Synapse Analytics pipeline that uses mapping data flows. The pipeline runs slowly when processing 100 GB of data. Which THREE settings should you adjust to improve performance?

Select 3 answers
A.Set 'partition option' to 'Round robin' with a higher number of partitions.
B.Set a tumbling window trigger to run the pipeline every 5 minutes.
C.Increase the 'Compute type' to 'Memory Optimized' and increase the number of cores.
D.Enable 'Data flow debug' to monitor execution details.
E.Use 'Optimize shuffle' in the data flow settings.
AnswersA, C, E

Round robin partitioning distributes data evenly across partitions, improving parallelism.

Why this answer

Options A, C, and E are correct. Setting a higher number of partitions with Round robin partitioning improves parallelism across nodes. Increasing Compute type to Memory Optimized and increasing cores provides more resources for data processing.

Using Optimize shuffle reduces data movement during joins and aggregations, improving performance. Option B (tumbling window trigger) is a scheduling mechanism, not a performance tuning setting. Option D (Data flow debug) is for development and debugging, not for production performance optimization.

649
Multi-Selecthard

Which THREE metrics from Azure Monitor should you use to evaluate the performance of an Azure Data Lake Storage Gen2 account?

Select 3 answers
A.Ingress
B.CPU Usage
C.Success E2E Latency
D.Available Storage Capacity
E.Blob Count
AnswersA, C, E

Measures incoming throughput.

Why this answer

To evaluate the performance of an Azure Data Lake Storage Gen2 account, key metrics include Ingress (data written to the account), Success E2E Latency (end-to-end latency of successful requests), and Blob Count (total number of blobs/objects). Option A (Ingress) is correct because it measures the throughput of data entering the storage account. Option C (Success E2E Latency) is correct as it reflects the overall response time for successful operations.

Option E (Blob Count) is correct because it helps assess scalability and cost. Options B (CPU Usage) and D (Available Storage Capacity) are not metrics for storage accounts; CPU is for compute resources, and capacity is a property, not a performance metric.

650
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are running slower over time. The table is hash-distributed on a date key and has a clustered columnstore index. Which action should you take to improve query performance?

A.Add a non-clustered index on frequently filtered columns.
B.Change the distribution column to a column with higher cardinality.
C.Change the distribution to round-robin.
D.Rebuild the clustered columnstore index.
AnswerD

Rebuilding the columnstore index improves compression, removes deleted rows, and reorganizes rowgroups, enhancing scan performance.

Why this answer

Over time, columnstore indexes can become fragmented due to insert, update, and delete operations, leading to compressed row groups that are not optimally sized or have deleted records. Rebuilding the clustered columnstore index reorganizes the data into fully compressed row groups, removes deleted rows, and restores the high compression and segment elimination that columnstore indexes rely on for fast query performance.

Exam trap

The trap here is that candidates may assume performance degradation is always due to data skew or distribution choice, overlooking the common real-world issue of columnstore index fragmentation from ongoing DML operations.

How to eliminate wrong answers

Option A is wrong because adding a non-clustered index on frequently filtered columns would introduce additional index maintenance overhead and is unlikely to outperform the existing columnstore index for large fact tables; columnstore indexes already excel at scanning and filtering large datasets. Option B is wrong because changing the distribution column to one with higher cardinality does not address the root cause of performance degradation over time, which is index fragmentation, not data skew or distribution inefficiency. Option C is wrong because changing the distribution to round-robin would eliminate data locality for joins and aggregations, likely worsening query performance, and does not resolve the fragmentation issue.

651
MCQhard

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

A.Create external tables with statistics on relevant columns.
B.Create clustered columnstore indexes on the external tables.
C.Convert the Parquet files to CSV format for faster reads.
D.Partition the data into many small files.
AnswerA

Statistics help the optimizer prune data and improve query performance.

Why this answer

In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Exam trap

The trap here is that candidates confuse external table capabilities with dedicated SQL pool features, assuming that indexes like columnstore can be applied to external tables, or that file format changes (CSV) or file count adjustments are the primary performance levers, when in fact statistics are the critical missing piece for serverless SQL pool optimization.

How to eliminate wrong answers

Option B is wrong because clustered columnstore indexes are not supported on external tables in serverless SQL pool; they are only applicable to tables in dedicated SQL pools. Option C is wrong because CSV format is slower than Parquet for analytical queries due to lack of compression, columnar storage, and predicate pushdown; converting to CSV would degrade performance. Option D is wrong because partitioning data into many small files increases metadata overhead and file open operations, which slows down queries in serverless SQL pool; optimal performance is achieved with a moderate number of reasonably sized files.

652
MCQeasy

You have an Azure Stream Analytics job that writes output to Azure Synapse Analytics. You need to ensure that the job can authenticate to Synapse Analytics using a managed identity. What should you do?

A.Enable system-assigned managed identity on the Stream Analytics job and configure the output to use it.
B.Generate a shared access signature (SAS) token for the Synapse Analytics workspace.
C.Create a user-assigned managed identity and assign it to the Stream Analytics job.
D.Configure the output to use SQL Server authentication with a username and password.
AnswerA

This is the correct method to use managed identity for authentication.

Why this answer

To authenticate to Azure Synapse Analytics using a managed identity, you should enable the system-assigned managed identity on the Stream Analytics job (it is enabled by default) and then configure the output to use managed identity authentication. Option A is correct. Option B is incorrect because a SAS token is not a managed identity.

Option C is incorrect because while a user-assigned managed identity could be used, the system-assigned managed identity is sufficient and is the simpler option. Option D is incorrect because SQL Server authentication does not use managed identity and is less secure than managed identity authentication.

653
Matchingmedium

Match each data transformation concept to its definition.

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

Concepts
Matches

Handling flexible columns that change over time

Timestamp to track incremental data processing

Optimization to read only relevant partitions

Merge insert and update operations into a single action

Why these pairings

The correct matches are: PolyBase queries external data via T-SQL; Data Lake is a raw data repository; ELT loads before transforming; Azure Data Factory is a cloud integration service. Common confusions include mixing up PolyBase with Data Lake, or ELT with Azure Data Factory.

654
MCQmedium

You have an Azure Data Lake Storage Gen2 account that stores parquet files. You need to ensure that files containing personally identifiable information (PII) are automatically classified and tagged. Which Azure service should you integrate?

A.Azure Policy
B.Microsoft Sentinel
C.Microsoft Defender for Cloud
D.Microsoft Purview
AnswerD

Purview provides automated scanning and classification of PII.

Why this answer

Microsoft Purview provides automated data classification and labeling for Azure Storage, including Azure Data Lake Storage Gen2. Option A is wrong because Azure Policy enforces rules but does not classify content. Option B is wrong because Microsoft Sentinel is a SIEM, not for classification.

Option C is wrong because Microsoft Defender for Cloud is for security posture, not data classification.

655
MCQmedium

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

A.Transition to cool tier after 30 days; delete after 7 years.
B.Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).
C.Transition to archive tier after 30 days; delete after 7 years.
D.Transition to cool tier after 30 days; transition to cool tier again after 365 days.
AnswerB

Matches the requirements exactly.

Why this answer

It aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Exam trap

The trap here is that candidates may confuse the required tiering order (cool then archive) with direct archiving after 30 days (Option C) or fail to include a deletion rule (Option D), missing the 7-year compliance requirement.

How to eliminate wrong answers

Option A is wrong because it deletes the data after 7 years but does not include a transition to the archive tier after 1 year, which is required by the compliance policy to archive data after 365 days. Option C is wrong because it transitions to archive tier after only 30 days, which violates the requirement to keep data in a lower-cost tier (cool) for the first year before archiving. Option D is wrong because it transitions to cool tier again after 365 days, which does not archive the data as required, and it lacks a deletion rule for the 7-year retention period.

656
Multi-Selecthard

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Select 3 answers
A.Store data in Parquet format
B.Partition files by date to enable partition elimination
C.Compress files using snappy or gzip
D.Use many small files (under 64 MB) to increase parallelism
E.Store data in nested folder structures for better organization
AnswersA, B, C

Why this answer

Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Exam trap

The trap here is that candidates often confuse file size optimization with parallelism, assuming smaller files increase parallelism, but in serverless SQL, too many small files cause excessive metadata requests and reduce throughput, while larger files enable better batch processing.

Why the other options are wrong

D

Small files cause overhead; larger files (128 MB+) are recommended.

E

Deeply nested folders increase file listing time, impacting performance.

657
Multi-Selecteasy

Which TWO configurations are recommended to secure data processing in Azure Synapse Pipelines?

Select 2 answers
A.Configure a self-hosted integration runtime on a public cloud VM.
B.Use the default Auto-resolve Integration Runtime for all data flows.
C.Store connection strings and secrets in Azure Key Vault and reference them via linked services.
D.Enable Managed Virtual Network (VNet) to isolate data flows.
E.Allow all public IP addresses to access the Azure Synapse workspace.
AnswersC, D

Key Vault centralizes secret management.

Why this answer

Storing connection strings and secrets in Azure Key Vault and referencing them via linked services ensures secrets are stored securely and not exposed in pipeline definitions. Option D is correct: Enabling Managed Virtual Network (VNet) isolates data flows within a managed network boundary, preventing public network access. Option A is incorrect: Configuring a self-hosted integration runtime on a public cloud VM does not necessarily improve security; it may expose the runtime to the public internet.

Option B is incorrect: Using the default Auto-resolve Integration Runtime is not recommended for secure data processing because it may use public endpoints and lacks network isolation. Option E is incorrect: Allowing all public IP addresses to access the Azure Synapse workspace exposes the workspace to potential security threats.

658
MCQeasy

Your organization uses Azure SQL Database with Active Geo-Replication for disaster recovery. You need to ensure that all connections to the database use Microsoft Entra ID authentication and that access is audited. You also want to minimize the attack surface by disabling SQL authentication. What should you do?

A.Configure Conditional Access policies to require MFA for database access.
B.Enable 'Azure AD-only authentication' in the Azure SQL Database server settings and remove all SQL Server authenticated logins.
C.Create a server-level firewall rule to allow only specific IP addresses and enable SQL authentication.
D.Create an Azure RBAC role to restrict access to the database and assign it to users.
AnswerB

Disables SQL authentication and enforces Entra ID.

Why this answer

Enabling 'Azure AD-only authentication' in the Azure SQL Database server settings disables SQL authentication and enforces Microsoft Entra ID authentication, which also ensures auditing through Entra ID logs. Option A is incorrect: Conditional Access policies enforce MFA for Entra ID authentication, but they do not disable SQL authentication, leaving the attack surface open. Option C is incorrect: Creating a firewall rule restricts IP addresses but does not disable SQL authentication.

Option D is incorrect: Azure RBAC controls management plane access (e.g., resource management), not database-level authentication; it does not disable SQL authentication.

659
MCQeasy

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

A.Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.
B.Azure Data Factory mapping data flows.
C.Azure Cosmos DB Core (SQL) API.
D.Azure SQL Database with JSON columns.
AnswerA

Serverless SQL pool can query JSON files with schema-on-read using T-SQL.

Why this answer

Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Exam trap

The trap here is that candidates often confuse schema-on-read with schema-on-write, picking Azure SQL Database or Cosmos DB because they support JSON, but those require predefined schemas or containers, failing the schema-on-read requirement.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory mapping data flows are designed for ETL/ELT transformations, not for direct T-SQL querying of data at rest. Option C is wrong because Azure Cosmos DB Core (SQL) API stores data as JSON but does not support schema-on-read; it requires a defined container schema and uses its own SQL dialect, not standard T-SQL. Option D is wrong because Azure SQL Database with JSON columns requires a predefined table schema and does not support schema-on-read for external files; it stores JSON in relational columns, not as files.

660
MCQeasy

You need to monitor an Azure Data Factory pipeline for failures and send an email notification when a pipeline run fails. Which Azure service should you use to create an alert based on the pipeline run metrics?

A.Microsoft Sentinel
B.Azure Monitor
C.Azure Service Health
D.Azure Log Analytics
AnswerB

Azure Monitor can create alerts based on ADF metrics like 'Failed pipeline runs', making it the correct choice.

Why this answer

Azure Monitor can create alerts based on ADF metrics like 'Failed pipeline runs'. Option A is wrong because Microsoft Sentinel is for security. Option C is wrong because Azure Service Health monitors Azure service health, not pipeline runs.

Option D is wrong because Azure Log Analytics is for log queries, not alerting.

661
MCQeasy

A company uses Azure Synapse Analytics dedicated SQL pool. They need to load data from Azure Data Lake Storage Gen2 (ADLS Gen2) incrementally. Which PolyBase external table configuration supports incremental loading without reprocessing historical data?

A.Create an external table with a clustered index on the date column.
B.Create an external table partitioned by date column with a partition location pattern.
C.Create an external table on ORC files with a clustered columnstore index.
D.Create an external table with round-robin distribution on the date column.
AnswerB

Partitioned external tables allow PolyBase to eliminate partitions, loading only new data.

Why this answer

Partitioning an external table by a date column with a partition location pattern allows PolyBase to perform partition elimination during queries. This means only the partitions containing new or modified data are read from ADLS Gen2, enabling incremental loading without scanning historical data.

Exam trap

The trap here is that candidates confuse indexing or distribution strategies (which apply to internal tables) with external table capabilities, overlooking that only partition elimination on external tables enables incremental file-level filtering in PolyBase.

How to eliminate wrong answers

Option A is wrong because a clustered index on an external table is not supported; external tables in Azure Synapse are read-only and cannot have indexes. Option C is wrong because a clustered columnstore index applies to internal tables in the dedicated SQL pool, not to external tables, and does not control which files are read from ADLS Gen2. Option D is wrong because round-robin distribution distributes data evenly across distributions but does not enable partition elimination or incremental loading; it is a distribution method for internal tables, not a mechanism for selective file reading.

662
MCQmedium

Your organization has an Azure Data Factory pipeline that executes a series of activities to transform data. One of the activities is an Azure Databricks notebook that should run only if the previous activity succeeds. You need to configure the pipeline to handle failures gracefully and send an email alert if the Databricks activity fails. What should you do?

A.Add a failure output path from the Databricks activity to a Web activity that calls an email API.
B.Configure a retry policy and a timeout for the Databricks activity.
C.Use a Schedule trigger to run the pipeline and check for failures using Azure Monitor.
D.Set a dependency condition on the Databricks activity to 'Succeeded' and add a Send Email activity on the success path.
AnswerA

You can route failure output to a Web activity to send an email via Logic Apps or Azure Functions.

Why this answer

You can configure a failure output path from the Databricks activity and attach a Web activity that calls an email API to send an alert when the Databricks activity fails. Option B is incorrect because retry and timeout settings do not send email alerts. Option C is incorrect because a Schedule trigger does not handle activity-level failures directly.

Option D is incorrect because setting a dependency to 'Succeeded' only runs subsequent activities on success, not on failure.

663
MCQeasy

A healthcare organization needs to store electronic health records (EHR) in a format that supports schema flexibility and complex nested data. The solution must allow fast queries by patient ID and enable analytics with Azure Synapse. Which data store should you choose?

A.Azure Table Storage
B.Azure Data Lake Storage Gen2 with files in JSON format
C.Azure Cosmos DB with analytical store enabled
D.Azure SQL Database with JSON columns
AnswerC

Cosmos DB provides schema flexibility, nested data support, and Synapse integration for analytics.

Why this answer

Azure Cosmos DB with analytical store enabled is the correct choice because it provides schema flexibility for complex nested EHR data, supports fast point reads by patient ID via its indexed partition key, and the analytical store enables efficient analytics with Azure Synapse through the Synapse Link feature, which automatically synchronizes operational data into a columnar format optimized for large-scale queries.

Exam trap

The trap here is that candidates often choose Azure SQL Database with JSON columns (Option D) because they assume relational databases can handle JSON, but they overlook the requirement for schema flexibility and native analytical store integration, which Cosmos DB with analytical store uniquely provides for hybrid transactional/analytical processing (HTAP) workloads.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store that does not support complex nested data structures or schema flexibility for hierarchical EHR records, and it lacks native integration with Azure Synapse for analytics. Option B is wrong because while Azure Data Lake Storage Gen2 with JSON files can store nested data, it does not provide fast point queries by patient ID without additional indexing or processing, and it requires separate ETL for analytics rather than real-time analytical store access. Option D is wrong because Azure SQL Database with JSON columns imposes a fixed relational schema and does not offer the same level of schema flexibility as a NoSQL document store; JSON columns also complicate indexing and nested query performance, and it lacks a built-in analytical store for seamless Synapse integration.

664
MCQeasy

You need to monitor the performance of an Azure Synapse Analytics dedicated SQL pool. Which DMV should you query to find queries that are currently running and their execution status?

A.sys.dm_pdw_nodes
B.sys.dm_pdw_request_steps
C.sys.dm_pdw_errors
D.sys.dm_pdw_exec_requests
AnswerD

This DMV lists all currently executing requests and their status.

Why this answer

Sys.dm_pdw_exec_requests shows currently running requests in a dedicated SQL pool. Option A is wrong because sys.dm_pdw_nodes is a system view, not for queries. Option B is wrong because sys.dm_pdw_request_steps shows steps of completed or running requests, not high-level status.

Option C is wrong because sys.dm_pdw_errors shows errors, not running queries.

665
Multi-Selecteasy

Which TWO features of Azure Databricks help manage data governance and security for sensitive data?

Select 2 answers
A.Structured Streaming
B.Secret Scopes
C.Auto Loader
D.Delta Live Tables
E.Unity Catalog
AnswersB, E

Secret Scopes securely store and manage access tokens and keys.

Why this answer

Secret Scopes (B) allow secure storage and referencing of sensitive credentials (e.g., API keys, database passwords) in Azure Databricks, preventing hardcoding in notebooks. Unity Catalog (E) provides fine-grained access control, data lineage, and centralized metadata management across workspaces, enabling governance of sensitive data through policies and auditing.

Exam trap

The trap here is that candidates confuse data processing features (Structured Streaming, Auto Loader, Delta Live Tables) with governance/security tools, because all are part of the Databricks ecosystem but serve fundamentally different purposes.

666
MCQhard

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

A.The storage account will be created, but the policy will be evaluated later during a compliance scan.
B.The storage account will be created with HTTPS enabled automatically.
C.The storage account creation will be denied and the request will fail.
D.The storage account will be created, but an audit event will be logged.
AnswerC

The policy denies the creation if the condition is met.

Why this answer

The Azure Policy definition shown in the exhibit uses the 'Deny' effect for the 'Secure transfer to storage accounts should be enabled' policy. When a data engineer attempts to create a storage account with 'Enable secure transfer' set to Disabled, the policy evaluation occurs at resource creation time (before the resource is provisioned). Because the request violates the policy's condition (HTTPS traffic not enforced), Azure Resource Manager denies the creation request and returns a failure, preventing the non-compliant storage account from being created.

Exam trap

The trap here is that candidates often confuse the 'Deny' effect with 'Audit' or 'Disabled' effects, mistakenly believing that policy evaluation only happens during periodic compliance scans rather than at resource creation time, leading them to choose Option A or D.

How to eliminate wrong answers

Option A is wrong because Azure Policy with a 'Deny' effect is evaluated synchronously during resource creation, not deferred to a later compliance scan; a 'Deny' policy blocks the request immediately. Option B is wrong because the policy does not automatically modify the storage account configuration to enable HTTPS; it only denies or audits, and with 'Deny' effect it rejects the request outright. Option D is wrong because an 'audit' event would only occur if the policy effect were 'Audit' or 'AuditIfNotExists', but the exhibit specifies a 'Deny' effect, which blocks creation rather than logging an audit event.

667
MCQeasy

Your company uses Azure Blob Storage to store backups. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault. Which feature should you enable?

A.Azure Purview
B.Azure Disk Encryption
C.Azure Storage Service Encryption with customer-managed keys
D.Azure Information Protection
AnswerC

Allows using CMK from Key Vault for Blob Storage.

Why this answer

Azure Storage Service Encryption (SSE) encrypts data at rest and supports customer-managed keys stored in Azure Key Vault. Option A is incorrect because Azure Purview is a data governance service, not for encryption. Option B is incorrect because Azure Disk Encryption is used for virtual machine disks, not Blob Storage.

Option D is incorrect because Azure Information Protection is for classification and labeling. Therefore, option C is correct.

668
Multi-Selecthard

Your company stores JSON documents in Azure Cosmos DB Core (SQL) API. You need to improve query performance for a common filter on the 'status' field and a sort on 'timestamp'. Which three actions should you take?

Select 3 answers
A.Create a composite index on (status, timestamp)
B.Increase the provisioned RU/s
C.Choose a partition key that ensures even distribution
D.Denormalize the status field into a separate container
E.Disable indexing on the timestamp field
AnswersA, B, C

Why this answer

A composite index on (status, timestamp) is correct because Azure Cosmos DB Core (SQL) API uses composite indexes to efficiently support queries with multiple filter and sort conditions. This index allows the query engine to first filter on the 'status' field and then sort by 'timestamp' in a single index seek, avoiding a full scan or in-memory sort. Without this composite index, the query would require a cross-partition scan or an expensive sort operation, degrading performance.

Exam trap

The trap here is that candidates may think increasing RU/s (Option B) or disabling indexing (Option E) are sufficient fixes, but the core issue is the lack of an appropriate composite index to support the combined filter and sort, which is a common DP-203 exam pattern for Cosmos DB query optimization.

Why the other options are wrong

D

Unnecessary complexity; composite index is sufficient.

E

Sorting requires indexing on that field.

669
Multi-Selecthard

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The workload includes large fact tables and dimension tables. You need to improve query performance for star join queries. Which TWO actions should you take?

Select 2 answers
A.Use round-robin distribution on fact tables.
B.Use hash distribution on dimension tables.
C.Use heap table structure for fact tables.
D.Use replicated distribution on dimension tables.
E.Use hash distribution on fact tables using the join key.
AnswersD, E

Replicated tables avoid data movement.

Why this answer

The correct actions are D and E. For star join queries in Azure Synapse Analytics dedicated SQL pool, best practices are to use hash distribution on fact tables using the join key (option E) to distribute data across distributions efficiently, and use replicated distribution on dimension tables (option D) to avoid data movement. Option A is wrong because round-robin distribution is suitable for staging or temporary tables, not for large fact tables in star schemas.

Option B is wrong because dimension tables should be replicated, not hash-distributed, to minimize shuffling. Option C is wrong because heap tables are not optimized for analytical queries; clustered columnstore index is the recommended table structure for fact tables.

670
MCQmedium

Refer to the exhibit. A user with Storage Blob Data Reader role on the container rawdata cannot list files under /2023/07/. What is the most likely reason?

A.The directory ACL does not grant 'execute' permission to the user
B.The user does not have Storage Blob Data Contributor role
C.The user is not the owner of the directory
D.The container name is misspelled
AnswerA

To list directory contents, the user needs execute permission on the directory. 'other' has no permissions, so the user (not being owner or group) is denied.

Why this answer

In Azure Data Lake Storage Gen2, listing files in a directory requires both read (r) and execute (x) permissions on the directory itself. The Storage Blob Data Reader role grants read access to blob data but does not automatically grant the execute permission on directory ACLs. Without execute permission on the /2023/07/ directory, the user cannot traverse or list its contents, even though they can read blobs they know the path to.

Exam trap

The trap here is that candidates assume the Storage Blob Data Reader role is sufficient for all read operations, but Azure Data Lake Storage Gen2 requires explicit ACL execute permission on directories for listing and traversal, which is a common point of confusion between flat blob storage and hierarchical namespace storage.

How to eliminate wrong answers

Option B is wrong because Storage Blob Data Contributor role is not required for listing files; the issue is specifically the missing execute permission on the directory ACL, not the role level. Option C is wrong because ownership of the directory is not a prerequisite for listing its contents; ACLs (not ownership) control access to list and traverse. Option D is wrong because the container name being misspelled would cause a different error (e.g., container not found), not a permission-denied error when attempting to list files.

671
MCQhard

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

A.Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.
B.Increase the 'connection timeout' setting in the linked service to 30 minutes.
C.Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.
D.Disable fault tolerance in the copy activity to improve performance.
AnswerA

The self-hosted IR needs sufficient resources for large data transfers; 'Connection closed' often indicates resource exhaustion or network interruptions.

Why this answer

A 'Connection closed' error during large data transfers (50 GB) often occurs due to resource exhaustion or network instability on the self-hosted integration runtime (IR) machine. Insufficient memory or CPU on the IR node or unreliable network connectivity can cause the connection to drop. This is the first thing to troubleshoot.

Option B (increasing connection timeout) might help if the error is due to timeouts, but 'Connection closed' typically indicates an abrupt termination, not a timeout. Option C (staged copy) can improve reliability by using Azure Blob as an intermediate store, but it adds complexity and is not the first step. Option D (disabling fault tolerance) would make the pipeline less resilient and is not recommended; fault tolerance helps handle transient errors.

672
Multi-Selecthard

Which THREE factors should you consider when choosing between Azure Data Factory Mapping Data Flows and Azure Synapse Spark pools for data transformation?

Select 3 answers
A.Scheduling: Only Data Flows can be scheduled via triggers.
B.Ease of use: Mapping Data Flows provide a visual designer, while Spark requires code.
C.Data volume: Data Flows are limited to 100 GB, while Spark can handle petabytes.
D.Integration with other services: Data Flows can use integration runtimes, while Spark is limited to Synapse.
E.Debugging: Data Flows have a debug session limit of 8 hours, while Spark pools have no debug limit.
AnswersB, D, E

Data Flows are no-code, Spark requires coding.

Why this answer

Azure Data Factory Mapping Data Flows offer a visual, no-code designer for building data transformations, which lowers the barrier for users who are not proficient in programming. In contrast, Azure Synapse Spark pools require writing code in languages like PySpark, Scala, or SQL, making them more suitable for developers comfortable with coding. This distinction directly addresses ease of use as a key factor in choosing between the two services.

Exam trap

The trap here is that candidates assume Mapping Data Flows have a hard data volume limit (like 100 GB) or that Spark pools cannot be scheduled, when in fact both services are highly scalable and can be orchestrated via triggers, and the key differentiator is the coding versus visual interface.

673
Multi-Selectmedium

Which THREE factors should be considered when choosing between Azure Synapse Dedicated SQL Pool and Azure SQL Database for a data warehouse workload?

Select 3 answers
A.Need for geo-replication
B.Concurrency and workload management
C.Integration with Azure Data Lake Storage
D.Volume of data to be stored
E.Complexity of analytical queries
AnswersB, D, E

Dedicated SQL Pool has workload management.

Why this answer

Azure Synapse Dedicated SQL Pool provides built-in workload management features like workload classification, importance, and concurrency slots, which are essential for managing mixed analytical workloads with varying resource demands. Azure SQL Database, while supporting resource governance, lacks the granular workload management capabilities needed for complex data warehouse scenarios with concurrent queries.

Exam trap

The trap here is that candidates often assume geo-replication (Option A) is a critical differentiator, but both services support it, and the real decision hinges on workload management, data volume scalability, and analytical query complexity.

674
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by month. You notice that queries filtering on a specific month still scan all partitions. The table has a clustered columnstore index. What is the most likely cause?

A.The table is hash-distributed on a column other than date, preventing partition elimination.
B.The statistics on the date column are outdated or missing.
C.The columnstore index has a large number of open rowgroups that are not compressed.
D.The table is replicated, so partition elimination does not apply.
AnswerC

Open rowgroups cause the entire columnstore to be scanned, bypassing partition elimination.

Why this answer

When a clustered columnstore index has a large number of open rowgroups (i.e., rowgroups that have not yet been compressed into columnstore segments), queries may fall back to the delta store, which stores data in a B-tree-like structure. This bypasses partition elimination because the delta store does not maintain the same metadata for partition pruning, causing all partitions to be scanned. Compressing open rowgroups into closed, compressed rowgroups restores proper partition elimination behavior.

Exam trap

The trap here is that candidates often assume partition elimination is always effective with columnstore indexes, overlooking that unclosed delta store rowgroups can disable this optimization, and they may incorrectly attribute the issue to distribution or statistics.

How to eliminate wrong answers

Option A is wrong because hash distribution on a non-date column does not prevent partition elimination; partition elimination is a storage-level optimization based on partition boundaries, independent of distribution method. Option B is wrong because statistics on the date column are used for cardinality estimation, not for partition elimination; partition elimination is determined by metadata about partition ranges, not statistics. Option D is wrong because replicated tables are fully copied to each distribution, but partition elimination still applies when querying a replicated table if it is partitioned; the issue here is specific to the columnstore index's rowgroup state, not replication.

675
MCQhard

Your team is running a critical Azure Stream Analytics job that writes results to Azure SQL Database. Recently, the job has been failing with high latency and occasional data loss. You need to monitor the job's performance and set up alerts for when the watermark delay exceeds a threshold. What should you use?

A.Application Insights SDK integration in the job.
B.Azure Log Analytics workspace connected to the job diagnostics logs.
C.Azure Monitor metrics for the Stream Analytics job.
D.Azure Data Explorer for querying job performance data.
AnswerC

Azure Monitor provides built-in metrics like watermark delay and can trigger alerts.

Why this answer

Azure Monitor provides built-in metrics for Azure Stream Analytics jobs, including the watermark delay metric, which can be used to set up alerts. Option A is incorrect because Application Insights SDK integration is not supported for Stream Analytics jobs; monitoring is done via Azure Monitor metrics. Option B is incorrect because while Log Analytics can store diagnostics logs, it does not directly expose the watermark delay metric for alerting.

Option D is incorrect because Azure Data Explorer is a data exploration service, not a monitoring tool for Stream Analytics.

Page 8

Page 9 of 11

Page 10

All pages