10 free Microsoft DP-700 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 139 questions. Work through them, then open each answer to check your reasoning.
Get all 139 questions (US$39) · Download these 10 as a PDF
Question 1
Show the case study this question is based on
CASE STUDY
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study -To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions.
Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs.
When you are ready to answer a question, click the Question button to return to the question.
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.
Overview. 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.
Existing Environment. 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.
Existing Environment. Product Data POS1 contains a product list and related data. The data comes from the following three tables:
• Products
• ProductCategories
• ProductSubcategories
In the data, products are related to product subcategories, and subcategories are related to product categories.
Existing Environment. Azure
Contoso has a Microsoft Entra tenant that has the following mail-enabled security groups:
DataAnalysts: Contains the data analysts DataEngineers: Contains the data engineers Contoso has an Azure subscription.
The company has an existing Azure DevOps organization and creates a new project for repositories that relate to Fabric.
Existing Environment. 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
Contoso plans to create the following two lakehouses:
Lakehouse1: Will store both raw and cleansed data from the sources Lakehouse2: Will serve data in a dimensional model to users for analytical queries Additional items will be added to facilitate data ingestion and transformation.
Contoso plans to use Azure Repos for source control in Fabric.
Requirements. Technical Requirements
The new lakehouses must follow a medallion architecture by using the following three layers: bronze, silver, and gold. There will be extensive data cleansing required to populate the MAR1 data in the silver layer, including deduplication, the handling of missing values, and the standardizing of capitalization.
Each layer must be fully populated before moving on to the next layer. If any step in populating the lakehouses fails, an email must be sent to the data engineers.
Data imports must run simultaneously, when possible.
The use of email data from the Amazon S3 bucket must meet the following requirements:
Minimize egress costs associated with cross-cloud data access.
Prevent saving a copy of the raw data in the lakehouses.
Items that relate to data ingestion must meet the following requirements:
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.
Lakehouses, data pipelines, and notebooks must be stored in WorkspaceA. Semantic models, reports, and dataflows must be stored in WorkspaceB.
Once a week, old files that are no longer referenced by a Delta table log must be removed.
Requirements. Data Transformation In the POS1 product data, ProductID values are unique. The product dimension in the gold layer must include only active products from product list. Active products are identified by an IsActive value of 1.
Some product categories and subcategories are NOT assigned to any product. They are NOT analytically relevant and must be omitted from the product dimension in the gold layer.
Requirements. Data Security
Security in Fabric must meet the following requirements:
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 ensure that the data analysts can access the gold layer lakehouse.
What should you do?
Show answer and explanation
Correct answer: C. Share the lakehouse with the DataAnalysts group and grant the Read all SQL Endpoint data permission.
The data analysts need read-only access to the Delta tables in the gold layer and nothing else. Sharing the lakehouse and granting the Read all SQL Endpoint data permission lets them query the gold tables through the SQL analytics endpoint without any access to the underlying OneLake files. Because all lakehouses live in WorkspaceA, item level sharing is the only way to expose the gold lakehouse without exposing the bronze and silver data in Lakehouse1.
Why the other options are wrong
- A. Adding DataAnalysts to the Viewer role for WorkspaceA grants access to every item in that workspace, including Lakehouse1 with its bronze and silver data.
- B. Build reports on the default semantic model only enables report creation on that model, it does not provide the read access to the gold Delta tables that the analysts require.
- D. Read all Apache Spark grants access to the lakehouse files and folders through Spark, which goes beyond the read-only Delta table access the analysts are allowed.
Question 2
You have a Fabric workspace.
You have semi-structured data.
You need to read the data by using T-SQL, KQL, and Apache Spark. The data will only be written by using Spark.
What should you use to store the data?
Show answer and explanation
Correct answer: A. a lakehouse
A lakehouse stores the data as Delta tables in OneLake, which Spark writes to directly. The same tables are readable with T-SQL through the SQL analytics endpoint, and they can be surfaced for KQL queries by creating a OneLake shortcut from a KQL database in an eventhouse. That combination of one Spark write path and three read engines matches the requirement.
Why the other options are wrong
- B. An eventhouse is built for high volume streaming and telemetry ingestion queried with KQL, so it is not the store for Spark-written semi-structured data.
- C. A datamart is a Power Query driven SQL store for structured tabular data and offers no Spark write path.
- D. A warehouse is written and queried with T-SQL, and Spark can only read from it, not write to it.
Question 3
You have a Fabric workspace that contains a warehouse named Warehouse1.
You have an on-premises Microsoft SQL Server database named Database1 that is accessed by using an on-premises data gateway.
You need to copy data from Database1 to Warehouse1.
Which item should you use?
Show answer and explanation
Correct answer: B. a data pipeline
A data pipeline Copy activity supports connections that use an on-premises data gateway and can write directly into a Fabric warehouse. It is the built-in, low code way to move the Database1 tables into Warehouse1 and can be scheduled and monitored.
Why the other options are wrong
- A. A Dataflow Gen1 dataflow can only output to Power BI dataflow storage, so it cannot load data into a Fabric warehouse at all.
- C. A KQL queryset only runs queries against KQL databases and cannot copy data from SQL Server.
- D. A notebook runs on Spark compute that cannot use an on-premises data gateway to reach Database1.
Question 4
You have a Fabric workspace that contains a warehouse named Warehouse1.
You have an on-premises Microsoft SQL Server database named Database1 that is accessed by using an on-premises data gateway.
You need to copy data from Database1 to Warehouse1.
Which item should you use?
Show answer and explanation
Correct answer: B. a data pipeline
A data pipeline Copy activity works with on-premises data gateway connections and supports a Fabric warehouse as the destination. It gives a built-in, schedulable ingestion path from Database1 into Warehouse1 with no custom code.
Why the other options are wrong
- A. An Apache Spark job definition runs on Spark compute, which cannot connect through an on-premises data gateway.
- C. A Dataflow Gen1 dataflow writes only to Power BI dataflow storage and cannot load a Fabric warehouse.
- D. An eventstream handles streaming event sources and does not perform gatewa-ased batch copies from SQL Server.
Question 5
You have a Fabric F32 capacity that contains a workspace. The workspace contains a warehouse named DW1 that is modelled by using MD5 hash surrogate keys.
DW1 contains a single fact table that has grown from 200 million rows to 500 million rows during the past year.
You have Microsoft Power BI reports that are based on Direct Lake. The reports show year-over-year values.
Users report that the performance of some of the reports has degraded over time and some visuals show errors.
You need to resolve the performance issues. The solution must meet the following requirements:
Provide the best query performance.
Minimize operational costs.
Which should you do?
Show answer and explanation
Correct answer: D. Modify the surrogate keys to use a different data type.
Modifying the surrogate keys to use a different data type addresses the root cause of performance degradation. MD5 hash surrogate keys are large (128-bit), and as the fact table grew from 200 million to 500 million rows, the increased storage and key size impacts query performance. Using a more efficient data type such as bigint reduces memory usage, improves query performance, and minimizes operational costs without additional capacity purchases.
Why the other options are wrong
- A. Changing MD5 to SHA256 would make the surrogate key even larger (256-bit), worsening the performance problem.
- B. Increasing capacity is expensive and doesn't address the underlying cause of performance degradation related to inefficient surrogate key design.
- C. While V-Order can improve performance in some scenarios, it doesn't address the fundamental issue of oversized surrogate keys affecting query efficiency.
- E. Creating views doesn't resolve the performance issues caused by large surrogate keys and fact table growth.
Question 6
You have a Fabric workspace that contains a lakehouse named Lakehouse1. Data is ingested into Lakehouse1 as one flat table. The table contains the following columns.
You plan to load the data into a dimensional model and implement a star schema. From the original flat table, you create two tables named FactSales and DimProduct. You will track changes in DimProduct.
You need to prepare the data.
Which three columns should you include in the DimProduct table? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Show answer and explanation
Correct answer: B, C, F
B. ProductName C. ProductColor F. ProductID The DimProduct table in a star schema should contain all attributes that describe a product dimension. ProductID (F) is the primary key and unique identifier for the dimension table. ProductName (B) and ProductColor (C) are descriptive attributes that provide business context about each product. These three columns form a complete product dimension that can be referenced by a fact table through a foreign key relationship. Date belongs in the fact table as a transaction timestamp, TransactionID is a fact table identifier, and SalesAmount is a transactional measure that belongs in the FactSales table, not the dimension.
Why the other options are wrong
- A. Date is a transaction-level attribute that belongs in the FactSales table, not the product dimension.
- D. TransactionID is a unique transaction identifier that belongs in the FactSales fact table, not the product dimension.
- E. SalesAmount is a measured value from a transaction that belongs in the FactSales fact table as a measure, not in the dimension.
Question 7
You have a Fabric workspace named Workspace1 that contains a notebook named Notebook1.
In Workspace1, you create a new notebook named Notebook2.
You need to ensure that you can attach Notebook2 to the same Apache Spark session as Notebook1.
What should you do?
Show answer and explanation
Correct answer: A. Enable high concurrency for notebooks.
Enabling high concurrency mode for notebooks allows multiple notebooks to attach to the same Apache Spark session, enabling them to share variables and state. This feature is specifically designed for notebook collaboration scenarios where you need multiple notebooks to work within a single Spark context.
Why the other options are wrong
- B. Dynamic allocation adjusts executor scaling based on workload but doesn't enable session attachment between notebooks.
- C. Changing the runtime version affects Spark and Python versions available but doesn't enable multiple notebooks to share a session.
- D. Increasing the number of executors improves parallelism but doesn't provide the ability to attach multiple notebooks to the same session.
Question 8
You have a Fabric workspace named Workspace1 that contains a lakehouse named Lakehouse1. Lakehouse1 contains the following tables:
• Orders
• Customer
• Employee
The Employee table contains Personally Identifiable Information (PII).
A data engineer is building a workflow that requires writing data to the Customer table, however, the user does NOT have the elevated permissions required to view the contents of the Employee table.
You need to ensure that the data engineer can write data to the Customer table without reading data from the Employee table.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Show answer and explanation
Correct answer: D, E, F
D. Assign the data engineer the Contributor role for Workspace1. E. Migrate the Employee table from Lakehouse1 to Lakehouse2. F. Create a new workspace named Workspace2 that contains a new lakehouse named Lakehouse2. Workspace roles apply to every item in the workspace, so the only way to let the engineer write to Customer while blocking the PII is to move the Employee table out of that workspace. Create Workspace2 with Lakehouse2, migrate the Employee table into it, and give the engineer the Contributor role on Workspace1. With no role on Workspace2, the engineer has full write access to the Customer table and no path to the Employee data.
Why the other options are wrong
- A. Sharing Lakehouse1 gives access to the whole lakehouse, including the Employee table with the PII.
- B. Contributor on Workspace2 would let the engineer read and write Lakehouse2, which is where the Employee table now lives.
- C. Viewer on Workspace2 would still allow the engineer to read the Employee data through the SQL analytics endpoint.
- G. Viewer on Workspace1 is read-only and would block the required writes to the Customer table.
Question 9
You have a Fabric warehouse named DW1. DW1 contains a table that stores sales data and is used by multiple sales representatives.
You plan to implement row-level security (RLS).
You need to ensure that the sales representatives can see only their respective data.
Which warehouse object do you require to implement RLS?
Show answer and explanation
Correct answer: D. FUNCTION
A function is required to implement row-level security in a warehouse. RLS uses security predicates defined through functions to dynamically filter rows based on user context. The function evaluates the current user and returns a table of allowed row identifiers, enabling row-level filtering transparently to queries.
Why the other options are wrong
- A. A stored procedure executes custom logic but cannot define the row-level security predicates required for RLS.
- B. A constraint defines data integrity rules but is not used for implementing row-level access control.
- C. A schema organizes objects logically but doesn't provide the row filtering mechanism that RLS requires.
Question 10
You have a Fabric deployment pipeline that uses three workspaces named Dev, Test, and Prod.
You need to deploy an eventhouse as part of the deployment process.
What should you use to add the eventhouse to the deployment process?
Show answer and explanation
Correct answer: B. a deployment pipeline
Eventhouse is a supported item type in Fabric deployment pipelines, so the item is added to the process simply by placing it in the Dev workspace that the pipeline is already assigned to and deploying it to Test and Prod. The native deployment pipeline handles the item and its KQL databases, including deployment rules, without any external tooling.
Why the other options are wrong
- A. GitHub Actions is an external CI/CD service that would require custom API calls and is not how an item is added to an existing Fabric deployment pipeline.
- C. An Azure DevOps pipeline is external orchestration that is unnecessary here because the Fabric deployment pipeline already supports eventhouse deployment.
That was 10 of 139.
The full Microsoft DP-700 pack has all 139 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.