Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 281:

  You develop data engineering solutions for a company.

  A project requires the deployment of data to Azure Data Lake Storage.

  You need to implement role-based access control (RBAC) so that project members can manage the Azure Data Lake Storage resources.

  Which three actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Create security groups in Azure Active Directory (Azure AD) and add project members.

  B. Configure end-user authentication for the Azure Data Lake Storage account.

  C. Assign Azure AD security groups to Azure Data Lake Storage.

  D. Configure Service-to-service authentication for the Azure Data Lake Storage account.

  E. Configure access control lists (ACL) for the Azure Data Lake Storage account.

  Correct Answer: ACE

  AC: Create security groups in Azure Active Directory. Assign users or security groups to Data Lake Storage Gen1 accounts.

  E: Assign users or security groups as ACLs to the Data Lake Storage Gen1 file system

  Reference: https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-secure-data

• Question 282:

  You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1.

  Several users execute ad hoc queries to DW1 concurrently.

  You regularly perform automated data loads to DW1.

  You need to ensure that the automated data loads have enough memory available to complete quickly and successfully when the adhoc queries run.

  What should you do?

  A. Hash distribute the large fact tables in DW1 before performing the automated data loads.

  B. Assign a smaller resource class to the automated data load queries.

  C. Assign a larger resource class to the automated data load queries.

  D. Create sampled statistics for every column in each table of DW1.

  Correct Answer: C

  The performance capacity of a query is determined by the user's resource class. Resource classes are pre-determined resource limits in Synapse SQL pool that govern compute resources and concurrency for query execution. Resource classes can help you configure resources for your queries by setting limits on the number of queries that run concurrently and on the compute-resources assigned to each query. There's a trade-off between memory and concurrency.

  Smaller resource classes reduce the maximum memory per query, but increase concurrency. Larger resource classes increase the maximum memory per query, but reduce concurrency.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource-classes-for-workload-management

• Question 283:

  You have an Azure Synapse Analytics de contains a fact table named Table1.

  You need to identify the extent of the data skew in Table1.

  What should you do in Synapse Studio?

  A. Connect to the built-in pool and run DBCC PDW_SHOWSPACEUSED.

  B. Connect to the built-in pool and run DBCC CHECKALLOC.

  C. Connect to Pool1 and query sys.dm_pdw_node_status.

  D. Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

  Correct Answer: D

  Microsoft recommends use of sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

• Question 284:

  You have a SQL pool in Azure Synapse.

  You discover that some queries fail or take a long time to complete.

  You need to monitor for transactions that have rolled back.

  Which dynamic management view should you query?

  A. sys.dm_pdw_request_steps

  B. sys.dm_pdw_nodes_tran_database_transactions

  C. sys.dm_pdw_waits

  D. sys.dm_pdw_exec_sessions

  Correct Answer: B

  You can use Dynamic Management Views (DMVs) to monitor your workload including investigating query execution in SQL pool.

  If your queries are failing or taking a long time to proceed, you can check and monitor if you have any transactions rolling back.

  Example: -- Monitor rollback SELECT

  SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),

  t.pdw_node_id,

  nod.[type]

  FROM sys.dm_pdw_nodes_tran_database_transactions t JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id GROUP BY t.pdw_node_id, nod.[type]

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-rollback

• Question 285:

  You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information.

  You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information.

  What should you include in the recommendation?

  A. data masking

  B. Always Encrypted

  C. column-level security

  D. row-level security

  Correct Answer: C

  Column-level security simplifies the design and coding of security in your application, allowing you to restrict column access to protect sensitive data.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/column-level-security

• Question 286:

  You are designing an Azure Databricks interactive cluster. The cluster will be used infrequently and will be configured for auto-termination. You need to use that the cluster configuration is retained indefinitely after the cluster is terminated. The solution must minimize costs. What should you do?

  A. Pin the cluster.

  B. Create an Azure runbook that starts the cluster every 90 days.

  C. Terminate the cluster manually when processing completes.

  D. Clone the cluster after it is terminated.

  Correct Answer: A

  Azure Databricks retains cluster configuration information for up to 70 all-purpose clusters terminated in the last 30 days and up to 30 job clusters recently terminated by the job scheduler. To keep an all-purpose cluster configuration even after it has been terminated for more than 30 days, an administrator can pin a cluster to the cluster list.

  Reference: https://docs.microsoft.com/en-us/azure/databricks/clusters/

• Question 287:

  You plan to create an Azure Synapse Analytics dedicated SQL pool.

  You need to minimize the time it takes to identify queries that return confidential information as defined by the company's data privacy regulations and the users who executed the queues.

  Which two components should you include in the solution? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. sensitivity-classification labels applied to columns that contain confidential information

  B. resource tags for databases that contain confidential information

  C. audit logs sent to a Log Analytics workspace

  D. dynamic data masking for columns that contain confidential information

  Correct Answer: AC

  A: You can classify columns manually, as an alternative or in addition to the recommendation-based classification:

  

  1.

  Select Add classification in the top menu of the pane.

  2.

  In the context window that opens, select the schema, table, and column that you want to classify, and the information type and sensitivity label.

  3.

  Select Add classification at the bottom of the context window.

  C: An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. This field logs the sensitivity classifications (labels) of the data that was returned by a query. Here's an example:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

• Question 288:

  You have an Azure Synapse Analytics dedicated SQL pool.

  You need to ensure that data in the pool is encrypted at rest. The solution must NOT require modifying applications that query the data.

  What should you do?

  A. Enable encryption at rest for the Azure Data Lake Storage Gen2 account.

  B. Enable Transparent Data Encryption (TDE) for the pool.

  C. Use a customer-managed key to enable double encryption for the Azure Synapse workspace.

  D. Create an Azure key vault in the Azure subscription grant access to the pool.

  Correct Answer: B

  Transparent Data Encryption (TDE) helps protect against the threat of malicious activity by encrypting and decrypting your data at rest. When you encrypt your database, associated backups and transaction log files are encrypted without requiring any changes to your applications. TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-manage-security

• Question 289:

  You have an Azure data factory.

  You need to examine the pipeline failures from the last 60 days.

  What should you use?

  A. the Activity log blade for the Data Factory resource

  B. the Monitor and Manage app in Data Factory

  C. the Resource health blade for the Data Factory resource

  D. Azure Monitor

  Correct Answer: D

  Data Factory stores pipeline-run data for only 45 days. Use Azure Monitor if you want to keep that data for a longer time.

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor

• Question 290:

  You are monitoring an Azure Stream Analytics job.

  The Backlogged Input Events count has been 20 for the last hour.

  You need to reduce the Backlogged Input Events count.

  What should you do?

  A. Drop late arriving events from the job.

  B. Add an Azure Storage account to the job.

  C. Increase the streaming units for the job.

  D. Stop the job.

  Correct Answer: C

  General symptoms of the job hitting system resource limits include:

  If the backlog event metric keeps increasing, it's an indicator that the system resource is constrained (either because of output sink throttling, or high CPU).

  Note: Backlogged Input Events: Number of input events that are backlogged. A non-zero value for this metric implies that your job isn't able to keep up with the number of incoming events. If this value is slowly increasing or consistently nonzero, you should scale out your job: adjust Streaming Units.

  Reference:

  https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-scale-jobs

  https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-monitoring