DP-203 Practice Test 12 — 15 Questions

Question 1

You are reviewing an Azure Data Factory JSON definition for a linked service. The linked service uses a service principal to connect to Azure Data Lake Storage Gen1. What is a security concern with this configuration?

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

Accepted Answer

The service principal key is stored as a SecureString but is visible in the JSON definition.. The service principal key is stored as a SecureString, but in the JSON definition it is visible as plain text, which is a security concern. Option A is incorrect because specifying the subscription ID and resource group is necessary for resource management and not a security issue. Option B is incorrect because using a service principal is a valid and secure authentication method when the key is properly stored. Option C is incorrect because the choice between Data Lake Storage Gen1 and Gen2 is a performance or feature decision, not a security concern.

Answer

The subscription ID and resource group are specified.

Answer

The linked service uses a service principal instead of a managed identity.

Answer

Using Azure Data Lake Storage Gen1 instead of Gen2.

Question 2

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to ensure that all data at rest in the SQL pool is encrypted using a customer-managed key stored in Azure Key Vault. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault provides the required encryption for data at rest in Azure Synapse Analytics dedicated SQL pool. Option A is wrong because Always Encrypted protects data in transit and at rest in application logic, not at rest in the database. Option B is wrong because Dynamic Data Masking does not encrypt data; it obfuscates it for users. Option C is wrong because Azure Storage Service Encryption is for Azure Storage accounts, not for SQL pools.

Answer

Implement Always Encrypted with column encryption keys stored in Azure Key Vault.

Answer

Configure Dynamic Data Masking to obfuscate sensitive data.

Answer

Enable Azure Storage Service Encryption with a customer-managed key.

Question 3

Your team has deployed an Azure Stream Analytics job that reads from an Event Hubs input and writes to Azure Synapse Analytics. The job is falling behind, causing a growing backlog in Event Hubs. You have already scaled the Stream Analytics job to maximum streaming units. What should you do to improve throughput?

Accepted Answer

Partition the input Event Hubs and the output Synapse table, and adjust the Stream Analytics query to use PARTITION BY. Partitioning the input Event Hubs and the output Synapse table, and using PARTITION BY in the query, increases parallelism and allows the Stream Analytics job to process more data simultaneously. Option A is incorrect because the job is already at maximum streaming units. Option B is incorrect because a late arrival window handles out-of-order events, not throughput issues. Option C is incorrect because while increasing Event Hubs throughput units could help if Event Hubs is the bottleneck, the most likely bottleneck is the output sink (Azure Synapse Analytics), and partitioning the output is a more direct solution.

Answer

Increase the streaming units further

Answer

Configure a late arrival window to drop late events

Answer

Increase the throughput units of the Event Hubs namespace

Question 4

A company is migrating an on-premises Hadoop cluster to Azure. The cluster uses Hive tables stored as Parquet files on HDFS. They want to minimize changes to existing Hive queries and continue using HiveQL. Which Azure storage solution should they choose?

Accepted Answer

Azure HDInsight with Hive and Azure Data Lake Storage Gen2. Azure HDInsight with Hive and Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a fully managed Hadoop service that supports HiveQL with minimal changes. ADLS Gen2 offers a hierarchical namespace and is optimized for Hadoop workloads, allowing the existing Parquet files on HDFS to be directly mounted and queried without data movement or schema changes.

Answer

Azure SQL Database with PolyBase

Answer

Azure Databricks with Delta Lake

Answer

Azure Synapse Analytics with external tables

Question 5

A data engineer needs to store semi-structured JSON log files from a web application. Each log entry is about 1 KB. The logs are rarely queried (once a month) and must be retained for 7 years for compliance. The solution must minimize storage cost. Which storage option should be used?

Accepted Answer

Store the logs in Azure Blob Storage with cool access tier.. Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Answer

Store the logs in Azure SQL Database as a table.

Answer

Store the logs in Azure Files share.

Answer

Store the logs in Azure Cosmos DB with a JSON container.

Question 6

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.. The current query uses a static date. For incremental loads, a watermark column like OrderDate with a last run timestamp is needed, typically using a lookup activity or variable.

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 7

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Analytics pointing to Parquet files in ADLS Gen2. The query 'SELECT * FROM Sales' returns 0 rows, but the files exist. What is the most likely cause?

Accepted Answer

The files are stored in a different container or path.. The LOCATION in the external table is 'sales/2024/03/' which is a subfolder. The files must be directly in that folder. If the files are in a different folder structure (e.g., with partition folders like year=2024/month=03), the external table will not find them. Also, check that the credential has access.

Answer

The credential does not have read permission on the storage account.

Answer

The external table is not refreshed after creation.

Answer

The Parquet files are compressed with Gzip instead of Snappy.

Question 8

You are a data engineer at a financial services company. The company uses Azure Synapse Analytics with a dedicated SQL pool for its data warehouse. The current table 'FactTransactions' is 2 TB and uses round-robin distribution. Query performance is poor for queries that frequently filter on 'CustomerID' and join with a 'DimCustomer' table (10 GB, replicated). You need to redesign the table to improve query performance while minimizing data movement during queries. The solution must also support incremental data loading with minimal overhead. You cannot change the storage size limit or add more DWU. What should you do?

Accepted Answer

Recreate the table using hash distribution on CustomerID and use CTAS for incremental loads.. Hash distribution on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution, which eliminates data movement during joins with the replicated DimCustomer table. Using CTAS (CREATE TABLE AS SELECT) for incremental loads allows you to efficiently rebuild the table with minimal overhead by loading only new data into a staging table, then swapping partitions or using CTAS to replace the target table without blocking reads.

Answer

Replicate the FactTransactions table to all distributions.

Answer

Keep round-robin distribution but add indexes on CustomerID and TransactionDate.

Answer

Change distribution to hash on TransactionDate and partition by month.

Question 9

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that reduces storage costs for historical data that is rarely accessed but must be available for querying within minutes. The solution should not require application changes. What should you do?

Accepted Answer

Create external tables pointing to data stored in Azure Data Lake Storage Gen2 with appropriate tiering. Creating external tables in Azure Synapse Analytics dedicated SQL pool that point to data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) allows you to query historical data directly from low-cost storage tiers (e.g., cool or archive) without moving it into the pool. This reduces storage costs for rarely accessed data while keeping it queryable within minutes, and requires no application changes since the external tables are accessed via standard T-SQL queries.

Answer

Use Azure Blob Storage with PolyBase and configure lifecycle management to archive data

Answer

Drop older partitions and reload data when needed

Answer

Change the distribution to round-robin for the fact table

Question 10

You deploy the Azure Security Center automation shown in the exhibit. What is the purpose of this automation?

Accepted Answer

It sends high-severity security alerts to an Event Hub for further processing.. Azure Security Center automation can be configured to send high-severity security alerts to an Event Hub for further processing, enabling integration with SIEM systems or custom workflows. Option A is incorrect because Azure Monitor logging is not configured by this automation; it's about forwarding alerts. Option B is incorrect because Azure Policy is used for compliance enforcement, not for remediating alerts via automation. Option D is incorrect because creating incidents in Azure Sentinel is a separate action, not directly triggered by this automation; it forwards alerts to Event Hub.

Answer

It configures Azure Monitor to log high-severity alerts.

Answer

It applies an Azure Policy to remediate high-severity alerts.

Answer

It creates incidents in Azure Sentinel for high-severity alerts.

Question 11

You are a data engineer for a financial services company. You manage an Azure Data Lake Storage Gen2 account that stores real-time stock trade data ingested from Azure Event Hubs via Azure Stream Analytics. The data is partitioned by date and symbol. Each day, a downstream Azure Databricks job runs an ETL process to aggregate trades into 5-minute bars and writes the results to a separate container. The Databricks job runs on a cluster with 10 worker nodes (Standard_DS3_v2) using Auto-Scaling enabled (2-10 workers). Recently, the job has been taking longer than expected, and you observe that the cluster is often at 10 workers but still the job duration increased by 30%. The storage account shows high transaction costs. You suspect the issue is related to how data is read. What should you do to optimize the job's performance and reduce costs?

Accepted Answer

Modify the Stream Analytics job to output larger files (e.g., set the minimum file size to 100 MB) and use coalesce in Databricks to reduce the number of output partitions.. The performance issue stems from reading many small files (small file problem) in Azure Data Lake Storage Gen2, which increases transaction costs and slows down Spark jobs. By configuring Stream Analytics to output larger files (e.g., minimum 100 MB) and using coalesce in Databricks to reduce output partitions, you minimize the number of files read/written, reducing overhead and transaction costs. This directly addresses the root cause—high transaction costs and cluster saturation at 10 workers—without unnecessary scaling or tier changes.

Answer

Convert the data to Avro format to reduce file size.

Answer

Increase the maximum number of workers to 20 and use a larger instance type.

Answer

Move the data to Azure Blob Storage Premium tier to reduce latency.

Question 12

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. You notice frequent failures due to transient network errors. Which TWO actions should you take to improve reliability?

Accepted Answer

Enable fault tolerance in the copy activity to skip incompatible rows.. Options C and D are correct. Enabling fault tolerance allows the copy activity to skip incompatible rows and continue, while configuring a retry policy automatically retries the activity on failure due to transient errors. Option A is incorrect because deploying a self-hosted IR in Azure does not address transient network errors; it is used for connectivity to on-prem data stores. Option B is incorrect because staged copy is for copying large datasets efficiently, not for handling transient errors. Option E is incorrect because increasing parallelism improves throughput but does not improve reliability against transient failures.

Answer

Deploy a self-hosted integration runtime on a VM in Azure.

Answer

Use staged copy with Azure Data Lake as intermediate storage.

Answer

Increase the degree of copy parallelism.

Question 13

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

Accepted Answer

Hash distribution on a column that is frequently used in joins and aggregations.. Hash distribution on a column frequently used in joins and aggregations is the best choice for a fact table with billions of rows in a dedicated SQL pool. It distributes rows across distributions based on a hash of the distribution column, ensuring that rows with the same key value are co-located on the same distribution. This minimizes data movement during joins and aggregations, as the data required for these operations is already local to each distribution, significantly improving query performance.

Answer

Round-robin distribution.

Answer

Partitioned table with a partition key.

Answer

Replicated distribution.

Question 14

Which TWO strategies can be used to optimize storage costs for historical data in Azure Data Lake Storage Gen2?

Accepted Answer

Implement lifecycle management policies to move data to archive tier. Azure Blob Storage lifecycle management policies allow you to automatically transition data from hot to cool to archive tiers based on age or last modification time. Moving historical data to the archive tier significantly reduces storage costs, as archive is the lowest-cost storage tier, though it incurs higher retrieval latency and costs.

Answer

Enable soft delete to recover data

Answer

Use geo-redundant storage (GRS) for durability

Answer

Encrypt data with Azure Storage Service Encryption

Question 15

You are a data engineer at a manufacturing company. You need to process sensor data from IoT devices that arrive in real time. The data is sent to Azure Event Hubs. You need to aggregate the data over 5-minute windows and store the results in Azure Data Lake Storage Gen2 in Parquet format. The solution should minimize cost and use serverless components. Which solution should you use?

Accepted Answer

Use Azure Stream Analytics to create a query with a tumbling window of 5 minutes, and output the results to Azure Data Lake Storage Gen2 in Parquet format.. Azure Stream Analytics is a serverless, cost-effective solution for real-time stream processing with windowed aggregations. It can output directly to ADLS Gen2 in Parquet. Option B is wrong because Azure Databricks with Structured Streaming requires a running cluster, which is not serverless and incurs cost. Option C is wrong because Azure Data Factory is not designed for real-time streaming. Option D is wrong because Azure Functions would require custom code and may not handle large throughput efficiently.

Answer

Use Azure Databricks with Structured Streaming to read from Event Hubs, aggregate with a sliding window, and write to ADLS Gen2 in Parquet.

Answer

Use Azure Data Factory with a tumbling window trigger to run a pipeline every 5 minutes that copies data from Event Hubs to ADLS Gen2.

Answer

Use Azure Functions with an Event Hubs trigger to aggregate data in memory and write to ADLS Gen2.