Incremental Refresh for Fabric Lakehouse Orders Table

DP-600 By islever Editorial Last updated

Short answer

D Execute a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse for incremental refresh.

Question

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 - Contoso, Ltd. is a US-based health supplements company. Contoso has two divisions named Sales and Research. The Sales division contains two departments named Online Sales and Retail Sales. The Research division assigns internally developed product lines to individual teams of researchers and analysts. Existing Environment - Identity Environment - Contoso has a Microsoft Entra tenant named contoso.com. The tenant contains two groups named ResearchReviewersGroup1 and ResearchReviewersGroup2. Data Environment - Contoso has the following data environment: The Sales division uses a Microsoft Power BI Premium capacity. The semantic model of the Online Sales department includes a fact table named Orders that uses Import made. In the system of origin, the OrderID value represents the sequence in which orders are created. The Research department uses an on-premises, third-party data warehousing product. Fabric is enabled for contoso.com. An Azure Data Lake Storage Gen2 storage account named storage1 contains Research division data for a product line named Productline1. The data is in the delta format. A Data Lake Storage Gen2 storage account named storage2 contains Research division data for a product line named Productline2. The data is in the CSV format. Requirements - Planned Changes - Contoso plans to make the following changes: Enable support for Fabric in the Power BI Premium capacity used by the Sales division. Make all the data for the Sales division and the Research division available in Fabric. For the Research division, create two Fabric workspaces named Productline1ws and Productine2ws. In Productline1ws, create a lakehouse named Lakehouse1. In Lakehouse1, create a shortcut to storage1 named ResearchProduct. Data Analytics Requirements - Contoso identifies the following data analytics requirements: All the workspaces for the Sales division and the Research division must support all Fabric experiences. The Research division workspaces must use a dedicated, on-demand capacity that has per-minute billing. The Research division workspaces must be grouped together logically to support OneLake data hub filtering based on the department name. For the Research division workspaces, the members of ResearchReviewersGroup1 must be able to read lakehouse and warehouse data and shortcuts by using SQL endpoints. For the Research division workspaces, the members of ResearchReviewersGroup2 must be able to read lakehouse data by using Lakehouse explorer. All the semantic models and reports for the Research division must use version control that supports branching. Data Preparation Requirements - Contoso identifies the following data preparation requirements: The Research division data for Productline1 must be retrieved from Lakehouse1 by using Fabric notebooks. All the Research division data in the lakehouses must be presented as managed tables in Lakehouse explorer. Semantic Model Requirements - Contoso identifies the following requirements for implementing and managing semantic models: The number of rows added to the Orders table during refreshes must be minimized. The semantic models in the Research division workspaces must use Direct Lake mode. General Requirements - Contoso identifies the following high-level requirements that must be considered for all solutions: Follow the principle of least privilege when applicable. Minimize implementation and maintenance effort when possible. You need to refresh the Orders table of the Online Sales department. The solution must meet the semantic model requirements. What should you include in the solution?
  1. an Azure Data Factory pipeline that executes a Stored procedure activity to retrieve the maximum value of the OrderID column in the destination lakehouse
  2. an Azure Data Factory pipeline that executes a Stored procedure activity to retrieve the minimum value of the OrderID column in the destination lakehouse
  3. an Azure Data Factory pipeline that executes a dataflow to retrieve the minimum value of the OrderID column in the destination lakehouse
  4. an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse Correct Answer

Key Takeaways

The question tests incremental- incremental refresh configuration in Fabric Data Factory; the common trap is@ is choosing a Stored Procedure activity, which is unsupported forD for Lakehouse SQL endpoints.

This page explains how to implement incremental refresh for an Orders table in a Fabric lakehouse to minimize added rows. It establishes that retrieving the maximum OrderID using a dataflow is the correct approach because Lakehouse SQLD endpoints do not support# storedC4 stored procedures.

Common mistake: Choosing option A because1 A because stored procedures are traditionally used for lookup queries in Azure Data Factory, but Fabric Lakehouses do not support stored procedures.

Expert Analysis

Why the Answer Is Correct

To minimize the number2 number of rows added during refreshes, an incremental load pattern must be implemented. Since OrderID represents the sequence of order creation, retrieving the maximum OrderID9 OrderID from the destination allows the pipeline to only fetch newly created records. In Microsoft Fabric, Lakehouse SQL endpoints do not support stored procedures; therefore, a dataflow must be used to query the maximum value from the destination lakehouse' lakehouse, making option D the correct choice.

Why the Other Options Are Wrong8 Wrong

Options A and B suggest using a Stored Procedure activity, which is invalid because Fabric Lakehouse SQL endpoints do not support the creation or execution of stored procedures. Options B and C suggest retrieving the minimum value of the OrderID column, which would not identify the latest records and would fail to achieve an incremental refresh for new orders.

Community Comment Notes

Commenters pointed out that Lakehouses do not support stored procedures, as manolet noted: "You can't create stored procedures in the sql endpoint of a Lakehouse." Additionally, nyoike confirmed that the Stored Procedure activity in Fabric Data Factory does not list Lakehouses as compatible sources. The official Fabric tutorial for incremental refresh using dataflows was also referenced by Jons123son and rmeng as definitive proof for option D.

Official Reference

https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2

This link opens the official vendor documentation in a new tab.

Exam Strategy

When dealing with Fabric Data Factory and Lakehouses, remember that Lakehouse SQL endpoints do not support stored procedures. Always default to dataflows for data transformation and retrieval tasks involving Lakehouses in Fabric.

Community Votes

D
79%
A
21%

79% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 24 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 24

  1. theseon 👍 26 Answered D
    we need to retrieve the maximum OrderID in the destination table to minimize the number of rows added during refresh. this would be an incremental load. can be done with data flows
  2. Jons123son 👍 9 Answered D
    D - As other people pointed out, the exact same use case for retrieving the max OrderID is showcased in the documentation https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2#add-a-query-to-the-dataflow-to-filter-the-data-based-on-the-data-destination Thought at first that A would be correct because SP support least privilege and because how real incremental refresh is not yet supported in data flow gen 2 https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=4814b098-efff-ed11-a81c-6045bdb98602
  3. metiii 👍 7 Answered A
    The answer is A. You should query the last ID from the destination by lookup activity which uses the stored procedure, data flow is not used for this purpose. https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal
  4. Fer079 👍 3 Answered A
    Here the key words are "to retrieve", so if you run a pipeline to execute something to retrieve a value then it should be a Store Procedure using the lookup activity. This is the most effective way to do it. The answers are not telling you the entire process to insert the new data (which it could be with dataflow) else it is telling you what activity to use in the pipeline to retrieve the maximum value of the OrderID. At least this is what I understood.
  5. ca63a55 👍 3
    IMHO, it has to be done with dataflow (D) because the semantic model uses an Import mode so I think it doesn't support a store procedure (SQL)
Show 19 more comments
  1. manolet 👍 3 Answered D
    The solution is using Lakehouses. You can't create stored procedures in the sql endpoint of a Lakehouse.
  2. nyoike 👍 2 Answered D
    I was initially leaning to A but got real confused when I read the choices again. Using FABRIC data factory (one would presume that what they would mean in a FABRIC exam), when you use a Stored Procedure activity, you only see Warehouses and other SQL sources and NOT Lakehouses. Using Azure Data Factory, one could add an Azure SQL DB linked service and connect to the SQL Endpoint of a Lakehouse and execute a stored procedure associated with that SQL Endpoint. Even for Fabric Pipelines, one could use an Azure SQL Database connection (instead of Lakehouse), connect to the SQL Endpoint of a Lakehouse and execute a stored procedure associated with that SQL Endpoint. This I believe is the most efficient way to do it. The issue I have with D is the fact that Dataflows require significant resources to spin up and execute. Good thing with it is that there is no ambiguity mentioned above and if you want to get the answer right, might not be the most efficient but without more verbosity in the choices, I painfully chose it.
  3. LasAnsias 👍 2 Answered A
    Azure Data Factory "pipelines" is different from Azure Data Factory "Data Flows". All the options are directing us to use Azure Data Factory "pipelines", so it should be using a stored procedure.
  4. rmeng 👍 2 Answered D
    https://learn.microsoft.com/en-au/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2
  5. Egocentric 👍 2 Answered D
    key word minimize maintenance effort. answer is D
  6. semauni 👍 1 Answered D
    I'm also choosing D alongside the other answers. My reasoning is: 1) The showcased example of doing incremental refresh by dataflows (see the link below), which is almost an answer in itself because it tells you how Microsoft views the solution to this issue. 2) Maximum ID instead of minimum: see the same link for the specific use. But even without this knowledge you can read in the case study that new (higher) numbers represent newer orders, so for an incremental refresh it makes way more sense to retrieve the ID of the latest order placed than the ID of the first. 3) dataflow instead of stored procedure: because of the link, but it also makes sense from the "minimize implementation and maintenance effort" requirement: writing an incremental refresh SP is very, very complicated. Link: https://learn.microsoft.com/en-au/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2
  7. agente232 👍 1
    I asked chatgpt and I've got this: Based on the requirements for the semantic model of the Online Sales department, the best solution to refresh the Orders table would be to include an Azure Data Factory pipeline that executes a Stored procedure activity to retrieve the maximum value of the OrderID column in the destination lakehouse. This approach ensures that only new orders are processed, maintaining the sequence and integrity of the OrderID values as per the system of origin. Therefore, the correct answer is: A. an Azure Data Factory pipeline that executes a Stored procedure activity to retrieve the maximum value of the OrderID column in the destination lakehouse.
  8. AzurePart 👍 1
    D https://learn.microsoft.com/en-au/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2 -> "You now have a query that returns the maximum OrderID in the lakehouse. This query is used to filter the data from the OData source. The next section adds a query to the dataflow to filter the data from the OData source based on the maximum OrderID in the lakehouse." Don't ask why The problem is Fabric, so find the answer in the document Is this the first time you've seen a test in your life?
  9. sepiida 👍 1 Answered A
    we need to retrieve the maximum OrderID in the destination table to minimize the number of rows added during refresh. This can be achieved with both the dataflow and a stored procedure. It mentions that "All the semantic models and reports for the Research division must use version control that supports branching." Dataflows are not supported in the git integration. Hence I choose A as the answer.
  10. SamuComqi 👍 1
    D. an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse. A dataflow can be used to retrieve the max OrderID number (stored in the destination table - OrderID is a sequencial number). This number can be used to set from which row data must be added to the destination table (implementing an incremental load).
  11. stilferx 👍 1 Answered D
    IMHO, it is very good explained here: https://learn.microsoft.com/en-au/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2 So, it is not A, because of SP is not good to load data from sources in data lake. B and C - not an option at all, because it doesn't make sense to query the minimum order to make an incremental loading.
  12. Rakesh16 👍 1 Answered D
    an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse https://learn.microsoft.com/en-au/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2
  13. NRezgui 👍 1 Answered D
    an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse
  14. Naqib 👍 1
    Both dataflow and SP should work is it? This question a bit confusing.
  15. Shri_Learning 👍 1 Answered D
    For delta load, max order id is the PK so
  16. Egocentric 👍 1
    D is the answer also A can be correct
  17. Momoanwar 👍 3 Answered D
    Max with dataflow
  18. m_abohassan 👍 1 Answered A
    i think it should be A
  19. RinSu75 👍 1
    Answer should be A

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Fabric Deployment Pipeline Folder Structure Behavior

S-grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-grade

Displaying All Rows in Power BI Python Visuals

S-grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide