DP-203 practice questions 141–150

1. Question 141 · 370 in bank

   HOTSPOT - The following code segment is used to create an Azure Databricks cluster.

   

   For each of the following statements, select Yes if the statement is true. Otherwise, select No. 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: Yes - A cluster mode of 'High Concurrency' is selected, unlike all the others which are 'Standard'. This results in a worker type of Standard_DS13_v2. Box 2: No - When you run a job on a new cluster, the job is treated as a data engineering (job) workload subject to the job workload pricing. When you run a job on an existing cluster, the job is treated as a data analytics (all-purpose) workload subject to all-purpose workload pricing. Box 3: Yes - Delta Lake on Databricks allows you to configure Delta Lake based on your workload patterns. Reference: https://adatis.co.uk/databricks-cluster-sizing/ https://docs.microsoft.com/en-us/azure/databricks/jobs https://docs.databricks.com/administration-guide/capacity-planning/cmbp.html https://docs.databricks.com/delta/index.html

   Source: Adapted from community discussion — GameLift

   References

   • Microsoft Learn
   • Databricks documentation
   • Databricks documentation
   • Databricks documentation
   • Microsoft Learn

   

   Next question

2. Question 142 · 370 in bank

   You are building a data flow in Azure Data Factory that upserts data into a table in an Azure Synapse Analytics dedicated SQL pool. You need to add a transformation to the data flow. The transformation must specify logic indicating when a row from the input data must be upserted into the sink. Which type of transformation should you add to the data flow?

   • Ajoin
   • Balter row
   • Csurrogate key
   • Dselect

   Show answer

   Answer: B

   Explanation

   correct, chatGPT: For upsert operations (insert or update), you typically need to determine whether a record already exists in the destination table based on some condition. In Azure Data Factory's data flow, you would use the "Alter Row" transformation for this purpose.

   Source: Adapted from community discussion — warre

   References

   • Microsoft Learn

   Next question

3. Question 143 · 370 in bank

   You have an on-premises database named db1 and a set-hosted integration runtime. You have an Azure subscription that contains an Azure Data Lake Storage account named dl1. You need to develop four data pipeline projects that will use Microsoft Power Query to copy data from db1 to dl1. The solution must meet the following requirements: • All pipelines must use the self-hosted integration runtime. • Each project must be stored in a separate Git repository. • Development effort must be minimized. What should you use?

   • AAzure Synapse Analytics
   • BAzure Logic Apps.
   • CAzure Data Factory
   • DMicrosoft Power BI

   Show answer

   Answer: C

   Explanation

   To develop data pipeline projects that use Microsoft Power Query and meet the specified requirements, you should use Azure Data Factory (option C). Azure Data Factory is a cloud-based data integration service that allows you to create, schedule, and manage data pipelines that can move data between supported on-premises and cloud-based data stores. With Azure Data Factory, you can use Power Query to transform and prepare your data before moving it to the destination. It supports the use of self-hosted integration runtimes for on-premises data movement.

   Source: Adapted from community discussion — Happynewyear1001

   Next question

4. Question 144 · 370 in bank

   You have the Azure Synapse Analytics pipeline shown in the following exhibit.

   

   You need to add a set variable activity to the pipeline to ensure that after the pipeline’s completion, the status of the pipeline is always successful. What should you configure for the set variable activity?

   • Aa skipped dependency on the Upon Failure activity
   • Ba skipped dependency on the Upon Success activity
   • Ca success dependency on the Business Activity That Fails activity
   • Da failure dependency on the Upon Failure activity

   Show answer

   Answer: B

   Explanation

   In this approach, customer defines the business logic, and defines both the Upon Failure path, and Upon Success path, with a dummy Upon Skipped activity attached. This approach renders pipeline succeeds, if Upon Failure path succeeds.

   Source: Adapted from community discussion — Azure_2023

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

5. Question 145 · 370 in bank

   You have an on-premises Linux server that contains a database named DB1. You have an Azure subscription that contains an Azure data factory named ADF1 and an Azure Data Lake Storage account named ADLS1. You need to create a pipeline in ADF1 that will copy data from DB1 to ADLS1. Which type of integration runtime should you use to read the data from DB1?

   • Aself-hosted integration runtime
   • BAzure integration runtime
   • CAzure-SQL Server Integration Services (SSIS)

   Show answer

   Answer: A

   Explanation

   Although the self-hosted IR runs on Windows, it can connect to databases hosted on Linux servers. This is possible because the self-hosted IR uses database connectors and drivers that support remote connections to databases on different operating systems.

   Source: Adapted from community discussion — Swayansu

   Next question

6. Question 146 · 370 in bank

   DRAG DROP - You have an Azure Data Lake Storage account named account1. You use an Azure Synapse Analytics serverless SQL pool to access sales data stored in account1. You need to create a bar chart that displays sales by product. The solution must minimize development effort. In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

   

   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

   

   Next question

7. Question 147 · 370 in bank

   DRAG DROP - You have an Azure Synapse Analytics dedicated SQL pool. You need to create a copy of the data warehouse and make the copy available for 28 days. The solution must minimize costs. Which three 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.

   

   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. In case you're looking for a Long-Term Backup (LTR) concept: Create a new user-defined restore point, or you can use one of the automatically generated restore points. Restore from the newly created restore point to a new data warehouse. After you have restored, you have the dedicated SQL pool online. Pause it indefinitely to save compute costs. The paused database incurs storage charges at the Azure Synapse storage rate. If you need an active copy of the restored data warehouse, you can resume, which should take only a few minutes. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/backup-and-restore?context=%2Fazure%2Fsynapse-analytics%2Fcontext%2Fcontext

   Source: Adapted from community discussion — Azure_2023

   References

   • Microsoft Learn

   

   Next question

8. Question 148 · 370 in bank

   HOTSPOT - You have an Azure Synapse Analytics workspace that contains an Apache Spark pool named Pool1. You need to read data from a CSV file and write the data to a Delta table by using Pool1. How should you complete the PySpark code? 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 answers are correct. Besides the link from @GermanGerman, check the databricks syntax here: https://docs.databricks.com/en/delta/tutorial.html The page has only "forName". For the question, the similar "forPath" is the choice.

   Source: Adapted from community discussion — renan_ineu

   References

   • Databricks documentation
   • Microsoft Learn

   

   Next question

9. Question 149 · 370 in bank

   HOTSPOT - You have an Azure Data Lake Storage account that contains one CSV file per hour for January 1, 2020, through January 31, 2023. The files are partitioned by using the following folder structure.

   

   You need to query the files by using an Azure Synapse Analytics serverless SQL pool. The solution must return the row count of each file created during the last three months of 2022. How should you complete the query? To answer, select the appropriate options in the answer 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

   Answers are correct: As per next example, parameter (1) refers to year, and paremeter (2) to month. SELECT r.filepath() AS filepath ,r.filepath(1) AS [year] ,r.filepath(2) AS [month] ,COUNT_BIG(*) AS [rows] FROM OPENROWSET( BULK 'csv/taxi/yellow_tripdata_*-*.csv', DATA_SOURCE = 'SqlOnDemandDemo', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIRSTROW = 2 ) WITH ( vendor_id INT ) AS [r] WHERE r.filepath(1) IN ('2017') AND r.filepath(2) IN ('10', '11', '12') GROUP BY r.filepath() ,r.filepath(1) ,r.filepath(2) ORDER BY filepath;

   Source: Adapted from community discussion — [Removed]

   References

   • Microsoft Learn

   

   Next question

10. Question 150 · 370 in bank

    HOTSPOT - You have an Azure Synapse Analytics dedicated SQL pool named Pool1 that contains an external table named Sales. Sales contains sales data. Each row in Sales contain data on a single sale, including the name of the salesperson. You need to implement row-level security (RLS). The solution must ensure that the salespeople can access only their respective sales. What should you do? 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

    References

    • Microsoft Learn

    

    Next question

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

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