Courseiva

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 376–450

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

Take a mock examExam hub
Page 5

Page 6 of 11

Page 7
376
MCQeasy

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

A.Use Azure Stream Analytics to transform the data in real time.
B.Use PolyBase to load data and then use T-SQL stored procedures to transform.
C.Use Azure Databricks notebooks with Spark to perform transformations.
D.Use mapping data flows in Azure Synapse pipelines.
AnswerD

Correct. Mapping data flows in Azure Synapse pipelines are serverless, provide a visual interface for data transformations, and integrate directly with Azure Synapse pipelines, making them ideal for cleansing and transforming data before loading into a dedicated SQL pool.

Why this answer

Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless.

Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Full explanation →
377
MCQeasy

A company stores IoT sensor data in Azure Blob Storage. The data is appended every minute and must be queried in near real-time using a SQL interface. Which Azure service should be used to enable this?

A.Azure Cosmos DB
B.Azure SQL Database
C.Azure Synapse SQL Pool
D.Azure Data Lake Storage Gen2
AnswerC

Azure Synapse SQL Pool (within Azure Synapse Analytics) can query data in Blob Storage/ADLS Gen2 using PolyBase or external tables, enabling near real-time SQL queries over the stored IoT data.

Why this answer

Azure Synapse SQL Pool (part of Azure Synapse Analytics) can query IoT sensor data stored in Blob Storage or ADLS Gen2 in near real-time using PolyBase or external tables. This schema-on-read approach provides a SQL interface directly over the stored data without needing to move it into a traditional database.

Exam trap

Candidates often assume that Azure Data Lake Storage Gen2 provides a SQL interface on its own, but it is only a storage layer. The correct service to enable SQL queries over Blob Storage is Azure Synapse Analytics (using SQL Pool or Serverless SQL).

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB is a NoSQL database optimized for low-latency, globally distributed key-value or document workloads, not for SQL-based ad-hoc querying of append-only blob data. Option B is wrong because Azure SQL Database is a relational OLTP engine that requires schema-defined tables and transactional ingestion, making it unsuitable for direct, schema-on-read queries over raw append-blob files. Option C is wrong because Azure Synapse SQL Pool (dedicated) is a massively parallel processing (MPP) data warehouse designed for large-scale batch analytics, not for near real-time queries on continuously appended blob data without complex ingestion pipelines.

Full explanation →
378
MCQmedium

You have an Azure Data Factory pipeline that uses a Self-Hosted Integration Runtime (SHIR) to copy data from an on-premises Oracle database to Azure Blob Storage. The pipeline is failing with a connectivity error. You have verified that the SHIR is running and the network firewall allows outbound traffic to Azure. What is the most likely cause of the failure?

A.The SHIR is not registered with Azure Data Factory.
B.The SHIR cannot reach the Oracle database due to a network firewall.
C.The SHIR requires inbound port 443 from Azure to on-premises.
D.The SHIR does not have access to Azure Key Vault.
AnswerB

The SHIR must have network access to the on-premises database.

Why this answer

The most likely cause is that the SHIR cannot reach the on-premises Oracle database due to a network firewall. While the SHIR can connect to Azure outbound, the connection from the SHIR to the Oracle database must also be allowed through any on-premises firewalls. Option A is incorrect because if the SHIR were not registered, you would see a different error.

Option C is incorrect because the SHIR only requires outbound connectivity to Azure, not inbound. Option D is incorrect because Azure Key Vault is not required for basic connectivity; it is used for storing credentials.

Full explanation →
379
Multi-Selecthard

Which THREE components are required to implement a modern data warehouse architecture on Microsoft Azure using Azure Synapse Analytics?

Select 3 answers
A.Microsoft Purview for data governance and lineage.
B.Power BI as the data visualization layer.
C.Azure Data Lake Storage Gen2 as the data lake.
D.Azure Analysis Services for semantic modeling.
E.Azure Synapse dedicated SQL pool for data warehousing.
AnswersA, C, E

Purview provides metadata management and data discovery.

Why this answer

Microsoft Purview is required for data governance and lineage in a modern data warehouse architecture on Azure because it provides a unified data governance service that helps manage and monitor data across on-premises, multi-cloud, and SaaS sources. It enables automated data discovery, classification, and lineage tracking, which are essential for compliance and data quality in a modern analytics environment.

Exam trap

The trap here is that candidates often mistake Power BI as a required architectural component, but it is only a visualization tool, not a core storage or compute layer in the modern data warehouse stack.

Full explanation →
380
MCQhard

You have an Azure Data Factory pipeline defined as shown. The pipeline is failing because the preCopyScript truncates the staging table before each run, but the table is empty on the first run. What change would you make to ensure the pipeline works correctly?

A.Remove the preCopyScript entirely.
B.Increase the writeBatchSize to 50000 to speed up the copy.
C.Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.
D.Set recursive to false in the source.
AnswerC

This conditional truncation prevents error when table is empty.

Why this answer

The preCopyScript runs on every pipeline execution. On the first run, the staging table does not exist, so TRUNCATE TABLE dbo.Staging would fail. By adding a check with IF OBJECT_ID, the script only truncates if the table exists.

This handles the first run gracefully. Option A is wrong because removing the preCopyScript would not truncate the table on subsequent runs, potentially causing duplicate data issues. Option B is wrong because increasing writeBatchSize does not solve the truncation error.

Option D is wrong because setting recursive to false affects how the source dataset handles recursive file systems, not the truncation problem.

Full explanation →
381
MCQmedium

A company is designing a data lake on Azure Data Lake Storage Gen2. Data comes from multiple sources with varying schemas. The team must minimize storage costs while keeping all data available for future processing. Which storage tier should they use for the raw ingested data?

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

Correct. Cool tier balances cost and availability for infrequently accessed data.

Why this answer

The Cool tier is the optimal choice for raw ingested data in a data lake because it offers low storage costs while maintaining low-latency access for future processing. Unlike the Archive tier, Cool tier data is immediately available without the multi-hour rehydration delay, and it is significantly cheaper than the Hot tier for data that is infrequently accessed but must remain online for ETL or batch processing.

Exam trap

The trap here is that candidates often confuse 'minimize storage costs' with 'cheapest tier possible' and select Archive, forgetting that raw data must be immediately accessible for future processing, which Archive cannot provide without significant delay.

How to eliminate wrong answers

Option A is wrong because the Premium tier is designed for high-transaction workloads with sub-millisecond latency, not for cost-efficient storage of raw data; it would incur unnecessary expense. Option B is wrong because the Archive tier requires data to be rehydrated (taking up to 15 hours) before it can be read, making it unsuitable for raw data that must be available for future processing. Option D is wrong because the Hot tier is optimized for frequent access and has the highest storage cost, which contradicts the requirement to minimize storage costs for data that is not accessed often.

Full explanation →
382
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool that is used for reporting. You notice that the tempdb database is growing rapidly and causing queries to fail. Which two actions should you take to mitigate the issue? (Select two.)

A.Enable result-set caching to reduce query reruns.
B.Increase the service level (DWU) of the dedicated SQL pool.
C.Reduce the degree of parallelism (MAXDOP) for the workload.
D.Move tempdb to a separate storage account.
E.Optimize queries that perform large sorts or hash joins.
AnswerC, E

Lowering MAXDOP reduces the number of concurrent operations that can consume tempdb resources.

Why this answer

Options C and E are correct. Reducing the degree of parallelism (MAXDOP) limits the number of concurrent operations, reducing tempdb usage. Optimizing queries that perform large sorts or hash joins reduces the demand on tempdb.

Option A is incorrect because result-set caching does not affect tempdb usage. Option B is incorrect because increasing DWU may provide more tempdb space but does not address the root cause of excessive tempdb usage. Option D is incorrect because moving tempdb to a separate storage account is not supported in Azure Synapse Analytics.

Full explanation →
383
MCQmedium

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

A.Use query hints to optimize execution plans.
B.Query the sys.dm_exec_requests DMV to view running queries and their resource usage.
C.Enable diagnostic settings and send query logs to Log Analytics.
D.Create statistics on all columns used in queries.
AnswerB

DMVs give real-time insight into resource consumption.

Why this answer

Querying the sys.dm_exec_requests DMV provides real-time resource consumption data for running queries in serverless SQL pool, allowing identification of the most resource-intensive queries. Option A is incorrect because query hints optimize execution but should be applied after identifying problematic queries. Option C is incorrect as diagnostic settings send logs to Log Analytics, but DMVs offer immediate insights without additional configuration.

Option D is incorrect because serverless SQL pool automatically manages statistics, and creating additional statistics is not the priority when first troubleshooting resource consumption.

Full explanation →
384
MCQmedium

You execute the above T-SQL in a serverless SQL pool in Azure Synapse Analytics. The external table creation succeeds, but when you query the table, it returns zero rows. The folder 'sales/products/' exists in the container and contains multiple .parquet files. What is the most likely cause?

A.The file format is incorrect; should be DELIMITEDTEXT instead of PARQUET.
B.The LOCATION path in the external table does not match the actual file path.
C.The external data source location uses the wrong endpoint; should use .blob.core.windows.net instead.
D.The credential used in the external data source does not have read permissions.
AnswerB

If the files are in a subfolder or the path is incorrect, no files are read.

Why this answer

The external table's LOCATION parameter specifies a path relative to the external data source's root. Even though the folder 'sales/products/' exists, the LOCATION must exactly match the subfolder path within the container. A mismatch (e.g., missing a trailing slash, case sensitivity, or an extra prefix) causes the serverless SQL pool to scan no files, returning zero rows.

Exam trap

The trap here is that candidates assume the LOCATION must include the full container path, but it is relative to the external data source's root, so a mismatch in the relative subfolder (e.g., missing a slash or using an absolute path) leads to zero rows without an error.

How to eliminate wrong answers

Option A is wrong because the files are .parquet, so PARQUET is the correct file format; using DELIMITEDTEXT would fail to parse the binary Parquet data. Option C is wrong because serverless SQL pools in Azure Synapse use the .dfs.core.windows.net endpoint (Azure Data Lake Storage Gen2) by default; .blob.core.windows.net is for legacy Blob Storage and would cause a connection error, not zero rows. Option D is wrong because if the credential lacked read permissions, the query would throw an authorization error (e.g., 'Access denied'), not silently return zero rows.

Full explanation →
385
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

A.Keep round-robin distribution but increase the degree of parallelism.
B.Change the distribution to hash on multiple columns.
C.Change the distribution to hash on the column that is most frequently used in joins.
D.Rebuild the table as a heap to improve insert performance.
AnswerC

Hash distribution on a join key reduces data shuffling.

Why this answer

Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling.

Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Full explanation →
386
MCQmedium

Refer to the exhibit. A data engineer notices that the target SQL table contains duplicate rows after a pipeline run. Which change to the pipeline configuration would prevent duplicates?

A.Remove the 'preCopyScript'
B.Change 'writeBatchSize' to 5000
C.Add a 'Upsert' setting in SqlSink with a key column
D.Set 'recursive' to false
AnswerC

Upsert ensures that rows are updated or inserted based on a key, preventing duplicates.

Why this answer

The preCopyScript truncates the table before copy, but if the pipeline is run multiple times and truncation fails or is skipped, duplicates can occur. Using upsert semantics or adding a watermark could help, but the simplest fix is to ensure truncation is reliable. However, the best practice is to use a merge/upsert pattern.

Among the options, adding a surrogate key and using upsert is most effective.

Full explanation →
387
MCQeasy

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

A.Ensure the Integration Runtime is registered and online
B.Check if the Blob Storage endpoint is accessible from the Integration Runtime
C.Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled
D.Verify that the SQL Server login credentials are correct
AnswerC

Timeout often indicates network blocking or SQL Server not listening on TCP/IP.

Why this answer

The 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Exam trap

The trap here is that candidates assume a 'Connection timed out' error is always a firewall or network issue, overlooking the SQL Server-side protocol configuration that must be explicitly enabled for remote TCP connections.

How to eliminate wrong answers

Option A is wrong because the question states that the Integration Runtime is already verified as running, so re-checking its registration and online status is redundant and does not address the timeout. Option B is wrong because the error is a connection timeout to SQL Server, not to Blob Storage; the pipeline fails before data transfer begins, so Blob Storage accessibility is irrelevant at this stage. Option D is wrong because incorrect login credentials would result in an authentication error (e.g., 'Login failed'), not a 'Connection timed out' error, which is a network-level issue.

Full explanation →
388
MCQhard

A company stores sensitive customer data in Azure Data Lake Storage Gen2. They need to implement a data retention policy where data older than 90 days is automatically moved to the 'cold' access tier, and data older than 365 days is deleted. Which Azure feature should be used to automate this?

A.Blob Storage lifecycle management
B.Azure Automation
C.Azure Data Factory
D.Azure Policy
AnswerA

Lifecycle management policies can automatically move blobs between tiers and delete based on age.

Why this answer

Azure Blob Storage lifecycle management is the correct feature because it allows you to define rules that automatically transition blobs to cooler access tiers (like the 'cold' tier) after a specified number of days and delete them after a further period. This directly meets the requirement to move data older than 90 days to the cold tier and delete data older than 365 days, all without custom code or manual intervention.

Exam trap

The trap here is that candidates often confuse Azure Policy (which enforces resource-level compliance) with data lifecycle management, or they assume a general automation tool like Azure Automation is needed when a native, policy-driven feature already exists.

How to eliminate wrong answers

Option B (Azure Automation) is wrong because it is a general-purpose automation service for running PowerShell or Python runbooks, not a native data lifecycle management feature; it would require custom scripting to enumerate blobs, check ages, and perform tier changes or deletions, adding complexity and maintenance overhead. Option C (Azure Data Factory) is wrong because it is an orchestration and data integration service for moving and transforming data between stores, not a policy-based lifecycle management tool; it could be used to copy or delete data but lacks the declarative, rule-based tiering and deletion capabilities of lifecycle management. Option D (Azure Policy) is wrong because it enforces compliance rules on Azure resource configurations (e.g., requiring encryption or specific SKUs) and cannot directly manage blob tier transitions or deletions based on data age.

Full explanation →
389
MCQhard

You are designing a near-real-time data processing solution for a retail company. The source is a Kafka cluster on-premises. The target is an Azure Synapse Dedicated SQL Pool. The solution must handle up to 10,000 events per second with less than 5-minute latency. Which Azure service should you use to ingest the data?

A.Azure Event Hubs (with Kafka protocol support)
B.Azure Data Lake Storage Gen2
C.Azure IoT Hub
D.Azure Stream Analytics
AnswerA

Event Hubs supports Kafka protocol and can ingest 10K events/sec with low latency.

Why this answer

Azure Event Hubs with Kafka protocol support is the correct choice because it provides a fully managed, high-throughput data ingestion service that can handle up to 10,000 events per second with sub-second latency, and it natively supports the Kafka protocol, allowing direct integration with your on-premises Kafka cluster without custom code or additional gateways. This meets the near-real-time requirement (<5-minute latency) and scales to the specified throughput.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics as an ingestion service, but it is a processing engine that requires an ingestion layer (like Event Hubs) first, and they may overlook that Azure Event Hubs natively supports the Kafka protocol, making it the direct replacement for Kafka ingestion in Azure.

How to eliminate wrong answers

Option B (Azure Data Lake Storage Gen2) is wrong because it is a hierarchical file store designed for batch analytics and data lake storage, not a real-time event ingestion service; it cannot natively consume Kafka streams or provide sub-5-minute latency for streaming data. Option C (Azure IoT Hub) is wrong because it is optimized for device-to-cloud telemetry from IoT devices, not for high-throughput event streams from a Kafka cluster, and it imposes device identity and throttling limits that are unsuitable for 10,000 events per second from a non-IoT source. Option D (Azure Stream Analytics) is wrong because it is a stream processing engine that requires an input source (like Event Hubs) to ingest data; it cannot directly ingest from Kafka on-premises and is not an ingestion service itself.

Full explanation →
390
MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

A.Azure Data Factory
B.Azure Blob Storage
C.Azure Event Hubs
D.Azure Analysis Services
AnswerC

Event Hubs is designed for streaming data ingestion and works with Synapse.

Why this answer

Azure Event Hubs is a big data streaming platform and event ingestion service that integrates with Synapse Analytics for streaming data. Option A is incorrect because Azure Data Factory is primarily for batch data integration, not real-time streaming. Option B is incorrect because Azure Blob Storage is a storage service, not an ingestion service.

Option D is incorrect because Azure Analysis Services is for semantic modeling, not streaming ingestion.

Full explanation →
391
MCQmedium

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

A.Azure Batch
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Synapse Pipelines
AnswerB

Stream Analytics provides real-time stream processing with SQL-like queries.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Synapse Pipelines with stream processing because they support 'real-time' triggers, but these services are fundamentally batch-oriented and cannot achieve the sub-second latency required for continuous stream aggregation.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a batch processing service for running large-scale parallel jobs, not designed for near real-time stream processing; it introduces significant latency due to job scheduling and queuing. Option C is wrong because Azure Data Factory is an ETL and data orchestration service focused on batch data movement and transformation, lacking native support for continuous stream processing. Option D is wrong because Azure Synapse Pipelines are built on the same orchestration engine as Data Factory and are intended for batch-oriented workflows, not for real-time stream aggregation.

Full explanation →
392
Multi-Selecteasy

Which TWO are valid methods to load data into Azure Synapse Analytics dedicated SQL pool?

Select 2 answers
A.BCp utility
B.Azure Data Factory Copy Activity
C.PolyBase
D.BULK INSERT
E.COPY statement
AnswersC, E

Loads from Azure Storage.

Why this answer

PolyBase is a valid method for loading data into Azure Synapse Analytics dedicated SQL pool because it uses the T-SQL language to access and combine data from external sources like Azure Blob Storage or Azure Data Lake Store without needing to move the data first. The COPY statement is also valid as it provides a high-throughput, flexible ingestion mechanism that supports various file formats and error handling options directly via T-SQL.

Exam trap

The trap here is that candidates often confuse BULK INSERT (which is SQL Server-specific) with the COPY statement (which is Synapse-specific), or they mistakenly think Azure Data Factory is a direct loading method rather than an orchestration tool.

Full explanation →
393
Multi-Selectmedium

Which TWO actions should you take to secure sensitive data in Azure Data Lake Storage Gen2? (Choose two.)

Select 2 answers
A.Enable public network access from all networks for ease of use
B.Use access control lists (ACLs) to restrict access to specific directories
C.Allow anonymous access to enable sharing
D.Disable soft delete to prevent accidental retention of deleted data
E.Enable encryption at rest using customer-managed keys in Azure Key Vault
AnswersB, E

Granular permissions

Why this answer

The correct answers are B and E. Option B: Using access control lists (ACLs) in Azure Data Lake Storage Gen2 allows granular permissions at the directory and file level, enabling you to restrict access to sensitive data. Option E: Enabling encryption at rest with customer-managed keys in Azure Key Vault provides an additional layer of security by allowing you to manage your own encryption keys.

Option A is incorrect because public network access from all networks exposes data to potential threats; it should be restricted. Option C is incorrect as anonymous access should always be disabled for sensitive data. Option D is incorrect because soft delete should be enabled to protect against accidental data deletion or corruption.

Exam trap

Candidates may confuse access control methods or overlook that customer-managed keys add an extra security layer. They might also assume that disabling soft delete is a security measure, but it actually reduces data protection.

Full explanation →
394
MCQeasy

Which Azure storage solution is best suited for storing large volumes of unstructured data, such as log files and media files, and supports both hierarchical namespace and POSIX-like access control lists?

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

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with POSIX-like access control lists (ACLs) on top of Azure Blob Storage. This makes it ideal for storing large volumes of unstructured data (e.g., log files, media files) while supporting fine-grained, POSIX-compliant permissions and directory-level operations that are essential for big data analytics workloads.

Exam trap

The trap here is that candidates often choose Azure Blob Storage because it is the underlying storage for ADLS Gen2, but they overlook the key differentiators—hierarchical namespace and POSIX ACLs—that are exclusive to ADLS Gen2 and not available in standard Blob Storage.

Why the other options are wrong

A

Blob Storage supports unstructured data but does not provide a hierarchical namespace or POSIX ACLs by default.

C

Azure Files is for SMB file shares, not optimized for large-scale unstructured data.

D

Azure SQL Database is a relational database for structured data, not for unstructured data.

Full explanation →
395
MCQmedium

Your company uses Azure Synapse Analytics to run a data warehouse. You have a dedicated SQL pool with a hash-distributed fact table named Sales. The distribution column is ProductID. You notice that queries against the Sales table are slow due to data skew. After analysis, you find that a few products (e.g., ProductID 100, 200) account for 80% of the rows. You need to optimize query performance without redesigning the entire table. You also need to minimize data movement during queries. Which action should you take?

A.Change the distribution to round-robin.
B.Increase the number of distributions to 120.
C.Change the distribution to replicate for the Sales table.
D.Create non-clustered indexes on the ProductID column.
AnswerA

Correct. Round-robin distribution evenly distributes rows, eliminating skew. Although it can increase data movement during joins, it is an effective fix for severe skew without redesign.

Why this answer

Round-robin distribution distributes data evenly across distributions, eliminating skew. However, it may increase data movement for joins. Given the severe skew, round-robin is a reasonable trade-off.

Option B is wrong because increasing the number of distributions does not fix skew. Option C is wrong because changing to replicate distribution is not suitable for large fact tables. Option D is wrong because creating non-clustered indexes does not address distribution skew.

Full explanation →
396
MCQhard

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

A.Enable staging and use PolyBase as the copy method for the sink.
B.Change the copy behavior to 'sequential' to reduce load on the source.
C.Increase the degree of copy parallelism (DOP) to the maximum value supported.
D.Split the source data into multiple smaller files and use multiple copy activities running in parallel.
AnswerA

Staging with PolyBase dramatically improves performance for large data loads.

Why this answer

Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Exam trap

The trap here is that candidates often assume increasing parallelism (DOP) or splitting data into multiple activities is always better, but they overlook that PolyBase's MPP integration with Synapse is the only option that fundamentally changes the data loading mechanism from a serial to a parallel bulk operation.

How to eliminate wrong answers

Option B is wrong because changing the copy behavior to 'sequential' would reduce parallelism and increase the copy duration, not reduce it. Option C is wrong because increasing the degree of copy parallelism (DOP) to the maximum value supported can cause resource contention and throttling on the source Azure SQL Database, often leading to diminishing returns or even slower performance. Option D is wrong because splitting the source data into multiple smaller files and using multiple copy activities running in parallel would require additional orchestration and staging, and without PolyBase or staging, each copy activity would still use the slow row-by-row INSERT method, making it less effective than a single PolyBase-based load.

Full explanation →
397
Multi-Selectmedium

Which TWO of the following are recommended practices for designing a data storage solution using Azure Data Lake Storage Gen2?

Select 2 answers
A.Enable soft delete to protect against accidental deletion
B.Use Kerberos authentication for the storage account
C.Use a partition strategy that groups related data together
D.Store all files in a single directory for simplicity
E.Enable anonymous public access for ease of use
AnswersA, C

Soft delete provides a recovery window for deleted data.

Why this answer

Enabling soft delete on Azure Data Lake Storage Gen2 protects against accidental deletion by retaining deleted data for a specified retention period. This allows recovery of blobs or snapshots that were deleted, overwritten, or modified, which is a critical data protection practice for enterprise storage solutions.

Exam trap

Microsoft often tests the misconception that Kerberos is the primary authentication method for Azure Data Lake Storage Gen2, but the correct protocol is Azure AD OAuth 2.0, and candidates may confuse Gen2 with Gen1 or on-premises Hadoop.

Full explanation →
398
MCQeasy

Refer to the exhibit. You have created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. When you query the external table, you get an error that the external table is not accessible. What should you check first?

A.Check that the external table's LOCATION path is relative to the container and does not start with a slash.
B.Verify that the serverless SQL pool has been granted the 'Storage Blob Data Reader' role on the storage account.
C.Ensure that the external file format is correctly referencing the Parquet format.
D.Confirm that the Snappy compression codec is supported by the serverless SQL pool.
AnswerB

The serverless SQL pool needs read permissions on the storage account to access the data.

Why this answer

The error 'external table is not accessible' in Azure Synapse serverless SQL pool typically indicates an authorization failure when the SQL pool attempts to read the underlying Parquet files in Azure Data Lake Storage Gen2. Serverless SQL pool uses its own service identity to access storage, and it must be granted the 'Storage Blob Data Reader' role on the storage account at the storage account scope to have read permissions. Without this role assignment, the SQL pool cannot authenticate to the storage, resulting in the access error.

Exam trap

The trap here is that candidates often confuse 'external table not accessible' with file path or format issues, but the error message specifically points to a permissions/authorization problem, not a configuration or syntax error.

How to eliminate wrong answers

Option A is wrong because the LOCATION path in an external table for serverless SQL pool must be relative to the container and should not start with a slash; however, an incorrect path format would cause a 'file not found' or 'path does not exist' error, not an 'external table is not accessible' error which is specifically about permissions. Option C is wrong because if the external file format incorrectly references the Parquet format, the error would be about format mismatch or parsing failure (e.g., 'Cannot parse file'), not about table accessibility. Option D is wrong because Snappy compression is fully supported by serverless SQL pool for Parquet files; an unsupported codec would cause a decompression error, not an access-denied error.

Full explanation →
399
Multi-Selectmedium

Which TWO Azure services can be used to implement a polyglot persistence architecture for an e-commerce application that requires both a relational database for orders and a document database for product catalogs?

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

Suitable for relational data like orders.

Why this answer

Azure SQL Database is a relational database service that supports ACID transactions and structured querying, making it ideal for storing and managing e-commerce order data with strong consistency and referential integrity. Azure Cosmos DB is a multi-model NoSQL database that provides document database capabilities with flexible schemas and low-latency access, perfectly suited for product catalogs that require high read throughput and schema evolution. Together, they enable a polyglot persistence architecture by using the best storage model for each workload.

Exam trap

The trap here is that candidates often confuse Azure Table Storage with a document database, but Table Storage is a key-value store without native JSON support or rich querying, whereas Cosmos DB provides a true document database with SQL API and indexing.

Full explanation →
400
MCQhard

You are troubleshooting a slow-running pipeline in Azure Data Factory. The pipeline copies data from an on-premises SQL Server to Azure Synapse Analytics using a self-hosted integration runtime. The copy activity is using the 'Auto' copy method. You notice that network bandwidth is limited. Which configuration change would most likely improve performance?

A.Enable staging using Azure Blob Storage and use PolyBase to load into Synapse
B.Increase the Data Integration Units (DIU) for the copy activity
C.Change the copy method to 'Bulk insert'
D.Set the Fault Tolerance option to skip incompatible rows
AnswerA

Staging improves performance by using parallel uploads to Blob Storage.

Why this answer

When network bandwidth is limited, staging data in Azure Blob Storage allows the copy activity to use PolyBase, which leverages Azure's internal high-speed network for the final load into Synapse. This bypasses the constrained on-premises-to-cloud link for the bulk of the data transfer, significantly improving throughput.

Exam trap

The trap here is that candidates assume increasing DIU or changing the copy method directly speeds up data movement, when in fact the real bottleneck is the network link, and only staging with PolyBase offloads the heavy data transfer to Azure's internal network.

How to eliminate wrong answers

Option B is wrong because Data Integration Units (DIU) control parallelism within the copy activity but do not address the underlying network bandwidth bottleneck; increasing DIU on a constrained link can actually worsen contention. Option C is wrong because 'Bulk insert' is the default method for loading into Synapse and does not change the data path; it still sends all data over the limited network connection. Option D is wrong because Fault Tolerance skips incompatible rows to avoid failures, but it has no impact on data transfer speed or network utilization.

Full explanation →
401
MCQeasy

You are monitoring an Azure Data Factory pipeline that runs daily. You notice that some runs are failing due to transient network errors. You want to automatically retry the failed activities with a 5-minute delay, up to 3 times. How should you configure this?

A.Set the pipeline's 'Concurrency' to 3 and 'Retry' to 1.
B.Leave the default settings as they are because Azure Data Factory automatically retries failed activities 3 times.
C.On each activity, set 'Retry' to 3 and 'Retry interval' to 00:05:00.
D.Configure a 'Retry' policy on the pipeline itself, setting maximum retries to 3 and retry interval to 5 minutes.
AnswerC

Correct: Activities have individual retry settings. Setting retry to 3 with 5-minute interval achieves the requirement.

Why this answer

Azure Data Factory activities have a 'Retry' property that can be set to 3, and 'Retry interval' to 00:05:00. Option A is too low (1 retry). Option B is wrong because retry is per activity, not at pipeline level.

Option D is wrong because the default retry is 0.

Full explanation →
402
MCQeasy

You run the above query on a table named 'visits' in a dedicated SQL pool. The table has 1 billion rows and is hash-distributed on user_id. The query takes a long time. What is the most likely reason?

A.The query uses a date filter which cannot be pushed down to the distribution.
B.The table is hash-distributed on user_id, but the query uses a different column for aggregation.
C.The table should use a replicated distribution instead of hash distribution.
D.COUNT(DISTINCT) operations are expensive because they require data movement across distributions.
AnswerD

COUNT(DISTINCT) needs to combine distinct values from all distributions.

Why this answer

In a dedicated SQL pool, COUNT(DISTINCT) is inherently expensive because it requires all distinct values to be gathered across distributions before counting. Since the table is hash-distributed on user_id, the distinct count on a different column (likely visit_date or another attribute) forces data shuffling across all distributions to ensure uniqueness, causing significant performance degradation.

Exam trap

The trap here is that candidates often blame the distribution key mismatch (Option B) or filter pushdown (Option A), overlooking the fact that COUNT(DISTINCT) forces a global data movement step regardless of distribution strategy.

How to eliminate wrong answers

Option A is wrong because date filters can be pushed down to distributions in dedicated SQL pool via partition elimination or predicate pushdown, so this is not the primary bottleneck. Option B is wrong because aggregation on a different column than the distribution key does not inherently cause slowness; hash distribution supports aggregation on any column, though it may require partial aggregation per distribution. Option C is wrong because replicated distribution is typically beneficial for small dimension tables (under 2 GB), not for a 1-billion-row fact table, and would cause massive storage overhead and maintenance issues.

Full explanation →
403
MCQeasy

A logistics company needs to store delivery tracking data that is updated frequently by multiple services. The solution must support transactions across multiple documents and provide real-time analytics. Which Azure service should you recommend?

A.Azure Table Storage
B.Azure Cosmos DB with SQL API
C.Azure Data Lake Storage Gen2
D.Azure Service Bus
AnswerB

Cosmos DB supports transactions via stored procedures and analytical store for analytics.

Why this answer

Azure Cosmos DB with SQL API is the correct choice because it provides multi-document transaction support (ACID within a logical partition) and real-time analytics via its change feed and integrated analytical store. This meets the requirement for frequent updates from multiple services while enabling low-latency reads for analytics, unlike other Azure storage options that lack transactional guarantees across documents or real-time query capabilities.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's single-entity transactions with multi-document support, or mistakenly think Azure Data Lake Storage Gen2 can handle transactional updates, when it is designed for append-heavy, analytical workloads.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage does not support multi-document transactions; it only offers single-entity transactions and lacks the ability to perform ACID operations across multiple documents. Option C is wrong because Azure Data Lake Storage Gen2 is optimized for large-scale batch analytics and data lake workloads, not for transactional updates or real-time analytics on frequently updated data. Option D is wrong because Azure Service Bus is a message broker for decoupling services and asynchronous communication, not a data store for transactional or analytical workloads.

Full explanation →
404
MCQmedium

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

A.Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations
B.Cache the data in memory after reading
C.Use bucketing with a fixed number of buckets
D.Partition the data by a high-cardinality column
AnswerA

Z-ordering co-locates related data, reducing data shuffling.

Why this answer

Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Exam trap

The trap here is that candidates often confuse partitioning (which can increase shuffle) with Z-ordering (which reduces shuffle by improving data locality without creating new partitions), leading them to choose bucketing or high-cardinality partitioning as a solution for shuffle minimization.

How to eliminate wrong answers

Option B is wrong because caching data in memory after reading only speeds up repeated access to the same data but does not reduce shuffle operations during transformations or aggregations; shuffle is caused by data movement across partitions, not by I/O latency. Option C is wrong because bucketing with a fixed number of buckets can actually increase shuffle operations if the bucketing columns do not align with the join or aggregation keys, and it does not inherently minimize shuffle; it is primarily used for optimizing joins and aggregations when the number of buckets matches the cluster parallelism. Option D is wrong because partitioning by a high-cardinality column (e.g., a column with many unique values) creates many small partitions, which leads to excessive shuffle overhead and task scheduling inefficiency, increasing rather than minimizing shuffle operations.

Full explanation →
405
MCQhard

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to an Azure Synapse Analytics dedicated SQL pool. The pipeline uses a staging Azure Blob Storage account. Recently, the pipeline has been failing with timeout errors. You need to ensure the pipeline completes successfully within the scheduled window. What should you do?

A.Increase the staging blob's copy timeout and enable parallel copy in the copy activity
B.Move the staging storage to Azure Data Lake Storage Gen2
C.Increase the dedicated SQL pool's data warehouse units (DWU)
D.Use PolyBase instead of staging for data loading
AnswerA

Increasing timeout allows more time for large data transfers, and parallel copy improves throughput.

Why this answer

The timeout errors occur because the default copy timeout for staging blob storage is insufficient for large data volumes or slow network conditions. Increasing the staging blob's copy timeout and enabling parallel copy in the copy activity directly addresses this by allowing more time for data transfer and leveraging multiple concurrent connections to improve throughput, ensuring the pipeline completes within the scheduled window.

Exam trap

The trap here is that candidates may confuse scaling the destination (Synapse DWUs) or changing storage tiers with fixing the root cause of timeout errors, which is often a configuration issue in the copy activity's staging settings rather than a capacity problem.

How to eliminate wrong answers

Option B is wrong because moving to Azure Data Lake Storage Gen2 does not inherently resolve timeout issues; it provides hierarchical namespace and POSIX permissions but does not change the copy timeout or parallelism settings. Option C is wrong because increasing DWUs scales the dedicated SQL pool's compute resources for query performance, not for data ingestion timeout issues during staging. Option D is wrong because PolyBase is an alternative loading method that bypasses staging but does not address timeout errors in the existing staging-based pipeline; it would require architectural changes and may introduce different performance characteristics.

Full explanation →
406
MCQmedium

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data from multiple sources. You need to ensure that the data is encrypted at rest and in transit, and that access to the data is audited. Which combination of Azure services should you use?

A.Azure Data Lake Storage (encrypted at rest), Azure Synapse Analytics (TDE and SSL), and Microsoft Purview
B.Azure Blob Storage (encrypted at rest), Azure HDInsight, and Azure Log Analytics
C.Azure SQL Database (TDE and SSL), Azure Analysis Services, and Microsoft Purview
D.Azure Data Lake Storage (encrypted at rest), Microsoft Fabric, and Azure Monitor
AnswerA

Azure Storage provides encryption at rest; Synapse supports TDE for at-rest and SSL for in-transit; Purview provides data lineage and auditing.

Why this answer

Azure Data Lake Storage provides encryption at rest using Azure Storage Service Encryption (SSE) with 256-bit AES, and Azure Synapse Analytics supports Transparent Data Encryption (TDE) for at-rest encryption and SSL/TLS for in-transit encryption. Microsoft Purview enables data governance and auditing by capturing lineage, classification, and access activity logs, meeting the compliance requirements for sensitive financial data.

Exam trap

The trap here is that candidates often confuse Azure Monitor or Log Analytics with Microsoft Purview for auditing, but Microsoft Purview is the dedicated service for data governance, classification, and access auditing, while Azure Monitor is for operational monitoring and does not provide data-level audit trails.

How to eliminate wrong answers

Option B is wrong because Azure HDInsight does not natively enforce encryption at rest for data stored in its managed disks or external storage without additional configuration, and Azure Log Analytics focuses on monitoring and diagnostics rather than auditing data access at the granularity required for sensitive customer data. Option C is wrong because Azure Analysis Services does not provide built-in auditing of data access at the storage level; it is an analytical engine that relies on underlying data sources for encryption, and Microsoft Purview is correctly included but the combination lacks a scalable storage layer for multiple sources. Option D is wrong because Microsoft Fabric is a unified analytics platform that does not inherently provide the same level of granular access auditing as Microsoft Purview, and Azure Monitor is designed for infrastructure monitoring, not data access auditing.

Full explanation →
407
Multi-Selectmedium

Which TWO Azure services can be used to implement a data lake architecture for storing structured, semi-structured, and unstructured data?

Select 2 answers
A.Azure Blob Storage with hierarchical namespace
B.Azure SQL Database
C.Azure Synapse Analytics
D.Azure Cosmos DB
E.Azure Data Lake Storage Gen2
AnswersA, E

Can function as a data lake.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Azure Blob Storage and adds a hierarchical namespace, enabling a true data lake architecture. It supports storing structured, semi-structured, and unstructured data at scale, with POSIX-like access control lists and directory-level operations. Azure Blob Storage with hierarchical namespace enabled is essentially the same service as ADLS Gen2, providing the same data lake capabilities.

Exam trap

The trap here is that candidates often confuse Azure Synapse Analytics as a storage service for data lakes, when in fact it is a query and analytics engine that relies on ADLS Gen2 or other storage for the underlying data.

Full explanation →
408
MCQmedium

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

A.Implement workload management to prioritize queries.
B.Use OPENROWSET with explicit file path filtering.
C.Enable result-set caching on the serverless SQL pool.
D.Create materialized views on the Parquet files.
AnswerB

OPENROWSET with path filtering prunes partitions and improves performance.

Why this answer

Using OPENROWSET with explicit file path filtering allows partition pruning by limiting the scan to the specified folders, thus improving query performance for frequently accessed partitions. Option A is wrong because workload management is a feature for dedicated SQL pool, not serverless. Option C is wrong because result-set caching is not supported in serverless SQL pool; it is a feature of dedicated SQL pool.

Option D is wrong because materialized views are not supported in serverless SQL pool.

Full explanation →
409
MCQmedium

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that some queries are taking longer than expected. You need to identify queries that are experiencing significant memory pressure. Which dynamic management view (DMV) should you query?

A.sys.dm_pdw_exec_requests
B.sys.dm_pdw_wait_stats
C.sys.dm_pdw_query_stats_xe
D.sys.dm_pdw_nodes_os_performance_counters
AnswerA

This DMV includes memory_grant and memory_used columns to assess memory pressure.

Why this answer

Sys.dm_pdw_exec_requests provides information about queries including their memory grant size, which can be used to identify queries experiencing memory pressure. Option B is incorrect because sys.dm_pdw_wait_stats shows wait statistics, not memory pressure. Option C is incorrect because sys.dm_pdw_query_stats_xe is for extended events, not directly for memory grants.

Option D is incorrect because sys.dm_pdw_nodes_os_performance_counters shows OS-level performance counters, not query-level memory information.

Full explanation →
410
MCQeasy

You need to design a data storage solution for a batch processing pipeline that processes petabytes of data daily. The data is stored in Parquet format and must be accessible by both Azure Databricks and Azure Synapse Analytics. Which storage solution should you recommend?

A.Azure Data Lake Storage Gen2
B.Azure Files
C.Azure SQL Database
D.Azure Blob Storage
AnswerA

Provides hierarchical namespace, high throughput, and is compatible with Databricks and Synapse.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, providing native POSIX-like access control and high throughput for petabyte-scale batch processing. Both Azure Databricks and Azure Synapse Analytics have optimized connectors for ADLS Gen2 that leverage the hierarchical namespace for efficient partition pruning and file listing, which is critical for Parquet-based analytics at this scale.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage (flat namespace) with ADLS Gen2 (hierarchical namespace), assuming both are equivalent for big data analytics, but the hierarchical namespace is a critical differentiator for performance at petabyte scale in batch pipelines.

How to eliminate wrong answers

Option B (Azure Files) is wrong because it is designed for SMB-based file sharing with low-latency access for small-to-medium workloads, not for petabyte-scale batch analytics, and lacks the throughput and native integration with Spark and Synapse pipelines required for Parquet data. Option C (Azure SQL Database) is wrong because it is a relational OLTP store optimized for transactional queries and small row-based operations, not for storing and processing petabytes of Parquet files in a batch pipeline. Option D (Azure Blob Storage) is wrong because while it can store Parquet files, it lacks a hierarchical namespace, which forces Azure Databricks and Synapse to use slower flat namespace listing operations (e.g., ListBlobs) that degrade performance at petabyte scale compared to ADLS Gen2's directory-aware operations.

Full explanation →
411
MCQhard

Your Azure Synapse Analytics pipeline uses PolyBase to load data from Azure Blob Storage into a dedicated SQL pool. The load is slow and suffers from high latency. Which optimization should you apply first?

A.Split the source files into smaller chunks.
B.Use a round-robin distribution for the staging table.
C.Increase the DWU (Data Warehouse Units) of the SQL pool.
D.Create clustered columnstore indexes on the staging table.
AnswerB

Round-robin distribution avoids data movement during load, which is the primary cause of latency in PolyBase operations.

Why this answer

Using a round-robin distribution for staging tables minimizes data movement during PolyBase loads, reducing latency. Option A is not the first optimization because splitting files can help parallelism but may not directly address high latency. Option C is not the first optimization because increasing DWU can improve performance but incurs additional cost and is not specific to the load process.

Option D is incorrect because clustered columnstore indexes are beneficial for query performance, not for import speed.

Full explanation →
412
MCQhard

You are designing a data ingestion pipeline for Azure Data Lake Storage Gen2 using Azure Databricks. The source is an on-premises SQL Server database with incremental changes captured via change data capture (CDC). The requirement is to ensure exactly-once semantics for each row while minimizing latency. Which approach should you recommend?

A.Use Azure Data Factory with a tumbling window trigger to copy data every 5 minutes.
B.Use PolyBase to create external tables and run T-SQL MERGE statements.
C.Use Azure Databricks Auto Loader with COPY INTO command.
D.Use Spark Structured Streaming in Azure Databricks to read CDC changes and write to Delta Lake.
AnswerD

Structured Streaming with Delta Lake ensures exactly-once and low latency.

Why this answer

Spark Structured Streaming in Azure Databricks, combined with Delta Lake, provides exactly-once semantics through checkpointing and transaction logs, and it is designed for low-latency streaming ingestion—ideal for CDC data. Option A is incorrect: Azure Data Factory with a tumbling window is a batch-based approach and cannot achieve exactly-once semantics for streaming data with low latency. Option B is incorrect: PolyBase is used for bulk loading data into Azure Synapse, not for streaming CDC ingestion.

Option C is incorrect: Auto Loader and COPY INTO are designed for incremental batch loads of files, not for streaming row-level changes from a database.

Full explanation →
413
MCQeasy

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to implement a security solution that prevents unauthorized access from the public internet while allowing access from specific Azure services and on-premises networks. Which feature should you configure?

A.Use private endpoints for all storage accounts.
B.Enable Microsoft Entra ID authentication only.
C.Configure firewall rules and virtual network service endpoints.
D.Use shared access signatures (SAS) with IP restrictions.
AnswerC

Firewall rules and service endpoints restrict access from public internet while allowing trusted services and VNets.

Why this answer

Configuring firewall rules and virtual network service endpoints allows you to restrict access to your Azure Data Lake Storage Gen2 account to only traffic originating from specific virtual networks and trusted Azure services, while blocking all public internet traffic. This provides a network-level security boundary that meets the requirement of preventing unauthorized public access while permitting access from on-premises networks (via VPN/ExpressRoute) and specific Azure services.

Exam trap

The trap here is that candidates often confuse network-level access controls (firewall and service endpoints) with identity-based controls (Entra ID) or delegation mechanisms (SAS), mistakenly thinking authentication alone can block public internet traffic.

How to eliminate wrong answers

Option A is wrong because private endpoints use Azure Private Link to assign a private IP from a virtual network to the storage account, but they do not inherently block public internet access; you must also disable public network access separately, and they do not directly allow access from on-premises networks without additional VPN/ExpressRoute configuration. Option B is wrong because enabling Microsoft Entra ID authentication only controls identity-based access (who can authenticate), not network-level access (where traffic can originate from); it does not prevent public internet traffic from reaching the storage endpoint. Option D is wrong because shared access signatures (SAS) with IP restrictions provide time-limited, delegated access with source IP constraints, but they are designed for granular per-request authorization, not as a permanent network security boundary to block all public internet traffic while allowing Azure services and on-premises networks.

Full explanation →
414
Matchingmedium

Match each storage redundancy option to its description in Azure Storage.

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

Concepts
Matches

Three synchronous copies within a single data center

Three copies across multiple availability zones in a region

Geo-redundant storage with read access in secondary region

Geo-zone-redundant storage with read access in secondary region

Why these pairings

Common redundancies: LRS uses local replication, ZRS uses zonal replication, and GRS adds a secondary region. Distractors mix these concepts.

Full explanation →
415
MCQmedium

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

A.The core count of 8 is insufficient for the data flow.
B.The TTL setting is too low, causing the cluster to be recreated frequently.
C.The AutoResolve location cannot be used for Managed IR.
D.The integration runtime type should be 'Self-Hosted' for data flows.
AnswerB

Low TTL leads to frequent cluster teardown and startup delays.

Why this answer

The first data flow execution takes a long time because the TTL (time-to-live) setting of 10 minutes causes the cluster to be deallocated shortly after the previous run. When a new data flow starts after the TTL expires, a new cluster must be provisioned from scratch, which adds significant startup latency. A higher TTL (e.g., 60 minutes) would keep the cluster warm for subsequent executions, reducing cold-start delays.

Exam trap

The trap here is that candidates may attribute the slow first execution to insufficient compute resources (Option A) rather than recognizing that the TTL setting directly controls cluster reuse and cold-start latency.

How to eliminate wrong answers

Option A is wrong because the core count of 8 is a default value and is not inherently insufficient; the issue is cluster startup time, not compute capacity. Option C is wrong because 'AutoResolve' is a valid and recommended location setting for a Managed integration runtime in Azure Synapse, as it automatically selects the optimal region. Option D is wrong because a Self-Hosted IR is not required for data flows; Managed IR is the correct and supported runtime type for executing data flows in Azure Synapse.

Full explanation →
416
MCQhard

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

A.The dependency condition should be 'Completed' instead of 'Succeeded'.
B.The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
C.The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.
D.The CopyData activity runs before the WatermarkQuery activity completes.
AnswerC

The copy query filters for rows where LastModified > NewWatermark, but NewWatermark is the maximum, so no rows satisfy the condition. The previous watermark should be stored and used.

Why this answer

The WatermarkQuery activity retrieves the maximum LastModified value from the source. If the CopyData activity's source query filters for rows where LastModified equals that value (e.g., using a parameter reference), it will only copy rows with that exact timestamp. In an incremental load scenario, the correct filter should be LastModified greater than the previous watermark value, not equal to the current maximum.

Since the current maximum is the highest timestamp, no rows will have a timestamp greater than it, resulting in zero rows copied. Option A is wrong because the dependency condition 'Succeeded' is appropriate and the pipeline succeeded. Option B is wrong because the WatermarkQuery activity succeeded (no failure indicated).

Option D is wrong because the WatermarkQuery activity runs before the CopyData activity due to the dependency, so no ordering issue exists.

Full explanation →
417
Multi-Selectmedium

Which TWO factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for migrating a legacy application? (Choose two.)

Select 2 answers
A.Support for active geo-replication
B.Authentication using Microsoft Entra ID
C.Need for SQL Server Agent jobs
D.Requirement for cross-database queries
E.Integration with Azure VNet
AnswersC, D

A is correct because SQL Database does not have SQL Agent; Managed Instance does.

Why this answer

SQL Managed Instance includes full support for SQL Server Agent, which is essential for legacy applications that rely on scheduled jobs, alerts, or automation. Azure SQL Database does not support SQL Server Agent, making Managed Instance the appropriate choice when this feature is required.

Exam trap

The trap here is that candidates often assume VNet integration (Option E) is exclusive to Managed Instance, but Azure SQL Database also supports VNet integration via private endpoints, making it a non-differentiating factor for this migration decision.

Full explanation →
418
Multi-Selectmedium

Which TWO actions should you take to secure data in Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Select 2 answers
A.Use PolyBase to load data from external sources.
B.Enable result-set caching for query performance.
C.Configure workload classification for resource governance.
D.Apply dynamic data masking (DDM) to obfuscate sensitive data.
E.Implement row-level security (RLS) to restrict data access.
AnswersD, E

DDM hides sensitive data from non-privileged users.

Why this answer

The correct answers are D and E. Dynamic data masking (DDM) obfuscates sensitive data, and row-level security (RLS) restricts data access at the row level. Option A is incorrect because PolyBase is used for data loading, not security.

Option B is incorrect because result-set caching improves performance but does not secure data. Option C is incorrect because workload classification governs resource allocation, not security.

Full explanation →
419
MCQeasy

You need to store data that is rarely accessed but must be retained for 10 years for compliance. The data will be accessed occasionally for audits. Which Azure storage tier is the most cost-effective?

A.Cool storage tier
B.Hot storage tier
C.Archive storage tier
D.Premium storage tier
AnswerA

Cool tier is designed for infrequently accessed data with lower storage cost.

Why this answer

The Cool storage tier is designed for data that is infrequently accessed and stored for at least 30 days, making it a cost-effective choice for compliance data accessed occasionally over a 10-year period. It offers lower storage costs than Hot tier while still providing low-latency access for audits, unlike Archive which requires hours to rehydrate.

Exam trap

The trap here is that candidates often choose Archive tier for long-term retention without considering the access latency requirement for audits, assuming 'rarely accessed' means never needing quick access.

How to eliminate wrong answers

Option B (Hot storage tier) is wrong because it is optimized for frequent access and has higher storage costs, making it unnecessarily expensive for rarely accessed compliance data. Option C (Archive storage tier) is wrong because although it has the lowest storage cost, it requires a rehydration process that can take up to 15 hours to access data, which is impractical for occasional audit access. Option D (Premium storage tier) is wrong because it is designed for high-performance, low-latency workloads (e.g., VMs, databases) and has the highest cost, making it unsuitable for rarely accessed archival data.

Full explanation →
420
MCQhard

Your Azure Synapse Analytics workspace uses serverless SQL pools for ad-hoc querying. Users report that queries are slow. You examine the execution plan and see that the query scans multiple partitions in the openrowset. What is the best way to improve performance?

A.Increase the MAXDOP setting
B.Create materialized views on the external tables
C.Partition the underlying data by a frequently filtered column
D.Add a WHERE clause on the partition column
AnswerD

Filtering on partition column enables partition elimination, reducing data scanned.

Why this answer

In serverless SQL pools, performance is improved by file pruning, which reduces the amount of data scanned. Adding a WHERE clause on the partition column allows the query engine to skip irrelevant partitions, thus reducing scan size. Option A is incorrect because MAXDOP controls parallelism, not data pruning.

Option B is incorrect because materialized views are not supported in serverless SQL pools. Option C is incorrect because partitioning the underlying data helps, but the question asks for the best way to improve performance given the current query behavior; adding a WHERE clause on the partition column is the most direct and effective solution.

Full explanation →
421
MCQeasy

You need to monitor resource utilization for an Azure Synapse Analytics dedicated SQL pool. Which Azure Monitor metric shows the percentage of allocated DWU being used?

A.Memory percentage
B.Data IO percentage
C.CPU percentage
D.DWU used
AnswerD

This metric shows the percentage of allocated DWU being consumed.

Why this answer

The 'DWU used' metric shows the percentage of allocated DWU being consumed. Option A is incorrect because 'Memory percentage' is not the metric for DWU utilization. Option B is incorrect because 'Data IO percentage' is not the primary metric for overall DWU usage.

Option C is incorrect because 'CPU percentage' does not directly represent DWU consumption.

Full explanation →
422
MCQeasy

You are using Azure Stream Analytics to process real-time data from an event hub and output to Azure Synapse Analytics. You need to ensure exactly-once delivery semantics to the output. What should you configure?

A.Set the input to 'Exactly Once' consumption mode.
B.Configure event ordering and late arrival policies.
C.Enable checkpointing in the query.
D.Set the output to 'Exactly Once' delivery mode.
AnswerD

Correct: Azure Stream Analytics provides exactly-once semantics when configured on the output.

Why this answer

Azure Stream Analytics supports exactly-once delivery to Azure Synapse Analytics by configuring the output to 'Exactly Once' delivery mode. Option A is incorrect because the input consumption mode does not control output delivery guarantees. Option B is incorrect because event ordering and late arrival policies handle timing, not delivery semantics.

Option C is incorrect because checkpointing is for managing internal query state, not for ensuring exactly-once output.

Full explanation →
423
MCQmedium

You are designing a data processing pipeline in Azure Synapse Analytics that ingests streaming data from Azure Event Hubs and stores it in a dedicated SQL pool. The data must be available for querying within 5 minutes of ingestion. Which processing approach should you recommend?

A.Use Azure Data Factory with a tumbling window trigger set to 5 minutes.
B.Use Azure Stream Analytics with a dedicated SQL pool output and configure a 1-minute window.
C.Use PolyBase to load data from Event Hubs into the dedicated SQL pool every 5 minutes.
D.Use Spark Structured Streaming in Azure Synapse to write micro-batches every 5 minutes.
AnswerB

Stream Analytics provides sub-minute latency and is designed for real-time ingestion into Synapse dedicated SQL pool.

Why this answer

Azure Stream Analytics is purpose-built for real-time stream processing and can output directly to a dedicated SQL pool. By configuring a 1-minute window, you ensure data is materialized in the SQL pool well within the 5-minute SLA, meeting the latency requirement with headroom.

Exam trap

The trap here is that candidates confuse batch-oriented tools (Data Factory, PolyBase) or general-purpose streaming frameworks (Spark Structured Streaming) with the dedicated, low-latency stream processing service (Stream Analytics) that is optimized for sub-minute latency to Synapse SQL pools.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory with a tumbling window trigger is a batch-oriented orchestration tool, not a streaming engine; it cannot process Event Hubs data in near real-time and introduces at least 5 minutes of latency before the trigger even fires. Option C is wrong because PolyBase is a bulk-load technology for reading external data sources like Azure Blob Storage or Data Lake, not for ingesting streaming data from Event Hubs; it cannot connect to Event Hubs directly. Option D is wrong because Spark Structured Streaming in Synapse writes micro-batches every 5 minutes, which meets the 5-minute SLA only at the boundary; any processing delay or checkpoint overhead could push latency beyond the requirement, and it lacks the native, low-latency integration with dedicated SQL pool that Stream Analytics provides.

Full explanation →
424
MCQmedium

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that some queries are experiencing high wait times due to concurrency slots being exhausted. You need to optimize the workload to reduce contention. Which three actions should you take? (Select three.)

A.Increase the data warehouse service level (DWU).
B.Create workload groups with different importance levels.
C.Configure workload isolation to limit the amount of resources a workload group can use.
D.Use workload classification to assign queries to appropriate workload groups.
E.Enable result-set caching for frequently executed queries.
AnswerB, C, D

Creating workload groups with different importance levels allows critical queries to be prioritized, reducing contention.

Why this answer

Options B, C, and D are correct. Workload groups with different importance levels allow you to prioritize critical queries, reducing contention. Workload isolation limits resources for a group, preventing resource-intensive queries from blocking others.

Workload classification routes queries to appropriate groups based on criteria. Option A is incorrect because increasing DWU increases concurrency slots but may raise costs. Option E is incorrect because result-set caching does not affect concurrency slot usage.

Full explanation →
425
Multi-Selectmedium

You have an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to prevent data exfiltration to unauthorized external IP addresses. Which TWO actions should you take?

Select 2 answers
A.Use private endpoints for the storage account
B.Enable Azure Firewall on the storage account
C.Use shared access signatures (SAS) with limited permissions
D.Configure storage firewall to allow only specific virtual networks
E.Enable geo-redundant storage (GRS)
AnswersA, D

Private endpoints ensure that all traffic to the storage account remains within the Microsoft backbone network, effectively preventing access from unauthorized external IPs.

Why this answer

Network security controls: enabling the storage firewall to allow only specific virtual networks (Option D) restricts access to traffic originating from those VNets, blocking external IPs. Using private endpoints (Option A) ensures that all traffic to the storage account stays within the Microsoft backbone network, preventing exposure to the public internet. Option B is incorrect because Azure Firewall is a separate service, not a storage account setting.

Option C (SAS) provides fine-grained access control but does not prevent exfiltration from external IPs. Option E (GRS) is for data durability, not security.

Exam trap

Candidates often confuse Azure Firewall (a network security service) with storage firewall settings. Also, they might think that restricting SAS tokens alone prevents exfiltration, but SAS does not block external IPs.

Full explanation →
426
MCQmedium

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

A.Change the distribution type to round-robin.
B.Update statistics on all columns used in joins.
C.Increase the number of distributions to 120.
D.Redistribute the fact table on the join column using hash distribution.
AnswerD

Redistributing the fact table on the join column using hash distribution ensures collocation of matching rows, directly reducing data movement.

Why this answer

Redistributing the fact table on the join column using hash distribution aligns the distribution key with the join column. This ensures that rows with the same join key value are stored on the same distribution, minimizing data movement during joins. Option A is wrong because round-robin distribution places rows randomly, which actually increases data movement for joins.

Option B is wrong because while updating statistics helps the optimizer choose better plans, it does not directly reduce data movement. Option C is wrong because increasing the number of distributions does not change the alignment of the distribution key with the join column, so data movement remains high.

Full explanation →
427
MCQmedium

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

A.The user does not have read permission on the table.
B.The table is not registered in the Spark metastore; it is only in the lakehouse.
C.The Parquet file format is incompatible with the Delta source.
D.The Delta table is corrupted.
AnswerB

In Synapse, lakehouse tables are stored in a separate catalog; the Spark session's default catalog may not include it, so the table is not found.

Why this answer

In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Exam trap

Microsoft often tests the misconception that a table existing in the lakehouse automatically makes it visible to Spark notebooks, when in fact the Spark metastore and lakehouse catalog are separate metadata layers that must be explicitly synchronized.

How to eliminate wrong answers

Option A is wrong because an AnalysisException for 'Table or view not found' is a metadata resolution error, not a permission error; a lack of read permission would typically produce a SecurityException or AccessDeniedException. Option C is wrong because Parquet and Delta are both columnar formats based on Parquet, and writing to Parquet from a Delta source does not cause incompatibility; the error occurs before any read/write operation begins. Option D is wrong because a corrupted Delta table would cause read failures (e.g., file not found, checksum mismatch) during data access, not a table-not-found error at the metadata level.

Full explanation →
428
MCQmedium

You are an Azure administrator. You apply the Azure Policy shown in the exhibit to a management group. What is the outcome of this policy?

A.It allows storage accounts only if they have a firewall rule.
B.It denies storage accounts that allow public network access.
C.It requires all storage accounts to use HTTPS only.
D.It denies the creation of any new storage account.
AnswerB

The policy denies when defaultAction equals Allow, meaning public access is allowed.

Why this answer

The policy denies the creation of storage accounts that allow public network access. Therefore, Option B is correct. Option A is incorrect because the policy does not require a firewall rule; it denies public access entirely.

Option C is incorrect because the policy does not address HTTPS enforcement. Option D is incorrect because the policy only denies storage accounts that allow public network access, not all storage accounts.

Full explanation →
429
MCQhard

You are a data engineer for a global e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. The environment includes a large fact table 'Sales' distributed by hash on 'CustomerID', and dimension tables 'Customer' (hash-distributed on 'CustomerID') and 'Product' (replicated). Recently, queries that join Sales and Customer are performing poorly. You run a query to check data skew on the Sales table and find that one distribution has 40% more rows than the average. Additionally, the Customer table has high data movement during joins. You need to optimize the performance of these joins. What should you do?

A.Change the distribution of the Customer table to replicated.
B.Increase the data warehouse performance level (DWU) to allocate more resources.
C.Change the distribution of the Sales table to round-robin.
D.Change the distribution key of the Sales table to 'ProductID' to align with the Product table.
AnswerA

Replicated tables avoid data movement for joins.

Why this answer

To change the distribution of the Customer table to replicated. In Azure Synapse Analytics dedicated SQL pool, replicated tables are small enough to be cached on each compute node, eliminating the need to move data during joins. Since Customer is a dimension table, it is likely small enough to benefit from replication.

This directly addresses the high data movement during joins. Option B (increasing DWU) would allocate more resources but does not fix the root cause of data movement and may only mask the issue. Option C (changing Sales to round-robin) is not recommended because fact tables in star schemas should be hash-distributed on a join key to minimize data movement.

Option D (changing Sales distribution key to ProductID) would not improve the join with Customer unless the join is on ProductID, but the problem is with the Sales-Customer join; changing the distribution key to ProductID would affect the join with Product, not Customer, and could worsen the skew issue.

Full explanation →
430
MCQhard

You need to assign permissions to a service principal so that it can write data to a specific container in Azure Data Lake Storage Gen2, but not delete blobs. The above JSON shows the built-in role 'Storage Blob Data Contributor'. The role includes delete permission in DataActions. What should you do?

A.Create a custom role that includes read and write DataActions but excludes the delete DataAction, then assign that custom role.
B.Assign the Storage Blob Data Contributor role and create a deny assignment that denies delete.
C.Assign the Storage Blob Data Contributor role and use ACLs to deny delete on the container.
D.Assign the Storage Blob Data Contributor role and remove the delete permission at the role assignment scope.
AnswerA

Custom roles allow fine-grained control over permissions.

Why this answer

Azure RBAC roles are all-or-nothing at the permission level; you cannot selectively remove a single DataAction from a built-in role at assignment time. The only way to grant write access without delete is to create a custom role that explicitly includes the required read and write DataActions (e.g., Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write) and omits the delete DataAction (Microsoft.Storage/storageAccounts/blobServices/containers/blobs/delete). This custom role is then assigned to the service principal at the container scope, ensuring it can write data but never delete blobs.

Exam trap

The trap here is that candidates mistakenly believe you can modify a built-in role's permissions at assignment time (Option D) or that ACLs can override RBAC permissions (Option C), when in reality Azure requires a custom role for such granular control.

How to eliminate wrong answers

Option B is wrong because deny assignments cannot be used to selectively block a specific DataAction within a role assignment; deny assignments are designed to block all assignments of a role at a higher scope, not to carve out individual permissions. Option C is wrong because ACLs in Azure Data Lake Storage Gen2 are applied to the data plane for user/group identities, but they cannot override an RBAC role assignment that explicitly grants delete permission; RBAC takes precedence over ACLs when both are present. Option D is wrong because built-in roles like Storage Blob Data Contributor have fixed DataActions that cannot be modified at the role assignment scope; you cannot 'remove' a permission from a built-in role during assignment.

Full explanation →
431
MCQmedium

You are designing an Azure Data Factory pipeline to ingest data from an on-premises SQL Server into Azure Synapse Analytics. The data must be encrypted in transit. Which integration runtime type should you use and what additional configuration is required?

A.Use Azure Integration Runtime with ExpressRoute
B.Use Self-hosted Integration Runtime with Azure VPN Gateway
C.Use Self-hosted Integration Runtime with a certificate for HTTPS
D.Use Azure Integration Runtime with a public endpoint
AnswerC

Self-hosted IR with certificate encrypts data in transit.

Why this answer

A self-hosted integration runtime is required to connect to on-premises networks. To encrypt data in transit, a certificate must be configured for HTTPS encryption. Option A is wrong because Azure Integration Runtime cannot directly access on-premises data sources.

Option B is incorrect because Azure VPN Gateway provides network-level connectivity but does not handle application-layer encryption; the self-hosted IR still needs a certificate. Option D is wrong because using a public endpoint does not ensure encryption in transit without additional configuration, and Azure IR still cannot access on-premises directly.

Full explanation →
432
MCQeasy

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

A.Run CONVERT TO DELTA on the underlying Parquet files.
B.Run OPTIMIZE to compact small files.
C.Run DESCRIBE HISTORY to analyze file sizes.
D.Run VACUUM to delete old files.
AnswerB

OPTIMIZE compacts small files into larger ones, improving read performance.

Why this answer

The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Exam trap

The trap here is that candidates confuse VACUUM (which cleans up old files) with OPTIMIZE (which compacts files), or think DESCRIBE HISTORY is a performance-tuning command rather than a diagnostic tool.

How to eliminate wrong answers

Option A is wrong because CONVERT TO DELTA is used to convert existing Parquet files into a Delta table format, not to compact small files within an already existing Delta table. Option C is wrong because DESCRIBE HISTORY only shows the transaction log of operations performed on the table, such as writes and compactions; it does not modify or optimize file sizes. Option D is wrong because VACUUM removes old, unreferenced data files that are no longer needed for time travel or rollback, but it does not compact or merge small files into larger ones.

Full explanation →
433
MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The sales table is partitioned by month and has a clustered columnstore index. Over time, the performance of queries filtering on a specific month has degraded. The data engineer suspects high rowgroup elimination. Which action should be taken to improve performance?

A.Change the table distribution to hash-distributed on the partition key.
B.Reorganize or rebuild the columnstore index on the table.
C.Drop and recreate the affected partitions.
D.Update statistics on the partitioned column.
AnswerB

Reorganizing or rebuilding consolidates small rowgroups, improving partition elimination.

Why this answer

Reorganizing or rebuilding the columnstore index compresses fragmented rowgroups and merges small rowgroups into optimal sizes (typically 102,400 rows per rowgroup). This directly addresses the degraded rowgroup elimination: when rowgroups are too small or fragmented, the engine cannot efficiently skip entire rowgroups during partition-level scans, causing more I/O and slower performance.

Exam trap

The trap here is that candidates confuse rowgroup elimination (a columnstore physical storage concept) with partition elimination (a table design concept), and incorrectly choose partition-related actions like dropping partitions or updating statistics instead of addressing the columnstore index fragmentation directly.

How to eliminate wrong answers

Option A is wrong because changing the distribution to hash-distributed on the partition key does not fix rowgroup fragmentation; distribution affects data movement across distributions, not the internal rowgroup structure of columnstore indexes. Option C is wrong because dropping and recreating affected partitions is an overly aggressive operation that drops data and requires reloading; it does not target the root cause of fragmented rowgroups within the columnstore index. Option D is wrong because updating statistics on the partitioned column improves cardinality estimates for the query optimizer but does not repair the physical rowgroup layout that causes poor rowgroup elimination.

Full explanation →
434
MCQhard

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline fails intermittently with network connectivity errors. You need to ensure reliable data transfer with minimal latency. Which solution should you recommend?

A.Set up a site-to-site VPN gateway
B.Deploy a self-hosted IR with high availability on two nodes
C.Stage data in Azure Blob Storage using sharded files
D.Use an Azure Integration Runtime instead
AnswerB

Deploying a self-hosted IR with high availability on two nodes provides redundancy and load balancing, ensuring that if one node fails, the other can take over, minimizing the impact of network connectivity errors.

Why this answer

A self-hosted integration runtime (SHIR) is required for on-premises data sources. To improve reliability, a high-availability SHIR with two or more nodes provides redundancy and load balancing. Option A is incorrect because a site-to-site VPN provides network connectivity but does not directly improve the reliability of the data transfer pipeline; the IR component is still needed.

Option C is incorrect because staging data in Blob Storage with sharded files is a performance optimization for parallel loading, but it does not address intermittent network connectivity issues. Option D is incorrect because the Azure Integration Runtime cannot access on-premises networks directly.

Full explanation →
435
MCQeasy

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline has been failing intermittently with an error indicating 'Activity timeout'. Which Azure Monitor metric should you set an alert on to proactively detect such failures?

A.Integration runtime queue depth metric
B.Pipeline duration metric
C.Data read and data written metrics
D.Failed pipeline runs metric
AnswerD

This metric increments each time a pipeline run fails, allowing proactive alerting.

Why this answer

The 'Failed pipeline runs' metric directly captures the number of pipeline runs that have failed, including those due to timeout errors, making it the most appropriate metric for alerting on such failures. Option A is incorrect because 'Integration runtime queue depth' indicates how many activities are queued for execution, not failures. Option B is incorrect because 'Pipeline duration' shows how long the pipeline runs, but a timeout is a specific failure type that would be captured by failed runs.

Option C is incorrect because 'Data read and data written' measure data throughput and are unrelated to pipeline failures.

Full explanation →
436
MCQeasy

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

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

Why this answer

Azure SQL Database is a fully managed, serverless relational database service designed for transactional workloads. It provides built-in high availability, automatic scaling, and pay-per-use billing, making it ideal for OLTP scenarios without the need to manage underlying infrastructure.

Exam trap

The trap here is that candidates confuse 'fully managed serverless relational database' with Azure Cosmos DB (which is serverless but not relational) or Azure Synapse Analytics (which is relational but designed for analytics, not transactions), leading them to overlook the specific OLTP focus of Azure SQL Database.

Why the other options are wrong

A

Cosmos DB is a NoSQL database, not relational.

C

Synapse Analytics is for large-scale analytics, not transactional workloads.

D

Data Lake Storage is for big data analytics, not relational OLTP.

Full explanation →
437
MCQhard

You are building a data processing pipeline in Azure Synapse Analytics. The pipeline should read data from Azure Data Lake Storage Gen2 (Parquet files), apply transformations using a mapping data flow, and write the results to a dedicated SQL pool table. The source data contains personally identifiable information (PII). You need to mask the PII columns (e.g., email) using a data masking function within the data flow. Which transformation should you use?

A.Derived Column transformation
B.Join transformation
C.Aggregate transformation
D.Pivot transformation
AnswerA

Derived Column can apply expressions, including hash functions like SHA2 for masking PII.

Why this answer

The Derived Column transformation in mapping data flows allows you to create new columns or modify existing ones using expressions, including built-in data masking functions like `mask()`, `maskEmail()`, or `substring()`. This is the correct transformation to apply PII masking on columns such as email addresses within the data flow pipeline before writing to the dedicated SQL pool.

Exam trap

The trap here is that candidates may confuse the Derived Column transformation with the Select transformation (which can also rename or drop columns but does not support expression-based masking), or assume that masking must be done in the sink (dedicated SQL pool) rather than within the data flow itself.

How to eliminate wrong answers

Option B (Join transformation) is wrong because it is used to combine rows from two sources based on a matching condition, not to mask or transform column values. Option C (Aggregate transformation) is wrong because it performs grouping and aggregation operations (e.g., SUM, COUNT) and does not support per-row data masking functions. Option D (Pivot transformation) is wrong because it rotates rows into columns for reshaping data, not for applying masking or transformations to individual column values.

Full explanation →
438
MCQmedium

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

A.Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.
B.Azure Data Explorer (ADX) with a continuous export to Power BI.
C.Power BI with DirectQuery mode and configure automatic page refresh.
D.Azure Synapse Serverless SQL pool with Power BI import mode.
AnswerC

DirectQuery mode allows real-time queries to Azure SQL Database; automatic page refresh supports 30-second intervals.

Why this answer

Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards.

Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Full explanation →
439
MCQeasy

You are developing a data processing pipeline in Azure Databricks that processes streaming data from Azure Event Hubs. You need to ensure that the pipeline can recover from failures and process data exactly once. The pipeline writes to Delta Lake. Which approach should you use?

A.Use Azure Stream Analytics to process the stream and output to Delta Lake via Azure Data Lake Storage Gen2.
B.Use Structured Streaming with foreachBatch to write micro-batches to Delta Lake, and set the checkpoint location to Azure Data Lake Storage Gen2.
C.Use Structured Streaming with a Delta Lake sink and specify a checkpoint location on Azure Data Lake Storage Gen2.
D.Use Auto Loader to ingest streaming data from Event Hubs and write to Delta Lake with checkpointing.
AnswerC

Provides exactly-once semantics with checkpointing.

Why this answer

Using Structured Streaming with a Delta Lake sink and specifying a checkpoint location on Azure Data Lake Storage Gen2 enables exactly-once processing. Delta Lake's ACID transactions guarantee idempotent writes, and checkpointing stores stream offsets for recovery. Option A is incorrect because Azure Stream Analytics does not integrate natively with Delta Lake.

Option B is incorrect because while foreachBatch can be used for custom processing, the direct Delta Lake sink with checkpointing is the recommended approach for exactly-once semantics. Option D is incorrect because Auto Loader is for batch ingestion from files, not streaming from Event Hubs.

Full explanation →
440
MCQhard

You are designing a data lake architecture using Azure Data Lake Storage Gen2. Sensitive customer data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. Additionally, access must be audited at the file level. Which combination of features should you implement?

A.Azure AD authentication and Azure Storage firewall
B.Azure Information Protection labels and Azure Policy
C.Customer-managed keys (CMK) in Azure Key Vault and Azure Storage analytics logs
D.Service-managed keys and Azure Monitor alerts
AnswerC

CMK provides encryption at rest, analytics logs provide audit

Why this answer

Customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer-controlled keys, and Azure Storage analytics logs (or diagnostic settings) capture file-level audit events such as read and write operations. Option A is incorrect because Azure AD authentication controls access but does not provide encryption at rest. Option B is incorrect because Azure Information Protection is a classification and labeling solution, not an encryption mechanism for data at rest.

Option D is incorrect because service-managed keys do not give customer control over encryption, and Azure Monitor alerts are for notifications, not audit logging.

Full explanation →
441
Multi-Selecthard

Which THREE features should you use to optimize query performance in Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Select 3 answers
A.T-SQL views.
B.Geo-redundant storage (GRS).
C.Materialized views.
D.Workload management with workload groups and importance.
E.Result-set caching.
AnswersC, D, E

Materialized views precompute and store results for faster queries.

Why this answer

Options C, D, and E are correct. Materialized views precompute and store aggregated data, reducing compute for complex queries. Workload management with workload groups and importance allocates resources and prioritizes critical queries.

Result-set caching stores the results of repeated queries, eliminating recomputation. Option A (T-SQL views) is a logical abstraction and does not inherently optimize performance. Option B (geo-redundant storage) provides disaster recovery but does not affect query performance.

Full explanation →
442
MCQeasy

You need to store log files from multiple applications in a central location for long-term retention and occasional analysis. The data is rarely accessed after 30 days. Which storage solution should you use to minimize cost?

A.Azure Files
B.Azure Data Lake Storage Gen2
C.Azure Cosmos DB
D.Azure Blob Storage (cool or archive tier)
AnswerD

Blob Storage with cool/archive tiers provides low-cost storage for infrequently accessed data.

Why this answer

Azure Blob Storage with cool or archive tier is the most cost-effective solution for storing log files that are rarely accessed after 30 days. The cool tier offers low storage costs with higher access costs, while the archive tier provides the lowest storage cost but requires rehydration for access, making both ideal for long-term retention and occasional analysis.

Exam trap

The trap here is that candidates may choose Azure Data Lake Storage Gen2 for its analytics capabilities, overlooking that blob storage tiers are specifically designed for cost-efficient long-term retention of infrequently accessed data.

How to eliminate wrong answers

Option A is wrong because Azure Files provides fully managed file shares using SMB protocol, which is designed for shared access and active workloads, not for cost-optimized long-term archival storage. Option B is wrong because Azure Data Lake Storage Gen2 is optimized for big data analytics with hierarchical namespace and high-throughput access, incurring higher storage costs than blob storage tiers for infrequently accessed data. Option C is wrong because Azure Cosmos DB is a NoSQL database with low-latency access and global distribution, designed for transactional workloads, not for cost-efficient long-term retention of log files.

Full explanation →
443
MCQeasy

You need to design a storage solution for streaming data from IoT devices. The solution must support real-time analytics and long-term storage for historical analysis. Which combination of Azure services should you use?

A.Azure Queue Storage and Azure Cosmos DB
B.Azure Event Hubs and Azure Blob Storage
C.Azure IoT Hub and Azure SQL Database
D.Azure Event Hubs and Azure Data Lake Storage Gen2
AnswerD

Event Hubs ingests streaming data, and Data Lake Storage stores it for analytics.

Why this answer

Azure Event Hubs is designed for high-throughput, low-latency ingestion of streaming data from IoT devices, supporting real-time analytics via integration with Azure Stream Analytics. Azure Data Lake Storage Gen2 provides hierarchical namespace and POSIX-compliant access for long-term storage, enabling efficient historical analysis with tools like Azure Synapse Analytics or Spark. This combination meets both real-time and historical requirements without the limitations of other options.

Exam trap

The trap here is that candidates often confuse Azure IoT Hub with Event Hubs, assuming IoT Hub is the primary streaming ingestion service, but IoT Hub is for device management and control, not high-throughput event streaming, making Event Hubs the correct choice for this scenario.

How to eliminate wrong answers

Option A is wrong because Azure Queue Storage is a message queuing service for decoupling applications, not optimized for high-throughput streaming ingestion, and Azure Cosmos DB is a NoSQL database for transactional workloads, not designed for long-term analytical storage with cost-effective tiering. Option B is wrong because while Azure Event Hubs handles streaming ingestion, Azure Blob Storage lacks the hierarchical namespace and optimized analytics capabilities of Data Lake Storage Gen2, making it less suitable for large-scale historical analysis with tools like Spark or Hive. Option C is wrong because Azure IoT Hub is primarily for device management and bidirectional communication, not a high-throughput event ingestion service, and Azure SQL Database is a relational OLTP store not designed for streaming ingestion or cost-effective long-term storage of massive historical data.

Full explanation →
444
MCQeasy

Your team is building a real-time dashboard in Power BI that displays sales data from Azure Stream Analytics. The data must be updated every 5 seconds with low latency. Which output type should you configure in Stream Analytics to achieve this?

A.Power BI
B.Azure Blob Storage
C.Azure SQL Database
D.Azure Event Hubs
AnswerA

Direct streaming to Power BI for low latency.

Why this answer

Power BI is the correct output type because Azure Stream Analytics has a native Power BI output connector that supports real-time streaming datasets, enabling sub-second latency for dashboards. This connector pushes data directly to Power BI's streaming API, which refreshes visuals automatically every 5 seconds as required.

Exam trap

The trap here is that candidates often confuse Azure Event Hubs as a direct output for Power BI, overlooking that Event Hubs is an intermediary and not a visualization endpoint, while Power BI's native Stream Analytics output is the only option that directly feeds the real-time dashboard with low latency.

How to eliminate wrong answers

Option B is wrong because Azure Blob Storage is a batch-oriented, file-based storage service that introduces latency due to file writes and lacks real-time push capabilities, making it unsuitable for sub-5-second updates. Option C is wrong because Azure SQL Database, while supporting row inserts, incurs higher latency due to transactional overhead and connection pooling, and Power BI's DirectQuery or import modes cannot achieve 5-second refresh rates from SQL without significant tuning. Option D is wrong because Azure Event Hubs is a message ingestion service, not a visualization endpoint; it would require an additional downstream consumer to push data to Power BI, adding latency and complexity.

Full explanation →
445
MCQhard

You are building a data processing solution using Azure Databricks. The solution must process streaming data from Azure Event Hubs, join it with a static reference table stored in Azure Data Lake Storage Gen2 (Parquet format), and write the output to Azure Synapse Analytics. The reference table is updated daily. Which approach minimizes latency and ensures data consistency?

A.Use Spark Structured Streaming with a streaming join and cache the reference table as a static DataFrame.
B.Use Spark Structured Streaming with foreachBatch to write to Synapse.
C.Use Spark Structured Streaming with a streaming join and load the reference table in each micro-batch.
D.Use a batch job that runs every hour to process the data.
AnswerA

Caching the reference table as static minimizes latency and ensures consistency.

Why this answer

Using Spark Structured Streaming with a streaming join and caching the reference table as a static DataFrame minimizes latency and ensures consistency by reading the reference table once and caching it for the duration of the stream. This avoids reloading the reference table in each micro-batch (as in option C) and avoids the latency of batch processing (as in option D). Option B, foreachBatch, is useful for writing to sinks like Synapse but does not address the join performance or consistency of the reference data.

Full explanation →
446
MCQhard

Your company has a data lake in Azure Data Lake Storage Gen2 that stores sensitive customer information. You need to implement fine-grained access control so that data engineers can read all data, data scientists can read only anonymized data, and auditors can view access logs. The solution must use Azure role-based access control (RBAC) and access control lists (ACLs). You also need to enable auditing of read operations. What should you do?

A.Use Azure RBAC to assign Storage Blob Data Reader to data scientists, and configure lifecycle management to move raw data to archive tier.
B.Assign Storage Blob Data Contributor RBAC role to data engineers at the storage account level, use ACLs to deny read access to raw data for data scientists, and enable diagnostic settings for read requests to Log Analytics.
C.Assign RBAC roles at the storage account level and enable Storage Analytics logs for read operations.
D.Assign RBAC roles at the container level to grant read access to all users, and use Azure Policy to audit access.
AnswerB

Provides fine-grained control and auditing.

Why this answer

To implement fine-grained access control in Azure Data Lake Storage Gen2, combine Azure RBAC roles for coarse permissions (e.g., Storage Blob Data Contributor for data engineers at the storage account level) and ACLs for fine-grained control (e.g., deny read on raw data for data scientists). Enable diagnostic settings for read requests to Log Analytics to audit read operations. Option A is incorrect because assigning Storage Blob Data Reader to data scientists would give them read access to all data, not just anonymized data, and lifecycle management does not control access.

Option C is incorrect because Storage Analytics logs are deprecated; diagnostic settings should be used instead. Option D is incorrect because granting read access to all users at the container level is overly permissive and does not provide fine-grained control; Azure Policy is for compliance, not access control.

Full explanation →
447
MCQmedium

You are running a batch processing job using Azure Data Factory. The job reads from Azure Blob Storage, transforms data with a Data Flow, and writes to Azure Synapse Analytics. The job fails intermittently with the error: 'Operation on target WriteToSynapse failed: Cannot bulk load because the file could not be opened.' You need to resolve the issue with minimal downtime. What should you do?

A.Enable staging blob file deletion in the copy activity settings.
B.Increase the degree of copy parallelism in the Data Flow.
C.Use PolyBase to load data directly from Blob Storage without staging.
D.Switch to staging via SQL authentication instead of Managed Identity.
AnswerA

Deleting the staging blob after successful load prevents lock conflicts on retry.

Why this answer

The error indicates a transient lock on the staging blob file. Enabling staging blob file deletion in the copy activity settings allows the pipeline to clean up and retry. Increasing the degree of copy parallelism does not address file locking.

Using PolyBase with external tables requires schema changes. Switching to staging via SQL authentication does not solve the file lock issue.

Full explanation →
448
Multi-Selectmedium

You are designing a data processing solution that requires exactly-once processing semantics for streaming data. Which two Azure services support exactly-once processing? (Choose two.)

Select 2 answers
A.Azure Event Hubs
B.Azure Stream Analytics
C.Azure Databricks with Structured Streaming
D.Azure Data Lake Storage Gen2
AnswersB, C

Why this answer

Azure Stream Analytics supports exactly-once processing by using checkpointing and event sourcing to ensure that each event is processed exactly once, even in the event of failures or restarts. It achieves this through its internal state management and the use of checkpoint offsets in the output sink, guaranteeing no duplicate or missed events.

Exam trap

The trap here is that candidates often confuse the ingestion guarantee of Event Hubs (at-least-once) with the processing guarantee of Stream Analytics, or mistakenly think that a storage service like Data Lake Storage Gen2 inherently provides processing semantics.

Why the other options are wrong

A

Event Hubs provides at-least-once delivery; exactly-once requires processing logic.

D

It is a storage service, not a processing engine.

Full explanation →
449
MCQeasy

Which Azure service is primarily used for orchestrating data pipelines in a cloud-native ETL workflow?

A.Azure Data Factory
B.Azure Synapse Analytics
C.Azure HDInsight
D.Azure Databricks
AnswerA

Why this answer

Azure Data Factory (ADF) is the correct answer because it is a cloud-native, serverless data integration service specifically designed for orchestrating and automating data pipelines. ADF provides a code-free visual interface, supports over 90 built-in connectors, and enables complex ETL/ELT workflows with control flow, data flow, and trigger-based scheduling, making it the primary orchestration tool in Azure.

Exam trap

The trap here is that candidates confuse Azure Synapse Analytics (which includes pipeline capabilities) as the primary orchestrator, but Synapse pipelines are actually built on Azure Data Factory, and the exam expects you to identify ADF as the dedicated, cloud-native orchestration service.

Why the other options are wrong

B

Synapse is an analytics platform that includes pipelines but is not primarily an orchestration-only service.

C

HDInsight is a managed Hadoop/Spark cluster, not a pipeline orchestrator.

D

Databricks is a collaborative data engineering environment, not an orchestration service.

Full explanation →
450
MCQmedium

A company uses Azure Data Factory to orchestrate an ETL pipeline that copies data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline runs hourly and uses a self-hosted integration runtime. Recently, the pipeline started failing with timeout errors. The on-premises SQL Server is healthy and the network is stable. What is the most likely cause and solution?

A.The self-hosted integration runtime version is outdated; update it to the latest version
B.The copy activity is not using staging; enable staging through Azure Blob Storage
C.The self-hosted integration runtime is under-provisioned; scale up the VM or add more nodes
D.The source query timeout in the copy activity is too low; increase it to 3600 seconds
AnswerC

Under-provisioned IR can cause timeouts; scaling resolves the issue.

Why this answer

Timeouts in Azure Data Factory copy activities using a self-hosted integration runtime (IR) often occur when the IR is under-provisioned. Overloaded VMs or insufficient nodes lead to resource contention and slower data processing, causing timeouts. Scaling up the VM or adding more nodes resolves the issue.

Option A is incorrect because outdated IR versions are not the typical cause; updates are managed automatically. Option B is incorrect because staging is used for large data transfers or cross-region copies, but not directly linked to timeouts from resource exhaustion. Option D is incorrect because increasing the source query timeout does not address the underlying resource constraint; it may only mask the problem.

Full explanation →
Page 5

Page 6 of 11

Page 7

All pages

1234567891011

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

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