DP-203 Practice Test 30 — 15 Questions

Question 1

You have an Azure Data Lake Storage Gen2 account that contains a container named raw with millions of small JSON files, each under 1 MB. A daily Azure Data Factory pipeline reads these files and writes them to a curated container as Parquet files. You notice that the pipeline runs slowly and you want to optimize read performance. What should you do first?

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

Accepted Answer

Compact the small files into larger files before processing.. The primary performance bottleneck when reading millions of small files is the per-file overhead. Compacting them into larger files reduces the number of read operations and improves throughput. Other options like increasing parallelism or enabling hierarchical namespace do not address the root cause, and binary copy would not solve the small file issue for analytics.

Answer

Use a binary copy and then convert to Parquet in a downstream process.

Answer

Enable hierarchical namespace on the storage account.

Answer

Increase the degree of copy parallelism in the Azure Data Factory Copy activity.

Question 2

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and has recently started taking longer than expected. You need to identify the cause of the performance degradation. Which two actions should you perform? (Choose two.)

Accepted Answer

Check the on-premises SQL Server performance counters for CPU, memory, and disk I/O during the pipeline run.. Reviewing activity run logs helps identify which activity is slow and its duration, while checking on-premises SQL Server performance counters determines if the source is a bottleneck. Together, these diagnostic steps pinpoint the cause of the performance degradation. The other options are either remediation actions or broader monitoring setups that do not directly diagnose the recent slowdown.

Answer

Increase the DIU (Data Integration Units) for the copy activity to improve throughput.

Answer

Reconfigure the pipeline to use a self-hosted integration runtime with more nodes.

Answer

Enable Azure Data Factory diagnostic settings to send logs to Log Analytics and analyze with Kusto queries.

Question 3

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table with over 1 billion rows. Queries frequently join this fact table with smaller dimension tables on a distribution key. You notice that many queries perform poorly due to data movement. You need to reduce data movement and improve query performance. Which two actions should you take? (Choose two.)

Accepted Answer

Use hash distribution on the fact table's join column.. Hash distributing the fact table on the join column and replicating the dimension tables co-locate related data, minimizing data movement during joins. This is a standard optimization in Azure Synapse Analytics dedicated SQL pools. Other options do not address the root cause of data movement.

Answer

Partition the fact table by date.

Answer

Use round-robin distribution for the fact table.

Answer

Create a clustered columnstore index on the fact table.

Question 4

You manage an Azure Data Lake Storage Gen2 account containing a large volume of JSON logs. Users frequently query only the last seven days of data, but each query scans the entire dataset, causing high costs and slow response times. You need to reduce the amount of data scanned by queries without changing the data format or moving the data. What should you do?

Accepted Answer

Partition the data by date into separate folders and update queries to filter on the date path.. Partitioning the dataset by date and aligning queries with the partition path allows the query engine to prune unnecessary folders, dramatically reducing the bytes scanned. This approach keeps the original JSON format and location, satisfying the constraints. It directly addresses the root cause: lack of data organization that forces full scans.

Answer

Enable hierarchical namespace on the storage account.

Answer

Convert the JSON files to Parquet format.

Answer

Increase the throughput of the storage account.

Question 5

You have an Azure Data Lake Storage Gen2 account that contains a container named raw with millions of small JSON files, each under 1 MB. A daily Azure Data Factory pipeline reads these files and writes them to a curated container as Parquet files. You notice that the pipeline runs slowly and you want to optimize read performance. What should you do first?

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

Accepted Answer

Enable hierarchical namespace and configure POSIX access control lists (ACLs) on files and folders, and enable Azure Storage logging to capture access.. To enforce access control at the file and folder level, you need to enable hierarchical namespace and use POSIX ACLs. This allows granular permissions on directories and files. To audit access, enable Azure Storage logging or integrate with Azure Monitor. This solution minimizes administrative effort because ACLs can be inherited and managed centrally. The other options either provide only coarse-grained access control or focus on network security rather than access auditing.

Answer

Use shared access signatures (SAS) with specific permissions and IP restrictions, and enable Azure Defender for Storage.

Answer

Configure Azure Private Endpoints and enable firewall rules to restrict access to the storage account.

Answer

Enable Azure role-based access control (RBAC) on the storage account and assign the Storage Blob Data Contributor role to users.

Question 6

You are implementing dynamic data masking on an Azure Synapse Analytics dedicated SQL pool. A table named Customers contains columns: CustomerID (int), Email (varchar), Phone (varchar), and CreditCard (varchar). You need to mask the Email and Phone columns so that users without elevated permissions see only the last four characters of the Email and Phone, while users with elevated permissions see the full values. You also need to ensure that the masking does not affect the storage size of the columns. What should you do?

Accepted Answer

Use the MASKED WITH (FUNCTION = 'partial(0,"****",4)') clause on the Email and Phone columns and grant UNMASK permission to elevated users.. Dynamic data masking in Azure Synapse Analytics dedicated SQL pools allows you to define masking rules at the column level using the MASKED WITH clause. The partial function can reveal the last four characters while masking the rest. This does not change the underlying storage size because masking is applied at query time. Granting UNMASK permission to privileged users allows them to see the full data, fulfilling the access requirements.

Answer

Implement row-level security (RLS) on the Customers table to filter rows based on user permissions.

Answer

Encrypt the Email and Phone columns using Always Encrypted with deterministic encryption and grant decryption keys only to elevated users.

Answer

Create a masked view that concatenates '****' with the last four characters of Email and Phone, and grant SELECT on the view to users.

Question 7

You are designing a storage solution for a healthcare analytics platform. The data lands in Azure Data Lake Storage Gen2, and analysts must query it through Azure Synapse Analytics serverless SQL pools. Security policy requires that analysts see only the columns relevant to their role, and that access be governed by Microsoft Entra ID identities rather than shared keys. Which two actions should you include in the design? (Choose two.)

Accepted Answer

Grant analysts the Storage Blob Data Reader role on the ADLS Gen2 container through Microsoft Entra ID.. Serverless SQL pools in Synapse authenticate to ADLS Gen2 through the caller's Microsoft Entra identity, so Storage Blob Data Reader on the container provides the necessary file access without shared keys. Column-level restriction is then achieved by exposing only approved columns through a view or stored procedure and granting SELECT on that object. Together these actions satisfy both the identity governance and least-privilege column visibility requirements.

Answer

Enable anonymous public read access on the ADLS Gen2 container so that serverless queries do not require credentials.

Answer

Create a database scoped credential in the serverless database that stores the storage account access key.

Answer

Configure a shared access signature with read permissions and distribute it to each analyst for use in their queries.

Question 8

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. You need to minimize data movement during query execution for joins between the fact table and smaller dimension tables. What should you do?

Accepted Answer

Distribute the fact table using hash distribution on the join column and replicate the dimension tables.. Hash distributing the fact table on the join column co-locates matching rows, and replicating small dimension tables places them on all compute nodes. This combination eliminates data movement during joins, which is critical for performance in dedicated SQL pools. The other options either use inappropriate distribution or do not fully minimize movement.

Answer

Distribute the fact table using hash distribution on the primary key and replicate the dimension tables.

Answer

Distribute the fact table using round-robin distribution and replicate the dimension tables.

Answer

Distribute the fact table using hash distribution on the join column and distribute the dimension tables using round-robin distribution.

Question 9

You are designing a security strategy for an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to ensure that only specific Azure services can access the storage account. What should you do?

Accepted Answer

Configure a customer-managed key for encryption and set the storage firewall to allow access from selected Azure services.. Customer-managed keys in Azure Key Vault enable encryption at rest with keys controlled by the organization. Configuring the storage firewall to allow access from selected Azure services ensures that only trusted services can reach the data. Together, these settings satisfy the encryption and access restriction requirements. Other options address network isolation or threat detection but not the specific encryption and service-level access controls needed.

Answer

Configure Azure Private Link and enable soft delete.

Answer

Enable infrastructure encryption and use shared access signatures (SAS) for access.

Answer

Enable Azure Defender for Storage and configure a private endpoint.

Question 10

You are designing a data storage solution for a healthcare analytics platform. The solution must store patient records in Azure SQL Database and allow point-in-time restore for any time within the last 35 days. The data must be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. You need to configure the Azure SQL Database to meet these requirements. What should you do?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and configure the retention policy for automated backups to 35 days.. The requirement is for encryption at rest with customer-managed keys and point-in-time restore for 35 days. TDE with a customer-managed key in Azure Key Vault provides the required encryption. Configuring the backup retention policy to 35 days enables point-in-time restore within that window. Other options either use the wrong encryption method or confuse long-term retention with point-in-time restore.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and configure long-term retention (LTR) for 35 days.

Answer

Enable Transparent Data Encryption (TDE) with a service-managed key and configure the retention policy for automated backups to 35 days.

Answer

Enable Always Encrypted with a column master key stored in Azure Key Vault and configure the retention policy for automated backups to 35 days.

Question 11

You are optimizing an Azure Synapse Analytics dedicated SQL pool that experiences performance degradation during peak hours. You need to reduce query execution time by improving data distribution and reducing data movement. Which two actions should you take? (Choose two.)

Accepted Answer

Convert small dimension tables to replicated tables.. Hash distributing large fact tables on a high-cardinality join column colocates matching rows, minimizing data movement during joins. Replicating small dimension tables eliminates the need to shuffle them across nodes. Together, these actions directly reduce data movement and improve query performance in a dedicated SQL pool. Other options either add resources without addressing distribution or introduce inappropriate indexing.

Answer

Implement table partitioning on a date column in the fact tables.

Answer

Create a clustered index on all columns of the fact tables.

Answer

Increase the number of distributions by scaling the dedicated SQL pool to a higher service level.

Question 12

You are configuring security for an Azure Data Lake Storage Gen2 account that stores sensitive data. You need to ensure that all data access is logged and that you can audit who accessed the data and when. You also need to retain the logs for 90 days. What should you do?

Accepted Answer

Configure diagnostic settings to send logs to a Log Analytics workspace.. Diagnostic settings in Azure allow you to export resource logs to a Log Analytics workspace, where you can analyze and retain them for up to 90 days (or longer with custom retention). This provides the necessary auditing capability for Data Lake Storage Gen2 access.

Answer

Enable soft delete for blobs.

Answer

Use Azure Storage Analytics logging to a storage account.

Answer

Enable Azure Defender for Storage.

Question 13

You manage an Azure Synapse Analytics workspace. A dedicated SQL pool contains a table with a column named CustomerEmail that stores email addresses. You need to ensure that users who are not members of the DataPrivacy role see only a masked version of the email addresses when they query the table, while members of DataPrivacy see the actual values. The solution must minimize administrative effort. What should you do?

Accepted Answer

Create a dynamic data masking rule on the CustomerEmail column using the email() masking function and grant UNMASK to the DataPrivacy role.. Dynamic data masking is designed to limit sensitive data exposure by masking it to non-privileged users. Applying an email() mask to the CustomerEmail column ensures that users without the UNMASK permission see a masked format, while granting UNMASK to the DataPrivacy role allows those members to see the actual values. This approach requires only a one-time configuration and no changes to queries or applications.

Answer

Enable Always Encrypted with a column encryption key and configure the DataPrivacy role as the only role with access to the key.

Answer

Apply a column-level security policy using GRANT SELECT ON the CustomerEmail column to the DataPrivacy role.

Answer

Implement row-level security with a predicate function that filters rows based on the user's role.

Question 14

You are designing a storage solution in Azure Synapse Analytics for a financial services company. The company ingests trade data into a dedicated SQL pool. The data is partitioned by trade date and queried primarily by date ranges. To improve query performance and reduce data movement, you need to choose an appropriate distribution type for the fact table. The table is large (over 2 billion rows) and frequently joined with a smaller dimension table on a non-distributed key. Which distribution type should you use?

Accepted Answer

Hash-distributed on the join key column. For a large fact table frequently joined on a specific key, hash-distributing on that join key ensures that matching rows from both tables reside on the same distribution, enabling collocated joins and avoiding costly data movement. This distribution strategy is recommended when the join key has high cardinality and is used in most queries.

Answer

Hash-distributed on the trade date column

Answer

Round-robin distribution

Answer

Replicated distribution

Question 15

You are designing a storage solution for a global application that requires low-latency reads and writes of JSON documents. The data model includes nested properties, and you need to query these properties efficiently. You also need to ensure the data is available in multiple regions with automatic failover. Which Azure service should you use?

Accepted Answer

Azure Cosmos DB with Core (SQL) API and multi-region writes enabled.. Azure Cosmos DB with Core (SQL) API is designed for JSON documents, supports querying nested properties, and offers multi-region writes with automatic failover, ensuring low latency globally. Other services either lack native JSON support, do not support multi-region writes, or are not optimized for document queries.

Answer

Azure SQL Database with active geo-replication.

Answer

Azure Blob Storage with RA-GRS replication.

Answer

Azure Table Storage with a partition key and row key.