DP-203 Practice Test 27 — 15 Questions

Question 1

You are designing a data lake in Azure Data Lake Storage Gen2 for a large enterprise. You need to ensure that only authorized users can access the data, and you must implement the principle of least privilege. Which security mechanism should you use to grant fine-grained access to specific directories and files without modifying the underlying storage account firewall settings?

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

Accepted Answer

Azure RBAC roles combined with POSIX-like ACLs. Azure RBAC combined with POSIX-like ACLs allows fine-grained permissions at the directory and file level, supporting the principle of least privilege without modifying firewall settings. Option D is incorrect because shared access signatures (SAS) grant time-limited access but are not fine-grained at the directory/file level and can be complex to manage. Option B is incorrect because managed identities provide identity-based access but still require RBAC or ACLs for fine-grained control. Option C is incorrect because storage account firewall rules apply at the account level, not at the directory or file level.

Answer

Managed identities for Azure resources

Answer

Storage account firewall rules

Answer

Shared access signatures (SAS)

Question 2

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

Accepted Answer

Query the sys.dm_exec_requests DMV to view running queries and their resource usage.. Querying the sys.dm_exec_requests DMV provides real-time resource consumption data for running queries in serverless SQL pool, allowing identification of the most resource-intensive queries. Option A is incorrect because query hints optimize execution but should be applied after identifying problematic queries. Option C is incorrect as diagnostic settings send logs to Log Analytics, but DMVs offer immediate insights without additional configuration. Option D is incorrect because serverless SQL pool automatically manages statistics, and creating additional statistics is not the priority when first troubleshooting resource consumption.

Answer

Use query hints to optimize execution plans.

Answer

Enable diagnostic settings and send query logs to Log Analytics.

Answer

Create statistics on all columns used in queries.

Question 3

Your Azure Synapse Analytics dedicated SQL pool is experiencing high resource utilization. You need to identify queries that are consuming excessive memory. Which THREE DMVs should you query?

Accepted Answer

sys.dm_pdw_sql_requests. To identify queries consuming excessive memory in Azure Synapse Dedicated SQL pool, you can query sys.dm_pdw_exec_requests to view all requests, sys.dm_pdw_request_steps to view the steps of requests, and sys.dm_pdw_sql_requests to view the distributed SQL requests. Option B (sys.dm_pdw_nodes_resource_usage) shows node-level resource usage, and Option E (sys.dm_pdw_waits) shows waiting queries, but neither directly identifies memory-intensive queries.

Answer

sys.dm_pdw_nodes_resource_usage

Answer

sys.dm_pdw_waits

Question 4

You are reviewing an Azure Policy assignment that uses the above JSON to define a role-based access control (RBAC) action. What is the primary purpose of this policy?

Accepted Answer

To authorize generation of a shared access signature (SAS) token for the storage account.. The policy JSON defines a role-based access control (RBAC) action that grants the 'Microsoft.Storage/storageAccounts/listAccountSas/action' permission. This specific action authorizes the generation of a shared access signature (SAS) token at the storage account level, not at the container or blob level. Therefore, the primary purpose is to allow the generation of an account SAS token, which provides delegated access to storage services.

Answer

To assign RBAC roles to users for the storage account.

Answer

To enable delegation of access to a specific blob.

Answer

To allow users to set permissions on storage account containers.

Question 5

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.

Question 6

You are a data engineer for a large e-commerce company. You have an Azure Synapse Analytics dedicated SQL pool that stores transactional data. The pool is currently at DWU1000c. You have a critical dashboard that runs a complex query every 5 minutes. The query scans a large fact table partitioned by date. The query performance is degrading over time as data accumulates. You need to improve performance without increasing DWUs or changing the dashboard query. You also need to minimize data movement overhead. You have the following options:

A. Create a columnstore index on the fact table with a partition alignment.
B. Create a materialized view that aggregates the data at the partition level.
C. Implement result-set caching and set the cache to expire every 5 minutes.
D. Redistribute the fact table using hash distribution on the date column.

Which option should you choose?

Accepted Answer

Implement result-set caching and set the cache to expire every 5 minutes.. Result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A (columnstore index) is wrong because the table likely already has a columnstore index (the default in Synapse). Option B (materialized view) is wrong because materialized views require maintenance and may not match the exact query. Option D (hash distribution on date) is wrong because it can cause data skew and does not reduce scan overhead as effectively as caching.

Answer

Create a columnstore index on the fact table with a partition alignment.

Answer

Create a materialized view that aggregates the data at the partition level.

Answer

Redistribute the fact table using hash distribution on the date column.

Question 7

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that queries are experiencing excessive wait time due to concurrency slots being exhausted. What is the recommended approach to improve concurrency without increasing cost?

Accepted Answer

Classify queries using workload classification and assign lower importance to reduce concurrency slot usage.. By classifying queries with workload classification and assigning lower importance to background or non-critical queries, those queries consume fewer concurrency slots, freeing slots for higher-priority queries without additional cost. Option A is incorrect because workload groups manage resources but do not directly reduce per-query concurrency slot usage; they allocate resources among groups but total concurrency slots remain fixed. Option C is incorrect because scaling up increases cost without necessarily improving concurrency efficiency. Option D is incorrect because table distribution type (e.g., round-robin) affects query performance and data movement, not concurrency slot consumption.

Answer

Create additional workload groups and assign queries to them.

Answer

Scale up the dedicated SQL pool to a higher service level.

Answer

Change the distribution type of tables to round-robin.

Question 8

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

Accepted Answer

Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.. It uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Answer

Use hash-distributed tables for all tables and clustered columnstore indexes.

Answer

Use replicated tables for all fact tables and hash-distributed tables for dimension tables.

Answer

Use round-robin tables for all tables to simplify the migration.

Question 9

You are designing a data processing pipeline in Azure Synapse Analytics that ingests streaming data from Azure Event Hubs and stores it in a dedicated SQL pool. The data must be available for querying within 5 minutes of ingestion. Which processing approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a dedicated SQL pool output and configure a 1-minute window.. Azure Stream Analytics is purpose-built for real-time stream processing and can output directly to a dedicated SQL pool. By configuring a 1-minute window, you ensure data is materialized in the SQL pool well within the 5-minute SLA, meeting the latency requirement with headroom.

Answer

Use Azure Data Factory with a tumbling window trigger set to 5 minutes.

Answer

Use PolyBase to load data from Event Hubs into the dedicated SQL pool every 5 minutes.

Answer

Use Spark Structured Streaming in Azure Synapse to write micro-batches every 5 minutes.

Question 10

Your organization uses Azure Purview for data governance. You need to automatically scan an Azure Data Lake Storage Gen2 account and classify sensitive data such as credit card numbers and social security numbers. What should you configure?

Accepted Answer

A new scan rule set in Purview with classification rules for sensitive data types. A new scan rule set in Purview with classification rules for sensitive data types. Purview allows you to create custom scan rule sets that include classification rules for sensitive data types like credit card numbers and social security numbers. When you run a scan on Azure Data Lake Storage Gen2, it uses these rules to automatically detect and classify the sensitive data. Option A is incorrect because Azure Information Protection (AIP) scanner is for classifying and protecting files in on-premises file shares and SharePoint, not for scanning Azure Data Lake Storage. Option B is incorrect because Microsoft Defender for Cloud is for security posture management and threat detection, not data classification. Option D is incorrect because Azure Policy with guest configuration is for auditing and enforcing compliance settings on virtual machines, not for data classification in storage.

Answer

Azure Information Protection (AIP) scanner

Answer

Microsoft Defender for Cloud

Answer

Azure Policy with built-in guest configuration

Question 11

Which TWO are valid ways to process data in Azure Synapse Analytics?

Accepted Answer

Use Synapse SQL pool to run T-SQL queries.. Synapse SQL pool (formerly SQL DW) is a dedicated or serverless SQL engine within Azure Synapse Analytics that allows you to run T-SQL queries for data transformation, loading, and querying. It is a first-class compute resource designed for large-scale data warehousing workloads, making T-SQL queries a valid and primary method for processing data in Synapse.

Answer

Use Logic Apps to run data transformations.

Answer

Use Azure Functions to process data in a serverless manner.

Answer

Use Power BI to transform data.

Question 12

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 13

You are developing a data processing pipeline in Azure Data Factory. The pipeline uses a mapping data flow to transform data. You need to ensure that the data flow can handle schema drift from the source. Which THREE of the following actions should you take? (Select THREE.)

Accepted Answer

Enable 'Allow schema drift' in the source settings.. Enabling 'Allow schema drift' in the source settings of a mapping data flow tells Azure Data Factory to accept incoming columns that are not defined in the source schema. This is essential for handling schema drift, as it prevents the pipeline from failing when new columns appear in the source data.

Answer

Use a 'Select' transformation to explicitly choose only the known columns.

Answer

Add a 'Derived Column' transformation for every possible column that might be added.

Question 14

You are designing a data lake architecture using Azure Data Lake Storage Gen2. Sensitive customer data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. Additionally, access must be audited at the file level. Which combination of features should you implement?

Accepted Answer

Customer-managed keys (CMK) in Azure Key Vault and Azure Storage analytics logs. Customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer-controlled keys, and Azure Storage analytics logs (or diagnostic settings) capture file-level audit events such as read and write operations. Option A is incorrect because Azure AD authentication controls access but does not provide encryption at rest. Option B is incorrect because Azure Information Protection is a classification and labeling solution, not an encryption mechanism for data at rest. Option D is incorrect because service-managed keys do not give customer control over encryption, and Azure Monitor alerts are for notifications, not audit logging.

Answer

Azure AD authentication and Azure Storage firewall

Answer

Azure Information Protection labels and Azure Policy

Answer

Service-managed keys and Azure Monitor alerts

Question 15

You are designing a data pipeline in Azure Data Factory that reads data from an Azure SQL Database and writes to Azure Synapse Analytics dedicated SQL pool. The pipeline will run hourly and process incremental changes. You need to ensure that the pipeline handles retries for transient failures and logs all activities for audit purposes. What should you configure?

Accepted Answer

Enable pipeline logging to an Azure Storage account and set retry policy on copy activity.. Enabling pipeline logging to an Azure Storage account captures all activity for audit purposes, and setting a retry policy on the copy activity handles transient failures. Option A is incorrect because Azure SQL Database and Synapse SQL pool auditing logs database access, not pipeline activity. Option B is incorrect because a tumbling window trigger schedules runs at intervals but does not log activities or handle retries by itself; retries must be configured separately. Option D is incorrect because an Azure Monitor alert notifies on failures but does not log pipeline activities or provide automated retries.

Answer

Enable auditing on the Azure SQL Database and Azure Synapse SQL pool.

Answer

Configure a tumbling window trigger to rerun the pipeline on failure.

Answer

Use an Azure Monitor alert to notify on failures.