Microsoft DP-600 Fabric Analytic Engineer Practice Exam Test

Explore case study two from Lightware, Inc., focusing on fabric-driven analytics POC to build a data store with a lake, semantic models, and secure access.

Master T-SQL querying with the having clause to filter aggregated sums; learn to retrieve 2023 product IDs and names with totals over 10,000 using Staging Sales data.

query stage.customers table to return each customer ID, name, postal code, and the most recent update time, using row_number and where x = 1.

Demonstrate using a fabric notebook with spark data frames to create a new Power BI report via PBI visualize. Show how display_df_summary_true reveals data frame statistics.

Use the SQL endpoint to read data from the lake house's managed table; external Spark tables aren't accessible, and SQL endpoints are read-only.

Use the Power Query function Table.Profile in a fabric tenant data flow to summarize the maximum values for numeric columns when loading data from a lake to the warehouse.

Use a fabric notebook to generate predictions from a registered ML model via the predict function, using spark SQL or PySpark.

Use the display function to make a spark data frame available in the fabric notebook chart view for interactive data exploration and visualization.

Add a unique field to each row in the Python visual to prevent Power BI from grouping duplicates. Referencing the data by index does not affect grouping after preprocessing.

Practice crafting a DAX query on a fabric semantic model to run via the XMLA endpoint and return stores opened since December 2023.

In a Fabric dataflow, you identify that the pick up longitude column contains duplicate values by profiling 1000 rows in Power Query.

Modify the capacity settings (C1) to enable the Xmla endpoint, granting read/write access to DS1.

Recommend enabling xmla endpoints for external tools, enabling read/write on the xmla endpoint, and allowing users to create fabric items to enable custom direct lake semantic models under least privilege.

Save the semantic model in Power BI desktop using the PBP format to enable bulk edits with the tabular model definition language in Visual Studio Code.

Grant alter on schema A to user one to allow truncating tables within that schema, since alter enables DDL operations. The permission applies at the schema level only.

Grant developers admin access to the fabric deployment pipeline, viewer access to production workspace, and contributor access to development and test workspaces to deploy Power BI items under least privilege.

Learn how to speed up Power BI reports with direct query by using user defined aggregations and automatic aggregations to pre-aggregate data and improve query performance.

In a fabric tenant with 30 daily CSV files, the incremental refresh of a Power BI semantic model can fail when the partition column data type changes, causing resource exhaustion.

in a fabric tenant with Microsoft Power BI premium capacity, enable scale out for a semantic model by turning on large data set storage format at the semantic model level.

This lecture shows that with a direct lake semantic model on a warehouse using row level security, queries operate in direct query mode to enforce LRS.

Use model view in Power BI Desktop to visually diagram a star schema semantic model, centering the sales fact table and its related dimension tables.

Identify memory-heavy columns in a fabric tenant's semantic model using direct lake mode, the Vertipaq analyzer, and DMVs (discover storage table, column segments) to reveal loaded memory usage.

Link order items and product using company ID and product ID, and denormalize company information into customer and product to enable analysis of order items by date, product, and customer.

In a fabric semantic model, split order date time into separate date and time columns and replace total sales amount with a measure to reduce memory and speed refresh.

Use Power BI Desktop and Tabular Editor to discourage implicit measures in a fabric semantic model.

Identify when transformation steps fold at the data source versus run in the Power Query engine using green folding and red non-folding icons, including custom steps.

Configure the copy data activity in a fabric lake house to overwrite the destination table, so the schema and data from the external source are consistently reflected.

Learn how to enable SQL endpoint queries on a fabric lake house by creating a shortcut in the tables section and storing delta format data in an S3 bucket.

Use Lake House Explorer's load to tables feature to convert csv files to Delta format with v order optimization, loading from subfolder one into Delta Lake tables.

First, set the destination tab mode to overwrite in the copy activity to enable partitioning based on a date column.

Understand how the as clone syntax creates a zero-copy clone in fabric, duplicating only metadata while sharing parquet data; the clone is independent, so changes do not reflect across copies.

Learn to populate a data store with fabric using lake house with Delta Lake format for analytics and warehouse with v order write optimization on parquet for fast reads.

Explore how to use Spark partitioning and Parquet files to efficiently manage data frames in a fabric notebook in a lake house, with partitioning, parallel querying, and snappy compression.

Transform data columns into attribute-value pairs by using the unpivot other columns option, turning columns into rows for the vendor ID column.

Configure the fabric data pipeline to a weekly schedule that runs every four hours on Mondays and Fridays.

Use the Microsoft Fabric Capacity Metrics app to identify throttling by tracking operations rejected during capacity limits, as other tools lack real-time throttle diagnostics.

Learn how Spark reads a CSV from a lake house, why column selection happens after load, how partitioning affects performance, and how info schema inference impacts execution time.

Identify why a report stalls by querying the system exec requests DMV in Microsoft Fabric, distinguishing it from system exec sessions and connections on Azure Synapse Analytics.

Schedule vacuum and optimize commands to remove unused files and merge small delta files into larger ones in Lake House one, improving queries for IoT device readings.

Learn how to implement a type 1 SCD data loading pattern by updating existing rows when non-key attributes change and inserting new records for new natural keys.

Create a shortcut from Lake House One to Storage One using https and the dfs endpoint to leverage the data lake storage Gen2 hierarchical namespace.

Learn how to use broadcast join in PySpark to join a large transactions dataframe with a small customers dataframe on customer ID, minimizing data shuffling and speeding up the join.

Assess performance with the Performance Analyzer to compare direct lake and direct query usage; the first card uses direct lake and the second uses direct query, with automatic fallback.

use a fabric notebook with pyspark to add a pickup date column by casting pickup_datetime to date and filter rows where fare_amount is positive and less than 100.

Practice shows using PySpark to compute min, max, mean, and standard deviation for all string and numeric columns with df.describe, not explain or show.

Assess maintenance on the delta table customer in Lake House one by using Spark SQL describe history. The refresh table and explain table commands do not reveal maintenance history.

Apply least-privilege lake security: grant user one read all across the lake house via Apache Spark, and user two read data via the SQL analytics endpoint.

Save the report and semantic model as a Power BI project (PBP) to store definitions as text files in a folder hierarchy for simplified version control.

Orchestrate medallion architecture across bronze, silver, and gold layers. Schedule daily processing in bronze, silver, gold, using invoke pipeline for bronze, data flow for silver, and stored procedure for gold.

Transform a spark data frame in a fabric notebook by casting the age column from string to integer using withColumn, while preserving all columns and verifying results with show.

Load the parquet file from Azure data lake storage gen two into default lake house, and register it as a Delta table named sales for display in lake house explorer.

Use a dataflow to ingest CSV files from an Azure storage account into Lake House one, leveraging Power Query formula language for transformations during data ingestion.

Append and merge schema. Use mode append to add new rows to the delta table without overwriting, and set merge schema to true to accommodate the two new columns.

Assess a t-sql query to return smallest value between list price and sale price using least, and return weight or quantity via coalesce when null or one if both null.

Practice your fabric analytic skills with this question on merging two tables using a full outer join to return all rows from both tables in a visual query.

vacuum the delta table to remove files not referenced by the table in the last 30 days, preserving transaction log consistency and acid properties.

Apply slowly changing dimension techniques to two dimension tables in a fabric warehouse, using SCD type 2 for customers and SCD type 1 for products to manage versioning and history.

Merge the queries by using fuzzy matching to perform an inner join between data from a fabric workspace and an Azure SQL database, ignoring spaces for minimal development effort.

Use create table schema two city as clone of schema one.city to copy the table structure while minimizing data copying in a fabric tenant.

Configure the SQL analytics endpoint settings to control automatic synchronization of Lake House tables with the Power BI semantic model, preventing new tables from being added automatically.

Create a new table named pos customers in warehouse one by querying the customer table in Lake House one using create table as select.

Use the lookup activity in Azure Data Factory to execute a stored procedure and retrieve its results for downstream pipeline activities, such as active customers and their average sales.

Use a shortcut to reference data from different workspaces without copying data, enabling you to query dbo.sales and dbo.customers in a single sql query.

Design a type two slowly changing dimension for the customer in a fabric tenant's star schema by adding surrogate key and effective start and end date and time columns.

Configure a conditional path between the two sequential activities to allow the second to run regardless of the first activity's outcome. Select 'upon completion' as the correct option.

Use the best practice analyzer in Tabular Editor to automatically detect surrogate key columns with summarize by set to anything other than none, minimizing manual inspection.

Identify the slowest DAX query in a Power BI report using Performance Analyzer, copy it to DAX Studio, and compare formula engine and storage engine timings to optimize rendering.

In a fabric tenant with a semantic model of 15 tables, use Tabular Editor to programmatically modify all columns ending with key, including hiding, nullable, and marking as key.

Explore how to create a calculation item in a Power BI semantic model to switch the context to month-to-date (MTD) using DAX and the selected measure.

In a fabric semantic model Power BI report, Performance Analyzer identifies orders by date as slow; reducing the number of visuals yields the greatest rendering improvement.

Enforce direct lake mode for a direct lake semantic model by configuring the direct lake behavior option in the model using Tabular Editor via the Xmla endpoint.

Learn how to configure object level security in a Power BI desktop semantic model using Tabular Editor to hide the address column from the Sales Region Manager role.

Replace the filter function with keep filters when filtering a single import-mode table column to preserve filters and use boolean expressions, improving execution time.

examine a semantic model with dynamic row level security by using 'select test as role' to view the report as the hr manager, ensuring data visibility matches department assignments.

View column information in a new semantic model from Power Query in a Microsoft Fabric tenant by selecting show column value distribution, enable column profile, and show column quality details.

Learn to write a T-SQL query on a sales products table using greatest to pick highest price and coalesce to select trade price from agent, wholesale, or list price.

Identify descriptive analytics by summarizing historical data with a histogram to reveal tip distributions, and distinguish it from diagnostic, predictive, and prescriptive analytics.

Learn to craft a SQL query for fabric analytics that returns the first day of the month from order_date using date_trunc('month', order_date) and the day name with date_name_week_day_order_date.

The PySpark library in a fabric notebook enables parallel processing for transforming and visualizing a 1 billion-item JSON dataset, supporting time series analysis and anomaly detection with fast load times.

Identify descriptive analytics through visualizing historical churn data with column charts comparing retained and lost customers across geography, number of products purchased, age, and customer tenure.

Learn to write a DAX expression using CALCULATE to return the total sales for the same period last year via the XMLA endpoint, and return ly_sales.

Identify the minimum and maximum values per column by enabling column profile, showing it in the details pane, and enabling the details pane within a fabric workspace data flow.

Learn how table profile in Power Query provides column statistics such as count, average, null count, distinct count, and standard deviation for data profiling.

Identify whether maintenance tasks were performed on a delta table by using describe history to track operations, since describe detail shows only file counts and sizes.

Explain computing min, max, mean, and standard deviation for string and numeric columns in a spark data frame in a fabric lake with a semantic model, using df.summary or df.describe.

Learn dax optimization for a fabric semantic model by comparing filters on related data; not is empty calculate table order item improves performance, while count rows order item >=0 fails.