Microsoft Microsoft Certified: Azure Data Engineer Associate DP-203 Questions & Answers

• Question 1:

  HOTSPOT

  You need to design a data storage structure for the product sales transactions. The solution must meet the sales transaction dataset requirements.

  What should you include in the solution? 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

  Scenario:

  Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

  A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

  Box 2: Set the distribution column to the sales date.

  Scenario: Partition data that contains sales transaction records. Partitions must be designed to provide efficient loads by month. Boundary values must belong to the partition on the right.

  Reference:

  https://rajanieshkaushikk.com/2020/09/09/how-to-choose-right-data-distribution-strategy-for-azure-synapse/

• Question 2:

  HOTSPOT

  You need to design an analytical storage solution for the transactional data. The solution must meet the sales transaction dataset requirements.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Round-robin

  Round-robin tables are useful for improving loading speed.

  Scenario: Partition data that contains sales transaction records. Partitions must be designed to provide efficient loads by month.

  Box 2: Hash

  Hash-distributed tables improve query performance on large fact tables.

  Scenario:

  1.

  You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

  2.

  Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

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

• Question 3:

  HOTSPOT

  You have an Azure Synapse serverless SQL pool.

  You need to read JSON documents from a file by using the OPENROWSET function.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 4:

  HOTSPOT

  You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Sales.Orders. Sales.Orders contains a column named SalesRep.

  You plan to implement row-level security (RLS) for Sales.Orders.

  You need to create the security policy that will be used to implement RLS. The solution must ensure that sales representatives only see rows for which the value of the SalesRep column matches their username.

  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:

  

• Question 5:

  HOTSPOT

  You have an Azure subscription that contains an Azure Databricks workspace named databricks1 and an Azure Synapse Analytics workspace named synapse1. The synapse1 workspace contains an Apache Spark pool named pool1.

  You need to share an Apache Hive catalog of pool1 with databricks1.

  What should you do? 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 SQL Database

  Use external Hive Metastore for Synapse Spark Pool Azure Synapse Analytics allows Apache Spark pools in the same workspace to share a managed HMS (Hive Metastore) compatible metastore as their catalog.

  Set up linked service to Hive Metastore

  Follow below steps to set up a linked service to the external Hive Metastore in Synapse workspace.

  Open Synapse Studio, go to Manage > Linked services at left, click New to create a new linked service.

  Set up Hive Metastore linked service

  Choose Azure SQL Database or Azure Database for MySQL based on your database type, click Continue.

  Provide Name of the linked service. Record the name of the linked service, this info will be used to configure Spark shortly.

  You can either select Azure SQL Database/Azure Database for MySQL for the external Hive Metastore from Azure subscription list, or enter the info manually.

  Provide User name and Password to set up the connection.

  Test connection to verify the username and password.

  Click Create to create the linked service.

  Box 2: A Hive Metastore

• Question 6:

  A company purchases IoT devices to monitor manufacturing machinery. The company uses an Azure IoT Hub to communicate with the IoT devices.

  The company must be able to monitor the devices in real-time.

  You need to design the solution.

  What should you recommend?

  A. Azure Analysis Services using Microsoft Visual Studio

  B. Azure Data Factory instance using Azure PowerShell

  C. Azure Analysis Services using Azure PowerShell

  D. Azure Stream Analytics cloud job using Azure Portal

  Correct Answer: D

  In a real-world scenario, you could have hundreds of these sensors generating events as a stream. Ideally, a gateway device would run code to push these events to Azure Event Hubs or Azure IoT Hubs. Your Stream Analytics job would

  ingest these events from Event Hubs and run real-time analytics queries against the streams.

  Create a Stream Analytics job:

  In the Azure portal, select + Create a resource from the left navigation menu. Then, select Stream Analytics job from Analytics.

  Reference:

  https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-get-started-with-azure-stream-analytics-to-process-data-from-iot-devices

• Question 7:

  You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a fact table named Table1.

  You need to identify the extent of the data skew in Table1.

  What should you do in Synapse Studio?

  A. Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

  B. Connect to Pool1 and run DBCC PDW_SHOWSPACEUSED.

  C. Connect to Pool1 and query sys.dm_pdw_node_status.

  D. Connect to the built-in pool and query sys.dm_pdw_sys_info.

  Correct Answer: A

  Microsoft recommends use of sys.dm_pdw_nodes_db_partition_stats to analyze any skewness in the data.

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

• Question 8:

  You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named account1 and an Azure Synapse Analytics workspace named workspace1.

  You need to create an external table in a serverless SQL pool in workspace1. The external table will reference CSV files stored in account1. The solution must maximize performance.

  How should you configure the external table?

  A. Use a native external table and authenticate by using a shared access signature (SAS).

  B. Use a native external table and authenticate by using a storage account key.

  C. Use an Apache Hadoop external table and authenticate by using a shared access signature (SAS).

  D. Use an Apache Hadoop external table and authenticate by using a service principal in Microsoft Azure Active Directory (Azure AD), part of Microsoft Entra.

  Correct Answer: A

  External table type: Native. Storage authentication: Shared Access Signature(SAS), Azure Active Directory passthrough, Managed identity, Custom application Azure AD identity.

  Example for CREATE EXTERNAL DATA SOURCE

  The following example creates a Hadoop external data source in dedicated SQL pool for Azure Data Lake Gen2 pointing to the New York data set:

  CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]

  WITH IDENTITY='SHARED ACCESS SIGNATURE',

  SECRET = 'sv=2018-03-28andss=bfandsrt=scoandsp=rlandst=2019-10-14T12%3A10%3A25Zandse=2061-12-31T12%3A10%3A00Zandsig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

  GO

  CREATE EXTERNAL DATA SOURCE AzureDataLakeStore

  WITH -- Please note the abfss endpoint when your account has secure transfer enabled ( LOCATION = 'abfss://[email protected]' ,

  CREDENTIAL = ADLS_credential ,

  TYPE = HADOOP

  ) ;

  Incorrect:

  Not C, not D: Hadoop external tables are available in dedicated SQL pools, but they aren't available in serverless SQL pools.

  Note: Depending on the type of the external data source, you can use two types of external tables:

  *

  Hadoop external tables that you can use to read and export data in various data formats such as CSV, Parquet, and ORC. Hadoop external tables are available in dedicated SQL pools, but they aren't available in serverless SQL pools.

  *

  Native external tables that you can use to read and export data in various data formats such as CSV and Parquet. Native external tables are available in serverless SQL pools, and they are in public preview in dedicated SQL pools. Writing/ exporting data using CETAS and the native external tables is available only in the serverless SQL pool, but not in the dedicated SQL pools.

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

• Question 9:

  What should you recommend using to secure sensitive customer contact information?

  A. Transparent Data Encryption (TDE)

  B. row-level security

  C. column-level security

  D. data sensitivity labels

  Correct Answer: C

  Scenario: All cloud data must be encrypted at rest and in transit.

  Always Encrypted is a feature designed to protect sensitive data stored in specific database columns from access (for example, credit card numbers, national identification numbers, or data on a need to know basis). This includes database administrators or other privileged users who are authorized to access the database to perform management tasks, but have no business need to access the particular data in the encrypted columns. The data is always encrypted, which means the encrypted data is decrypted only for processing by client applications with access to the encryption key.

  References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview

• Question 10:

  DRAG DROP

  You need to implement versioned changes to the integration pipelines. The solution must meet the data integration requirements.

  In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Scenario: Identify a process to ensure that changes to the ingestion and transformation activities can be version-controlled and developed independently by multiple data engineers.

  Step 1: Create a repository and a main branch You need a Git repository in Azure Pipelines, TFS, or GitHub with your app. Step 2: Create a feature branch Step 3: Create a pull request Step 4: Merge changes

  Merge feature branches into the main branch using pull requests. Step 5: Publish changes