Data Engineering on Microsoft Azure Test 20

The Microsoft Azure Data engineer mock test is an excellent way to validate your knowledge of Azure Data engineering skills. Employers respect certifications, but they expect more than exam readiness.

To get hired, you need proficiency in visualization tools like Power BI and Tableau, querying languages like SQL, and statistical platforms such as SAS. Data engineering skills in Snowflake, Databricks, and PySpark are critical for managing pipelines.

On top of that, data science and AI expertise in TensorFlow, PyTorch, LLMs, and Generative AI are increasingly required. Modern roles blend data analysis, engineering, and AI, demanding versatility. SynergisticIT’s Data Science JOPP program ensures you go beyond certification, equipping you with advanced skills, project work, and interview preparation to thrive in today’s competitive market

Welcome to your Data Engineering on Microsoft Azure - Test-20

Name

Phone

Q1. A company KloudSaga uses Azure Stream Analytics to monitor devices.

The company plans to double the number of devices that are monitored.
You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?

Select 1 option(s):

Early Input Events

Late Input Events

Watermark delay

Input Deserialization Errors

Q2. You have an Azure Synapse Analytics SQL pool named Pool1. In Azure Active Directory (Azure AD), you have a security group named Group1.

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? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

GRANT
CREATE SECURITY POLICY

CREATE CRYPTOGRAPHIC PROVIDER
CREATE PARTITION FUNCTION

CREATE PARTITION FUNCTION
CREATE SECURITY POLICY

CREATE SECURITY POLICY
GRANT

Q3. You need to implement an Azure Databricks cluster that automatically connects to Azure Data Lake Storage Gen2 by using Azure Active Directory (Azure AD) integration.

How should you configure the new cluster? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Premium
Azure Data Lake Storage Credential Passthrough

Standard
Table Access Control

Premium
Table Access Control

Standard
Azure Data Lake Storage Credential Passthrough

Q4. You are designing database for an Azure Synapse Analytics dedicated SQL pool to support workloads for detecting ecommerce transaction fraud.

Data will be combined from multiple ecommerce sites and can include sensitive financial information such as credit card numbers.
You need to recommend a solution that meets the following requirements:
Users must be able to identify potentially fraudulent transactions.
Users must be able to use credit cards as a potential feature in models.
Users must NOT be able to access the actual credit card numbers.
What should you include in the recommendation?

Select 1 option(s):

Transparent Data Encryption (TDE)

row-level security (RLS)

column-level encryption

Azure Active Directory (Azure AD) pass-through authentication

Q5. You have an Azure subscription linked to an Azure Active Directory (Azure AD) tenant that contains a service principal named ServicePrincipal1. The subscription contains an Azure Data Lake Storage account named adls1. Adls1 contains a folder named Folder2 that has a URI of https://adls1.dfs.core.windows.net/ container1/Folder1/Folder2/.

ServicePrincipal1 has the access control list (ACL) permissions shown in the following table.

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? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 2 option(s):

Access Read

Access Write

Access Execute

Default Read

Default Write

Default Execute

Q6. You have an Azure Synapse Analytics dedicated SQL pool.

You need to ensure that data in the pool is encrypted at rest. The solution must NOT require modifying applications that query the data.
What should you do?

Select 1 option(s):

Enable encryption at rest for the Azure Data Lake Storage Gen2 account.

Enable Transparent Data Encryption (TDE) for the pool.

Use a customer-managed key to enable double encryption for the Azure Synapse workspace.

Create an Azure key vault in the Azure subscription grant access to the pool.

Q7. You have an Azure subscription that contains an Azure Data Lake Storage Gen2 account named storage1. Storage1 contains a container named container1.

Container1 contains a directory named directory1. Directory1 contains a file named file1.
You have an Azure Active Directory (Azure AD) user named User1 that is assigned the Storage Blob Data Reader role for storage1.
You need to ensure that User1 can append data to file1. The solution must use the principle of least privilege.
Which permissions should you grant? To answer, drag the appropriate permissions to the correct resources. Each permission 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.
Select and Place:

Select 1 option(s):

Execute
Execute
Write

Read
Write
Execute

Write
Write
Execute

Read
Execute
Read

Q8. You have an Azure subscription that contains an Azure Databricks workspace named databricks1 and an Azure Synapse Analytics workspace named synapse1.

The synapse1 workspace contains an Apache Spark pool named pool1.
You need to share an Apache Hive catalog of pool1 with databricks1.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Azure SQL Database
A Hive metastore

Azure Cosmos DB
A managed Hive metastore service

Azure Data Lake Storage Gent
An Azure Purview account

Azure SQL Database
A managed Hive metastore service

Q9. You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.

You need to alter the table to meet the following requirements:
Ensure that users can identify the current manager of employees.
Support creating an employee reporting hierarchy for your entire company.
Provide fast lookup of the managers‘ attributes such as name and job title.
Which column should you add to the table?

Select 1 option(s):

[ManagerEmployeeID] [smallint] NULL

[ManagerEmployeeKey] [smallint] NULL

[ManagerEmployeeKey] [int] NULL

[ManagerName] [varchar](200) NULL

Q1o. You have an Azure Synapse workspace named MyWorkspace that contains an Apache Spark database named mytestdb.

You run the following command in an Azure Synapse Analytics Spark pool in MyWorkspace.
CREATE TABLE mytestdb.myParquetTable(
EmployeeID int,
EmployeeName string,
EmployeeStartDate date)
USING Parquet –
You then use Spark to insert a row into mytestdb.myParquetTable. The row contains the following data.

One minute later, you execute the following query from a serverless SQL pool in MyWorkspace.
SELECT EmployeeID –
FROM mytestdb.dbo.myParquetTable
WHERE name = ‘Alice‘;
What will be returned by the query?

Select 1 option(s):

an error

a null value

Q11. 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? 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:

Select 1 option(s):

Create an empty table named SalesFact_Work that has the same schema as SalesFact.
Switch the partition containing the stale data from SalesFact to SalesFact_Work.
Drop the SalesFact_Work table.

Truncate the partition containing the stale data.
Drop the SalesFact_Work table.
Create an empty table named SalesFact_Work that has the same schema as SalesFact.

Execute a DELETE statement where the value in the Date column is more than 36 months ago.

Switch the partition containing the stale data from SalesFact to SalesFact_Work.
Copy the data to a new table by using CREATE TABLE AS SELECT (CTAS).

Q12. 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?

Select 1 option(s):

File2.csv and File3.csv only

File1.csv and File4.csv only

File1.csv, File2.csv, File3.csv, and File4.csv

File1.csv only

Q13. You are planning the deployment of Azure Data Lake Storage Gen2.

You have the following two reports that will access the data lake:
Report1: Reads three columns from a file that contains 50 columns.
Report2: Queries a single record based on a timestamp.
You need to recommend in which format to store the data in the data lake to support the reports. The solution must minimize read times.
What should you recommend for each report? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Parquet
Avro

Avro
CSV

TSV
CSV

Avro
Parquet

Q14. You are designing the folder structure for an Azure Data Lake Storage Gen2 container.

Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQL pools. The data will be secured by subject area. Most queries will include data from the current year or current month.
Which folder structure should you recommend to support fast queries and simplified folder security?

Select 1 option(s):

/{SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}.csv

/{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}.csv

Q15. You need to output files from Azure Data Factory.

Which file format should you use for each type of output? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

TXT
Parquet

Parquet
Avro

Avro
GZip

GZip
Parquet

Q16. 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? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Merge files
Parquet

Preserve hierarchy
TXT

Flatten hierarchy
Parquet

Merge files
CSV

Q17. You have a data model that you plan to implement in a data warehouse in Azure Synapse Analytics as shown in the following exhibit.

All the dimension tables will be less than 2 GB after compression, and the fact table will be approximately 6 TB. The dimension tables will be relatively static with very few data inserts and updates.
Which type of table should you use for each table? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Replicated
Replicated
Replicated
Hash distributed

Hash distributed
Hash distributed
Replicated
Replicated

Replicated
Hash distributed
Replicated
Hash distributed

Q18. You have an Azure Data Lake Storage Gen2 container.

Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files.
You need to design a data archiving solution that meets the following requirements:
New data is accessed frequently and must be available as quickly as possible.
Data that is older than five years is accessed infrequently but must be available within one second when requested.
Data that is older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible.
Costs must be minimized while maintaining the required availability.
How should you manage the data? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point
Hot Area:

Select 1 option(s):

Delete the blob.
Move to hot storage.

Move to cool storage.
Move to hot storage.

Move to archive storage.
Move to hot storage.

Move to cool storage.
Move to archive storage.

Q19. You need to implement a Type 3 slowly changing dimension (SCD) for product category data in an Azure Synapse Analytics dedicated SQL pool.

You have a table that was created by using the following Transact-SQL statement.

Which two columns should you add to the table? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 2 option(s):

[EffectiveStartDate] [datetime] NOT NULL,

[CurrentProductCategory] [nvarchar] (100) NOT NULL,

[EffectiveEndDate] [datetime] NULL,

[ProductCategory] [nvarchar] (100) NOT NULL,

[OriginalProductCategory] [nvarchar] (100) NOT NULL,

Q20. You have an Azure Storage account and a data warehouse in Azure Synapse Analytics in the UK South region.

You need to copy blob data from the storage account to the data warehouse by using Azure Data Factory. The solution must meet the following requirements:
Ensure that the data remains in the UK South region at all times.
Minimize administrative effort.
Which type of integration runtime should you use?

Select 1 option(s):

Azure integration runtime

Azure-SSIS integration runtime

Self-hosted integration runtime

Q21. You have an Azure Stream Analytics job that receives clickstream data from an Azure event hub.

You need to define a query in the Stream Analytics job. The query must meet the following requirements:
Count the number of clicks within each 10-second window based on the country of a visitor.
Ensure that each click is NOT counted more than once.
How should you define the query?

Select 1 option(s):

SELECT Country, Count(*) AS Count FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, TumblingWindow(second, 10)

SELECT Country, Count(*) AS Count FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, SessionWindow(second, 5, 10)

SELECT Country, Avg(*) AS Average FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, SlidingWindow(second, 10)

SELECT Country, Avg(*) AS Average FROM ClickStream TIMESTAMP BY CreatedAt GROUP BY Country, HoppingWindow(second, 10, 2)

Q22. You need to schedule an Azure Data Factory pipeline to execute when a new file arrives in an Azure Data Lake Storage Gen2 container.

Which type of trigger should you use?

Select 1 option(s):

on-demand

tumbling window

schedule

event

Q23. 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?

Select 1 option(s):

From ADFdev, modify the Git configuration.

From ADFdev, create a linked service.

From Azure DevOps, create a release pipeline.

From Azure DevOps, update the main branch.

Q4. You are developing a solution that will stream to Azure Stream Analytics. The solution will have both streaming data and reference data.

Which input type should you use for the reference data?

Select 1 option(s):

Azure Cosmos DB

Azure Blob storage

Azure IoT Hub

Azure Event Hubs

Q25. You are designing an Azure Stream Analytics job to process incoming events from sensors in retail environments.

You need to process the events to produce a running average of shopper counts during the previous 15 minutes, calculated at five-minute intervals.
Which type of window should you use?

Select 1 option(s):

snapshot

tumbling

hopping

sliding

Q26. You are monitoring an Azure Stream Analytics job by using metrics in Azure.

You discover that during the last 12 hours, the average watermark delay is consistently greater than the configured late arrival tolerance.
What is a possible cause of this behavior?

Select 1 option(s):

Events whose application timestamp is earlier than their arrival time by more than five minutes arrive as inputs.

There are errors in the input data.

The late arrival policy causes events to be dropped.

The job lacks the resources to process the volume of incoming data

Q27. You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval. The output will be sent to a Delta Lake table.

Which output mode should you use?

Select 1 option(s):

update

complete

append

Q28. 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? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 2 option(s):

sensitivity-classification labels applied to columns that contain confidential information

resource tags for databases that contain confidential information

audit logs sent to a Log Analytics workspace

dynamic data masking for columns that contain confidential information

Q29. You have an Azure Data Factory version 2 (V2) resource named Df1. Df1 contains a linked service.

You have an Azure Key vault named vault1 that contains an encryption key named key1.
You need to encrypt Df1 by using key1.
What should you do first?

Select 1 option(s):

Add a private endpoint connection to vaul1.

Enable Azure role-based access control on vault1.

Remove the linked service from Df1.

Create a self-hosted integration runtime.

Q30. You develop data engineering solutions for a company KloudSaga.

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? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 3 option(s):

Create security groups in Azure Active Directory (Azure AD) and add project members.

Configure end-user authentication for the Azure Data Lake Storage account.

Assign Azure AD security groups to Azure Data Lake Storage.

Configure Service-to-service authentication for the Azure Data Lake Storage account.

Configure access control lists (ACL) for the Azure Data Lake Storage account

Q31. You are designing an Azure Synapse Analytics dedicated SQL pool.

You need to ensure that you can audit access to Personally Identifiable Information (PII).
What should you include in the solution?

Select 1 option(s):

column-level security

dynamic data masking

row-level security (RLS)

sensitivity classifications

Q32. You have a data warehouse in Azure Synapse Analytics.

You need to ensure that the data in the data warehouse is encrypted at rest.
What should you enable?

Select 1 option(s):

Advanced Data Security for this database

Transparent Data Encryption (TDE)

Secure transfer required

Dynamic Data Masking

Q33. You have an Azure Synapse Analytics Apache Spark pool named Pool1.

You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file.
You need to load the files into the tables. The solution must maintain the source data types.
What should you do?

Select 1 option(s):

Use a Conditional Split transformation in an Azure Synapse data flow.

Use a Get Metadata activity in Azure Data Factory.

Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.

Load the data by using PySpark.

Q34. You have an Azure Databricks workspace named workspace1 in the Standard pricing tier. Workspace1 contains an all-purpose cluster named cluster1.

You need to reduce the time it takes for cluster1 to start and scale up. The solution must minimize costs.
What should you do first?

Select 1 option(s):

Configure a global init script for workspace1.

Create a cluster policy in workspace1.

Upgrade workspace1 to the Premium pricing tier.

Create a pool in workspace1.

Q35. You are building an Azure Stream Analytics job that queries reference data from a product catalog file. The file is updated daily.

The reference data input details for the file are shown in the Input exhibit. (Click the Input tab.)

The storage account container view is shown in the Refdata exhibit. (Click the Refdata tab.)

You need to configure the Stream Analytics job to pick up the new reference data.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

{date}/product.csv
YYYY-MM-DD

{date}/{time}/product.csv
MM/DD/YYYY

product.csv
YYYY/MM/DD

*/product.csv
YYYY-MM-DD

Q36. You have the following Azure Stream Analytics query.

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:

Select 1 option(s):

YES
NO
YES

YES
YES
NO

YES
YES
YES

YES
NO
NO

Q37. You are building a database in an Azure Synapse Analytics serverless SQL pool.

You have data stored in Parquet files in an Azure Data Lake Storege Gen2 container.
Records are structured as shown in the following sample.
{
“id“: 123,
“address_housenumber“: “19c“,
“address_line“: “Memory Lane“,
“applicant1_name“: “Jane“,
“applicant2_name“: “Dev“
}
The records contain two applicants at most.
You need to build a table that includes only the address fields.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

CREATE VIEW
CROSS APPLY

CREATE VIEW
OPENROWSET

CREATE TABLE
OPENJSON

CREATE EXTERNAL TABLE
OPENROWSET

Q38. You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and an Azure Data Lake Storage Gen2 account named Account1.

You plan to access the files in Account1 by using an external table.
You need to create a data source in Pool1 that you can reference when you create the external table.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

table
PUSHDOWN = ON

blob
TYPE = HADOOP

blob
TYPE = BLOB STORAGE

dfs
TYPE = HADOOP

Q39. 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?

Select 1 option(s):

JSON

Parquet

Avro

CSV

Q40. You plan to create a table in an Azure Synapse Analytics dedicated SQL pool.

Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted.
You need to ensure that the data is distributed evenly across partitions. The solution must minimize the amount of time required to delete old data.
How should you complete the Transact-SQL statement? 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:

Select 1 option(s):

OrderDateKey
SalesOrderNumber

REPLICATE
ROUND ROBIN

OrderDateKey
CustomerKey

HASH
OrderDateKey

Q41. You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.

You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files in container1 into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of
Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.
Does this meet the goal?

Select 1 option(s):

Yes

Q42. You have an Azure Data Lake Storage account that contains a staging zone.

You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes an Azure Databricks notebook, and then inserts the data into the data warehouse.
Does this meet the goal?

Select 1 option(s):

Yes

Q43. You need to trigger an Azure Data Factory pipeline when a file arrives in an Azure Data Lake Storage Gen2 container.

Which resource provider should you enable?

Select 1 option(s):

Microsoft.Sql

Microsoft.Automation

Microsoft.EventGrid

Microsoft.EventHub

Q44. You plan to perform batch processing in Azure Databricks once daily.

Which type of Databricks cluster should you use?

Select 1 option(s):

High Concurrency

Automated/Job

interactive

Q45. 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? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

MAX
TumblingWindow
DATEDIFF

TOPONE
TumblingWindow
DATENAME

MIN
SessionWindow
DATEPART

COUNT
SlidingWindow
DATEADD

Q46. You have an Azure Data Factory instance that contains two pipelines named Pipeline1 and Pipeline2.

Pipeline1 has the activities shown in the following exhibit.

Pipeline2 has the activities shown in the following exhibit.

You execute Pipeline2, and Stored procedure1 in Pipeline1 fails.
What is the status of the pipeline runs?

Select 1 option(s):

Pipeline1 and Pipeline2 succeeded.

Pipeline1 and Pipeline2 failed.

Pipeline1 succeeded and Pipeline2 failed.

Pipeline1 failed and Pipeline2 succeeded.

Q47. A company KloudSaga plans to use Platform-as-a-Service (PaaS) to create the new data pipeline process. The process must meet the following requirements:

Ingest:
Access multiple data sources.
Provide the ability to orchestrate workflow.
Provide the capability to run SQL Server Integration Services packages.
Store:
Optimize storage for big data workloads.
Provide encryption of data at rest.
Operate with no size limits.
Prepare and Train:
Provide a fully-managed and interactive workspace for exploration and visualization.
Provide the ability to program in R, SQL, Python, Scala, and Java.
Provide seamless user authentication with Azure Active Directory
Model & Serve:
Implement native columnar storage.
Support for the SQL language
Provide support for structured streaming.
You need to build the data integration pipeline.
Which technologies should you use? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Azure Data Factory
Azure Data Lake Storage
Azure Databricks
Azure Synapse Analytics

Logic Apps
Azure files
HDlnsight Apache Storm cluster
Azure Data Lake Storage

Azure Automation
Azure Blob storage
HDlnsight Apache Spark cluster
HDlnsight Apache Kafka cluster

Q48. You have an Azure Data Factory that contains 10 pipelines.

You need to label each pipeline with its main purpose of either ingest, transform, or load. The labels must be available for grouping and filtering when using the monitoring experience in Data Factory.
What should you add to each pipeline?

Select 1 option(s):

a resource tag

a correlation ID

a run group ID

an annotation

Q49. You have an Azure Data Lake Storage account that contains a staging zone.

You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes mapping data flow, and then inserts the data into the data warehouse.
Does this meet the goal?

Select 1 option(s):

Yes

Q50. You have an Azure Data Lake Storage account that contains a staging zone.

You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You schedule an Azure Databricks job that executes an R notebook, and then inserts the data into the data warehouse.
Does this meet the goal?

Select 1 option(s):

Yes

Q51. You plan to create an Azure Data Factory pipeline that will include a mapping data flow.

You have JSON data containing objects that have nested arrays.
You need to transform the JSON-formatted data into a tabular dataset. The dataset must have one row for each item in the arrays.
Which transformation method should you use in the mapping data flow?

Select 1 option(s):

new branch

unpivot

alter row

flatten

Q52. You have an Azure subscription that contains a logical Microsoft SQL server named Server1. Server1 hosts an Azure Synapse Analytics SQL dedicated pool named Pool1.

You need to recommend a Transparent Data Encryption (TDE) solution for Server1. The solution must meet the following requirements:
Track the usage of encryption keys.
Maintain the access of client apps to Pool1 in the event of an Azure datacenter outage that affects the availability of the encryption keys.
What should you include in the recommendation? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Always Encrypted
Enable Advanced Data Security on Served.

TDE with platform-managed keys
Implement the client apps by using a Microsoft .NET Framework data provider.

TDE with customer-managed keys
Create and configure Azure key vaults in two Azure regions.

Q53. 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?

Select 1 option(s):

an Azure Active Directory (Azure AD) user

a shared key

a shared access signature (SAS)

a managed identity

Q54. You plan to create an Azure Data Lake Storage Gen2 account in your company KloudSaga.

You need to recommend a storage solution that meets the following requirements:
Provides the highest degree of data resiliency
Ensures that content remains available for writes if a primary data center fails
What should you include in the recommendation? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

Read-access geo-zone-redundant storage (RA-GZRS)
Failover manually initiated by the customer

Change feed
Failover automatically initiated by an Azure Automation job

Read-access geo-redundant storage (RA-GRS)
Failover automatically initiated by an Azure Automation job

Zone-redundant storage (ZRS)
Failover initiated by Microsoft

Q55. 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.

You need to design the table storage for pool1. The solution must meet the following requirements:
Maximize the performance of data loading operations to Staging.WebSessions.
Minimize query times for reporting queries against the dimensional model.
Which type of table distribution should you use for each table? To answer, drag the appropriate table distribution types to the correct tables. Each table distribution type 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:

Select 1 option(s):

Replicated
Hash
Round-robin

Round-robin
Round-robin
Hash

Replicated
Hash
Replicated

Replicated
Round-robin
Hash

Q56. You have an Azure Synapse Analytics dedicated SQL pool.

You need to create a table named FactInternetSales that will be a large fact table in a dimensional model. FactInternetSales will contain 100 million rows and two columns named SalesAmount and OrderQuantity. Queries executed on FactInternetSales will aggregate the values in SalesAmount and OrderQuantity from the last year for a specific product. The solution must minimize the data size and query execution time.
How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Select 1 option(s):

( CLUSTERED COLUMNSTORE INDEX
Hash([ProductKey])

( CLUSTERED INDEX ([OrderDateKey])
REPUCATE

( HEAP
ROUND_ROBIN
( INDEX on [ProductKey]
Hash((OrderDateKey))

Q57. You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:

One billion rows
A clustered columnstore index
A hash-distributed column named Product Key
A column named Sales Date that is of the date data type and cannot be null
Thirty million rows will be added to Table1 each month.
You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.
How often should you create a partition?

Select 1 option(s):

once per month

once per year

once per day

once per week

Q58. You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.

Table1 is a Type 2 slowly changing dimension (SCD) table.
You need to apply updates from a source table to Table1.
Which Apache Spark SQL operation should you use?

Select 1 option(s):

CREATE

UPDATE

ALTER

MERGE

Q59. You are designing an Azure Data Lake Storage solution that will transform raw JSON files for use in an analytical workload.

You need to recommend a format for the transformed files. The solution must meet the following requirements:
Contain information about the data types of each column in the files.
Support querying a subset of columns in the files.
Support read-heavy analytical workloads.
Minimize the file size.
What should you recommend?

Select 1 option(s):

JSON

CSV

Apache Avro

Apache Parquet

Q60. 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 modify the files to ensure that each row is less than 1 MB.
Does this meet the goal?

Select 1 option(s):

Yes

Q61. You plan to create a dimension table in Azure Synapse Analytics that will be less than 1 GB.

You need to create the table to meet the following requirements:
Provide the fastest query time.
Minimize data movement during queries.
Which type of table should you use?

Select 1 option(s):

replicated

hash distributed

heap

round-robin

Q62. You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.

You need to create a surrogate key for the table. The solution must provide the fastest query performance.
What should you use for the surrogate key?

Select 1 option(s):

a GUID column

a sequence object

an IDENTITY column

Q63. You use Azure Stream Analytics to receive Twitter data from Azure Event Hubs and to output the data to an Azure Blob storage account.

You need to output the count of tweets during the last five minutes every five minutes. Each tweet must only be counted once.
Which windowing function should you use?

Select 1 option(s):

a five-minute Sliding window

a five-minute Session window

a five-minute Hopping window that has a one-minute hop

a five-minute Tumbling window

Q64. You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1.

You need to determine the size of the transaction log file for each distribution of DW1.
What should you do?

Select 1 option(s):

On DW1, execute a query against the sys.database_files dynamic management view.

From Azure Monitor in the Azure portal, execute a query against the logs of DW1.

Execute a query against the logs of DW1 by using the Get-AzOperationalInsightsSearchResult PowerShell cmdlet.

On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.

Q65. 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?

Select 1 option(s):

Azure Databricks

Azure Stream Analytics

Azure SQL Database

[/ppwp]