Courseiva

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

509 questions total · 7pages · All types, answers revealed

Take a mock examExam hub
Page 6

Page 7 of 7

451
MCQhard

A company is migrating its on-premises SQL Server data warehouse to Azure Synapse Analytics. They have a fact table with 2 billion rows and 30 columns. The table is frequently joined on CustomerID and filtered on OrderDate. What is the recommended table design?

A.Hash-distribute on CustomerID and partition on OrderDate
B.Replicate the table to all nodes
C.Round-robin distribution with partitions on OrderDate
D.Hash-distribute on OrderDate and partition on CustomerID
AnswerA

Hash distribution on CustomerID co-locates joined rows, eliminating data movement during joins, while partitioning on OrderDate enables partition elimination for date filters. Together they satisfy both the join and filter patterns on the 2-billion-row table.

Why this answer

Hash-distributing the fact table on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution node, which makes joins on CustomerID efficient by avoiding data movement. Partitioning on OrderDate enables partition elimination when filtering by date, reducing the amount of data scanned. This combination optimizes both the join and filter operations for a large fact table in Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse the roles of distribution and partitioning, thinking that partitioning on the join column or distributing on the filter column will improve performance, when in fact distribution should align with join keys and partitioning with filter keys.

How to eliminate wrong answers

Option B is wrong because replicating a 2-billion-row fact table to all nodes would consume excessive storage and cause significant overhead during data loading and maintenance, and it is intended for small dimension tables, not large fact tables. Option C is wrong because round-robin distribution distributes rows evenly but without any logical grouping, so joins on CustomerID would require shuffling all data across nodes, leading to poor performance. Option D is wrong because hash-distributing on OrderDate would scatter rows with the same CustomerID across nodes, making joins on CustomerID highly inefficient, and partitioning on CustomerID is not supported (partition columns must be date/time types in Synapse) and would not help with date-based filtering.

Full explanation →
452
MCQhard

Your Azure Data Lake Storage Gen2 account stores sensitive customer data. You need to ensure that data is encrypted at rest using customer-managed keys (CMK) and that access to the encryption key is logged. What should you do?

A.Enable infrastructure encryption on the storage account.
B.Enable double encryption using both platform-managed and customer-managed keys.
C.Configure customer-managed keys in Azure Key Vault and enable Key Vault diagnostics logging.
D.Use Azure Storage Service Encryption (SSE) with platform-managed keys.
AnswerC

Customer-managed keys stored in Azure Key Vault replace Microsoft-managed keys for encryption at rest, and Key Vault diagnostic logging records every key operation. This satisfies both the CMK requirement and the key-access logging requirement in the stem.

Why this answer

Customer-managed keys (CMK) stored in Azure Key Vault allow you to control and audit key usage, and enabling Key Vault diagnostics logging captures access events. Option A is incorrect because infrastructure encryption uses platform-managed keys. Option B is incorrect because double encryption adds a second layer but does not directly provide logging of key access.

Option D is incorrect because SSE with platform-managed keys does not give customer control or logging.

Full explanation →
453
Multi-Selectmedium

Which TWO of the following are supported storage options for use as a source in Azure Synapse Pipeline Copy Activity?

Select 2 answers
A.Azure Data Lake Storage Gen2
B.Azure Analysis Services
C.Azure Cognitive Search
D.Azure Purview
E.Azure Blob Storage
AnswersA, E

ADLS Gen2 is a supported source.

Why this answer

Azure Data Lake Storage Gen2 is a supported source for Azure Synapse Pipeline Copy Activity because it combines a hierarchical file system with Azure Blob Storage APIs, enabling efficient data ingestion. The Copy Activity can read data from ADLS Gen2 using the AzureBlobFS linked service, which supports both file and folder-level reads for structured and unstructured data.

Exam trap

The trap here is that candidates confuse Azure services that manage or process data (like Analysis Services, Cognitive Search, or Purview) with actual storage services that can serve as a source for the Copy Activity, leading them to select non-storage options.

Full explanation →
454
MCQmedium

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

A.The SQL Server authentication credentials have expired.
B.The Self-Hosted Integration Runtime is not installed.
C.The on-premises network configuration has changed.
D.The Azure Storage account firewall is blocking access.
AnswerC

A self-hosted integration runtime that worked for months rules out credentials and installation faults. A connection timeout to the on-premises SQL Server therefore points to changed network configuration, such as firewall rules, routing, or proxy settings, blocking the runtime's outbound path to the source.

Why this answer

A 'Connection timeout' error when using a Self-Hosted Integration Runtime (SHIR) that has been running successfully for months indicates a network-level issue rather than an authentication or configuration problem. The most likely cause is a change in the on-premises network configuration (e.g., firewall rules, proxy settings, or DNS changes) that prevents the SHIR from reaching the SQL Server on the specified port (typically TCP 1433). Since the SHIR is already installed and was working, the timeout points to a connectivity break, not a credential or installation issue.

Exam trap

The trap here is that candidates confuse authentication errors (which produce specific error messages like 'Login failed') with connectivity errors (which produce timeouts), leading them to incorrectly select credential-related options when the symptom is clearly a network timeout.

How to eliminate wrong answers

Option A is wrong because expired SQL Server authentication credentials would result in an 'Authentication failed' or 'Login failed' error, not a 'Connection timeout'. Option B is wrong because if the Self-Hosted Integration Runtime were not installed, the pipeline would fail with a different error (e.g., 'Unable to connect to Integration Runtime' or 'Integration Runtime is offline'), not a timeout. Option D is wrong because the Azure Storage account firewall controls inbound access to the storage endpoint, not outbound connectivity from the SHIR to the on-premises SQL Server; a storage firewall issue would produce an error related to storage access (e.g., 403 Forbidden), not a timeout to the source.

Full explanation →
455
MCQmedium

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The exhibit shows the encryption configuration. What is the effect of setting infrastructureEncryption to Enabled?

A.It disables encryption using the customer-managed key.
B.It enables encryption of data in transit between nodes.
C.It enables transparent data encryption for SQL pools.
D.It adds a second layer of encryption at the infrastructure level, ensuring data is encrypted at rest with two different keys.
AnswerD

Infrastructure encryption applies a second, independent encryption layer at the hardware level using a platform-managed key, in addition to the service-level encryption already applied with your key. Data is therefore encrypted at rest twice, with two different keys, satisfying the infrastructureEncryption Enabled setting.

Why this answer

Enabling infrastructureEncryption adds a second layer of encryption at the infrastructure level, ensuring data is encrypted at rest with two different keys (double encryption). Option A is incorrect because enabling infrastructure encryption does not disable customer-managed key encryption; it adds an additional layer. Option B is incorrect because infrastructure encryption applies to data at rest, not in transit between nodes.

Option C is incorrect because infrastructure encryption applies to the entire Azure Synapse Analytics workspace, not just SQL pools, and it is not transparent data encryption (TDE); it is a separate infrastructure-level encryption.

Full explanation →
456
MCQmedium

You are responsible for managing an Azure Data Lake Storage Gen2 account that stores parquet files for analytics. You need to implement a data retention policy that automatically deletes files older than 90 days in the 'logs' container. Additionally, you need to ensure that no data is lost due to accidental deletion; you want to be able to recover deleted files within 30 days. You also need to monitor the storage account for unusual access patterns. The solution must minimize administrative effort. What should you do?

A.Enable soft delete with a retention period of 30 days and configure a lifecycle management rule to delete blobs older than 90 days
B.Create an Azure Policy to enforce tag-based retention and use Azure Monitor to alert on access
C.Enable versioning and configure a retention policy in Azure Policy
D.Use Azure Backup for the storage account and manually delete old files
AnswerA

Soft delete retains deleted blobs for 30 days, enabling recovery from accidental deletion, while a lifecycle management rule automatically tiers or deletes blobs older than 90 days. Both are policy-driven, meeting the retention and recovery requirements with minimal administrative effort.

Why this answer

Enabling soft delete with a 30-day retention period allows recovery of accidentally deleted blobs within that window. A lifecycle management rule automatically deletes blobs older than 90 days, meeting the retention policy with minimal administrative effort.

Exam trap

DP-203 often tests the difference between soft delete (recovery) and lifecycle management (deletion); candidates may incorrectly choose versioning or Azure Policy for automatic deletion.

How to eliminate wrong answers

Option B is wrong because Azure Policy enforces tag-based retention but does not automatically delete blobs; it also does not provide recovery for accidental deletion. Option C is wrong because versioning alone does not delete old files automatically, and Azure Policy retention policies are for compliance, not lifecycle deletion. Option D is wrong because Azure Backup for storage accounts is for operational backup, not for lifecycle deletion, and manual deletion increases administrative effort.

Full explanation →
457
MCQhard

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

A.Change the output format to Parquet to reduce file size.
B.Review the job logs in Azure Data Lake Analytics to identify the specific script error.
C.Migrate the U-SQL script to Azure Synapse Spark pool.
D.Increase the degree of parallelism for the U-SQL job.
AnswerB

Azure Data Lake Analytics records detailed job logs, including the vertex and script error behind UserError failures. Reviewing them first pinpoints the faulty U-SQL statement, avoiding guesswork before changing the terabytes-scale script or pipeline configuration.

Why this answer

The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Full explanation →
458
MCQmedium

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

A.The key vault does not have soft-delete enabled.
B.The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.
C.The key name or version is incorrect.
D.The key vault URI is incorrectly formatted.
AnswerB

The storage account's identity must have these permissions to use the key.

Why this answer

When an ARM template configures customer-managed keys for Azure Data Lake Storage Gen2, the storage account's system-assigned or user-assigned managed identity must have Get, Wrap Key, and Unwrap Key permissions on the key vault. The error 'encryption key cannot be accessed' almost always indicates the access policy or RBAC role assignment is missing these permissions, so the storage service cannot retrieve the key. Soft-delete, key name/version, and URI formatting would produce different, more specific errors.

Exam trap

DP-203 often tests the distinction between key vault configuration errors (soft-delete, URI, key name) and identity/permission errors, so candidates must recognize that 'cannot be accessed' points to missing Get/Wrap/Unwrap Key permissions.

How to eliminate wrong answers

Option A is wrong because soft-delete on the key vault is a data-recovery feature; its absence does not prevent the storage account from accessing an existing key and would not produce an 'encryption key cannot be accessed' error. Option C is wrong because an incorrect key name or version typically yields a 'key not found' or 'resource not found' error, not an access-denied error. Option D is wrong because a malformed key vault URI would cause a validation or parsing failure during template deployment, distinct from a permissions-based access denial.

Full explanation →
459
MCQmedium

You are designing a storage layer for an Azure Synapse Analytics dedicated SQL pool that ingests 4 TB of CSV files daily into a fact table. The files are landed in Azure Data Lake Storage Gen2 by an external ETL process. You need to load the data with the highest possible throughput while minimizing the load window. What should you do?

A.Use the COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.
B.Use the COPY statement with a shared access signature (SAS) token and set MAXDOP to a value equal to the number of files.
C.Use PolyBase with an external table and enable REJECT_TYPE VALUE with a threshold of 0.
D.Use Azure Data Factory with a copy activity that has a single pipeline and a single parallel copy thread.
AnswerA

The COPY statement is the recommended high-throughput ingestion method for dedicated SQL pools and supports SAS authentication to ADLS Gen2. Splitting large source files into multiple evenly sized files allows the COPY statement to parallelize reads across distributions, dramatically reducing the load window. This combination is the documented best practice for maximizing ingestion throughput in Synapse dedicated SQL pools.

Why this answer

The COPY statement is the preferred high-throughput ingestion mechanism for dedicated SQL pools, especially when reading from ADLS Gen2 with SAS authentication. Splitting the source files into evenly sized chunks enables parallel reads across distributions, which is essential for loading 4 TB within a tight window. Combining COPY with proper file sizing directly addresses the throughput and load-window requirements.

Exam trap

The trap here is assuming that any supported ingestion method (PolyBase, Data Factory, COPY) will automatically deliver maximum throughput without considering file layout and parallelism.

Full explanation →
460
MCQhard

Refer to the exhibit. You are reviewing an ARM template for an Azure Data Lake Storage Gen2 account. Which of the following security best practices is violated in this template?

A.The location should be fixed instead of using resourceGroup().location
B.The SKU should be Standard_GRS for disaster recovery
C.The account does not enable hierarchical namespace (HNS)
D.The account allows HTTP traffic and uses an outdated TLS version
AnswerD

Allowing HTTP traffic and an outdated TLS version violates Azure Data Lake Storage Gen2 security baselines, which require HTTPS-only and TLS 1.2 minimum. This exposes data in transit to interception and downgrade attacks, breaching the template's security best practise.

Why this answer

The template sets supportsHttpsTrafficOnly to false, allowing HTTP traffic, which is insecure. Additionally, minimumTlsVersion is set to TLS1_0, an outdated and insecure version. Option A is incorrect because the location uses resourceGroup().location, which is a common and acceptable practice for flexibility.

Option B is incorrect because Standard_LRS is a valid SKU; Geo-redundant storage is not a security best practice requirement. Option C is incorrect because the template does enable hierarchical namespace (isHnsEnabled: true), so that is not a violation.

Full explanation →
461
MCQmedium

Refer to the exhibit. You are reviewing a Data Factory JSON definition. The factory has a user-assigned managed identity configured. However, the linked service to Azure Storage uses an account key. What security improvement should you recommend?

A.Add a firewall rule to limit access to the storage account
B.Modify the linked service to use the managed identity for authentication
C.Remove the managed identity and use a service principal
D.Keep the account key but store it in Azure Key Vault
AnswerB

Switching the linked service from account-key authentication to the user-assigned managed identity removes stored credentials, letting Microsoft Entra ID issue and rotate tokens automatically. This eliminates secret sprawl and satisfies the security improvement sought for the Storage linked service.

Why this answer

Since the Data Factory already has a user-assigned managed identity, the most secure improvement is to reconfigure the Azure Storage linked service to authenticate via that managed identity instead of an account key. Managed identities eliminate the need to store and rotate secrets, and Azure RBAC can grant the identity the minimum required role (e.g., Storage Blob Data Contributor) on the storage account. This is Microsoft's recommended authentication pattern for ADF linked services.

Exam trap

DP-203 often tests whether candidates recognize that storing a key in Key Vault is not the same as eliminating the key — the exam rewards the more secure managed identity pattern over 'hide the secret' workarounds.

How to eliminate wrong answers

Option A is wrong because a firewall rule restricts network access but does not address the credential exposure of using an account key — the key still grants full access if leaked. Option C is wrong because removing the managed identity and using a service principal reintroduces a secret (client secret or certificate) that must be stored and rotated, which is less secure than a managed identity. Option D is wrong because storing the account key in Key Vault is better than hardcoding it, but it still relies on a shared secret with full storage account access; managed identity is strictly more secure and is the recommended approach.

Full explanation →
462
MCQeasy

You need to implement column-level security in Azure Synapse Analytics to restrict access to salary information. Only users with the 'HRManager' role should see salary columns. Which feature should you use?

A.Row-level security using security predicates
B.Dynamic data masking
C.Column-level security using GRANT on columns
D.Azure Purview data classification
AnswerC

GRANT on specific columns lets you deny salary data to everyone except HRManager, since permissions are enforced by the database engine at query time regardless of the client tool. This directly satisfies the stem's requirement to restrict salary columns by role, unlike object-level or row-level alternatives.

Why this answer

Column-level security (CLS) in Azure Synapse Analytics uses GRANT on specific columns to restrict access. Option A is incorrect because row-level security filters rows, not columns. Option B is incorrect because dynamic data masking obfuscates data but does not prevent access.

Option D is incorrect because Azure Purview is a governance tool, not a security enforcement mechanism.

Exam trap

Candidates may confuse row-level security (RLS) with column-level security. RLS controls row access via security predicates, while CLS controls column access via GRANT.

Full explanation →
463
MCQeasy

A data engineer needs to store semi-structured JSON logs from IoT devices. The data will be queried using SQL and must support high-throughput writes. Which Azure data store is most appropriate?

A.Azure Blob Storage with JSON blobs
B.Azure Cosmos DB with Core (SQL) API
C.Azure Data Lake Storage Gen2 with JSON files and PolyBase
D.Azure SQL Database with JSON columns
AnswerB

Cosmos DB's Core (SQL) API stores schema-agnostic JSON documents and exposes them to SQL-like queries, while its partitioned, multi-region write model delivers the high-throughput ingestion the IoT logs demand. This combination satisfies both the semi-structured format and write-throughput constraints in the stem.

Why this answer

Azure Cosmos DB with Core (SQL) API is the most appropriate choice because it natively stores semi-structured JSON documents, supports high-throughput writes with single-digit millisecond latency, and allows querying the JSON data directly using SQL syntax. Its schema-agnostic design and automatic indexing make it ideal for IoT workloads where device telemetry arrives at high velocity and must be immediately queryable.

Exam trap

The trap here is that candidates often choose Azure Blob Storage or Data Lake Storage because they associate JSON files with cheap storage, but they overlook the requirement for high-throughput writes and native SQL querying, which Cosmos DB uniquely satisfies among the options.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage with JSON blobs does not provide native SQL querying capabilities; querying would require additional services like Azure Synapse or external tools, and it is optimized for large, infrequent access rather than high-throughput writes. Option C is wrong because Azure Data Lake Storage Gen2 with JSON files and PolyBase is designed for analytical batch processing and large-scale data lakes, not for real-time high-throughput writes; PolyBase is used for querying external data in Synapse, not for direct ingestion at IoT scale. Option D is wrong because Azure SQL Database with JSON columns imposes a fixed relational schema and transactional overhead that cannot match the write throughput and schema flexibility of Cosmos DB; it is optimized for ACID transactions and structured data, not for high-velocity semi-structured ingestion.

Full explanation →
464
MCQhard

You manage an Azure Data Lake Storage Gen2 account used by an Azure Synapse Analytics workspace. You need to ensure that only authorized users can access data, and that all access attempts are logged for auditing. You configure Azure Active Directory (Azure AD) authentication and role-based access control (RBAC). Which additional feature should you enable to capture detailed access logs for compliance?

A.Storage account firewall and virtual network rules
B.Azure Defender for Storage
C.Azure Monitor diagnostic settings for the storage account
D.Azure Storage analytics logging
AnswerC

Azure Monitor diagnostic settings allow you to stream resource logs from the storage account to destinations like Log Analytics, Event Hubs, or a storage account. For Data Lake Storage Gen2, these logs include detailed information about read, write, and delete operations, including the identity of the requester. This meets the requirement for auditing access attempts.

Why this answer

Azure Monitor diagnostic settings capture resource logs for Data Lake Storage Gen2, including operations like GetBlob, PutBlob, and DeleteBlob, along with the caller's identity. These logs can be sent to Log Analytics for querying and retention, enabling comprehensive auditing. This is the correct feature to enable for detailed access logging in a compliance scenario.

Exam trap

The trap here is confusing security monitoring features like Azure Defender with auditing features that provide a complete access log.

Full explanation →
465
MCQhard

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

A.Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake
B.Use Azure Stream Analytics to output to both Power BI and Data Lake Storage
C.Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery
D.Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs
AnswerB

Stream Analytics performs the in-flight aggregation and routes one query to two sinks: Power BI for live dashboards and Data Lake Storage Gen2 for historical retention. This dual output satisfies both consumption patterns from a single managed, serverless job, minimising the operational overhead the stem demands.

Why this answer

Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Databricks (Option C) for its flexibility, overlooking that Stream Analytics provides a simpler, fully managed approach with native dual-output support that minimizes operational overhead.

How to eliminate wrong answers

Option A is wrong because Azure Functions writing directly to Data Lake Storage introduces additional latency and operational complexity (e.g., managing function scaling, retries) and does not natively support real-time streaming output to Power BI without extra integration. Option C is wrong because Azure Databricks with Structured Streaming requires managing a cluster and incurs higher operational overhead compared to a fully managed service like Stream Analytics, and Power BI DirectQuery from Data Lake Storage is not optimized for real-time dashboards due to latency. Option D is wrong because Event Hubs Capture writes data in Avro format to Data Lake Storage in batches (e.g., every 5 minutes or 100 MB), which introduces latency unsuitable for real-time dashboards, and Power BI directly querying Event Hubs is not supported without an intermediary like Stream Analytics.

Full explanation →
466
MCQmedium

Your organization uses Azure Synapse Analytics. You need to design a data transformation pipeline that processes streaming data from Azure Event Hubs, performs aggregations over a 5-minute tumbling window, and loads the results into a dedicated SQL pool table. Which Azure service should you use to implement the streaming transformation?

A.Azure Stream Analytics
B.Azure Data Factory
C.Apache Spark for Azure Synapse
D.Azure Functions
AnswerA

Azure Stream Analytics natively consumes Event Hubs streams and supports tumbling windows, enabling the required five-minute aggregations before writing results to the dedicated SQL pool. This satisfies the stem's streaming-transformation requirement without building custom windowing logic in Spark or Functions.

Why this answer

Azure Stream Analytics is the appropriate service for real-time stream processing with windowed aggregations. Option B is wrong because Azure Data Factory is for batch orchestration. Option C is wrong because Spark Structured Streaming is for big data workloads but less integrated with SQL pools.

Option D is wrong because Azure Functions is not designed for streaming windowed aggregations.

Full explanation →
467
MCQeasy

You are designing a data storage solution for IoT sensor data. The data is written thousands of times per second and requires low-latency reads for real-time dashboards. Which Azure storage solution should you use?

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

Cosmos DB provides single-digit-millisecond reads and writes with horizontal partitioning, absorbing thousands of writes per second across partitions. Its low-latency, globally distributed model suits real-time IoT dashboards, whereas Blob or Table storage cannot match that ingest rate and latency.

Why this answer

Azure Cosmos DB is the correct choice because it provides single-digit millisecond read and write latency at any scale, with automatic indexing and multi-region distribution. Its support for multiple APIs (SQL, MongoDB, Cassandra, etc.) and configurable consistency levels makes it ideal for IoT sensor data requiring high-throughput writes and low-latency reads for real-time dashboards.

Exam trap

The trap here is that candidates often choose Azure Blob Storage or Data Lake Storage Gen2 because they associate IoT data with 'storage' rather than 'real-time querying,' overlooking the critical requirement for low-latency reads and high-frequency writes that only a NoSQL database like Cosmos DB can satisfy.

Why the other options are wrong

A

Blob Storage is optimized for large, unstructured data with higher latency, not real-time ingestion.

C

SQL Database can handle writes but may struggle with the scale and low-latency requirements of IoT sensor data.

D

Designed for big data analytics, not real-time ingestion and query.

Full explanation →
468
Multi-Selectmedium

You are designing a data storage solution for a media company that stores video files in Azure Blob Storage. The company wants to optimize storage costs by automatically moving older files to cooler tiers. The files are accessed frequently for the first 30 days, then infrequently for the next 60 days, and rarely after that. You need to configure a lifecycle management policy. Which two actions should you include in the policy? (Choose two.)

Select 2 answers
A.Move blobs to Cool tier after 30 days since last modification.
B.Move blobs to Cool tier after 90 days since last modification.
C.Move blobs to Archive tier after 30 days since last modification.
D.Delete blobs after 180 days since last modification.
E.Move blobs to Archive tier after 90 days since last modification.
AnswersA, E

Moving blobs to Cool tier after 30 days aligns with the access pattern: frequently accessed for the first 30 days, then infrequently. Cool tier is optimized for infrequent access and lower storage cost. This action reduces cost while maintaining availability. It is a valid lifecycle management action.

Why this answer

The access pattern indicates that data is frequently accessed for 30 days, infrequently for the next 60 days (days 31-90), and rarely after 90 days. Therefore, moving to Cool tier after 30 days and to Archive tier after 90 days optimizes storage costs while matching access needs. Deletion is not specified.

Exam trap

The trap here is misaligning tier transitions with the access pattern, such as moving to Archive too early or Cool too late.

Full explanation →
469
MCQmedium

You are building an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a five-minute tumbling window average per device and tolerate events that arrive up to three minutes late. During testing, you observe that events arriving after the window closes are silently dropped. You need to ensure late events are included in the correct window result. What should you configure in the Stream Analytics job?

A.Set the event ordering policy's Out-of-order events tolerance to 00:03:00.
B.Increase the streaming units allocated to the job to at least six.
C.Set the Event Hubs consumer group's message retention to seven days.
D.Configure the late arrival tolerance on the temporal window to 00:03:00.
AnswerD

Late arrival tolerance extends how long a temporal window such as a tumbling window stays open for events whose timestamp falls inside the window but that physically arrive afterward. Setting it to three minutes lets the job include those delayed telemetry events in the correct five-minute window aggregate instead of discarding them.

Why this answer

Tumbling windows emit once the window period elapses, and by default any event whose timestamp falls in that period but arrives after emission is discarded. The late arrival tolerance setting explicitly extends the window's acceptance period, so a three-minute value preserves the delayed device telemetry and aggregates it into the correct window. Scaling or retention settings do not affect timestamp-based window membership.

Exam trap

The trap here is confusing out-of-order tolerance, which reorders events relative to one another, with late arrival tolerance, which keeps a temporal window open for events that arrive after the window boundary.

Full explanation →
470
MCQmedium

You are designing a data lakehouse architecture in Azure using Delta Lake. The solution needs to process batch and streaming data from multiple sources, including IoT devices and CRM systems. You need to ensure data quality by enforcing schema validation and handling schema evolution. You also need to provide a unified catalog for querying. Which service should you use?

A.Azure Purview
B.Azure Data Lake Storage Gen2
C.Azure Synapse Analytics serverless SQL pool
D.Azure Databricks Unity Catalog
AnswerD

Unity Catalog provides centralised governance, fine-grained access control and a unified metastore across workspaces, while Delta Lake enforces schema validation and supports schema evolution. This satisfies the requirement for a unified catalog spanning batch and streaming sources such as IoT and CRM data.

Why this answer

Azure Databricks Unity Catalog is the only option that provides a unified governance layer across workspaces with built-in support for Delta Lake schema enforcement, schema evolution, and a centralized metastore for querying. It natively integrates with Delta Lake's transaction log to enforce schema-on-write and supports MERGE/ALTER operations for evolution. The other services provide storage or query capabilities but not unified cataloging with schema governance.

Exam trap

DP-203 often tests the confusion between governance/cataloging tools (Purview) and unified metastore with schema enforcement (Unity Catalog) — Purview catalogs metadata but does not enforce schema at write time.

How to eliminate wrong answers

Option A is wrong because Azure Purview is a data governance and cataloging service for discovery, lineage, and classification — it does not enforce schema validation or handle schema evolution at write time. Option B is wrong because ADLS Gen2 is just hierarchical blob storage; it has no catalog, no schema enforcement, and no Delta Lake transaction semantics. Option C is wrong because Synapse serverless SQL pool can query Delta files but does not provide a unified catalog with schema enforcement or evolution management across workspaces.

Full explanation →
471
MCQhard

You are designing a storage solution for a financial services company. The solution must store large volumes of semi-structured JSON data in Azure Data Lake Storage Gen2. The data is accessed by Azure Databricks for batch processing and by Azure Synapse Analytics for interactive queries. The data must be organized for efficient partition elimination and must support atomic operations. You need to choose the appropriate file format and partitioning strategy. What should you do?

A.Store the data as CSV files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
B.Store the data as Parquet files without partitioning, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
C.Store the data as Avro files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
D.Store the data as Parquet files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
AnswerD

Parquet is a columnar format ideal for analytical workloads, offering efficient compression and predicate pushdown. Partitioning by date enables partition elimination, reducing the amount of data scanned. Registering the folder as an external table in both services allows them to query the same data without duplication. This meets the requirements for efficient querying and atomic operations (Parquet files are immutable, and writes can be atomic at file level).

Why this answer

Parquet is the optimal format for analytical workloads due to its columnar storage, compression, and predicate pushdown capabilities. Partitioning by date enables partition elimination, which is critical for efficient querying. Registering the folder as an external table in both Azure Databricks and Azure Synapse Analytics allows both services to access the same data seamlessly.

Other formats like CSV and Avro are not as efficient for these requirements.

Exam trap

The trap here is assuming that any file format with partitioning will meet the performance requirements, but columnar formats like Parquet are essential for efficient analytical queries.

Full explanation →
472
MCQmedium

You manage an Azure Synapse Analytics dedicated SQL pool that stores a 4 TB fact table named FactSales. The table is currently distributed using ROUND_ROBIN and has a clustered columnstore index. Most analytical queries join FactSales to a much smaller dimension table DimProduct on ProductKey and then filter by DateKey. You need to redesign the physical storage to minimize data movement during these joins and improve query performance. What should you do?

A.Keep ROUND_ROBIN distribution and add a nonclustered index on ProductKey in FactSales.
B.Change the distribution of FactSales to HASH(DateKey) and create a partition on ProductKey.
C.Change the distribution of FactSales to HASH(ProductKey) and ensure DimProduct is replicated.
D.Recreate FactSales as a replicated table and create a hash distribution on DimProduct using ProductKey.
AnswerC

Hash distributing the large fact table on the frequently joined column ProductKey colocates rows with the same key on the same compute node. Replicating the small dimension table makes its rows available on every node. This combination eliminates data movement during joins on ProductKey, which is the primary performance bottleneck described in the scenario.

Why this answer

For large fact tables in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during joins. Replicating the smaller dimension table ensures its rows are present on every compute node, so the join can be performed locally. This design directly addresses the scenario's need to reduce data movement and improve query performance.

Exam trap

The trap here is assuming that any hash distribution improves performance, when the distribution key must match the join column to actually eliminate data movement.

Full explanation →
473
MCQeasy

You are designing a data processing solution in Azure Databricks to transform streaming data from Azure Event Hubs. The data must be aggregated in 1-minute tumbling windows and written to Azure Synapse Analytics. Which Spark API should you use?

A.RDD API
B.Structured Streaming
C.Spark Streaming (DStreams)
D.DataFrame API with batch processing
AnswerB

Structured Streaming satisfies the 1-minute tumbling window requirement through its event-time windowing on the streaming DataFrame, aggregating Event Hubs data incrementally. It writes results to Azure Synapse Analytics via the Synapse connector, unlike DStreams (RDD-based, deprecated) or batch APIs, which cannot process continuous streams natively.

Why this answer

Structured Streaming is the correct choice because it provides native support for event-time-based aggregations, such as 1-minute tumbling windows, and integrates seamlessly with Azure Event Hubs as a streaming source and Azure Synapse Analytics as a streaming sink using the `foreachBatch` or `writeStream` API. It offers exactly-once semantics and automatic state management for windowed operations, which are essential for reliable streaming ETL.

Exam trap

The trap here is that candidates confuse the older Spark Streaming (DStreams) API with Structured Streaming, assuming both are equally capable for event-time windows, but DStreams lack native event-time support and are deprecated in favor of Structured Streaming.

How to eliminate wrong answers

Option A is wrong because the RDD API operates at a low level without built-in support for event-time windowing, stateful aggregation, or streaming sinks like Azure Synapse Analytics, requiring manual implementation of checkpointing and fault tolerance. Option C is wrong because Spark Streaming (DStreams) uses micro-batch processing with a DStream API that is now in maintenance mode and lacks native event-time handling, making tumbling window aggregations more complex and less efficient compared to Structured Streaming. Option D is wrong because the DataFrame API with batch processing is designed for static data, not continuous streaming; it cannot process unbounded data from Event Hubs in real time or maintain state for tumbling windows without additional custom orchestration.

Full explanation →
474
MCQeasy

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

A.Azure Private Endpoint
B.Shared access signature (SAS)
C.Azure Defender for Storage
D.Firewall and virtual network service endpoints
AnswerD

Enabling the storage account firewall with virtual network service endpoints restricts traffic to specified subnets, blocking all public internet access. This directly satisfies the requirement to deny direct internet access while permitting the designated virtual network.

Why this answer

Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Exam trap

The trap here is that candidates often confuse Azure Private Endpoint with a complete internet-blocking solution, but Private Endpoint alone does not disable the public endpoint; you must also configure the firewall to deny all public traffic.

How to eliminate wrong answers

Option A is wrong because Azure Private Endpoint uses a private IP address from your virtual network to connect to the storage account, but it does not inherently block all internet access; it provides a private connection but still requires additional firewall rules to fully prevent internet access. Option B is wrong because Shared access signature (SAS) provides delegated access to storage resources via tokens that can be used over the internet, and it does not restrict network-level access from the internet. Option C is wrong because Azure Defender for Storage is a security monitoring and threat detection service, not a network access control mechanism; it does not block or restrict network traffic.

Full explanation →
475
MCQhard

You are designing a disaster recovery plan for an Azure Synapse Analytics dedicated SQL pool. The primary region becomes unavailable. You need to fail over to a secondary region with minimal data loss. The recovery point objective (RPO) is 1 hour. What should you configure?

A.Configure active geo-replication to the secondary region.
B.Use automatic restore points and copy them to the secondary region using Azure Data Factory.
C.Enable geo-backup on the dedicated SQL pool.
D.Create user-defined restore points every hour and store them in the secondary region.
AnswerC

Geo-backup restores a dedicated SQL pool in a paired Azure region from the latest geo-replicated backup, giving an RPO of up to eight hours. It satisfies the stem's failover requirement, though the one-hour RPO demands more frequent backups than the default.

Why this answer

Geo-backup is a built-in feature for Azure Synapse Analytics dedicated SQL pools that automatically takes full backups at regular intervals and replicates them to a paired region. The default recovery point objective (RPO) is 1 hour, meeting the requirement. Option A is incorrect because active geo-replication is a feature for Azure SQL Database, not for Synapse dedicated SQL pools.

Option B is incorrect because automatic restore points are local and not automatically replicated; copying them via Azure Data Factory adds complexity and does not guarantee the RPO. Option D is incorrect because user-defined restore points are manual and would require additional orchestration to store them in a secondary region, making it less reliable and harder to meet the 1-hour RPO consistently.

Full explanation →
476
Multi-Selectmedium

Which TWO are valid ways to process data in Azure Synapse Analytics?

Select 2 answers
A.Use Logic Apps to run data transformations.
B.Use Azure Functions to process data in a serverless manner.
C.Use Synapse SQL pool to run T-SQL queries.
D.Use Power BI to transform data.
E.Use Synapse Spark notebooks to run Scala code.
AnswersC, E

A dedicated Synapse SQL pool is a provisioned MPP engine that executes T-SQL, including distributed queries, stored procedures and CETAS, directly against data held in its own distributions. This satisfies the requirement for a T-SQL-based processing path within Synapse Analytics.

Why this answer

Option C is correct because a Synapse SQL pool (dedicated or serverless) is a core Synapse Analytics engine that executes T-SQL queries directly against data in the workspace, making it a valid data-processing method. Option E is correct because Synapse Spark notebooks run Apache Spark code, including Scala, Python, SQL, and R, and are a first-class way to transform and process data in Azure Synapse Analytics. Option A is not appropriate because Logic Apps is a workflow/automation service for orchestrating triggers and connectors, not a data transformation engine.

Option B is not the intended Synapse processing method because Azure Functions is a separate serverless compute service outside Synapse's built-in SQL and Spark engines. Option D is not correct because Power BI is a visualization and reporting tool; its data transformation (Power Query) is for modeling/reporting, not a Synapse data-processing workload.

Exam trap

The trap here is that candidates confuse general Azure services (Logic Apps, Functions, Power BI) with native Synapse Analytics processing capabilities, forgetting that only Synapse SQL and Synapse Spark are first-class compute engines within the service.

Full explanation →
477
MCQeasy

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2 using Azure Data Factory. Which approach is the most efficient?

A.Use a lookup activity to retrieve the last watermark value, then copy only new records with a filter.
B.Use a tumbling window trigger with a data flow that processes all data each time.
C.Use a mapping data flow with a full load and then use Azure Databricks to deduplicate.
D.Copy the entire table every time and use Azure Synapse serverless SQL to filter duplicates.
AnswerA

A watermark lookup captures the last-loaded value, and the copy activity's filter predicate then transfers only rows exceeding it, avoiding full-table reloads. This incremental pattern minimises data movement and pipeline duration compared with repeatedly copying the entire source table.

Why this answer

It uses a lookup activity to retrieve the last watermark value (e.g., a timestamp or incrementing key), then copies only new or changed records via a filter in the Copy activity. This minimizes data movement and processing time, making it the most efficient incremental loading approach in Azure Data Factory.

Exam trap

Microsoft often tests the misconception that any trigger-based or full-load approach can be adapted for incremental loading, but the key is minimizing data movement; candidates may overlook the watermark pattern and choose a full-load option because they think deduplication later solves the problem.

How to eliminate wrong answers

Option B is wrong because a tumbling window trigger with a data flow that processes all data each time performs a full load on every run, ignoring incremental logic and wasting resources. Option C is wrong because performing a full load followed by deduplication in Azure Databricks is inefficient; it moves all data repeatedly and adds unnecessary compute overhead. Option D is wrong because copying the entire table every time and using Azure Synapse serverless SQL to filter duplicates still transfers all data, incurring high egress costs and defeating the purpose of incremental loading.

Full explanation →
478
MCQmedium

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

A.Assign the Storage Blob Data Contributor role to the users on the Azure Data Lake Storage Gen2 account.
B.Generate a shared access signature (SAS) token for the storage account and include it in the external table definition.
C.Configure an IP firewall rule on the storage account to allow only the SQL pool's outbound IP addresses.
D.Create a managed identity for the SQL pool and grant it access to the storage account.
AnswerA

Correct. The Storage Blob Data Contributor role grants the user permissions to read data. Azure Synapse serverless SQL pools use the caller's Microsoft Entra ID identity to access Azure Data Lake Storage Gen2, so role-based access control (RBAC) ensures only authorized users can query the data.

Why this answer

Azure Synapse Analytics serverless SQL pools rely on Microsoft Entra ID tokens and RBAC roles to authorize access to data in Azure Data Lake Storage Gen2. The Storage Blob Data Contributor role grants read and write access to data. Option B is wrong because SAS tokens are shared secrets, not tied to user identity.

Option C is wrong because firewall rules control network access, not user-level authorization. Option D is wrong because managed identity is not suitable for per-user authorization.

Full explanation →
479
Multi-Selectmedium

You are designing a data processing solution in Azure Data Factory that must process files as they arrive in Azure Blob Storage. The solution must trigger a pipeline automatically when a new file is created, and then run a Databricks notebook to process the file. You need to configure the trigger and the pipeline activity. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Add a Databricks Notebook activity to the pipeline and configure it to pass the file path as a parameter.
B.Create an event-based trigger of type 'BlobCreated' and configure it to start the pipeline when a file is created.
C.Add a Copy activity to the pipeline to copy the file to a staging location before processing.
D.Add a Stored Procedure activity to the pipeline to call a stored procedure in Azure SQL Database.
E.Create a tumbling window trigger and configure it to run every 5 minutes to check for new files.
AnswersA, B

The Databricks Notebook activity in Azure Data Factory allows you to run a notebook in Azure Databricks. By passing the file path as a parameter, the notebook can process the specific file that triggered the pipeline. This is the correct activity to use for running a Databricks notebook in this scenario.

Why this answer

To trigger a pipeline when a file is created in Blob Storage, you need an event-based trigger of type BlobCreated. To process the file with a Databricks notebook, you add a Databricks Notebook activity and pass the file path as a parameter. Tumbling window triggers are scheduled, not event-driven, and Copy or Stored Procedure activities do not run Databricks notebooks.

Exam trap

The trap here is confusing event-based triggers with scheduled triggers, and assuming any activity can run a Databricks notebook.

Full explanation →
480
MCQmedium

Your organization uses Azure Purview for data governance. You need to automatically scan an Azure Data Lake Storage Gen2 account and classify sensitive data such as credit card numbers and social security numbers. What should you configure?

A.Azure Information Protection (AIP) scanner
B.Microsoft Defender for Cloud
C.A new scan rule set in Purview with classification rules for sensitive data types
D.Azure Policy with built-in guest configuration
AnswerC

Purview scan rule sets bind classification rules to a scan, so credit card and social security patterns are detected and labelled during the Data Lake Storage Gen2 scan. Without a rule set, the scan runs only system classifications, so the required sensitive data types would not be identified.

Why this answer

In Azure Purview, to automatically scan and classify sensitive data, you create a scan rule set that includes classification rules for the specific sensitive data types (e.g., credit card numbers, SSNs). The scan rule set is applied to the scan of the Azure Data Lake Storage Gen2 account, and Purview uses built-in or custom classification rules to identify and tag sensitive data.

Exam trap

DP-203 often tests the confusion between data governance tools (Purview) and security tools (Defender for Cloud, Azure Policy), so candidates must know that Purview is the service for data classification and scanning.

How to eliminate wrong answers

Option A is wrong because Azure Information Protection (AIP) scanner is used for on-premises and cloud file shares to classify and protect files, but it is not the primary tool for scanning Azure Data Lake Storage Gen2 in Purview. Option B is wrong because Microsoft Defender for Cloud provides security posture management and threat protection, not data classification and governance scanning. Option D is wrong because Azure Policy with guest configuration is used to audit and enforce settings on VMs and other resources, not to scan and classify data in storage accounts.

Full explanation →
481
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must ensure that data at rest in a dedicated SQL pool is encrypted using customer-managed keys (CMK) stored in Azure Key Vault. The encryption should be enabled at the database level. What should you configure?

A.Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.
B.Azure Purview data classification and encryption policies.
C.Azure Disk Encryption on the nodes hosting the dedicated SQL pool.
D.Always Encrypted with keys stored in Azure Key Vault.
AnswerA

TDE with a customer-managed key in Azure Key Vault encrypts data at rest at the database level in a dedicated SQL pool, satisfying the CMK and database-scope constraints. The key wraps the database encryption key, giving you control over the protector.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault provides database-level encryption for dedicated SQL pools in Azure Synapse Analytics. TDE encrypts data at rest, and using a customer-managed key (CMK) allows the organization to control the key lifecycle. Option B is incorrect because Azure Purview is for data governance and classification, not encryption.

Option C is incorrect because Azure Disk Encryption encrypts VM disks, not the SQL pool database. Option D is incorrect because Always Encrypted is a column-level encryption feature, not for full database encryption.

Full explanation →
482
MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads data into a staging table using PolyBase, then transforms and inserts it into a large fact table. You need to minimize data movement during the transformation step and ensure the fact table is optimized for large range scans. Which table distribution and index should you choose for the fact table?

A.Use a round-robin distributed table with a clustered index on the date column.
B.Use a replicated table with a clustered columnstore index.
C.Use a hash-distributed table on the date column with a heap index.
D.Use a hash-distributed table on the primary join key with a clustered columnstore index.
AnswerD

Hash distribution on the primary join key co-locates matching rows on the same distribution, minimizing data movement during joins and transformations. A clustered columnstore index compresses data and is optimized for large range scans and aggregations, which is exactly the workload described. This combination is the recommended pattern for large fact tables in a dedicated SQL pool.

Why this answer

For a large fact table in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during transformations and joins. Pairing it with a clustered columnstore index provides columnar compression and segment elimination, which accelerates the large range scans and aggregations typical of fact-table queries. Together they satisfy both stated requirements.

Exam trap

The trap here is choosing distribution or indexing based on load convenience rather than on the join keys and scan patterns that dominate the transformation and reporting workload.

Full explanation →
483
MCQmedium

You are implementing a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The query groups events into 30-second windows. You need to ensure that the job can handle late-arriving events up to 2 minutes after the window closes without dropping them. What should you configure?

A.Set the event ordering policy's late arrival tolerance to 2 minutes in the job's Event Ordering settings.
B.Change the window type from Tumbling to Sliding with a 2-minute duration.
C.Enable the 'Adjust event ordering' option and set the out-of-order tolerance to 2 minutes.
D.Increase the streaming units of the Stream Analytics job to accommodate the late events.
AnswerA

The late arrival tolerance in the event ordering policy tells Stream Analytics how long to wait for out-of-order events before finalizing a window. Setting it to 2 minutes ensures events arriving up to 2 minutes late are included in the correct window. This directly addresses the requirement without changing the query or output.

Why this answer

Late-arriving events are handled by the late arrival tolerance in the event ordering policy. This setting defines how long Stream Analytics waits for events that arrive after the window end before finalizing results. Configuring it to 2 minutes ensures events up to 2 minutes late are included.

Other options affect performance or window semantics but not late-event inclusion.

Exam trap

The trap here is confusing out-of-order tolerance with late arrival tolerance, which control different aspects of event timing.

Full explanation →
484
MCQmedium

You are designing a data pipeline to ingest streaming data from IoT devices into Azure Synapse Analytics. The data must be available for querying with minimal latency, but you also need to handle spikes in throughput without data loss. Which service should you use as the ingestion layer?

A.Azure Data Lake Storage Gen2
B.Azure Blob Storage
C.Azure IoT Hub
D.Azure Event Hubs
AnswerD

Azure Event Hubs ingests millions of events per second with partitioned consumer groups, buffering telemetry during throughput spikes so no data is dropped. Its native capture and Stream Analytics integration feed Synapse with minimal latency, satisfying both the spike-tolerance and low-latency querying constraints in the stem.

Why this answer

Azure Event Hubs is the correct choice because it is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from millions of IoT devices. It provides at-least-once delivery, supports partitioning for massive scale, and integrates natively with Azure Synapse Analytics via the Synapse Pipeline or Event Hubs Capture to handle throughput spikes without data loss.

Exam trap

The trap here is that candidates often confuse Azure IoT Hub with Event Hubs, assuming IoT Hub is the default for all IoT streaming, but IoT Hub is designed for device management and lower-throughput telemetry, whereas Event Hubs is the dedicated high-throughput ingestion service for analytics pipelines.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is a hierarchical file storage service designed for batch and analytical workloads, not for real-time streaming ingestion; it lacks native event streaming and buffering capabilities. Option B is wrong because Azure Blob Storage is an object storage service for unstructured data, not built for low-latency, high-throughput event ingestion; it would require additional services like Event Hubs to capture streaming data. Option C is wrong because Azure IoT Hub is a managed service for bi-directional communication with IoT devices, but it is not optimized for high-volume streaming ingestion into Synapse; it is better suited for device management and command/control scenarios, and its default throughput is lower than Event Hubs for pure data ingestion.

Full explanation →
485
MCQmedium

You have an Azure Synapse Analytics serverless SQL pool that queries data in Azure Data Lake Storage Gen2. You need to ensure that only users with specific Microsoft Entra ID groups can access the data through the serverless SQL pool. What should you configure?

A.Grant the Microsoft Entra ID group the Storage Blob Data Reader role on the storage account
B.Grant the Microsoft Entra ID group CONNECT permission on the serverless SQL pool and configure ACLs on the storage to allow read access for the group
C.Configure a firewall rule to allow only the Microsoft Entra ID group IP ranges
D.Use a shared access signature (SAS) token with the SQL pool and distribute it to users
AnswerB

Serverless SQL pool access is controlled at the SQL layer via CONNECT permission, while underlying ADLS Gen2 reads are authorised by POSIX ACLs. Granting both to the Microsoft Entra ID group restricts data access to those specific users.

Why this answer

Controlling access involves two layers: the serverless SQL pool and the underlying storage. Users must have both CONNECT permission on the SQL pool to query and appropriate ACLs on the storage to read the data. Granting the Microsoft Entra ID group CONNECT permission and configuring ACLs on the storage account to allow read access for the group ensures that only those users can access data through the serverless SQL pool.

Option A is incorrect because the Storage Blob Data Reader role alone does not grant SQL-level permissions. Option C is incorrect because firewall rules are network-level and do not provide granular user access. Option D is incorrect because SAS tokens are not recommended for user-level access and bypass Entra ID permissions.

Full explanation →
486
Drag & Dropmedium

Drag and drop the steps to implement Slowly Changing Dimension (SCD) Type 2 in Azure Synapse Analytics dedicated SQL pool into the correct order.

Drag or tap steps into the slots.

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

Why this order

SCD Type 2: stage data, merge to find changes, expire old records, insert new versions, and update attributes (if needed).

Full explanation →
487
MCQmedium

You are monitoring an Azure Cosmos DB account using Azure Monitor. The 'Normalized RU Consumption' metric for a container is consistently above 90%. You need to ensure that the container can handle the load without throttling. What should you do?

A.Change the partition key to a different property.
B.Increase the provisioned throughput (RU/s) for the container.
C.Switch the account to serverless mode.
D.Modify the indexing policy to exclude unused paths.
AnswerB

Normalized RU Consumption above 90% indicates the container is approaching its provisioned RU/s ceiling, so requests risk rate-limiting (429s). Raising the provisioned throughput directly increases the available RU/s budget, lowering normalised consumption and preventing throttling under the current load.

Why this answer

The 'Normalized RU Consumption' metric indicates the percentage of provisioned throughput (RU/s) being used. Consistently above 90% means the container is operating near its capacity limit, risking throttling (HTTP 429 errors) during traffic spikes. Increasing the provisioned throughput (RU/s) directly raises the capacity, allowing the container to handle the load without throttling.

Exam trap

The trap here is that candidates confuse optimizing RU consumption (e.g., indexing or partition key changes) with the need to increase capacity when the metric already shows the system is at its limit, leading them to choose options that reduce per-request cost rather than addressing the throughput ceiling.

How to eliminate wrong answers

Option A is wrong because changing the partition key does not increase the total throughput; it redistributes existing throughput across partitions, which may improve distribution but does not solve a capacity shortage. Option C is wrong because switching to serverless mode caps throughput at a lower maximum (typically 5,000 RU/s per container) and is intended for intermittent or low-traffic workloads, not for consistently high load. Option D is wrong because modifying the indexing policy to exclude unused paths reduces RU consumption per request, but with normalized RU already above 90%, the reduction is unlikely to bring consumption below the threshold and does not address the root cause of insufficient provisioned capacity.

Full explanation →
488
MCQeasy

You are tuning an Azure Stream Analytics job that reads from an Event Hub and writes to an Azure Synapse Analytics table. The job's SU% utilization is consistently at 90%. Which action would most likely reduce the SU% utilization?

A.Decrease the Event Hub throughput units.
B.Partition the output table in Azure Synapse Analytics.
C.Use a reference data join to filter events.
D.Increase the number of streaming units (SU) allocated to the job.
AnswerD

Streaming units represent the compute and memory capacity assigned to the job. Raising the SU count distributes the same query workload across more parallel processing nodes, directly lowering the percentage of allocated capacity consumed and relieving the 90% saturation.

Why this answer

Increasing the number of streaming units (SU) allocated to the job directly adds more compute resources, which reduces the SU% utilization by distributing the workload across more SUs. Since the job is consistently at 90% utilization, adding SUs lowers the per-SU load, preventing throttling and improving throughput. This is the standard scaling approach for Azure Stream Analytics when SU% is high.

Exam trap

The trap here is that candidates often confuse scaling the input source (Event Hub throughput units) or optimizing the output sink (partitioning) with directly addressing the compute bottleneck, but only increasing SUs reduces the compute utilization percentage.

How to eliminate wrong answers

Option A is wrong because decreasing Event Hub throughput units reduces the ingress capacity, which can cause backpressure and increase SU% utilization as the job struggles to keep up with incoming data. Option B is wrong because partitioning the output table in Azure Synapse Analytics improves write throughput but does not affect the compute load on the Stream Analytics job itself, so SU% utilization remains unchanged. Option C is wrong because using a reference data join to filter events adds additional processing overhead (lookups and state management), which would likely increase, not decrease, SU% utilization.

Full explanation →
489
MCQmedium

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

A.Partition by device ID first, then by date.
B.Partition by device ID only, with a separate directory for each device.
C.Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).
D.Partition by device type only, with a directory for each type.
AnswerC

Partitioning by date first lets the analytics engine prune to the last 30 days, then the device-type subfolder prunes further within each date. This hierarchical layout matches the query predicate exactly, minimising files scanned and reducing both I/O cost and query latency in ADLS Gen2.

Why this answer

Partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Exam trap

The trap here is that candidates often assume partitioning by the most granular attribute (device ID) first will provide the best performance, but they overlook that query patterns typically filter by time range, making date the most effective first-level partition for cost and performance optimization.

How to eliminate wrong answers

Option A is wrong because partitioning by device ID first, then by date, would require scanning all device ID partitions even when querying only recent data, leading to high I/O and cost. Option B is wrong because partitioning only by device ID with a separate directory per device does not support efficient date-range pruning; queries for the last 30 days would need to scan every device directory, which is prohibitively expensive for millions of devices. Option D is wrong because partitioning only by device type would force scanning all date directories for every query, even when the query is limited to a specific time range, resulting in unnecessary data reads and higher costs.

Full explanation →
490
MCQeasy

A company is planning to migrate an on-premises data warehouse to Azure Synapse Analytics dedicated SQL pool. The data warehouse contains a large fact table with billions of rows and several dimension tables. The company wants to optimize query performance and minimize data movement during joins. They need to choose an appropriate distribution type for the fact table. The fact table is frequently joined with dimension tables on a column that has high cardinality and is evenly distributed. What distribution type should they use?

A.Replicated distribution
B.Hash distribution on the join column
C.Round-robin distribution
D.Hash distribution on a column with low cardinality
AnswerB

Hash distribution on the join column ensures that rows with the same join key are stored in the same distribution, eliminating data movement during joins. For a high-cardinality, evenly distributed column, this provides balanced data distribution and optimal query performance. This is the recommended approach for large fact tables in Azure Synapse Analytics dedicated SQL pool.

Why this answer

Hash distribution on the join column aligns the data layout with the most common join operation, ensuring that matching rows are colocated and eliminating the need for data movement. Since the column has high cardinality and even distribution, it also provides balanced data across distributions, which is critical for query performance in a dedicated SQL pool.

Exam trap

The trap here is assuming that round-robin distribution is always best for large tables, but it causes data movement during joins; hash distribution on the join column is preferred when the column is high-cardinality and evenly distributed.

Full explanation →
491
MCQeasy

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline occasionally fails with a timeout error. You need to identify the cause of the failures and receive proactive alerts when similar issues occur. What should you do?

A.Configure a tumbling window trigger with retry policy.
B.Use Azure Advisor recommendations for Data Factory.
C.Increase the DIU count for the copy activity.
D.Enable diagnostic settings to send logs to Azure Monitor and create an alert rule on the pipeline failure metric.
AnswerD

Enabling diagnostic settings streams pipeline run logs and metrics to Azure Monitor, where you can analyze failures and create alert rules based on metrics like PipelineFailedRuns. This provides both the diagnostic data to identify the timeout cause and proactive notifications. It directly addresses the need to monitor and alert on failures without manual intervention.

Why this answer

Diagnostic settings in Azure Data Factory can route detailed logs and metrics to Azure Monitor, enabling analysis of pipeline failures. Creating an alert rule on failure metrics ensures you are notified proactively. This combination provides both root-cause investigation and ongoing monitoring.

Other options either mask failures, improve performance without visibility, or offer generic recommendations.

Exam trap

The trap here is focusing on performance tuning or automatic retries to address timeouts, while overlooking that the core requirement is to diagnose the cause and get alerted, which is achieved through monitoring and alerts.

Full explanation →
492
MCQmedium

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You notice high queue wait times in sys.dm_pdw_exec_requests. What is the most likely cause?

A.Outdated statistics
B.A single long-running query blocking others
C.Concurrency throttling due to insufficient resources
D.Data skew in distribution
AnswerC

Dedicated SQL pools cap concurrent queries per resource class; when demand exceeds that limit, requests queue in sys.dm_pdw_exec_requests. High queue wait times therefore indicate concurrency throttling, not data skew or statistics staleness, which would slow execution rather than delay admission.

Why this answer

High queue wait times in sys.dm_pdw_exec_requests typically indicate that queries are waiting for resources due to concurrency limits. In Azure Synapse dedicated SQL pools, each DWU level supports a fixed number of concurrent queries and slots; when the number of concurrent queries exceeds this limit, additional queries are queued. Therefore, the most likely cause is concurrency throttling due to insufficient resources, making option C correct.

Exam trap

DP-203 often tests the interpretation of DMV outputs; candidates might attribute queue waits to blocking or skew, but the specific DMV and symptom point to concurrency limits.

How to eliminate wrong answers

Option A is wrong because outdated statistics can cause poor query plans and longer execution times, but they do not directly cause high queue wait times; they would increase execution duration, not waiting for resources. Option B is wrong because a single long-running query blocking others would show as blocking waits (e.g., LCK_M_* waits) rather than queue waits; sys.dm_pdw_exec_requests would show the blocking session, but the question specifies high queue wait times. Option D is wrong because data skew can cause performance degradation and longer query times, but it does not directly lead to queue waits; it would affect individual query performance, not overall concurrency.

Full explanation →
493
MCQmedium

Refer to the exhibit. You have an Azure Data Factory pipeline that copies trade data from Azure Blob Storage to Azure SQL Database. The pipeline runs every hour and truncates the destination table before each copy. However, users report that data is missing during the copy window. What is the most likely cause?

A.The output dataset is not correctly configured
B.The writeBatchSize is too low, causing timeouts
C.The preCopyScript truncates the table before the copy completes, causing a period with no data
D.The source dataset is set to recursive, which includes unwanted files
AnswerC

The preCopyScript runs before the copy activity begins, truncating the destination table. Until the copy writes rows, the table is empty, so users querying during that window see missing data. This is the specific mechanism causing the reported gap, not a pipeline scheduling or mapping fault.

Why this answer

The preCopyScript truncates the destination table before the copy activity begins writing data, creating a window where the table is empty or partially populated. Since the pipeline runs every hour and users query the table during the copy window, they see missing data until the copy completes. This is the classic 'truncate-then-load' anti-pattern in ADF that causes data availability gaps.

The correct fix is to use a staging table with an atomic swap or to use incremental loading instead of truncation.

Exam trap

DP-203 often tests the misconception that preCopyScript is a safe way to refresh data — candidates must recognize that truncating before a copy creates a data availability gap, and the exam expects you to identify this as the root cause of 'missing data during the copy window' symptoms.

How to eliminate wrong answers

Option A is wrong because an incorrectly configured output dataset would typically cause the copy to fail entirely or write to the wrong location — it would not produce a consistent pattern of missing data only during the copy window. Option B is wrong because a low writeBatchSize would cause slower performance or timeouts, not a predictable data gap during every hourly run — and timeouts would manifest as pipeline failures, not silent data absence. Option D is wrong because a recursive source setting would include extra files (potentially duplicating or adding unwanted data), not cause missing data during the copy window — the symptom described is a temporal gap, not data contamination.

Full explanation →
494
MCQeasy

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

A.Enable Optimized Write on the Delta table.
B.Enable Auto Optimize on the Delta table.
C.Rely on Delta Lake's built-in ACID transactions.
D.Use Dynamic Partition Pruning in your Spark jobs.
AnswerC

Delta Lake's ACID transaction log serialises concurrent writes, guaranteeing atomicity and consistency across simultaneous jobs. This satisfies the stem's requirement for atomic, consistent operations when multiple jobs write to the same Delta table, without needing external locking mechanisms or manual coordination.

Why this answer

Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Exam trap

The trap here is that candidates confuse performance-tuning features (Optimized Write, Auto Optimize, Dynamic Partition Pruning) with transactional guarantees, assuming they provide atomicity or consistency when they only address file layout or query speed.

How to eliminate wrong answers

Option A is wrong because Optimized Write is a performance feature that reduces the number of small files written by coalescing partitions, but it does not provide atomicity or consistency guarantees for concurrent writes. Option B is wrong because Auto Optimize is a Delta Lake feature that automatically compacts small files and optimizes file layout, but it does not handle transactional concurrency or atomicity. Option D is wrong because Dynamic Partition Pruning is a Spark SQL optimization that improves query performance by skipping irrelevant partitions during joins, not a mechanism for ensuring atomic or consistent concurrent writes.

Full explanation →
495
Multi-Selecthard

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Select 3 answers
A.Enable Network Security Group flow logs for the storage account subnet.
B.Enable Storage Insights to monitor capacity and transactions.
C.Configure lifecycle management policies to move cold data to archive tier.
D.Use Azure Storage Analytics logs to analyze latency and request rate.
E.Enable Azure Monitor diagnostic settings to capture read and write requests.
AnswersB, D, E

Storage Insights provides capacity and transaction metrics for Azure Data Lake Storage Gen2, directly satisfying the requirement to monitor performance. It surfaces throttling, latency and request-volume trends through Azure Monitor workbooks, letting you detect hotspots and optimise partitioning or request patterns before throughput degrades.

Why this answer

Option B is correct because Azure Storage Insights (part of Azure Monitor) provides built-in dashboards for monitoring ADLS Gen2 capacity, transactions, latency, and availability metrics, which is essential for tracking and optimizing storage performance. Option D is correct because Storage Analytics logging captures detailed per-request records (including latency, request rate, and operation type) for blob and Data Lake Gen2 endpoints, enabling granular performance analysis. Option E is correct because Azure Monitor diagnostic settings route resource logs (such as StorageRead, StorageWrite, and StorageDelete) and metrics to Log Analytics, Event Hubs, or Storage, allowing you to query read/write request patterns and diagnose performance bottlenecks.

Option A is not correct because NSG flow logs capture IP-level network traffic metadata for security and connectivity troubleshooting, not storage performance or capacity metrics. Option C is not correct because lifecycle management policies optimize cost by tiering or deleting data based on age, not by monitoring or optimizing performance.

Exam trap

DP-203 often tests whether candidates pick cost-optimization features (lifecycle policies) or network-layer tools (NSG flow logs) when the question is specifically about performance monitoring of storage.

Full explanation →
496
MCQeasy

You are designing a data processing solution in Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 and you need to perform transformations using Apache Spark. The security requirements mandate that all data in transit must be encrypted and that the storage account must not be accessible from the public internet. What should you configure?

A.Enable the storage account firewall and add a private endpoint for Azure Databricks to use.
B.Disable TLS on the storage account and use a shared access signature (SAS) token for authentication.
C.Use Azure Databricks with VNet injection and configure a service endpoint for the storage account.
D.Configure the storage account to use HTTPS only and enable firewall rules to allow only Azure services.
AnswerA

A private endpoint gives Azure Databricks a private IP path to the storage account, removing public internet exposure, while the storage firewall blocks all other networks. Traffic over this private link is encrypted in transit, satisfying both stated requirements.

Why this answer

It satisfies both security requirements: encrypting data in transit and preventing public internet access. A private endpoint uses Azure Private Link to connect Azure Databricks to the storage account over the Microsoft backbone network, ensuring all traffic stays within the Azure network and is encrypted via TLS. The storage account firewall is then configured to deny all public traffic, so only the private endpoint can access the storage account, meeting the 'not accessible from the public internet' mandate.

Exam trap

The trap here is confusing service endpoints (which still use public endpoints) with private endpoints (which use private IPs and fully isolate the resource from the internet), leading candidates to pick Option C thinking VNet injection plus service endpoints provides complete public internet isolation.

How to eliminate wrong answers

Option B is wrong because disabling TLS removes encryption in transit, violating the requirement that all data in transit must be encrypted; SAS tokens provide authentication but do not encrypt the channel. Option C is wrong because a service endpoint still exposes the storage account to the public internet (it only restricts source IPs via the firewall), so it does not meet the 'not accessible from the public internet' requirement; VNet injection alone does not enforce private connectivity. Option D is wrong because enabling HTTPS only and allowing only Azure services still leaves the storage account accessible from the public internet (Azure services can originate from public IPs), failing the requirement to block all public internet access.

Full explanation →
497
MCQeasy

You are designing a data storage solution for real-time streaming data from IoT devices. The data must be stored in its original format for immediate processing and later transformed for analytics. Which Azure service should you use for raw data ingestion?

A.Azure Data Lake Storage Gen2
B.Azure Event Hubs
C.Azure Stream Analytics
D.Azure Data Factory
AnswerB

Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for later transformation. This makes it the correct choice for raw data ingestion.

Why this answer

Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for up to 7 days (or longer with Event Hubs Capture) for later transformation and analytics. This makes it the correct choice for raw data ingestion before any transformation occurs.

Exam trap

The trap here is that candidates confuse data ingestion (Event Hubs) with data storage (Data Lake Storage) or data processing (Stream Analytics), assuming a single service must handle both raw capture and transformation, when in fact the question explicitly asks for raw data ingestion only.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is a hierarchical file store for analytics workloads, not a real-time ingestion endpoint; it cannot natively accept streaming events at high velocity without an intermediary like Event Hubs or IoT Hub. Option C is wrong because Azure Stream Analytics is a stream processing engine that consumes data from sources like Event Hubs and performs real-time transformations, but it does not store raw data itself—it outputs results to sinks. Option D is wrong because Azure Data Factory is a cloud-based ETL and orchestration service for batch and scheduled data movement, not designed for real-time, high-throughput streaming ingestion from IoT devices.

Full explanation →
498
MCQmedium

You are monitoring an Azure Data Factory pipeline that copies data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The pipeline runs hourly. You notice that the copy activity sometimes takes much longer than expected. You need to identify the cause of the performance variability. Which action should you take first?

A.Enable Azure Monitor diagnostic settings for the Data Factory and analyze the Copy activity duration metrics in Azure Monitor Logs.
B.Configure a self-hosted integration runtime on a virtual machine with more memory and CPU.
C.Increase the Data Integration Units (DIUs) for the copy activity to the maximum allowed.
D.Enable Azure SQL Database Auditing and review the audit logs for long-running queries.
AnswerA

Azure Monitor diagnostic settings can send Data Factory logs and metrics to a Log Analytics workspace, where you can query Copy activity duration and identify patterns or spikes. This provides the detailed telemetry needed to diagnose performance variability. It is the most direct and appropriate first step for monitoring and troubleshooting.

Why this answer

To diagnose performance variability in an Azure Data Factory copy activity, you need detailed telemetry. Enabling diagnostic settings and analyzing metrics and logs in Azure Monitor provides insights into activity duration, data read/written, and errors. This data helps pinpoint whether the issue is due to source throttling, network latency, or other factors.

Exam trap

The trap here is jumping to scaling resources without first collecting diagnostic data to understand the root cause of the performance issue.

Full explanation →
499
MCQeasy

A company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. They need to restrict a specific application's access to only write files in a particular directory without being able to read or list files. Which type of permission should be assigned?

A.Configure a firewall rule to allow only the application's IP address.
B.Configure an access control list (ACL) that grants execute and write permissions to the application's service principal.
C.Generate a shared access signature (SAS) with write and list permissions.
D.Assign the Storage Blob Data Contributor role at the storage account level.
AnswerB

POSIX ACLs on Data Lake Storage Gen2 let you grant write and execute on a directory without read, so the service principal can create files but cannot list or read existing ones. This satisfies the least-privilege write-only requirement that RBAC alone cannot express.

Why this answer

In ADLS Gen2 with hierarchical namespace, POSIX-style ACLs are the granular permission mechanism. To allow an application to write files in a directory without reading or listing, you grant the service principal 'execute' (traverse) and 'write' permissions on the directory. Execute allows traversal through the directory, and write allows creating files within it; without read, the principal cannot list directory contents.

This is the least-privilege approach for the stated requirement.

Exam trap

DP-203 often tests the distinction between RBAC (coarse, account-level) and ACLs (fine-grained, directory-level) in ADLS Gen2, and candidates must remember that 'write without read' requires execute+write ACLs, not a Contributor role or SAS with list.

How to eliminate wrong answers

Option A is wrong because firewall rules restrict network access by IP, not file-system permissions — they do not differentiate read, write, or list operations and would not satisfy the granular requirement. Option C is wrong because a SAS with write and list permissions grants listing capability, which the requirement explicitly forbids, and SAS is a delegated access token rather than a directory-level ACL. Option D is wrong because the Storage Blob Data Contributor role is a broad RBAC role that grants read, write, and delete across the entire storage account, far exceeding the requirement and violating least privilege.

Full explanation →
500
MCQmedium

You are designing a storage solution for a global application that requires low-latency reads and writes of JSON documents. The data model includes nested properties, and you need to query these properties efficiently. You also need to ensure the data is available in multiple regions with automatic failover. Which Azure service should you use?

A.Azure SQL Database with active geo-replication.
B.Azure Blob Storage with RA-GRS replication.
C.Azure Table Storage with a partition key and row key.
D.Azure Cosmos DB with Core (SQL) API and multi-region writes enabled.
AnswerD

Azure Cosmos DB is a globally distributed, multi-model database that natively supports JSON documents and allows querying nested properties using SQL-like syntax. With multi-region writes enabled, it provides low-latency reads and writes across multiple regions and automatic failover. It is designed for global distribution and elastic scalability, making it ideal for this scenario.

Why this answer

Azure Cosmos DB with Core (SQL) API is designed for JSON documents, supports querying nested properties, and offers multi-region writes with automatic failover, ensuring low latency globally. Other services either lack native JSON support, do not support multi-region writes, or are not optimized for document queries.

Exam trap

The trap here is assuming that relational databases with geo-replication provide multi-region writes; most only allow writes to a single primary region.

Full explanation →
501
Multi-Selecthard

Which THREE components are valid parts of the Microsoft Purview Data Map? (Choose THREE)

Select 3 answers
A.Scan rule sets
B.Sensitivity labels
C.Data flows
D.Data sources
E.Classifications
AnswersA, D, E

Scan rule sets are a genuine Data Map construct: they define which file types and classification rules a scan applies to a registered source. This satisfies the stem's requirement for valid Data Map components, sitting alongside the collection, source, asset and scan constructs that Microsoft Purview uses to populate the map.

Why this answer

In Microsoft Purview Data Map, scan rule sets (A) are valid components because they define which file types and system types are scanned and which classification rules are applied during a scan. Data sources (D) are valid because the Data Map registers and stores metadata about sources such as Azure SQL Database, SQL Server, and Microsoft Fabric, which are then scanned and cataloged. Classifications (E) are valid because they are the built-in or custom classification definitions (for example, credit card number or passport number) that scans use to tag assets in the Data Map.

Sensitivity labels (B) are not a Data Map component; they belong to Microsoft Purview Information Protection and are applied to content, not to the Data Map's scanning/cataloging structure. Data flows (C) are not a Data Map component either; data flows are associated with Azure Data Factory/Synapse pipelines rather than the Purview Data Map's scanning and metadata model.

Full explanation →
502
MCQeasy

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. You need to minimize data movement during query execution for joins between the fact table and smaller dimension tables. What should you do?

A.Distribute the fact table using hash distribution on the join column and replicate the dimension tables.
B.Distribute the fact table using hash distribution on the primary key and replicate the dimension tables.
C.Distribute the fact table using round-robin distribution and replicate the dimension tables.
D.Distribute the fact table using hash distribution on the join column and distribute the dimension tables using round-robin distribution.
AnswerA

Hash distributing the fact table on the join column ensures that rows with the same join key are co-located on the same distribution. Replicating the smaller dimension tables makes them available on all compute nodes, eliminating the need to shuffle dimension data during joins. This minimizes data movement and improves query performance for star-schema joins.

Why this answer

Hash distributing the fact table on the join column co-locates matching rows, and replicating small dimension tables places them on all compute nodes. This combination eliminates data movement during joins, which is critical for performance in dedicated SQL pools. The other options either use inappropriate distribution or do not fully minimize movement.

Exam trap

The trap here is assuming that hash distribution on the primary key always aligns with join columns, which is often not the case in fact tables.

Full explanation →
503
MCQmedium

Your company stores sensitive customer data in Azure SQL Database. You need to encrypt the data at rest and ensure that only your application can decrypt it, even from database administrators. What should you implement?

A.Transparent Data Encryption (TDE)
B.Always Encrypted
C.Dynamic Data Masking
D.Azure Storage Service Encryption
AnswerB

Always Encrypted keeps column encryption keys on the client, so data is encrypted at rest and decrypted only by the application holding the keys. Database administrators see ciphertext, satisfying the requirement that even privileged administrators cannot read sensitive customer data.

Why this answer

Always Encrypted is correct because it ensures that sensitive data is encrypted at rest and in use, and the encryption keys are stored client-side, so only the application can decrypt the data. Database administrators (DBAs) cannot access the plaintext data because they lack the column encryption keys, even though they have full administrative access to the database.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with client-side encryption, assuming TDE protects against DBA access, but TDE only protects data at rest from storage theft, not from authorized database users.

Why the other options are wrong

A

TDE encrypts data at rest but the database engine holds the keys, allowing DBAs to decrypt.

C

Only masks data from unauthorized users; data is still stored in plaintext.

D

This applies to Azure Storage, not SQL Database.

Full explanation →
504
MCQmedium

You are designing a data storage solution for a financial analytics platform. The platform ingests CSV files into Azure Data Lake Storage Gen2 and processes them with Azure Synapse Analytics serverless SQL pools. Queries frequently filter on a transaction date column and a region column, but the files are currently organized in a flat folder structure. You need to minimize the amount of data scanned by serverless SQL queries while keeping the files queryable using standard T-SQL OPENROWSET. What should you do?

A.Move the files into an Azure Blob Storage container and query them using the Blob Storage REST API.
B.Partition the data by transaction date and region using a Hive-style folder hierarchy, then query with OPENROWSET and a wildcard path.
C.Convert all CSV files to Parquet and place them in a single folder without subfolders.
D.Create an external table in the serverless SQL pool over the entire folder path and add a clustered columnstore index.
AnswerB

A Hive-style hierarchy such as /year=2024/month=03/region=us/ enables serverless SQL pools to perform partition elimination when the query filters on those columns and uses FILEPATH or the appropriate wildcard path. This reduces the bytes scanned, lowering cost and latency, and keeps the data queryable through standard T-SQL OPENROWSET without any additional service.

Why this answer

Organizing files into a Hive-style folder hierarchy by transaction date and region allows the serverless SQL pool to perform partition elimination. When the query filters on those virtual columns, only the relevant folders are read, reducing scanned bytes and cost. The data remains fully queryable with standard T-SQL OPENROWSET, satisfying both the performance and compatibility requirements.

Exam trap

The trap here is assuming that converting to Parquet alone solves the scanning problem, when folder-based partitioning is what enables partition elimination in serverless SQL pools.

Full explanation →
505
MCQhard

Refer to the exhibit. A Bicep file is used to deploy an Azure Synapse Analytics workspace. What is the purpose of the 'purviewConfiguration' property?

A.It links the workspace to a Microsoft Purview account for data lineage and cataloging
B.It configures automated backups of the Synapse workspace
C.It enables monitoring of data movement by Azure Monitor
D.It connects the workspace to a data catalog for pipeline sources
AnswerA

The purviewConfiguration property binds the Synapse workspace to a Microsoft Purview account, enabling automatic lineage capture and metadata cataloging for workspace artefacts. This satisfies the requirement to govern and trace data assets across the workspace.

Why this answer

The 'purviewConfiguration' property in a Bicep file for Azure Synapse Analytics links the workspace to a Microsoft Purview account. This integration enables automated data lineage tracking, cataloging, and discovery across the Synapse environment, allowing users to search for and govern data assets directly from Purview. Without this property, the Synapse workspace operates independently of Purview's unified data governance capabilities.

Exam trap

The trap here is that candidates confuse the Purview integration with general cataloging or monitoring features, assuming it only applies to pipeline sources rather than understanding it provides full data lineage and cataloging across the entire Synapse workspace.

How to eliminate wrong answers

Option B is wrong because automated backups of a Synapse workspace are configured via the 'sqlPoolBackup' or workspace-level backup policies, not through the 'purviewConfiguration' property, which is solely for Purview integration. Option C is wrong because enabling monitoring of data movement by Azure Monitor is done through diagnostic settings and workspace-level monitoring configurations, not by linking to Purview. Option D is wrong because connecting the workspace to a data catalog for pipeline sources is a general description of Purview's role, but the specific purpose of 'purviewConfiguration' is to link to a Microsoft Purview account for full data lineage and cataloging, not just for pipeline sources.

Full explanation →
506
Multi-Selecthard

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Select 3 answers
A.Partition switching can be used to quickly load data into a table.
B.Partitions are automatically aligned with distributions.
C.Each partition is stored as a separate set of rowgroups in a columnstore index.
D.Partitioning is only supported on tables with clustered rowstore indexes.
E.Excessive partitioning can lead to fragmentation and poor query performance.
AnswersA, C, E

Partition switching uses ALTER TABLE ... SWITCH to move a staging table's partition into the target table's matching partition as a metadata operation, avoiding row-by-row insertion. This satisfies the requirement for fast data loading by replacing expensive DML with near-instant metadata swaps.

Why this answer

Option A is correct because partition switching (ALTER TABLE ... SWITCH PARTITION) is a metadata-only operation that instantly moves a fully prepared staging table's partition into the target table, making it a fast way to load data. Option C is correct because in a clustered columnstore index each partition is stored as its own set of rowgroups, so partition boundaries also define rowgroup boundaries.

Option E is correct because creating too many partitions (especially small ones) increases metadata overhead, causes rowgroup fragmentation, and degrades query performance due to reduced segment elimination efficiency. Option B is not correct because partitions and distributions are independent constructs; partitioning does not automatically align with the 60 distributions, and alignment must be managed explicitly. Option D is not correct because partitioning is supported on clustered columnstore, clustered rowstore, and heap tables in dedicated SQL pools, not only on clustered rowstore indexes.

Exam trap

The trap here is that candidates often confuse partitions with distributions, thinking they are automatically aligned, or assume partitioning is only for rowstore indexes, when in fact columnstore indexes are the recommended and most common storage type for partitioning in dedicated SQL pool.

Full explanation →
507
MCQeasy

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

A.Azure SQL Database
B.Azure Blob Storage
C.Azure Event Hubs
D.Azure Data Lake Storage
AnswerC

Event Hubs ingests millions of telemetry events per second with low latency, buffering the stream for downstream consumers. This satisfies the real-time dashboard requirement while retaining data for long-term analytics, unlike batch-oriented stores such as Blob Storage or Azure SQL Database.

Why this answer

Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Exam trap

The trap here is that candidates confuse the ingestion layer with the storage layer, choosing Azure Blob Storage or Data Lake Storage because they think 'store data' means persistent storage, but the question specifically asks for the ingestion layer where real-time, low-latency streaming is required, which Event Hubs uniquely provides.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational OLTP store optimized for structured queries and ACID transactions, not for high-velocity, schema-less JSON ingestion at millions of events per second, and it would introduce latency and cost bottlenecks. Option B is wrong because Azure Blob Storage is an object store designed for batch and large-file storage, not for real-time, per-second event ingestion; it lacks native streaming ingestion, pub-sub semantics, and sub-second latency for dashboards. Option D is wrong because Azure Data Lake Storage is a hierarchical file system optimized for analytics on large datasets, not for real-time event ingestion; it is typically used as a destination for data after it has been processed or captured from a streaming source like Event Hubs.

Full explanation →
508
MCQmedium

You are designing a security strategy for an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to ensure that only specific Azure services can access the storage account. What should you do?

A.Configure Azure Private Link and enable soft delete.
B.Enable infrastructure encryption and use shared access signatures (SAS) for access.
C.Configure a customer-managed key for encryption and set the storage firewall to allow access from selected Azure services.
D.Enable Azure Defender for Storage and configure a private endpoint.
AnswerC

Using a customer-managed key stored in Azure Key Vault satisfies the encryption at rest requirement with customer control. Configuring the storage account firewall to allow access from selected Azure services, such as Azure Synapse Analytics or Azure Data Factory, restricts access to only those services. This combination directly meets both the encryption and service-level access requirements without overcomplicating the solution.

Why this answer

Customer-managed keys in Azure Key Vault enable encryption at rest with keys controlled by the organization. Configuring the storage firewall to allow access from selected Azure services ensures that only trusted services can reach the data. Together, these settings satisfy the encryption and access restriction requirements.

Other options address network isolation or threat detection but not the specific encryption and service-level access controls needed.

Exam trap

The trap here is confusing network-level security features like private endpoints or Azure Defender with the requirement for customer-managed encryption keys and service-specific access, which are configured separately.

Full explanation →
509
MCQeasy

You need to transform data in Azure Synapse Analytics using a language that supports procedural logic and error handling. Which option should you use?

A.T-SQL stored procedures
B.CREATE VIEW
C.PolyBase
D.CREATE EXTERNAL TABLE
AnswerA

T-SQL stored procedures provide procedural constructs such as IF, WHILE, TRY/CATCH and transactions within Synapse, satisfying the requirement for procedural logic and error handling. Spark notebooks and Mapping Data Flows offer transformation but not native T-SQL error-handling semantics against dedicated SQL pools.

Why this answer

T-SQL stored procedures are the correct choice because they support procedural logic (e.g., IF/ELSE, loops, TRY/CATCH) and error handling within Azure Synapse Analytics dedicated SQL pools. This allows you to encapsulate complex data transformation logic, handle runtime errors gracefully, and manage transactions, which is not possible with declarative objects like views or external tables.

Exam trap

The trap here is that candidates confuse PolyBase's ability to query external data with the ability to perform procedural transformations, overlooking that PolyBase is a query engine, not a programming construct for logic and error handling.

How to eliminate wrong answers

Option B is wrong because CREATE VIEW creates a read-only virtual table that cannot contain procedural logic or error handling; it is purely declarative. Option C is wrong because PolyBase is a data virtualization technology for querying external data sources (e.g., Azure Blob Storage) using T-SQL, but it does not support procedural logic or error handling itself. Option D is wrong because CREATE EXTERNAL TABLE defines a schema for external data but provides no procedural capabilities or error handling; it is a metadata object for PolyBase queries.

Full explanation →
Page 6

Page 7 of 7

All pages

1234567

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

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