Microsoft 70-475 Online Practice Questions and Exam Preparation

Microsoft 70-475 Online Questions & Answers

• Question 41:

  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.

  Hot Area:

  

  

  ---

  Box 1: No

  Box 2: No

  Output from the batch and speed layers are stored in the serving layer, which responds to ad-hoc queries by returning precomputed views or building views from the processed data.

  

  Box 3: Yes.

  We are excited to announce Interactive Queries, a new feature for stream processing with Apache Kafka. Interactive Queries allows you to get more than just processing from streaming.

  Note: Lambda architecture is a popular choice where you see stream data pipelines applied (speed layer). Architects can combine Apache Kafka or Azure Event Hubs (ingest) with Apache Storm (event processing), Apache HBase (speed layer), Hadoop for storing the master dataset (batch layer), and, finally, Microsoft Power BI for reporting and visualization (serving layer).

  Reference: https://www.confluent.io/blog/unifying-stream-processing-and-interactive-queries-in-apache-kafka/ https://en.wikipedia.org/wiki/Lambda_architecture

• Question 42:

  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

  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.

  Hot Area:

  

  

  ---

  Box 1: table

  Box 2: /t

  Apache Hive example:

  CREATE TABLE raw (line STRING)

  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

  Box 3: count(*)

  Box 4: '*.log'

  Reference: https://cwiki.apache.org/confluence/display/Hive/CompressedStorage

• Question 43:

  DRAG DROP

  You need to implement a security solution for Microsoft Azure SQL database. The solution must meet the following requirements:

  Ensure that users can see the data from their respective department only.

  Prevent administrators from viewing the data.

  Which feature should you use for each requirement? To answer, drag the appropriate features to the correct requirements. Each feature 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:

  

  

• Question 44:

  You plan to create a Microsoft Azure Data Factory pipeline that will connect to an Azure HDInsight cluster that uses Apache Spark.

  You need to recommend which file format must be used by the pipeline. The solution must meet the following requirements: Store data in the columnar format Support compression

  Which file format should you recommend?

  A. XML
  B. AVRO
  C. text
  D. Parquet
  D. Parquet

  ---

  Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem, regardless of the choice of data processing framework, data model or programming language. Apache Parquet supports compression.

  Incorrect Answers:

  A: Azure Data Factory does not support XML.

  C: The text format does not support compression.

  Note: Azure Data Factory supports the following file format types: Text format JSON format Parquet format ORC format Avro format

  References: https://parquet.apache.org/

• Question 45:

  DRAG DROP

  Your company has a Microsoft Azure environment that contains an Azure HDInsight Hadoop cluster and an Azure SQL data warehouse. The Hadoop cluster contains text files that are formatted by using UTF-8 character encoding.

  You need to implement a solution to ingest the data to the SQL data warehouse from the Hadoop cluster. The solution must provide optimal read performance for the data after ingestion.

  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.

  Select and Place:

  

  

  ---

  SQL Data Warehouse supports loading data from HDInsight via PolyBase. The process is the same as loading data from Azure Blob Storage - using PolyBase to connect to HDInsight to load data.

  Use PolyBase and T-SQL

  Summary of loading process:

  Move your data to HDInsight and store it in text files, ORC or Parquet format.

  Configure external objects in SQL Data Warehouse to define the location and format of the data.

  Run a T-SQL command to load the data in parallel into a new database table.

  Recommendations

  Create statistics on newly loaded data. Azure SQL Data Warehouse does not yet support auto create or auto update statistics. In order to get the best performance from your queries, it's important to create statistics on all columns of all tables

  after the first load or any substantial changes occur in the data.

  Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-load#load-from-hdinsight

• Question 46:

  You use Microsoft Azure Data Factory to orchestrate data movements and data transformations within Azure.

  You plan to monitor the data factory to ensure that all of the activity slices run successfully.

  You need to identify a solution to rerun failed slices.

  What should you do?

  A. From the Diagram tile on the Data Factory blade of the Azure portal, double-click the pipeline that has a failed slice.
  B. Move the data factory to a different resource group.
  C. From the Azure portal, select the Data slice blade, and then click Run.
  D. Delete and recreate the data factory.
  C. From the Azure portal, select the Data slice blade, and then click Run.

  ---

  Once you troubleshoot and debug failures in a pipeline, you can re-run failures by navigating to the error slice and clicking the Run button on the command bar.

  

  References: https://github.com/Huachao/azure-content/blob/master/articles/data-factory/data-factory-monitor-manage-pipelines.md

• Question 47:

  DRAG DROP

  You have an Apache Spark cluster on Microsoft Azure HDInsight for all analytics workloads.

  You plan to build a Spark streaming application that processes events ingested by using Azure Event Hubs.

  You need to implement checkpointing in the Spark streaming application for high availability of the event data.

  In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in correct order.

  Select and Place:

  

  

  ---

  Step 1: Create a SparkConf object

  SparkConf. Spark properties control most application settings and are configured separately for each application. These properties can be set directly on a SparkConf passed to your SparkContext.

  Step 2: Create a StreamingContext

  Example:

  val conf = new SparkConf().setMaster("local[2]").setAppName("NetworkWordCount")

  val ssc = new StreamingContext(conf, Seconds(1))

  Step 3: Create a stream

  Step 4: Set the checkpoint directory for the StreamingContext

  Set a checkpoint directory in an HDFS-compatible file system with streamingContext.checkpoint(hdfsDirectory).

  Step 5: Set the checkpoint interval for the stream

  tried to write in C++ but mostly fall back to the better know C

  References:

  https://spark.apache.org/docs/latest/streaming-programming-guide.html#checkpointing

• Question 48:

  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.

  Select and Place:

  

  

  ---

  Box 1: Security

  Security Policy

  Example: After we have created Predicate function, we have to bind it to the table, using Security Policy. We will be using CREATE SECURITY POLICY command to set the security policy in place.

  CREATE SECURITY POLICY DepartmentSecurityPolicy

  ADD FILTER PREDICATE dbo.DepartmentPredicateFunction(UserDepartment) ON dbo.Department

  WITH(STATE = ON)

  Box 2: Filter

  [ FILTER | BLOCK ]

  The type of security predicate for the function being bound to the target table. FILTER predicates silently filter the rows that are available to read operations. BLOCK predicates explicitly block write operations that violate the predicate function.

  Box 3: Block

  Box 4: Block

  Box 5: Filter

  Reference:

  https://social.technet.microsoft.com/wiki/contents/articles/37813.sql-server-2016-implement-row-level-security-using-predicate-function-and-security-policy.aspx

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policytransact-sql

• Question 49:

  You have a Microsoft Azure Machine Learning application named App1 that is used by several departments in your organization.

  App 1 connects to an Azure database named DB1. DB1 contains several tables that store sensitive information.

  You plan to implement a security solution for the tables.

  You need to prevent the users of App1 from viewing the data of users in other departments in the tables. The solution must ensure that the users can see only data of the users in their respective department.

  Which feature should you implement?

  A. Cell-level encryption
  B. Row-Level Security (RLS)
  C. Transparent Data Encryption (TDE)
  D. Dynamic Data Masking
  D. Dynamic Data Masking

• Question 50:

  A company named Fabricam, Inc, has a web app hosted in Microsoft Azure. Millions of users visit the app daily.

  All of the user visits are logged in Azure Blob storage. Data analysts at Fabrikam built a dashboard that processes the user visit logs.

  Fabrikam plans to use an Apache Hadoop cluster on Azure HDInsight to process queries. The queries will access the data only once.

  You need to recommend a query execution strategy.

  What is the best to recommend using to achieve the goal?

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

  A. Load the text files to ORC files, and then run dashboard queries on the ORC files.
  B. Load the text files to sequence files, and then run dashboard queries on the sequence files.
  C. Run the queries on the text files directly.
  D. Load the text files to parquet files, and then run dashboard queries on the parquet files.
  B. Load the text files to sequence files, and then run dashboard queries on the sequence files.

  ---

  File format versatility and Intelligent caching: Fast analytics on Hadoop have always come with one big catch: they require up-front conversion to a columnar format like ORCFile, Parquet or Avro, which is time-consuming, complex and limits your agility.

  With Interactive Query Dynamic Text Cache, which converts CSV or JSON data into optimized in-memory format on-the-fly, caching is dynamic, so the queries determine what data is cached. After text data is cached, analytics run just as fast as if you had converted it to specific file formats.

  References: https://azure.microsoft.com/en-us/blog/azure-hdinsight-interactive-query-simplifying-big-data-analytics-architecture-and-operations/