DP-203 Practice Test 19 — 15 Questions

Question 1

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

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

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Answer

Row-level security (RLS)

Answer

Dynamic data masking

Answer

Microsoft Defender for Cloud

Question 2

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

Accepted Answer

Derived Column transformation. The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Answer

Pivot transformation

Answer

Aggregate transformation

Answer

Lookup transformation

Question 3

A data engineer needs to store log data from multiple applications in Azure. The data is append-only, heavily compressed, and queried infrequently. Cost minimization is critical. Which storage solution is best?

Accepted Answer

Azure Blob Storage with cool or archive access tier. Azure Blob Storage with cool or archive access tier is the best choice because the data is append-only, heavily compressed, and infrequently queried, making cost minimization the top priority. The cool tier offers low storage costs with higher access charges, while the archive tier provides the lowest storage cost for data that is rarely accessed and can tolerate hours of retrieval latency. This aligns perfectly with the append-only, infrequently queried nature of the log data.

Answer

Azure Table Storage

Answer

Azure Cosmos DB with analytical store

Answer

Azure Data Lake Storage Gen2 with hot tier

Question 4

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

Accepted Answer

Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data. Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Answer

Use Azure Data Lake Storage Gen2 for both transactional and historical data

Answer

Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data

Answer

Use Azure SQL Database with Hyperscale tier for both transactional and historical data

Question 5

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

Accepted Answer

Change the distribution to hash on the column that is most frequently used in joins.. Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling. Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Answer

Keep round-robin distribution but increase the degree of parallelism.

Answer

Change the distribution to hash on multiple columns.

Answer

Rebuild the table as a heap to improve insert performance.

Question 6

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

Accepted Answer

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

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 7

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

Accepted Answer

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

Answer

Session window

Answer

Sliding window

Answer

Tumbling window

Question 8

You need to secure data at rest for an Azure Data Lake Storage Gen2 account that contains sensitive financial data. Which configuration should you enable to ensure that data is encrypted using a customer-managed key stored in Azure Key Vault, and that access to the key is logged?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault and enable Key Vault logging. Azure Storage encryption with customer-managed keys in Key Vault provides control and logging. Option A is wrong because Microsoft-managed keys are the default but do not provide customer control. Option B is wrong because client-side encryption requires managing keys on the client side. Option C is wrong because infrastructure encryption adds a second layer but does not use customer-managed keys.

Answer

Enable Azure Storage encryption with Microsoft-managed keys

Answer

Implement client-side encryption using Azure Key Vault

Answer

Enable infrastructure encryption for double encryption

Question 9

You need to ensure that an Azure Data Factory pipeline retries a failed activity up to three times with a 5-minute delay between retries. How should you configure the activity?

Accepted Answer

Set retry to 3 and retryIntervalInSeconds to 300 in the activity policy. Azure Data Factory activity policies expose two retry-related properties: 'retry' (an integer count) and 'retryIntervalInSeconds' (the fixed delay between attempts). Setting retry to 3 and retryIntervalInSeconds to 300 produces exactly three retries with a 5-minute (300-second) fixed interval, matching the requirement. This is configured on the activity's policy object in the pipeline JSON or via the Author UI.

Answer

Configure the Retry policy on the pipeline activity as 'Exponential' with count 3

Answer

Set the activity timeout to 15 minutes and enable retry

Answer

Set maxRetries to 3 and delay to 5 minutes in the pipeline JSON

Question 10

You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?

Accepted Answer

Store data in columnar format (e.g., Parquet). Option A is correct because storing append-only IoT telemetry in a columnar format such as Parquet enables column pruning and compression, so time-range queries scan only the needed timestamp/value columns and consume far less I/O and storage, reducing cost. Option D is correct because partitioning the data by date (e.g., a partition per day or month) lets the query engine perform partition pruning, skipping entire partitions outside the requested time range, which dramatically improves time-range query performance and lowers scanned data volume. Option B is not appropriate because indexing every column on high-velocity append-only data adds heavy write and storage overhead while providing little benefit for range scans on time-series data. Option C does not belong because row-level security is an access-control feature, not a query-performance or cost optimization. Option E is not relevant because geo-redundant storage improves durability and disaster recovery across regions but does not optimize time-range query performance and typically increases cost.

Answer

Create indexes on all columns

Answer

Enable row-level security

Answer

Enable geo-redundant storage

Question 11

Which TWO are benefits of using Azure Databricks Auto Loader for incremental data ingestion?

Accepted Answer

It can process new files as they arrive in cloud storage.. Option A is correct because Auto Loader is designed to incrementally and efficiently detect and process new files as they land in cloud storage (e.g., Azure Data Lake Storage or Blob Storage) using file notification or directory listing modes, making it ideal for streaming ingestion of arriving data. Option B is correct because Auto Loader manages the ingestion state, including checkpoints and the schema/state tracking, so it can scale to large volumes of files without requiring manual checkpoint management by the user. Option C is not correct because while Auto Loader supports schema inference and schema evolution, evolution typically requires enabling and configuring options such as cloudFiles.schemaEvolutionMode and related settings, so it is not automatic without any configuration. Option D is not correct because Auto Loader is a file-based incremental ingestion mechanism and does not guarantee sub-second latency real-time streaming. Option E is not correct because Auto Loader does not provide built-in record-level deduplication; deduplication must be implemented separately, for example with dropDuplicates or Delta Lake MERGE logic.

Answer

It automatically evolves the schema without any configuration.

Answer

It provides sub-second latency for real-time streaming.

Answer

It provides built-in deduplication of records.

Question 12

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

Accepted Answer

The SQL Server is experiencing resource contention or timeout due to heavy load.. The error 'SqlOperationFailed' on the source side indicates that the SQL Server itself is failing to complete the query or data extraction operation. Under heavy load, the SQL Server may experience resource contention (CPU, memory, I/O) or reach query timeout thresholds, causing the copy activity to fail intermittently. This is consistent with the described scenario of heavy load during business hours.

Answer

The Azure Blob Storage account is throttling requests.

Answer

The authentication method to SQL Server is incorrect.

Answer

The self-hosted integration runtime is not connected to the network.

Question 13

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

Accepted Answer

Notebook activity configured to use a Python kernel. A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Answer

Web activity

Answer

Stored Procedure activity

Answer

HDInsight Hive activity

Question 14

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

Accepted Answer

The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account. The 403 Forbidden error indicates that authentication succeeded but authorization failed. For a managed identity to write data to Azure Data Lake Storage Gen2, it must be assigned the 'Storage Blob Data Contributor' RBAC role on the storage account. This role grants read, write, and delete permissions for blobs and directories. Without this role assignment, the managed identity cannot write data, resulting in a 403 error. Even if the storage account name is correct (A), the firewall is configured (B), or a private endpoint exists (C), the missing RBAC role assignment will cause the failure.

Answer

The storage account name is correct

Answer

The storage account firewall is configured to allow Azure services

Answer

A private endpoint is configured between Databricks and the storage account

Question 15

You are developing a data processing pipeline for a gaming company that uses Azure Databricks. The pipeline processes game event data from Azure Event Hubs. You need to detect cheating patterns by analyzing events in real time. The solution must be able to handle high throughput and low latency. The output should be written to Azure Cosmos DB for real-time dashboards. Which approach should you use?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use Spark SQL and machine learning to detect cheating patterns, and write to Cosmos DB using the Azure Cosmos DB Spark connector.. Azure Databricks Structured Streaming is designed for scalable, low-latency stream processing and integrates with Event Hubs and Cosmos DB via connectors. It allows using Spark SQL and machine learning libraries to detect cheating patterns in real time. This approach handles high throughput and provides the necessary analytics capabilities. The other options either lack the required real-time processing or the advanced analytics.

Answer

Use Azure Functions with Event Hubs trigger to process each event and write to Cosmos DB.

Answer

Use Azure Data Factory with continuous copy to load data into Cosmos DB and then use Azure Synapse Analytics to detect patterns.

Answer

Use Azure Stream Analytics to query the stream for cheating patterns and output to Cosmos DB.