Microsoft 70-475 Online Practice Questions and Exam Preparation

Microsoft 70-475 Online Questions & Answers

• Question 11:

  You have a Microsoft Azure subscription that contains an Azure Data Factory pipeline.

  You have an RSS feed that is published on a public website.

  You need to configure the RSS feed as a data source for the pipeline.

  Which type of linked service should you use?

  A. web
  B. OData
  C. Azure Search
  D. Azure Data Lake Store
  A. web

  ---

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

• Question 12:

  HOTSPOT

  You have four on-premises Microsoft SQL Server data sources as described in the following table.

  

  You plan to create three Azure data factories that will interact with the data sources as described in the following table

  

  You need to deploy Microsoft Data Management Gateway to support the Azure Data Factory deployment. The solution must use new servers to host the instances of Data Management Gateway.

  What is the minimum number of new servers and date management gateways you should you deploy? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: 3 Box 2: 3 Considerations for using gateway A single instance of Data Management Gateway can be used for multiple on-premises data sources. However, a single gateway instance is tied to only one Azure data factory and cannot be shared with another data factory. You can have only one instance of Data Management Gateway installed on a single machine. Suppose, you have two data factories that need to access on-premises data sources, you need to install gateways on two on-premises computers.

  In other words, a gateway is tied to a specific data factory.

  Reference: https://docs.microsoft.com/en-us/azure/data-factory/data-factory-data-management-gateway

• Question 13:

  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.

  A. STORED AS RCFILE
  B. STORED AS GZIP
  C. STORED AS ORC
  D. STORED AS TEXTFILE
  C. STORED AS ORC

  ---

  The Optimized Row Columnar (ORC) file format provides a highly efficient way to store Hive data. It was designed to overcome limitations of the other Hive file formats. Using ORC files improves performance when Hive is reading, writing, and processing data. Compared with RCFile format, for example, ORC file format has many advantages such as: a single file as the output of each task, which reduces the NameNode's load Hive type support including datetime, decimal, and the complex types (struct, list, map, and union) light-weight indexes stored within the file skip row groups that don't pass predicate filtering seek to a given row block-mode compression based on data type run-length encoding for integer columns dictionary encoding for string columns concurrent reads of the same file using separate RecordReaders ability to split files without scanning for markers bound the amount of memory needed for reading or writing metadata stored using Protocol Buffers, which allows addition and removal of fields

  Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC#LanguageManualORC-ORCFileFormat

• Question 14:

  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 SQL Server Integration Services (SSIS) to transfer data from SQL Server to Azure SQL Data Warehouse.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Integration Services (SSIS) is a powerful and flexible Extract Transform and Load (ETL) tool that supports complex workflows, data transformation, and several data loading options. The main drawback is speed. We should use Polybase instead.

  References: https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services

• Question 15:

  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 have a Microsoft Azure subscription that includes Azure Data Lake and Cognitive Services.

  An administrator plans to deploy an Azure Data Factory.

  You need to ensure that the administrator can create the data factory.

  Solution: You add the user to the Data Factory Contributor role.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Membership in the Data Factory Contributor role lets users do the following things:

  * Create, edit, and delete data factories and child resources including datasets, linked services, pipelines, triggers, and integration runtimes.

  Note:

  To create Data Factory instances, the user account that you use to sign in to Azure must be a member of the contributor or owner role, or an administrator of the Azure subscription.

  References: https://docs.microsoft.com/en-us/azure/data-factory/concepts-roles-permissions

• Question 16:

  DRAG DROP

  You have raw data in Microsoft Azure Blob storage. Each data file is 10 KB and is the XML format.

  You identify the following requirements for the data:

  The data must be converted into a flat data structure by using a C# MapReduce job.

  The data must be moved to an Azure SQL database, which will then be used to visualize the data.

  Additional stored procedures must run against the data once the data is in the database.

  You need to create the workflow for the Azure Data Factory pipeline.

  Which activity type should you use for each requirement? To answer, drag the appropriate workflow components to the correct requirements. Each workflow component may be used once, more than once, or not at all. You may need to drag

  the split bar between the panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Box 1: HDinsightMapReduce The HDInsight MapReduce activity in a Data Factory pipeline invokes MapReduce program on your own or on-demand HDInsight cluster. Box 2: HDInsightStreaming Box 3: SQLServerStoredProcedure

• Question 17:

  You are developing an Apache Storm application by using Microsoft Visual Studio.

  You need to implement a custom topology that uses a custom bolt.

  Which type of object should you initialize in the main class?

  A. Stream
  B. TopologyBuilder
  C. StreamInfo
  D. Logger
  B. TopologyBuilder

  ---

  Bolts: Performs processing on streams emitted by spouts or other bolts, and emits one or more streams.

  The important method is GetTopologyBuilder, which is used to define the topology that is submitted to Storm.

  The topology ties the spouts and bolts together into a graph, which defines how data flows between the components.

  References:

  https://docs.microsoft.com/en-us/azure/hdinsight/storm/apache-storm-develop-java-topology

• Question 18:

  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.

  Hot Area:

  

  

  ---

  From Scenario: Relecloud plans to implement a data warehouse named DB2.

  Box 1: Temporal table

  From Scenario:

  Relecloud identifies the following requirements for DB2:

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

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

  A system-versioned temporal table is a new type of user table in SQL Server 2017, designed to keep a full history of data changes and allow easy point in time analysis. A temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

  Box 2: Clustered Columnstore

  From Scenario:

  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.

  Relecloud identifies the following requirements for DB2:

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

  How SQL Database deletes aged rows?

  The cleanup process depends on the index layout of the history table. It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables

• Question 19:

  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 have a Microsoft Azure deployment that contains the following services: Azure Data Lake Azure Cosmos DB Azure Data Factory Azure SQL Database

  You load several types of data to Azure Data Lake.

  You need to load data from Azure SQL Database to Azure Data Lake.

  Solution: You use the AzCopy utility.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Note: You can use the Copy Activity in Azure Data Factory to copy data to and from Azure Data Lake Storage Gen1 (previously known as Azure Data Lake Store). Azure SQL database is supported as source. References: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-data-lake-store

• Question 20:

  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 have a Microsoft Azure deployment that contains the following services: Azure Data Lake Azure Cosmos DB Azure Data Factory Azure SQL Database

  You load several types of data to Azure Data Lake.

  You need to load data from Azure SQL Database to Azure Data Lake.

  Solution: You use the Azure Import/Export service.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Azure Import/Export service is used to securely import large amounts of data to Azure Blob storage and Azure Files by shipping disk drives to an Azure datacenter. This service can also be used to transfer data from Azure Blob storage to disk

  drives and ship to your on-premises sites.

  Note:

  You can use the Copy Activity in Azure Data Factory to copy data to and from Azure Data Lake Storage Gen1 (previously known as Azure Data Lake Store). Azure SQL database is supported as source.

  References: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-data-lake-store