DP-600 Fabric Analytics Engineer Practice Exam

These questions are part of the practice quiz. Start practicing

• Which statement is true about SCD in general?
• In the PySpark visualization code, which axis displays the SalesTerritory categories on the bar chart?
• Which two parameters should you create to enable incremental refresh for a Power BI table in a typical scenario?
• In the schema with dimGeography and FactSales, which field is used to join the two tables?
• When calling describe() on object (string) columns, which statistics are included?
• What is the general purpose of slowly changing dimensions?
• Which DAX function creates the rolling date range for the last N months in the measure?
• Given a model with a Calendar Dimension and a Sales fact with a 1-to-many relationship, which measure expression provides better performance when filtering by a specific year?
• In a Dataflow Gen2 Diagram, after selecting FactSales and adding dimGeography, what should you do to retain all rows in FactSales and include geography where available?
• Litware Price Groups: In the first T-SQL example, which keyword completes the CREATE statement to define the object?
• To retrieve a filtered list of stores using an XMLA endpoint, which approach is most appropriate?
• In a Type 1 slowly changing dimension, which of the following best captures the required action to reflect changes in non-key attributes?
• In a Power BI model with multiple dimensions, which feature allows end users to switch the axis category for a single visual among ProductCategory, Year, and CustomerCity with minimal effort?
• Which feature allows referencing data without copying in lakehouse context?
• During an incremental refresh of a model built on 30 CSV files in OneLake, the refresh fails due to resource exhaustion. What is a plausible cause?
• Which file format is used to store per-customer data in the Parquet folder structure described?
• You plan to use a Fabric notebook and PySpark to read sales data and save the data as a Delta table named Sales. The table must be partitioned by Year and Quarter. You load the sales data to a DataFrame named df that contains a Year column and a Quarter column. Which option describes the correct approach to write this as a partitioned Delta table?
• Which columns define the identity and versioning in the CustomerMaster example?
• Which two tools are commonly used to profile and analyze DAX queries in Power BI?
• You have a Fabric workspace named Workspace1 that contains a lakehouse named Lakehouse1. You have write permissions to an Azure Data Lake Storage Gen2 account named storage1 that contains a folder named Folder1. You plan to delete a shortcut named Shortcut1 that points to a file named File1 stored in Folder1. You run the delete operation on the following path. Lakehouse1\Files\Shortcut1 What will occur after you run the delete operation?
• In a Fabric environment where a report is running much longer than usual, which DMV should you query to identify the currently executing requests causing the delay?
• When connecting to Lakehouse1 via its SQL endpoint, which table type is readable?
• You notice a Delta Parquet lakehouse table with over 1,000 versions and a six-month retention policy. To reduce the table size and the number of files, which maintenance action should you configure?
• Which mode must the semantic models in the Research division workspaces use?
• In the DAX TOPN expression used in the visual, what does TOPN(1001, __DS0Core, [Sales], 0, 'Company'[Manufacturer], 1) do?
• Which function activates different physical relationships between tables during a query execution?
• After connecting to Lakehouse1 via its SQL endpoint, what will you be able to read?
• Which tool is used to bulk edit and save the semantic model as a TMDL extension file in Visual Studio Code?
• To convert CSV files in Subfolder1 into Delta with V-Order optimization, which Lakehouse Explorer feature should you use?
• Which command provides a quick, initial view of basic stats for numeric columns?
• In a Fabric semantic model using Direct Lake mode, which tool can identify frequently used columns loaded into memory?
• To restrict access to the CompanyCosts measure so that only User1 can view it in reports, which security concept should you implement?
• Which file format is used for raw data stored in OneLake as part of the data pipeline?
• Which SCD type keeps the history of old data by adding a new row?
• Which query correctly returns ProductID, ProductName, and TotalAmount (SUM(Amount)) for year 2023 with TotalAmount > 10,000 from Staging.Sales?
• In DAX, what is the effect of using ALL around a column, such as ALL('Product'[Product Name])?
• Which top-level keyword starts an XMLA/DAX query that returns a table result?
• Which window function should fill the placeholder to return the most recent row per CustomerID?
• A lakehouse maintenance task aims to improve query performance and reduce storage costs for a Delta table with seven days retention and many small files. Which combination of commands should you run?
• With XMLA Endpoint set to Read Write, what is the main limitation of using XMLA endpoints for the Microsoft Power BI deployment process when deploying changes to only one table from the data model?
• Which statement best describes a broadcast join in Spark?
• What does V-ORDER do?
• Which two actions should you include in the data loading pattern for a Type 1 slowly changing dimension to reflect changes in attributes?
• In Delta lake maintenance, VACUUM removes old files no longer referenced by a Delta table log.
• What is an advantage of Type 1 slowly changing dimension?
• Which SQL statement returns the total Quantity per Product_ID for January 2024?
• Which tool is recommended to conform the date format across many date columns quickly in a complex semantic model?
• To enable end users to analyze SalesAmount by ProductCategory, Year, or CustomerCity using a single column chart with minimal development effort, what modeling feature should you implement?
• Which statement about broadcast joins in PySpark is true?
• CustomerMaster is described as a Type 2 slowly changing dimension. Which statement best describes this concept?
• Which SQL statement retrieves the latest last_stocked_date for each category where stock_quantity is less than 50?
• In the second DAX measure example, which expression should replace <ANSWER HERE> to calculate the average of responses?
• Which SCD type adds a new attribute to store the changed value?
• Which operation consolidates small Parquet files into larger ones?
• In the DAX query example, which function computes the median of Sales across the current filters?
• To complete the query that analyzes Sales by ProductName and Year for products with yearly SalesAmount greater than 10000, which clause completes the statement?
• Which DAX function should you use to establish a virtual relationship between two tables with no physical relationship?
• Dimension_city table used in the visualization has which two columns?
• Which Fabric tool is used to view basic summary statistics before loading data to a Delta table?
• For a DirectQuery semantic model with a large dataset, which feature helps reduce query time for visuals on all pages?
• For reducing query load on Azure SQL during full semantic model refreshes, which deployment pipeline adjustment should you configure?
• In the DAX Rolling 12 Overall Satisfaction measure, which term should replace <ANSWER HERE> to apply the 12-month period filter?
• You have an Azure SQL database that contains a customer dimension table. The table contains two columns named CustomerID and CustomerCompositeKey. You have a Fabric workspace that contains a Dataflow Gen2 query that connects to the database. You need to use Dataflows Query Editor to identify which of the two columns contains non-duplicate values per customer. Which option should you use?
• What is a disadvantage of Type 3 slowly changing dimension?
• In a table visual with three measures (Sales, Units, and Customers), which approach allows formatting logic to apply only to the Sales measure while preventing changes to the other two measures?
• Which of the following is a valid pandas call to read a Parquet file into a DataFrame?
• If warehouse queries degrade in performance due to throttling, which app should you use to identify throttling?
• Best load for small data or specific connector.
• To profile data in a Fabric notebook with minimal administrative effort, which approach should you take?
• In a Fabric lakehouse, which join type should you use to include products with no sales when analyzing by product?
• PBIP is the file format used to save semantic models when using the Tabular Model Definition Language extension.
• In a Fabric workspace containing a Power BI report, when access to a data source is restricted to narrow time windows, what is the recommended approach to bring data into a Power BI semantic model using dataflows?
• What is the recommended method to ingest preformatted forecast data into a Fabric lakehouse with minimal development effort and cost?
• In the TOP 10 with ROW_NUMBER example, what are the Ranking values for the first two records?
• To combine data from Azure Databricks and an existing Fabric lakehouse using data pipelines for Databricks data and shortcuts for the lakehouse, which Fabric artifact should you create?
• To enable read-write access to a dataset via the XMLA endpoint in a Fabric workspace, which setting should be modified first?
• Which built-in tool in Power BI Desktop helps identify the visual that takes the longest to render on a page?
• You have a Fabric lakehouse named Lakehouse1. Forecast data stored in Azure Data Lake Storage Gen2. You plan to ingest the forecast data into Lakehouse1. The data is already formatted, and you do NOT need to apply any further data transformations. The solution must minimize development effort and costs. Which method should you recommend to efficiently ingest the data?
• Which two languages can be used to perform model scoring with the PREDICT function in a Fabric notebook?
• You plan to copy data to an unpartitioned Table1 and partition by a date column. What should you configure first in the Copy activity's Destination settings?
• When using Load to Tables to convert CSVs to Delta format, which optimization is enabled?
• Which approach ensures all rows appear in a Python visual by modifying the data rather than the visual settings?
• If a dataflow experiences timeouts due to the number of Power Query transformations, and query optimization and refresh timing cannot be adjusted, what is the recommended action?
• Which expression sorts the grouped counts by ProductKey in descending order?
• A DAX measure uses nested IFs to categorize variance. To optimize for speed, which code snippet should you use?
• Which tool would you use to script and apply date formatting across many date columns in a tabular model?
• Which PySpark code should you run to load the Customers.parquet data into a pandas DataFrame?
• In a Dataflow Gen2 merge, which join type excludes records present in the second dataset from the first?
• You publish a very large Microsoft Power BI semantic model to a Power BI workspace. The model refresh will take two hours. In Power BI Desktop, you limit the data you work with by using parameters. You need to update the definition of a measure. What can you use to update the measure definition without having to refresh the model in the Power BI service?
• Which pattern preserves historical values in a dimensional model during changes over time?
• You have a Fabric workspace that contains a set of Dataflow Gen2 queries. You plan to use the native Dataflow Gen2 refresh scheduler to configure the queries to refresh as often as possible. What is the fastest refresh interval that can be configured?
• Type 6 slowly changing dimension is described as a combination of Type 1, Type 2, and Type 3. Which option best reflects this statement?
• Which operation is most appropriate to rewrite small Delta files into larger, more scan-efficient files to reduce the number of files and improve performance?
• To ingest CustomerMaster and keep only the latest record for each Customer ID, which combination of applied steps should you implement in a Dataflow transformation?
• You have a Fabric workspace that contains a lakehouse named Lakehouse1. You need to create a data pipeline and ingest data into Lakehouse1 by using the Copy data activity. Which properties on the General tab are mandatory for the activity?
• If a report is taking an unusually long time to render, which DMV provides real-time information about active requests that can help diagnose the issue?
• Which tool should you use to identify which report element consumes the most rendering time with minimal administrative effort?
• What does the Fabric notebook code using PowerBIClient accomplish?
• In a Fabric notebook, which syntax should you use to access the Research division data for Productline1?
• In a lakehouse scenario with external data schema changing regularly, which destination action ensures the target matches the external data schema?
• In a Power BI visual using a DAX query, which type of analytics line is produced by the MEDIANX calculation?
• You have a semantic model that pulls data from an Azure SQL database and is synced via Fabric deployment pipelines to Development, Test, and Production workspaces. You need to reduce the size of the query requests sent to the Azure SQL database when full semantic model refreshes occur in Development or Test. What should you do for the deployment pipeline?
• Which function and value should you insert for <target1> and <target2> to display the previous year's sales in the given window function?
• You have a new Fabric tenant. You need to recommend a workspace architecture to meet best practices for content distribution and data governance. Which action should you recommend?
• To prevent report creators from populating visuals by using implicit measures, which tool can you use?
• Where can you find the SQL connection string for Lakehouse1 when using SSMS?
• Which type is described as 'the history is preserved by adding new rows'?
• Which SCD type is the passive method that always retains the original value?
• Which Python expression selects rows in df_customers where any column contains NULL values (axis=1)?
• To schedule a pipeline to run every four hours on Mondays and Fridays, which Repeat setting should you use?
• Which function applies the result of a table expression as filters to columns from an unrelated table?
• To minimize the number of rows added to the Orders table during refreshes, which solution component should be included?
• You have a Fabric tenant that contains a workspace named Workspace1. Workspace1 contains a warehouse that has a table named Orders. You have a Microsoft Power BI semantic model in Power BI Desktop that sources data from the Orders table. You need to enable incremental refresh for the table. Which two parameters should you create?
• What should you do to schedule Notebook1 to run daily as part of an ingestion workflow?
• Litware Price Groups: In the first T-SQL snippet, which keyword should begin the CASE expression inside the SELECT?
• To optimize a complex semantic model by reducing joins, which tables should you denormalize?
• When joining a large fact table with a small dimension table in Spark, which technique reduces network I/O and avoids expensive shuffles?
• lakehouse shortcuts allow users to reference data without copying it.
• Which SCD type uses a separate history table and a surrogate key with the fact table to identify original and historic data?
• Which component should be used to minimize refresh changes to the Orders table when refreshing from OneLake?
• Which PySpark method returns a DataFrame containing the first N rows?
• To enforce access control for embedded reports and prevent public sharing, which tenant setting should you disable?
• For migrating a 200 million row table from an external Snowflake database into Lakehouse1, which ingestion method is most performant?
• What is the purpose of a type 2 slowly changing dimension in a data warehouse?
• Which clause completes the code to return the last updated time of the most recent row for each customer?
• When calling describe() on a dataset, which statistics are included for numeric columns?
• Which other tool can you use to prevent implicit measures?
• ALM Toolkit.
• Best practice analyzer.
• What should you configure first to enable scale-out for a semantic model in a Microsoft Power BI Premium capacity?
• Which function should you run to make a Spark DataFrame's data available in the Chart view of a notebook?
• In Microsoft Power BI Desktop, to diagram the model containing only the Sales table and its related tables, which view should you use?
• What is the effect of using a broadcast join in a Spark job?
• Which join would you use to ensure you keep all rows from the left query and include matches from the right query?
• Where should you create a shortcut when querying S3 data and using Delta format?
• Your workspace contains a Dataflow Gen2 query that displays dimensional product information. The query table contains a column named Product ID/Name that is a concatenation of Product ID and Product Name values. You need to use an applied step in Microsoft Power Query Editor to create a new column for Product ID and Product Name. The solution must use a single command to create two new columns and remove the original combined (Product ID/Name) column. Which applied step should you use?
• You plan to implement Fabric to store company sales data and support yearly ad-hoc analysis of legacy accounting data stored in Azure Data Lake Storage Gen2. Which architecture and integration approach minimizes administrative effort and costs?
• To reduce the number of queries sent to the database when a user interacts with a DirectQuery report using filters and slicers, what should you do?
• Which sequence correctly retrieves a list of stores opened after a given date by first projecting the Name and OpenDate columns and then applying the date filter?
• To ensure all rows appear in a Python visual that groups automatically and hides duplicates, what should you do?
• What type of dimension is used to support point-in-time analysis by persisting changes in a new row with a timestamp?
• Which SCD type is least suitable when the data model is expected to evolve with new attributes?
• To audit a Power BI semantic model for performance/design issues, which Tabular Editor tool should you use?
• To minimize the number of emails sent when Pipeline1 experiences issues, which connection option should you configure for the Office 365 Outlook activity?
• Which practice minimizes data shuffling when joining a large fact dataset with a small dimension dataset in Spark?
• Best load for large data source.
• Ingesting CustomerMaster as a Type 2 slowly changing dimension, which step ensures you keep the latest record for each Customer ID?
• What does OPTIMIZE do in Lakehouse Delta lake table maintenance?
• How many Power BI modelers with an individual Power BI Pro license are included in the planning scenario?
• When saving a semantic model changed with the Tabular Model Definition Language extension in Visual Studio Code, which file format should you save to?
• Which file format is used for raw data loaded into OneLake according to the Delta Lake specifications?
• In the Direct Lake mode scenario, which tool can identify loaded columns?
• For a Copy data activity that replaces Table1 with the schema of external data and replaces all data, which option should you set?
• In a DAX query executed via the XMLA endpoint, which function should you use to create a table with specific columns (for example Name and OpenDate) before applying a date filter?
• You have a Fabric workspace that contains a Microsoft Power BI report. You need to modify the column names in the Power BI report without changing the original names in the underlying Delta table. Which warehouse object should you create?
• While denormalizing Employee and Contract tables to include ContractType, which join type preserves all rows from Employee and includes matching ContractType data?
• Which tool would a user use to read Lakehouse data in a Lakehouse Explorer within the Fabric environment?
• To ensure embedded reports require authentication and only read-only live connections against Fabric capacity cloud semantic models, which action should you perform in the Fabric admin portal?
• To grant ALTER on a specific schema for User1, which object should appear after the ON clause?
• To improve a Dataflow Gen2 ingestion performance, which ordering of steps is recommended when dealing with a DateTime column that must be filtered to the current year and then split by position?
• In a Fabric workspace that contains a lakehouse named Lakehouse1, which workspace role should you assign to User1 to follow the principle of least privilege for reading Lakehouse1 data?
• Which two activities support parameterization in the data pipeline UI?
• AnalyticsPOC data store recommendation: Which type of data store is suitable given read access via T-SQL or Python, semi-structured data, and Delta Lake compatibility?
• A Fabric tenant has a workspace named Workspace1 containing a lakehouse, a data pipeline, a notebook, and several Microsoft Power BI reports. A user named User1 plans to use SQL to access the lakehouse to analyze data. User1 must have read-only access to the lakehouse, must NOT be able to access the rest of the items in Workspace1, and must NOT be able to use Spark to query the underlying files. How should you configure access for User1?
• Which description best matches a broadcast join in Spark?
• In Power Query Editor, which tab would you use to create a derived column based on a conditional statement?
• Which approach is recommended when joining a large transactions DataFrame with a small customers DataFrame to minimize shuffling in PySpark?
• Type 2 slowly changing dimension (SCD) describes which concept?
• Which operation reorders and encodes Delta parquet data to improve read performance?
• In the Litware price groups T-SQL example, which keyword completes the statement to start the CASE expression within the SELECT?
• You have two lakehouses named Lakehouse1 and Lakehouse2. Lakehouse1 contains a table FactSales that is partitioned by a column named CustomerID. You need to create a shortcut to the FactSales table in Lakehouse2. The shortcut must only connect to data for CustomerID 100. What should you do?
• A Power BI report named Sales uses an Excel data source with a flat table containing ProductID, ProductColor, ProductName, ProductCategory, and SalesAmount. You want an optimal fact table in a star schema. Which two columns should remain part of the new fact tables?
• AnalyticsPOC data load requirement: Which statement describes the rule for updating data before populating the dimensional model?
• Which SCD type uses a separate history table and a surrogate key to map to original and historic data?
• What is the primary role of the Dataflow Gen2 query within Lakehouse1 in this scenario?
• When querying sales data files by using the SQL endpoint with data in S3, which file format should you use?
• Which DMV would you query to view currently running requests in a Fabric data environment?
• In Power Query Editor, which option on the Add Column tab should you select to create a new column based on a product's unit price where values over $1,000 are labeled High and others Regular?
• Which external tool can you use to identify which columns contribute most to model size by providing size information for each table and column?
• What is a disadvantage of Type 2 slowly changing dimension?
• Which maintenance command removes obsolete files while respecting a retention policy in Delta Lake, helping reclaim storage without discarding needed data?
• You have a Microsoft Power BI semantic model that contains a large fact table. You plan to enable incremental refresh. Which two parameters should you add to Power Query to enable incremental refresh?
• In a T-SQL statement ranking specific customers by SalesAmount with ties, which function should you use for the Ranking column to ensure proper tie handling?
• In a Fabric workspace that contains a lakehouse, a data pipeline, a notebook, and several Microsoft Power BI reports, which artifact is used to present analytics to business users?
• Litware Price Groups: In a subsequent variant of the T-SQL statement, which keyword should begin the CASE expression?
• The df.write.partitionBy('year','month','day').mode('overwrite') results in what data layout?
• Type 6 slowly changing dimension is described as a combination of Type 1, Type 2, and Type 3.
• Which command should you run to add a primary key on CustomerKey in dimCustomer, including NOT ENFORCED?
• Research division data ingestion: Which tool should be used by data engineers to ingest customer data into the data store in the AnalyticsPOC workspace?
• In Dataflow Gen2, you plan to merge the Sales table with ExceptionRecords so that records present in ExceptionRecords are excluded from Sales, while maintaining query folding. Which applied steps should you configure?
• To populate the results DataFrame by joining a large transactions DataFrame with a small customers DataFrame in PySpark while minimizing data shuffling, which code should you run?
• Which function defines crossfiltering direction of a physical relationship?
• Which DAX function enables a virtual relationship across unrelated tables?
• In Power BI, to let users change the y-axis category of a bar chart using a slicer, which feature should you add?
• Which statement about the XMLA endpoint in Fabric is true?
• Which SCD type keeps the latest data, with old data overwritten?
• Which PowerQuery function should you include in the PowerQuery step to identify the maximum values for numeric columns during profiling?
• In a Direct Lake semantic model that uses Delta tables and warehouse row-level security, when users interact with a report, which mode will the DAX queries use?
• Which PySpark expression returns the total number of records in the fact table grouped by ProductKey, with results sorted by count in descending order?
• Which operation removes old files not referenced by the Delta table log?
• Which tool would you use to update a measure definition without requiring a service refresh?
• In Fabric, which license type should you choose to support 10 report authors and 600 report consumers when using Dataflow Gen2 for data ingestion?
• To allow a user to truncate tables in a specific schema, which permission should be used in the GRANT statement?
• When merging Product and ProductCategory to create a single Product table, and you need all rows from Product with matching ProductCategory data, which join configuration should you use?
• How many data scientists are included in the planning scenario?
• A database query performance issue arises when statistics are out of date. Which column statistics should you update to improve performance for a table?
• Which practice supports governance when distributing Fabric content across multiple workspaces?
• Which statement accurately describes the DAX measure RANKX( ALL( 'Product'[Product Name] ), [Sales],, DESC, Skip )?
• You notice a Fabric compute usage issue, and you need to increase the Fabric capacity unit size. Which portal should you use to perform this change?
• You are mapping queries to aggregated data in user-defined aggregations. Which two tools should you use? Each correct answer presents part of the solution.
• You have a CSV file locally with a static list of company office locations. You need to recommend a method to perform a one-time copy to ingest the CSV file into the lakehouse with minimal administrative effort.
• How many Microsoft Power BI consumers without an individual Power BI Pro license are included in the planning scenario?
• Which of the following statements about joining a large fact table with a small dimension in Spark is true?
• In designing data loading for a Type 1 slowly changing dimension, which wording best describes the required action?
• In Power BI Desktop, you limit the data you work with by using parameters. What feature is this describing?
• In Power BI, which Power Query option helps you identify the percentage of valid records in a column before loading to the report?
• Best load for complex data transformations.
• Which Fabric capacity SKU should be recommended to minimize costs for a deployment that includes 2,000 Power BI consumers without an individual Power BI Pro license, 32 Power BI modelers with a Pro license, and 16 data scientists?
• Which line of code loads all rows from the Product table into a DataFrame using Spark SQL?
• What is the name of the Fabric lakehouse that contains a Dataflow Gen2 query in this scenario?
• You are planning a Fabric analytics solution. You need to recommend a licensing strategy to support 10 Microsoft Power BI report authors and 600 report consumers. The solution must use Dataflow Gen2 for data ingestion and minimize costs. Which Fabric license type should you recommend?
• To ensure that each customer's sales data is written to its own Parquet file within the Parquet folder structure, which data pipeline configuration should you implement?
• Which Lakehouse table type is a managed table?
• Which DAX function returns a string of all the members in the column hierarchy?
• In a Fabric notebook, after loading the Product table into a DataFrame with spark.sql('SELECT * FROM Product'), which PySpark command will display the top 100 rows?