DP-300 practice questions 11–20

1. Question 11 · 354 in bank

   DRAG DROP - You are creating a managed data warehouse solution on Microsoft Azure. You must use PolyBase to retrieve data from Azure Blob storage that resides in parquet format and load the data into a large table called FactSalesOrderDetails. You need to configure Azure Synapse Analytics to receive the 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. Select and Place:

   

   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

   To query the data in your Hadoop data source, you must define an external table to use in Transact-SQL queries. The following steps describe how to configure the external table. Step 1: Create a master key on database. 1. Create a master key on the database. The master key is required to encrypt the credential secret. (Create a database scoped credential for Azure blob storage.) Step 2: Create an external data source for Azure Blob storage. 2. Create an external data source with CREATE EXTERNAL DATA SOURCE.. Step 3: Create an external file format to map the parquet files. 3. Create an external file format with CREATE EXTERNAL FILE FORMAT. Step 4. Create an external table FactSalesOrderDetails 4. Create an external table pointing to data stored in Azure storage with CREATE EXTERNAL TABLE. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-azure-blob-storage

   

   Next question

2. Question 12 · 354 in bank

   DRAG DROP - You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB database. A recent failure causes the master database to be lost. You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run on the virtual machine. Which four actions should you perform in sequence to resolve the issue? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct. Select and Place:

   

   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

   Step 1: Attach the SSISDB database Step 2: Turn on the TRUSTWORTHY property and the CLR property If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) Step 3: Open the master key for the SSISDB database Restore the master key by this method if you have the original password that was used to create SSISDB. open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB' Alter Master Key Add encryption by Service Master Key Step 4: Encrypt a copy of the master key by using the service master key Reference: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog

   Source: Adapted from community discussion — bakamon

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

3. Question 13 · 354 in bank

   HOTSPOT - You configure version control for an Azure Data Factory instance as 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 graphic. 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: adf_publish - By default, data factory generates the Resource Manager templates of the published factory and saves them into a branch called adf_publish. To configure a custom publish branch, add a publish_config.json file to the root folder in the collaboration branch. When publishing, ADF reads this file, looks for the field publishBranch, and saves all Resource Manager templates to the specified location. If the branch doesn't exist, data factory will automatically create it. And example of what this file looks like is below: { "publishBranch": "factory/adf_publish" } Box 2: /dwh_barchlet/ adf_publish/contososales RepositoryName: Your Azure Repos code repository name. Azure Repos projects contain Git repositories to manage your source code as your project grows. You can create a new repository or use an existing repository that's already in your project. Reference: https://docs.microsoft.com/en-us/azure/data-factory/source-control

   

   Next question

4. Question 14 · 354 in bank

   You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval. The output will be sent to a Delta Lake table. Which output mode should you use?

   • Acomplete
   • Bappend
   • Cupdate

   Show answer

   Answer: B

   Explanation

   complete - You can also use Structured Streaming to replace the entire table with every batch. One example use case is to compute a summary using aggregation https://docs.databricks.com/delta/delta-streaming.html

   Source: Adapted from community discussion — ramelas

   References

   • Databricks documentation
   • Microsoft Learn

   Next question

5. Question 15 · 354 in bank

   HOTSPOT - You are performing exploratory analysis of bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool. You execute the Transact-SQL query 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 graphic. 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: CSV files that have file named beginning with "tripdata_2020" Box 2: a header - FIRSTROW = 'first_row' Specifies the number of the first row to load. The default is 1 and indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based. Example: Option firstrow is used to skip the first row in the CSV file that represents header in this case (firstrow=2). select top 10 * from openrowset( bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv', format = 'csv', parser_version = '2.0', firstrow = 2 ) as rows Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file

   Source: Adapted from community discussion — pepix74

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

6. Question 16 · 354 in bank

   You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email. You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of aXXX@XXXX.com instead. What should you do?

   • AFrom the Azure portal, set a mask on the Email column.
   • BFrom the Azure portal, set a sensitivity classification of Confidential for the Email column.
   • CFrom Microsoft SQL Server Management Studio, set an email mask on the Email column.
   • DFrom Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.

   Show answer

   Answer: A

   Explanation

   Yep A You set up a dynamic data masking policy in the Azure portal by selecting the Dynamic Data Masking blade under Security in your SQL Database configuration pane. This feature cannot be set using portal for SQL Managed Instance. For more information, see Dynamic Data Masking. https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

   Source: Adapted from community discussion — jerkyflexoff

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 17 · 354 in bank

   You have an Azure Databricks workspace named workspace1 in the Standard pricing tier. Workspace1 contains an all-purpose cluster named cluster1. You need to reduce the time it takes for cluster1 to start and scale up. The solution must minimize costs. What should you do first?

   • AUpgrade workspace1 to the Premium pricing tier.
   • BConfigure a global init script for workspace1.
   • CCreate a pool in workspace1.
   • DCreate a cluster policy in workspace1.

   Show answer

   Answer: C

   Explanation

   You can use Databricks Pools to Speed up your Data Pipelines and Scale Clusters Quickly. Databricks Pools, a managed cache of virtual machine instances that enables clusters to start and scale 4 times faster. Reference: https://databricks.com/blog/2019/11/11/databricks-pools-speed-up-data-pipelines.html

   Next question

8. Question 18 · 354 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 Synapse Analytics dedicated SQL pool that contains a table named Table1. You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1. You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1. You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1. Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: A

   Explanation

   Yes, Get Metadata can be used to retrieve the DateTime of the files and allow you to use this data. The question is to add it to Table1, not to an external table.

   Source: Adapted from community discussion — CaptainJameson

   Next question

9. Question 19 · 354 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 Synapse Analytics dedicated SQL pool that contains a table named Table1. You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1. You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1. You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1. Solution: You use an Azure Synapse Analytics serverless SQL pool to create an external table that has an additional DateTime column. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Explanation

   No, Get Metadata can be used to retrieve the DateTime of the files and allow you to use this data. The question is to add it to Table1, not to an external table.

   Source: Adapted from community discussion — CaptainJameson

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

10. Question 20 · 354 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 Synapse Analytics dedicated SQL pool that contains a table named Table1. You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1. You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1. You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1. Solution: You use a dedicated SQL pool to create an external table that has an additional DateTime column. Does this meet the goal?

    • AYes
    • BNo

    Show answer

    Answer: B

    Explanation

    Instead, in an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files. Note: You can use the Get Metadata activity to retrieve the metadata of any data in Azure Data Factory or a Synapse pipeline. You can use the output from the Get Metadata activity in conditional expressions to perform validation, or consume the metadata in subsequent activities. Reference: https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

    References

    • Microsoft Learn

    Next question

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

DP-300 practice, question 11. 354 questions available.