Updated Microsoft MCSA 70-475 Exam Questions

1. Overview:

Relecloud is a social media company that processes hundreds of millions of social media posts per day and sells advertisements to several hundred companies.

Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.

Relecloud has two main offices. The offices are located in San Francisco and New York City.

The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.

Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame. The highest trending topics generate the highest advertising revenue.

Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide real-time data on trending topics, current advertising rates, and advertising costs for a given month.

Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long-term trending.

Requirements:

Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near real-time.

Relecloud plans to implement a new streaming analytics platform that will report on trending topics.

Relecloud plans to implement a data warehouse named DB2.

Relecloud identifies the following technical requirements:

- Social media data must be analyzed to identify trending topics in real-time.

- The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.

- The real-time solution used to analyze the social media data must support scaling up and down without service interruption.

Relecloud identifies the following technical requirements for the advertisers:

- The advertisers must be able to see only their own data in the Power BI reports.

- The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.

- The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the real-time streaming solution.

- Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the advertisers to which they are assigned.

- The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting rows for the advertisers to which they are not assigned.

- The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers, and then to upload the file to Azure Blob storage.

Relecloud identifies the following requirements for DB1:

- Data generated by the streaming analytics platform must be stored in DB1.

- The user names of the advertisers must be mapped to CustomerID in a table named Table2.

- The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.

- The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.

Relecloud identifies the following requirements for DB2:

- DB2 must have minimal storage costs.

- DB2 must run load processes in parallel.

- DB2 must support massive parallel processing.

- DB2 must be able to store more than 40 TB of data.

- DB2 must support scaling up and down, as required.

- Data from DB1 must be archived in DB2 for long-term storage.

- All of the reports that are executed from DB2 must use aggregation.

- Users must be able to pause DB2 when the data warehouse is not in use.

- Users must be able to view previous versions of the data in DB2 by using aggregates.

Relecloud identifies the following requirements for extract, transformation, and load (ETL):

- Data movement between DB1 and DB2 must occur each hour.

- An email alert must be generated when a failure of any type occurs during ETL processing.

Sample code and data:

You execute the following code for a table named rls_table1.

You use the following code to create Table1.

create table table1

(customerid int,

salespersonid int

...

)

Go

The following is a sample of the streaming data.

Which technology should you recommend to meet the technical requirement for analyzing the social media data?

2. DRAG DROP

Overview:

Relecloud is a social media company that processes hundreds of millions of social media posts per day and sells advertisements to several hundred companies.

Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.

Relecloud has two main offices. The offices are located in San Francisco and New York City.

The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.

Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame. The highest trending topics generate the highest advertising revenue.

Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide real-time data on trending topics, current advertising rates, and advertising costs for a given month.

Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long-term trending.

Requirements:

Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near real-time.

Relecloud plans to implement a new streaming analytics platform that will report on trending topics.

Relecloud plans to implement a data warehouse named DB2.

Relecloud identifies the following technical requirements:

- Social media data must be analyzed to identify trending topics in real-time.

- The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.

- The real-time solution used to analyze the social media data must support scaling up and down without service interruption.

Relecloud identifies the following technical requirements for the advertisers:

- The advertisers must be able to see only their own data in the Power BI reports.

- The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.

- The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the real-time streaming solution.

- Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the advertisers to which they are assigned.

- The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting rows for the advertisers to which they are not assigned.

- The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers, and then to upload the file to Azure Blob storage.

Relecloud identifies the following requirements for DB1:

- Data generated by the streaming analytics platform must be stored in DB1.

- The user names of the advertisers must be mapped to CustomerID in a table named Table2.

- The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.

- The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.

Relecloud identifies the following requirements for DB2:

- DB2 must have minimal storage costs.

- DB2 must run load processes in parallel.

- DB2 must support massive parallel processing.

- DB2 must be able to store more than 40 TB of data.

- DB2 must support scaling up and down, as required.

- Data from DB1 must be archived in DB2 for long-term storage.

- All of the reports that are executed from DB2 must use aggregation.

- Users must be able to pause DB2 when the data warehouse is not in use.

- Users must be able to view previous versions of the data in DB2 by using aggregates.

Relecloud identifies the following requirements for extract, transformation, and load (ETL):

- Data movement between DB1 and DB2 must occur each hour.

- An email alert must be generated when a failure of any type occurs during ETL processing.

Sample code and data:

You execute the following code for a table named rls_table1.

You use the following code to create Table1.

create table table1

(customerid int,

salespersonid int

...

)

Go

The following is a sample of the streaming data.

You need to implement a solution that meets the data refresh requirement for DB1.

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

3. DRAG DROP

Overview:

Relecloud is a social media company that processes hundreds of millions of social media posts per day and sells advertisements to several hundred companies.

Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.

Relecloud has two main offices. The offices are located in San Francisco and New York City.

The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.

Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame. The highest trending topics generate the highest advertising revenue.

Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide real-time data on trending topics, current advertising rates, and advertising costs for a given month.

Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long-term trending.

Requirements:

Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near real-time.

Relecloud plans to implement a new streaming analytics platform that will report on trending topics.

Relecloud plans to implement a data warehouse named DB2.

Relecloud identifies the following technical requirements:

- Social media data must be analyzed to identify trending topics in real-time.

- The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.

- The real-time solution used to analyze the social media data must support scaling up and down without service interruption.

Relecloud identifies the following technical requirements for the advertisers:

- The advertisers must be able to see only their own data in the Power BI reports.

- The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.

- The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the real-time streaming solution.

- Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the advertisers to which they are assigned.

- The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting rows for the advertisers to which they are not assigned.

- The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers, and then to upload the file to Azure Blob storage.

Relecloud identifies the following requirements for DB1:

- Data generated by the streaming analytics platform must be stored in DB1.

- The user names of the advertisers must be mapped to CustomerID in a table named Table2.

- The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.

- The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.

Relecloud identifies the following requirements for DB2:

- DB2 must have minimal storage costs.

- DB2 must run load processes in parallel.

- DB2 must support massive parallel processing.

- DB2 must be able to store more than 40 TB of data.

- DB2 must support scaling up and down, as required.

- Data from DB1 must be archived in DB2 for long-term storage.

- All of the reports that are executed from DB2 must use aggregation.

- Users must be able to pause DB2 when the data warehouse is not in use.

- Users must be able to view previous versions of the data in DB2 by using aggregates.

Relecloud identifies the following requirements for extract, transformation, and load (ETL):

- Data movement between DB1 and DB2 must occur each hour.

- An email alert must be generated when a failure of any type occurs during ETL processing.

Sample code and data:

You execute the following code for a table named rls_table1.

You use the following code to create Table1.

create table table1

(customerid int,

salespersonid int

...

)

Go

The following is a sample of the streaming data.

You need to create a query that identifies the trending topics.

How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

4. HOTSPOT

Overview:

Relecloud is a social media company that processes hundreds of millions of social media posts per day and sells advertisements to several hundred companies.

Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.

Relecloud has two main offices. The offices are located in San Francisco and New York City.

The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.

Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame. The highest trending topics generate the highest advertising revenue.

Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide real-time data on trending topics, current advertising rates, and advertising costs for a given month.

Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long-term trending.

Requirements:

Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near real-time.

Relecloud plans to implement a new streaming analytics platform that will report on trending topics.

Relecloud plans to implement a data warehouse named DB2.

Relecloud identifies the following technical requirements:

- Social media data must be analyzed to identify trending topics in real-time.

- The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.

- The real-time solution used to analyze the social media data must support scaling up and down without service interruption.

Relecloud identifies the following technical requirements for the advertisers:

- The advertisers must be able to see only their own data in the Power BI reports.

- The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.

- The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the real-time streaming solution.

- Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the advertisers to which they are assigned.

- The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting rows for the advertisers to which they are not assigned.

- The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers, and then to upload the file to Azure Blob storage.

Relecloud identifies the following requirements for DB1:

- Data generated by the streaming analytics platform must be stored in DB1.

- The user names of the advertisers must be mapped to CustomerID in a table named Table2.

- The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.

- The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.

Relecloud identifies the following requirements for DB2:

- DB2 must have minimal storage costs.

- DB2 must run load processes in parallel.

- DB2 must support massive parallel processing.

- DB2 must be able to store more than 40 TB of data.

- DB2 must support scaling up and down, as required.

- Data from DB1 must be archived in DB2 for long-term storage.

- All of the reports that are executed from DB2 must use aggregation.

- Users must be able to pause DB2 when the data warehouse is not in use.

- Users must be able to view previous versions of the data in DB2 by using aggregates.

Relecloud identifies the following requirements for extract, transformation, and load (ETL):

- Data movement between DB1 and DB2 must occur each hour.

- An email alert must be generated when a failure of any type occurs during ETL processing.

Sample code and data:

You execute the following code for a table named rls_table1.

You use the following code to create Table1.

create table table1

(customerid int,

salespersonid int

...

)

Go

The following is a sample of the streaming data.

You implement DB2.

You need to configure the tables in DB2 to host the data from DB1. The solution must meet the requirements for DB2.

Which type of table and history table storage should you use for the tables? To answer, select the

appropriate options in the answer area. NOTE: Each correct selection is worth one point.

5. DRAG DROP

Overview:

Relecloud is a social media company that processes hundreds of millions of social media posts per day and sells advertisements to several hundred companies.

Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.

Relecloud has two main offices. The offices are located in San Francisco and New York City.

The offices connect to each other by using a site-to-site VPN. Each office connects directly to the Internet.

Relecloud modifies the pricing of its advertisements based on trending topics. Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame. The highest trending topics generate the highest advertising revenue.

Relecloud wants to deliver reports to the advertisers by using Microsoft Power BI. The reports will provide real-time data on trending topics, current advertising rates, and advertising costs for a given month.

Relecloud will analyze the trending topics data, and then store the data in a new data warehouse for ad-hoc analysis. The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long-term trending.

Requirements:

Management at Relecloud must be able to view which topics are trending to adjust advertising rates in near real-time.

Relecloud plans to implement a new streaming analytics platform that will report on trending topics.

Relecloud plans to implement a data warehouse named DB2.

Relecloud identifies the following technical requirements:

- Social media data must be analyzed to identify trending topics in real-time.

- The use of Infrastructure as a Service (IaaS) platforms must minimized, whenever possible.

- The real-time solution used to analyze the social media data must support scaling up and down without service interruption.

Relecloud identifies the following technical requirements for the advertisers:

- The advertisers must be able to see only their own data in the Power BI reports.

- The advertisers must authenticate to Power BI by using Azure Active Directory (Azure AD) credentials.

- The advertisers must be able to leverage existing Transact-SQL language knowledge when developing the real-time streaming solution.

- Members of the internal advertising sales team at Relecloud must be able to see only the sales date of the advertisers to which they are assigned.

- The internal Relecloud advertising sales team must be prevented from inserting, updating, and deleting rows for the advertisers to which they are not assigned.

- The internal Relecloud advertising sales team must be able to use a text file to update the list of advertisers, and then to upload the file to Azure Blob storage.

Relecloud identifies the following requirements for DB1:

- Data generated by the streaming analytics platform must be stored in DB1.

- The user names of the advertisers must be mapped to CustomerID in a table named Table2.

- The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.

- The user names of the employees at Relecloud must be mapped to EmployeeID in a table named Table3.

Relecloud identifies the following requirements for DB2:

- DB2 must have minimal storage costs.

- DB2 must run load processes in parallel.

- DB2 must support massive parallel processing.

- DB2 must be able to store more than 40 TB of data.

- DB2 must support scaling up and down, as required.

- Data from DB1 must be archived in DB2 for long-term storage.

- All of the reports that are executed from DB2 must use aggregation.

- Users must be able to pause DB2 when the data warehouse is not in use.

- Users must be able to view previous versions of the data in DB2 by using aggregates.

Relecloud identifies the following requirements for extract, transformation, and load (ETL):

- Data movement between DB1 and DB2 must occur each hour.

- An email alert must be generated when a failure of any type occurs during ETL processing.

Sample code and data:

You execute the following code for a table named rls_table1.

You use the following code to create Table1.

create table table1

(customerid int,

salespersonid int

...

)

Go

The following is a sample of the streaming data.

You need to implement rls_table1.

Which code should you execute? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

6. Note: The question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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.

Your company has multiple databases that contain millions of sales transactions.

You plan to implement a data mining solution to identify purchasing fraud.

You need to design a solution that mines 10 terabytes (TB) of sales date. The solution must meet the following requirements:

- Run the analysis to identify fraud once per week.

- Continue to receive new sales transactions while the analysis runs.

- Be able to stop computing services when the analysis is NOT running.

Solution: You create a Cloudera Hadoop cluster on Microsoft Azure virtual machines.

Does this meet the goal?

7. Note: The question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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.

Your company has multiple databases that contain millions of sales transactions.

You plan to implement a data mining solution to identify purchasing fraud.

You need to design a solution that mines 10 terabytes (TB) of sales date.

The solution must meet the following requirements:

- Run the analysis to identify fraud once per week.

- Continue to receive new sales transactions while the analysis runs.

- Be able to stop computing services when the analysis is NOT running.

Solution: You create a Microsoft Azure Data Lake job.

Does this meet the goal?

8. Note: The question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. 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.

Your company has multiple databases that contain millions of sales transactions.

You plan to implement a data mining solution to identify purchasing fraud.

You need to design a solution that mines 10 terabytes (TB) of sales date.

The solution must meet the following requirements:

- Run the analysis to identify fraud once per week.

- Continue to receive new sales transactions while the analysis runs.

- Be able to stop computing services when the analysis is NOT running.

Solution: You create a Microsoft Azure HDInsight cluster.

Does this meet the goal?

9. HOTSPOT You are designing a solution based on the lambda architecture.

The solution has the following layers:

- Batch

- Speed

- Serving

You are planning the data ingestion process and the query execution. For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

10. DRAG DROP

You have a web app that accepts user input, and then uses a Microsoft Azure Machine Learning model to predict a characteristic of the user.

You need to perform the following operations:

- Track the number of web app users from month to month.

- Track the number of successful predictions made during the last minute.

- Create a dashboard showcasing the analytics for the predictions and the web app usage.

Which lambda layer should you query for each operation? To answer, drag the appropriate layers to the correct operations. Each layer 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.

11. HOTSPOT

Your company has 2,000 servers.

You plan to aggregate all of the log files from the servers in a central repository that uses Microsoft Azure HDInsight. Each log file contains approximately one million records. All of the files use the .log file name extension.

The following is a sample of the entries in the log files.

2017-02-03 20:26:41 SampleClass3 [ERROR] verbose detail for id 1527353937

In Apache Hive, you need to create a data definition and a query capturing the number of records that have an error level of [ERROR].

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

NOTE: Each correct selection is worth one point.

12. DRAG DROP You work for a telecommunications company that uses Microsoft Azure Stream Analytics. You have data related to incoming calls.

You need to group the data in the following ways:

- Group A: Every five minutes for a duration of five minutes

- Group B: Every five minutes for a duration of 10 minutes

Which type of window should you use for each group? To answer, drag the appropriate window types to the correct groups. Each window type 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.

13. HOTSPOT

You have a Microsoft Azure Stream Analytics solution.

You need to identify which types of windows must be used to group the following types of events:

- Events that have random time intervals and are captured in a single fixed-size window

- Events that have random time intervals and are captured in overlapping windows

Which window type should you identify for each event type? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

14. You are designing an Apache HBase cluster on Microsoft Azure HDInsight. You need to identify which nodes are required for the cluster.

Which three nodes should you identify? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

15. DRAG DROP

You have data generated by sensors. The data is sent to Microsoft Azure Event Hubs. You need to have an aggregated view of the data in near real time by using five-minute tumbling windows

to identify short-term trends. You must also have hourly and a daily aggregated views of the data.

Which technology should you use for each task? To answer, drag the appropriate technologies to the

correct tasks. Each technology may be used once, more than once, or not at all. You may need to drag the spilt bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

16. You are designing a solution that will use Apache HBase on Microsoft Azure HDInsight.

You need to design the row keys for the database to ensure that client traffic is directed over all of the nodes in the cluster.

What are two possible techniques that you can use? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

17. HOTSPOT

You have the following script.

CREATE TABLE UserVisits (username string, url string, time date)

STORED AS TEXTFILE LOCATION "wasb:///Logs";

CREATE TABLE UserVisitsOrc (username string, url string, time date)

STORED AS ORC;

INSERT INTO TABLE UserVisitsOrc SELECT * FROM UserVisits

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the script.

NOTE: Each correct selection is worth one point.

18. A company named Fabrikam, Inc. has a Microsoft Azure web app. Billions of users visit the app daily.

The web app logs all user activity by using text files in Azure Blob storage. Each day, approximately 200 GB of text files are created. Fabrikam uses the log files from an Apache Hadoop cluster on Azure HDInsight. You need to recommend a solution to optimize the storage of the log files for later Hive use.

What is the best property to recommend adding to the Hive table definition to achieve the goal? More than

one answer choice may achieve the goal. Select the BEST answer.

19. You have structured data that resides in Microsoft Azure Blob storage.

You need to perform a rapid interactive analysis of the data and to generate visualizations of the data.

What is the best type of Azure HDInsight cluster to use to achieve the goal? More than one answer choice may achieve the goal. Choose the BEST answer.

20. You are designing a solution based on the lambda architecture.

You need to recommend which technology to use for the serving layer.

What should you recommend?