DP-203 Practice Test 3 — 25 Questions

Question 1

A manufacturing company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled and Azure Databricks for analytics. The security team requires that all data stored in the 'raw' container be encrypted at rest using customer-managed keys. The data is ingested via Azure Data Factory. What should the data engineer configure to meet the requirement?

Accepted Answer

Configure the storage account to use Azure Key Vault for customer-managed key encryption.. Option C is correct because Azure Data Lake Storage Gen2 with hierarchical namespace supports encryption at rest using customer-managed keys (CMK) via Azure Key Vault. To meet the security requirement, the data engineer must configure the storage account's encryption settings to use a key from Azure Key Vault, which allows the organization to control and rotate the encryption keys independently of Azure.

Answer

Assign an Azure Policy that requires encryption at rest.

Answer

Enable Azure Information Protection on the storage account.

Answer

Enable the 'require secure transfer' setting on the storage account.

Question 2

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is slow. Which action would most likely improve performance?

Accepted Answer

Create statistics on the columns used in WHERE clauses. In Azure Synapse Analytics serverless SQL pool, query performance on external data like Parquet files heavily relies on statistics to enable the optimizer to generate efficient query plans. Without statistics, the engine makes default cardinality estimates, often leading to suboptimal joins and filters. Creating statistics on columns used in WHERE clauses allows the serverless SQL pool to accurately estimate row counts and choose better execution strategies, directly improving query performance. While serverless SQL pool does support partition pruning through folder hierarchies, the benefits of partition elimination are limited to reducing data scanned; statistics provide more critical metadata for cost-based optimization, making them the most impactful choice for slow queries.

Answer

Partition the Parquet files by date

Answer

Create a materialized view on the external data

Answer

Create a PolyBase external table and query that

Question 3

You have an Azure Synapse Analytics serverless SQL pool. You need to monitor the number of queries that are currently executing. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_requests. The correct answer is C. sys.dm_exec_returns detailed information about each request currently executing on the serverless SQL pool, including its state, command, and session ID. This DMV is specifically designed for monitoring active queries.

Option A (sys.dm_resource_governor_workload_groups) shows workload group configuration and resource statistics, not current requests.

Option B (sys.dm_exec_query_stats) provides cumulative performance statistics for cached query plans, not currently executing queries.

Option D (sys.dm_exec_sessions) contains session-level information but does not indicate which sessions are actively executing a request.

Answer

sys.dm_resource_governor_workload_groups

Answer

sys.dm_exec_query_stats

Answer

sys.dm_exec_sessions

Question 4

Drag and drop the steps to implement Slowly Changing Dimension (SCD) Type 2 in Azure Synapse Analytics dedicated SQL pool into the correct order.

Accepted Answer

Stage data, then identify changes, then expire old records, then insert new versions.. SCD Type 2: stage data, merge to find changes, expire old records, insert new versions, and update attributes (if needed).

Answer

Stage data, then insert new versions, then expire old records.

Answer

Identify changes, then stage data, then expire old records, then insert new versions.

Answer

Stage data, then expire old records, then identify changes, then insert new versions.

Question 5

Drag and drop the steps to convert data from CSV to Parquet format using Azure Data Factory into the correct order.

Accepted Answer

Define source CSV dataset, then define sink Parquet dataset, then create copy activity with mapping, then run and monitor.. Define source (CSV) and sink (Parquet) datasets, then a copy activity with mapping, run, and monitor.

Answer

Define sink Parquet dataset, then define source CSV dataset, then create copy activity with mapping, then run and monitor.

Answer

Define source CSV dataset, then create copy activity with mapping, then define sink Parquet dataset, then run and monitor.

Answer

Create copy activity with mapping, then define source CSV dataset, then define sink Parquet dataset, then run and monitor.

Question 6

Drag and drop the steps to implement incremental data loading using Azure Data Factory into the correct order.

Accepted Answer

Lookup the last watermark value from control table, then copy data from source where timestamp > watermark, then update watermark with max timestamp from copied data.. Incremental loading requires a watermark column. The pipeline retrieves the last watermark, copies data changed since then, then updates the watermark for next run.

Answer

Copy data from source, then lookup the last watermark value, then update watermark.

Answer

Update watermark with max timestamp from source, then lookup the last watermark, then copy data.

Answer

Lookup the last watermark value, then update watermark with max timestamp, then copy data.

Question 7

Drag and drop the steps to set up Azure Purview for data cataloging and lineage tracking into the correct order.

Accepted Answer

Create Azure Purview account, then Register data sources, then Scan data sources, then Set classifications, then Explore catalog. First create the Purview account, then register sources, scan them, set classifications, and finally explore the catalog.

Answer

Create Azure Purview account, then Register data sources, then Set classifications, then Scan data sources, then Explore catalog

Answer

Register data sources, then Create Azure Purview account, then Scan data sources, then Set classifications, then Explore catalog

Answer

Create Azure Purview account, then Scan data sources, then Register data sources, then Set classifications, then Explore catalog

Question 8

Drag and drop the steps to set up Azure Data Lake Storage Gen2 hierarchical namespace for a data lake into the correct order.

Accepted Answer

Enable hierarchical namespace on storage account, create a container, create directories, set permissions, upload data.. The storage account must have hierarchical namespace enabled. Then create a container, directories, set permissions, and upload data.

Answer

Enable hierarchical namespace on storage account, create directories, create a container, set permissions, upload data.

Answer

Enable hierarchical namespace on storage account, create a container, set permissions, create directories, upload data.

Answer

Create a container, enable hierarchical namespace on storage account, create directories, set permissions, upload data.

Question 9

Refer to the exhibit. You created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files. Queries return no rows even though the files exist. What is the most likely issue?

Accepted Answer

The LOCATION path in the external table is relative to the data source, but the data source points to the wrong container or folder. The most likely issue is that the LOCATION path in the external table is relative to the data source. In the exhibit, the external data source points to the root container 'sales', and the external table LOCATION is 'parquet/sales/'. The combined path becomes 'sales/parquet/sales/', which is likely incorrect. The correct data source should point to the container containing the 'parquet' folder, or the LOCATION should be adjusted. Option A is incorrect because CREDENTIAL is already defined. Option B is incorrect because FILE_FORMAT is specified and valid for Parquet. Option C is incorrect because DATA_COMPRESSION is supported for Parquet.

Answer

The CREDENTIAL is missing

Answer

The FILE_FORMAT is incorrectly specified

Answer

The DATA_COMPRESSION is not supported for Parquet

Question 10

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

Accepted Answer

Increase spark.sql.shuffle.partitions to 800.. Increasing spark.sql.shuffle.partitions to 800 improves parallelism for a 200 GB shuffle-heavy workload, reducing partition sizes and shuffle time. Option A is incorrect because executor count alone does not directly address shuffle partitioning. Option B may help but is less impactful than partition tuning. Option D would worsen the problem by making partitions larger.

Answer

Increase the number of executors to 4.

Answer

Change executor size to 'Large' to increase memory per executor.

Answer

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Question 11

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. You have Parquet files in Azure Data Lake Storage Gen2 partitioned by date and hour. You need to query the data for the last 7 days efficiently, minimizing the amount of data scanned. Which approach should you use?

Accepted Answer

Use OPENROWSET with a file path that limits to the last 7 days' partitions and use a WHERE clause on the date column.. Option D is correct because using OPENROWSET with a file path that restricts to the last 7 days' partitions (e.g., '/date=2025-03-*/hour=*/*.parquet') minimizes data scanned by only reading the relevant Parquet files. Applying a WHERE clause on the date column further filters rows within those files, leveraging partition pruning at the file system level. This approach avoids scanning all partitions, which is critical for cost and performance in serverless SQL pool.

Answer

Use OPENROWSET with a wildcard path that includes all partitions and then apply a WHERE clause.

Answer

Create an external table with partition definition and query with a WHERE clause on the partition column.

Answer

Create a view using PolyBase external table and query with a date filter.

Question 12

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use replicated tables for small dimension tables. Correct: C and D. Replicated tables are optimal for small dimension tables because they avoid data movement during joins. Materialized views pre-compute and store aggregation results, improving query performance for common aggregations. A is incorrect because hash distribution on a low-cardinality column can lead to data skew and uneven distribution. B is incorrect because round-robin distribution is generally used for staging tables, not fact tables in a star schema. E is incorrect because increasing DWU is a scaling action, not a query optimization design choice.

Answer

Use hash distribution on a low-cardinality column

Answer

Use round-robin distribution for fact tables

Answer

Increase the DWU setting after every query

Question 13

You are designing data security for an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to ensure that only authorized users can access the data and that access can be audited. Which TWO actions should you implement?

Accepted Answer

Configure diagnostic settings to send logs to a Log Analytics workspace.. Option A is correct because configuring diagnostic settings to send logs to a Log Analytics workspace enables auditing of data access events, such as read, write, and delete operations, which is essential for compliance and security monitoring. Option E is correct because Azure AD authentication provides fine-grained access control using role-based access control (RBAC) and eliminates the need for shared keys, aligning with the principle of least privilege.

Answer

Use storage account access keys (Shared Key) for authentication.

Answer

Generate shared access signatures (SAS) with a long expiry time.

Answer

Configure a firewall to allow access from all Azure services.

Question 14

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

Accepted Answer

Store processed file names in a Delta table and compare with source folder listing. Option D is correct because storing processed file names in a Delta table allows the pipeline to track which files have already been ingested, supporting incremental processing and handling late-arriving data up to 2 hours. By comparing the current source folder listing against the Delta table, the pipeline can identify only new or late-arriving files, avoiding reprocessing and ensuring exactly-once semantics. This approach integrates seamlessly with Azure Databricks and Delta Lake's ACID transactions, providing reliable state management for batch pipelines.

Answer

Use Blob Storage event triggers to invoke Azure Functions

Answer

Use Azure Synapse Pipelines with a schedule and full load each time

Answer

Use Azure Data Factory with watermark columns in the source

Question 15

A company uses Azure Synapse Analytics with a dedicated SQL pool. They need to ensure that a team of data scientists can query all tables in the 'sales' schema but cannot modify any data or schema objects. Which role should the team be assigned?

Accepted Answer

db_datareader. The `db_datareader` role grants read-only access to all user tables in a database, allowing the team to query all tables in the 'sales' schema without the ability to modify data or schema objects. This aligns perfectly with the requirement for data scientists to perform SELECT queries only.

Answer

db_owner

Answer

db_ddladmin

Answer

db_datawriter

Question 16

You are designing a data transformation pipeline using Azure Databricks. The pipeline reads from Azure Data Lake Storage Gen2, performs aggregations, and writes to a Synapse dedicated SQL pool. Which three configurations should you implement to optimize performance and minimize cost? (Choose three.)

Accepted Answer

Enable Delta Lake on the storage account. Option A is correct because enabling Delta Lake on the storage account allows you to use Delta tables, which provide ACID transactions, scalable metadata handling, and unified batch/streaming capabilities. This is essential for reliable and performant data transformations in Azure Databricks, especially when reading from ADLS Gen2 and writing to Synapse.

Answer

Use a single-node cluster to reduce cost

Answer

Disable autoscaling to avoid cost variability

Answer

Use default Spark shuffle partitions (200)

Question 17

You are designing a data processing solution for a retail company. The solution must ingest streaming sales data from point-of-sale (POS) systems and batch uploads from stores that are offline. The total data volume is 5 TB daily. The solution must allow real-time dashboards and periodic batch processing. Which combination of services and ingestion patterns is most cost-effective and scalable?

Accepted Answer

Use Azure Event Hubs with Kafka protocol for all incoming data. Use Stream Analytics for real-time dashboards and Event Hubs Capture to land data in ADLS for batch processing. Option B is correct because Azure Event Hubs with Kafka protocol provides a unified ingestion endpoint for both streaming POS data and batch offline uploads, eliminating the need for separate services. Stream Analytics enables real-time dashboards, while Event Hubs Capture automatically lands data into Azure Data Lake Storage for cost-effective batch processing, making this the most scalable and cost-effective solution for 5 TB daily.

Answer

Use Azure IoT Hub for POS streaming and Azure Blob Storage for offline store uploads, then process with Stream Analytics and Data Factory

Answer

Use Azure Data Lake Storage for all data, then use Azure Databricks structured streaming for real-time and batch

Answer

Use Azure Stream Analytics directly on POS data and store offline uploads in Blob Storage, then batch process with U-SQL

Question 18

You have a Synapse Analytics dedicated SQL pool. You need to load 100 GB of CSV data from Azure Data Lake Storage Gen2 into a fact table. The table has a hash-distributed column. Which pattern is most efficient for loading with minimal impact on concurrent queries?

Accepted Answer

Use CREATE TABLE AS SELECT (CTAS) with the hash-distributed column. Option B is correct because CTAS with a hash-distributed column loads data directly into the target table with the same distribution scheme, avoiding data movement and minimizing resource contention. This pattern is optimized for bulk loading large datasets into a hash-distributed fact table, as it leverages the Synapse SQL pool's MPP architecture to parallelize the operation without blocking concurrent queries.

Answer

Use PolyBase INSERT...SELECT with rowstore table

Answer

Use COPY INTO command with a round-robin distribution

Answer

Use Azure Data Factory Copy activity with staging enabled

Question 19

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

Accepted Answer

Power BI with DirectQuery mode and configure automatic page refresh.. Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards. Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Answer

Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.

Answer

Azure Data Explorer (ADX) with a continuous export to Power BI.

Answer

Azure Synapse Serverless SQL pool with Power BI import mode.

Question 20

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected. After reviewing the query plans, you see that some queries are spilling to tempdb. What should you do to reduce tempdb spills?

Accepted Answer

Increase the resource class for the user executing the queries.. Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Answer

Redistribute the tables using hash distribution.

Answer

Rebuild all columnstore indexes.

Answer

Add partitioning to the tables.