DP-203 Practice Test 30 — 15 Questions

Question 1

You are designing a data pipeline that ingests JSON files from Azure Blob Storage into Azure Synapse Analytics using PolyBase. The files contain nested JSON arrays. What should you do to ensure that the data is loaded correctly?

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

Accepted Answer

Flatten the JSON arrays into a tabular format using Azure Data Factory or Databricks before loading.. PolyBase in Azure Synapse Analytics cannot directly handle nested JSON arrays; it requires a flat, tabular structure. Azure Data Factory or Databricks can flatten the nested arrays into rows and columns before loading, ensuring compatibility with PolyBase's external table format.

Answer

Create an external table with the JSON file type and use a schema definition.

Answer

Use the OPENJSON function in T-SQL to parse the JSON during the load.

Answer

Use PolyBase with a JSON format file specifying the schema.

Question 2

Which THREE best practices should be followed when designing a data lake in Azure Data Lake Storage Gen2 for optimal performance?

Accepted Answer

Use Parquet file format for analytics workloads.. Options C, D, and E are correct. Parquet format is columnar and provides efficient compression and query performance. Partitioning by date allows partition elimination, reducing data scanned. A naming convention that avoids special characters and high cardinality prevents throttling and improves parallelism. Option A is incorrect because disabling hierarchical namespace is not a best practice; hierarchical namespace enables folder-level security and is required for ADLS Gen2. Option B is incorrect because a deep directory structure can cause performance issues due to metadata overhead; a flat or shallow structure is recommended.

Answer

Disable hierarchical namespace to improve performance.

Answer

Use a deep directory structure with many subfolders.

Question 3

Which TWO actions should you take to optimize a Spark job in Azure Synapse Analytics that is experiencing excessive shuffling and long execution times?

Accepted Answer

Broadcast one of the tables if it is small enough to fit in memory.. Broadcasting a small table avoids shuffle operations during joins, as the small table is sent to all executors. Option E is correct because increasing the number of shuffle partitions (via spark.sql.shuffle.partitions) can distribute data more evenly, reducing the size of each shuffle block and improving parallelism. Option A is incorrect: coalescing reduces the number of partitions, which may increase the amount of data shuffled per task and worsen skew. Option B is incorrect: checkpointing improves fault tolerance but does not directly reduce shuffling. Option D is incorrect: disabling dynamic resource allocation can lead to resource contention or waste, not optimized shuffling.

Answer

Coalesce the number of partitions to reduce overhead.

Answer

Enable checkpointing to persist intermediate results.

Answer

Disable dynamic resource allocation.

Question 4

You have an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage. The pipeline runs successfully most of the time, but occasionally fails with a 'FTP server connection refused' error during peak hours. You need to minimize these failures with minimal cost. What should you do?

Accepted Answer

Add a retry policy to the copy activity with a backoff interval.. Adding a retry policy with a backoff interval handles transient connection issues. Option D is wrong because increasing parallel copies does not solve connection refused errors. Option B is wrong because Azure ExpressRoute is costly and unnecessary for FTP. Option C is wrong because migrating to SFTP may not resolve connection refused if the server is overloaded.

Answer

Set up Azure ExpressRoute to improve network reliability.

Answer

Migrate the FTP server to SFTP.

Answer

Increase the parallel copy count in the copy activity.

Question 5

You are optimizing cost for an Azure Data Lake Storage Gen2 account that stores historical data. The data is accessed infrequently after 30 days and must be retained for 7 years. Which lifecycle management rule should you apply?

Accepted Answer

Move blobs to cool tier after 30 days, then to archive tier after 1 year.. It moves blobs to the cool tier after 30 days (saving costs for infrequent access) and then to the archive tier after 1 year (maximizing savings for long-term retention), meeting both the access pattern and 7-year retention requirement. Option A is wrong because moving directly to archive after 30 days makes data unavailable for immediate access and incurs high retrieval costs if needed. Option B is wrong because deleting after 30 days violates the 7-year retention policy. Option C is wrong because the premium tier is designed for high performance, not cost savings on cold data.

Answer

Move blobs to archive tier immediately after 30 days.

Answer

Delete blobs after 30 days.

Answer

Move blobs to premium tier after 30 days.

Question 6

You are designing a data storage solution for a financial services company. The solution must meet the following requirements: store transaction data for 7 years for regulatory compliance, support point-in-time restore (PITR) for the last 30 days, and minimize storage costs for historical data. Which THREE actions should you take?

Accepted Answer

Use Azure Blob Storage with cool access tier for data older than 30 days. Azure Blob Storage's cool access tier is designed for infrequently accessed data with lower storage costs, making it ideal for historical transaction data older than 30 days. This tier provides cost-effective storage while still allowing retrieval when needed, aligning with the requirement to minimize storage costs for historical data.

Answer

Store historical data in Azure Data Lake Storage Gen2

Answer

Use Azure Table Storage for archival

Question 7

You need to ensure that data in an Azure Data Lake Storage Gen2 account is encrypted at rest using a customer-managed key. Which feature should you configure?

Accepted Answer

Azure Key Vault integration with Storage Service Encryption. Azure Key Vault integration with Storage Service Encryption. This feature allows ADLS Gen2 to use customer-managed keys stored in Azure Key Vault for encryption at rest. Option B is incorrect because Azure Information Protection is for data classification and labeling, not encryption. Option C is incorrect because Storage Service Encryption with Microsoft-managed keys is the default, but the requirement specifies customer-managed keys. Option D is incorrect because Azure Disk Encryption applies to VM disks, not ADLS Gen2 storage.

Answer

Azure Information Protection

Answer

Azure Storage Service Encryption with Microsoft-managed keys

Answer

Azure Disk Encryption

Question 8

You are migrating an on-premises SQL Server database to Azure. The database has a large fact table (500 GB) and several dimension tables (10 GB total). Reporting queries join the fact table with dimension tables and aggregate by date. Which Azure service and table design should you recommend to minimize query latency?

Accepted Answer

Azure Synapse SQL Pool with hash distribution on the fact table's foreign key and round-robin for dimension tables. Azure Synapse SQL Pool with hash distribution on the fact table's foreign key ensures that related rows from the fact and dimension tables are co-located on the same compute node, minimizing data movement during joins. Round-robin distribution for the small dimension tables is appropriate since they are under 1 GB each and can be broadcast to all nodes, further reducing shuffle overhead. This design optimizes parallel query execution for large fact table aggregations by date.

Answer

Azure Synapse SQL Pool with replicated tables for both fact and dimension tables

Answer

Azure SQL Database Hyperscale with columnstore indexes

Answer

Azure SQL Database with rowstore indexes and a single database

Question 9

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully but you notice that the data transfer is taking longer than expected. You need to improve the transfer performance without increasing the cost significantly. Which configuration change should you make?

Accepted Answer

Enable staged copy with an Azure Blob Storage staging location.. Enabling staged copy with an Azure Blob Storage staging location improves performance by allowing parallel uploads and retries. Option B is wrong because PolyBase is used for loading data into Azure Synapse Analytics or SQL Data Warehouse, not Blob Storage. Option C is wrong because increasing concurrent connections on SQL Server may not address the bottleneck and could impact source performance. Option D is wrong because increasing Data Integration Units (DIU) would increase cost without necessarily improving performance if the bottleneck is elsewhere (e.g., network or source).

Answer

Use PolyBase to load the data into Blob Storage.

Answer

Increase the number of concurrent connections on the SQL Server.

Answer

Increase the Data Integration Units (DIU) for the copy activity.

Question 10

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

Accepted Answer

Increase the number of Streaming Units (SUs) allocated to the job.. The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Answer

Decrease the watermark delay interval.

Answer

Increase the late arrival tolerance window.

Answer

Increase the number of partitions in the output table.

Question 11

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

Accepted Answer

Use a Copy activity with pagination rules enabled in the source.. The Copy activity in Azure Data Factory supports pagination rules, allowing it to automatically iterate through API pages without a separate loop activity. Option A is incorrect because a ForEach activity would need to know the exact number of pages upfront, which is not dynamic. Option B is incorrect because the Lookup activity retrieves a single value and cannot iterate through pages. Option C is incorrect because an Until activity would require custom logic to check for the next page, whereas the Copy activity with pagination rules handles this natively.

Answer

Use a ForEach activity to iterate over a fixed number of pages.

Answer

Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

Answer

Use an Until activity to loop until the API returns no more pages.

Question 12

You are troubleshooting a slow-running Azure Synapse Pipeline that loads data from Azure Blob Storage into a dedicated SQL pool using a Copy activity. The source is a set of CSV files totaling 500 GB. The sink is a staging table with a clustered columnstore index. The pipeline takes 4 hours to complete. You need to reduce the execution time to under 1 hour. What should you do?

Accepted Answer

Enable PolyBase in the Copy activity sink settings.. Enabling PolyBase in the Copy activity sink settings allows the data to be loaded into the dedicated SQL pool using the high-throughput PolyBase technology, which leverages the SQL pool's distributed architecture and can read data directly from Azure Blob Storage in parallel. This bypasses the bottleneck of row-by-row inserts, drastically reducing load time for large datasets like 500 GB.

Answer

Increase the Data Integration Units (DIU) in the Copy activity to the maximum.

Answer

Increase the dedicated SQL pool's DWU setting to the highest tier.

Answer

Partition the staging table on a date column.

Question 13

A data engineer runs the Azure CLI command shown in the exhibit. The blob is stored in Azure Blob Storage. The team previously set a lifecycle management rule to move blobs to the Archive tier after 30 days. The blob was created 45 days ago. What is the most likely reason the blob is still in the Cool tier?

Accepted Answer

The lifecycle management rule was configured after the blob was created, and it can take up to 24 hours for the rule to be evaluated.. The lifecycle management rule is evaluated by Azure Storage once per day. If the rule was configured after the blob was created, it may not have been evaluated yet, and it can take up to 24 hours for the rule to apply. Since the blob is 45 days old and still in Cool tier, the most likely reason is that the rule has not yet been evaluated after its configuration.

Answer

The lifecycle management rule only applies to blobs in the Hot tier.

Answer

The blob is in a container that is excluded from the lifecycle rule.

Answer

The blob must be in the Hot tier for the rule to move it to Archive.

Question 14

You are designing a data storage solution for a marketing analytics platform. The platform collects clickstream data from websites and needs to store it for both real-time dashboards and historical analysis. The data is semi-structured (JSON) and arrives at a rate of 10,000 events per second. You need to choose an Azure storage solution that can handle the ingestion rate, support schema-on-read, and integrate with Azure Databricks for advanced analytics. The solution must also be cost-effective for long-term storage. What should you use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, providing high-throughput ingestion (up to 60 GB/s per account) to handle 10,000 events per second of semi-structured JSON data. It supports schema-on-read natively, allowing Azure Databricks to query the data directly using Spark without prior schema definition, and its tiered storage (hot, cool, archive) makes it cost-effective for long-term historical analysis.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 15

Your team needs to provide near-real-time analytics on IoT sensor data streaming into Azure Event Hubs. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and device ID. Which architecture should you implement?

Accepted Answer

Use Azure Stream Analytics with output to Data Lake Storage Gen2, using partitioning by date and device ID.. Azure Stream Analytics provides native, low-latency processing of streaming data from Event Hubs with direct output to Azure Data Lake Storage Gen2. It supports automatic partitioning by specifying a partition key (e.g., date and device ID) in the output configuration, enabling efficient, near-real-time writes in Parquet format without additional orchestration.

Answer

Use Azure Data Factory with a tumbling window trigger to copy data from Event Hubs to Data Lake Storage.

Answer

Use Azure Functions to read from Event Hubs and write to Data Lake Storage.

Answer

Use Azure Databricks with Structured Streaming to read from Event Hubs and write to Data Lake Storage.