Microsoft 70-475 Online Practice Questions and Exam Preparation

Microsoft 70-475 Online Questions & Answers

• Question 71:

  You have an Apache Hive cluster in Microsoft Azure HDInsight. The cluster contains 10 million data files.

  You plan to archive the data.

  The data will be analyzed monthly.

  You need to recommend a solution to move and store the data. The solution must minimize how long it takes to move the data and must minimize costs.

  Which two services should you include in the recommendation? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Azure Queue storage
  B. Microsoft SQL Server Integration Services (SSIS)
  C. Azure Table Storage
  D. Azure Data Lake
  E. Azure Data Factory
  D. Azure Data Lake
  E. Azure Data Factory

  ---

  D: To analyze data in HDInsight cluster, you can store the data either in Azure Storage, Azure Data Lake Storage Gen 1/Azure Data Lake Storage Gen 2, or both. Both storage options enable you to safely delete HDInsight clusters that are used for computation without losing user data.

  E: The Spark activity in a Data Factory pipeline executes a Spark program on your own or on-demand HDInsight cluster. It handles data transformation and the supported transformation activities.

  References: https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hadoop-use-data-lake-store https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-spark

• Question 72:

  HOTSPOT

  Your company has several thousand sensors deployed.

  You have a Microsoft Azure Stream Analytics job that receives two data streams Input1 and Input2 from an Azure event hub. The data streams are portioned by using a column named SensorName. Each sensor is identified by a field named SensorID.

  You discover that Input2 is empty occasionally and the data from Input1 is ignored during the processing of the Stream Analytics job.

  You need to ensure that the Stream Analytics job always processes the data from Input1.

  How should you modify the query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: LEFT OUTER JOIN LEFT OUTER JOIN specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

  Box 2: ON I1.SensorID= I2.SensorID

  References: https://docs.microsoft.com/en-us/stream-analytics-query/join-azure-stream-analytics

• Question 73:

  You deploy a Microsoft Azure SQL database.

  You create a job to upload customer data to the database.

  You discover that the job cannot connect to the database and fails.

  You verify that the database runs successfully in Azure.

  You need to run the job successfully.

  What should you create?

  A. a virtual network rule
  B. a network security group (NSG)
  C. a firewall rule
  D. a virtual network
  C. a firewall rule

  ---

  If the application persistently fails to connect to Azure SQL Database, it usually indicates an issue with one of the following:

  Firewall configuration. The Azure SQL database or client-side firewall is blocking connections to Azure SQL Database.

  Network reconfiguration on the client side: for example, a new IP address or a proxy server.

  User error: for example, mistyped connection parameters, such as the server name in the connection string.

  References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-troubleshoot-common-connection-issues

• Question 74:

  DRAG DROP

  Your company has a data visualization solution that contains a customized Microsoft Azure Stream Analytics Solution. The solution provides data to a Microsoft Powel BI deployment.

  Every 10 seconds, you need to query for instances that have more than three records.

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

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Box 1: TumblingWindow(second, 10)

  Tumbling Windows define a repeating, non-overlapping window of time.

  Example: Calculate the count of sensor readings per device every 10 seconds

  SELECT sensorId, COUNT(*) AS Count

  FROM SensorReadings TIMESTAMP BY time

  GROUP BY sensorId, TumblingWindow(second, 10)

  Box 2: [Count] >= 3

  Count(*) returns the number of items in a group.

  Reference: https://blogs.technet.microsoft.com/machinelearning/2015/06/01/the-azure-stream-analytics-query-language/

• Question 75:

  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.

  Select and Place:

  

  

  ---

  Group A: Tumbling

  Tumbling Windows define a repeating, non-overlapping window of time.

  Group B: Hopping

  Like Tumbling Windows, Hopping Windows move forward in time by a fixed period but they can overlap with one another.

  Reference: https://blogs.technet.microsoft.com/machinelearning/2015/06/01/the-azure-stream-analytics-query-language/

• Question 76:

  Your company deploys thousands of sensors.

  You plan to join the data from the sensors by using Azure Data Factory. The reference data file refreshes every 30 minutes.

  You need to include the path to the reference data in Data Factory.

  Which path should you include?

  A. products/{date}/{time}/product_list.json
  B. products/{sensor_name}/product_list.json
  C. products/{batch}/product_list.json
  D. products/{time}/product_list.json
  A. products/{date}/{time}/product_list.json

  ---

  Stream Analytics supports taking reference data stored in Azure blob storage as one of the "inputs" for the job. To enable support for refreshing reference data the user needs to specify a list of blobs in the input configuration using the {date} and {time} tokens inside the path pattern. The job will load the corresponding blob based on the date and time encoded in the blob names using UTC time zone.

  For example if the job has a reference input configured in the portal with the path pattern such as: /referencedata/{date}/{time}/customertable.csv where the date format is "YYYY/MM/DD" and the time format is "HH/mm" then the job will pick up a file named / referencedata/2015/07/26/08/30/customertable.csv at 8:30 AM on July 26th 2015 UTC time zone.

  References: https://azure.microsoft.com/es-es/blog/refreshing-reference-data-with-azure-data-factory-for-azure-stream-analytics-jobs-3/

• Question 77:

  You need to ingest data from various data stores into a Microsoft Azure SQL data warehouse by using PolyBase.

  You create an Azure Data Factory.

  Which three components should you create next? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. an Azure Function
  B. datasets
  C. a pipeline
  D. an Azure Batch account
  E. linked services
  B. datasets
  C. a pipeline
  E. linked services

  ---

  The dataset specifies the name of the database and the table that contains the source data.

  Create a pipeline to look up the tables to be copied and another pipeline to perform the actual copy operation.

  You create linked services to link your data stores and computes to a data factory. A linked service has the connection information that the Data Factory service uses to connect to the data store at runtime.

  References:

  https://docs.microsoft.com/en-us/azure/data-factory/tutorial-bulk-copy-portal

• Question 78:

  Your company has two Microsoft Azure SQL databases named db1 and db2.

  You need to move data from a table in db1 to a table in db2 by using a pipeline in Azure Data Factory.

  You create an Azure Data Factory named ADF1.

  Which two types of objects should you create in ADF1 to complete the pipeline? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. a linked service
  B. an Azure Service Bus
  C. sources and targets
  D. input and output datasets
  E. transformations
  A. a linked service
  D. input and output datasets

  ---

  You perform the following steps to create a pipeline that moves data from a source data store to a sink data store:

  Create linked services to link input and output data stores to your data factory.

  Create datasets to represent input and output data for the copy operation.

  Create a pipeline with a copy activity that takes a dataset as an input and a dataset as an output.

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/data-factory-azure-table-connector

• Question 79:

  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?

  A. Azure Stream Analytics
  B. Azure Data Lake Analytics
  C. Azure Machine Learning
  D. Azure HDInsight Storm clusters
  A. Azure Stream Analytics

  ---

  Azure Stream Analytics is a fully managed event-processing engine that lets you set up real-time analytic computations on streaming data.

  Scalability

  Stream Analytics can handle up to 1 GB of incoming data per second. Integration with Azure Event Hubs and Azure IoT Hub allows jobs to ingest millions of events per second coming from connected devices, clickstreams, and log files, to

  name a few. Using the partition feature of event hubs, you can partition computations into logical steps, each with the ability to be further partitioned to increase scalability.

  From scenario: 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.

  Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-introduction

• Question 80:

  Note: This 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

  the 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.

  You plan to deploy a Microsoft Azure SQL data warehouse and a web application.

  The data warehouse will ingest 5 TB of data from an on-premises Microsoft SQL Server database daily. The web application will query the data warehouse.

  You need to design a solution to ingest data into the data warehouse.

  Solution: You use AzCopy to transfer the data as text files from SQL Server to Azure Blob storage, and then you use PolyBase to run Transact-SQL statements that refresh the data warehouse database.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  If you need the best performance, then use PolyBase to import data into Azure SQL warehouse.

  Note: Often the speed of migration is an overriding concern compared to ease of setup and maintainability, particularly when there's a large amount of data to move. Optimizing purely for speed, a source controlled differentiated approach relying on bcp to export data to files, efficiently moving the files to Azure Blob storage, and using the Polybase engine to import from blob storage works best. References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-migrate-data