IT Exam Solutions
  • QUESTIONS
  • COURSES
    • CCNA
    • Cisco Enterprise Core
    • VMware vSphere: Install, Configure, Manage
  • CERTIFICATES
No Result
View All Result
  • Login
  • Register
IT Quesion Library
  • Cisco
    • 200-301
    • 200-901
      • Multiple Choice
      • Drag Drop
    • 350-401
      • Multiple Choice
      • Drag Drop
    • 350-701
    • 300-410
      • Multiple Choice
      • Drag Drop
    • 300-415
      • Multiple Choice
      • Drag Drop
    • 300-425
    • Others
  • AWS
    • CLF-C02
    • SAA-C03
    • SAP-C02
    • ANS-C01
    • Others
  • Microsoft
    • AZ-104
    • AZ-204
    • AZ-305
    • AZ-900
    • AI-900
    • SC-900
    • Others
  • CompTIA
    • SY0-601
    • N10-008
    • 220-1101
    • 220-1102
    • Others
  • Google
    • Associate Cloud Engineer
    • Professional Cloud Architect
    • Professional Cloud DevOps Engineer
    • Others
  • ISACA
    • CISM
    • CRIS
    • Others
  • LPI
    • 101-500
    • 102-500
    • 201-450
    • 202-450
  • Fortinet
    • NSE4_FGT-7.2
  • VMware
  • >>
    • Juniper
    • EC-Council
      • 312-50v12
    • ISC
      • CISSP
    • PMI
      • PMP
    • Palo Alto Networks
    • RedHat
    • Oracle
    • GIAC
    • F5
    • ITILF
    • Salesforce
Contribute
IT Exam Solutions
  • QUESTIONS
  • COURSES
    • CCNA
    • Cisco Enterprise Core
    • VMware vSphere: Install, Configure, Manage
  • CERTIFICATES
No Result
View All Result
IT Exam Solutions
No Result
View All Result
Home Practice Test Free

DP-200 Practice Test Free

Table of Contents

Toggle
  • DP-200 Practice Test Free – 50 Questions to Test Your Knowledge
  • 50 Free DP-200 Practice Questions
  • Get More DP-200 Practice Questions

DP-200 Practice Test Free – 50 Questions to Test Your Knowledge

Are you preparing for the DP-200 certification exam? If so, taking a DP-200 practice test free is one of the best ways to assess your knowledge and improve your chances of passing. In this post, we provide 50 free DP-200 practice questions designed to help you test your skills and identify areas for improvement.

By taking a free DP-200 practice test, you can:

  • Familiarize yourself with the exam format and question types
  • Identify your strengths and weaknesses
  • Gain confidence before the actual exam

50 Free DP-200 Practice Questions

Below, you will find 50 free DP-200 practice questions to help you prepare for the exam. These questions are designed to reflect the real exam structure and difficulty level.

Question 1

DRAG DROP -
You need to provision the polling data storage account.
How should you configure the storage account? To answer, drag the appropriate Configuration Value to the correct Setting. Each Configuration 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:
 Image

 


Suggested Answer:
Correct Answer Image

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

Question 2

Which two metrics should you use to identify the appropriate RU/s for the telemetry data? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Number of requests

B. Number of requests exceeded capacity

C. End to end observed read latency at the 99 th percentile

D. Session consistency

E. Data + Index storage consumed

F. Avg Throughput/s

 


Suggested Answer: AE

Scenario: The telemetry data must be monitored for performance issues. You must adjust the Cosmos DB Request Units per second (RU/s) to maintain a performance SLA while minimizing the cost of the RU/s.
With Azure Cosmos DB, you pay for the throughput you provision and the storage you consume on an hourly basis.
While you estimate the number of RUs per second to provision, consider the following factors:
Item size: As the size of an item increases, the number of RUs consumed to read or write the item also increases.

Question 3

HOTSPOT -
You need to ensure that Azure Data Factory pipelines can be deployed. How should you configure authentication and authorization for deployments? To answer, select the appropriate options in the answer choices.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

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

Question 4

You need to set up Azure Data Factory pipelines to meet data movement requirements.
Which integration runtime should you use?

A. self-hosted integration runtime

B. Azure-SSIS Integration Runtime

C. .NET Common Language Runtime (CLR)

D. Azure integration runtime

 


Suggested Answer: A

The following table describes the capabilities and network support for each of the integration runtime types:
Reference Image
Scenario: The solution must support migrating databases that support external and internal application to Azure SQL Database. The migrated databases will be supported by Azure Data Factory pipelines for the continued movement, migration and updating of data both in the cloud and from local core business systems and repositories.
References: alt=”Reference Image” />
Scenario: The solution must support migrating databases that support external and internal application to Azure SQL Database. The migrated databases will be supported by Azure Data Factory pipelines for the continued movement, migration and updating of data both in the cloud and from local core business systems and repositories.
References:
https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

Question 5

You need to implement event processing by using Stream Analytics to produce consistent JSON documents.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Define an output to Cosmos DB.

B. Define a query that contains a JavaScript user-defined aggregates (UDA) function.

C. Define a reference input.

D. Define a transformation query.

E. Define an output to Azure Data Lake Storage Gen2.

F. Define a stream input.

 


Suggested Answer: DEF

✑ 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.
<img src=”https://www.examtopics.com/assets/media/exam-media/03872/0014200003.png” alt=”Reference Image” />

Question 6

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to configure data encryption for external applications.
Solution:
1. Access the Always Encrypted Wizard in SQL Server Management Studio
2. Select the column to be encrypted
3. Set the encryption type to Deterministic
4. Configure the master key to use the Azure Key Vault
5. Validate configuration results and deploy the solution
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: A

We use the Azure Key Vault, not the Windows Certificate Store, to store the master key.
Note: The Master Key Configuration page is where you set up your CMK (Column Master Key) and select the key store provider where the CMK will be stored.
Currently, you can store a CMK in the Windows certificate store, Azure Key Vault, or a hardware security module (HSM).
Reference Image
However, if you use the Windows Certificate Store for external applications to use the key, the external application must run on the same computer where you ran the Always Encrypted wizard, or you must deploy the Always Encrypted certificates to the computer running the external application.
Reference: alt=”Reference Image” />
However, if you use the Windows Certificate Store for external applications to use the key, the external application must run on the same computer where you ran the Always Encrypted wizard, or you must deploy the Always Encrypted certificates to the computer running the external application.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault
https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-certificate-store-configure

Question 7

HOTSPOT -
You need to mask tier 1 data. Which functions should you use? To answer, select the appropriate option in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

A: Default –
Full masking according to the data types of the designated fields.
For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).
B: email –
C: Custom text –
Custom StringMasking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Tier 1 Database must implement data masking using the following masking logic:
Reference Image
References: alt=”Reference Image” />
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

Question 8

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to configure data encryption for external applications.
Solution:
1. Access the Always Encrypted Wizard in SQL Server Management Studio
2. Select the column to be encrypted
3. Set the encryption type to Deterministic
4. Configure the master key to use the Windows Certificate Store
5. Validate configuration results and deploy the solution
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: B

Use the Azure Key Vault, not the Windows Certificate Store, to store the master key as it must be used by external applications.
Note: The Master Key Configuration page is where you set up your CMK (Column Master Key) and select the key store provider where the CMK will be stored.
Currently, you can store a CMK in the Windows certificate store, Azure Key Vault, or a hardware security module (HSM).
Reference Image
However, if you use the Windows Certificate Store for external applications to use the key, the external application must run on the same computer where you ran the Always Encrypted wizard, or you must deploy the Always Encrypted certificates to the computer running the external application.
Reference: alt=”Reference Image” />
However, if you use the Windows Certificate Store for external applications to use the key, the external application must run on the same computer where you ran the Always Encrypted wizard, or you must deploy the Always Encrypted certificates to the computer running the external application.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault
https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-certificate-store-configure

Question 9

HOTSPOT -
You have an Azure data factory that has two pipelines named PipelineA and PipelineB.
PipelineA has four activities as shown in the following exhibit.
 Image
PipelineB has two activities as shown in the following exhibit.
 Image
You create an alert for the data factory that uses Failed pipeline runs metrics for both pipelines and all failure types. The metric has the following settings:
✑ Operator: Greater than
✑ Aggregation type: Total
✑ Threshold value: 2
✑ Aggregation granularity (Period): 5 minutes
✑ Frequency of evaluation: Every 5 minutes
Data Factory monitoring records the failures shown in the following table.
 Image
For each of the following statements, select yes if the statement is true. Otherwise, select no.
NOTE: Each correct answer selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: No –
Only one failure at this point.
Box 2: No –
Only two failures within 5 minutes.
Box 3: Yes –
More than two (three) failures in 5 minutes
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/alerts-insights-configure-portal

Question 10

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need setup monitoring for tiers 6 through 8.
What should you configure?

A. extended events for average storage percentage that emails data engineers

B. an alert rule to monitor CPU percentage in databases that emails data engineers

C. an alert rule to monitor CPU percentage in elastic pools that emails data engineers

D. an alert rule to monitor storage percentage in databases that emails data engineers

E. an alert rule to monitor storage percentage in elastic pools that emails data engineers

 


Suggested Answer: E

Scenario:
Tiers 6 through 8 must have unexpected resource storage usage immediately reported to data engineers.
Tier 3 and Tier 6 through Tier 8 applications must use database density on the same server and Elastic pools in a cost-effective manner.

Question 11

You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1.
Several users execute adhoc queries to DW1 concurrently.
You regularly perform automated data loads to DW1.
You need to ensure that the automated data loads have enough memory available to complete quickly and successfully when the adhoc queries run.
What should you do?

A. Hash distribute the large fact tables in DW1 before performing the automated data loads.

B. Assign a larger resource class to the automated data load queries.

C. Create sampled statistics for every column in each table of DW1.

D. Assign a smaller resource class to the automated data load queries.

 


Suggested Answer: B

To ensure the loading user has enough memory to achieve maximum compression rates, use loading users that are a member of a medium or large resource class.
Reference:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data

Question 12

HOTSPOT -
You need to receive an alert when Azure Synapse Analytics consumes the maximum allotted resources.
Which resource type and signal should you use to create the alert in Azure Monitor? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Resource type: SQL data warehouse
DWU limit belongs to the SQL data warehouse resource type.
Signal: DWU limit –
SQL Data Warehouse capacity limits are maximum values allowed for various components of Azure SQL Data Warehouse.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-insights-alerts-portal

Question 13

HOTSPOT -
You have a new Azure Data Factory environment.
You need to periodically analyze pipeline executions from the last 60 days to identify trends in execution durations. The solution must use Azure Log Analytics to query the data and create charts.
Which diagnostic settings should you configure in Data Factory? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Log type: PipelineRuns –
A pipeline run in Azure Data Factory defines an instance of a pipeline execution.
Storage location: An Azure Storage account
Data Factory stores pipeline-run data for only 45 days. Use Monitor if you want to keep that data for a longer time. With Monitor, you can route diagnostic logs for analysis. You can also keep them in a storage account so that you have factory information for your chosen duration.
Save your diagnostic logs to a storage account for auditing or manual inspection. You can use the diagnostic settings to specify the retention time in days.
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers
https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor

Question 14

HOTSPOT -
You are implementing automatic tuning mode for Azure SQL databases.
Automatic tuning mode is configured as shown in the following table.
 Image
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Automatic tuning options can be independently enabled or disabled per database, or they can be configured on SQL Database servers and applied on every database that inherits settings from the server. SQL Database servers can inherit Azure defaults for Automatic tuning settings. Azure defaults at this time are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning

Question 15

You have an Azure Stream Analytics job.
You need to ensure that the job has enough streaming units provisioned.
You configure monitoring of the SU% Utilization metric.
Which two additional metrics should you monitor? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Watermark Delay

B. Late Input Events

C. Out of order Events

D. Backlogged Input Events

E. Function Events

 


Suggested Answer: BD

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).
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-scale-jobs

Question 16

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a container named Sales in an Azure Cosmos DB database. Sales has 120 GB of data. Each entry in Sales has the following structure.
 Image
The partition key is set to the OrderId attribute.
Users report that when they perform queries that retrieve data by ProductName, the queries take longer than expected to complete.
You need to reduce the amount of time it takes to execute the problematic queries.
Solution: You increase the Request Units (RUs) for the database.
Does this meet the goal?

A. Yes

B. No

 


Suggested Answer: A

To scale the provisioned throughput for your application, you can increase or decrease the number of RUs at any time.
Note: The cost of all database operations is normalized by Azure Cosmos DB and is expressed by Request Units (or RUs, for short). You can think of RUs per second as the currency for throughput. RUs per second is a rate-based currency. It abstracts the system resources such as CPU, IOPS, and memory that are required to perform the database operations supported by Azure Cosmos DB.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/request-units

Question 17

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
A company uses Azure Data Lake Gen 1 Storage to store big data related to consumer behavior.
You need to implement logging.
Solution: Use information stored in Azure Active Directory reports.
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: B

Instead configure Azure Data Lake Storage diagnostics to store logs and metrics in a storage account.
Note:
You can enable diagnostic logging for your Azure Data Lake Storage Gen1 accounts, blobs, files, queues and tables.
Diagnostic logs aren’t available for Data Lake Storage Gen2 accounts [as of August 2019].
References:
https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-diagnostic-logs
https://github.com/MicrosoftDocs/azure-docs/issues/34286

Question 18

You are monitoring an Azure Stream Analytics job.
You discover that the Backlogged Input Events metric is increasing slowly and is consistently non-zero.
You need to ensure that the job can handle all the events.
What should you do?

A. Change the compatibility level of the Stream Analytics job.

B. Increase the number of streaming units (SUs).

C. Create an additional output stream for the existing input stream.

D. Remove any named consumer groups from the connection and use $default.

 


Suggested Answer: B

Backlogged Input Events: Number of input events that are backlogged. A non-zero value for this metric implies that your job isn’t able to keep up with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job. You should increase the Streaming Units.
Note: 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.
Reference:
https://docs.microsoft.com/bs-cyrl-ba/azure/stream-analytics/stream-analytics-monitoring

Question 19

DRAG DROP -
A company builds an application to allow developers to share and compare code. The conversations, code snippets, and links shared by people in the application are stored in a Microsoft Azure SQL Database instance. The application allows for searches of historical conversations and code snippets.
When users share code snippets, the code snippet is compared against previously share code snippets by using a combination of Transact-SQL functions including SUBSTRING, FIRST_VALUE, and SQRT. If a match is found, a link to the match is added to the conversation.
Customers report the following issues:
✑ Delays occur during live conversations
✑ A delay occurs before matching links appear after code snippets are added to conversations
You need to resolve the performance issues.
Which technologies should you use? To answer, drag the appropriate technologies to the correct issues. Each technology 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:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: memory-optimized table –
In-Memory OLTP can provide great performance benefits for transaction processing, data ingestion, and transient data scenarios.
Box 2: materialized view –
To support efficient querying, a common solution is to generate, in advance, a view that materializes the data in a format suited to the required results set. The
Materialized View pattern describes generating prepopulated views of data in environments where the source data isn’t in a suitable format for querying, where generating a suitable query is difficult, or where query performance is poor due to the nature of the data or the data store.
These materialized views, which only contain data required by a query, allow applications to quickly obtain the information they need. In addition to joining tables or combining data entities, materialized views can include the current values of calculated columns or data items, the results of combining values or executing transformations on the data items, and values specified as part of the query. A materialized view can even be optimized for just a single query.
References:
https://docs.microsoft.com/en-us/azure/architecture/patterns/materialized-view

Question 20

You manage a process that performs analysis of daily web traffic logs on an HDInsight cluster. Each of the 250 web servers generates approximately 10 megabytes (MB) of log data each day. All log data is stored in a single folder in Microsoft Azure Data Lake Storage Gen 2.
You need to improve the performance of the process.
Which two changes should you make? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Combine the daily log files for all servers into one file

B. Increase the value of the mapreduce.map.memory parameter

C. Move the log files into folders so that each day’s logs are in their own folder

D. Increase the number of worker nodes

E. Increase the value of the hive.tez.container.size parameter

 


Suggested Answer: AC

A: Typically, analytics engines such as HDInsight and Azure Data Lake Analytics have a per-file overhead. If you store your data as many small files, this can negatively affect performance. In general, organize your data into larger sized files for better performance (256MB to 100GB in size). Some engines and applications might have trouble efficiently processing files that are greater than 100GB in size.
C: For Hive workloads, partition pruning of time-series data can help some queries read only a subset of the data which improves performance.
Those pipelines that ingest time-series data, often place their files with a very structured naming for files and folders. Below is a very common example we see for data that is structured by date:
DataSetYYYYMMDDdatafile_YYYY_MM_DD.tsv
Notice that the datetime information appears both as folders and in the filename.
References:
https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-performance-tuning-guidance

Question 21

You have an Azure Data Lake Storage Gen2 account. You have a number of CSV files loaded in the account. Each file has a header row. After the header row is a property that is formatted by carriage return (/r) and line feed (/n).
You need to load the files daily as a batch into Azure SQL Data warehouse using Polybase. You have to skip the header row when the files are imported.
Which of the following actions would you take to implement this requirement? (Choose three.)

A. Create an external data source and ensure to use the abfs location

B. Create an external data source and ensure to use the Hadoop location

C. Create an external file format and set the First_row option

D. Create a database scoped credential that uses OAuth2 token and a key

E. Use the CREATE EXTERNAL TABLE AS SELECT and create a view that removes the empty row

 


Suggested Answer: ACD

The Microsoft documentation highlights the steps required to load data from Azure Data Lake Gen2 to an Azure SQL Data warehouse.
One of the steps is to create a database scoped credential:
Reference Image
Another step is to create the external data source using ‘abfs’ as the file location:
Create the external data source –
Use this CREATE EXTERNAL DATA SOURCE command to store the location of the data.
Reference Image
And you can use the FIRST_ROW parameter to skip the first row of the file.
FIRST_ROW = First_row_int –
Specifies the row number that is read first in all files during a PolyBase load. This parameter can take values 1-15. If the value is set to two, the first row in every file (header row) is skipped when the data is loaded. Rows are skipped based on the existence of row terminators (/r/n, /r, /n). When this option is used for export, rows are added to the data to make sure the file can be read with no data loss. If the value is set to >2, the first row exported is the Column names of the external table.
Reference: alt=”Reference Image” />
Another step is to create the external data source using ‘abfs’ as the file location:
Create the external data source –
Use this CREATE EXTERNAL DATA SOURCE command to store the location of the data.
<img src=”https://www.examtopics.com/assets/media/exam-media/03872/0060600001.jpg” alt=”Reference Image” />
And you can use the FIRST_ROW parameter to skip the first row of the file.
FIRST_ROW = First_row_int –
Specifies the row number that is read first in all files during a PolyBase load. This parameter can take values 1-15. If the value is set to two, the first row in every file (header row) is skipped when the data is loaded. Rows are skipped based on the existence of row terminators (/r/n, /r, /n). When this option is used for export, rows are added to the data to make sure the file can be read with no data loss. If the value is set to >2, the first row exported is the Column names of the external table.
Reference:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15

Question 22

A company has an Azure SQL Datawarehouse. They have a table named whizlab_salesfact that contains data for the past 12 months. The data is partitioned by month. The table contains around a billion rows. The table has clustered columnstore indexes. At the beginning of each month you need to remove the data from the table that is older than 12 months.
Which of the following actions would you implement for this requirement? (Choose three.)

A. Create a new empty table named XYZ_salesfact_new that has the same schema as XYZ_salesfact

B. Drop the XYZ_salesfact_new table

C. Copy the data to the new table by using CREATE TABLE AS SELECT (CTAS)

D. Truncate the partition containing the stale data

E. Switch the partition containing the stale data from XYZ_salesfact to XYZ_salesfact_new

F. Execute the DELETE statement where the value in the Date column is greater than 12 months

 


Suggested Answer: BCE

An example of this is given in a blog post. To achieve this, we first need to copy the data onto a new table using the ג€CREATE TABLE AS SELECTג€ command.
Then we switch the partition and then delete the staging table.
Option ג€Create a new empty table named XYZ_salesfact_new that has the same schema as XYZ_salesfactג€ is incorrect because we also need to copy the data onto the new table.
Option ג€Truncate the partition containing the stale dataג€ is incorrect because we need to switch the partition.
Option ג€Execute the DELETE statement where the value in the Date column is greater than 12 monthsג€ is incorrect because issuing the DELETE statement would take time.
Reference:
https://blogs.msdn.microsoft.com/apsblog/2018/06/18/azure-sql-dw-performance-ctaspartition-switching-vs-updatedelete/

Question 23

SIMULATION -
 Image
Use the following login credentials as needed:
Azure Username: xxxxx -
Azure Password: xxxxx -
The following information is for technical support purposes only:
Lab Instance: 10277521 -
You plan to create large data sets on db2.
You need to ensure that missing indexes are created automatically by Azure in db2. The solution must apply ONLY to db2.
To complete this task, sign in to the Azure portal.

 


Suggested Answer: See the explanation below.

1. To enable automatic tuning on Azure SQL Database logical server, navigate to the server in Azure portal and then select Automatic tuning in the menu.
Reference Image
2. Select database db2
3. Click the Apply button
Reference: alt=”Reference Image” />
2. Select database db2
3. Click the Apply button
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable

Question 24

You have to implement Azure Stream Analytics Functions as part of your data streaming solution.
The solution has the following requirements:
- Segment the data stream into distinct time segments that do not repeat or overlap
- Segment the data stream into distinct time segments that repeat and can overlap
- Segment the data stream to produce an output when an event occurs
Which of the following windowing function would you use for the following requirement?
`Segment the data stream into distinct time segments that do not repeat or overlap`

A. Hopping

B. Session

C. Sliding

D. Tumbling

 


Suggested Answer: D

You need to use the Tumbling windowing function for this requirement.
The Microsoft documentation mentions the following:
Tumbling window –
Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.
Reference Image
Since this is clearly given in the documentation, all other options are incorrect.
Reference: alt=”Reference Image” />
Since this is clearly given in the documentation, all other options are incorrect.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-window-functions

Question 25

You have an Azure Cosmos DB database that uses the SQL API.
You need to delete stale data from the database automatically.
What should you use?

A. soft delete

B. Low Latency Analytical Processing (LLAP)

C. schema on read

D. Time to Live (TTL)

 


Suggested Answer: D

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.microsoft.com/en-us/azure/cosmos-db/time-to-live

Question 26

Overview -
XYZ is an online training provider.
Current Environment -
The company currently has Microsoft SQL databases that are split into different categories or tiers. Some of the databases are used by Internal users, some by external partners and external distributions.
Below is the List of applications, tiers and their individual requirements:
 Image
Below are the current requirements of the company:
* For Tier 4 and Tier 5 databases, the backup strategy must include the following:
- Transactional log backup every hour
- Differential backup every day
- Full backup every week
* Backup strategies must be in place for all standalone Azure SQL databases using methods available with Azure SQL databases
* Tier 1 database must implement the following data masking logic:
- For Data type XYZ-A `" Mask 4 or less string data type characters
- For Data type XYZ-B `" Expose the first letter and mask the domain
- For Data type XYZ-C `" Mask everything except characters at the beginning and the end
* All certificates and keys are internally managed in on-premise data stores
* For Tier 2 databases, if there are any conflicts between the data transfer from on-premise, preference should be given to on-premise data.
* Monitoring must be setup on every database
* Applications with Tiers 6 through 8 must ensure that unexpected resource storage usage is immediately reported to IT data engineers.
* Azure SQL Data warehouse would be used to gather data from multiple internal and external databases.
* The Azure SQL Data warehouse must be optimized to use data from its cache
* The below metrics must be available when it comes to the cache:
- Metric XYZ-A `" Low cache hit %, high cache usage %
- Metric XYZ-B `" Low cache hit %, low cache usage %
- Metric XYZ-C `" high cache hit %, high cache usage %
* The reporting data for external partners must be stored in Azure storage. The data should be made available during regular business hours in connecting regions.
* The reporting for Tier 9 needs to be moved to Event Hubs.
* The reporting for Tier 10 needs to be moved to Azure Blobs.
The following issues have been identified in the setup:
* The External partners have control over the data formats, types and schemas
* For External based clients, the queries can't be changed or optimized
* The database development staff are familiar with T-SQL language
* Because of the size and amount of data, some applications and reporting features are not performing at SLA levels.
You have to implement logging for monitoring the data warehousing solution.
Which of the following would you log?

A. Requeststeps

B. DmWorkers

C. SQLRequest

D. ExecRequest

 


Suggested Answer: C

Since the SQL requests would affect the cache, these requests need to be monitored
The Microsoft documentation mentions the following on caching:
Cache hit and used percentage –
The matrix below describes scenarios based on the values of the cache metrics:
Reference Image
Scenario 1: You are optimally using your cache. Troubleshoot other areas which may be slowing down your queries.
Scenario 2: Your current working data set cannot fit into the cache which causes a low cache hit percentage due to physical reads. Consider scaling up your performance level and rerun your workload to populate the cache.
Scenario 3: It is likely that your query is running slow due to reasons unrelated to the cache. Troubleshoot other areas which may be slowing down your queries.
You can also consider scaling down your instance to reduce your cache size to save costs.
Scenario 4: You had a cold cache which could be the reason why your query was slow. Consider rerunning your query as your working dataset should now be in cached.
Since this is the ideal metric to monitor, all other options are incorrect.
Reference: alt=”Reference Image” />
Scenario 1: You are optimally using your cache. Troubleshoot other areas which may be slowing down your queries.
Scenario 2: Your current working data set cannot fit into the cache which causes a low cache hit percentage due to physical reads. Consider scaling up your performance level and rerun your workload to populate the cache.
Scenario 3: It is likely that your query is running slow due to reasons unrelated to the cache. Troubleshoot other areas which may be slowing down your queries.
You can also consider scaling down your instance to reduce your cache size to save costs.
Scenario 4: You had a cold cache which could be the reason why your query was slow. Consider rerunning your query as your working dataset should now be in cached.
Since this is the ideal metric to monitor, all other options are incorrect.
Reference:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-how-to-monitor-cache

Question 27

HOTSPOT -
You have an Azure Cosmos DB database.
You need to use Azure Stream Analytics to check for uneven distributions of queries that can affect performance.
Which two settings should you configure? To answer, select the appropriate settings in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: RIGHT –
Use right for dates.
1- RIGHT means =
2- LEFT means .
Box 2: 20090101, 201001010, 20110101, 20120101
Four values are better than three or two.
Reference:
https://medium.com/@selcukkilinc23/what-it-means-range-right-and-left-in-table-partitioning-2d654cb99ade

Question 28

You need to deploy a Microsoft Azure Stream Analytics job for an IoT based solution. The solution must minimize latency. The solution must also minimize the bandwidth usage between the job and the IoT device.
Which of the following actions must you perform for this requirement? (Choose four.)

A. Ensure to configure routes

B. Create an Azure Blob storage container

C. Configure Streaming Units

D. Create an IoT Hub and add the Azure Stream Analytics modules to the IoT Hub namespace

E. Create an Azure Stream Analytics edge job and configure job definition save location

F. Create an Azure Stream Analytics cloud job and configure job definition save location

 


Suggested Answer: ABDF

There is an article in the Microsoft documentation on configuring Azure Stream Analytics on IoT Edge devices.
You need to have a storage container for the job definition:
Installation instructions –
The high-level steps are described in the following table. More details are given in the following sections.
Reference Image
You also need to create a cloud part job definition:
Reference Image
You also need to set the modules for your IoT edge device:
Deployment ASA on your IoT Edge device(s)
Add ASA to your deployment –
ג€¢ In the Azure portal, open IoT Hub, navigate to IoT Edge and click on the device you want to target for this deployment.
ג€¢ Select Set modules, then select + Add and choose Azure Stream Analytics Module.
ג€¢ Select the subscription and the ASA Edge job that you created. Click Save.
Reference Image
You also need to configure the Routes:
Configure routes –
IoT Edge provides a way to declaratively route messages between modules, and between modules and IoT Hub. The full syntax is described here. Names of the inputs and outputs created in the ASA job can be used as endpoints for routing.
Since this is clear from the Microsoft documentation, all other options are incorrect.
Reference: alt=”Reference Image” />
You also need to create a cloud part job definition:
Reference Image
You also need to set the modules for your IoT edge device:
Deployment ASA on your IoT Edge device(s)
Add ASA to your deployment –
ג€¢ In the Azure portal, open IoT Hub, navigate to IoT Edge and click on the device you want to target for this deployment.
ג€¢ Select Set modules, then select + Add and choose Azure Stream Analytics Module.
ג€¢ Select the subscription and the ASA Edge job that you created. Click Save.
<img src=”https://www.examtopics.com/assets/media/exam-media/03872/0056000001.jpg” alt=”Reference Image” />
You also need to configure the Routes:
Configure routes –
IoT Edge provides a way to declaratively route messages between modules, and between modules and IoT Hub. The full syntax is described here. Names of the inputs and outputs created in the ASA job can be used as endpoints for routing.
Since this is clear from the Microsoft documentation, all other options are incorrect.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-edge

Question 29

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You plan to create an Azure Databricks workspace that has a tiered structure. The workspace will contain the following three workloads:
✑ A workload for data engineers who will use Python and SQL
✑ A workload for jobs that will run notebooks that use Python, Scala, and SQL
✑ A workload that data scientists will use to perform ad hoc analysis in Scala and R
The enterprise architecture team at your company identifies the following standards for Databricks environments:
✑ The data engineers must share a cluster.
✑ The job cluster will be managed by using a request process whereby data scientists and data engineers provide packaged notebooks for deployment to the cluster.
✑ All the data scientists must be assigned their own cluster that terminates automatically after 120 minutes of inactivity. Currently, there are three data scientists.
You need to create the Databricks clusters for the workloads.
Solution: You create a High Concurrency cluster for each data scientist, a High Concurrency cluster for the data engineers, and a Standard cluster for the jobs.
Does this meet the goal?

A. Yes

B. No

 


Suggested Answer: B

No need for a High Concurrency cluster for each data scientist.
Standard clusters are recommended for a single user. Standard can run workloads developed in any language: Python, R, Scala, and SQL.
A high concurrency cluster is a managed cloud resource. The key benefits of high concurrency clusters are that they provide Apache Spark-native fine-grained sharing for maximum resource utilization and minimum query latencies.
References:
https://docs.azuredatabricks.net/clusters/configure.html

Question 30

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 a workload group using the Azure storage account name as the pool name
3. Load the data using the INSERT`¦SELECT statement
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: B

You need to create an external file format and external table using the external data source.
You then 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

Question 31

You have an Azure SQL database that contains a table named Customer. Customer contains the columns shown in the following table.
 Image
You apply a masking rule as shown in the following table.
 Image
Which users can view the email addresses of the customers?

A. Server administrators and all users who are granted the UNMASK permission to the Customer_Email column only.

B. All users who are granted the UNMASK permission to the Customer_Email column only.

C. Server administrators only.

D. Server administrators and all users who are granted the SELECT permission to the Customer_Email column only.

 


Suggested Answer: B

Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

Question 32

HOTSPOT -
You develop data engineering solutions for a company. An application creates a database on Microsoft Azure. You have the following code:
 Image
Which database and authorization types are used? To answer, select the appropriate option in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: Azure Cosmos DB –
The DocumentClient.CreateDatabaseAsync(Database, RequestOptions) method creates a database resource as an asychronous operation in the Azure Cosmos
DB service.
Box 2: Master Key –
Azure Cosmos DB uses two types of keys to authenticate users and provide access to its data and resources: Master Key, Resource Tokens
Master keys provide access to the all the administrative resources for the database account. Master keys:
Provide access to accounts, databases, users, and permissions.
Reference Image
✑ Cannot be used to provide granular access to containers and documents.
✑ Are created during the creation of an account.
✑ Can be regenerated at any time.
Incorrect Answers:
Resource Token: Resource tokens provide access to the application resources within a database.
References: alt=”Reference Image” />
✑ Cannot be used to provide granular access to containers and documents.
✑ Are created during the creation of an account.
✑ Can be regenerated at any time.
Incorrect Answers:
Resource Token: Resource tokens provide access to the application resources within a database.
References:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.documents.client.documentclient.createdatabaseasync
https://docs.microsoft.com/en-us/azure/cosmos-db/secure-access-to-data

Question 33

HOTSPOT -
You have an Azure subscription that contains the following resources:
✑ An Azure Active Directory (Azure AD) tenant that contains a security group named Group1
✑ An Azure Synapse Analytics SQL pool named Pool1
You need to control the access of Group1 to specific columns and rows in a table in Pool1.
Which Transact-SQL commands should you use? To answer, select the appropriate options in the answer area.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: GRANT –
You can implement column-level security with the GRANT T-SQL statement.
Box 2: CREATE SECURITY POLICY –
Implement Row Level Security by using the CREATE SECURITY POLICY Transact-SQL statement
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/column-level-security

Question 34

You have an Azure Blob storage account.
Developers report that an HTTP 403 (Forbidden) error is generated when a client application attempts to access the storage account. You cannot see the error messages in Azure Monitor.
What is a possible cause of the error?

A. The client application is using an expired shared access signature (SAS) when it sends a storage request.

B. The client application deleted, and then immediately recreated a blob container that has the same name.

C. The client application attempted to use a shared access signature (SAS) that did not have the necessary permissions.

D. The client application attempted to use a blob that does not exist in the storage service.

 


Suggested Answer: C

Reference:
https://docs.microsoft.com/en-us/rest/api/storageservices/sas-error-codes

Question 35

You have an Azure subscription the contains the resources shown in the following table:
 Image
All the resources have the default encryption settings.
You need to ensure that all the data stored in the resources is encrypted at rest.
What should you do?

A. Enable Azure Storage encryption for storageaccount1.

B. Enable Transparent Data Encryption (TDE) for synapsedb1.

C. Enable Azure Storage encryption for storageaccount2.

D. Enable encryption at rest for cosmosdb1.

 


Suggested Answer: B

Incorrect answers:
A, C: Azure Disks, and data in Azure Storage accounts are automatically encrypted at rest by default
D: All user data stored in Azure Cosmos DB is encrypted at rest by default
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-overview
https://docs.microsoft.com/en-us/azure/synapse-analytics/security/workspaces-encryption
https://docs.microsoft.com/en-us/azure/cosmos-db/database-encryption-at-rest

Question 36

DRAG DROP -
You have an ASP.NET web app that uses an Azure SQL database. The database contains a table named Employee. The table contains sensitive employee information, including a column named DateOfBirth.
You need to ensure that the data in the DateOfBirth column is encrypted both in the database and when transmitted between a client and Azure. Only authorized clients must be able to view the data in the column.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions in the answer area and arrange them in the correct order.
Select and Place:
 Image

 


Suggested Answer:
Correct Answer Image

Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted

Question 37

You have a data warehouse in Azure Synapse Analytics.
You need to ensure that the data in the data warehouse is encrypted at rest.
What should you enable?

A. Transparent Data Encryption (TDE)

B. Secure transfer required

C. Always Encrypted for all columns

D. Advanced Data Security for this database

 


Suggested Answer: A

Azure SQL Database currently supports encryption at rest for Microsoft-managed service side and client-side encryption scenarios.
✑ Support for server encryption is currently provided through the SQL feature called Transparent Data Encryption.
✑ Client-side encryption of Azure SQL Database data is supported through the Always Encrypted feature.
Reference:
https://docs.microsoft.com/en-us/azure/security/fundamentals/encryption-atrest

Question 38

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 a remote service binding pointing to the Azure Data Lake Gen 2 storage account
2. Create an external file format and external table using the external data source
3. Load the data using the CREATE TABLE AS SELECT statement
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: B

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

Question 39

You have an Azure SQL server named Server1 that hosts two development databases named DB1 and DB2.
You have an administrative workstation that has an IP address of 192.168.8.8. The development team at your company has an IP addresses in the range of
192.168.8.1 to 192.168.8.5.
You need to set up firewall rules to meet the following requirements:
✑ Allows connection from your workstation to both databases.
✑ The development team must be able connect to DB1 but must be prevented from connecting to DB2.
✑ Web services running in Azure must be able to connect to DB1 but must be prevented from connecting to DB2.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Create a firewall rule on DB1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.

B. Create a firewall rule on DB1 that has a start and end IP address of 0.0.0.0.

C. Create a firewall rule on Server1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.

D. Create a firewall rule on DB1 that has a start and end IP address of 192.168.8.8.

E. Create a firewall rule on Server1 that has a start and end IP address of 192.168.8.8.

 


Suggested Answer: ACE

 

Question 40

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 Data Lake Gen 2 storage account
2. Create an external file format and external table using the external data source
3. Load the data using the CREATE TABLE AS SELECT statement
Does the solution meet the goal?

A. Yes

B. No

 


Suggested Answer: A

You need to create an external file format and external table using the external data source.
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

Question 41

HOTSPOT -
A company is planning to use Microsoft Azure Cosmos DB as the data store for an application. You have the following Azure CLI command: az cosmosdb create -`"name "cosmosdbdev1" `"-resource-group "rgdev"
You need to minimize latency and expose the SQL API. How should you complete the command? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: Eventual –
With Azure Cosmos DB, developers can choose from five well-defined consistency models on the consistency spectrum. From strongest to more relaxed, the models include strong, bounded staleness, session, consistent prefix, and eventual consistency.
The following image shows the different consistency levels as a spectrum.
Reference Image
Box 2: GlobalDocumentDB –
Select Core(SQL) to create a document database and query by using SQL syntax.
Note: The API determines the type of account to create. Azure Cosmos DB provides five APIs: Core(SQL) and MongoDB for document databases, Gremlin for graph databases, Azure Table, and Cassandra.
References: alt=”Reference Image” />
Box 2: GlobalDocumentDB –
Select Core(SQL) to create a document database and query by using SQL syntax.
Note: The API determines the type of account to create. Azure Cosmos DB provides five APIs: Core(SQL) and MongoDB for document databases, Gremlin for graph databases, Azure Table, and Cassandra.
References:
https://docs.microsoft.com/en-us/azure/cosmos-db/consistency-levels
https://docs.microsoft.com/en-us/azure/cosmos-db/create-sql-api-dotnet

Question 42

HOTSPOT -
A company is deploying a service-based data environment. You are developing a solution to process this data.
The solution must meet the following requirements:
✑ Use an Azure HDInsight cluster for data ingestion from a relational database in a different cloud service
✑ Use an Azure Data Lake Storage account to store processed data
✑ Allow users to download processed data
You need to recommend technologies for the solution.
Which technologies should you use? To answer, select the appropriate options in the answer area.
Hot Area:
 Image

 


Suggested Answer:
Correct Answer Image

Box 1: Apache Sqoop –
Apache Sqoop is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.
Azure HDInsight is a cloud distribution of the Hadoop components from the Hortonworks Data Platform (HDP).
Incorrect Answers:
DistCp (distributed copy) is a tool used for large inter/intra-cluster copying. It uses MapReduce to effect its distribution, error handling and recovery, and reporting.
It expands a list of files and directories into input to map tasks, each of which will copy a partition of the files specified in the source list. Its MapReduce pedigree has endowed it with some quirks in both its semantics and execution.
RevoScaleR is a collection of proprietary functions in Machine Learning Server used for practicing data science at scale. For data scientists, RevoScaleR gives you data-related functions for import, transformation and manipulation, summarization, visualization, and analysis.
Box 2: Apache Kafka –
Apache Kafka is a distributed streaming platform.
A streaming platform has three key capabilities:
Publish and subscribe to streams of records, similar to a message queue or enterprise messaging system.
Store streams of records in a fault-tolerant durable way.
Process streams of records as they occur.
Kafka is generally used for two broad classes of applications:
Building real-time streaming data pipelines that reliably get data between systems or applications
Building real-time streaming applications that transform or react to the streams of data
Box 3: Ambari Hive View –
You can run Hive queries by using Apache Ambari Hive View. The Hive View allows you to author, optimize, and run Hive queries from your web browser.
References:
https://sqoop.apache.org/

https://kafka.apache.org/intro

https://docs.microsoft.com/en-us/azure/hdinsight/hadoop/apache-hadoop-use-hive-ambari-view

Question 43

You manage a solution that uses Azure HDInsight clusters.
You need to implement a solution to monitor cluster performance and status.
Which technology should you use?

A. Azure HDInsight .NET SDK

B. Azure HDInsight REST API

C. Ambari REST API

D. Azure Log Analytics

E. Ambari Web UI

 


Suggested Answer: E

Ambari is the recommended tool for monitoring utilization across the whole cluster. The Ambari dashboard shows easily glanceable widgets that display metrics such as CPU, network, YARN memory, and HDFS disk usage. The specific metrics shown depend on cluster type. The ג€Hostsג€ tab shows metrics for individual nodes so you can ensure the load on your cluster is evenly distributed.
The Apache Ambari project is aimed at making Hadoop management simpler by developing software for provisioning, managing, and monitoring Apache Hadoop clusters. Ambari provides an intuitive, easy-to-use Hadoop management web UI backed by its RESTful APIs.
References:
https://azure.microsoft.com/en-us/blog/monitoring-on-hdinsight-part-1-an-overview/
https://ambari.apache.org/

Question 44

Each day, company plans to store hundreds of files in Azure Blob Storage and Azure Data Lake Storage. The company uses the parquet format.
You must develop a pipeline that meets the following requirements:
✑ Process data every six hours
✑ Offer interactive data analysis capabilities
✑ Offer the ability to process data using solid-state drive (SSD) caching
✑ Use Directed Acyclic Graph(DAG) processing mechanisms
✑ Provide support for REST API calls to monitor processes
✑ Provide native support for Python
✑ Integrate with Microsoft Power BI
You need to select the appropriate data technology to implement the pipeline.
Which data technology should you implement?

A. Azure SQL Data Warehouse

B. HDInsight Apache Storm cluster

C. Azure Stream Analytics

D. HDInsight Apache Hadoop cluster using MapReduce

E. HDInsight Spark cluster

 


Suggested Answer: B

Storm runs topologies instead of the Apache Hadoop MapReduce jobs that you might be familiar with. Storm topologies are composed of multiple components that are arranged in a directed acyclic graph (DAG). Data flows between the components in the graph. Each component consumes one or more data streams, and can optionally emit one or more streams.
Python can be used to develop Storm components.
References:
https://docs.microsoft.com/en-us/azure/hdinsight/storm/apache-storm-overview

Question 45

Your company uses Azure Stream Analytics to monitor devices.
The company plans to double the number of devices that are monitored.
You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?

A. Input Deserialization Errors

B. Early Input Events

C. Late Input Events

D. Watermark delay

 


Suggested Answer: D

There are a number of other resource constraints that can cause the streaming pipeline to slow down. The watermark delay metric can rise due to:
✑ Not enough processing resources in Stream Analytics to handle the volume of input events.
✑ Not enough throughput within the input event brokers, so they are throttled.
✑ Output sinks are not provisioned with enough capacity, so they are throttled. The possible solutions vary widely based on the flavor of output service being used.
Incorrect Answers:
A: Deserialization issues are caused when the input stream of your Stream Analytics job contains malformed messages.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling

Question 46

SIMULATION -
 Image
Use the following login credentials as needed:
Azure Username: xxxxx -
Azure Password: xxxxx -
The following information is for technical support purposes only:
Lab Instance: 10277521 -
You plan to create multiple pipelines in a new Azure Data Factory V2.
You need to create the data factory, and then create a scheduled trigger for the planned pipelines. The trigger must execute every two hours starting at 24:00:00.
To complete this task, sign in to the Azure portal.

 


Suggested Answer: See the explanation below.

Step 1: Create a new Azure Data Factory V2
1. Go to the Azure portal.
2. Select Create a resource on the left menu, select Analytics, and then select Data Factory.
Reference Image
4. On the New data factory page, enter a name.
5. For Subscription, select your Azure subscription in which you want to create the data factory.
6. For Resource Group, use one of the following steps:
✑ Select Use existing, and select an existing resource group from the list.
✑ Select Create new, and enter the name of a resource group.
7. For Version, select V2.
8. For Location, select the location for the data factory.
9. Select Create.
10. After the creation is complete, you see the Data Factory page.
Step 2: Create a schedule trigger for the Data Factory
1. Select the Data Factory you created, and switch to the Edit tab.
Reference Image
2. Click Trigger on the menu, and click New/Edit.
Reference Image
3. In the Add Triggers page, click Choose trigger…, and click New.
Reference Image
4. In the New Trigger page, do the following steps:
a. Confirm that Schedule is selected for Type.
b. Specify the start datetime of the trigger for Start Date (UTC) to: 24:00:00 c. Specify Recurrence for the trigger. Select Every Hour, and enter 2 in the text box.
Reference Image
5. In the New Trigger window, check the Activated option, and click Next.
6. In the New Trigger page, review the warning message, and click Finish.
7. Click Publish to publish changes to Data Factory. Until you publish changes to Data Factory, the trigger does not start triggering the pipeline runs.
Reference Image
References: alt=”Reference Image” />
4. On the New data factory page, enter a name.
5. For Subscription, select your Azure subscription in which you want to create the data factory.
6. For Resource Group, use one of the following steps:
✑ Select Use existing, and select an existing resource group from the list.
✑ Select Create new, and enter the name of a resource group.
7. For Version, select V2.
8. For Location, select the location for the data factory.
9. Select Create.
10. After the creation is complete, you see the Data Factory page.
Step 2: Create a schedule trigger for the Data Factory
1. Select the Data Factory you created, and switch to the Edit tab.
Reference Image
2. Click Trigger on the menu, and click New/Edit.
Reference Image
3. In the Add Triggers page, click Choose trigger…, and click New.
Reference Image
4. In the New Trigger page, do the following steps:
a. Confirm that Schedule is selected for Type.
b. Specify the start datetime of the trigger for Start Date (UTC) to: 24:00:00 c. Specify Recurrence for the trigger. Select Every Hour, and enter 2 in the text box.
Reference Image
5. In the New Trigger window, check the Activated option, and click Next.
6. In the New Trigger page, review the warning message, and click Finish.
7. Click Publish to publish changes to Data Factory. Until you publish changes to Data Factory, the trigger does not start triggering the pipeline runs.
<img src=”https://www.examtopics.com/assets/media/exam-media/03872/0017700001.png” alt=”Reference Image” />
References:
https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal
https://docs.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger

Question 47

SIMULATION -
 Image
Use the following login credentials as needed:
Azure Username: xxxxx -
Azure Password: xxxxx -
The following information is for technical support purposes only:
Lab Instance: 10277521 -
You plan to generate large amounts of real-time data that will be copied to Azure Blob storage.
You plan to create reports that will read the data from an Azure Cosmos DB database.
You need to create an Azure Stream Analytics job that will input the data from a blob storage named storage10277521 to the Cosmos DB database.
To complete this task, sign in to the Azure portal.

 


Suggested Answer: See the explanation below.

Step 1: Create a Stream Analytics job
1. Sign in to the Azure portal.
2. Select Create a resource in the upper left-hand corner of the Azure portal.
3. Select Analytics > Stream Analytics job from the results list.
4. Fill out the Stream Analytics job page.
Reference Image
5. Check the Pin to dashboard box to place your job on your dashboard and then select Create.
6. You should see a Deployment in progress… notification displayed in the top right of your browser window.
Step 2: Configure job input –
1. Navigate to your Stream Analytics job.
2. Select Inputs > Add Stream input > Azure Blob storage
Reference Image
3. In the Azure Blob storage setting choose: storage10277521. Leave other options to default values and select Save to save the settings.
Reference: alt=”Reference Image” />
5. Check the Pin to dashboard box to place your job on your dashboard and then select Create.
6. You should see a Deployment in progress… notification displayed in the top right of your browser window.
Step 2: Configure job input –
1. Navigate to your Stream Analytics job.
2. Select Inputs > Add Stream input > Azure Blob storage
<img src=”https://www.examtopics.com/assets/media/exam-media/03872/0016200001.jpg” alt=”Reference Image” />
3. In the Azure Blob storage setting choose: storage10277521. Leave other options to default values and select Save to save the settings.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-quick-create-portal

Question 48

You have an Azure Stream Analytics query. The query returns a result set that contains 10,000 distinct values for a column named clusterID.
You monitor the Stream Analytics job and discover high latency.
You need to reduce the latency.
Which two actions should you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Add a pass-through query.

B. Add a temporal analytic function.

C. Scale out the query by using PARTITION BY.

D. Convert the query to a reference query.

E. Increase the number of streaming units.

 


Suggested Answer: CE

C: Scaling a Stream Analytics job takes advantage of partitions in the input or output. Partitioning lets you divide data into subsets based on a partition key. A process that consumes the data (such as a Streaming Analytics job) can consume and write different partitions in parallel, which increases throughput.
E: 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-parallelization
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-streaming-unit-consumption

Question 49

A company plans to use Azure SQL Database to support a mission-critical application.
The application must be highly available without performance degradation during maintenance windows.
You need to implement the solution.
Which three technologies should you implement? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Premium service tier

B. Virtual machine Scale Sets

C. Basic service tier

D. SQL Data Sync

E. Always On availability groups

F. Zone-redundant configuration

 


Suggested Answer: AEF

A: Premium/business critical service tier model that is based on a cluster of database engine processes. This architectural model relies on a fact that there is always a quorum of available database engine nodes and has minimal performance impact on your workload even during maintenance activities.
E: In the premium model, Azure SQL database integrates compute and storage on the single node. High availability in this architectural model is achieved by replication of compute (SQL Server Database Engine process) and storage (locally attached SSD) deployed in 4-node cluster, using technology similar to SQL
Server Always On Availability Groups.
Reference Image
F: Zone redundant configuration –
By default, the quorum-set replicas for the local storage configurations are created in the same datacenter. With the introduction of Azure Availability Zones, you have the ability to place the different replicas in the quorum-sets to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).
References: alt=”Reference Image” />
F: Zone redundant configuration –
By default, the quorum-set replicas for the local storage configurations are created in the same datacenter. With the introduction of Azure Availability Zones, you have the ability to place the different replicas in the quorum-sets to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability

Question 50

DRAG DROP -
Your company plans to create an event processing engine to handle streaming data from Twitter.
The data engineering team uses Azure Event Hubs to ingest the streaming data.
You need to implement a solution that uses Azure Databricks to receive the streaming data from the Azure Event Hubs.
Which three actions should you recommend be performed 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:
 Image

 


Suggested Answer:
Correct Answer Image

Step 1: Deploy the Azure Databricks service
Create an Azure Databricks workspace by setting up an Azure Databricks Service.
Step 2: Deploy a Spark cluster and then attach the required libraries to the cluster.
To create a Spark cluster in Databricks, in the Azure portal, go to the Databricks workspace that you created, and then select Launch Workspace.
Attach libraries to Spark cluster: you use the Twitter APIs to send tweets to Event Hubs. You also use the Apache Spark Event Hubs connector to read and write data into Azure Event Hubs. To use these APIs as part of your cluster, add them as libraries to Azure Databricks and associate them with your Spark cluster.
Step 3: Create and configure a Notebook that consumes the streaming data.
You create a notebook named ReadTweetsFromEventhub in Databricks workspace. ReadTweetsFromEventHub is a consumer notebook you use to read the tweets from Event Hubs.
References:
https://docs.microsoft.com/en-us/azure/azure-databricks/databricks-stream-from-eventhubs

Get More DP-200 Practice Questions

If you’re looking for more DP-200 practice test free questions, click here to access the full DP-200 practice test.

We regularly update this page with new practice questions, so be sure to check back frequently.

Good luck with your DP-200 certification journey!

Share18Tweet11
Previous Post

DOP-C02 Practice Test Free

Next Post

DP-201 Practice Test Free

Next Post

DP-201 Practice Test Free

DP-203 Practice Test Free

DP-500 Practice Test Free

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recommended

DP-100 Practice Test Free

XK0-005 Practice Test Free

XK0-004 Practice Test Free

SY0-701 Practice Test Free

SY0-601 Practice Test Free

SY0-501 Practice Test Free

  • About
  • DMCA
  • Privacy & Policy
  • Contact

ITexamsolutions.net Materials do not contain actual questions and answers from Cisco's Certification Exams. ITexamsolutions.net doesn't offer Real Microsoft Exam Questions. ITexamsolutions.net doesn't offer Real Amazon Exam Questions.

  • Login
  • Sign Up
No Result
View All Result
  • Quesions
    • Cisco
    • AWS
    • Microsoft
    • CompTIA
    • Google
    • ISACA
    • ECCouncil
    • F5
    • GIAC
    • ISC
    • Juniper
    • LPI
    • Oracle
    • Palo Alto Networks
    • PMI
    • RedHat
    • Salesforce
    • VMware
  • Courses
    • CCNA
    • ENCOR
    • VMware vSphere
  • Certificates

Welcome Back!

Login to your account below

Forgotten Password? Sign Up

Create New Account!

Fill the forms below to register

All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.