DP-203 practice questions 1–10

1. Question 1 · 370 in bank

   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?

   • A[ManagerEmployeeID] [smallint] NULL
   • B[ManagerEmployeeKey] [smallint] NULL
   • C[ManagerEmployeeKey] [int] NULL
   • D[ManagerName] [varchar](200) NULL

   Show answer

   Answer: C

   Explanation

   Answer C. Smallint eliminates A and B. But I would name the field [ManagerEmployeeID] [int] NULL since it should reference EmployeeID, not EmployeeKey since this one is IDENTITY.

   Source: Adapted from community discussion — jskibick

   References

   • Microsoft Learn

   Next question

2. Question 2 · 370 in bank

   HOTSPOT - You have an Azure Data Lake Storage Gen2 container. Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files. You need to design a data archiving solution that meets the following requirements: ✑ New data is accessed frequently and must be available as quickly as possible. ✑ Data that is older than five years is accessed infrequently but must be available within one second when requested. ✑ Data that is older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible. ✑ Costs must be minimized while maintaining the required availability. How should you manage the data? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Move to cool storage - Box 2: Move to archive storage - Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours. The following table shows a comparison of premium performance block blob storage, and the hot, cool, and archive access tiers. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

   Source: Adapted from community discussion — yobllip

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

3. Question 3 · 370 in bank

   Select 3

   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.

   • A[EffectiveStartDate] [datetime] NOT NULL
   • B[EffectiveEndDate] [datetime] NOT NULL
   • C[OriginalProductDescription] NVARCHAR(2000) NOT NULL
   • D[IsCurrentRow] [bit] NOT NULL
   • E[OriginalColor] NVARCHAR(50) NOT NULL
   • F[OriginalProductName] NVARCHAR(100) NULL

   Show answer

   Answer: CEF

   Explanation

   You dont need start and end date for a type 3 scd In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. CEF is correct

   Source: Adapted from community discussion — Kapello10

   Next question

4. Question 4 · 370 in bank

   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.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Correct, dedicated SQL pool divides partitions into 60 databases and should aim for at least 1M rows per distribution (although not mentioned in the question, clustered columnstore compression becomes efficient at this scale). Therefore, partitioning by day would result in too small partitions. Having partitioned by months, we can use switch to move it using metadata operations only, which makes the operation extremely efficient.

   Source: Adapted from community discussion — jongert

   

   Next question

5. Question 5 · 370 in bank

   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?

   • Ain a file in a data lake
   • Bin a relational database
   • Cin a global temporary table
   • Din a session temporary table

   Show answer

   Answer: A

   Explanation

   Correct: Serverless SQL pools can query, import, and store data from Azure Blob Storage, Azure Data Lake Storage Gen1 and Gen2. Serverless does not support TYPE=Hadoop. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&tabs=dedicated#overview-azure-synapse-analytics

   Source: Adapted from community discussion — jongert

   References

   • Microsoft Learn

   Next question

6. Question 6 · 370 in bank

   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?

   • Apartition style
   • Bbusiness area
   • Csize
   • Dfacts and dimensions

   Show answer

   Answer: B

   Explanation

   Azure Synapse database templates are organized by "business areas," which are broader groupings that include various tables related to specific aspects of a business or industry. While "facts and dimensions" are a part of the overall data modeling process within these business areas, the question specifically asks about the grouping method used in the template tables. Therefore, the grouping method for adding additional tables to an Azure Synapse database template would align with the business areas defined within the template, making option B, "business area," the most appropriate choice given the context of the question and the structure of the Synapse database templates.

   Source: Adapted from community discussion — vernillen

   References

   • Microsoft Learn

   Next question

7. Question 7 · 370 in bank

   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?

   • APublish the data factory.
   • BCreate a pull request to merge the changes into the main branch.
   • CCreate a pull request to merge the changes into the abc branch.
   • DPush the code to a remote origin.

   Show answer

   Answer: B

   Explanation

   Correct, simply best practices for version control. Each feature branch develops changes, then should create pull requests to merge with main branch before publishing.

   Source: Adapted from community discussion — jongert

   Next question

8. Question 8 · 370 in bank

   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?

   • AYes
   • BNo

   Show answer

   Answer: A

   Explanation

   Partial saves: "Whether your pipelines are not finished or you simply don't want to lose changes if your computer crashes, git integration allows for incremental changes of data factory resources regardless of what state they are in." https://learn.microsoft.com/en-us/azure/data-factory/source-control

   Source: Adapted from community discussion — lola_mary5

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

9. Question 9 · 370 in bank

   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?

   • AYes
   • BNo

   Show answer

   Answer: A

   Explanation

   Anwser should be "Yes", and because of the phrase: "You need to ensure that you can save the logic of the pipeline.". This means you have to save the logic of the pipeline, and not the pipeline itself. This won't, however, resolve the issues and errors, but it will provide you with a back-up of your work so far.

   Source: Adapted from community discussion — vernillen

   Next question

10. Question 10 · 370 in bank

    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?

    • AYes
    • BNo

    Show answer

    Answer: B

    Explanation

    This has to be B. If you have experience working with Azure before you will know that it is impossible to export anything as an ARM template with validation errors. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-invalid-template?tabs=bicep Scroll down to "Solution 3".

    Source: Adapted from community discussion — [Removed]

    References

    • Microsoft Learn
    • Microsoft Learn
    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-203 practice, question 1. 370 questions available.