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

Question 1

You are building a data transformation in Azure Databricks using PySpark. The data includes a column 'timestamp' in string format 'yyyy-MM-dd HH:mm:ss'. You need to convert this to a timestamp type and extract the date part for partitioning. Which code snippet should you use?

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

Accepted Answer

df.withColumn('date', to_date('timestamp', 'yyyy-MM-dd HH:mm:ss')). `to_date` with the format string 'yyyy-MM-dd HH:mm:ss' converts the string column to a date type, extracting only the date part (year, month, day) as required for partitioning. This matches the requirement to convert the timestamp string to a date for partitioning, not a full timestamp.

Answer

df.withColumn('date', col('timestamp').cast('date'))

Answer

df.withColumn('date', to_timestamp('timestamp', 'yyyy-MM-dd HH:mm:ss'))

Answer

df.withColumn('date', to_date('timestamp'))

Question 2

A data engineer is tasked with optimizing a Spark job in Azure Synapse Analytics that processes 10 TB of data daily. The job currently uses 50 executors with 4 cores each. The performance is bottlenecked by shuffle operations. The engineer wants to reduce shuffle data size. Which technique should be applied?

Accepted Answer

Use column pruning to select only required columns before shuffle.. Column pruning (selecting only required columns) reduces the amount of data shuffled, decreasing shuffle size. Option A is incorrect because coalescing reduces the number of partitions but does not reduce the data volume shuffled. Option B is incorrect because increasing the number of shuffle partitions can increase shuffle overhead, not reduce it. Option C is incorrect because broadcasting all tables is impractical for large datasets (10 TB) and does not reduce shuffle data.

Answer

Coalesce the number of partitions before the shuffle.

Answer

Increase the number of shuffle partitions.

Answer

Broadcast all tables to avoid shuffles.

Question 3

Your company uses Azure Data Lake Storage Gen2 and Azure Databricks for data processing. Some Parquet files in the lake are written with a schema that includes a column 'address' of struct type. A downstream process expects 'address' to be a string. You need to transform the data in a way that minimizes read overhead and does not rewrite the entire dataset. Which approach should you use?

Accepted Answer

Apply a schema-on-read transformation that casts the column to string when reading.. Schema-on-read allows you to cast the 'address' column from struct to string at query time without modifying the underlying Parquet files. This approach minimizes read overhead by avoiding a full data rewrite and leverages Spark's ability to apply transformations during the read path, which is efficient for downstream processes that expect a string type.

Answer

Use schema evolution in Delta Lake to change the column type.

Answer

Create a view that selects the column as a string and save it as a new table.

Answer

Rewrite the Parquet files with the correct schema using a Spark job.

Question 4

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

Accepted Answer

Increase the length of the destination columns in the SQL table to accommodate the source data.. The error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists. Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Answer

Set 'enable identity insert' to true.

Answer

Use auto-create table option in the copy activity.

Answer

Enable staging copy to use PolyBase.

Question 5

A company is ingesting streaming data from IoT devices into Azure Event Hubs. The data must be processed in near real-time and stored in Azure Synapse Analytics for reporting. The solution must handle late-arriving data and ensure exactly-once semantics. Which Azure service should you use for stream processing?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is correct because it supports exactly-once semantics for output to Azure Synapse Analytics, handles late-arriving data via adjustable event ordering policies, and is purpose-built for real-time stream processing. Option A (Azure Data Factory) is incorrect as it is primarily a batch ETL service and not designed for near-real-time streaming. Option B (Azure Synapse Spark Structured Streaming) can process streaming data but does not provide exactly-once semantics to Synapse out of the box without additional configuration. Option D (Azure Event Hubs with Capture) is incorrect because it only captures raw event data to storage and does not perform stream processing or guarantee exactly-once delivery to Synapse.

Answer

Azure Data Factory with Event Hubs source

Answer

Azure Synapse Spark Structured Streaming

Answer

Azure Event Hubs with Capture

Question 6

You are designing a data processing solution for a large e-commerce company. The data includes sensitive customer information that must be encrypted at rest and in transit. The solution uses Azure Data Lake Storage Gen2 and Azure Databricks. Compliance requires that all access to the data is logged and monitored. Which combination of security features should you implement?

Accepted Answer

Enable Azure Storage encryption at rest, enforce HTTPS, and configure diagnostic settings to log all read/write operations.. It covers encryption at rest (Azure Storage encryption), encryption in transit (HTTPS), and logging via diagnostic settings for read/write operations. Option A is incorrect because Azure RBAC does not log access details; it only controls permissions. Option B is incorrect because Azure Firewall restricts network traffic but does not provide logging of data access or encryption. Option D is incorrect because Transparent Data Encryption (TDE) is a feature for Azure SQL databases, not for Azure Data Lake Storage Gen2.

Answer

Enable Azure RBAC for storage and use Azure AD authentication.

Answer

Use Azure Firewall to restrict access and enable Azure AD Authentication for Databricks.

Answer

Enable transparent data encryption (TDE) on the storage account and use service endpoints.

Question 7

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

Accepted Answer

The file was uploaded to the workspace filesystem, not to DBFS.. The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path. Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue. Option C is incorrect because the job cluster automatically has permission to access DBFS.

Answer

The job is using a different runtime that does not support Python.

Answer

The file is too large for DBFS.

Answer

The job cluster does not have permission to access DBFS.

Question 8

A data engineer is building a pipeline in Azure Data Factory to load data from Azure Blob Storage to Azure Synapse Analytics using PolyBase. The pipeline must handle large files efficiently and minimize load time. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Split large files into multiple smaller files (e.g., 256 MB each).. To optimize PolyBase loads for large files, splitting files into smaller chunks (e.g., 256 MB) enables parallel processing and reduces load time. Configuring PolyBase with the 'RejectValue' option allows handling of bad rows without failing the entire load, improving resilience. Option B (using a staging table) is not a PolyBase-specific optimization; staging is used for other loading methods like COPY INTO. Option C (converting to Parquet) is not directly related to PolyBase performance and is not required. Option D (copy activity with staging) may use PolyBase but the question asks for PolyBase-specific actions, and staging is not a PolyBase feature. Therefore, the correct actions are A and E.

Answer

Use a staging table to stage the data before loading.

Answer

Convert files to Parquet format for better compression.

Answer

Use the copy activity with staging enabled.

Question 9

You are designing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs, and the output is to Azure Synapse Analytics. The solution must guarantee exactly-once delivery to Synapse. Which THREE configurations are required? (Choose three.)

Accepted Answer

Define a watermark strategy in the query to handle late-arriving events.. For exactly-once delivery to Azure Synapse Analytics from Stream Analytics, you need: a unique identifier column in the output table to enable deduplication (D), a watermark strategy to handle late-arriving events (B), and the output table must have a primary key to support upsert operations (E). Batch mode (A) is not supported for exactly-once delivery; instead, the output should use row-level insert/upsert. Setting the late arrival tolerance to zero (C) is not required; it is used to discard events that arrive after the tolerance, but it does not contribute to exactly-once semantics.

Answer

Configure the output to use batch mode for writing.

Answer

Set the late arrival tolerance window to zero.

Question 10

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. Azure Synapse Serverless SQL can query data directly from Azure Data Lake Storage Gen2 and supports dynamic data masking (DDM) to obfuscate sensitive columns like credit card numbers from non-admin users. This allows masking to be applied at query time without moving or transforming the underlying data in the lake.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies

Question 11

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options:

Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase.

Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase.

Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse.

Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source.

Which option should you choose?

Accepted Answer

Option B. It handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency. Option D requires additional log-shipping, increasing complexity and latency.

Answer

Option C

Answer

Option A

Answer

Option D

Question 12

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. Delta Lake is correct because it provides ACID transactions, schema enforcement, and time travel capabilities, which are essential for handling late-arriving data and ensuring exactly-once semantics when combined with Structured Streaming. It allows you to merge late records into existing Delta tables using merge operations (upserts) without corrupting the data state.

Answer

PolyBase

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 13

You are developing a data pipeline in Azure Data Factory that ingests data from multiple on-premises SQL Server databases to Azure Data Lake Storage Gen2. The data volume is about 1 TB per day. You need to ensure the pipeline can handle the volume and provide monitoring and alerting. Which THREE components should you include?

Accepted Answer

Self-hosted integration runtime. Correct answers: B, C, and E. Self-hosted integration runtime (B) is required for connectivity to on-premises SQL Server databases. Azure Monitor (C) provides monitoring and alerting for the pipeline. Data flow activity (E) allows data transformation at scale. Option A is incorrect because Azure Synapse Analytics pipeline is not used in this context; the pipeline is in Azure Data Factory. Option D is incorrect because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 14

You are reviewing a mapping data flow in Azure Data Factory that reads a CSV file from ADLS Gen2 and writes to an Azure Synapse Analytics dedicated SQL pool. The data flow includes a Derived Column transformation with the expression: `column1 == "Error" ? toString(column1) : column1`. The pipeline fails with an error indicating that the sink table could not be created. What is the most likely cause?

Accepted Answer

The sink dataset is configured for JSON format.. In a mapping data flow, the sink dataset must match the destination format. For an Azure Synapse dedicated SQL pool, the dataset should be of type 'Azure Synapse Analytics' (table), not JSON. If the dataset is set to JSON, the pipeline will fail when trying to create the sink table because the format is incompatible. Option C is incorrect because allowCopyCommand is a feature of the Copy activity, not mapping data flows.

Answer

The source file does not have a header row.

Answer

The Derived Column expression has a syntax error.

Answer

Using allowCopyCommand with autoCreate is not supported.

Question 15

You are a data engineer for a healthcare company that processes patient data. You have an Azure Databricks workspace with a cluster configured for data processing. You need to implement a solution that processes streaming data from Azure Event Hubs, enriches it with reference data stored in Azure Cosmos DB, and writes the output to Delta Lake in Azure Data Lake Storage Gen2. The solution must ensure that the data processing is fault-tolerant and can handle schema evolution. The reference data is updated infrequently. You need to choose an approach that minimizes complexity and cost. What should you do?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use a streaming static join to enrich with reference data from Cosmos DB, and write to Delta Lake. Enable schema evolution on the Delta table.. Azure Databricks Structured Streaming provides a scalable and fault-tolerant way to process streaming data from Event Hubs. Using a streaming static join efficiently enriches the stream with infrequently updated reference data from Cosmos DB without the complexity of change data capture. Writing to Delta Lake enables schema evolution natively, and the solution minimizes cost by leveraging existing Databricks infrastructure. Option A is incorrect because Auto Loader is for batch file ingestion, not streaming from Event Hubs, and Change Data Capture adds unnecessary complexity. Option B is incorrect because using batch processing (Azure Data Factory copy) increases latency and complexity compared to a streaming approach. Option C is incorrect because Azure Stream Analytics would introduce additional service cost and lacks the flexibility of Databricks for schema evolution and advanced transformations.

Answer

Use Azure Databricks Auto Loader with Delta Live Tables to ingest streaming data, and use Change Data Capture from Cosmos DB to update the reference data inline.

Answer

Use Azure Data Factory to copy data from Event Hubs to Azure Data Lake Storage Gen2 in batches, then use Azure Databricks to process and enrich with Cosmos DB.

Answer

Use Azure Stream Analytics to ingest from Event Hubs, join with Cosmos DB reference data, and output to Azure Data Lake Storage Gen2 in Parquet format.