DP-200 Sample Questions Answers

Scenario: A daily process creates reporting data in REPORTINGDB from the data in SALESDB. The process is implemented as a SQL Server Integration Services (SSIS) package that runs a stored procedure from SALESDB.

Step 1: Create a linked service to each database

Step 2: Create two datasets

You can create two datasets: InputDataset and OutputDataset. These datasets are of type AzureBlob. They refer to the Azure Storage linked service that you created in the previous section.

Step 3: Create a pipeline

You create and validate a pipeline with a copy activity that uses the input and output datasets.

Step 4: Add a copy activity

References:

https://docs.m icrosoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal

Scenario:

Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.

The sales data including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics.

Streaming Units (SUs) represents the computing resources that are allocated to execute a Stream Analytics job. The higher the number of SUs, the more CPU and memory resources are allocated for your job. This capacity lets you focus on the query logic and abstracts the need to manage the hardware to run your Stream Analytics job in a timely manner.

References:

https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-streaming-unit-consumption

Data in SALESDB must encrypted by using Transparent Data Encryption (TDE). The encryption must use your own key.

Step 1: Implement an Azure key vault

You must create an Azure Key Vault and Key to use for TDE

Step 2: Create a key

Step 3: From the settings of the Azure SQL database …

You turn transparent data encryption on and off on the database level.

References:

https://docs.microsoft.com/en-us/azure/sql-database/transpa rent-data-encryption-byok-azure-sql-configure

Scenario: Disk IO, CPU, and memory usage must be monitored for SALESDB

The sys.resource_stats returns historical data for CPU, IO, DTU consumption. There’s one row every 5 minute for a database in an Azure logical SQL Server if there’s a change in the metrics.

DOCDB stored documents that connect to the sales data in SALESDB. The documents are stored in two different JSON formats based on the sales channel.

The sales data including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytic process will perform aggregations that must be done continuously, without gaps, and without overlapping.

As they arrive, all the sales documents in JSON format must be transformed into one consistent format.

With Time to Live or TTL, Azure Cosmos DB provides the ability to delete items automatically from a container after a certain time period. By default, you can set time to live at the container level and override the value on a per-item basis. After you set the TTL at a container or at an item level, Azure Cosmos DB will automatically remove these items after the time period, since the time they were last modified.

References:

https://docs.mi crosoft.com/en-us/azure/cosmos-db/time-to-live

We must use Custom Text data masking, which exposes the first and last characters and adds a custom

padding string in the middle.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started

The Set-AzStorageAccountManagementPolicy cmdlet creates or modifies the management policy of an Azure Storage account.

Example: Create or update the management policy of a Storage account with ManagementPolicy rule objects.

Action -BaseBlobAction Delete -daysAfterModificationGreaterThan 100

PS C:\>$action1 = Add-AzStorageAccountManagementPolicyAction -InputObject $action1 -BaseBlobAction TierToArchive -daysAfterModificationGreaterThan 50

PS C:\>$action1 = Add-AzStorageAccountManagementPolicyAction -InputObject $action1 -BaseBlobAction TierToCool -daysAfterModificationGreaterThan 30

PS C:\>$action1 = Add-AzStorageAccountManagementPolicyAction -InputObject $action1 -SnapshotAction Delete -daysAfterCreationGreaterThan 100

PS C:\>$filter1 = New-AzStorageAccountManagementPolicyFilter -PrefixMatch ab,cd

PS C:\>$rule1 = New-AzStorageAccountManagementPolicyRule -Name Test -Action $action1 -Filter $filter1

PS C:\>$action2 = Add-AzStorageAccountManagementPolicyAction -BaseBlobAction Delete -daysAfterModificationGreaterThan 100

PS C:\>$filter2 = New-AzStorageAccountManagementPolicyFilter

References:

https://docs.microsoft.com/e n-us/powershell/module/az.storage/set-azstorageaccountmanagementpolicy

B: Late Input Events: events that arrived later than the configured late arrival tolerance window.

Note: While comparing utilization over a period of time, use event rate metrics. InputEvents and OutputEvents metrics show how many events were read and processed.

D: In job diagram, there is a per partition backlog event metric for each input. If the backlog event metric keeps increasing, it’s also an indicator that the system resource is constrained (either because of output sink throttling, or high CPU).

You need to create an external file format and external table from an external data source, instead from a remote service binding pointing.

References:

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

We need one reference data input for LocationIncomes, which rarely changes.

We need two queries, on for in-store customers, and one for online customers.

For each query two outputs is needed.

Note: Stream Analytics also supports input known as reference data. Reference data is either completely static or changes slowly.

References:

https://do cs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-add-inputs#stream-and-reference-inputs

https://docs.microsoft.com/en-us/azure /stream-analytics/stream-analytics-define-outputs

Scenario:

All deployments must be performed by using Azure DevOps. Deployments must use templates used in multiple environments

No credentials or secrets should be used during deployments

The way you control access to resources using RBAC is to create role assignments. This is a key concept to understand – it’s how permissions are enforced. A role assignment consists of three elements: security principal, role definition, and scope.

Scenario:

No credentials or secrets should be used during deployments

Phone-based poll data must only be uploaded by authorized users from authorized devices

Contractors must not have access to any polling data other than their own

Access to polling data must set on a per-active directory user basis

References:

https://docs.microsoft.com/en-us/azure/role-based-access-control/overview

Box 1: Azure Active Directory user

Scenario:

Access to polling data must set on a per-active directory user basis

Box 2: DataBase Scoped Credential

SQL Server uses a database scoped credential to access non-public Azure blob storage or Kerberos-secured Hadoop clusters with PolyBase.

PolyBase cannot authenticate by using Azure AD authentication.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-databa se-scoped-credential-transact-sql

Box 1: FileCapacity

FileCapacity is the amount of storage used by the storage account’s File service in bytes.

Box 2: Avg

The aggregation type of the FileCapacity metric is Avg.

Scenario:

All services and processes must be resilient to a regional Azure outage.

All Azure services must be monitored by using Azure Monitor. On-premises SQL Server performance must be monitored.

References:

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/metrics-supported

Account type: StorageV2

You must create new storage accounts as type StorageV2 (general-purpose V2) to take advantage of Data Lake Storage Gen2 features.

Scenario: Polling data is stored in one of the two locations:

• An on-premises Microsoft SQL Server 2019 database named PollingData
• Azure Data Lake Gen 2

Data in Data Lake is queried by using PolyBase

Replication type: RA-GRS

Scenario: All services and processes must be resilient to a regional Azure outage.

Geo-redundant storage (GRS) is designed to provide at least 99.99999999999999% (16 9's) durability of objects over a given year by replicating your data to a secondary region that is hundreds of miles away from the primary region. If your storage account has GRS enabled, then your data is durable even in the case of a complete regional outage or a disaster in which the primary region isn't recoverable.

If you opt for GRS, you have two related options to choose from:

• GRS replicates your data to another data center in a secondary region, but that data is available to be read only if Microsoft initiates a failover from the primary to secondary region.
• Read-access geo-redundant storage (RA-GRS) is based on GRS. RA-GRS replicates your data to another data center in a secondary region, and also provides you with the option to read from the secondary region. With RA-GRS, you can read from the secondary region regardless of whether Microsoft initiates a failover from the primary to secondary region.

References:

https://docs.microsoft.com/bs-cyrl-ba/azure/storage/blobs/data-lake-storage-quickstart-create-account

https://docs .microsoft.com/en-us/azure/storage/common/storage-redundancy-grs

When creating a schedule trigger, you specify a schedule (start date, recurrence, end date etc.) for the trigger, and associate with a Data Factory pipeline.

Scenario:

All data migration processes must use Azure Data Factory

All data migrations must run automatically during non-business hours

References:

https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger

Event Hubs provides a Kafka endpoint that can be used by your existing Kafka based applications as an alternative to running your own Kafka cluster.

You can stream data into Kafka-enabled Event Hubs and process it with Azure Stream Analytics, in the following steps:

• Create a Kafka enabled Event Hubs namespace.
• Create a Kafka client that sends messages to the event hub.
• Create a Stream Analytics job that copies data from the event hub into an Azure blob storage.

Scenario:

Tier 9 reporting must be moved to Event Hubs, queried, and persisted in the same Azure region as the company’s main office

References:

https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-kafka-stream-analytics

Tier 7 and 8 data access is constrained to single endpoints managed by partners for access

Step 1: Set the Allow Azure Services to Access Server setting to Disabled

Set Allow access to Azure services to OFF for the most secure configuration.

By default, access through the SQL Database firewall is enabled for all Azure services, under Allow access to Azure services. Choose OFF to disable access for all Azure services.

Note: The firewall pane has an ON/OFF button that is labeled Allow access to Azure services. The ON setting allows communications from all Azure IP addresses and all Azure subnets. These Azure IPs or subnets might not be owned by you. This ON setting is probably more open than you want your SQL Database to be. The virtual network rule feature offers much finer granular control.

Step 2: In the Azure portal, create a server firewall rule

Set up SQL Database server firewall rules

Server-level IP firewall rules apply to all databases within the same SQL Database server.

To set up a server-level firewall rule:

• In Azure portal, select SQL databases from the left-hand menu, and select your database on the SQL databases page.
• On the Overview page, select Set server firewall. The Firewall settings page for the database server opens.

Step 3: Connect to the database and use Transact-SQL to create a database firewall rule

Database-level firewall rules can only be configured using Transact-SQL (T-SQL) statements, and only after you've configured a server-level firewall rule.

To setup a database-level firewall rule:

• Connect to the database, for example using SQL Server Management Studio.
• In Object Explorer, right-click the database and select New Query.
• In the query window, add this statement and modify the IP address to your public IP address:

• EXECUTE sp_set_database_firewall_rule N'Example DB Rule','0.0.0.4','0.0.0.4';
• On the toolbar, select Execute to create the firewall rule.

References:

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

Box 1: Custom text/string: A masking method, which exposes the first and/or last characters and adds a custom padding string in the middle.

Only show the last four digits of the values in a column named SuspensionSprings.

Box 2: Default

Default uses a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).

Scenario: Only show a zero value for the values in a column named ShockOilWeight.

Scenario:

The company identifies the following data masking requirements for the Race Central data that will be stored in SQL Database:

• Only show a zero value for the values in a column named ShockOilWeight.
• Only show the last four digits of the values in a column named SuspensionSprings.

Scenario: Transparent data encryption (TDE) must be enabled on all data stores, whenever possible.

The datacentre for Mechanical Workflow must be moved to Azure SQL data Warehouse.

Scenario:

An Azure Data Factory pipeline must be used to move data from Cosmos DB to SQL Database for Race Central. If the data load takes longer than 20 minutes, configuration changes must be made to Data Factory.

The telemetry data is sent to a MongoDB database. A custom application then moves the data to databases in SQL Server 2017. The telemetry data in MongoDB has more than 500 attributes. The application changes the attribute names when the data is moved to SQL Server 2017.

You can copy data to or from Azure Cosmos DB (SQL API) by using Azure Data Factory pipeline.

Column mapping applies when copying data from source to sink. By default, copy activity map source data to sink by column names. You can specify explicit mapping to customize the column mapping based on your need. More specifically, copy activity:

Read the data from source and determine the source schema

• Use default column mapping to map columns by name, or apply explicit column mapping if specified.
• Write the data to sink
• Write the data to sink

References:

https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping

Performance tuning tips and optimization features. In some cases, when you run a copy activity in Azure Data Factory, you see a "Performance tuning tips" message on top of the copy activity monitoring, as shown in the following example. The message tells you the bottleneck that was identified for the given copy run. It also guides you on what to change to boost copy throughput. The performance tuning tips currently provide suggestions like:

Use PolyBase when you copy data into Azure SQL Data Warehouse.

Increase Azure Cosmos DB Request Units or Azure SQL Database DTUs (Database Throughput Units)

when the resource on the data store side is the bottleneck.

Remove the unnecessary staged copy.

References:

https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-performance

API: MongoDB

Consistency level: Strong

Use the strongest consistency Strong to minimize convergence time.

Scenario: The data must be written to the Azure datacentre closest to each race and must converge in the least amount of time.

References:

https://docs.microsoft.com/en-us/azure/cosmos-db/consistency-levels

Box 1: Default

Default uses a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).

Only Show a zero value for the values in a column named ShockOilWeight.

Box 2: Credit Card

The Credit Card Masking method exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of a credit card.

Example: XXXX-XXXX-XXXX-1234

Only show the last four digits of the values in a column named SuspensionSprings.

Scenario:

The company identifies the following data masking requirements for the Race Central data that will be stored in SQL Database:

Only Show a zero value for the values in a column named ShockOilWeight.

Only show the last four digits of the values in a column named SuspensionSprings.

Scenario: The query performance of Race Central must be stable, and the administrative time it takes to

perform optimizations must be minimized.

sp_update updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Table Type: Hash distributed.

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

Index type: Clusted columnstore

Scenario:

Mechanical Workflow has a named Table1 that is 1 TB. Large aggregations are performed on a single column of Table 1.

References:

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