DP-203 Practice Test 35 — 15 Questions

Question 1

A financial services company needs to store transaction data for audit purposes. The data must be immutable and cannot be modified or deleted for 7 years. Which Azure storage feature should be used?

Accepted Answer

Azure Blob Storage immutability policy (time-based retention).. Azure Blob Storage immutability policy with time-based retention ensures that blobs cannot be modified or deleted for a specified retention period (e.g., 7 years). This meets the audit requirement for immutable storage by locking the data at the storage level, preventing any writes or deletes until the retention interval expires. The policy is enforced at the container level and applies to all blobs within, making it the correct choice for regulatory compliance.

Answer

Azure Blob Storage soft delete.

Answer

Azure Blob Storage versioning.

Answer

Azure Files share snapshots.

Question 2

You are a data engineer for a global e-commerce company. You need to design a data processing solution using Azure Databricks that processes real-time clickstream data from Azure Event Hubs. The solution must join the streaming data with a slowly changing dimension (SCD) table that stores product details. The SCD table is stored in Azure Data Lake Storage Gen2 as Delta format and is updated every few hours. The joined results must be written to a Delta table for near-real-time dashboards. The key requirement is to ensure that the join always uses the latest version of the SCD data without reprocessing the entire stream. The solution must minimize latency and cost. Which approach should you recommend?

Accepted Answer

Use Spark Structured Streaming with a static DataFrame for the SCD table and refresh it periodically using a trigger that reads the latest snapshot.. It uses a static DataFrame for the SCD table and refreshes it periodically using a trigger (e.g., a time-based or file-based trigger). This approach ensures that the join always uses the latest version of the SCD data without reprocessing the entire stream, as the static DataFrame is re-read only when the SCD is updated. It minimizes latency and cost by avoiding the overhead of reading the SCD in every micro-batch (as in Option A) or using a streaming join (as in Option D), which is not suitable for batch-updated SCD tables.

Answer

Use Spark Structured Streaming with foreachBatch and read the SCD table inside the foreachBatch function.

Answer

Use Spark Structured Streaming with a batch read of the SCD table in each micro-batch using spark.read.

Answer

Use Spark Structured Streaming with a streaming join on the SCD table by converting it to a stream using readStream.

Question 3

You are a data engineer for a healthcare company. You have a production Azure Synapse Analytics dedicated SQL pool (DW500c) that hosts patient data. The pool is used for both ETL and reporting. You need to ensure that reporting queries always get resources even during heavy ETL loads. You also need to monitor query performance and set up alerts when certain queries exceed a threshold. You have configured workload management using workload groups and classifiers. However, reporting queries are still waiting for resources when ETL is running. You check the sys.dm_pdw_exec_requests DMV and see that ETL queries are using the largest resource class. You need to modify the configuration to guarantee resources for reporting. What should you do?

Accepted Answer

Create a new workload group for reporting with min_percentage_resource set to 30%. Creating a separate workload group for reporting with a minimum percentage of resources (min_percentage_resource) guarantees a baseline amount of resources for reporting queries, isolating them from ETL even when ETL is using a large resource class. This is the correct solution. Option B (importance) can help order queries within the same group but does not guarantee resource availability if the group has no minimum. Option C (increasing DWU) adds more overall resources but does not isolate; reporting may still be starved if ETL uses them. Option D (changing classifier) does not guarantee resources because both queries would compete in the same group.

Answer

Set importance to HIGH for the reporting workload group

Answer

Increase the DWU setting to DW1000c

Answer

Change the classifier for reporting queries to use the same workload group as ETL but with a different resource class

Question 4

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a folder structure that separates data by sensitivity level. Which access control method should you use?

Accepted Answer

Use Azure RBAC at the resource group level and ACLs on directories. Azure Data Lake Storage Gen2 supports both Azure RBAC at the resource group level for coarse-grained control and POSIX-like ACLs on directories for fine-grained, sensitivity-based access. This combination allows you to assign read/write/execute permissions per directory, enabling a folder structure that separates data by sensitivity level without compromising security.

Answer

Use a storage account firewall and virtual network service endpoints

Answer

Use storage account keys for all access

Answer

Use shared access signatures (SAS) for each folder

Question 5

A company is designing a data storage solution for streaming IoT telemetry data. The data is JSON-formatted, arrives at up to 10,000 events per second, and must be stored for at least 30 days for real-time dashboards and ad-hoc querying. The solution must minimize operational overhead and query latency. Which Azure service should they use?

Accepted Answer

Azure Data Explorer (ADX). Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and time-series data, ingesting up to 10,000 events per second with low latency. Its columnar storage and indexing enable sub-second queries on JSON data for real-time dashboards, while the 30-day retention is natively configurable via caching and soft-delete policies. This minimizes operational overhead by eliminating the need for manual partitioning or index tuning.

Answer

Azure Blob Storage with Azure Data Lake Storage Gen2

Answer

Azure Cosmos DB with analytical store

Answer

Azure SQL Database with elastic query

Question 6

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

Accepted Answer

Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.. Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Answer

Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.

Answer

Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.

Answer

Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.

Question 7

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected. After reviewing the query plans, you see that some queries are spilling to tempdb. What should you do to reduce tempdb spills?

Accepted Answer

Increase the resource class for the user executing the queries.. Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Answer

Redistribute the tables using hash distribution.

Answer

Rebuild all columnstore indexes.

Answer

Add partitioning to the tables.

Question 8

You are designing a change data capture (CDC) pipeline to ingest incremental changes from an on-premises SQL Server database into Azure Data Lake Storage Gen2. The pipeline must run every 5 minutes and handle high-volume DML changes. Which Azure service should you use to capture the changes with low latency?

Accepted Answer

Azure Data Factory with a change data capture (CDC) source in the mapping data flow.. Azure Data Factory's mapping data flow includes a native CDC source that can connect to SQL Server and capture incremental DML changes (inserts, updates, deletes) with low latency. This approach uses change tracking or change data capture features in SQL Server to identify changes, and the pipeline can run every 5 minutes to meet the high-volume requirement without custom coding.

Answer

Azure Data Share to share the SQL Server data and capture changes.

Answer

Azure Synapse Pipelines with a copy activity that uses a query to capture changes.

Answer

Azure Databricks with Auto Loader and Delta Live Tables to capture changes.

Question 9

You are monitoring an Azure Data Lake Storage Gen2 account that stores streaming data from IoT devices. You notice that query performance on the data in Parquet format is degrading over time. You need to improve query performance for both current and future data. Which TWO actions should you take?

Accepted Answer

Partition the data by a column commonly used in filter conditions.. Partitioning the data by a column commonly used in filter conditions (e.g., date, device ID) enables predicate pushdown in query engines like Azure Synapse or Spark, allowing them to skip irrelevant partitions and scan only the necessary files. This directly addresses the performance degradation by reducing the amount of data read during queries, and it benefits both current and future data when applied consistently.

Answer

Move frequently accessed data to Azure SQL Database.

Answer

Enable soft delete on the storage account to optimize read performance.

Answer

Migrate the data to Azure NetApp Files for lower latency.

Question 10

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.. It uses Azure Data Factory to stage the JSON data from Event Hubs into Azure Data Lake Storage Gen2 (ADLS Gen2) as JSON files, and then uses a PolyBase activity to load the data from ADLS Gen2 into the dedicated SQL pool. This approach meets the requirement of using PolyBase for loading, handles the 100 MB every 5 minutes with minimal latency, and leverages the efficient bulk loading capability of PolyBase. Option A is incorrect because Azure Stream Analytics does not support output directly to a dedicated SQL pool; it can output to Azure SQL Database or other sinks, but not dedicated SQL pool, and it does not use PolyBase. Option C is incorrect because using Azure Databricks with JDBC does not utilize PolyBase and would require a continuously running cluster, increasing cost and complexity. Option D is incorrect because PolyBase cannot create an external data source pointing directly to Event Hubs; PolyBase external data sources only support Azure Blob Storage, ADLS Gen2, and Hadoop, not Event Hubs.

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.

Question 11

You are optimizing a pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure Synapse Analytics. The pipeline uses a copy activity with PolyBase. The data is partitioned by date in Blob Storage. You notice that the load is slow. What is the most likely cause?

Accepted Answer

The source files are too many and too small (e.g., thousands of 1 MB files). PolyBase in Azure Synapse Analytics performs best when reading large, contiguous files. When the source contains thousands of small files (e.g., 1 MB each), PolyBase must initiate a separate read operation for each file, causing excessive overhead from file open/close operations and metadata requests. This dramatically reduces throughput compared to reading fewer, larger files.

Answer

The source files are stored in Azure Blob Storage instead of Data Lake Storage Gen2

Answer

The source files are in CSV format instead of Parquet

Answer

The sink table has a clustered columnstore index

Question 12

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Accepted Answer

Amazon S3. Amazon S3 is a supported source for Azure Data Factory Copy activity because ADF includes a built-in Amazon S3 connector that allows data ingestion from S3 into Azure. This connector uses the S3 REST API to read objects, supporting both public and private buckets via access keys or IAM roles.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 13

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

Accepted Answer

Data is unevenly distributed across distributions, causing some distributions to be overloaded.. D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Answer

Statistics on the table are outdated.

Answer

The table is not properly partitioned.

Answer

Data compression is not working efficiently.

Question 14

A company is running a Spark job on Azure Databricks that processes 500 GB of data daily. The job frequently fails with 'OutOfMemoryError' during shuffles. The cluster uses 10 workers of type Standard_DS3_v2 (14 GB memory each). Which configuration change should you make to improve stability without over-provisioning?

Accepted Answer

Set spark.sql.shuffle.partitions to a higher value, e.g., 500.. The 'OutOfMemoryError' during shuffles indicates that individual partitions are too large for the executor memory. Increasing `spark.sql.shuffle.partitions` to 500 reduces the amount of data per partition, lowering memory pressure during shuffle operations. This directly addresses the error without adding more hardware.

Answer

Increase the driver memory to 28 GB.

Answer

Increase the number of workers to 20.

Answer

Reduce spark.sql.shuffle.partitions to 100.

Question 15

Which TWO techniques can you use to handle schema drift in Azure Data Factory mapping data flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation. Enabling 'Allow schema drift' in the source transformation tells Azure Data Factory (ADF) mapping data flows to dynamically accept incoming columns that are not defined in the schema at design time. This is the primary built-in mechanism for handling schema drift without manual intervention, as it automatically propagates new columns through the data flow.

Answer

Use derived column transformation to handle each new column manually

Answer

Use assertion rules to reject rows with unknown columns

Answer

Use a fixed schema mapping to ignore unknown columns