DP-600 Fabric Analytics Engineer Practice Exam

These questions are part of the practice quiz. Start practicing

• 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.
• In the TOP 10 with ROW_NUMBER example, what are the Ranking values for the first two records?
• Which Python expression selects rows in df_customers where any column contains NULL values (axis=1)?
• Which function activates different physical relationships between tables during a query execution?
• What should you do to schedule Notebook1 to run daily as part of an ingestion workflow?
• In a lakehouse scenario with external data schema changing regularly, which destination action ensures the target matches the external data schema?
• In the DAX Rolling 12 Overall Satisfaction measure, which term should replace <ANSWER HERE> to apply the 12-month period filter?
• 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?
• When using Load to Tables to convert CSVs to Delta format, which optimization is enabled?
• How many data scientists are included in the planning scenario?
• Which function and value should you insert for <target1> and <target2> to display the previous year's sales in the given window function?
• What is the name of the Fabric lakehouse that contains a Dataflow Gen2 query in this scenario?
• Which clause completes the code to return the last updated time of the most recent row for each customer?
• Which function defines crossfiltering direction of a physical relationship?
• Which PowerQuery function should you include in the PowerQuery step to identify the maximum values for numeric columns during profiling?
• To prevent report creators from populating visuals by using implicit measures, which tool can you use?
• Which SCD type keeps the latest data, with old data overwritten?
• 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.
• In a Fabric semantic model using Direct Lake mode, which tool can identify frequently used columns loaded into memory?
• Litware Price Groups: In the first T-SQL example, which keyword completes the CREATE statement to define the object?
• Which command provides a quick, initial view of basic stats for numeric columns?
• In Delta lake maintenance, VACUUM removes old files no longer referenced by a Delta table log.
• In Power Query Editor, which tab would you use to create a derived column based on a conditional statement?
• Which columns define the identity and versioning in the CustomerMaster example?
• 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?
• 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?
• Which DAX function should you use to establish a virtual relationship between two tables with no physical relationship?
• When joining a large fact table with a small dimension table in Spark, which technique reduces network I/O and avoids expensive shuffles?
• When calling describe() on a dataset, which statistics are included for numeric columns?
• A DAX measure uses nested IFs to categorize variance. To optimize for speed, which code snippet should you use?
• 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?
• 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?
• To minimize the number of rows added to the Orders table during refreshes, which solution component should be included?
• 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?
• 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?
• Which statement about broadcast joins in PySpark is true?
• Type 2 slowly changing dimension (SCD) describes which concept?
• Which DAX function enables a virtual relationship across unrelated tables?
• For a DirectQuery semantic model with a large dataset, which feature helps reduce query time for visuals on all pages?
• Which SCD type is least suitable when the data model is expected to evolve with new attributes?
• 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?
• In Microsoft Power BI Desktop, to diagram the model containing only the Sales table and its related tables, which view should you use?
• Which built-in tool in Power BI Desktop helps identify the visual that takes the longest to render on a page?
• Which statement best describes a broadcast join in Spark?
• 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 SQL statement retrieves the latest last_stocked_date for each category where stock_quantity is less than 50?
• Which two activities support parameterization in the data pipeline UI?
• In Power BI, to let users change the y-axis category of a bar chart using a slicer, which feature should you add?
• Which operation consolidates small Parquet files into larger ones?
• 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?
• Best load for large data source.
• 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?
• Which command should you run to add a primary key on CustomerKey in dimCustomer, including NOT ENFORCED?
• 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 complete the query that analyzes Sales by ProductName and Year for products with yearly SalesAmount greater than 10000, which clause completes the statement?
• For reducing query load on Azure SQL during full semantic model refreshes, which deployment pipeline adjustment should you configure?
• Which tool is recommended to conform the date format across many date columns quickly in a complex semantic model?
• CustomerMaster is described as a Type 2 slowly changing dimension. Which statement best describes this concept?
• Litware Price Groups: In the first T-SQL snippet, which keyword should begin the CASE expression inside the SELECT?
• Which other tool can you use to prevent implicit measures?
• 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?
• If warehouse queries degrade in performance due to throttling, which app should you use to identify throttling?
• What does V-ORDER do?
• To optimize a complex semantic model by reducing joins, which tables should you denormalize?
• Which mode must the semantic models in the Research division workspaces use?
• 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?
• In a Fabric notebook, which syntax should you use to access the Research division data for Productline1?
• Which window function should fill the placeholder to return the most recent row per CustomerID?
• 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?
• To schedule a pipeline to run every four hours on Mondays and Fridays, which Repeat setting should you use?
• What is the general purpose of slowly changing dimensions?
• 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?
• Which of the following statements about joining a large fact table with a small dimension in Spark is true?
• To minimize the number of emails sent when Pipeline1 experiences issues, which connection option should you configure for the Office 365 Outlook activity?
• For migrating a 200 million row table from an external Snowflake database into Lakehouse1, which ingestion method is most performant?
• Which operation reorders and encodes Delta parquet data to improve read performance?
• In DAX, what is the effect of using ALL around a column, such as ALL('Product'[Product Name])?
• 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 PySpark method returns a DataFrame containing the first N rows?
• In designing data loading for a Type 1 slowly changing dimension, which wording best describes the required action?
• 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?
• Which maintenance command removes obsolete files while respecting a retention policy in Delta Lake, helping reclaim storage without discarding needed data?
• How many Power BI modelers with an individual Power BI Pro license are included in the planning scenario?
• 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?
• When querying sales data files by using the SQL endpoint with data in S3, which file format should you use?
• How many Microsoft Power BI consumers without an individual Power BI Pro license are included in the planning scenario?
• Which pattern preserves historical values in a dimensional model during changes over time?
• Which statement about the XMLA endpoint in Fabric is true?
• Which SQL statement returns the total Quantity per Product_ID for January 2024?
• Which expression sorts the grouped counts by ProductKey in descending order?
• In a Power BI visual using a DAX query, which type of analytics line is produced by the MEDIANX calculation?
• In Power BI, which Power Query option helps you identify the percentage of valid records in a column before loading to the report?
• To enable read-write access to a dataset via the XMLA endpoint in a Fabric workspace, which setting should be modified first?
• Which DMV would you query to view currently running requests in a Fabric data environment?
• Where can you find the SQL connection string for Lakehouse1 when using SSMS?
• Which practice supports governance when distributing Fabric content across multiple workspaces?
• To retrieve a filtered list of stores using an XMLA endpoint, which approach is most appropriate?
• Which function should you run to make a Spark DataFrame's data available in the Chart view of a notebook?
• Which practice minimizes data shuffling when joining a large fact dataset with a small dimension dataset in Spark?
• 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?
• Which DAX function creates the rolling date range for the last N months in the measure?
• In a Dataflow Gen2 merge, which join type excludes records present in the second dataset from the first?
• Type 6 slowly changing dimension is described as a combination of Type 1, Type 2, and Type 3.
• Ingesting CustomerMaster as a Type 2 slowly changing dimension, which step ensures you keep the latest record for each Customer ID?
• What is an advantage of Type 1 slowly changing dimension?
• 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 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?
• Which approach is recommended when joining a large transactions DataFrame with a small customers DataFrame to minimize shuffling in PySpark?
• In the second DAX measure example, which expression should replace <ANSWER HERE> to calculate the average of responses?
• Which SCD type is the passive method that always retains the original value?
• 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?
• Dimension_city table used in the visualization has which two columns?
• 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?
• In a Fabric lakehouse, which join type should you use to include products with no sales when analyzing by product?
• In the PySpark visualization code, which axis displays the SalesTerritory categories on the bar chart?
• Which statement accurately describes the DAX measure RANKX( ALL( 'Product'[Product Name] ), [Sales],, DESC, Skip )?
• 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?
• 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?
• In the schema with dimGeography and FactSales, which field is used to join the two tables?
• What does the Fabric notebook code using PowerBIClient accomplish?
• In the DAX TOPN expression used in the visual, what does TOPN(1001, __DS0Core, [Sales], 0, 'Company'[Manufacturer], 1) 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?
• 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 a subsequent variant of the T-SQL statement, which keyword should begin the CASE expression?
• 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?
• Best practice analyzer.
• What does OPTIMIZE do in Lakehouse Delta lake table maintenance?
• In the Direct Lake mode scenario, which tool can identify loaded columns?
• To enforce access control for embedded reports and prevent public sharing, which tenant setting should you disable?
• Where should you create a shortcut when querying S3 data and using Delta format?
• Best load for small data or specific connector.
• Which statement is true about SCD in general?
• Which function applies the result of a table expression as filters to columns from an unrelated table?
• Which Lakehouse table type is a managed table?
• Which tool would you use to script and apply date formatting across many date columns in a tabular model?
• Which SCD type adds a new attribute to store the changed value?
• In the DAX query example, which function computes the median of Sales across the current filters?
• Which SCD type uses a separate history table and a surrogate key with the fact table to identify original and historic data?
• What is the effect of using a broadcast join in a Spark job?
• Which tool is used to bulk edit and save the semantic model as a TMDL extension file in Visual Studio Code?
• Which tool would you use to update a measure definition without requiring a service refresh?
• Which two tools are commonly used to profile and analyze DAX queries in Power BI?
• To profile data in a Fabric notebook with minimal administrative effort, which approach should you take?
• 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?
• What is the recommended method to ingest preformatted forecast data into a Fabric lakehouse with minimal development effort and cost?
• 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?
• 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 line of code loads all rows from the Product table into a DataFrame using Spark SQL?
• 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?
• To restrict access to the CompanyCosts measure so that only User1 can view it in reports, which security concept should you implement?
• 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?
• 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 component should be used to minimize refresh changes to the Orders table when refreshing from OneLake?
• 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?
• When calling describe() on object (string) columns, which statistics are included?
• 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?
• To allow a user to truncate tables in a specific schema, which permission should be used in the GRANT statement?
• To audit a Power BI semantic model for performance/design issues, which Tabular Editor tool should you use?
• Which SCD type uses a separate history table and a surrogate key to map to original and historic data?
• PBIP is the file format used to save semantic models when using the Tabular Model Definition Language extension.
• Which SCD type keeps the history of old data by adding a new row?
• The df.write.partitionBy('year','month','day').mode('overwrite') results in what data layout?
• ALM Toolkit.
• Which operation removes old files not referenced by the Delta table log?
• While denormalizing Employee and Contract tables to include ContractType, which join type preserves all rows from Employee and includes matching ContractType data?
• To grant ALTER on a specific schema for User1, which object should appear after the ON clause?
• Which PySpark code should you run to load the Customers.parquet data into a pandas DataFrame?
• 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?
• Best load for complex data transformations.
• Which query correctly returns ProductID, ProductName, and TotalAmount (SUM(Amount)) for year 2023 with TotalAmount > 10,000 from Staging.Sales?
• What type of dimension is used to support point-in-time analysis by persisting changes in a new row with a timestamp?
• lakehouse shortcuts allow users to reference data without copying it.
• 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?
• What is a disadvantage of Type 2 slowly changing dimension?
• Which join would you use to ensure you keep all rows from the left query and include matches from the right query?
• Which tool would a user use to read Lakehouse data in a Lakehouse Explorer within the Fabric environment?
• A database query performance issue arises when statistics are out of date. Which column statistics should you update to improve performance for a table?
• 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?
• For a Copy data activity that replaces Table1 with the schema of external data and replaces all data, which option should you set?
• Which description best matches a broadcast join in Spark?
• 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?
• In a Type 1 slowly changing dimension, which of the following best captures the required action to reflect changes in non-key attributes?
• Which two actions should you include in the data loading pattern for a Type 1 slowly changing dimension to reflect changes in attributes?
• 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 two languages can be used to perform model scoring with the PREDICT function in a Fabric notebook?
• In Fabric, which license type should you choose to support 10 report authors and 600 report consumers when using Dataflow Gen2 for data ingestion?
• Which feature allows referencing data without copying in lakehouse context?
• Which DAX function returns a string of all the members in the column hierarchy?
• 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?
• To convert CSV files in Subfolder1 into Delta with V-Order optimization, which Lakehouse Explorer feature should you use?
• AnalyticsPOC data load requirement: Which statement describes the rule for updating data before populating the dimensional model?
• After connecting to Lakehouse1 via its SQL endpoint, what will you be able to read?
• What is a disadvantage of Type 3 slowly changing dimension?
• Which file format is used for raw data loaded into OneLake according to the Delta Lake specifications?
• Which tool should you use to identify which report element consumes the most rendering time with minimal administrative effort?
• Which file format is used to store per-customer data in the Parquet folder structure described?
• 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?
• What should you configure first to enable scale-out for a semantic model in a Microsoft Power BI Premium capacity?
• 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?
• Which of the following is a valid pandas call to read a Parquet file into a DataFrame?
• 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?
• Which Fabric tool is used to view basic summary statistics before loading data to a Delta table?
• 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 approach ensures all rows appear in a Python visual by modifying the data rather than the visual settings?
• Research division data ingestion: Which tool should be used by data engineers to ingest customer data into the data store in the AnalyticsPOC workspace?
• 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?
• Which type is described as 'the history is preserved by adding new rows'?
• What is the purpose of a type 2 slowly changing dimension in a data warehouse?
• 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?
• Which file format is used for raw data stored in OneLake as part of the data pipeline?
• Which PySpark expression returns the total number of records in the fact table grouped by ProductKey, with results sorted by count in descending order?
• 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 two parameters should you create to enable incremental refresh for a Power BI table in a typical scenario?
• 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 top-level keyword starts an XMLA/DAX query that returns a table result?
• Which external tool can you use to identify which columns contribute most to model size by providing size information for each table and column?
• In the Litware price groups T-SQL example, which keyword completes the statement to start the CASE expression within the SELECT?
• To ensure all rows appear in a Python visual that groups automatically and hides duplicates, what should you do?
• 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?
• 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?
• 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?
• What is the primary role of the Dataflow Gen2 query within Lakehouse1 in this scenario?
• When connecting to Lakehouse1 via its SQL endpoint, which table type is readable?
• In Power BI Desktop, you limit the data you work with by using parameters. What feature is this describing?