logo
Exam overview and study plan

DP-700 Practice Questions

Fabric Data Engineer Associate

Read questions, check verified answers and discuss the reasoning. Use reading options to shape your revision, or switch to the simulator for timed practice.

View 69 free questions

69 questions in this view

Page 1 of 12

Question 1

Overview

Company Overview

Contoso, Ltd. is an online retail company that wants to modernize its analytics platform by moving to Fabric. The company plans to begin using Fabric for marketing analytics.

IT Structure

The company’s IT department has a team of data analysts and a team of data engineers that use analytics systems.

The data engineers perform the ingestion, transformation, and loading of data. They prefer to use Python or SQL to transform the data.

The data analysts query data and create semantic models and reports. They are qualified to write queries in Power Query and T-SQL.

Existing Environment

Fabric

Contoso has an F64 capacity named Cap1. All Fabric users are allowed to create items.

Contoso has two workspaces named WorkspaceA and WorkspaceB that currently use Pro license mode.

Source Systems

Contoso has a point of sale (POS) system named POS1 that uses an instance of SQL Server on Azure Virtual Machines in the same Microsoft Entra tenant as Fabric. The host virtual machine is on a private virtual network that has public access blocked. POS1 contains all the sales transactions that were processed on the company’s website.

The company has a software as a service (SaaS) online marketing app named MAR1. MAR1 has seven entities. The entities contain data that relates to email open rates and interaction rates, as well as website interactions. The data can be exported from MAR1 by calling REST APIs. Each entity has a different endpoint.

Contoso has been using MAR1 for one year. Data from prior years is stored in Parquet files in an Amazon Simple Storage Service (Amazon S3) bucket. There are 12 files that range in size from 300 MB to 900 MB and relate to email interactions.

Product Data

  • Products
  • ProductCategories
  • ProductSubcategories

Azure

  • DataAnalysts: Contains the data analysts
  • DataEngineers: Contains the data engineers

User Problems

The VP of marketing at Contoso requires analysis on the effectiveness of different types of email content. It typically takes a week to manually compile and analyze the data. Contoso wants to reduce the time to less than one day by using Fabric.

The data engineering team has successfully exported data from MAR1. The team experiences transient connectivity errors, which causes the data exports to fail.

Requirements

Planned Changes

  • Lakehouse1: Will store both raw and cleansed data from the sources
  • Lakehouse2: Will serve data in a dimensional model to users for analytical queries

Technical Requirements

  • Minimize egress costs associated with cross-cloud data access.
  • Prevent saving a copy of the raw data in the lakehouses.
  • The items must be source controlled alongside other workspace items.
  • Ingested data must land in the bronze layer of Lakehouse1 in the Delta format.
  • No changes other than changes to the file formats must be implemented before the data lands in the bronze layer.
  • Development effort must be minimized and a built-in connection must be used to import the source data.
  • In the event of a connectivity error, the ingestion processes must attempt the connection again.

Data Transformation

In the POS1 product data, ProductID values are unique. The product dimension in the gold layer must include only active products from the product list. Active products are identified by an IsActive value of 1.

Some product categories and subcategories are NOT assigned to any products. They are NOT analytically relevant and must be omitted from the product dimension in the gold layer.

Data Security

  • The data engineers must have read and write access to all the lakehouses, including the underlying files.
  • The data analysts must only have read access to the Delta tables in the gold layer.
  • The data analysts must NOT have access to the data in the bronze and silver layers.
  • The data engineers must be able to commit changes to source control in WorkspaceA.
You need to recommend a method to populate the POS1 data to the lakehouse medallion layers.

What should you recommend for each layer? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.
Bronze layer:
Silver layer:

Question 2

Overview

Company Overview

Contoso, Ltd. is an online retail company that wants to modernize its analytics platform by moving to Fabric. The company plans to begin using Fabric for marketing analytics.

IT Structure

The company’s IT department has a team of data analysts and a team of data engineers that use analytics systems.

The data engineers perform the ingestion, transformation, and loading of data. They prefer to use Python or SQL to transform the data.

The data analysts query data and create semantic models and reports. They are qualified to write queries in Power Query and T-SQL.

Existing Environment

Fabric

Contoso has an F64 capacity named Cap1. All Fabric users are allowed to create items.

Contoso has two workspaces named WorkspaceA and WorkspaceB that currently use Pro license mode.

Source Systems

Contoso has a point of sale (POS) system named POS1 that uses an instance of SQL Server on Azure Virtual Machines in the same Microsoft Entra tenant as Fabric. The host virtual machine is on a private virtual network that has public access blocked. POS1 contains all the sales transactions that were processed on the company’s website.

The company has a software as a service (SaaS) online marketing app named MAR1. MAR1 has seven entities. The entities contain data that relates to email open rates and interaction rates, as well as website interactions. The data can be exported from MAR1 by calling REST APIs. Each entity has a different endpoint.

Contoso has been using MAR1 for one year. Data from prior years is stored in Parquet files in an Amazon Simple Storage Service (Amazon S3) bucket. There are 12 files that range in size from 300 MB to 900 MB and relate to email interactions.

Product Data

  • Products
  • ProductCategories
  • ProductSubcategories

Azure

  • DataAnalysts: Contains the data analysts
  • DataEngineers: Contains the data engineers

User Problems

The VP of marketing at Contoso requires analysis on the effectiveness of different types of email content. It typically takes a week to manually compile and analyze the data. Contoso wants to reduce the time to less than one day by using Fabric.

The data engineering team has successfully exported data from MAR1. The team experiences transient connectivity errors, which causes the data exports to fail.

Requirements

Planned Changes

  • Lakehouse1: Will store both raw and cleansed data from the sources
  • Lakehouse2: Will serve data in a dimensional model to users for analytical queries

Technical Requirements

  • Minimize egress costs associated with cross-cloud data access.
  • Prevent saving a copy of the raw data in the lakehouses.
  • The items must be source controlled alongside other workspace items.
  • Ingested data must land in the bronze layer of Lakehouse1 in the Delta format.
  • No changes other than changes to the file formats must be implemented before the data lands in the bronze layer.
  • Development effort must be minimized and a built-in connection must be used to import the source data.
  • In the event of a connectivity error, the ingestion processes must attempt the connection again.

Data Transformation

In the POS1 product data, ProductID values are unique. The product dimension in the gold layer must include only active products from the product list. Active products are identified by an IsActive value of 1.

Some product categories and subcategories are NOT assigned to any products. They are NOT analytically relevant and must be omitted from the product dimension in the gold layer.

Data Security

  • The data engineers must have read and write access to all the lakehouses, including the underlying files.
  • The data analysts must only have read access to the Delta tables in the gold layer.
  • The data analysts must NOT have access to the data in the bronze and silver layers.
  • The data engineers must be able to commit changes to source control in WorkspaceA.
You need to schedule the population of the medallion layers to meet the technical requirements.

What should you do?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 3

You have a Fabric warehouse named Warehouse1 that contains a table named Table1. Table1 contains the following columns that store string values:

 

  • FirstName
  • Phone
  • Email

 

You have two Microsoft Entra security groups named Group1 and Group2. The members of both groups can query Table1 and see full values for FirstName, Phone, and Email.

 

You need to configure Table1 to meet the following requirements:

 

  • The members of Group1 must see masked values for Phone, showing only the last three characters of each phone number.
  • The Group1 members must see unmasked values for FirstName.
  • The members of Group2 must see unmasked values for all the columns.
  • The solution must use Fabric warehouse-supported permissions and T-SQL.

 

What should you configure? To answer, select the appropriate options in the answer area.

 

NOTE: Each correct selection is worth one point.

Masking function for Table1:
Permission to allow Group2 to see unmasked values for all the columns:

Question 4

You have a Fabric workspace that contains a warehouse named WH1.

 

Daily sales files are delivered as CSV files to an Azure Data Lake Storage Gen2 account named storage1.

 

You need to ingest the daily sales files into WH1 every night, validate the row counts, and then execute a T-SQL statement to update the load metadata. The solution must meet the following requirements:

 

  • Run repeatedly on a schedule.
  • Load the data from storage1 into WH1.
  • Use a code-free or low-code experience.
  • Support the orchestration of multiple activities in a single workflow.

 

What should you use? To answer, select the appropriate options in the answer area.

 

NOTE: Each correct selection is worth one point.

Primary ingestion method:
To update the load metadata:

Question 5

You have a Fabric workspace named Workspace1 that uses Git integration and is connected to the main branch of an Azure Repos Git repository named Repo1.

 

Multiple developers sync from Workspace1.

 

A hotfix was introduced two commits ago in main. The most recent commit in main contains unrelated changes that are NOT approved for release.

 

You need to deploy only the hotfix, while keeping the main branch history intact. The solution must minimize disruptions for other developers working in main.

 

What should you do?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.

Question 6

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have a Fabric eventstream that loads data into a table named bike_Location in a KQL database. The table contains the following columns:

  • BikepointID
  • Street
  • Neighbourhood
  • No_Bikes
  • No_Empty_Docks
  • Timestamp

You need to apply transformation and filter logic to prepare the data for consumption. The solution must return data for a neighbourhood named Sands End when No_Bikes is at least 15. The results must be ordered by No_Bikes in ascending order.

Solution: You use the following code segment:

bike_location
| where Neighbourhood == "Sands End" and No_Bikes >= 15
| sort by No_Bikes
| project BikepointID, Street, Neighbourhood, No_Bikes, No_Empty_Docks, Timestamp
| project BikepointID, Street, Neighbourhood, No_Bikes, No_Empty_Docks, Timestamp

Does this meet the goal?

Select an answer above, then submit your vote. Reading or practicing does not cast a vote.