DP-203 Practice Test

Question 71

You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a table named table1. You load 5 TB of data into table1. You need to ensure that columnstore compression is maximized for table1. Which statement should you execute?

1. DBCC INDEXDEFRAG (pool1, table1)
2. DBCC DBREINDEX (table1)
3. ALTER INDEX ALL on table1 REORGANIZE
4. ALTER INDEX ALL on table1 REBUILD

Question 72

You have an Azure Synapse Analytics dedicated SQL pool named pool1. You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code. You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD). Which two columns should you add? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

1. [HistoricalSalesPerson] [nvarchar] (256) NOT NULL
2. [EffectiveEndDate] [datetime] NOT NULL
3. [PreviousModifiedDate] [datetime] NOT NULL
4. [RowID] [bigint] NOT NULL
5. [EffectiveStartDate] [datetime] NOT NULL

Question 73

You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool. You plan to deploy a solution that will analyze sales data and include the following: • A table named Country that will contain 195 rows • A table named Sales that will contain 100 million rows • A query to identify total sales by country and customer from the past 30 days You need to create the tables. The solution must maximize query performance. How should you complete the script? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Question 74

You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named account1 and an Azure Synapse Analytics workspace named workspace1. You need to create an external table in a serverless SQL pool in workspace1. The external table will reference CSV files stored in account1. The solution must maximize performance. How should you configure the external table?

1. Use a native external table and authenticate by using a shared access signature (SAS).
2. Use a native external table and authenticate by using a storage account key.
3. Use an Apache Hadoop external table and authenticate by using a shared access signature (SAS).
4. Use an Apache Hadoop external table and authenticate by using a service principal in Microsoft Azure Active Directory (Azure AD), part of Microsoft Entra.

Question 75

You have an Azure Synapse Analytics serverless SQL pool that contains a database named db1. The data model for db1 is shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the exhibit. NOTE: Each correct selection is worth one point.