DP-203 Practice Test 18 — 15 Questions

Question 1

You need to ensure that sensitive data stored in Azure SQL Database is encrypted at rest. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) is correct because it encrypts the entire database at rest, including backups and log files. Option A (Always Encrypted) is incorrect because it encrypts data in use and in transit (client-side encryption), not at rest. Option B (Azure Information Protection) is incorrect because it is a classification and labeling service, not an encryption mechanism. Option C (Dynamic Data Masking) is incorrect because it masks data in query results to unauthorized users, but does not encrypt the underlying data.

Answer

Always Encrypted

Answer

Azure Information Protection

Answer

Dynamic Data Masking

Question 2

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

Accepted Answer

Redistribute the fact table on the join column using hash distribution.. Redistributing the fact table on the join column using hash distribution aligns the distribution key with the join column. This ensures that rows with the same join key value are stored on the same distribution, minimizing data movement during joins. Option A is wrong because round-robin distribution places rows randomly, which actually increases data movement for joins. Option B is wrong because while updating statistics helps the optimizer choose better plans, it does not directly reduce data movement. Option C is wrong because increasing the number of distributions does not change the alignment of the distribution key with the join column, so data movement remains high.

Answer

Change the distribution type to round-robin.

Answer

Update statistics on all columns used in joins.

Answer

Increase the number of distributions to 120.

Question 3

You are designing a data processing solution using Azure Databricks with Delta Live Tables (DLT). The solution must handle late-arriving data and ensure that updates to the delta table are processed exactly once. Which DLT pipeline setting should you configure?

Accepted Answer

Configure the DLT pipeline with 'Delta Live Tables' and use 'APPLY CHANGES INTO' with a watermark.. DLT pipelines support exactly-once processing and handle late data via watermarking and append-only or update modes. Option A is wrong because 'Trigger once' processes data one time only, not continuous. Option B is wrong because 'Continuous' mode processes data as it arrives but may not guarantee exactly-once without proper configuration. Option C is wrong because 'File arrival' is not a DLT setting.

Answer

Set the pipeline mode to 'Trigger once'.

Answer

Set the pipeline mode to 'Continuous' and enable 'Exactly once' delivery.

Answer

Use Auto Loader with 'file arrival' mode.

Question 4

Your organization uses Azure Data Lake Storage Gen2 to store parquet files. You need to secure the data at rest and control access. Which THREE methods should you implement?

Accepted Answer

Set POSIX-like ACLs on directories and files.. Options A, B, and D are correct. Encryption at rest is done by Azure Storage Service Encryption. Access control is via RBAC and ACLs. Option C is wrong because firewall restricts network access, not data at rest. Option E is wrong because soft delete is for data recovery, not security.

Answer

Configure Azure Storage Firewall to allow only trusted IPs.

Answer

Enable soft delete for blobs.

Question 5

You need to monitor the performance of your Azure Synapse Analytics dedicated SQL pool. Which metric should you use to identify queued queries due to concurrency limits?

Accepted Answer

Queued queries. 'Queued queries' metric directly shows the number of queries waiting due to concurrency limits. Option B is wrong because DWU percentage shows resource usage, not queuing. Option C is wrong because active queries show running queries. Option D is wrong because memory percentage shows memory pressure.

Answer

DWU percentage

Answer

Active queries

Answer

Memory percentage

Question 6

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The workload includes large fact tables and dimension tables. You need to improve query performance for star join queries. Which TWO actions should you take?

Accepted Answer

Use replicated distribution on dimension tables.. The correct actions are D and E. For star join queries in Azure Synapse Analytics dedicated SQL pool, best practices are to use hash distribution on fact tables using the join key (option E) to distribute data across distributions efficiently, and use replicated distribution on dimension tables (option D) to avoid data movement. Option A is wrong because round-robin distribution is suitable for staging or temporary tables, not for large fact tables in star schemas. Option B is wrong because dimension tables should be replicated, not hash-distributed, to minimize shuffling. Option C is wrong because heap tables are not optimized for analytical queries; clustered columnstore index is the recommended table structure for fact tables.

Answer

Use round-robin distribution on fact tables.

Answer

Use hash distribution on dimension tables.

Answer

Use heap table structure for fact tables.

Question 7

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Accepted Answer

Enable Storage Insights to monitor capacity and transactions.. Azure Storage Insights provides pre-built dashboards to monitor storage account metrics like capacity, transactions, and latency, aiding performance monitoring. Option D is correct: Azure Storage Analytics logs (now part of Azure Monitor) capture latency and request rate data, helping analyze performance. Option E is correct: Enabling diagnostic settings in Azure Monitor captures read and write request details, useful for troubleshooting performance issues. Option A is incorrect: Network Security Group flow logs monitor network traffic for security purposes, not storage performance. Option C is incorrect: Lifecycle management policies optimize costs by tiering data, not for performance monitoring.

Answer

Enable Network Security Group flow logs for the storage account subnet.

Answer

Configure lifecycle management policies to move cold data to archive tier.

Question 8

Your organization has an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only authorized users can access the data, and auditing must be enabled to track all access attempts. What should you do first?

Accepted Answer

Configure Microsoft Entra ID authentication and use RBAC to grant only necessary permissions.. The first step to secure access to the Azure Synapse Analytics dedicated SQL pool is to configure authentication and authorization using Microsoft Entra ID and RBAC. This establishes who can access the pool and what they can do. Once that is in place, you can then implement additional security measures like auditing (option B), column-level security (option A), or dynamic data masking (option D), but these are secondary steps. Option B is not the first step because auditing tracks access but does not control it. Option A is too granular for initial access control. Option D obfuscates data but does not prevent unauthorized access.

Answer

Implement column-level security to restrict sensitive columns.

Answer

Enable auditing on the SQL pool and configure a storage account for audit logs.

Answer

Apply dynamic data masking to the sensitive columns.

Question 9

You are using Azure Purview to scan an Azure Data Lake Storage Gen2 account. After scanning, you notice that some files are not classified. What is the most likely reason?

Accepted Answer

The file types are not included in the scan rule set. Purview uses scan rule sets to determine which file types to scan and apply classifications. If the file type is not included in the scan rule set, those files will be skipped during scanning, leading to no classification. Option A is incorrect because if the storage account were not registered, no files would be scanned at all. Option B is incorrect because Parquet files are supported and can be classified. Option C is incorrect because if classification rules were disabled, no files would be classified, not just some.

Answer

The storage account is not registered in Purview

Answer

The files are in Parquet format

Answer

The classification rules are disabled

Question 10

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.. `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 11

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is ingested from multiple sources and needs to be deduplicated based on a composite key (source_id, record_id). New data may have duplicates within the same batch. Which write mode and table property should you use to handle this efficiently?

Accepted Answer

Use 'append' mode and perform a MERGE operation after write to deduplicate.. Using 'append' mode writes all incoming data as new files, and then performing a MERGE operation (upsert) based on the composite key (source_id, record_id) allows you to efficiently deduplicate both within the batch and against existing data. This approach leverages Delta Lake's ACID transactions and avoids the cost of rewriting entire partitions, making it suitable for handling duplicates from multiple sources.

Answer

Use 'overwrite' mode and enable 'delta.autoOptimize.optimizeWrite' = true.

Answer

Use 'ignore' mode and set 'delta.autoCompact' = true.

Answer

Use 'error' mode and enable 'delta.merge.onSchemaMismatch' = true.

Question 12

A company is designing a data lake on Azure Data Lake Storage Gen2. Data comes from multiple sources with varying schemas. The team must minimize storage costs while keeping all data available for future processing. Which storage tier should they use for the raw ingested data?

Accepted Answer

Cool tier. The Cool tier is the optimal choice for raw ingested data in a data lake because it offers low storage costs while maintaining low-latency access for future processing. Unlike the Archive tier, Cool tier data is immediately available without the multi-hour rehydration delay, and it is significantly cheaper than the Hot tier for data that is infrequently accessed but must remain online for ETL or batch processing.

Answer

Premium tier

Answer

Archive tier

Answer

Hot tier

Question 13

You are designing a solution to monitor the performance of Azure Synapse Analytics dedicated SQL pools. You need to identify queries that are consuming more than 100 GB of memory and have been running for more than 30 minutes. Which DMV should you query?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests, is correct because it provides detailed information about active or recently completed queries, including memory consumption (estimated_memory_kb) and duration (start_time, total_elapsed_time). You can filter this DMV to find queries with memory > 100 GB and runtime > 30 minutes. Option A (sys.dm_pdw_resource_waits) shows resource wait statistics, not memory usage. Option B (sys.dm_pdw_nodes) provides node-level information, not query details. Option D (sys.dm_pdw_waits) shows wait types for queries, not memory or duration.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_waits

Question 14

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2 using Azure Data Factory. Which approach is the most efficient?

Accepted Answer

Use a lookup activity to retrieve the last watermark value, then copy only new records with a filter.. It uses a lookup activity to retrieve the last watermark value (e.g., a timestamp or incrementing key), then copies only new or changed records via a filter in the Copy activity. This minimizes data movement and processing time, making it the most efficient incremental loading approach in Azure Data Factory.

Answer

Use a tumbling window trigger with a data flow that processes all data each time.

Answer

Use a mapping data flow with a full load and then use Azure Databricks to deduplicate.

Answer

Copy the entire table every time and use Azure Synapse serverless SQL to filter duplicates.

Question 15

Match each Azure security feature to its description.

Accepted Answer

Azure Active Directory: A cloud-based identity and access management service that provides authentication and authorization.. The correct matches are: Azure AD for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Azure AD with Managed Identity and Key Vault with RBAC.

Answer

Azure Active Directory: An automatically managed identity in Azure AD for applications to connect to resources without managing credentials.

Answer

Azure Key Vault: A service that provides role-based access control for Azure resources.