Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 131:

  HOTSPOT

  You need to output files from Azure Data Factory.

  Which file format should you use for each type of output? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Parquet

  Parquet stores data in columns, while Avro stores data in a row-based format. By their very nature, column-oriented data stores are optimized for read-heavy analytical workloads, while row-based databases are best for write-heavy

  transactional workloads.

  Box 2: Avro

  An Avro schema is created using JSON format.

  AVRO supports timestamps.

  Note: Azure Data Factory supports the following file formats (not GZip or TXT).

  Avro format

  Binary format

  Delimited text format

  Excel format

  JSON format

  ORC format

  Parquet format

  XML format

  Reference:

  https://www.datanami.com/2018/05/16/big-data-file-formats-demystified

• Question 132:

  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.

  Select and Place:

  

  Correct Answer:

  

• Question 133:

  HOTSPOT

  You are creating dimensions for a data warehouse in an Azure Synapse Analytics dedicated SQL pool.

  You create a table by using the Transact-SQL statement 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: Type 2

  A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use

  a surrogate key to provide a unique reference to a version of the dimension member. It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example,

  IsCurrent) to easily filter by current dimension members.

  Incorrect Answers:

  A Type 1 SCD always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten.

  Box 2: a business key

  A business key or natural key is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules. For example business keys are customer code in a customer table, composite of sales

  order header number and sales order item line number within a sales order details table.

  Reference:

  https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

• Question 134:

  DRAG DROP

  You have an Azure subscription.

  You plan to build a data warehouse in an Azure Synapse Analytics dedicated SQL pool named pool1 that will contain staging tables and a dimensional model. Pool1 will contain the following tables.

  

  You need to design the table storage for pool1. The solution must meet the following requirements:

  Maximize the performance of data loading operations to Staging.WebSessions. Minimize query times for reporting queries against the dimensional model.

  Which type of table distribution should you use for each table? To answer, drag the appropriate table distribution types to the correct tables. Each table distribution type 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.

  Select and Place:

  

  Correct Answer:

  

• Question 135:

  DRAG DROP

  You have an Azure Data Lake Storage Gen 2 account named storage1.

  You need to recommend a solution for accessing the content in storage1. The solution must meet the following requirements:

  List and read permissions must be granted at the storage account level. Additional permissions can be applied to individual objects in storage1. Security principals from Microsoft Azure Active Directory (Azure AD), part of Microsoft Entra, must

  be used for authentication.

  What should you use? To answer, drag the appropriate components to the correct requirements. Each component 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.

  Select and Place:

  

  Correct Answer:

  

• Question 136:

  DRAG DROP

  You have an Azure subscription that contains an Azure Databricks workspace. The workspace contains a notebook named Notebook1. In Notebook1, you create an Apache Spark DataFrame named df_sales that contains the following columns:

  1.

  Customer

  2.

  Salesperson

  3.

  Region

  4.

  Amount

  You need to identify the three top performing salespersons by amount for a region named HQ.

  How should you complete the query? 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.

  Select and Place:

  

  Correct Answer:

  

• Question 137:

  DRAG DROP

  You have an Apache Spark DataFrame named temperatures. A sample of the data is shown in the following table.

  

  You need to produce the following table by using a Spark SQL query.

  

  How should you complete the query? 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.

  Select and Place:

  

  Correct Answer:

  

• Question 138:

  DRAG DROP

  You plan to create a table in an Azure Synapse Analytics dedicated SQL pool.

  Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted.

  You need to ensure that the data is distributed evenly across partitions. The solution must minimize the amount of time required to delete old data.

  How should you complete the Transact-SQL statement? 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.

  Select and Place:

  

  Correct Answer:

  

  Box 1: HASH

  Box 2: OrderDateKey

  In most cases, table partitions are created on a date column.

  A way to eliminate rollbacks is to use Metadata Only operations like partition switching for data management. For example, rather than execute a DELETE statement to delete all rows in a table where the order_date was in October of 2001,

  you could partition your data early. Then you can switch out the partition with data for an empty partition from another table.

• Question 139:

  DRAG DROP

  You have an Azure subscription that contains an Azure Synapse Analytics workspace named workspace1. Workspace1 connects to an Azure DevOps repository named repo1. Repo1 contains a collaboration branch named main and a

  development branch named branch1. Branch1 contains an Azure Synapse pipeline named pipeline1.

  In workspace1, you complete testing of pipeline1.

  You need to schedule pipeline1 to run daily at 6 AM.

  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.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer:

  

• Question 140:

  DRAG DROP

  You use PySpark in Azure Databricks to parse the following JSON input.

  

  You need to output the data in the following tabular format.

  

  How should you complete the PySpark 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 spit bar between panes or scroll to view

  content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  Correct Answer:

  

  Box 1: select

  Box 2: explode

  Bop 3: alias

  pyspark.sql.Column.alias returns this column aliased with a new name or names (in the case of expressions that return more than one column, such as explode).

  Reference:

  https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.Column.alias.html

  https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/explode