Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 101:

  You have an Azure subscription that contains a server named Server1. Server1 hosts two Azure SQL databases named DB1 and DB2.

  You plan to deploy a Windows app named App1 that will authenticate to DB2 by using SQL authentication.

  You need to ensure that App1 can access DB2. The solution must meet the following requirements:

  1.

  App1 must be able to view only DB2.

  2.

  Administrative effort must be minimized. What should you create?

  A. a contained database user for App1 on DB2

  B. a login for App1 on Server1

  C. a contained database user from an external provider for App1 on DB2

  D. a contained database user from a Windows login for App1 on DB2

  Correct Answer: A

• Question 102:

  You create five Azure SQL Database instances on the same logical server.

  In each database, you create a user for an Azure Active Directory (Azure AD) user named User1.

  User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error.

  You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases.

  What should you do?

  A. Create User1 in the master database.

  B. Assign User1 the db_datareader role for the master database.

  C. Assign User1 the db_datareader role for the databases that User1 creates.

  D. Grant SELECTon sys.databases to public in the master database.

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

• Question 103:

  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?

  A. Yes

  B. No

  Correct Answer: A

  In dedicated SQL pools you can only use Parquet native external tables. Native external tables are generally available in serverless SQL pools.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

• Question 104:

  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?

  A. Yes

  B. No

  Correct Answer: B

  Instead use a serverless SQL pool to create an external table with the extra column.

  Note: In dedicated SQL pools you can only use Parquet native external tables. Native external tables are generally available in serverless SQL pools.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

• Question 105:

  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?

  A. Yes

  B. No

  Correct Answer: B

  Instead use a serverless SQL pool to create an external table with the extra column.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

• Question 106:

  You are creating a new notebook in Azure Databricks that will support R as the primary language but will also support Scala and SQL. Which switch should you use to switch between languages?

  A. \\[]

  B. %

  C. \\[]

  D. @

  Correct Answer: B

  You can override the default language by specifying the language magic command % at the beginning of a cell. The supported magic commands are: %python, %r, %scala, and %sql.

  Reference: https://docs.microsoft.com/en-us/azure/databricks/notebooks/notebooks-use

• Question 107:

  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?

  A. complete

  B. append

  C. update

  Correct Answer: B

• Question 108:

  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?

  A. Upgrade workspace1 to the Premium pricing tier.

  B. Configure a global init script for workspace1.

  C. Create a pool in workspace1.

  D. Create a cluster policy in workspace1.

  Correct Answer: C

  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

• Question 109:

  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 [email protected] instead.

  What should you do?

  A. From the Azure portal, set a mask on the Email column.

  B. From the Azure portal, set a sensitivity classification of Confidential for the Email column.

  C. From Microsoft SQL Server Management Studio, set an email mask on the Email column.

  D. From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.

  Correct Answer: A

• Question 110:

  You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?

  A. HASH

  B. REPLICATE

  C. ROUND_ROBIN

  Correct Answer: B

  A replicated table has a full copy of the table available on every Compute node. Queries run fast on replicated tables since joins on replicated tables don't require data movement. Replication requires extra storage, though, and isn't practical for large tables.

  Incorrect Answers:

  C: A round-robin distributed table distributes table rows evenly across all distributions. The assignment of rows to distributions is random. Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

  As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables- distribute