Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 251:

  You have an Azure Synapse Analystics dedicated SQL pool that contains a table named Contacts. Contacts contains a column named Phone. You need to ensure that users in a specific role only see the last four digits of a phone number when querying the Phone column.

  What should you include in the solution?

  A. a default value

  B. dynamic data masking

  C. row-level security (RLS)

  D. column encryption

  E. table partitions

  Correct Answer: B

  Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It's a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

• Question 252:

  You are designing a dimension table for a data warehouse. The table will track the value of the dimension attributes over time and preserve the history of the data by adding new rows as the data changes. Which type of slowly changing dimension (SCD) should use?

  A. Type 0

  B. Type 1

  C. Type 2

  D. Type 3

  Correct Answer: C

  Type 2 - Creating a new additional record. In this methodology all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and

  new rows contain as attributes the natural key(or other durable identifier). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with current indicator set to 'Y'. For 'effective date' columns,

  i.e. start_date and end_date, the end_date

  for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added

  in the future.

  https://www.datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

• Question 253:

  You have an Azure Synapse Analytics serverless SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named storage1. The AllowedBlobpublicAccess porperty is disabled for storage1.

  You need to create an external data source that can be used by Azure Active Directory (Azure AD) users to access storage1 from Pool1.

  What should you create first?

  A. an external resource pool

  B. a remote service binding

  C. database scoped credentials

  D. an external library

  Correct Answer: C

  Security

  User must have SELECT permission on an external table to read the data. External tables access underlying Azure storage using the database scoped credential defined in data source.

  Note: A database scoped credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql

• Question 254:

  You use Azure Data Lake Storage Gen2.

  You need to ensure that workloads can use filter predicates and column projections to filter data at the time the data is read from disk.

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

  NOTE: Each correct selection is worth one point.

  A. Reregister the Azure Storage resource provider.

  B. Create a storage policy that is scoped to a container.

  C. Reregister the Microsoft Data Lake Store resource provider.

  D. Create a storage policy that is scoped to a container prefix filter.

  E. Register the query acceleration feature.

  Correct Answer: AE

  Prerequisites

  To access Azure Storage, you'll need an Azure subscription. If you don't already have a subscription, create a free account before you begin.

  A general-purpose v2 storage account.

  Query acceleration accepts filtering predicates and column projections which enable applications to filter rows and columns at the time that data is read from disk. Only the data that meets the conditions of a predicate are transferred over the network to the application. This reduces network latency and compute cost.

  Note: Query acceleration enables applications and analytics frameworks to dramatically optimize data processing by retrieving only the data that they require to perform a given operation. This reduces the time and processing power that is required to gain critical insights into stored data.

  Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-query-acceleration-how-to

• Question 255:

  You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications:

  1.

  Contain sales data for 20,000 products.

  2.

  Use hash distribution on a column named ProduclID,

  3.

  Contain 2.4 billion records for the years 20l9 and 2020.

  Which number of partition ranges provides optimal compression and performance of the clustered columnstore index?

  A. 40

  B. 240

  C. 400

  D. 2400

  Correct Answer: A

  Each partition should have around 1 millions records. Dedication SQL pools already have 60 partitions.

  We have the formula: Records/(Partitions*60)= 1 million Partitions= Records/(1 million * 60)

  Partitions= 2.4 x 1,000,000,000/(1,000,000 * 60) = 40 Note: Having too many partitions can reduce the effectiveness of clustered columnstore indexes if each partition has fewer than 1 million rows. Dedicated SQL pools automatically partition

  your data into 60 databases. So, if you create a table with 100 partitions, the result will be 6000 partitions.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool

• Question 256:

  You have an Azure Factory instance named DF1 that contains a pipeline named PL1.PL1 includes a tumbling window trigger.

  You create five clones of PL1. You configure each clone pipeline to use a different data source.

  You need to ensure that the execution schedules of the clone pipeline match the execution schedule of PL1.

  What should you do?

  A. Add a new trigger to each cloned pipeline

  B. Associate each cloned pipeline to an existing trigger.

  C. Create a tumbling window trigger dependency for the trigger of PL1.

  D. Modify the Concurrency setting of each pipeline.

  Correct Answer: B

• Question 257:

  You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1. You need to verify whether the size of the transaction log file for each distribution of DW1 is smaller than 160 GB. What should you do?

  A. On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.

  B. From Azure Monitor in the Azure portal, execute a query against the logs of DW1.

  C. On DW1, execute a query against the sys.database_files dynamic management view.

  D. Execute a query against the logs of DW1 by using the Get-AzOperationalInsightSearchResult PowerShell cmdlet.

  Correct Answer: A

  The following query returns the transaction log size on each distribution. If one of the log files is reaching 160 GB, you should consider scaling up your instance or limiting your transaction size.

  -- Transaction log size SELECT instance_name as distribution_db, cntr_value*1.0/1048576 as log_file_size_used_GB,pdw_node_id FROM sys.dm_pdw_nodes_os_performance_counters WHERE instance_name like 'Distribution_%' AND counter_name = 'Log File(s) Used Size (KB)'

  References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-managemonitor

• Question 258:

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure Storage account that contains 100 GB of files. The files contain rows of text and numerical values. 75% of the rows contain description data that has an average length of 1.1 MB.

  You plan to copy the data from the storage account to an enterprise data warehouse in Azure Synapse Analytics.

  You need to prepare the files to ensure that the data copies quickly.

  Solution: You copy the files to a table that has a columnstore index.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead convert the files to compressed delimited text files.

  Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data

• Question 259:

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure Storage account that contains 100 GB of files. The files contain rows of text and numerical values. 75% of the rows contain description data that has an average length of 1.1 MB.

  You plan to copy the data from the storage account to an enterprise data warehouse in Azure Synapse Analytics.

  You need to prepare the files to ensure that the data copies quickly.

  Solution: You modify the files to ensure that each row is more than 1 MB.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead convert the files to compressed delimited text files.

  Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data

• Question 260:

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have an Azure Storage account that contains 100 GB of files. The files contain rows of text and numerical values. 75% of the rows contain description data that has an average length of 1.1 MB.

  You plan to copy the data from the storage account to an enterprise data warehouse in Azure Synapse Analytics.

  You need to prepare the files to ensure that the data copies quickly.

  Solution: You convert the files to compressed delimited text files.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  All file formats have different performance characteristics. For the fastest load, use compressed delimited text files.

  Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data