DP-203 Develop data processing Practice Test 8 — 15 Questions

Question 1

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

Accepted Answer

The table is not registered in the Spark metastore; it is only in the lakehouse.. In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Answer

The user does not have read permission on the table.

Answer

The Parquet file format is incompatible with the Delta source.

Answer

The Delta table is corrupted.

Question 2

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

Accepted Answer

Incremental copy (watermark) pattern using a Lookup activity and a Copy activity. The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Answer

Alter Row transformation

Answer

Schedule trigger

Answer

Lookup activity alone

Question 3

You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?

Accepted Answer

Hash distribution on CustomerID. Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.

Answer

Round-robin distribution

Answer

Replicate distribution

Answer

Hash distribution on Date

Question 4

Refer to the exhibit. The pipeline executes a Spark job definition that reads from the input path and writes to the output path. The job fails with an error indicating that the output path already exists. The job is configured with default behavior. What configuration change should you make to allow the job to overwrite the existing output?

Accepted Answer

Add a Delete activity before the Spark job to delete the output folder if it exists.. Spark jobs by default fail if the output directory exists. To overwrite, you need to set the Spark configuration 'spark.hadoop.parquet.enable.summary-metadata' is not relevant. The correct approach is to set the Spark configuration 'spark.sql.sources.partitionOverwriteMode' to 'STATIC' or use 'overwrite' save mode in the code. However, in the exhibit, the job is a Spark job definition that calls a file; the simplest way is to set the argument to use overwrite mode. Option B (Delete activity) is a separate activity. Option C changes the job definition. Option D (output mode) is not a pipeline property.

Answer

Modify the Spark job definition to include 'overwrite' save mode.

Answer

Change the output path in the arguments to a non-existing folder.

Answer

Set the pipeline property 'outputMode' to 'overwrite' on the SynapseSparkJob activity.

Question 5

You need to process a large number of small files (each < 1 MB) from Azure Blob Storage in Azure Synapse Analytics. The processing is I/O-bound due to many small file operations. Which approach should you use to improve performance?

Accepted Answer

Use 'spark.sql.files.maxPartitionBytes' to coalesce small files into larger partitions.. Option D is correct because `spark.sql.files.maxPartitionBytes` controls the maximum number of bytes packed into a single partition when reading files. By increasing this value, Spark coalesces many small files into fewer, larger partitions, reducing the overhead of task scheduling and I/O operations. This directly addresses the I/O-bound bottleneck caused by processing numerous small files in Azure Synapse Analytics.

Answer

Use wildcard paths to read multiple files at once.

Answer

Enable optimized write on the Spark session.

Answer

Convert the files to a binary format like Avro before processing.

Question 6

Which TWO of the following are valid ways to handle late-arriving data in a streaming solution with Azure Stream Analytics? (Choose two.)

Accepted Answer

Configure the 'late arrival tolerance' window in the event ordering settings up to 21 days.. Option D is correct because Azure Stream Analytics allows you to configure a 'late arrival tolerance' window in the event ordering settings, which can be set up to a maximum of 21 days. This window defines how long the service will wait to accommodate events that arrive after their timestamp, reordering them within that tolerance before processing. Option E is correct because a temporal join (e.g., using LATERAL or JOIN with DATEDIFF) can combine a late-arriving event with historical data from a reference or stream window, enabling you to retroactively correct aggregations or state.

Answer

Reprocess the entire stream from the beginning when late data is detected.

Answer

Implement a custom Azure Function as a 'LateDataHandler' in the query.

Answer

Use a reference data input to store late-arriving events.

Question 7

Which THREE of the following are best practices for optimizing performance of Delta Lake tables in Azure Synapse Analytics? (Choose three.)

Accepted Answer

Run the OPTIMIZE command to compact small files and improve read performance.. Option A is correct because the OPTIMIZE command in Delta Lake compacts small files into larger ones, reducing the number of files that need to be read during queries. This improves read performance by minimizing file listing overhead and enabling more efficient I/O, especially in scenarios with many small writes.

Answer

Partition on high-cardinality columns like UserID to maximize parallelism.

Question 8

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Accepted Answer

Amazon S3. Option A is correct because Azure Blob Storage is a supported source. Option C is correct because Amazon S3 is a supported source via a connector. Option B is wrong because Power BI is not a data source for Copy activity. Option D is wrong because Azure Analysis Services is not a supported source. Option E is wrong because Azure DevOps is not a data store.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 9

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Serverless SQL pools infer the schema from the first file encountered. If files have different schemas, the inference may be inconsistent. Option A is correct because the schema inference is done on the first file, and mismatches cause errors. Option B is wrong because recursive reads subfolders but not different schemas. Option C is wrong because .parquet extension is fine. Option D is wrong because derivedModel is false, which means no model is used, but that doesn't cause schema mismatch.

Answer

The Parquet files are not using the .parquet extension.

Answer

The derivedModel option is set to false, which disables schema inference.

Answer

The recursive option is causing the table to include files from subfolders that have different schemas.

Question 10

You are building a data processing solution that requires exactly-once semantics when writing to Azure Event Hubs from Azure Stream Analytics. Which output format should you configure?

Accepted Answer

Avro. Azure Stream Analytics supports exactly-once semantics when writing to Event Hubs only when using Avro serialization. This is because Avro provides a compact binary format with embedded schema, enabling Stream Analytics to track and deduplicate events precisely during output, which is required for exactly-once delivery. JSON, CSV, and Parquet do not support the necessary metadata and checkpointing mechanisms for exactly-once guarantees in this specific integration.

Answer

JSON

Answer

CSV

Answer

Parquet

Question 11

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

Accepted Answer

Notebook activity configured to use a Python kernel. A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Answer

Web activity

Answer

Stored Procedure activity

Answer

HDInsight Hive activity

Question 12

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

Accepted Answer

The statistics on the tables are outdated or missing.. Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Answer

The tables are distributed using round-robin distribution.

Answer

Result-set caching is disabled.

Answer

The resource class for the user is set to smallrc.

Question 13

You are designing a data ingestion pipeline for Azure Synapse Analytics using PolyBase. The source data is in Azure Blob Storage, with files in the Parquet format. The data volume is approximately 2 TB daily. You need to minimize the time to load data into a dedicated SQL pool table. Which approach should you recommend?

Accepted Answer

Create an external table pointing to the Parquet files and use CREATE TABLE AS SELECT (CTAS) to load the data.. Option B is correct because creating an external table with the Parquet file format and using CREATE TABLE AS SELECT (CTAS) allows PolyBase to leverage predicate pushdown and column pruning, which reduces data movement and improves performance. Option A is wrong because splitting files into smaller chunks increases the number of parallel reads but the overhead of many small files can degrade performance. Option C is wrong because converting to CSV adds overhead and increases data size. Option D is wrong because COPY INTO is optimized for small to medium loads, while CTAS with external tables is better for large-scale loads.

Answer

Split the Parquet files into many small files (e.g., 64 MB each) to maximize parallelism.

Answer

Convert the Parquet files to CSV format before loading using PolyBase.

Answer

Use the COPY INTO command to load the Parquet files directly into the dedicated SQL pool.

Question 14

You are building a data pipeline in Azure Data Factory to copy data from an on-premises SQL Server database to Azure Blob Storage. The pipeline must run daily and handle incremental updates. The on-premises SQL Server table has a LastModifiedDate column that is updated when a row changes. What is the most efficient way to implement incremental loads?

Accepted Answer

Use a Lookup activity to get the maximum LastModifiedDate from the destination, then use a Copy activity with a query that filters rows where LastModifiedDate > that value.. Option B is correct because it uses a Lookup activity to retrieve the maximum LastModifiedDate from the destination (Azure Blob Storage), then passes that value as a parameter to a Copy activity that queries only rows where LastModifiedDate exceeds it. This minimizes data transfer by reading only new or changed rows, and it avoids the overhead of enabling Change Data Capture or performing full loads, making it the most efficient approach for incremental loads in Azure Data Factory.

Answer

Enable Change Data Capture (CDC) on the SQL Server database and use an ADF mapping data flow to read changes.

Answer

Use a tumbling window trigger with a window size of 1 day and copy all data from the source each time.

Answer

Perform a full load every day and use a Delete activity to remove duplicates.

Question 15

You are designing a streaming solution in Azure Synapse Analytics using the serverless SQL pool to query streaming data in real-time. The data is ingested via Azure Event Hubs and processed using Azure Stream Analytics. The output of Stream Analytics is written to Azure Data Lake Storage Gen2 in Delta Lake format. You need to ensure that the serverless SQL pool can query the latest data with minimal latency. Which approach should you use?

Accepted Answer

Create an external table in serverless SQL pool that points to the Delta Lake folder and query it directly.. Option D is correct because serverless SQL pool can directly query Delta Lake format files stored in Azure Data Lake Storage Gen2 by creating an external table with LOCATION pointing to the Delta folder. This allows real-time querying of the latest streaming data without any data movement or transformation, achieving minimal latency since Stream Analytics writes continuously to Delta Lake.

Answer

Ingest data directly from Event Hubs into serverless SQL pool using CETAS (CREATE EXTERNAL TABLE AS SELECT).

Answer

Use a materialized view in serverless SQL pool that refreshes every minute.

Answer

Load the streaming data into a dedicated SQL pool using a scheduled pipeline and then query it from serverless SQL pool.