DP-203 practice questions 91–100

1. Question 91 · 370 in bank

   HOTSPOT - 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.

   

   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 answer should be join DimGeography and DimCustomer and 5 tables. You also need to combine ProductLine and Product in order for the schema to be considered a star schema. This would result in 5 remaining tables: DimCustomer (DimCustomer JOIN DimGeography), DimStore, Date, Product (Product JOIN ProductLine) and FactOrders.

   Source: Adapted from community discussion — blenau

   

   Next question

2. Question 92 · 370 in bank

   You have an Azure Databricks workspace and an Azure Data Lake Storage Gen2 account named storage1. New files are uploaded daily to storage1. You need to recommend a solution that configures storage1 as a structured streaming source. The solution must meet the following requirements: • Incrementally process new files as they are uploaded to storage1. • Minimize implementation and maintenance effort. • Minimize the cost of processing millions of files. • Support schema inference and schema drift. Which should you include in the recommendation?

   • ACOPY INTO
   • BAzure Data Factory
   • CAuto Loader
   • DApache Spark FileStreamSource

   Show answer

   Answer: C

   Explanation

   Auto Loader provides a Structured Streaming source called cloudFiles. Plus, it supports schema drift. Hence, Auto Loader is the correct answer. https://learn.microsoft.com/en-us/azure/databricks/ingestion/auto-loader/

   Source: Adapted from community discussion — Nikiboy

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

3. Question 93 · 370 in bank

   You have an Azure subscription that contains the resources shown in the following table.

   

   You need to read the TSV files by using ad-hoc queries and the OPENROWSET function. The solution must assign a name and override the inferred data type of each column. What should you include in the OPENROWSET function?

   • Athe WITH clause
   • Bthe ROWSET_OPTIONS bulk option
   • Cthe DATAFILETYPE bulk option
   • Dthe DATA_SOURCE parameter

   Show answer

   Answer: A

   Explanation

   In the Question "The solution must assign a name and override the inferred data type of each column", so we must need a WITH Clause to define the column names and data types.

   Source: Adapted from community discussion — henryphchan

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

4. Question 94 · 370 in bank

   You have an Azure Synapse Analytics dedicated SQL pool. You plan to create a fact table named Table1 that will contain a clustered columnstore index. You need to optimize data compression and query performance for Table1. What is the minimum number of rows that Table1 should contain before you create partitions?

   • A100,000
   • B600,000
   • C1 million
   • D60 million

   Show answer

   Answer: D

   Explanation

   Clustered Column Store will by default have 60 partitions. And to achieve best compression we need at least 1 Million rows per partition, hence Option D 60 Millions (1M per partition)

   Source: Adapted from community discussion — Ankit_Az

   References

   • Microsoft Learn

   Next question

5. Question 95 · 370 in bank

   Select 2

   You have an Azure Synapse Analytics dedicated SQL pool that contains a table named DimSalesPerson. DimSalesPerson contains the following columns: • RepSourceID • SalesRepID • FirstName • LastName • StartDate • EndDate • Region You are developing an Azure Synapse Analytics pipeline that includes a mapping data flow named Dataflow1. Dataflow1 will read sales team data from an external source and use a Type 2 slowly changing dimension (SCD) when loading the data into DimSalesPerson. You need to update the last name of a salesperson in DimSalesPerson. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • AUpdate three columns of an existing row.
   • BUpdate two columns of an existing row.
   • CInsert an extra row.
   • DUpdate one column of an existing row.

   Show answer

   Answer: CD

   Explanation

   - Update one column: EndDate to the change date - Insert a new record with the new value of LastName, StartDate as the change date.

   Source: Adapted from community discussion — tankwayep

   Next question

6. Question 96 · 370 in bank

   HOTSPOT - You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools. Files are initially ingested into an Azure Data Lake Storage Gen2 account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your company. You need to move the files to a different folder and transform the data to meet the following requirements: ✑ Provide the fastest possible query times. ✑ Automatically infer the schema from the underlying files. How should you configure the Data Factory copy activity? 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: Preserver hierarchy - Compared to the flat namespace on Blob storage, the hierarchical namespace greatly improves the performance of directory management operations, which improves overall job performance. Box 2: Parquet - Azure Data Factory parquet format is supported for Azure Data Lake Storage Gen2. Parquet supports the schema property. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction https://docs.microsoft.com/en-us/azure/data-factory/format-parquet

   Source: Adapted from community discussion — edba

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

7. Question 97 · 370 in bank

   HOTSPOT - You plan to use an Azure Data Lake Storage Gen2 account to implement a Data Lake development environment that meets the following requirements: • Read and write access to data must be maintained if an availability zone becomes unavailable. • Data that was last modified more than two years ago must be deleted automatically. • Costs must be minimized. What should you configure? 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

   Statement 1: For Storage redundancy, you should select ZRS (Zone-redundant storage). This will maintain read and write access to data even if an availability zone becomes unavailable. Statement 2: For data deletion, you should select A lifecycle management policy. This will allow you to automatically delete data that was last modified more than two years ago

   Source: Adapted from community discussion — bakamon

   References

   • Microsoft Learn

   

   Next question

8. Question 98 · 370 in bank

   HOTSPOT - You are designing an Azure Data Lake Storage Gen2 container to store data for the human resources (HR) department and the operations department at your company. You have the following data access requirements: • After initial processing, the HR department data will be retained for seven years and rarely accessed. • The operations department data will be accessed frequently for the first six months, and then accessed once per month. You need to design a data retention solution to meet the access requirements. The solution must minimize storage costs. What should you include in the storage policy for each department? 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

   The answer for HR depends on the meaning of "rarely" and the duration of "initial processing". If rarely is like once a year and initial processing is complete within 24 h the answer is correct. If rarely is like on a weekly basis, archiv might be the wrong way

   Source: Adapted from community discussion — [Removed]

   

   Next question

9. Question 99 · 370 in bank

   HOTSPOT - You are developing an Azure Synapse Analytics pipeline that will include a mapping data flow named Dataflow1. Dataflow1 will read customer data from an external source and use a Type 1 slowly changing dimension (SCD) when loading the data into a table named DimCustomer in an Azure Synapse Analytics dedicated SQL pool. You need to ensure that Dataflow1 can perform the following tasks: • Detect whether the data of a given customer has changed in the DimCustomer table. • Perform an upsert to the DimCustomer table. Which type of transformation should you use for each task? 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

   The answer is correct. Check "Exercise - Design and implement a Type 1 slowly changing dimension with mapping data flows", there is described implementation of the dataflow mentioned in this question. https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/4-exercise-design-implement-type-1-dimension In the exercise 'Derived column' transformation is used to add InsertedDate and ModifiedDate columns. ModifiedDate column can be used to detect whether the customer data has changed. For Upsert 'Alter row' tranformation is used. The answer is definitely correct.

   Source: Adapted from community discussion — aemilka

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 100 · 370 in bank

    DRAG DROP - You have an Azure Synapse Analytics serverless SQL pool. You have an Azure Data Lake Storage account named adls1 that contains a public container named container1. The container1 container contains a folder named folder1. You need to query the top 100 rows of all the CSV files in folder1. How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. 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

    The provided query is correct for Azure Synapse Analytics serverless SQL pool. It selects the top 100 rows from the data in CSV format located at the specified URL: https://adls1.dfs.core.windows.net/container1/folde1/*.csv. The results are returned under the alias rows. Answer is correct.

    Source: Adapted from community discussion — rocky48

    

    Next question

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

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