Data Engineering on Microsoft Azure

• #1

  You plan to create an Azure Databricks workspace that has a tiered structure. The workspace will contain the following three workloads: A workload for data engineers who will use Python and SQL. A workload for jobs that will run notebooks that use Python, Scala, and SQL. A workload that data scientists will use to perform ad hoc analysis in Scala and R. The enterprise architecture team at your company identifies the following standards for Databricks environments: The data engineers must share a cluster. The job cluster will be managed by using a request process whereby data scientists and data engineers provide packaged notebooks for deployment to the cluster. All the data scientists must be assigned their own cluster that terminates automatically after 120 minutes of inactivity. Currently, there are three data scientists. You need to create the Databricks clusters for the workloads. Solution: You create a Standard cluster for each data scientist, a High Concurrency cluster for the data engineers, and a High Concurrency cluster for the jobs. Does this meet the goal?

  • AYes.
  • BNo.
  Show answer
• #2

  You have an Apache Spark DataFrame named temperatures. A sample of the data is shown in the following table. You need to produce the following table by using a Spark SQL query. How should you complete the query?

  • ABox 1: PIVOT. Box 2: CAST.
  • BBox 1: COLLATE. Box 2: UNPIVOT.
  • CBox 1: CONVERT. Box 2: COLLATE.
  • DBox 1: FLATTEN. Box 2: PIVOT.
  Show answer
• #3

  Which Azure Data Factory components should you recommend using together to import the daily inventory data from the SQL server to Azure Data Lake Storage?

  • AIntegration runtime type: Azure integration runtime. Trigger type: Tumbling window trigger. Activity type: Stored procedure activity.
  • BIntegration runtime type: Azure integration runtime. Trigger type: Schedule trigger. Activity type: Lookup activity.
  • CIntegration runtime type: Azure-SSIS integration runtime. Trigger type: Event-based trigger. Activity type: Copy activity.
  • DIntegration runtime type: Self-hosted integration runtime. Trigger type: Schedule trigger. Activity type: Copy activity.
  Show answer
• #4

  You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications: Contain sales data for 20,000 products. Use hash distribution on a column named ProduclID, Contain 2.4 billion records for the years 20l9 and 2020. Which number of partition ranges provides optimal compression and performance of the clustered columnstore index?

  • A40.
  • B240.
  • C400.
  • D2,400.
  Show answer
• #5

  You are processing streaming data from vehicles that pass through a toll booth. You need to use Azure Stream Analytics to return the license plate, vehicle make, and hour the last vehicle passed during each 10-minute window. How should you complete the query?

  • ABox 1: COUNT. Box 2: HoppingWindow. Box 3: DATEADD.
  • BBox 1: MIN. Box 2: SessionWindow. Box 3: DATEPART.
  • CBox 1: TOPONE. Box 2: SlidingWindow. Box 3: DATENAME.
  • DBox 1: MAX. Box 2: TumblingWindow. Box 3: DATEDIFF.
  Show answer
• #6

  You have an Azure Data Lake Storage Gen2 account that contains two folders named Folder and Folder2. You use Azure Data Factory to copy multiple files from Folder1 to Folder2. You receive the following error. What should you do to resolve the error.

  • AAdd an explicit mapping.
  • BEnable fault tolerance to skip incompatible rows.
  • CLower the degree of copy parallelism.
  • DChange the Copy activity setting to Binary Copy.
  Show answer
• #7

  You are designing an Azure Stream Analytics solution that receives instant messaging data from an Azure Event Hub. You need to ensure that the output from the Stream Analytics job counts the number of messages per time zone every 15 seconds. How should you complete the Stream Analytics query?

  • AFROM MessageStream: LAST. GROUP BY TimeZone,: HOPPINGWINDOW.
  • BFROM MessageStream: OVER. GROUP BY TimeZone,: SESSIONWINDOW.
  • CFROM MessageStream: SYSTEM.TIMESTAMP(). GROUP BY TimeZone,: SLIDINGWINDOW.
  • DFROM MessageStream: TIMESTAMP BY. GROUP BY TimeZone,: TUMBLINGWINDOW.
  Show answer
• #8Choose 2

  You have an Azure Synapse Analytics dedicated SQL pool named pool1. You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code. You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD). Which two columns should you add? Each correct answer presents part of the solution.

  • A[HistoricalSalesPerson] [nvarchar] (256) NOT NULL
  • B[EffectiveEndDate] [datetime] NOT NULL
  • C[PreviousModifiedDate] [datetime] NOT NULL
  • D[RowID] [bigint] NOT NULL
  • E[EffectiveStartDate] [datetime] NOT NULL
  Show answer
• #9

  You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named account1 and an Azure Synapse Analytics workspace named workspace1. You need to create an external table in a serverless SQL pool in workspace1. The external table will reference CSV files stored in account1. The solution must maximize performance. How should you configure the external table?

  • AUse a native external table and authenticate by using a Shared Access Signature (SAS).
  • BUse a native external table and authenticate by using a storage account key.
  • CUse an Apache Hadoop external table and authenticate by using a Shared Access Signature (SAS).
  • DUse an Apache Hadoop external table and authenticate by using a service principal in Microsoft Azure Active Directory (Azure AD), part of Microsoft Entra.
  Show answer
• #10

  You plan to monitor an Azure data factory by using the Monitor & Manage app. You need to identify the status and duration of activities that reference a table in a source database. Which three actions should you perform in sequence?

  • ABox 1: From the Data Factory authoring Ul, generate a user property for Source on all activities. Box 2: From the Data Factory monitoring app, add the Source user property to the Pipeline Runs table. Box 3: From the Data Factory authoring Ul, publish the pipelines.
  • BBox 1: From the Data Factory authoring Ul, publish the pipelines. Box 2: From the Data Factory authoring Ul, publish the pipelines. Box 3: From the Data Factory authoring Ul, generate a user property for Source on all activities.
  • CBox 1: From the Data Factory authoring Ul, generate a user property for Source on all activities. Box 2: From the Data Factory monitoring app, add the Source user property to the Pipeline Runs table. Box 3: From the Data Factory authoring Ul, generate a user property for Source on all activities.
  • DBox 1: From the Data Factory authoring Ul, publish the pipelines. Box 2: From the Data Factory authoring Ul, generate a user property for Source on all activities. Box 3: From the Data Factory monitoring app, add the Source user property to the Pipeline Runs table.
  Show answer
• #11

  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 10 seconds and a window size of 10 seconds. Does this meet the goal?

  • AYes.
  • BNo.
  Show answer
• #12

  You have an Azure Data Lake Storage Gen2 account named adls2 that is protected by a virtual network. You are designing a SQL pool in Azure Synapse that will use adls2 as a source. What should you use to authenticate to adls2?

  • Aa Shared Access Signature (SAS).
  • Ba managed identity.
  • Ca shared key.
  • Dan Azure Active Directory (Azure AD) user.
  Show answer
• #13

  You have an Azure Storage account that generates 200,000 new files daily. The file names have a format of {YYYY}/{MM}/{DD}/{HH}/{CustomerID}.csv. You need to design an Azure Data Factory solution that will load new data from the storage account to an Azure Data Lake once hourly. The solution must minimize load times and costs. How should you configure the solution?

  • ALoad methodology: Incremental Load. Trigger: Tumbling window.
  • BLoad methodology: Full Load. Trigger: Tumbling window.
  • CLoad methodology: Incremental Load. Trigger: Fixed schedule.
  • DLoad methodology: Load Individual files as they arrive. Trigger: New file.
  Show answer
• #14

  You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements: Send the output to Azure Synapse. Identify spikes and dips in time series data. Minimize development and configuration effort. Which should you include in the solution?

  • AAzure Databricks.
  • BAzure Stream Analytics.
  • CAzure SQL Database.
  Show answer
• #15

  You have data stored in thousands of CSV files in Azure Data Lake Storage Gen2. Each file has a header row followed by a properly formatted carriage return (/r) and line feed (/n). You are implementing a pattern that batch loads the files daily into an enterprise data warehouse in Azure Synapse Analytics by using PolyBase. You need to skip the header row when you import the files into the data warehouse. Before building the loading pattern, you need to prepare the required database objects in Azure Synapse Analytics. Which three actions should you perform in sequence?

  • ABox 1: Create an external data source that uses the abfs location. Box 2: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages. Box 3: Create an external file format and set the Firat_Row option.
  • BBox 1: Create an external data source that uses the abfs location. Box 2: Create an external file format and set the Firat_Row option. Box 3: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages.
  • CBox 1: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages. Box 2: Create an external data source that uses the abfs location. Box 3: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages.
  • DBox 1: Create an external file format and set the Firat_Row option. Box 2: Use CREATE EXTERNAL TABLE AS SELECT (CETAS) and configure the reject options to specify reject values or percentages. Box 3: Create an external data source that uses the abfs location.
  Show answer
• #16

  You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit. You create an external table named ExtTable that has LOCATION='/topfolder/'. When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

  • AFile2.csv and File3.csv only.
  • BFile1.csv and File4.csv only.
  • CFile1.csv, File2.csv, File3.csv, and File4.csv.
  • DFile1.csv only.
  Show answer
• #17

  You are developing an application that uses Azure Data Lake Storage Gen 2. You need to recommend a solution to grant permissions to a specific application for a limited time period. What should you include in the recommendation?

  • AAzure Active Directory (Azure AD) identities.
  • BShared Access Signatures (SAS).
  • CAccount keys.
  • DRole assignments.
  Show answer
• #18Choose 3

  You develop data engineering solutions for a company. A project requires the deployment of data to Azure Data Lake Storage. You need to implement role-based access control (RBAC) so that project members can manage the Azure Data Lake Storage resources. Which three actions should you perform?

  • AAssign Azure AD security groups to Azure Data Lake Storage.
  • BConfigure end-user authentication for the Azure Data Lake Storage account.
  • CConfigure service-to-service authentication for the Azure Data Lake Storage account.
  • DCreate security groups in Azure Active Directory (Azure AD) and add project members.
  • EConfigure access control lists (ACL) for the Azure Data Lake Storage account.
  Show answer
• #19Choose 2

  You are designing a security model for an Azure Synapse Analytics dedicated SQL pool that will support multiple companies. You need to ensure that users from each company can view only the data of their respective company. Which two objects should you include in the solution?

  • ACustom role-based access control (RBAC) role.
  • BAsymmetric keys.
  • CPredicate function.
  • DColumn encryption key.
  • ESecurity policy.
  Show answer
• #20

  You have an Azure event hub named retailhub that has 16 partitions. Transactions are posted to retailhub. Each transaction includes the transaction ID, the individual line items, and the payment details. The transaction ID is used as the partition key. You are designing an Azure Stream Analytics job to identify potentially fraudulent transactions at a retail store. The job will use retailhub as the input. The job will output the transaction ID, the individual line items, the payment details, a fraud score, and a fraud indicator. You plan to send the output to an Azure event hub named fraudhub. You need to ensure that the fraud detection solution is highly scalable and processes transactions as quickly as possible. How should you structure the output of the Stream Analytics job?

  • ANumber of partitions: 32. Partition key: Transaction ID.
  • BNumber of partitions: 1. Partition key: Fraud indicator.
  • CNumber of partitions: 8. Partition key: Fraud score.
  • DNumber of partitions: 16. Partition key: Transaction ID.
  Show answer

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