[Microsoft] DP-600 - Fabric Analytics Engineer Associate Exam Dumps & Study Guide

Free [Microsoft] DP-600 - Fabric Analytics Engineer Associate Practice Questions Preview

• Question 1

  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 ensure that Contoso can use version control to meet the data analytics requirements and the general requirements.
  What should you do?

  • A. Store at the semantic models and reports in Data Lake Gen2 storage.
  • B. Modify the settings of the Research workspaces to use a GitHub repository.
  • C. Modify the settings of the Research division workspaces to use an Azure Repos repository.
  • D. Store all the semantic models and reports in Microsoft OneDrive.

  Correct Answer: C

  Explanation:

  The AI recommends to choose Option C: Modify the settings of the Research division workspaces to use an Azure Repos repository.
  
  Reasoning: The core requirement is to implement version control for the Power BI semantic models and reports within the Research division's workspaces. At the time of the question, and according to the most reliable information, Azure Repos was the only fully supported Git repository directly integrated with Microsoft Fabric and Power BI for version control. Although GitHub integration might be available in preview or recently GA, Azure Repos offers a more stable and officially supported solution for this specific scenario.
  
  Here is the reasoning for each choice:

  • Option A: Store the semantic models and reports in Data Lake Gen2 storage: While storing data in Data Lake Gen2 is beneficial for data storage and processing, it does not provide version control capabilities for the Power BI artifacts themselves (semantic models and reports). It addresses data storage, not version control.
  • Option B: Modify the settings of the Research workspaces to use a GitHub repository: While GitHub is a popular version control platform, it wasn't fully supported option for direct integration with Power BI workspaces for version control at the time this question was created.
  • Option C: Modify the settings of the Research division workspaces to use an Azure Repos repository: This is the most appropriate solution. Azure Repos, as part of Azure DevOps, provides Git-based version control. Integrating the Research division's workspaces with Azure Repos enables proper versioning, collaboration, and change tracking for their Power BI assets.
  • Option D: Store all the semantic models and reports in Microsoft OneDrive: OneDrive is a file storage and synchronization service, not a version control system. It lacks the features necessary for tracking changes, managing versions, and collaborating effectively on Power BI projects.

  Therefore, the most suitable answer is C because it directly addresses the need for version control using a supported Git repository (Azure Repos) at the time of this question.

  Updates may have occurred since the question was originally posed, however, as of the time this question was relevant, option C was correct.

  
  

  Citations:

  • Azure Repos Documentation, https://learn.microsoft.com/en-us/azure/devops/repos/?view=azure-devops

• Question 2

  HOTSPOT -
  
  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 recommend a solution to group the Research division workspaces.
  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.
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer.
  The recommended inclusions for this scenario are the Domain Admin portal and Fabric Admin portal.
  This recommendation is based on the understanding that domains within Microsoft Fabric are used to logically group services and items within an organization, and the Fabric Admin portal provides the necessary interface for creating and managing these domain groupings. The Domain Admin portal is likely a subset or specific view within the Fabric Admin portal, tailored for domain-related tasks.
  Other options, such as OneLake Data Hub or Tenant and MS Entra ID, are not the primary tools for creating and managing Fabric domains:

  • OneLake Data Hub: While the OneLake Data Hub allows users to view and filter data by domain, it does not provide functionality for creating or managing domains themselves.
  • Tenant and MS Entra ID: The Microsoft Entra Admin Portal is responsible for managing user identities and permissions at the tenant level. While user access is crucial for working with Fabric domains, Entra ID is not directly involved in creating or configuring Fabric domains.

  Citations:

  • Microsoft Fabric documentation on Domains: (Specific documentation link not available, as the Fabric documentation is frequently updated. Search for "Microsoft Fabric domains" to find the most current documentation.)

• Question 3

  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?

  • 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
  • B. an Azure Data Factory pipeline that executes a Stored procedure activity to retrieve the minimum value of the OrderID column in the destination lakehouse
  • C. an Azure Data Factory pipeline that executes a dataflow to retrieve the minimum value of the OrderID column in the destination lakehouse
  • D. an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse

  Correct Answer: D

  Explanation:

  The AI suggests that the correct answer is D. An Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse should be included in the solution.
  
  Reasoning:

  • Dataflows are preferred for incremental loading due to their ease of implementation and maintenance. Using a dataflow provides a visual and code-free environment (or low-code) to implement the logic for retrieving the maximum OrderID, which simplifies the overall process and reduces maintenance overhead. This aligns with minimizing maintenance efforts.
  • The question requires determining the maximum OrderID for incremental refresh. Dataflows can efficiently handle this requirement. By retrieving the maximum OrderID from the destination, the pipeline can load only new or updated data since the last refresh.

  
  Reasons for excluding other options:

  • Option A and B involve using stored procedures. While stored procedures can achieve the same result, they require more manual coding and maintenance compared to dataflows. Stored procedures might be harder to debug and maintain in the long run, especially as the complexity of the logic increases. This contradicts the objective of minimizing maintenance efforts.
  • Option C retrieves the minimum OrderID. This is incorrect because incremental loading requires knowing the maximum OrderID already present in the destination to avoid loading duplicate data.

• Question 4

  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.
  Which syntax should you use in a notebook to access the Research division data for Productline1?

  • A. spark.read.format(“delta”).load(“Tables/productline1/ResearchProduct”)
  • B. spark.sql(“SELECT * FROM Lakehouse1.ResearchProduct ”)
  • C. external_table(‘Tables/ResearchProduct)
  • D. external_table(ResearchProduct)

  Correct Answer: B

  Explanation:

  The AI agrees with the suggested answer B.
  Reasoning: The most appropriate syntax to access the Research division data for Productline1 in a notebook is using Spark SQL to query the `Lakehouse1.ResearchProduct` table directly. According to the discussion and corroborated by general practices in data warehousing with Spark and Delta Lake, this approach assumes that `ResearchProduct` is registered as a table or view within the `Lakehouse1` database/catalog. This allows for a straightforward SQL query to retrieve the desired data. The discussion also pointed out that the shortcut named `ResearchProduct` is created directly under the Tables folder in Lakehouse1, therefore there is no productline1 subfolder needed, further confirming the use of `Lakehouse1.ResearchProduct`.
  Why other options are not suitable:

  • Option A: `spark.read.format(“delta”).load(“Tables/productline1/ResearchProduct”)` - This option is used for loading data from a Delta Lake path into a Spark DataFrame, not for directly querying a registered table or view. While it can access the data, it's a more verbose and less direct approach compared to using Spark SQL. Additionally, it assumes that the data is stored at the path "Tables/productline1/ResearchProduct", which may not be the case if a table has been created.
  • Option C and D: `external_table(‘Tables/ResearchProduct)` and `external_table(ResearchProduct)` - The function `external_table` is not standard Spark syntax and is not a recognized way to access data in a Spark environment. These options are invalid and would result in an error.

  • Citations:
  • Delta Lake Documentation, https://delta.io/

• Question 5

  HOTSPOT -
  
  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 -
  Litware, Inc. is a manufacturing company that has offices throughout North America. The analytics team at Litware contains data engineers, analytics engineers, data analysts, and data scientists.
  
  Existing Environment -
  
  Fabric Environment -
  Litware has been using a Microsoft Power BI tenant for three years. Litware has NOT enabled any Fabric capacities and features.
  
  Available Data -
  Litware has data that must be analyzed as shown in the following table.
  
  The Product data contains a single table and the following columns.
  
  The customer satisfaction data contains the following tables:
  
  Survey -
  
  Question -
  
  Response -
  For each survey submitted, the following occurs:
  One row is added to the Survey table.
  One row is added to the Response table for each question in the survey.
  The Question table contains the text of each survey question. The third question in each survey response is an overall satisfaction score. Customers can submit a survey after each purchase.
  
  User Problems -
  The analytics team has large volumes of data, some of which is semi-structured. The team wants to use Fabric to create a new data store.
  Product data is often classified into three pricing groups: high, medium, and low. This logic is implemented in several databases and semantic models, but the logic does NOT always match across implementations.
  
  Requirements -
  
  Planned Changes -
  Litware plans to enable Fabric features in the existing tenant. The analytics team will create a new data store as a proof of concept (PoC). The remaining Liware users will only get access to the Fabric features once the PoC is complete. The PoC will be completed by using a Fabric trial capacity
  The following three workspaces will be created:
  AnalyticsPOC: Will contain the data store, semantic models, reports pipelines, dataflow, and notebooks used to populate the data store
  DataEngPOC: Will contain all the pipelines, dataflows, and notebooks used to populate OneLake
  DataSciPOC: Will contain all the notebooks and reports created by the data scientists
  The following will be created in the AnalyticsPOC workspace:
  A data store (type to be decided)
  
  A custom semantic model -
  
  A default semantic model -
  
  Interactive reports -
  The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
  All the semantic models and reports in the Analytics POC workspace will use the data store as the sole data source.
  
  Technical Requirements -
  The data store must support the following:
  Read access by using T-SQL or Python
  Semi-structured and unstructured data
  Row-level security (RLS) for users executing T-SQL queries
  Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
  Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
  The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
  The dimensional model must contain a date dimension. There is no existing data source for the date dimension. The Litware fiscal year matches the calendar year. The date dimension must always contain dates from 2010 through the end of the current year.
  The product pricing group logic must be maintained by the analytics engineers in a single location. The pricing group data must be made available in the data store for T-SOL. queries and in the default semantic model. The following logic must be used:
  List prices that are less than or equal to 50 are in the low pricing group.
  List prices that are greater than 50 and less than or equal to 1,000 are in the medium pricing group.
  List prices that are greater than 1,000 are in the high pricing group.
  
  Security Requirements -
  Only Fabric administrators and the analytics team must be able to see the Fabric items created as part of the PoC.
  Litware identifies the following security requirements for the Fabric items in the AnalyticsPOC workspace:
  Fabric administrators will be the workspace administrators.
  The data engineers must be able to read from and write to the data store. No access must be granted to datasets or reports.
  The analytics engineers must be able to read from, write to, and create schemas in the data store. They also must be able to create and share semantic models with the data analysts and view and modify all reports in the workspace.
  The data scientists must be able to read from the data store, but not write to it. They will access the data by using a Spark notebook
  The data analysts must have read access to only the dimensional model objects in the data store. They also must have access to create Power BI reports by using the semantic models created by the analytics engineers.
  The date dimension must be available to all users of the data store.
  The principle of least privilege must be followed.
  Both the default and custom semantic models must include only tables or views from the dimensional model in the data store. Litware already has the following Microsoft Entra security groups:
  FabricAdmins: Fabric administrators
  AnalyticsTeam: All the members of the analytics team
  DataAnalysts: The data analysts on the analytics team
  DataScientists: The data scientists on the analytics team
  DataEngineers: The data engineers on the analytics team
  AnalyticsEngineers: The analytics engineers on the analytics team
  
  Report Requirements -
  The data analysts must create a customer satisfaction report that meets the following requirements:
  Enables a user to select a product to filter customer survey responses to only those who have purchased that product.
  Displays the average overall satisfaction score of all the surveys submitted during the last 12 months up to a selected dat.
  Shows data as soon as the data is updated in the data store.
  Ensures that the report and the semantic model only contain data from the current and previous year.
  Ensures that the report respects any table-level security specified in the source data store.
  Minimizes the execution time of report queries.
  You need to assign permissions for the data store in the AnalyticsPOC workspace. The solution must meet the security requirements.
  Which additional permissions should you assign when you share the data store? To answer, select the appropriate options in the answer area.
  NOTE: Each correct selection is worth one point.
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer.
  The correct assignment of permissions is: Data Engineers: Read all Apache Spark, Data Analysts: Build Reports on the default dataset, Data Scientists: Read all Apache Spark.
  Reasoning:

  • Data Engineers: Require 'Read all Apache Spark' permission. Data engineers are responsible for reading and writing data to the data store. Apache Spark is used to perform these read and write actions.
  • Data Analysts: Require 'Build Reports on the default dataset' permission. Data analysts create Power BI reports using semantic models (default dataset).
  • Data Scientists: Require 'Read all Apache Spark' permission. The question explicitly states that data scientists use Spark notebooks. Thus, they need read access to all Apache Spark data.

  Reasons for not choosing other options:

  • 'Read SQL Endpoints' for Data Engineers is not sufficient because SQL Endpoints are generally read-only. Data Engineers need to write data.
  • Assigning 'Read SQL Endpoints' to Data Scientists might seem plausible because notebooks can use SQL endpoints. However, the problem specifies they are using Spark notebooks, and therefore need access to all Apache Spark data, not just SQL endpoints.

  Citations:

  • DP-600 Exam Information, No specific URL, general knowledge of the exam objectives
  • Apache Spark Overview, https://spark.apache.org/docs/latest/
  • Power BI Reporting, https://learn.microsoft.com/en-us/power-bi/

• Question 6

  HOTSPOT -
  
  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 -
  Litware, Inc. is a manufacturing company that has offices throughout North America. The analytics team at Litware contains data engineers, analytics engineers, data analysts, and data scientists.
  
  Existing Environment -
  
  Fabric Environment -
  Litware has been using a Microsoft Power BI tenant for three years. Litware has NOT enabled any Fabric capacities and features.
  
  Available Data -
  Litware has data that must be analyzed as shown in the following table.
  
  The Product data contains a single table and the following columns.
  
  The customer satisfaction data contains the following tables:
  
  Survey -
  
  Question -
  
  Response -
  For each survey submitted, the following occurs:
  One row is added to the Survey table.
  One row is added to the Response table for each question in the survey.
  The Question table contains the text of each survey question. The third question in each survey response is an overall satisfaction score. Customers can submit a survey after each purchase.
  
  User Problems -
  The analytics team has large volumes of data, some of which is semi-structured. The team wants to use Fabric to create a new data store.
  Product data is often classified into three pricing groups: high, medium, and low. This logic is implemented in several databases and semantic models, but the logic does NOT always match across implementations.
  
  Requirements -
  
  Planned Changes -
  Litware plans to enable Fabric features in the existing tenant. The analytics team will create a new data store as a proof of concept (PoC). The remaining Liware users will only get access to the Fabric features once the PoC is complete. The PoC will be completed by using a Fabric trial capacity
  The following three workspaces will be created:
  AnalyticsPOC: Will contain the data store, semantic models, reports pipelines, dataflow, and notebooks used to populate the data store
  DataEngPOC: Will contain all the pipelines, dataflows, and notebooks used to populate OneLake
  DataSciPOC: Will contain all the notebooks and reports created by the data scientists
  The following will be created in the AnalyticsPOC workspace:
  A data store (type to be decided)
  
  A custom semantic model -
  
  A default semantic model -
  
  Interactive reports -
  The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
  All the semantic models and reports in the Analytics POC workspace will use the data store as the sole data source.
  
  Technical Requirements -
  The data store must support the following:
  Read access by using T-SQL or Python
  Semi-structured and unstructured data
  Row-level security (RLS) for users executing T-SQL queries
  Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
  Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
  The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
  The dimensional model must contain a date dimension. There is no existing data source for the date dimension. The Litware fiscal year matches the calendar year. The date dimension must always contain dates from 2010 through the end of the current year.
  The product pricing group logic must be maintained by the analytics engineers in a single location. The pricing group data must be made available in the data store for T-SOL. queries and in the default semantic model. The following logic must be used:
  List prices that are less than or equal to 50 are in the low pricing group.
  List prices that are greater than 50 and less than or equal to 1,000 are in the medium pricing group.
  List prices that are greater than 1,000 are in the high pricing group.
  
  Security Requirements -
  Only Fabric administrators and the analytics team must be able to see the Fabric items created as part of the PoC.
  Litware identifies the following security requirements for the Fabric items in the AnalyticsPOC workspace:
  Fabric administrators will be the workspace administrators.
  The data engineers must be able to read from and write to the data store. No access must be granted to datasets or reports.
  The analytics engineers must be able to read from, write to, and create schemas in the data store. They also must be able to create and share semantic models with the data analysts and view and modify all reports in the workspace.
  The data scientists must be able to read from the data store, but not write to it. They will access the data by using a Spark notebook
  The data analysts must have read access to only the dimensional model objects in the data store. They also must have access to create Power BI reports by using the semantic models created by the analytics engineers.
  The date dimension must be available to all users of the data store.
  The principle of least privilege must be followed.
  Both the default and custom semantic models must include only tables or views from the dimensional model in the data store. Litware already has the following Microsoft Entra security groups:
  FabricAdmins: Fabric administrators
  AnalyticsTeam: All the members of the analytics team
  DataAnalysts: The data analysts on the analytics team
  DataScientists: The data scientists on the analytics team
  DataEngineers: The data engineers on the analytics team
  AnalyticsEngineers: The analytics engineers on the analytics team
  
  Report Requirements -
  The data analysts must create a customer satisfaction report that meets the following requirements:
  Enables a user to select a product to filter customer survey responses to only those who have purchased that product.
  Displays the average overall satisfaction score of all the surveys submitted during the last 12 months up to a selected dat.
  Shows data as soon as the data is updated in the data store.
  Ensures that the report and the semantic model only contain data from the current and previous year.
  Ensures that the report respects any table-level security specified in the source data store.
  Minimizes the execution time of report queries.
  You need to create a DAX measure to calculate the average overall satisfaction score.
  How should you complete the DAX code? To answer, select the appropriate options in the answer area.
  NOTE: Each correct selection is worth one point.
  

  Correct Answer: See interactive view.

  Explanation:

  The AI agrees with the suggested answer.
  Based on the question requirements and the consensus from the discussion, the correct DAX measure should calculate the average overall satisfaction score using the 'Response Value' from the 'Survey' table and filter it for the last 12 months.
  
  The DAX code should be: `AVERAGE('Survey'[Response Value])` and `Period`.
  
  Reasoning:
  

  • 'Survey'[Response Value]: This is the column that contains the customer satisfaction scores, and the AVERAGE function will calculate the average of these scores. This is the only relevant numeric field that represents customer satisfaction.
  • Period: This option suggests that a time intelligence function is used to filter the data for the last 12 months. This aligns with the requirement to calculate the average satisfaction score for a specific period.

  
  Reasons for not choosing other options:

  • Options including 'Customer Key' are incorrect. 'Customer Key' is an identifier and not a value that can be averaged to represent customer satisfaction.
  • Other options might have syntax errors or not align with the requirement to calculate the average satisfaction score over a period.

  
  The question requires calculating the average overall satisfaction score for the last 12 months, making the suggested answer the most appropriate choice.

  
  Citations:

  • DAX AVERAGE function - Microsoft Learn, https://learn.microsoft.com/en-us/dax/average-function-dax

• Question 7

  HOTSPOT -
  
  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 -
  Litware, Inc. is a manufacturing company that has offices throughout North America. The analytics team at Litware contains data engineers, analytics engineers, data analysts, and data scientists.
  
  Existing Environment -
  
  Fabric Environment -
  Litware has been using a Microsoft Power BI tenant for three years. Litware has NOT enabled any Fabric capacities and features.
  
  Available Data -
  Litware has data that must be analyzed as shown in the following table.
  
  The Product data contains a single table and the following columns.
  
  The customer satisfaction data contains the following tables:
  
  Survey -
  
  Question -
  
  Response -
  For each survey submitted, the following occurs:
  One row is added to the Survey table.
  One row is added to the Response table for each question in the survey.
  The Question table contains the text of each survey question. The third question in each survey response is an overall satisfaction score. Customers can submit a survey after each purchase.
  
  User Problems -
  The analytics team has large volumes of data, some of which is semi-structured. The team wants to use Fabric to create a new data store.
  Product data is often classified into three pricing groups: high, medium, and low. This logic is implemented in several databases and semantic models, but the logic does NOT always match across implementations.
  
  Requirements -
  
  Planned Changes -
  Litware plans to enable Fabric features in the existing tenant. The analytics team will create a new data store as a proof of concept (PoC). The remaining Liware users will only get access to the Fabric features once the PoC is complete. The PoC will be completed by using a Fabric trial capacity
  The following three workspaces will be created:
  AnalyticsPOC: Will contain the data store, semantic models, reports pipelines, dataflow, and notebooks used to populate the data store
  DataEngPOC: Will contain all the pipelines, dataflows, and notebooks used to populate OneLake
  DataSciPOC: Will contain all the notebooks and reports created by the data scientists
  The following will be created in the AnalyticsPOC workspace:
  A data store (type to be decided)
  
  A custom semantic model -
  
  A default semantic model -
  
  Interactive reports -
  The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
  All the semantic models and reports in the Analytics POC workspace will use the data store as the sole data source.
  
  Technical Requirements -
  The data store must support the following:
  Read access by using T-SQL or Python
  Semi-structured and unstructured data
  Row-level security (RLS) for users executing T-SQL queries
  Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
  Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
  The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
  The dimensional model must contain a date dimension. There is no existing data source for the date dimension. The Litware fiscal year matches the calendar year. The date dimension must always contain dates from 2010 through the end of the current year.
  The product pricing group logic must be maintained by the analytics engineers in a single location. The pricing group data must be made available in the data store for T-SOL. queries and in the default semantic model. The following logic must be used:
  List prices that are less than or equal to 50 are in the low pricing group.
  List prices that are greater than 50 and less than or equal to 1,000 are in the medium pricing group.
  List prices that are greater than 1,000 are in the high pricing group.
  
  Security Requirements -
  Only Fabric administrators and the analytics team must be able to see the Fabric items created as part of the PoC.
  Litware identifies the following security requirements for the Fabric items in the AnalyticsPOC workspace:
  Fabric administrators will be the workspace administrators.
  The data engineers must be able to read from and write to the data store. No access must be granted to datasets or reports.
  The analytics engineers must be able to read from, write to, and create schemas in the data store. They also must be able to create and share semantic models with the data analysts and view and modify all reports in the workspace.
  The data scientists must be able to read from the data store, but not write to it. They will access the data by using a Spark notebook
  The data analysts must have read access to only the dimensional model objects in the data store. They also must have access to create Power BI reports by using the semantic models created by the analytics engineers.
  The date dimension must be available to all users of the data store.
  The principle of least privilege must be followed.
  Both the default and custom semantic models must include only tables or views from the dimensional model in the data store. Litware already has the following Microsoft Entra security groups:
  FabricAdmins: Fabric administrators
  AnalyticsTeam: All the members of the analytics team
  DataAnalysts: The data analysts on the analytics team
  DataScientists: The data scientists on the analytics team
  DataEngineers: The data engineers on the analytics team
  AnalyticsEngineers: The analytics engineers on the analytics team
  
  Report Requirements -
  The data analysts must create a customer satisfaction report that meets the following requirements:
  Enables a user to select a product to filter customer survey responses to only those who have purchased that product.
  Displays the average overall satisfaction score of all the surveys submitted during the last 12 months up to a selected dat.
  Shows data as soon as the data is updated in the data store.
  Ensures that the report and the semantic model only contain data from the current and previous year.
  Ensures that the report respects any table-level security specified in the source data store.
  Minimizes the execution time of report queries.
  You need to resolve the issue with the pricing group classification.
  How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
  NOTE: Each correct selection is worth one point.
  

  Correct Answer: See interactive view.

  Explanation:

  Based on the question and the discussion, the AI agrees with the suggested answer.
  The suggested answer proposes creating a VIEW with a CASE statement to classify products into pricing groups based on their `ListPrice`. The `BETWEEN` operator in the `WHEN` clause correctly includes both 50 and 1000 in the specified range, aligning perfectly with the problem's requirements.
  
  Reasoning:
  

  • The use of a VIEW is crucial. A view provides a dynamic, up-to-date result set. As the underlying data in the `Products` table changes, the view automatically reflects these changes without requiring manual updates. This aligns with the requirement for dynamic data updates mentioned in the discussion.
  • The CASE statement allows for classifying the products based on different price ranges. This is a flexible and efficient way to categorize the products as 'low', 'medium', or 'high' based on their `ListPrice`.
  • The `BETWEEN` operator in the `WHEN` clause simplifies the range checking and includes both the lower and upper bounds. `ListPrice BETWEEN 50 AND 1000` correctly captures the 'medium' price range, including products with a `ListPrice` of 50 and 1000.

  Why other options are not ideal:
  While there might be alternative ways to express the same logic (e.g., using `ListPrice >= 50 AND ListPrice <= 1000` instead of `BETWEEN`), the suggested answer is concise and directly addresses the prompt's requirements. The key aspect is the combination of a `VIEW`, a `CASE` statement, and correct range handling using `BETWEEN` to provide a dynamic and accurate pricing group classification. Other options might omit the VIEW, use incorrect range checking or not using `CASE` statement, which would make them incorrect.
  

• Question 8

  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 -
  Litware, Inc. is a manufacturing company that has offices throughout North America. The analytics team at Litware contains data engineers, analytics engineers, data analysts, and data scientists.
  
  Existing Environment -
  
  Fabric Environment -
  Litware has been using a Microsoft Power BI tenant for three years. Litware has NOT enabled any Fabric capacities and features.
  
  Available Data -
  Litware has data that must be analyzed as shown in the following table.
  
  The Product data contains a single table and the following columns.
  
  The customer satisfaction data contains the following tables:
  
  Survey -
  
  Question -
  
  Response -
  For each survey submitted, the following occurs:
  One row is added to the Survey table.
  One row is added to the Response table for each question in the survey.
  The Question table contains the text of each survey question. The third question in each survey response is an overall satisfaction score. Customers can submit a survey after each purchase.
  
  User Problems -
  The analytics team has large volumes of data, some of which is semi-structured. The team wants to use Fabric to create a new data store.
  Product data is often classified into three pricing groups: high, medium, and low. This logic is implemented in several databases and semantic models, but the logic does NOT always match across implementations.
  
  Requirements -
  
  Planned Changes -
  Litware plans to enable Fabric features in the existing tenant. The analytics team will create a new data store as a proof of concept (PoC). The remaining Liware users will only get access to the Fabric features once the PoC is complete. The PoC will be completed by using a Fabric trial capacity
  The following three workspaces will be created:
  AnalyticsPOC: Will contain the data store, semantic models, reports pipelines, dataflow, and notebooks used to populate the data store
  DataEngPOC: Will contain all the pipelines, dataflows, and notebooks used to populate OneLake
  DataSciPOC: Will contain all the notebooks and reports created by the data scientists
  The following will be created in the AnalyticsPOC workspace:
  A data store (type to be decided)
  
  A custom semantic model -
  
  A default semantic model -
  
  Interactive reports -
  The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
  All the semantic models and reports in the Analytics POC workspace will use the data store as the sole data source.
  
  Technical Requirements -
  The data store must support the following:
  Read access by using T-SQL or Python
  Semi-structured and unstructured data
  Row-level security (RLS) for users executing T-SQL queries
  Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
  Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
  The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
  The dimensional model must contain a date dimension. There is no existing data source for the date dimension. The Litware fiscal year matches the calendar year. The date dimension must always contain dates from 2010 through the end of the current year.
  The product pricing group logic must be maintained by the analytics engineers in a single location. The pricing group data must be made available in the data store for T-SOL. queries and in the default semantic model. The following logic must be used:
  List prices that are less than or equal to 50 are in the low pricing group.
  List prices that are greater than 50 and less than or equal to 1,000 are in the medium pricing group.
  List prices that are greater than 1,000 are in the high pricing group.
  
  Security Requirements -
  Only Fabric administrators and the analytics team must be able to see the Fabric items created as part of the PoC.
  Litware identifies the following security requirements for the Fabric items in the AnalyticsPOC workspace:
  Fabric administrators will be the workspace administrators.
  The data engineers must be able to read from and write to the data store. No access must be granted to datasets or reports.
  The analytics engineers must be able to read from, write to, and create schemas in the data store. They also must be able to create and share semantic models with the data analysts and view and modify all reports in the workspace.
  The data scientists must be able to read from the data store, but not write to it. They will access the data by using a Spark notebook
  The data analysts must have read access to only the dimensional model objects in the data store. They also must have access to create Power BI reports by using the semantic models created by the analytics engineers.
  The date dimension must be available to all users of the data store.
  The principle of least privilege must be followed.
  Both the default and custom semantic models must include only tables or views from the dimensional model in the data store. Litware already has the following Microsoft Entra security groups:
  FabricAdmins: Fabric administrators
  AnalyticsTeam: All the members of the analytics team
  DataAnalysts: The data analysts on the analytics team
  DataScientists: The data scientists on the analytics team
  DataEngineers: The data engineers on the analytics team
  AnalyticsEngineers: The analytics engineers on the analytics team
  
  Report Requirements -
  The data analysts must create a customer satisfaction report that meets the following requirements:
  Enables a user to select a product to filter customer survey responses to only those who have purchased that product.
  Displays the average overall satisfaction score of all the surveys submitted during the last 12 months up to a selected dat.
  Shows data as soon as the data is updated in the data store.
  Ensures that the report and the semantic model only contain data from the current and previous year.
  Ensures that the report respects any table-level security specified in the source data store.
  Minimizes the execution time of report queries.
  What should you recommend using to ingest the customer data into the data store in the AnalyticsPOC workspace?

  • A. a stored procedure
  • B. a pipeline that contains a KQL activity
  • C. a Spark notebook
  • D. a dataflow

  Correct Answer: D

  Explanation:

  The AI agrees with the suggested answer of D. a dataflow.
  
  Reasoning:
  The technical requirements state that data engineers should use low-code tools for data ingestion whenever possible. Dataflows in Microsoft Fabric are a low-code/no-code solution specifically designed for data ingestion, transformation, and loading (ETL) processes. They provide a visual interface for building data pipelines, making them suitable for the scenario described. Dataflows also support connecting to a variety of data sources and performing data cleansing and transformations. Considering data engineers have discretion over the data cleansing and transformation tools, Dataflow fits the "low-code" ask.
  
  Reasons for not choosing other answers:

  • A. a stored procedure: While stored procedures can be used for data ingestion, they require more coding and are not considered a low-code solution. This contradicts the requirement to use low-code tools whenever possible.
  • B. a pipeline that contains a KQL activity: While pipelines are suitable for orchestrating data movement, using KQL (Kusto Query Language) activities within a pipeline involves more coding and is not as low-code as dataflows. Furthermore, KQL is optimized for analyzing large volumes of streaming data, which is not specified in the problem.
  • C. a Spark notebook: Spark notebooks are powerful tools for data processing, but they require coding in languages like Python or Scala, making them unsuitable for the low-code requirement. Although the data scientists will use Spark notebooks, this option is not the best tool for data *ingestion* by the data engineers, given the low-code priority.

  
  Citations:

  • Microsoft Fabric Dataflows Gen2, https://learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2/overview

• Question 9

  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 -
  Litware, Inc. is a manufacturing company that has offices throughout North America. The analytics team at Litware contains data engineers, analytics engineers, data analysts, and data scientists.
  
  Existing Environment -
  
  Fabric Environment -
  Litware has been using a Microsoft Power BI tenant for three years. Litware has NOT enabled any Fabric capacities and features.
  
  Available Data -
  Litware has data that must be analyzed as shown in the following table.
  
  The Product data contains a single table and the following columns.
  
  The customer satisfaction data contains the following tables:
  
  Survey -
  
  Question -
  
  Response -
  For each survey submitted, the following occurs:
  One row is added to the Survey table.
  One row is added to the Response table for each question in the survey.
  The Question table contains the text of each survey question. The third question in each survey response is an overall satisfaction score. Customers can submit a survey after each purchase.
  
  User Problems -
  The analytics team has large volumes of data, some of which is semi-structured. The team wants to use Fabric to create a new data store.
  Product data is often classified into three pricing groups: high, medium, and low. This logic is implemented in several databases and semantic models, but the logic does NOT always match across implementations.
  
  Requirements -
  
  Planned Changes -
  Litware plans to enable Fabric features in the existing tenant. The analytics team will create a new data store as a proof of concept (PoC). The remaining Liware users will only get access to the Fabric features once the PoC is complete. The PoC will be completed by using a Fabric trial capacity
  The following three workspaces will be created:
  AnalyticsPOC: Will contain the data store, semantic models, reports pipelines, dataflow, and notebooks used to populate the data store
  DataEngPOC: Will contain all the pipelines, dataflows, and notebooks used to populate OneLake
  DataSciPOC: Will contain all the notebooks and reports created by the data scientists
  The following will be created in the AnalyticsPOC workspace:
  A data store (type to be decided)
  
  A custom semantic model -
  
  A default semantic model -
  
  Interactive reports -
  The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
  All the semantic models and reports in the Analytics POC workspace will use the data store as the sole data source.
  
  Technical Requirements -
  The data store must support the following:
  Read access by using T-SQL or Python
  Semi-structured and unstructured data
  Row-level security (RLS) for users executing T-SQL queries
  Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
  Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
  The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
  The dimensional model must contain a date dimension. There is no existing data source for the date dimension. The Litware fiscal year matches the calendar year. The date dimension must always contain dates from 2010 through the end of the current year.
  The product pricing group logic must be maintained by the analytics engineers in a single location. The pricing group data must be made available in the data store for T-SOL. queries and in the default semantic model. The following logic must be used:
  List prices that are less than or equal to 50 are in the low pricing group.
  List prices that are greater than 50 and less than or equal to 1,000 are in the medium pricing group.
  List prices that are greater than 1,000 are in the high pricing group.
  
  Security Requirements -
  Only Fabric administrators and the analytics team must be able to see the Fabric items created as part of the PoC.
  Litware identifies the following security requirements for the Fabric items in the AnalyticsPOC workspace:
  Fabric administrators will be the workspace administrators.
  The data engineers must be able to read from and write to the data store. No access must be granted to datasets or reports.
  The analytics engineers must be able to read from, write to, and create schemas in the data store. They also must be able to create and share semantic models with the data analysts and view and modify all reports in the workspace.
  The data scientists must be able to read from the data store, but not write to it. They will access the data by using a Spark notebook
  The data analysts must have read access to only the dimensional model objects in the data store. They also must have access to create Power BI reports by using the semantic models created by the analytics engineers.
  The date dimension must be available to all users of the data store.
  The principle of least privilege must be followed.
  Both the default and custom semantic models must include only tables or views from the dimensional model in the data store. Litware already has the following Microsoft Entra security groups:
  FabricAdmins: Fabric administrators
  AnalyticsTeam: All the members of the analytics team
  DataAnalysts: The data analysts on the analytics team
  DataScientists: The data scientists on the analytics team
  DataEngineers: The data engineers on the analytics team
  AnalyticsEngineers: The analytics engineers on the analytics team
  
  Report Requirements -
  The data analysts must create a customer satisfaction report that meets the following requirements:
  Enables a user to select a product to filter customer survey responses to only those who have purchased that product.
  Displays the average overall satisfaction score of all the surveys submitted during the last 12 months up to a selected dat.
  Shows data as soon as the data is updated in the data store.
  Ensures that the report and the semantic model only contain data from the current and previous year.
  Ensures that the report respects any table-level security specified in the source data store.
  Minimizes the execution time of report queries.
  Which type of data store should you recommend in the AnalyticsPOC workspace?

  • A. a data lake
  • B. a warehouse
  • C. a lakehouse
  • D. an external Hive metastore

  Correct Answer: C

  Explanation:

  Based on the question and discussion, the AI agrees with the suggested answer of C. a lakehouse.
  Reasoning: The primary reason for choosing a lakehouse is its ability to handle diverse data types, including structured, semi-structured, and unstructured data. Given the requirements for handling semi-structured and unstructured data in the AnalyticsPOC workspace, a lakehouse architecture is the most suitable choice. Lakehouses combine the features of data lakes and data warehouses, providing the flexibility to store various data formats and the analytical capabilities for querying and processing that data. This aligns perfectly with the need to perform analytics on different types of data within the workspace.
  Reasons for not choosing other options:

  • A. a data lake: While data lakes are good for storing unstructured data, they typically lack the ACID transactions and data governance features needed for reliable analytics.
  • B. a warehouse: Data warehouses are primarily designed for structured data and are not well-suited for handling semi-structured or unstructured data.
  • D. an external Hive metastore: A Hive metastore is a component used for metadata management, typically within a Hadoop ecosystem. It doesn't provide the comprehensive data storage and analytical capabilities required for the AnalyticsPOC workspace.

• Question 10

  You have a Fabric warehouse that contains a table named Staging.Sales. Staging.Sales contains the following columns.
  
  You need to write a T-SQL query that will return data for the year 2023 that displays ProductID and ProductName and has a summarized Amount that is higher than 10,000.
  Which query should you use?

  • 
  • 
  • 
  • 

  Correct Answer: A

  Explanation:

  The AI agrees with the suggested answer, which is A.
  Reasoning:

  • Option A correctly filters the data for the year 2023 in the WHERE clause and filters the aggregated amount using the HAVING clause, which is the standard SQL practice. The HAVING clause is used to filter groups after aggregation.
  • Option A uses the SUM(Amount) in the HAVING clause, which is correct since HAVING must filter on aggregated values.

  Reasons for not choosing the other options:

  • Option B: The syntax is incorrect because it attempts to use an alias (TotalAmount) in the HAVING clause, which is not allowed. The HAVING clause requires the aggregate function itself. Also, the year filter is in the wrong place.
  • Option C: The syntax is incorrect because it attempts to use an alias (TotalAmount) in the HAVING clause, which is not allowed. The HAVING clause requires the aggregate function itself. Also, the year filter is missing.
  • Option D: The syntax is incorrect because it attempts to use an alias (TotalAmount) in the HAVING clause, which is not allowed. The HAVING clause requires the aggregate function itself. Also, the year filter is missing.

  Citations:

  • HAVING Clause, https://www.w3schools.com/sql/sql_having.asp