Data Engineering on Microsoft Azure

• #201

  You build a data warehouse in an Azure Synapse Analytics dedicated SQL pool. Analysts write a complex SELECT query that contains multiple JOIN and CASE statements to transform data for use in inventory reports. The inventory reports will use the data and additional WHERE parameters depending on the report. The reports will be produced once daily. You need to implement a solution to make the dataset available for the reports. The solution must minimize query times. What should you implement?

  • AMaterialized view.
  • BReplicated table.
  • CIn ordered clustered columnstore index.
  • DResult set chaching.
  Show answer
• #202

  You are designing a real-time dashboard solution that will visualize streaming data from remote sensors that connect to the internet. The streaming data must be aggregated to show the average value of each 10-second interval. The data will be discarded after being displayed in the dashboard. The solution will use Azure Stream Analytics and must meet the following requirements: Minimize latency from an Azure Event hub to the dashboard. Minimize the required storage. Minimize development effort. What should you include in the solution?

  • AAzure Stream Analytics input type: Azure Stream Analytics. Azure Stream Analytics output type: Azure Event Hub. Aggregation query location: Microsoft Power BI.
  • BAzure Stream Analytics input type: Azure Stream Analytics. Azure Stream Analytics output type: Azure Stream Analytics. Aggregation query location: Azure Event Hub.
  • CAzure Stream Analytics input type: Microsoft Power BI.Azure Stream Analytics output type: Azure Event Hub. Aggregation query location: Azure Event Hub.
  • DAzure Stream Analytics input type: Azure Event Hub. Azure Stream Analytics output type: Microsoft Power BI. Aggregation query location: Azure Stream Analytics.
  Show answer
• #203

  You are building a data flow in Azure Data Factory that upserts data into a table in an Azure Synapse Analytics dedicated SQL pool. You need to add a transformation to the data flow. The transformation must specify logic indicating when a row from the input data must be upserted into the sink. Which type of transformation should you add to the data flow?

  • Ajoin.
  • Bselect.
  • Csurrogate key.
  • Dalter row.
  Show answer
• #204

  You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage account named storage1. Storage1 requires secure transfers. You need to create an external data source in Pool1 that will be used to read .orc files in storage1. How should you complete the code?

  • ABox 1: abfss. Box 2: RDBMS.
  • BBox 1: wasb. Box 2: BLOB_STORAGE.
  • CBox 1: wasbs. Box 2: SHARP MAP MANAGER.
  • DBox 1: abfs. Box 2: HADOOP.
  Show answer
• #205

  You are designing a dimension table for a data warehouse. The table will track the value of the dimension attributes over time and preserve the history of the data by adding new rows as the data changes. Which type of slowly changing dimension (SCD) should use?

  • AType 0.
  • BType 1.
  • CType 2.
  • DType 3.
  Show answer
• #206

  You have an Azure Synapse Analytics serverless SQL pool that contains a database named db1. The data model for db1 is shown in the following exhibit.

  • ATo convert the data model to a star schema: join DimGeography and DimCustomer. Once the data model is converted into a star schema, there will be [...] tables: 6.
  • BTo convert the data model to a star schema: join DimGeography and FactOrders. Once the data model is converted into a star schema, there will be [...] tables: 5.
  • CTo convert the data model to a star schema: join DimGeography and DimCustomer. Once the data model is converted into a star schema, there will be [...] tables: 5.
  • DTo convert the data model to a star schema: union DimGeography and FactOrders. Once the data model is converted into a star schema, there will be [...] tables: 6.
  Show answer
• #207

  You have an Azure Storage account that contains 100 GB of files. The files contain rows of text and numerical values. 75% of the rows contain description data that has an average length of 1.1 MB. You plan to copy the data from the storage account to an enterprise data warehouse in Azure Synapse Analytics. You need to prepare the files to ensure that the data copies quickly. Solution: You convert the files to compressed delimited text files. Does this meet the goal?

  • AYes.
  • BNo.
  Show answer
• #208Choose 2

  You need to ensure that ServicePrincipal1 can perform the following actions: Traverse child items that are created in Folder2. Read files that are created in Folder2. The solution must use the principle of least privilege. Which two permissions should you grant to ServicePrincipal1 for Folder2?

  • AAccess – Read.
  • BAccess – Write.
  • CAccess – Execute.
  • DDefault-Read.
  • EDefault – Write.
  • FDefault – Execute.
  Show answer
• #209

  You are designing an application that will use an Azure Data Lake Storage Gen 2 account to store petabytes of license plate photos from toll booths. The account will use zone-redundant storage (ZRS). You identify the following usage patterns: The data will be accessed several times a day during the first 30 days after the data is created. The data must meet an availability SU of 99.9%. After 90 days, the data will be accessed infrequently but must be available within 30 seconds. After 365 days, the data will be accessed infrequently but must be available within five minutes.

  • AFirst 30 days: Hot. After 90 days: Cool. After 365 days: Cool.
  • BFirst 30 days: Hot. After 90 days: Archive. After 365 days: Archive.
  • CFirst 30 days: Cool. After 90 days: Cool. After 365 days: Archive.
  • DFirst 30 days: Cool. After 90 days: Hot. After 365 days: Archive.
  Show answer
• #210

  From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays. The data contains the following columns. You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension. To which table should you add each column?

  • AEventCategory: DimChannel. ChannelGrouping: FactEvents. TotalEvents: FactEvents.
  • BEventCategory: DimEvent. ChannelGrouping: DimChannel. TotalEvents: FactEvents.
  • CEventCategory: FactEvents. ChannelGrouping: DimEvent. TotalEvents: DimEvent.
  • DEventCategory: FactEvents. ChannelGrouping: DimChannel. TotalEvents: DimEvent.
  Show answer
• #211

  You plan to create a real-time monitoring app that alerts users when a device travels more than 200 meters away from a designated location. You need to design an Azure Stream Analytics job to process the data for the planned app. The solution must minimize the amount of code developed and the number of technologies used. What should you include in the Stream Analytics job?

  • AInput type: Reference. Function: Windowing.
  • BInput type: Stream. Function: Geospatial.
  • CInput type: Reference. Function: Geospatial.
  • DInput type: Stream. Function: Aggregate.
  Show answer
• #212

  You plan to implement an Azure Data Lake Gen2 storage account. You need to ensure that the data lake will remain available if a data center fails in the primary Azure region. The solution must minimize costs. Which type of replication should you use for the storage account?

  • Ageo-redundant storage (GRS).
  • Bzone-redundant storage (ZRS).
  • Clocally-redundant storage (LRS).
  • Dgeo-zone-redundant storage (GZRS).
  Show answer
• #213Choose 2

  You plan to create an Azure Synapse Analytics dedicated SQL pool. You need to minimize the time it takes to identify queries that return confidential information as defined by the company's data privacy regulations and the users who executed the queues. Which two components should you include in the solution?

  • ASensitivity-classification labels applied to columns that contain confidential information.
  • BResource tags for databases that contain confidential information.
  • CAudit logs sent to a Log Analytics workspace.
  • DDynamic data masking for columns that contain confidential information.
  Show answer
• #214

  You have two Azure Data Factory instances named ADFdev and ADFprod. ADFdev connects to an Azure DevOps Git repository. You publish changes from the main branch of the Git repository to ADFdev. You need to deploy the artifacts from ADFdev to ADFprod. What should you do first?

  • AFrom ADFdev, modify the Git configuration.
  • BFrom ADFdev, create a linked service.
  • CFrom Azure DevOps, create a release pipeline.
  • DFrom Azure DevOps, update the main branch.
  Show answer
• #215

  You are building an Azure Stream Analytics job to identify how much time a user spends interacting with a feature on a webpage. The job receives events based on user actions on the webpage. Each row of data represents an event. Each event has a type of either 'start' or 'end'. You need to calculate the duration between start and end events. How should you complete the query?

  • ABox 1: DATEPART. Box 2: TOPONE.
  • BBox 1: DATEDIFF. Box 2: LAST.
  • CBox 1: DATEADD. Box 2: LAST.
  • DBox 1: DATEADD. Box 2: ISFIRST.
  Show answer
• #216

  You plan to implement an Azure Data Lake Gen 2 storage account. You need to ensure that the data lake will remain available if a data center fails in the primary Azure region. The solution must minimize costs. Which type of replication should you use for the storage account?

  • Ageo-redundant storage (GRS).
  • Bgeo-zone-redundant storage (GZRS).
  • Clocally-redundant storage (LRS).
  • Dzone-redundant storage (ZRS).
  Show answer
• #217

  You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables. Which distribution type should you recommend to minimize data movement?

  • AHASH.
  • BREPLICATE.
  • CROUND ROBIN.
  Show answer
• #218

  You have an Azure Factory instance named DF1 that contains a pipeline named PL1.PL1 includes a tumbling window trigger. You create five clones of PL1. You configure each clone pipeline to use a different data source. You need to ensure that the execution schedules of the clone pipeline match the execution schedule of PL1. What should you do?

  • AAdd a new trigger to each cloned pipeline.
  • BAssociate each cloned pipeline to an existing trigger.
  • CCreate a tumbling window trigger dependency for the trigger of PL1.
  • DModify the Concurrency setting of each pipeline.
  Show answer
• #219

  You have an Azure subscription. You plan to build a data warehouse in an Azure Synapse Analytics dedicated SQL pool named pool1 that will contain staging tables and a dimensional model Pool1 will contain the following tables.

  • ABox 1: Replicated. Box 2: Round-robin. Box 3: Hash.
  • BBox 1: Replicated. Box 2: Hash. Box 3: Round-robin.
  • CBox 1: Replicated. Box 2: Replicated. Box 3: Hash.
  • DBox 1: Round-robin. Box 2: Hash. Box 3: Round-robin.
  Show answer
• #220

  You need to ensure that the Twitter feed data can be analyzed in the dedicated SQL pool. The solution must meet the customer sentiment analytics requirements. Which three Transaction-SQL DDL commands should you run in sequence?

  • ABox 1: CREATE EXTERNAL DATA SOURCE. Box 2: CREATE EXTERNAL TABLE AS SELECT. Box 3: CREATE EXTERNAL FILE FORMAT.
  • BBox 1: CREATE EXTERNAL DATA SOURCE. Box 2: CREATE EXTERNAL FILE FORMAT. Box 3: CREATE EXTERNAL TABLE AS SELECT.
  • CBox 1: CREATE EXTERNAL DATA SOURCE. Box 2: CREATE EXTERNAL FILE FORMAT. Box 3: CREATE TABLE.
  • DBox 1: CREATE DATABASE SCOPED CREDENTIAL. Box 2: CREATE EXTERNAL DATA SOURCE. Box 3: CREATE EXTERNAL FILE FORMAT.
  Show answer

Want to track your score, take timed mock exams and get AI explanations? Create a free account