DP-203 practice questions 121–130

1. Question 121 · 370 in bank

   DRAG DROP - You are batch loading a table in an Azure Synapse Analytics dedicated SQL pool. You need to load data from a staging table to the target table. The solution must ensure that if an error occurs while loading the data to the target table, all the inserts in that batch are undone. How should you complete the Transact-SQL code? 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

   Given answer is wrong. It should be BEGIN TRAN as SQL pool in Azure Synapse Analytics does not support distributed transaction. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-transactions "Limitations SQL pool does have a few other restrictions that relate to transactions. They are as follows: No distributed transactions No nested transactions permitted No save points allowed No named transactions No marked transactions No support for DDL such as CREATE TABLE inside a user-defined transaction " Distributed Transactions are only allowed in SQL Server and Azure SQL Managed Instance: https://learn.microsoft.com/de-de/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql?view=sql-server-ver16

   Source: Adapted from community discussion — [Removed]

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

2. Question 122 · 370 in bank

   HOTSPOT - You have two Azure SQL databases named DB1 and DB2. DB1 contains a table named Table1. Table1 contains a timestamp column named LastModifiedOn. LastModifiedOn contains the timestamp of the most recent update for each individual row. DB2 contains a table named Watermark. Watermark contains a single timestamp column named WatermarkValue. You plan to create an Azure Data Factory pipeline that will incrementally upload into Azure Blob Storage all the rows in Table1 for which the LastModifiedOn column contains a timestamp newer than the most recent value of the WatermarkValue column in Watermark. You need to identify which activities to include in the pipeline. The solution must meet the following requirements: • Minimize the effort to author the pipeline. • Ensure that the number of data integration units allocated to the upload operation can be controlled. What should you identify? To answer, select the appropriate options in the answer area. NOTE: Each correct answer 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

   Create a pipeline with the following workflow: The pipeline in this solution has the following activities: Create two Lookup activities. Use the first Lookup activity to retrieve the last watermark value. Use the second Lookup activity to retrieve the new watermark value. These watermark values are passed to the Copy activity. Create a Copy activity that copies rows from the source data store with the value of the watermark column greater than the old watermark value and less than the new watermark value. Then, it copies the delta data from the source data store to Blob storage as a new file. Create a StoredProcedure activity that updates the watermark value for the pipeline that runs next time.

   Source: Adapted from community discussion — CezarioAbrantesPP

   References

   • Microsoft Learn

   

   Next question

3. Question 123 · 370 in bank

   HOTSPOT - You have an Azure Synapse serverless SQL pool. You need to read JSON documents from a file by using the OPENROWSET function. How should you complete the query? 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. It's weird but best way to open a json is as a csv and with 0x0b for fieldterminator and fieldquote. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

   Source: Adapted from community discussion — Yemeral

   References

   • Microsoft Learn

   

   Next question

4. Question 124 · 370 in bank

   Select 2

   You use Azure Data Factory to create data pipelines. You are evaluating whether to integrate Data Factory and GitHub for source and version control. What are two advantages of the integration? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

   • Aadditional triggers
   • Blower pipeline execution times
   • Cthe ability to save without publishing
   • Dthe ability to save pipelines that have validation issues

   Show answer

   Answer: CD

   Explanation

   C. the ability to save without publishing D. the ability to save pipelines that have validation issues When you integrate Data Factory and GitHub, you can save your pipelines to a GitHub repository without publishing them to Azure. This allows you to work on your pipelines in a development environment and then publish them to Azure when you are ready. You can also save pipelines that have validation issues. This is because GitHub does not validate your pipelines when you save them. This allows you to work on your pipelines and fix the validation issues before you publish them to Azure.

   Source: Adapted from community discussion — akk_1289

   Next question

5. Question 125 · 370 in bank

   DRAG DROP - You have an Azure Synapse Analytics workspace named Workspace1. You perform the following changes: • Implement source control for Workspace1. • Create a branch named Feature based on the collaboration branch. • Switch to the Feature branch. • Modify Workspace1. You need to publish the changes to Azure Synapse. From which branch should you perform each change? 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

   

   Next question

6. Question 126 · 370 in bank

   You have two Azure Blob Storage accounts named account1 and account2. You plan to create an Azure Data Factory pipeline that will use scheduled intervals to replicate newly created or modified blobs from account1 to account2. You need to recommend a solution to implement the pipeline. The solution must meet the following requirements: • Ensure that the pipeline only copies blobs that were created or modified since the most recent replication event. • Minimize the effort to create the pipeline. What should you recommend?

   • ARun the Copy Data tool and select Metadata-driven copy task.
   • BCreate a pipeline that contains a Data Flow activity.
   • CCreate a pipeline that contains a flowlet.
   • DRun the Copy Data tool and select Built-in copy task.

   Show answer

   Answer: D

   Explanation

   "[...] use the Copy Data tool to create a pipeline that incrementally copies new and changed files only, from Azure Blob storage to Azure Blob storage. It uses LastModifiedDate to determine which files to copy." https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-lastmodified-copy-data-tool

   Source: Adapted from community discussion — vctrhugo

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 127 · 370 in bank

   You have an Azure Data Factory pipeline named pipeline1 that contains a data flow activity named activity1. You need to run pipeline1. Which runtime will be used to run activity1?

   • AAzure Integration runtime
   • BSelf-hosted integration runtime
   • CSSIS integration runtime

   Show answer

   Answer: A

   References

   • Microsoft Learn

   Next question

8. Question 128 · 370 in bank

   HOTSPOT - You have an Azure subscription that contains an Azure Synapse Analytics workspace named workspace1. Workspace1 contains a dedicated SQL pool named SQLPool1 and an Apache Spark pool named sparkpool1. Sparkpool1 contains a DataFrame named pyspark_df. You need to write the contents of pyspark_df to a table in SQLPool1 by using a PySpark notebook. How should you complete the 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

   Correct, also according to this link https://microsoftlearning.github.io/DP-203-Data-Engineer/Instructions/Labs/LAB_04_data_warehouse_using_apache_spark.html ******* %%spark // Make sure the name of the dedcated SQL pool (SQLPool01 below) matches the name of your SQL pool. val df = spark.sqlContext.sql("select * from top_purchases") df.write.synapsesql("SQLPool01.wwi.TopPurchases", Constants.INTERNAL)

   Source: Adapted from community discussion — alegiordx

   References

   • Microsoft Learn

   

   Next question

9. Question 129 · 370 in bank

   You have an Azure data factory named ADF1 and an Azure Synapse Analytics workspace that contains a pipeline named SynPipeLine1. SynPipeLine1 includes a Notebook activity. You create a pipeline in ADF1 named ADFPipeline1. You need to invoke SynPipeLine1 from ADFPipeline1. Which type of activity should you use?

   • AWeb
   • BSpark
   • CCustom
   • DNotebook

   Show answer

   Answer: A

   Explanation

   To invoke a Synapse Analytics pipeline from an Azure Data Factory pipeline, you should use the "Web" activity. This activity is specifically designed for making HTTP requests and can be used to trigger the execution of pipelines in other services, such as Azure Synapse Analytics.

   Source: Adapted from community discussion — Happynewyear1001

   References

   • Microsoft Learn

   Next question

10. Question 130 · 370 in bank

    You have an Azure Data Factory that contains 10 pipelines. You need to label each pipeline with its main purpose of either ingest, transform, or load. The labels must be available for grouping and filtering when using the monitoring experience in Data Factory. What should you add to each pipeline?

    • Aa resource tag
    • Ba correlation ID
    • Ca run group ID
    • Dan annotation

    Show answer

    Answer: D

    Explanation

    ADF annotations are tags that you can add to your Azure Data Factory components to identify them. A tag allows you to classify or group different objects in order to easily monitor them after an execution. You can create multiple Azure Data Factory annotations.

    Source: Adapted from community discussion — vigilante89

    Next question

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

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