Optimize and Troubleshoot Data Storage and Data Processing DP-203 Practice Questions (Page 2)

Questions 6–10

1. 6application · medium

   A data lake contains millions of small CSV files generated by an IoT device. A Synapse serverless SQL query that reads the entire lake is very slow. You need to improve query performance while minimizing changes to the existing data pipeline. What should you do?

   • ACreate an external table with a partition on the ingestion date and use partition pruning.
   • BUse OPENROWSET with the CSV parser and set the rowset option to a larger value.
   • CEnable result-set caching in Synapse serverless SQL.
   • DRun a one-time compaction job to convert the small CSV files into fewer, larger Parquet files.
   Reveal Solution Discussion Select an answer first
2. 7foundation · easy

   When troubleshooting a failed Spark job, where should you look first to find the root cause?

   • AThe Spark driver and executor logs.
   • BThe virtual network configuration.
   • CThe Azure Active Directory sign-in logs.
   • DThe Azure subscription billing report.
   Reveal Solution Discussion Select an answer first
4. 8expert · hard

   A data lake contains a Delta table that is updated every 5 minutes with small batches. The table has thousands of small files, and a nightly compaction job runs to merge them. However, the compaction job itself is slow and sometimes fails because it reads all the small files. You need to make the compaction job more efficient and reliable. What should you do?

   • AIncrease the number of executors for the compaction job.
   • BUse OPTIMIZE with a file size threshold and run it more frequently, such as every hour.
   • CUse ZORDER BY on the table to improve query performance.
   • DSet the Spark configuration spark.sql.shuffle.partitions to a lower value for the compaction job.
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   A Spark job that aggregates sales data by product category runs daily. Recently, one category ('Electronics') accounts for 80% of the records. The job's stages show that most tasks finish quickly, but a few tasks take much longer and some executors run out of memory. You need to improve the job's reliability and performance. What should you do?

   • AIncrease the number of shuffle partitions to 2000.
   • BUse broadcast join to distribute the category dimension to all executors.
   • CCache the input DataFrame in memory before aggregation.
   • DAdd a salt column to the category key and perform two-level aggregation: first aggregate by (category, salt), then aggregate by category.
   Reveal Solution Discussion Select an answer first
6. 10application · medium

   A data engineering team runs a daily Spark job that reads millions of small JSON files from Azure Data Lake Storage Gen2 and writes aggregated results to Parquet. The job takes much longer than expected, and the cluster's driver logs show high task scheduling overhead. You need to reduce the number of files read by the job without changing the source data. What should you do?

   • AIncrease the number of executors and cores per executor in the Spark cluster configuration.
   • BUse Azure Synapse Serverless SQL to query the JSON files directly instead of Spark.
   • CRun a separate compaction job that reads the small files and writes them into fewer, larger Parquet files in a new folder, then point the daily job to that folder.
   • DEnable Azure Data Lake Storage Gen2 hierarchical namespace and set the blob index tags on each file.
   Reveal Solution Discussion Select an answer first