Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 111:

  You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements:

  1.

  Send the output to an Azure Synapse.

  2.

  Identify spikes and dips in time series data.

  3.

  Minimize development and configuration effort. Which should you include in the solution?

  A. Azure SQL Database

  B. Azure Databricks

  C. Azure Stream Analytics

  Correct Answer: C

  Anomalies can be identified by routing data via IoT Hub to a built-in ML model in Azure Stream Analytics

  Reference: https://docs.microsoft.com/en-us/learn/modules/data-anomaly-detection-using-azure-iot-hub/ https://docs.microsoft.com/en-us/azure/stream-analytics/azure-synapse-analytics-output

• Question 112:

  You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1.

  You plan to create a database named DB1 in Pool1.

  You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool.

  Which format should you use for the tables in DB1?

  A. JSON

  B. CSV

  C. Parquet

  D. ORC

  Correct Answer: C

  Serverless SQL pool can automatically synchronize metadata from Apache Spark. A serverless SQL pool database will be created for each database existing in serverless Apache Spark pools.

  For each Spark external table based on Parquet and located in Azure Storage, an external table is created in a serverless SQL pool database. As such, you can shut down your Spark pools and still query Spark external tables from serverless SQL pool.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-spark-tables

• Question 113:

  You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.

  1.

  Clustered columnstore indexes

  2.

  Automatic tuning

  3.

  Change tracking

  4.

  PolyBase

  You plan to migrate DB1 to an Azure SQL database.

  What feature should be removed or replaced before DB1 can be migrated?

  A. Clustered columnstore indexes

  B. PolyBase

  C. Change tracking

  D. Automatic tuning

  Correct Answer: B

  This table lists the key features for PolyBase and the products in which they're available.

  

  Incorrect Answers:

  C: Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. It applies to both Azure SQL Database and SQL Server.

  D: Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary

• Question 114:

  You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4- TB database named DB1.

  You plan to migrate DB1 to an Azure SQL Database managed instance.

  What should you use to minimize downtime and data loss during the migration?

  A. distributed availability groups

  B. database mirroring

  C. log shipping

  D. Database Migration Assistant

  Correct Answer: D

  Ref: https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

• Question 115:

  You are designing a streaming data solution that will ingest variable volumes of data.

  You need to ensure that you can change the partition count after creation.

  Which service should you use to ingest the data?

  A. Azure Event Hubs Standard

  B. Azure Stream Analytics

  C. Azure Data Factory

  D. Azure Event Hubs Dedicated

  Correct Answer: D

  The partition count for an event hub in a dedicated Event Hubs cluster can be increased after the event hub has been created. Incorrect Answers:

  A: For Azure Event standard hubs, the partition count isn't changeable, so you should consider long-term scale when setting partition count.

  Reference: https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-features#partitions

• Question 116:

  You have an Azure Synapse Analytics Apache Spark pool named Pool1.

  You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file.

  You need to load the files into the tables. The solution must maintain the source data types.

  What should you do?

  A. Load the data by using PySpark.

  B. Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.

  C. Use a Get Metadata activity in Azure Data Factory.

  D. Use a Conditional Split transformation in an Azure Synapse data flow.

  Correct Answer: B

  Serverless SQL pool can automatically synchronize metadata from Apache Spark. A serverless SQL pool database will be created for each database existing in serverless Apache Spark pools. Serverless SQL pool enables you to query data

  in your data lake. It offers a T-SQL query surface area that accommodates semi-structured and unstructured data queries.

  To support a smooth experience for in place querying of data that's located in Azure Storage files, serverless SQL pool uses the OPENROWSET function with additional capabilities.

  The easiest way to see to the content of your JSON file is to provide the file URL to the OPENROWSET function, specify csv FORMAT.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-json-files

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage

• Question 117:

  You have 20 Azure SQL databases provisioned by using the vCore purchasing model.

  You plan to create an Azure SQL Database elastic pool and add the 20 databases.

  Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. total size of all the databases

  B. geo-replication support

  C. number of concurrently peaking databases * peak CPU utilization per database

  D. maximum number of concurrent sessions for all the databases

  E. total number of databases * average CPU utilization per database

  Correct Answer: ACE

  CE: Estimate the vCores needed for the pool as follows:

  For vCore-based purchasing model: MAX(,

  A: Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview

• Question 118:

  You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.

  

  FactSales has 6 billion rows and is loaded nightly by using a batch process.

  Which type of compression provides the greatest space reduction for the database?

  A. page compression

  B. row compression

  C. columnstore compression

  D. columnstore archival compression

  Correct Answer: D

  Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression.

  Note: Columnstore - The columnstore index is also logically organized as a table with rows and columns, but the data is physically stored in a column-wise data format.

  Incorrect Answers:

  B: Rowstore - The rowstore index is the traditional style that has been around since the initial release of SQL Server. For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. Reference:

  https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

• Question 119:

  You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information.

  You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information.

  What should you include in the recommendation?

  A. row-level security

  B. data masking

  C. Always Encrypted

  D. column-level security

  Correct Answer: D

  inferring work is the key. With data-masking you cannot prevent to ask if a column is equal to a certain value. Also, the salespeople don't need any information of the credit card, maybe customer support would have a use of the masked credit card. So, column-level security is the solution.

• Question 120:

  You have a data warehouse in Azure Synapse Analytics.

  You need to ensure that the data in the data warehouse is encrypted at rest.

  What should you enable?

  A. Transparent Data Encryption (TDE)

  B. Advanced Data Security for this database

  C. Always Encrypted for all columns

  D. Secure transfer required

  Correct Answer: A

  Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview