DP-203 Practice Test 20 — 15 Questions

Question 1

You need to process a large number of CSV files stored in Azure Data Lake Storage Gen2 using Azure Databricks. The files are nested in multiple folders, and the schema varies slightly between files. You want to automatically infer the schema and handle schema evolution. Which read option should you use?

Accepted Answer

spark.read.option("mergeSchema","true").csv(path). Option C (spark.read.format("csv").option("mergeSchema","true").load(path)) is correct because mergeSchema enables automatic schema inference and merging across files with different schemas. Option A loads without schema evolution. Option B infers schema but does not merge. Option D is for Delta Lake, not CSV.

Answer

spark.read.format("delta").load(path)

Answer

spark.read.option("inferSchema","true").csv(path)

Answer

spark.read.csv(path)

Question 2

You are designing a data processing pipeline that ingests data from a REST API endpoint every hour. The API returns JSON data with a varying schema. You need to store the raw data in Azure Data Lake Storage Gen2 and later process it using Azure Databricks. Which file format should you use for the raw data storage?

Accepted Answer

JSON. Option A (JSON) is correct because it preserves the original schema and is easy to store without transformation. Option B (Parquet) requires schema definition upfront. Option C (CSV) is not suitable for nested JSON. Option D (Avro) is good for streaming but less common for raw ingestion.

Answer

Parquet

Answer

CSV

Answer

Avro

Question 3

You are working with a Delta Lake table in Azure Databricks. The table is updated frequently with new data and occasionally with updates to existing rows. You need to optimize read performance for queries that filter on a specific date column. The table is partitioned by date. Which optimization technique should you apply?

Accepted Answer

Run ZORDER BY on the date column.. Option B is correct because Z-ordering reduces the amount of data scanned for filter columns. Option A is wrong because OPTIMIZE only compacts small files, not reorder data for filtering. Option C is wrong because VACUUM removes old files, not optimizes read. Option D is wrong because ANALYZE STATISTICS computes stats but does not physically reorganize data.

Answer

Run OPTIMIZE on the table.

Answer

Run ANALYZE STATISTICS on the table.

Answer

Run VACUUM to clean up old versions.

Question 4

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

Accepted Answer

Enable auto-resume and auto-pause on the serverless SQL pool endpoint. Option A is correct because serverless SQL pools automatically scale based on query demand without manual configuration. Option B is wrong because it's for dedicated SQL pools. Option C is wrong because serverless pools don't use explicit cache. Option D is wrong because serverless pools do not support workload classification.

Answer

Set a cache size for the serverless SQL pool

Answer

Configure a dedicated SQL pool with auto-scaling

Answer

Use workload classification to assign resources

Question 5

A company uses Azure Data Lake Storage Gen2 as the data lake. The data engineering team needs to ensure that sensitive data such as credit card numbers are masked when queried by non-admin users. The solution must be implemented within the data lake without moving data to another store. Which TWO features should they use? (Choose two.)

Accepted Answer

Azure Synapse Serverless SQL with dynamic data masking. Options B and C are correct. Azure Synapse Serverless SQL can create external tables over Data Lake storage and apply dynamic data masking. Azure Purview can classify sensitive columns for policy. Option A is incorrect because ADLS does not have built-in masking. Option D is incorrect because Azure SQL Database is a different store. Option E is incorrect because Azure Policy does not mask data.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies

Question 6

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Accepted Answer

Delta Lake. Correct answers: A and D. Delta Lake provides ACID transactions and schema enforcement, enabling exactly-once semantics. Azure Databricks Structured Streaming can handle late-arriving data using watermarking and event-time processing. Option B is wrong because Azure Stream Analytics does not integrate natively with Delta Lake for exactly-once semantics. Option C is wrong because Azure Data Factory is not suitable for streaming processing. Option E is wrong because PolyBase is for batch loading, not streaming.

Answer

PolyBase

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 7

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Using Azure Stream Analytics with output to both Power BI (real-time) and Data Lake Storage (historical) is a common pattern with minimal overhead. Option A is wrong because Azure Functions would require custom code for batching. Option B is wrong because Event Hubs doesn't provide real-time dashboards. Option C is wrong because Azure Databricks would require cluster management.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 8

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

Accepted Answer

Use Delta Lake's OPTIMIZE command to compact small files.. Options A and D are correct. Option A is correct because optimizing the Delta table with Z-order improves data skipping for columns used in queries. Option D is correct because using Delta Lake's optimize command (bin-packing) compacts small files, improving read performance. Option B is wrong because disabling automatic schema detection can be useful but is not required for performance. Option C is wrong because caching is optional and may not help for large datasets that don't fit in memory. Option E is wrong because enabling auto compaction helps but is not a requirement; it's a feature.

Answer

Disable automatic schema detection to reduce overhead.

Answer

Cache the entire DataFrame in memory after reading.

Answer

Enable auto-compaction in Spark configuration.

Question 9

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

Accepted Answer

Run OPTIMIZE to compact small files.. Option C is correct because OPTIMIZE compacts small files into larger ones, improving read performance. Option A (VACUUM) removes old files but does not compact. Option B (DESCRIBE HISTORY) shows history. Option D (CONVERT TO DELTA) converts Parquet but does not optimize.

Answer

Run CONVERT TO DELTA on the underlying Parquet files.

Answer

Run DESCRIBE HISTORY to analyze file sizes.

Answer

Run VACUUM to delete old files.

Question 10

Which TWO are valid output sinks for an Azure Stream Analytics job? (Choose two.)

Accepted Answer

Azure Blob Storage. Options A and D are correct. Azure Synapse Analytics and Azure Blob Storage are supported sinks. Option B (Azure Cosmos DB) is supported but not listed as correct here; note: Cosmos DB is a valid sink, but the question says 'Which TWO' and the intended correct ones are Synapse and Blob Storage. Option C (Azure Table Storage) is not directly supported. Option E (Azure SQL Data Warehouse) is the same as Synapse.

Answer

Azure SQL Data Warehouse (dedicated SQL pool)

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Table Storage

Question 11

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

Accepted Answer

Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.. Option A is correct because Partition Elimination allows the engine to skip irrelevant partitions. Option B is wrong because round-robin distribution does not improve partition elimination. Option C is wrong because increasing distribution columns may not help with date filtering. Option D is wrong because indexes are not the primary solution for partition pruning.

Answer

Create a non-clustered index on OrderDate.

Answer

Add ProductKey as a distribution column alongside the existing hash.

Answer

Change the distribution to round-robin to spread data evenly.

Question 12

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to process CSV files that arrive in a 'raw' container, transform them into Parquet format, and write them to a 'curated' container. The transformation includes filtering out rows with null values in the 'customer_id' column and adding a partition column 'year' based on the 'order_date'. You use Azure Synapse Pipelines. Which activity should you use for the transformation?

Accepted Answer

Data flow activity. Option C is correct because a data flow activity in Azure Synapse Pipelines can perform transformations like filtering and adding computed columns, and can write to ADLS Gen2 in Parquet format. Option A is wrong because Copy activity only copies data without transformation. Option B is wrong because Notebook activity requires Spark code; data flow is simpler for this scenario. Option D is wrong because Stored Procedure activity runs SQL, not file transformations.

Answer

Stored procedure activity

Answer

Notebook activity with PySpark

Answer

Copy data activity

Question 13

You are designing a data processing solution that requires running custom Python scripts for data transformation. The scripts have dependencies on specific libraries that are not pre-installed in the environment. You need a fully managed, serverless compute option that allows you to install custom libraries. Which service should you use?

Accepted Answer

Azure Databricks with cluster-scoped libraries. Azure Databricks with cluster configuration allows installation of custom libraries. Azure Synapse Spark pools also support libraries but require cluster management. Azure Functions may have limitations. Azure Container Instances require container images.

Answer

Azure Functions with custom handlers

Answer

Azure Container Instances with a custom Docker image

Answer

Azure Synapse Analytics serverless SQL pool

Question 14

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

Accepted Answer

Partition the data flow by a key column. Partitioning the data flow by a key column can improve performance by allowing parallel processing. Option A is wrong because using a staging table adds overhead. Option B is wrong because increasing batch size may cause memory issues. Option D is wrong because data flows do not use PolyBase.

Answer

Use a staging table to load data before transformation

Answer

Increase the batch size in the data flow

Answer

Use PolyBase to load data into the dedicated SQL pool

Question 15

You are designing a batch processing solution in Azure Databricks that reads Parquet files from Azure Data Lake Storage Gen2, performs aggregations, and writes results to a Delta table. The data volume is expected to grow to 10 TB per batch. You need to minimize shuffle operations during the aggregation step. Which approach should you recommend?

Accepted Answer

Partition the Delta table by the aggregation key and use the same partitioning when reading.. Option B is correct because partitioning the input data on the aggregation key ensures that each partition contains all rows for a given key, eliminating the need for a full shuffle. Option A is wrong because bucketing with a different column would not avoid shuffle on the aggregation key. Option C is wrong because coalesce reduces partitions but does not avoid shuffle. Option D is wrong because broadcast join is for joining small tables, not for aggregations.

Answer

Use bucketing on a different column to reduce partition skew.

Answer

Use a broadcast join to avoid shuffle on the aggregation.

Answer

Use coalesce to reduce the number of partitions before aggregation.