Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 141:

  DRAG DROP

  You need to build a solution to ensure that users can query specific files in an Azure Data Lake Storage Gen2 account from an Azure Synapse Analytics serverless SQL pool. Which three 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:

  

  Step 1: Create an external data source

  You can create external tables in Synapse SQL pools via the following steps:

  CREATE EXTERNAL DATA SOURCE to reference an external Azure storage and specify the credential that should be used to access the storage. CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files. CREATE

  EXTERNAL TABLE on top of the files placed on the data source with the same file format.

  Step 2: Create an external file format object

  Creating an external file format is a prerequisite for creating an external table.

  Step 3: Create an external table

• Question 142:

  DRAG DROP

  You are responsible for providing access to an Azure Data Lake Storage Gen2 account.

  Your user account has contributor access to the storage account, and you have the application ID and access key.

  You plan to use PolyBase to load data into an enterprise data warehouse in Azure Synapse Analytics.

  You need to configure PolyBase to connect the data warehouse to storage account.

  Which three components should you create in sequence? To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: a database scoped credential

  To access your Data Lake Storage account, you will need to create a Database Master Key to encrypt your credential secret used in the next step. You then create a database scoped credential.

  Step 2: an external data source Create the external data source. Use the CREATE EXTERNAL DATA SOURCE command to store the location of the data. Provide the credential created in the previous step.

  Step 3: an external file format Configure data format: To import the data from Data Lake Storage, you need to specify the External File Format. This object defines how the files are written in

  Data Lake Storage.

  References:

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store

• Question 143:

  DRAG DROP

  You are designing an Azure Data Lake Storage Gen2 structure for telemetry data from 25 million devices distributed across seven key geographical regions. Each minute, the devices will send a JSON payload of metrics to Azure Event Hubs.

  You need to recommend a folder structure for the data. The solution must meet the following requirements:

  1.

  Data engineers from each region must be able to build their own pipelines for the data of their respective region only.

  2.

  The data must be processed at least once every 15 minutes for inclusion in Azure Synapse Analytics serverless SQL pools.

  How should you recommend completing the structure? 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: {regionID}/raw

  Data engineers from each region must be able to build their own pipelines for the data of their respective region only.

  Box 2: {YYYY}/{MM}/{DD}/{HH}

  Date Format [optional]: if the date token is used in the prefix path, you can select the date format in which your files are organized. Example: YYYY/MM/DD

  Time Format [optional]: if the time token is used in the prefix path, specify the time format in which your files are organized. Currently the only supported value is HH.

  Box 3: {deviceID}

  Reference: https://github.com/paolosalvatori/StreamAnalyticsAzureDataLakeStore/blob/master/README.md

• Question 144:

  DRAG DROP

  You have data stored in thousands of CSV files in Azure Data Lake Storage Gen2. Each file has a header row followed by a properly formatted carriage return (/r) and line feed (/n).

  You are implementing a pattern that batch loads the files daily into an enterprise data warehouse in Azure Synapse Analytics by using PolyBase.

  You need to skip the header row when you import the files into the data warehouse. Before building the loading pattern, you need to prepare the required database objects in Azure Synapse Analytics.

  Which three 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: Each correct selection is worth one point

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create an external data source that uses the abfs location

  Create External Data Source to reference Azure Data Lake Store Gen 1 or 2

  Step 2: Create an external file format and set the First_Row option.

  Create External File Format.

  Step 3: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages

  To use PolyBase, you must create external tables to reference your external data.

  Use reject options.

  Note: REJECT options don't apply at the time this CREATE EXTERNAL TABLE AS SELECT statement is run. Instead, they're specified here so that the database can use them at a later time when it imports data from the external table. Later,

  when the CREATE TABLE AS SELECT statement selects data from the external table, the database will use the reject options to determine the number or percentage of rows that can fail to import before it stops the import.

  Reference:

  https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-t-sql-objects

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql

• Question 145:

  DRAG DROP

  You plan to monitor an Azure data factory by using the Monitor and Manage app.

  You need to identify the status and duration of activities that reference a table in a source database.

  Which three actions should you perform in sequence? To answer, move the actions from the list of actions to the answer are and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: From the Data Factory authoring UI, generate a user property for Source on all activities.

  Step 2: From the Data Factory monitoring app, add the Source user property to Activity Runs table.

  You can promote any pipeline activity property as a user property so that it becomes an entity that you can monitor. For example, you can promote the Source and Destination properties of the copy activity in your pipeline as user properties.

  You can also select Auto Generate to generate the Source and Destination user properties for a copy activity.

  Step 3: From the Data Factory authoring UI, publish the pipelines Publish output data to data stores such as Azure SQL Data Warehouse for business intelligence (BI) applications to consume.

  References:

  https://docs.microsoft.com/en-us/azure/data-factory/monitor-visually

• Question 146:

  HOTSPOT

  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:

  

  Box 1: PIVOT

  PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are

  wanted in the final output.

  Incorrect Answers:

  UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

  Box 2: CAST

  If you want to convert an integer value to a DECIMAL data type in SQL Server use the CAST() function.

  Example: SELECT CAST(12 AS DECIMAL(7,2) ) AS decimal_value;

  Here is the result: decimal_value

  12.00

  Reference: https://learnsql.com/cookbook/how-to-convert-an-integer-to-a-decimal-in-sql-server/ https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

• Question 147:

  DRAG DROP

  You have an Azure Synapse Analytics workspace named WS1.

  You have an Azure Data Lake Storage Gen2 container that contains JSON-formatted files in the following format.

  

  You need to use the serverless SQL pool in WS1 to read the files.

  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: openrowset

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

  Example:

  SELECT *

  FROM OPENROWSET( BULK 'csv/population/population.csv', DATA_SOURCE = 'SqlOnDemandDemo', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIELDTERMINATOR =',', ROWTERMINATOR = '\n'

  Box 2: openjson

  You can access your JSON files from the Azure File Storage share by using the mapped drive, as shown in the following example:

  SELECT book.* FROM OPENROWSET(BULK N't:\books\books.json', SINGLE_CLOB) AS json CROSS APPLY OPENJSON(BulkColumn) WITH( id nvarchar(100), name nvarchar(100), price float, pages_i int, author nvarchar(100)) AS book

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file https://docs.microsoft.com/en-us/sql/relational-databases/json/import-json-documents-into-sql-server

• Question 148:

  DRAG DROP

  You have an Azure Synapse Analytics SQL pool named Pool1 on a logical Microsoft SQL server named Server1.

  You need to implement Transparent Data Encryption (TDE) on Pool1 by using a custom key named key1.

  Which five 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.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Assign a managed identity to Server1

  You will need an existing Managed Instance as a prerequisite.

  Step 2: Create an Azure key vault and grant the managed identity permissions to the vault

  Create Resource and setup Azure Key Vault.

  Step 3: Add key1 to the Azure key vault

  The recommended way is to import an existing key from a .pfx file or get an existing key from the vault. Alternatively, generate a new key directly in Azure Key Vault.

  Step 4: Configure key1 as the TDE protector for Server1

  Provide TDE Protector key

  Step 5: Enable TDE on Pool1

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/scripts/transparent-data-encryption-byok-powershell

• Question 149:

  DRAG DROP

  You have the following table named Employees.

  

  You need to calculate the employee_type value based on the hire_date value.

  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: CASE

  CASE evaluates a list of conditions and returns one of multiple possible result expressions.

  CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and

  HAVING.

  Syntax: Simple CASE expression:

  CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ]

  END Box 2: ELSE Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

• Question 150:

  DRAG DROP

  You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36 months and has the following characteristics:

  1.

  Is partitioned by month

  2.

  Contains one billion rows

  3.

  Has clustered columnstore indexes

  At the beginning of each month, you need to remove data from SalesFact that is older than 36 months as quickly as possible.

  Which three actions should you perform in sequence in a stored procedure? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  Correct Answer:

  

  Step 1: Create an empty table named SalesFact_work that has the same schema as SalesFact.

  Step 2: Switch the partition containing the stale data from SalesFact to SalesFact_Work.

  SQL Data Warehouse supports partition splitting, merging, and switching. To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match.

  Loading data into partitions with partition switching is a convenient way stage new data in a table that is not visible to users the switch in the new data.

  Step 3: Drop the SalesFact_Work table.

  Reference:

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition