DP-203 Practice Test 24 — 15 Questions

Question 1

You are designing a data processing solution in Azure Synapse Analytics. The solution must ensure that sensitive columns containing personally identifiable information (PII) are masked at query time for users without explicit permissions. Which Azure Synapse Analytics feature should you use?

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

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) in Azure Synapse Analytics masks sensitive column data at query time for users without explicit UNMASK permissions, returning masked values (e.g., XXXX or 0) instead of the real data. It is applied via a MASKED WITH clause on the column definition and does not alter the stored data, making it the correct choice for query-time PII obfuscation.

Answer

Row-Level Security

Answer

Transparent Data Encryption

Answer

Always Encrypted

Question 2

Which TWO Azure features can be used to encrypt data at rest in Azure Blob Storage? (Choose two.)

Accepted Answer

Customer-managed keys in Azure Key Vault. Option C (Customer-managed keys in Azure Key Vault) is correct because Azure Blob Storage supports server-side encryption with customer-managed keys (CMK), where the key encryption key is stored in Azure Key Vault and used to wrap the account's data encryption key, providing control over the encryption of data at rest. Option D (Storage Service Encryption, SSE) is correct because SSE is the built-in Azure Storage feature that automatically encrypts blob data at rest using AES-256 before it is persisted to disk, and it is enabled by default for all storage accounts. Option A (Azure Disk Encryption) is not correct because it encrypts OS and data disks attached to Azure VMs (using BitLocker/DM-Crypt), not blobs in a storage account. Option B (Azure Information Protection) is not correct because it classifies and protects files at the application/document level rather than providing storage-account-level encryption at rest for blobs. Option E (Transport Layer Security) is not correct because TLS protects data in transit over the network, not data at rest.

Answer

Azure Disk Encryption

Answer

Azure Information Protection

Answer

Transport Layer Security (TLS)

Question 3

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

Accepted Answer

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

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2 with files in JSON format

Answer

Azure SQL Database with JSON columns

Question 4

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database. Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Answer

Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database

Answer

Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly

Answer

Use Azure Functions to process events and write to SQL Database

Question 5

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

Accepted Answer

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

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure Service Bus

Question 6

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

Accepted Answer

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

Answer

No distribution (single distribution)

Answer

Replicated distribution

Answer

Round-robin distribution

Question 7

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

Accepted Answer

Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.. Enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Answer

Increase the provisioned RU/s on the container to handle both transactional and analytical queries.

Answer

Change the partition key to /date to optimize for analytical queries.

Answer

Create a materialized view using the change feed and store aggregated data in a separate container.

Question 8

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

Accepted Answer

Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool. Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Answer

Use Azure Functions to process each file and write to Synapse via REST API

Answer

Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation

Answer

Use Azure Databricks with Python notebooks to process the files and write to Synapse

Question 9

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

Accepted Answer

Increase the executor memory setting in the Spark configuration.. An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Answer

Enable checkpointing to truncate the lineage.

Answer

Decrease the number of partitions to reduce overhead.

Answer

Use the cache() action on intermediate DataFrames.

Question 10

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?

Accepted Answer

Use a lookup activity to retrieve the last watermark value, then copy only new records with a filter.. 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.

Answer

Use a tumbling window trigger with a data flow that processes all data each time.

Answer

Use a mapping data flow with a full load and then use Azure Databricks to deduplicate.

Answer

Copy the entire table every time and use Azure Synapse serverless SQL to filter duplicates.

Question 11

You are designing a data processing solution in Azure Synapse Analytics. The solution must ensure that sensitive columns containing personally identifiable information (PII) are masked at query time for users without explicit permissions. Which Azure Synapse Analytics feature should you use?

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

Accepted Answer

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

Answer

RDD API

Answer

Spark Streaming (DStreams)

Answer

DataFrame API with batch processing

Question 12

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

Accepted Answer

Incremental copy (watermark) pattern using a Lookup activity and a Copy activity. The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Answer

Alter Row transformation

Answer

Schedule trigger

Answer

Lookup activity alone

Question 13

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Accepted Answer

Use ROUND_ROBIN distribution for the staging table. Option B is correct because a ROUND_ROBIN distributed staging table spreads incoming rows evenly across all distributions without requiring a distribution key, which maximizes parallel ingestion throughput and avoids data movement during the load before the data is redistributed into the final table. Option E is correct because CTAS performs a parallel, minimally logged bulk operation that creates a new table with the desired distribution and indexing in one step, and combining it with partition switching lets you swap the fully loaded table into the target quickly and efficiently. Option A is not appropriate because nonclustered indexes on every column slow down bulk loads and consume extra storage; dedicated SQL pools rely primarily on clustered columnstore indexes. Option C is wrong because the optimal row group size for columnstore compression is around 1,048,576 rows (1 million), not 100,000, which yields smaller, less efficient row groups. Option D is incorrect because change tracking is used for incremental data synchronization scenarios and does not improve bulk load performance.

Answer

Create nonclustered indexes on all columns of the target table

Answer

Set the row group size to 100,000 rows for optimal compression

Answer

Enable change tracking on the target table

Question 14

You are designing data security for Azure Data Lake Storage Gen2. The requirement is to prevent data from being accessed by anyone outside the corporate network. Which feature should you enable?

Accepted Answer

Use Azure Private Endpoint or service endpoint with a VNet.. Azure Private Endpoint or service endpoint with a VNet ensures that all traffic to the storage account stays within the corporate network and never traverses the public internet. Private Endpoint assigns a private IP from the VNet to the storage account, effectively isolating it from public access. This meets the requirement to prevent access from outside the corporate network by enforcing network-level isolation.

Answer

Assign RBAC roles to deny access to all except corporate users.

Answer

Configure IP firewall rules to allow only corporate IP ranges.

Answer

Enable encryption at rest using customer-managed keys.

Question 15

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

Accepted Answer

Use private endpoints for the storage account. Option A is correct because private endpoints assign the storage account a private IP address inside your virtual network via Azure Private Link, so traffic to the account never traverses the public internet and cannot be reached from unauthorized external IP addresses. Option D is correct because configuring the storage account firewall (network rules) to allow only specific virtual networks restricts access to those trusted VNets and blocks all other public IPs, directly preventing exfiltration to unauthorized external addresses. Option B is incorrect because Azure Firewall is a network security service deployed in a virtual network or hub, not a feature you enable on a storage account. Option C is incorrect because SAS tokens grant scoped access to data but do not by themselves block outbound exfiltration to arbitrary external IP addresses. Option E is incorrect because geo-redundant storage only replicates data across regions for durability and availability, and has no role in controlling network access or preventing exfiltration.

Answer

Enable Azure Firewall on the storage account

Answer

Use shared access signatures (SAS) with limited permissions

Answer

Enable geo-redundant storage (GRS)