Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 251:

  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:

  

  Correct Answer:

  

  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.

  Reference:

  https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

• Question 252:

  HOTSPOT

  You have an Azure Data Lake Storage Gen2 account named account1 that stores logs as shown in the following table.

  

  You do not expect that the logs will be accessed during the retention periods.

  You need to recommend a solution for account1 that meets the following requirements:

  1.

  Automatically deletes the logs at the end of each retention period

  2.

  Minimizes storage costs

  What should you include in the recommendation? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Store the infrastructure logs in the Cool access tier the application logs in the Archive access tier

  Hot - Optimized for storing data that is accessed frequently.

  Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days.

  Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.

  Box 2: Azure Blob storage lifecycle management rules

  Blob storage lifecycle management offers a rich, rule-based policy that you can use to transition your data to the best access tier and to expire data at the end of its lifecycle.

  Reference:

  https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

• Question 253:

  HOTSPOT

  From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays.

  The data contains the following columns:

  

  You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension.

  To which table should you add each column? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: FactEvents

  Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc.

  Box 2: DimChannel

  Dimension tables describe business entities - the things you model. Entities can include products, people, places, and concepts including time itself. The most consistent table you'll find in a star schema is a date dimension table. A dimension

  table contains a key column (or columns) that acts as a unique identifier, and descriptive columns.

  Box 3: DimEvent

  Reference:

  https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

• Question 254:

  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:

  

  Correct Answer:

  

  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

• Question 255:

  HOTSPOT

  You are building a database in an Azure Synapse Analytics serverless SQL pool.

  You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container.

  Records are structured as shown in the following sample.

  

  The records contain two applicants at most.

  You need to build a table that includes only the address fields.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: CREATE EXTERNAL TABLE An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool.

  Syntax:

  CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } ( [ ,...n ] ) WITH (

  LOCATION = 'folder_or_filepath',

  DATA_SOURCE = external_data_source_name,

  FILE_FORMAT = external_file_format_name

  Box 2. OPENROWSET

  When using serverless SQL pool, CETAS is used to create an external table and export query results to Azure Storage Blob or Azure Data Lake Storage Gen2.

  Example:

  AS

  SELECT decennialTime, stateName, SUM(population) AS population

  FROM

  OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',

  FORMAT='PARQUET') AS [r] GROUP BY decennialTime, stateName GO

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

• Question 256:

  HOTSPOT

  You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named Account1.

  You plan to access the files in Account1 by using an external table.

  You need to create a data source in Pool1 that you can reference when you create the external table.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: blob The following example creates an external data source for Azure Data Lake Gen2 CREATE EXTERNAL DATA SOURCE YellowTaxi WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',

  TYPE = HADOOP)

  Box 2: HADOOP

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

• Question 257:

  HOTSPOT

  You plan to develop a dataset named Purchases by using Azure Databricks. Purchases will contain the following columns:

  1.

  ProductID

  2.

  ItemPrice

  3.

  LineTotal

  4.

  Quantity

  5.

  StoreID

  6.

  Minute

  7.

  Month

  8.

  Hour

  9.

  Year 10.Day

  You need to store the data to support hourly incremental load pipelines that will vary for each StoreID. The solution must minimize storage costs.

  How should you complete the code? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: .partitionBy

  Example:

  df.write.partitionBy("y","m","d")

  .mode(SaveMode.Append)

  .parquet("/data/hive/warehouse/db_name.db/" + tableName)

  Box 2: ("Year","Month","Day","Hour","StoreID")

  Box 3: .parquet("/Purchases")

  Reference:

  https://intellipaat.com/community/11744/how-to-partition-and-write-dataframe-in-spark-without-deleting-partitions-with-no-new-data

• Question 258:

  HOTSPOT

  You have an Azure subscription that is linked to a hybrid Azure Active Directory (Azure AD) tenant. The subscription contains an Azure Synapse Analytics SQL pool named Pool1.

  You need to recommend an authentication solution for Pool1. The solution must support multi-factor authentication (MFA) and database-level authentication.

  Which authentication solution or solutions should you include in the recommendation? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Azure AD authentication

  Azure Active Directory authentication supports Multi-Factor authentication through Active Directory Universal Authentication.

  Box 2: Contained database users

  Azure Active Directory Uses contained database users to authenticate identities at the database level.

  Incorrect:

  SQL authentication: To connect to dedicated SQL pool (formerly SQL DW), you must provide the following information:

  1.

  Fully qualified servername

  2.

  Specify SQL authentication

  3.

  Username

  4.

  Password

  5.

  Default database (optional)

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

• Question 259:

  HOTSPOT

  You are building an Azure Stream Analytics job to retrieve game data.

  You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game.

  How should you complete the Stream Analytics query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: TopOne() OVER(PARTITION BY Game ORDER BY Score Desc)

  TopOne returns the top-rank record, where rank defines the ranking position of the event in the window according to the specified ordering. Ordering/ranking is based on event columns and can be specified in ORDER BY clause.

  Analytic Function Syntax:

  TopOne() OVER ([] ORDER BY ( [ASC |DESC])+ [])

  Box 2: Tumbling(minute 5)

  Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap,

  and an event cannot belong to more than one tumbling window.

  

  Reference: https://docs.microsoft.com/en-us/stream-analytics-query/topone-azure-stream-analytics https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md

• Question 260:

  HOTSPOT

  You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema.

  You plan to have a fact table for website visits. The table will be approximately 5 GB.

  You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.

  What should you recommend? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Hash

  Consider using a hash-distributed table when:

  The table size on disk is more than 2 GB.

  The table has frequent insert, update, and delete operations.

  Box 2: Clustered columnstore

  Clustered columnstore tables offer both the highest level of data compression and the best overall query performance.

  Reference:

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

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