Microsoft DP-200 Exam Actual Questions (P. 13)

Note: This question is a part of series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You develop a data ingestion process that will import data to an enterprise data warehouse in Azure Synapse Analytics. The data to be ingested resides in parquet files stored in an Azure Data Lake Gen 2 storage account.
You need to load the data from the Azure Data Lake Gen 2 storage account into the Data Warehouse.
Solution:
1. Use Azure Data Factory to convert the parquet files to CSV files
2. Create an external data source pointing to the Azure storage account
3. Create an external file format and external table using the external data source
4. Load the data using the INSERT`¦SELECT statement
Does the solution meet the goal?

Correct Answer: B 🗳️
There is no need to convert the parquet files to CSV files.
You load the data using the CREATE TABLE AS SELECT statement.
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store

Note: This question is a part of series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You develop a data ingestion process that will import data to an enterprise data warehouse in Azure Synapse Analytics. The data to be ingested resides in parquet files stored in an Azure Data Lake Gen 2 storage account.
You need to load the data from the Azure Data Lake Gen 2 storage account into the Data Warehouse.
Solution:
1. Create an external data source pointing to the Azure storage account
2. Create an external file format and external table using the external data source
3. Load the data using the INSERT`¦SELECT statement
Does the solution meet the goal?

Correct Answer: B 🗳️
You load the data using the CREATE TABLE AS SELECT statement.
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store

HOTSPOT -
You are building an Azure Stream Analytics job that queries reference data from a product catalog file. The file is updated daily.
The reference data input details for the file are shown in the Input exhibit.

The storage account container view is shown in the Refdata exhibit.

You need to configure the Stream Analytics job to pick up the new reference data.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Correct Answer:
Box 1: {date}/product.csv -
In the 2nd exhibit we see: Location: refdata / 2020-03-20
Note: Path Pattern: This is a required property that is used to locate your blobs within the specified container. Within the path, you may choose to specify one or more instances of the following 2 variables:
{date}, {time}
Example 1: products/{date}/{time}/product-list.csv
Example 2: products/{date}/product-list.csv

Example 3: product-list.csv -

Box 2: YYYY-MM-DD -
Note: Date Format [optional]: If you have used {date} within the Path Pattern that you specified, then you can select the date format in which your blobs are organized from the drop-down of supported formats.
Example: YYYY/MM/DD, MM/DD/YYYY, etc.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-use-reference-data

DRAG DROP -
You manage security for a database that supports a line of business application.
Private and personal data stored in the database must be protected and encrypted.
You need to configure the database to use Transparent Data Encryption (TDE).
Which five actions should you perform in sequence? To answer, select 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 a master key -
Step 2: Create or obtain a certificate protected by the master key
Step 3: Set the context to the company database
Step 4: Create a database encryption key and protect it by the certificate
Step 5: Set the database to use encryption
Example code:
USE master;

GO -
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
USE AdventureWorks2012;

GO -

CREATE DATABASE ENCRYPTION KEY -

WITH ALGORITHM = AES_128 -
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO -
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;

GO -
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

SIMULATION -
Use the following login credentials as needed:

Azure Username: xxxxx -

Azure Password: xxxxx -
The following information is for technical support purposes only:

Lab Instance: 10543936 -

You need to ensure that only the resources on a virtual network named VNET1 can access an Azure Storage account named storage10543936.
To complete this task, sign in to the Azure portal.

Correct Answer: See the explanation below.
You can use Private Endpoints for your Azure Storage accounts to allow clients on a virtual network (VNet) to securely access data over a Private Link.

Create your Private Endpoint -
1. On the upper-left side of the screen in the Azure portal, Storage > Storage account, and select your storage account storage10543936
2. Select Networking.
3. Select Add Private Endpoint.
4. In Create Private Endpoint, enter or select this information:
Virtual network: Selectג€‰ VNET1 from the resource group.
5. Select OK.
6. Select Review + create. You're taken to the Review + create page where Azure validates your configuration.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/create-private-endpoint-storage-portal

SIMULATION -
Use the following login credentials as needed:

Azure Username: xxxxx -

Azure Password: xxxxx -
The following information is for technical support purposes only:

Lab Instance: 10543936 -

You need to replicate db1 to a new Azure SQL server named db1-copy10543936 in the US West region.
To complete this task, sign in to the Azure portal.

Correct Answer: See the explanation below.
1. In the Azure portal, browse to the database db1-copy10543936 that you want to set up for geo-replication.
2. On the SQL database page, select geo-replication, and then select the region to create the secondary database: US West region

3. Select or configure the server and pricing tier for the secondary database.

4. Click Create to add the secondary.
5. The secondary database is created and the seeding process begins.

6. When the seeding process is complete, the secondary database displays its status.

Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-active-geo-replication-portal

You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email.
You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of [email protected] instead.
What should you do?

Correct Answer: B 🗳️
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

HOTSPOT -
You have an Azure SQL database that contains a table named Customer. Customer contains the columns shown in the following table.

You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements:
✑ The first six numerals of the customer phone numbers must be masked.
✑ The last four digits of the customer phone numbers must be visible.
✑ Hyphens must be preserved and displayed.
How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Correct Answer:
Exposed Prefix: 0 -
The first six digits must be masked. There is thus no exposed prefix.

Padding String: XXX-XXX -
The first six digits must be masked and hyphens must be preserved.

Exposed Suffix: 5 -
The last 4 digits must be visible. There is not option for 4 but we can use 5 as it would include the hyphen before the last 4 digits.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

SIMULATION -
Use the following login credentials as needed:

Azure Username: xxxxx -

Azure Password: xxxxx -
The following information is for technical support purposes only:

Lab Instance: 10543936 -

You need to ensure that you can recover any blob data from an Azure Storage account named storage10543936 up to 10 days after the data is deleted.
To complete this task, sign in to the Azure portal.

Correct Answer: See the explanation below.
Enable soft delete for blobs on your storage account by using Azure portal:
1. In the Azure portal, select your storage account.
2. Navigate to the Data Protection option under Blob Service.
3. Click Enabled under Blob soft delete

4. Enter the number of days you want to retain for under Retention policies. Here enter 10.
5. Choose the Save button to confirm your Data Protection settings
Note: Azure Storage now offers soft delete for blob objects so that you can more easily recover your data when it is erroneously modified or deleted by an application or other storage account user. Currently you can retain soft deleted data for between 1 and 365 days.
Reference:
https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-soft-delete

DRAG DROP -
You plan to create a new single database instance of Microsoft Azure SQL Database.
The database must only allow communication from the data engineer's workstation. You must connect directly to the instance by using Microsoft SQL Server
Management Studio.
You need to create and configure the Database. Which three Azure PowerShell cmdlets should you use to develop the solution? To answer, move the appropriate cmdlets from the list of cmdlets to the answer area and arrange them in the correct order.
Select and Place:

Correct Answer:
Step 1: New-AzureSqlServer -
Create a server.
Step 2: New-AzureRmSqlServerFirewallRule
New-AzureRmSqlServerFirewallRule creates a firewall rule for a SQL Database server.
Can be used to create a server firewall rule that allows access from the specified IP range.

Step 3: New-AzureRmSqlDatabase -
Example: Create a database on a specified server
PS C:\>New-AzureRmSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01
References:
https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-create-and-configure-database-powershell?toc=%2fpowershell%2fmodule%2ftoc.json