DP-203 Practice Test 28 — 15 Questions

Question 1

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?

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

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 2

You are tuning a dedicated SQL pool in Azure Synapse Analytics. A query that joins two large tables (fact_sales and dim_product) is slow. The fact_sales table is hash-distributed on product_id, and dim_product is replicated. You notice that the query plan shows a shuffle move. What is the most likely cause?

Accepted Answer

The join condition does not include the distribution key for fact_sales.. When the join condition does not include the distribution key (product_id) of the hash-distributed fact_sales table, the SQL engine cannot perform a collocated join. Instead, it must shuffle data across distributions to satisfy the join, which introduces expensive data movement. The shuffle move in the query plan directly indicates this redistribution.

Answer

The fact_sales table uses clustered columnstore index.

Answer

The dim_product table is replicated, causing a broadcast join.

Answer

Statistics are out of date on both tables.

Question 3

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

Accepted Answer

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0. A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Answer

Add a filter in the copy activity to exclude rows with NULL Amount

Answer

Modify the sink table to have a default value for the Amount column

Answer

Change the sink table column to allow NULLs

Question 4

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Question 5

You are tasked with designing a data storage solution for a social media analytics company. They need to store user profile data (JSON) and social media posts (text and images). The data is used for machine learning models that require fast random access to individual user profiles and the ability to run analytical queries over posts. The solution must provide low-latency reads for user profiles (milliseconds) and support for large-scale analytics on posts. Which combination of Azure data services should you recommend?

Accepted Answer

Azure Cosmos DB for user profiles and Azure Data Lake Storage Gen2 for posts. Azure Cosmos DB provides low-latency (millisecond) reads for user profiles via its indexing and partitioning capabilities, ideal for fast random access. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with Blob Storage, enabling large-scale analytical queries on posts using tools like Azure Synapse Analytics or Spark, while efficiently storing text and images.

Answer

Azure Cosmos DB for both user profiles and posts

Answer

Azure SQL Database for both user profiles and posts

Answer

Azure Table Storage for user profiles and Azure Blob Storage for posts

Question 6

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

Accepted Answer

Store processed file names in a Delta table and compare with source folder listing. Storing processed file names in a Delta table allows the pipeline to track which files have already been ingested, supporting incremental processing and handling late-arriving data up to 2 hours. By comparing the current source folder listing against the Delta table, the pipeline can identify only new or late-arriving files, avoiding reprocessing and ensuring exactly-once semantics. This approach integrates seamlessly with Azure Databricks and Delta Lake's ACID transactions, providing reliable state management for batch pipelines.

Answer

Use Blob Storage event triggers to invoke Azure Functions

Answer

Use Azure Synapse Pipelines with a schedule and full load each time

Answer

Use Azure Data Factory with watermark columns in the source

Question 7

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

Accepted Answer

Use the Azure Databricks dbutils.fs.mv() to move the file.. `dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Answer

Use Azure Data Factory with a Copy activity to move the file, then delete the source.

Answer

Change the file's metadata to update its directory path.

Answer

Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.

Question 8

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. Option C is correct because Azure Storage lifecycle management policies can automatically transition blobs from hot/cool tiers to the archive tier after a defined age, and the archive tier has the lowest storage cost per GB for rarely accessed historical data. Option D is correct because storing historical data in a compressed columnar format such as Parquet reduces the total bytes stored and scanned, and columnar compression typically achieves much higher compression ratios than row-based formats, directly lowering storage and query costs in ADLS Gen2. Option A is not a cost-optimization strategy; soft delete retains deleted data and can actually increase storage charges during the retention period. Option B is not cost-optimizing either, since GRS replicates data to a secondary region and costs more than LRS. Option E is also not a cost strategy; Azure Storage Service Encryption is enabled by default and does not reduce storage cost.

Answer

Enable soft delete to recover data

Answer

Use geo-redundant storage (GRS) for durability

Answer

Encrypt data with Azure Storage Service Encryption

Question 9

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Accepted Answer

Use coalesce() to reduce the number of partitions without a shuffle.. Option A is correct because coalesce() reduces the number of partitions by merging existing partitions without performing a full shuffle, which directly decreases the number of output files written and is efficient when reducing partitions. Option E is correct because repartition() with a smaller number of partitions also reduces the partition count, and although it triggers a shuffle, it results in fewer output files being written. Option B is incorrect because caching only stores the DataFrame in memory or disk to speed up repeated access; it does not change the number of partitions or output files. Option C is incorrect because bucketing organizes data into buckets for join or query optimization and does not reduce the number of files written by a DataFrame write operation. Option D is incorrect because increasing partitions with repartition() using a larger number would create more partitions and therefore more output files, worsening the small-file problem.

Answer

Enable caching on the DataFrame before writing.

Answer

Apply bucketing on a column to group data.

Answer

Increase the number of partitions using repartition() with a larger number.

Question 10

You need to monitor the performance of an Azure Stream Analytics job that processes real-time IoT data. Which metric indicates the number of events that are being dropped or delayed due to insufficient processing capacity?

Accepted Answer

Backlogged input events.. Backlogged input events (C) measures the number of events that are queued awaiting processing, indicating that the job is unable to keep up with the input rate. High backlog suggests insufficient processing capacity, leading to dropped or delayed events. Watermark delay (A) measures the time lag in processing, but does not directly count events dropped. Input events (D) is the total received, not dropped/delayed. Output events (B) is the total sent.

Answer

Watermark delay.

Answer

Output events.

Answer

Input events.

Question 11

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 12

A media company ingests high-definition video files into Azure Data Lake Storage Gen2. The files are uploaded once and then read by multiple analytics jobs for 48 hours, after which they are deleted. The company wants to optimize read performance and reduce latency for the analytics jobs. Which storage configuration should you recommend?

Accepted Answer

Enable hierarchical namespace and store the files as block blobs in a premium block blob storage account.. Premium block blob storage accounts provide low latency and high throughput, which are critical for analytics jobs reading large video files. Enabling hierarchical namespace (Data Lake Storage Gen2) adds a directory structure that improves query efficiency. Block blobs are the correct type for large files. This combination optimizes read performance and reduces latency for the 48-hour analytics window.

Answer

Store the files as page blobs in a premium page blob storage account.

Answer

Store the video files as append blobs in a general-purpose v2 storage account.

Answer

Store the files in a general-purpose v1 storage account with the Hot access tier.

Question 13

You are a data engineer at a large retail company. Your team uses an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to implement row-level security (RLS) so that sales representatives can see only data for their own region. You must ensure that the security predicate is evaluated at query time and that users cannot bypass it by using different tools. What should you do?

Accepted Answer

Create a database role, add users to it, and use a security policy with an inline table-valued function that filters rows based on the user's region.. Row-level security in Azure Synapse Analytics dedicated SQL pool is implemented by creating an inline table-valued function that defines the filter predicate, then creating a security policy that binds that function to the table. Users are granted access via database roles. The predicate is applied transparently to all queries, ensuring that sales representatives can only see rows for their region, regardless of the client tool used. This provides centralized, consistent enforcement.

Answer

Create a view that filters rows based on the CURRENT_USER function, and grant users SELECT permission only on the view.

Answer

Use dynamic data masking on the region column with a masking rule that shows only the user's region and masks others.

Answer

Implement column-level encryption on the region column and provide each sales representative with the encryption key for their region.

Question 14

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime. You notice that the copy activity sometimes takes much longer than expected, and you suspect network bottlenecks. You need to optimize the copy performance by adjusting the degree of parallelism. Which setting should you modify?

Accepted Answer

The 'parallelCopies' property in the copy activity.. The 'parallelCopies' property in a copy activity determines how many parallel threads are used to read from the source and write to the sink. For network-bound transfers, increasing this value can improve throughput by utilizing more concurrent connections. However, it should be tuned based on the capabilities of the source and sink to avoid overwhelming them.

Answer

The 'maxConcurrentConnections' property in the linked service.

Answer

The 'dataIntegrationUnits' property in the copy activity.

Answer

The 'throughput' setting in the self-hosted integration runtime configuration.

Question 15

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool. The security team requires that all data stored in the dedicated SQL pool be encrypted with a customer-managed key (CMK) stored in Azure Key Vault. You need to configure transparent data encryption (TDE) to use the CMK. What should you do first?

Accepted Answer

Create an Azure Key Vault and grant the Synapse workspace managed identity get, wrapKey, and unwrapKey permissions.. Configuring TDE with a customer-managed key in Azure Synapse Analytics dedicated SQL pool requires that the key exists in Azure Key Vault and that the Synapse workspace's managed identity has the necessary permissions (get, wrapKey, unwrapKey) on that key. Without these permissions, the SQL pool cannot access the key to encrypt or decrypt the database encryption key. Therefore, creating the Key Vault and granting permissions is the essential first step before enabling CMK-based TDE.

Answer

Enable double encryption at rest for the dedicated SQL pool.

Answer

Enable Azure Defender for SQL on the workspace.

Answer

Configure a private endpoint for the Azure Key Vault.