Data Engineering on Microsoft Azure

• #121

  You are implementing an Azure Stream Analytics solution to process event data from devices. The devices output events when there is a fault and emit a repeat of the event every five seconds until the fault is resolved. The devices output a heartbeat event every five seconds after a previous event if there are no faults present. A sample of the events is shown in the following table. You need to calculate the uptime between the faults. How should you complete the Stream Analytics SQL query?

  • ABox 1: WHERE WHERE LAG(EventType, 1) OVER (LIMIT DURATION(second, 5)) <> EventType. Box 2: SessionWindow(second, 5, 50000) OVER (PARTITION BY DeviceID).
  • BBox 1: WHERE EventType='HeartBeat'. Box 2: SessionWindow(second, 5, 50000) OVER (PARTITION BY DeviceID).
  • CBox 1: WHERE EventType='HeartBeat'. Box 2: TumblingWindow(second, 5).
  • DBox 1: WHERE IsFirst(second, 5) = 1. Box 2: HAVING DATEDIFF(second, MINE(EventTime), MAX(EventTime)) > 5.
  Show answer
• #122

  You are designing an Azure Data Lake Storage Gen2 container to store data for the human resources (HR) department and the operations department at your company. You have the following data access requirements: After initial processing, the HR department data will be retained for seven years. The operations department data will be accessed frequently for the first six months, and then accessed once per month. You need to design a data retention solution to meet the access requirements. The solution must minimize storage costs.

  • AHR: Archive storage after one day and delete storage after 2,555 days. Operations: Cool storage after 180 days.
  • BHR: Delete after 180 days. Operations: Cool storage after one day.
  • CHR: Delete after 180 days. Operations: Archive storage after one day and delete storage after 2,555 days.
  • DHR: Cool storage after 180 days. Operations: Archive storage after 2,555 days.
  Show answer
• #123

  You have an Azure subscription that contains the following resources: An Azure Active Directory (Azure AD) tenant that contains a security group named Group1 An Azure Synapse Analytics SQL pool named Pool1 You need to control the access of Group1 to specific columns and rows in a table in Pool1. Which Transact-SQL commands should you use?

  • ATon control access to the columns: CREATE SECURITY POLICY. Tocontrol access to the rows: CREATE PARTITION FUNCTION.
  • BTon control access to the columns: CREATE SECURITY POLICY.Tocontrol access to the rows: CREATE SECURITY POLICY.
  • CTon control access to the columns: CREATE SECURITY POLICY. Tocontrol access to the rows: CREATE SECURITY POLICY.
  • DTon control access to the columns: GRANT. Tocontrol access to the rows: CREATE SECURITY POLICY.
  Show answer
• #124

  You are designing an Azure Stream Analytics solution that will analyze Twitter data. You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once. Solution: You use a session window that uses a timeout size of 10 seconds. Does this meet the goal?

  • AYes.
  • BNo.
  Show answer
• #125

  You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36 months and has the following characteristics: Is partitioned by month Contains one billion rows Has clustered columnstore indexes At the beginning of each month, you need to remove data from SalesFact that is older than 36 months as quickly as possible. Which three actions should you perform in sequence in a stored procedure?

  • ABox 1: Switch the partition containing the stale data from SalesFact to SalesFact_Work. Box 2: Drop the SalesFact_Work table. Box 3: Switch the partition containing the stale data from SalesFact to SalesFact_Work.
  • BBox 1: Drop the SalesFact_Work table. Box 2: Create an empty table named SalesFact_Work that has the same schema as SalesFact. Box 3: Drop the SalesFact_Work table.
  • CBox 1: Create an empty table named SalesFact_Work that has the same schema as SalesFact. Box 2: Switch the partition containing the stale data from SalesFact to SalesFact_Work. Box 3: Drop the SalesFact_Work table.
  • DBox 1: Switch the partition containing the stale data from SalesFact to SalesFact_Work. Box 2: Create an empty table named SalesFact_Work that has the same schema as SalesFact. Box 3: Create an empty table named SalesFact_Work that has the same schema as SalesFact.
  Show answer
• #126

  You develop a dataset named DBTBL1 by using Azure Databricks. DBTBL1 contains the following columns: SensorTypeID GeographyRegionID Year Month Day HourMinute Temperature WindSpeed Other You need to store the data to support daily incremental load pipelines that vary for each GeographyRegionID. The solution must minimize storage costs. How should you complete the code?

  • ABox 1: .partitionBy. Box 2: ("Year", "Month", "Day", "GeographyRegionID"). Box 3: .saveAsTable(/DBTBL1").
  • BBox 1: .partitionBy. Box 2: ("GeographyRegionID", "Year", "Month", "Day"). Box 3: .parquet("/DBTBL1").
  • CBox 1: .sortBy. Box 2: (""). Box 3: .json("/DBTBL1").
  • DBox 1: .bucketBy. Box 2: ("GeographyRegionID"). Box 3: .csv("/DBTBL1").
  Show answer
• #127

  You need to design the partitions for the product sales transactions. The solution must meet the sales transaction dataset requirements. What should you include in the solution?

  • APartition product sales transactions data by: Promotion ID. Store product sales transactions data in: An Azure Synapse Analytics serverless SQL pool.
  • BPartition product sales transactions data by: Sales date. Store product sales transactions data in: An Azure Synapse Analytics dedicated SQL pool.
  • CPartition product sales transactions data by: Product ID. Store product sales transactions data in: An Azure Synapse Analytics serverless SQL pool.
  • DPartition product sales transactions data by: Promotion ID. Store product sales transactions data in: An Azure Data Lake Storage Gen2 account linked.
  Show answer
• #128

  You have an Azure data factory. You need to ensure that pipeline-run data is retained for 120 days. The solution must ensure that you can query the data by using the Kusto query language. Which four actions should you perform in sequence?

  • ABox 1: From the Azure portal, add a diagnostic setting. Box 2: Create an Azure Storage account that| has a lifecycle policy. Box 3: Send the data to a Log Analytics workspace. Box 4: Send the data to a Log Analytics workspace.
  • BBox 1: Send the data to a Log Analytics workspace. Box 2: From the Azure portal, add a diagnostic setting. Box 3: Send the data to a Log Analytics workspace. Box 4: From the Azure portal, add a diagnostic setting.
  • CBox 1: Create an Azure Storage account that| has a lifecycle policy. Box 2: Create a Log Analytics workspace that has Data Retention set to 120 days. Box 3: From the Azure portal, add a diagnostic setting. Box 4: Send the data to a Log Analytics workspace.
  • DBox 1: Create a Log Analytics workspace that has Data Retention set to 120 days. Box 2: Create an Azure Storage account that| has a lifecycle policy. Box 3: Create an Azure Storage account that| has a lifecycle policy. Box 4: From the Azure portal, add a diagnostic setting.
  Show answer
• #129

  You are responsible for providing access to an Azure Data Lake Storage Gen2 account. Your user account has contributor access to the storage account, and you have the application ID and access key. You plan to use PolyBase to load data into an enterprise data warehouse in Azure Synapse Analytics. You need to configure PolyBase to connect the data warehouse to storage account. Which three components should you create in sequence?

  • ABox 1: an external data source. Box 2: a database scoped credential. Box 3: an external file format.
  • BBox 1: an external file format. Box 2: an external data source. Box 3: a database scoped credential.
  • CBox 1: an asymetric key. Box 2: a database scoped credential. Box 3: an external data source.
  • DBox 1: a database scoped credential. Box 2: an external data source. Box 3: an external file format.
  Show answer
• #130

  You have an Azure Active Directory (Azure AD) tenant that contains a security group named Group1. You have an Azure Synapse Analytics dedicated SQL pool named dw1 that contains a schema named schema1. You need to grant Group1 read-only permissions to all the tables and views in schema1. The solution must use the principle of least privilege. Which three actions should you perform in sequence?

  • ABox 1: Create a database user in dw1 that represents Group1 and user the FROM EXTERNAL PROVIDER clause. Box 2: Create adatabase role named Role1 and grand Role1 SELECT premissions to schema1. Box 3: Assing Role1 to the Group1 database user.
  • BBox 1: Assign Rolel to the Groupl database user. Box 2: Create a database role named Role1 and grant Rolel SELECT permissions to schemal. Box 3: Assign the Azure role-based access control (Azure RBAC) Reader role for dwl to Group1.
  • CBox 1: Assign the Azure role-based access control (Azure RBAC) Reader role for dwl to Group1. Box 2: Assign Rolel to the Groupl database user. Box 3: Create a database role named Role1 and grant Rolel SELECT permissions to schemal.
  • DBox 1: Assign the Azure role-based access control (Azure RBAC) Reader role for dwl to Group1. Box 2: Create a database role named Role1 and grant Rolel SELECT permissions to schemal. Box 3: Assign Rolel to the Groupl database user.
  Show answer
• #131

  You have an Azure Databricks workspace named workspace1 in the Standard pricing tier. You need to configure workspace1 to support autoscaling all-purpose clusters. The solution must meet the following requirements: Automatically scale down workers when the cluster is underutilized for three minutes. Minimize the time it takes to scale to the maximum number of workers. Minimize costs. What should you do first?

  • AEnable container services for workspace1.
  • BUpgrade workspace1 to the Premium pricing tier.
  • CSet Cluster Mode to High Concurrency.
  • DCreate a cluster policy in workspace1.
  Show answer
• #132

  You configure monitoring for a Microsoft Azure SQL Data Warehouse implementation. The implementation uses PolyBase to load data from comma-separated value (CSV) files stored in Azure Data Lake Gen 2 using an external table. Files with an invalid schema cause errors to occur. You need to monitor for an invalid schema error. For which error should you monitor?

  • AEXTERNAL TABLE access failed due to internal error: "Java exception raised on call to HdfsBridge_Connect: Error [com.microsoft.polybase.client.KerberosSecureLogin] occurred while accessing external files.".
  • BEXTERNAL TABLE access failed due to internal error: Java exception raised on call to HdfsBridge_Connect: Error [No FileSystem for scheme: wasbs] occurred while accessing external file.
  • CCannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11": for linked server "(null)", Query aborted the maximum reject threshold (o rows) was reached while regarding from an external source: 1 rows rejected out of total 1 rows processed.
  • DEXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect: Error [Unable to instantiate LoginClass] occurred while accessing external files.
  Show answer
• #133

  You have a SQL pool in Azure Synapse. You plan to load data from Azure Blob storage to a staging table. Approximately 1 million rows of data will be loaded daily. The table will be truncated before each daily load. You need to create the staging table. The solution must minimize how long it takes to load the data to the staging table. How should you configure the table?

  • ADistribution: Hash. Indexing: Clustered columnstore. Partitioning: Date.
  • BDistribution: Replicated. Indexing: Clustered. Partitioning: Date.
  • CDistribution: Replicated. Indexing: Heap. Partitioning: None.
  • DDistribution: Round-robin. Indexing: Clustered. Partitioning: None.
  Show answer
• #134

  You are building an Azure Stream Analytics job to retrieve game data. You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game. How should you complete the Stream Analytics query?

  • ASELECT: TopOne OVER(PARTITION BY Game ORDER BY Score Desc). GROUP BY: Tumbling(minute, 5).
  • BSELECT: CollectTop(1)OVER(ORDER BY Score Desc). GROUP BY: Tumbling(minute, 5).
  • CSELECT: TopOne OVER(PARTITION BY Game ORDER BY Score Desc). GROUP BY: Hopping(minute, 5).
  • DSELECT: CollectTop(1)OVER(ORDER BY Score Desc). GROUP BY: Windows(TumblingWindow(minute,5),Hopping(minute,5)).
  Show answer
• #135Choose 2

  You are creating an Azure Data Factory data flow that will ingest data from a CSV file, cast columns to specified types of data, and insert the data into a table in an Azure Synapse Analytic dedicated SQL pool. The CSV file contains three columns named username, comment, and date. The data flow already contains the following: A source transformation. A Derived Column transformation to set the appropriate types of data. A sink transformation to land the data in the pool. You need to ensure that the data flow meets the following requirements: All valid rows must be written to the destination table. Truncation errors in the comment column must be avoided proactively. Any rows containing comment values that will cause truncation errors upon insert must be written to a file in blob storage. Which two actions should you perform?

  • ATo the data flow, add a sink transformation to write the rows to a file in blob storage.
  • BTo the data flow, add a Conditional Split transformation to separate the rows that will cause truncation errors.
  • CTo the data flow, add a filter transformation to filter out rows that will cause truncation errors.
  • DAdd a select transformation to select only the rows that will cause truncation errors.
  Show answer
• #136

  You are designing an Azure Stream Analytics solution that will analyze Twitter data. You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once. Solution: You use a hopping window that uses a hop size of 5 seconds and a window size 10 seconds. Does this meet the goal?

  • AYes.
  • BNo.
  Show answer
• #137

  You have an Azure subscription that contains an Azure Blob Storage account named storage1 and an Azure Synapse Analytics dedicated SQL pool named Pool1. You need to store data in storage1. The data will be read by Pool1. The solution must meet the following requirements: Enable Pool1 to skip columns and rows that are unnecessary in a query. Automatically create column statistics. Minimize the size of files. Which type of file should you use?

  • AJSON.
  • BParquet.
  • CAvro.
  • DCSV.
  Show answer
• #138

  You have an activity in an Azure Data Factory pipeline. The activity calls a stored procedure in a data warehouse in Azure Synapse Analytics and runs daily. You need to verify the duration of the activity when it ran last. What should you use?

  • Aactivity runs in Azure Monitor.
  • BActivity log in Azure Synapse Analytics.
  • Cthe sys.dm_pdw_wait_stats data management view in Azure Synapse Analytics.
  • Dan Azure Resource Manager template.
  Show answer
• #139Choose 2

  You have an Azure Data Lake Storage Gen2 account named storage1. You plan to implement query acceleration for storage1. Which two file types support query acceleration? Each correct answer presents a complete solution.

  • AJSON.
  • BApache Parquet.
  • CXML.
  • DCSV.
  • EAvro.
  Show answer
• #140

  You use Azure Data Factory to prepare data to be queried by Azure Synapse Analytics serverless SQL pools. Files are initially ingested into an Azure Data Lake Storage Gen2 account as 10 small JSON files. Each file contains the same data attributes and data from a subsidiary of your company. You need to move the files to a different folder and transform the data to meet the following requirements: Provide the fastest possible query times. Automatically infer the schema from the underlying files. How should you configure the Data Factory copy activity?

  • ACopy behavior: Merge files. Sink file type: CSV.
  • BCopy behavior: Preserve hierarchy. Sink file type: Parquet.
  • CCopy behavior: Merge files. Sink file type: Parquet.
  • DCopy behavior: Preserve hierarchy. Sink file type: JSON.
  Show answer

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