DP-203 Practice Test 36 — 15 Questions

Question 1

You are designing a data processing solution in Azure Synapse Analytics. You need to load data from multiple sources into a dedicated SQL pool. Which THREE of the following are best practices for loading data?

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

Accepted Answer

Use round-robin distribution for staging tables. Round-robin distribution is recommended for staging tables because it distributes data evenly across all distributions without requiring a hash key, minimizing data movement during the load process. Since staging tables are temporary and used for intermediate storage, the even distribution ensures that the subsequent INSERT...SELECT operation into the final table benefits from maximum parallelism and reduced skew.

Answer

Split data into many small files to increase parallelism

Answer

Enable clustered columnstore index during load to improve query performance

Question 2

You are designing a data processing solution in Azure Synapse Analytics. You need to load data from multiple sources into a dedicated SQL pool. Which THREE of the following are best practices for loading data?

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

Accepted Answer

Partition the Parquet files by date and use partition pruning in the query.. Partitioning Parquet files by a commonly filtered column, such as date, allows Azure Synapse serverless SQL pool to perform partition pruning, which eliminates scanning unnecessary partitions and reduces the amount of data read. Option A is incorrect because increasing MAXDOP (maximum degree of parallelism) can lead to resource contention and may not improve query performance in serverless SQL pool. Option B is incorrect because Parquet is a columnar format optimized for analytics and is more efficient than CSV for querying large datasets. Option C is incorrect because materialized views are not supported in serverless SQL pool; they are only available in dedicated SQL pool.

Answer

Increase the MAXDOP setting in the query.

Answer

Convert the Parquet files to CSV format for faster parsing.

Answer

Create materialized views on the external tables.

Question 3

You have an Azure Data Factory (ADF) pipeline that runs hourly to ingest data from an on-premises SQL Server into Azure Data Lake Storage Gen2. The pipeline includes a Copy activity that transfers all rows from a source table 'Sales' (approximately 10 million rows) to a Parquet file in the data lake. Recently, you notice that the pipeline runtime has increased from 15 minutes to over an hour. The source database CPU utilization is normal, and the network bandwidth is not saturated. You check ADF monitoring and see high 'Data integration unit' consumption and frequent 'BlobWrite' throttling errors. The storage account is in the same region as the ADF. You need to reduce the pipeline runtime. What should you do?

Accepted Answer

Modify the pipeline to use incremental loads instead of full loads each time.. The pipeline runtime has increased due to frequent BlobWrite throttling errors, indicating that the storage account is hitting its write request limits. By modifying the pipeline to use incremental loads instead of full loads each hour, you reduce the volume of data written per execution, which lowers the number of write operations and avoids throttling. This directly addresses the root cause without requiring a storage tier upgrade or a complete architectural change.

Answer

Change the storage account to Premium tier to increase throughput limits.

Answer

Replace the Copy activity with an Azure Databricks notebook to process the data.

Answer

Use PolyBase in the Copy activity to load data directly into Azure Synapse Analytics.

Question 4

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to ensure that only the 'data-scientists' group can read files in the 'processed' container, while denying access to all other users. You have already configured the storage account firewall to allow access only from your corporate network. What should you do next?

Accepted Answer

Configure access control lists (ACLs) on the 'processed' container to grant read and execute permissions to the data-scientists group and set the default ACL to deny all. In Azure Data Lake Storage Gen2 with hierarchical namespace, access control lists (ACLs) provide fine-grained permissions at the directory and file level. By granting read and execute permissions to the 'data-scientists' group on the 'processed' container and setting the default ACL to deny all, you ensure only that group can read files. Option A is incorrect because private endpoints control network access, not identity-based permissions. Option B is incorrect because RBAC roles (like Storage Blob Data Reader) grant permissions at the storage account or container level, and Azure RBAC does not support deny assignments that would block specific users while allowing others at the same scope; a deny assignment would block everyone. Option C is incorrect because managed identities are used for authenticating Azure services, not for granting permissions to a security group.

Answer

Create a private endpoint for the storage account and assign the data-scientists group to the private endpoint's access policy

Answer

Assign the Storage Blob Data Reader role to the data-scientists group at the storage account level and add a deny assignment for all other users

Answer

Use a managed identity for the data-scientists group and assign the Storage Blob Data Contributor role to the managed identity

Question 5

A company uses Azure Databricks to process streaming data from Event Hubs. The data is written to a Delta table. The job occasionally fails due to checkpoint corruption. Which THREE measures should you implement to improve reliability?

Accepted Answer

Configure checkpointing to a durable storage like Azure Data Lake Storage.. Checkpointing to durable storage like Azure Data Lake Storage (ADLS) ensures that streaming progress metadata is persisted across cluster restarts and failures. ADLS provides high durability and availability, preventing checkpoint corruption that can occur with local or ephemeral storage, thereby enabling exactly-once processing guarantees in Structured Streaming.

Answer

Increase the batch interval to reduce load.

Answer

Increase the cluster size to handle spikes.

Question 6

A company is designing a data storage solution for IoT device telemetry data. The data is append-only, needs to be stored cost-effectively for long-term analytics, and must support querying by device ID and timestamp. Which Azure storage solution should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines the cost-effective, append-only blob storage of Azure Blob Storage with a hierarchical namespace that enables directory-level operations and POSIX-like access control. This makes it ideal for storing large volumes of IoT telemetry data at low cost while supporting efficient querying by device ID and timestamp through partition pruning in tools like Azure Synapse Analytics or Apache Spark.

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Answer

Azure Blob Storage with hot access tier

Question 7

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 8

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

Accepted Answer

Azure Storage Service Encryption (SSE) with customer-managed keys. Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Answer

Azure Disk Encryption

Answer

Azure Storage Firewall

Answer

Azure Information Protection

Question 9

You have an Azure Data Factory pipeline that uses a Copy activity to move data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with a timeout error. You need to improve the reliability of the data transfer. Which configuration change should you make?

Accepted Answer

Enable fault tolerance and configure skip incompatible rows.. Enabling fault tolerance and configuring 'skip incompatible rows' allows the Copy activity to continue processing even when some rows cause errors (e.g., type conversion failures), which can manifest as timeouts when the activity repeatedly retries the same problematic rows. This setting improves reliability by skipping rows that cannot be copied, preventing the entire pipeline from failing on intermittent data issues.

Answer

Use staged copy with an intermediate Azure Blob Storage.

Answer

Use PolyBase as the sink.

Answer

Increase the retry count in the pipeline activity.

Question 10

You are designing a solution for a social media company that needs to store user profile data with strong consistency and low latency (under 10 ms) for reads and writes. The data model is simple key-value with occasional queries on secondary attributes. Which Azure data store meets these requirements?

Accepted Answer

Azure Cosmos DB with session consistency and secondary indexes. Azure Cosmos DB with session consistency and secondary indexes meets the requirements because it provides single-digit-millisecond latency for both reads and writes, supports strong consistency (session consistency offers monotonic reads and writes), and allows efficient queries on secondary attributes via indexing. This makes it ideal for a key-value store with occasional secondary attribute queries, unlike simpler stores that lack indexing or consistency guarantees.

Answer

Azure Table Storage

Answer

Azure SQL Database with clustered index on user ID

Answer

Azure Cache for Redis with persistence

Question 11

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 12

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Serverless SQL. Which statement about this table is correct?

Accepted Answer

The table is read-only. External tables in Azure Synapse Serverless SQL are read-only because they provide a relational abstraction over data stored externally (e.g., in Azure Data Lake Storage Gen2 or Blob Storage). You cannot perform INSERT, UPDATE, DELETE, or DDL modifications on the underlying data through the external table; it is designed solely for querying with T-SQL. This is a fundamental constraint of the serverless SQL pool architecture, which uses the OPENROWSET or CREATE EXTERNAL TABLE syntax to read files in place without a storage engine.

Answer

The table supports indexing for performance

Answer

The external data source TYPE must be 'HADOOP' for Azure Data Lake Storage Gen2

Answer

The table references a single Parquet file named 'Sales.parquet'

Question 13

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 14

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

Accepted Answer

Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).. Partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Answer

Partition by device ID first, then by date.

Answer

Partition by device ID only, with a separate directory for each device.

Answer

Partition by device type only, with a directory for each type.

Question 15

Which THREE actions improve the performance of a Delta table in Azure Databricks? (Choose three.)

Accepted Answer

Run VACUUM to remove old versions and compact small files. Running VACUUM on a Delta table removes old file versions that are no longer referenced by the Delta transaction log, freeing up storage and reducing the number of small files that must be read during queries. This compaction of small files improves I/O efficiency and query performance, especially in tables with frequent updates or deletes.

Answer

Disable auto-optimize to reduce overhead

Answer

Increase the number of shuffle partitions