DP-203 practice test

1. Question 1 · 1

   You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement. You need to alter the table to meet the following requirements: ✑ Ensure that users can identify the current manager of employees. ✑ Support creating an employee reporting hierarchy for your entire company. ✑ Provide fast lookup of the managers' attributes such as name and job title. Which column should you add to the table?

   Choose one answer.

   

   • [ManagerEmployeeID] [smallint] NULL
   • [ManagerEmployeeKey] [smallint] NULL
   • [ManagerEmployeeKey] [int] NULL
   • [ManagerName] [varchar](200) NULL

2. Question 2 · 1

   You are implementing a star schema in an Azure Synapse Analytics dedicated SQL pool. You plan to create a table named DimProduct. DimProduct must be a Type 3 slowly changing dimension (SCD) table that meets the following requirements: • The values in two columns named ProductKey and ProductSourceID will remain the same. • The values in three columns named ProductName, ProductDescription, and Color can change. You need to add additional columns to complete the following table definition. Which three columns should you add? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   Choose all answers that apply.

   

   • [EffectiveStartDate] [datetime] NOT NULL
   • [EffectiveEndDate] [datetime] NOT NULL
   • [OriginalProductDescription] NVARCHAR(2000) NOT NULL
   • [IsCurrentRow] [bit] NOT NULL
   • [OriginalColor] NVARCHAR(50) NOT NULL
   • [OriginalProductName] NVARCHAR(100) NULL

3. Question 3 · 1

   HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a fact table named Table1. Table1 contains sales data. Sixty-five million rows of data are added to Table1 monthly. At the end of each month, you need to remove data that is older than 36 months. The solution must minimize how long it takes to remove the data. How should you partition Table1, and how should you remove the old data? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   Choose an option for each prompt.

   

   Partition the data:

   • • Partition by date with one partition per day.
   • • Partition by date with one partition per month.
   • • Partition by product.

   Remove the data:

   • • Delete the old data from Table1 by using a WHERE clause.
   • • Delete the old data from Table1 by using a JOIN.
   • • Switch the oldest partition to another table named Table2 and drop Table2.
   • • Truncate the oldest partition.

4. Question 4 · 1

   You have an Azure subscription that contains an Azure Synapse Analytics serverless SQL pool. You execute the following query. Where will the rows returned by the query be stored?

   Choose one answer.

   

   • in a file in a data lake
   • in a relational database
   • in a global temporary table
   • in a session temporary table

5. Question 5 · 1

   You are deploying a lake database by using an Azure Synapse database template. You need to add additional tables to the database. The solution must use the same grouping method as the template tables. Which grouping method should you use?

   Choose one answer.

   • partition style
   • business area
   • size
   • facts and dimensions

6. Question 6 · 1

   You have an Azure data factory connected to a Git repository that contains the following branches: • main: Collaboration branch • abc: Feature branch • xyz: Feature branch You save changes to a pipeline in the xyz branch. You need to publish the changes to the live service. What should you do first?

   Choose one answer.

   • Publish the data factory.
   • Create a pull request to merge the changes into the main branch.
   • Create a pull request to merge the changes into the abc branch.
   • Push the code to a remote origin.

7. Question 7 · 1

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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 subscription that contains an Azure data factory named ADF1. From Azure Data Factory Studio, you build a complex data pipeline in ADF1. You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published. You need to ensure that you can save the logic of the pipeline. Solution: You enable Git integration for ADF1. Does this meet the goal?

   Choose one answer.

   • Yes
   • No

8. Question 8 · 1

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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 subscription that contains an Azure data factory named ADF1. From Azure Data Factory Studio, you build a complex data pipeline in ADF1. You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published. You need to ensure that you can save the logic of the pipeline. Solution: You view the JSON code representation of the resource and copy the JSON to a file. Does this meet the goal?

   Choose one answer.

   • Yes
   • No

9. Question 9 · 1

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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 subscription that contains an Azure data factory named ADF1. From Azure Data Factory Studio, you build a complex data pipeline in ADF1. You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published. You need to ensure that you can save the logic of the pipeline. Solution: You export ADF1 as an Azure Resource Manager (ARM) template. Does this meet the goal?

   Choose one answer.

   • Yes
   • No

10. Question 10 · 1

    HOTSPOT - You have an Azure Databricks workspace. You read data from a CSV file by using a notebook, and then load the data to a DataFrame. You need to add rows from the DataFrame to an existing Delta table by using Python code. How should you complete the code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

    Choose an option for each prompt.

    

    new_rows_df.write.format:

    • • format("csv")
    • • format("delta")
    • • format("json")
    • • format("parquet")

    new_rows_df.write.mode:

    • • mode("append")
    • • mode("error")
    • • mode("ignore")
    • • mode("overwrite")

11. Question 11 · 1

    DRAG DROP - You have an Azure subscription that contains an Azure Cosmos DB for NoSQL account named account1. The account1 account contains a container named Container1 that has the following configurations: • Analytical store: On • TTL: 3600 You need to remove analytical store support from Container1. The solution must meet the following requirements: • Minimize the impact on the apps that reference Container1. • Minimize storage usage. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

    Arrange the options in the correct order.

    

    • Create another container named Container1 and copy the contents of Container2 to Container1.
    • Set the TTL for Container1 to null.
    • Create a new container named Container2 and copy the contents of Container1 to Container2.
    • Set the TTL for Container1 to 0.
    • Delete Container1.
    • Delete Container2.

12. Question 12 · 1

    DRAG DROP - You have an Azure Synapse Analytics dedicated SQL pool named SQL1 that contains a hash-distributed fact table named Table1. You need to recreate Table1 and add a new distribution column. The solution must maximize the availability of data. Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

    Arrange the options in the correct order.

    

    • Create a new table named Table1v2 by running CTAS.
    • Rename Table1v2 as Table1.
    • Rename Table1 as Table1_old.
    • Run DBCC PDW_SHOWSPACEUSED.
    • Drop Table1_old.
    • Drop the indexes of Table1.

13. Question 13 · 1

    Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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 subscription that contains an Azure data factory named ADF1. From Azure Data Factory Studio, you build a complex data pipeline in ADF1. You discover that the Save button is unavailable, and there are validation errors that prevent the pipeline from being published. You need to ensure that you can save the logic of the pipeline. Solution: You disable all the triggers for ADF1. Does this meet the goal?

    Choose one answer.

    • Yes
    • No

14. Question 14 · 1

    You have an Azure subscription that contains the resources shown in the following table. You need to implement Azure Synapse Link for Azure SQL Database. Which two actions should you perform on sql1? Each correct answer presents a part of the solution. NOTE: Each correct selection is worth one point.

    Choose all answers that apply.

    

    • Update the firewall rules to allow Azure services to access sql1.
    • Enable the system-assigned managed identity.
    • From the Access control (IAM) settings, assign the Contributor role to the system-assigned managed identity of workspace1.
    • Disable Transparent Data Encryption (TDE).

15. Question 15 · 1

    You have an Azure subscription that contains an Azure Cosmos DB database. Azure Synapse Link is implemented on the database. You configure a full fidelity schema for the analytical store. You perform the following actions: • Insert {"customerID": 12, "customer": “Tailspin Toys"} as the first document in the container. • Insert {"customerID": "14", "customer": "Contoso"} as the second document in the container. How many columns will the analytical store contain?

    Choose one answer.

    • 1
    • 2
    • 3
    • 4

Paid access to all 283 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.