DP-203 Practice Test 16 — 15 Questions

Question 1

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

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

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

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 2

You have an Azure Data Lake Storage Gen2 account that stores large volumes of parquet files. A reporting application frequently queries a specific subset of data filtered by a 'region' column. To minimize query latency and cost, which optimization should you implement?

Accepted Answer

Partition the data by region in the folder structure.. Partitioning the data by region in the folder structure (e.g., /region=NorthAmerica/...) enables Azure Data Lake Storage Gen2 and query engines like Azure Synapse or PolyBase to perform partition pruning. This skips scanning irrelevant files entirely, reducing I/O and query latency while lowering cost by minimizing data processed.

Answer

Create a clustered index on the region column.

Answer

Compress the parquet files using gzip.

Answer

Enable hierarchical namespace on the storage account.

Question 3

Which TWO of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Use customer-managed keys in Azure Key Vault. Option C is correct because customer-managed keys (CMK) stored in Azure Key Vault let you control the encryption keys used for Azure Storage Service Encryption, so data written to ADLS Gen2 is encrypted at rest with keys you own and can rotate or revoke. Option D is correct because Azure Storage Service Encryption (SSE) is the built-in mechanism that automatically encrypts data at rest in ADLS Gen2 (and Blob/File storage) using AES-256, protecting stored data without any application changes. Option A is not a data-at-rest encryption method; RBAC roles govern authorization to access data, not how it is encrypted on disk. Option B is not correct because storage firewall rules restrict network access to the storage account, which is a network/perimeter control rather than encryption of stored data. Option E is not correct because TLS 1.2 secures data in transit over the wire, not data at rest.

Answer

Assign RBAC roles for data access

Answer

Configure storage firewall rules

Answer

Enable TLS 1.2 for all connections

Question 4

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

Accepted Answer

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

Answer

Power BI as the output sink

Answer

Azure Data Factory pipeline for orchestration

Question 5

You use Azure Data Lake Storage Gen2 with a hierarchical namespace. You need to delegate permissions to a group of data scientists so they can create folders and upload files only within a specific directory path. What is the best way to achieve this?

Accepted Answer

Set ACL entries on the specific directory path granting read, write, and execute permissions to the users.. Azure Data Lake Storage Gen2 with hierarchical namespace supports POSIX-like ACLs at the directory and file level. To delegate permissions to a group of data scientists so they can create folders and upload files only within a specific directory path, you set ACL entries on that directory granting the necessary permissions (read, write, execute) to the group. This allows granular access control without granting broader permissions at the storage account level.

Answer

Use a stored access policy to grant permissions to the directory.

Answer

Generate a shared access signature (SAS) with permissions scoped to the specific directory.

Answer

Assign the Storage Blob Data Contributor role to the users at the storage account level.

Question 6

You need to monitor the health of your Azure Data Lake Storage Gen2 account. Which metric should you use to track the number of successful and failed requests?

Accepted Answer

Transactions.. Transactions metric tracks all requests. Option B is wrong because Success E2E Latency measures latency, not count. Option C is wrong because Blob Capacity measures storage size. Option D is wrong because Ingress is about data incoming, not request count.

Answer

Success E2E Latency.

Answer

Blob Capacity.

Answer

Ingress.

Question 7

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?

Accepted Answer

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. 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.

Answer

Grant the Microsoft Entra ID group the Storage Blob Data Reader role on the storage account

Answer

Configure a firewall rule to allow only the Microsoft Entra ID group IP ranges

Answer

Use a shared access signature (SAS) token with the SQL pool and distribute it to users

Question 8

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

Accepted Answer

Azure Synapse Studio monitoring hub and dynamic management views (DMVs).. Azure Synapse Studio monitoring hub provides a centralized view of query performance, including long-running queries and resource utilization. Dynamic management views (DMVs) offer detailed, real-time insights into query execution and resource bottlenecks. Together, they are the primary tools for monitoring dedicated SQL pool performance.

Answer

Microsoft Purview Data Map.

Answer

Azure Log Analytics queries.

Answer

Azure Monitor Workbooks.

Question 9

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

Accepted Answer

sys.dm_pdw_exec_requests. The correct DMV is sys.dm_pdw_exec_requests, which provides information about all requests currently executing or recently completed on the dedicated SQL pool. It includes status, submit time, and other details, making it the primary view for monitoring active queries. This DMV is specifically designed for tracking request-level execution.

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_request_steps

Answer

sys.dm_pdw_errors

Question 10

Your company uses Azure Synapse Analytics to run a large-scale batch processing job every night. The job currently runs on a dedicated SQL pool and takes 4 hours. Management wants to reduce the runtime to under 2 hours without increasing cost. The job involves heavy compute operations with no data movement limitations. What should you do?

Accepted Answer

Increase the service level objective (DWU) of the dedicated SQL pool.. Increasing the service level objective (DWU) of the dedicated SQL pool provides more compute resources, directly reducing the runtime of compute-heavy batch jobs. Since the pool runs for fewer hours, the total cost (DWU × hours) may remain the same or even decrease, meeting the requirement to reduce runtime without increasing cost. Materialized views (A) and result-set caching (D) benefit repeated queries, not a unique nightly job. Workload management (C) only prioritizes resources, without increasing overall compute power.

Answer

Create materialized views on frequently queried tables.

Answer

Implement workload management to prioritize the job.

Answer

Enable result-set caching on the dedicated SQL pool.

Question 11

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

Accepted Answer

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

Answer

Assign the Storage Blob Data Contributor role and create a deny assignment that denies delete.

Answer

Assign the Storage Blob Data Contributor role and use ACLs to deny delete on the container.

Answer

Assign the Storage Blob Data Contributor role and remove the delete permission at the role assignment scope.

Question 12

Refer to the exhibit. A data engineer wants to copy only new orders from an Azure SQL database to Azure Data Lake Storage Gen2. The pipeline runs daily at midnight. What should be added to the pipeline to ensure incremental loads?

Accepted Answer

Use a Lookup activity to get the last run timestamp and modify the query to use it.. To perform incremental loads from Azure SQL Database to ADLS Gen2, the pipeline must know the last successful run timestamp to fetch only new records. A Lookup activity can retrieve the last run timestamp from a control table or a file, and then the copy activity's source query can be parameterized to filter records based on that timestamp. This ensures only new orders are copied daily.

Answer

Add a filter activity after the copy to remove duplicates.

Answer

Enable staging with a staging table.

Answer

Change the copy behavior to 'MergeFiles'.

Question 13

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

Accepted Answer

Enable blob soft delete on the storage account.. Azure Data Lake Storage Gen2 supports blob soft delete, which protects against accidental deletion by retaining deleted blobs for a specified retention period. Option A is incorrect because Azure Resource Manager locks prevent the deletion or modification of the storage account itself, not the data within it. Option B is incorrect because Azure Backup is designed for backing up VMs, SQL databases, and other workloads, not for managing soft delete of blobs. Option C is incorrect because blob versioning preserves previous versions of blobs, but it does not prevent deletion of the current version; soft delete is specifically for recovery from accidental deletion.

Answer

Apply an Azure Resource Manager lock.

Answer

Configure Azure Backup for the storage account.

Answer

Enable blob versioning.

Question 14

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

Accepted Answer

Azure Cosmos DB with multi-region writes and eventual consistency. Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Answer

Azure Cosmos DB with single-region writes and strong consistency

Answer

Azure Data Lake Storage Gen2 with hierarchical namespace

Answer

Azure Blob Storage with hot tier and append blobs

Question 15

You are designing a data processing solution for an e-commerce company that uses Azure Synapse Analytics. The solution must process clickstream data from a web application. The data arrives in JSON format through Azure Event Hubs. You need to load the data into a dedicated SQL pool every 5 minutes with minimal latency. The data volume is about 100 MB every 5 minutes. You want to use PolyBase for loading. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to copy data from Event Hubs to Azure Data Lake Storage Gen2 as JSON files, then use a PolyBase activity to load from ADLS Gen2 to the dedicated SQL pool.. Using Azure Data Factory to copy data from Event Hubs to ADLS Gen2 as JSON files, then using PolyBase to load into the dedicated SQL pool, is the recommended approach. PolyBase can efficiently load large volumes from ADLS Gen2, and Data Factory provides a scalable, low-latency pipeline. This approach leverages PolyBase's parallel loading capabilities and is cost-effective for 100 MB every 5 minutes.

Answer

Use Azure Stream Analytics to transform the JSON data and output directly to the dedicated SQL pool.

Answer

Use Azure Databricks to read from Event Hubs, transform the data, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase directly from Event Hubs to dedicated SQL pool by creating an external data source that points to Event Hubs.