Q01 - Question
An analyst has written KQL queries in a KQL queryset against tables in an Eventhouse. Operations staff need a single visual interface where each tile shows a different result from those queries and can be explored interactively after it is published. What should the analyst do?
Domain: Prepare data (45–50%) Type: Single choice
- A. Create an Activator rule for each query
- B. Add a derived stream as an eventstream destination
- C. Create a Real-Time Dashboard directly from the KQL queryset
- D. Subscribe to Fabric events in the Real-Time hub
C is correct.
Explanation: A Real-Time Dashboard pins visualizations to a single visual interface. Each tile is based on a KQL query that extracts data from tables in an eventhouse. You can create a dashboard directly from a KQL queryset. After you publish it, users can drill into the data, filter and aggregate it, and change the visualization type.
A is incorrect: Activator rules trigger actions when conditions are met. They do not provide a tiled interface for exploring query results.
B is incorrect: A derived stream is an eventstream destination that routes stream data. It does not display query results in tiles.
D is incorrect: Subscribing to Fabric events in the Real-Time hub makes system events available. It does not visualize the results of KQL queries.
Q02 - Question
An analyst queries staging.orders in a Fabric warehouse. The result must show total sales by region and year, and it must return only the region and year groups where total sales exceed 50000. Which T-SQL approach meets the requirement?
Domain: Prepare data (45–50%) Type: Single choice
- A. Add WHERE SUM(amount) > 50000 before the GROUP BY clause.
- B. Add a CASE expression that labels each order as High or Standard.
- C. Use ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date) and keep the first row of each partition.
- D. Add HAVING SUM(amount) > 50000 after the GROUP BY clause.
D is correct.
Explanation: HAVING filters groups after aggregation. Use it when you want to keep only the groups that meet a threshold, such as total sales greater than 50000.
A is incorrect: WHERE filters individual rows before grouping. It does not filter groups based on an aggregated total.
B is incorrect: A CASE expression adds a calculated column that classifies rows. It does not remove groups from the result.
C is incorrect: ROW_NUMBER is a window function that keeps every row and adds a sequence number. It does not apply a threshold to aggregated sales.
Q03 - Question
Several reports and semantic models use the same join and aggregation of fact.sales, dim.date, and dim.product in a Fabric warehouse. The calculation is a simple aggregation. Consumers must always see the current data, and the team does not want to consume extra storage. When the business changes the calculation, the team wants to update it in one place. What should you do?
Domain: Prepare data (45–50%) Type: Single choice
- A. Create a view in the warehouse that contains the join and aggregation.
- B. Create a table by using CREATE TABLE AS SELECT (CTAS) and load it once.
- C. Copy the query into each report and semantic model.
- D. Create a stored procedure that writes the results to a table each night.
A is correct.
Explanation: A view is a named SELECT statement that does not store data. It runs the underlying query each time, so it reflects the current state of the source tables and uses no extra storage. You update the view definition once, and every consumer gets the new logic.
B is incorrect: A CTAS table is a snapshot at load time and consumes storage. It does not show current data unless you reload it.
C is incorrect: Copying the query duplicates the logic. Each copy must be changed when the calculation changes.
D is incorrect: A stored procedure that writes to a table creates a stored snapshot that consumes storage. The data is current only as of the last run.
Q04 - Question
A partner team stores a folder of Delta tables in an Azure Data Lake Storage Gen2 account. You must make all of those tables appear as local tables in one schema of your Fabric lakehouse. You must not copy the data. Which option should you use?
Domain: Prepare data (45–50%) Type: Single choice
- A. Create a schema shortcut that maps the schema to the folder of Delta tables.
- B. Create a Data Factory pipeline that uses the Copy data activity to load each table into the Files folder.
- C. Select the folder and use Load to tables to create a Delta table for each item.
- D. Create a Dataflow Gen2 that imports each table by using Power Query.
A is correct.
Explanation: A schema shortcut maps an entire schema to a folder of Delta tables in Azure Data Lake Storage Gen2 or another lakehouse. All referenced tables appear as local tables within the schema. The data is referenced and not copied.
B is incorrect: The Copy data activity moves data from external sources into the lakehouse. This creates a copy, which does not meet your requirement.
C is incorrect: Load to tables creates a Delta table from a Parquet or CSV file or folder in the lakehouse. It does not reference tables in external storage without copying.
D is incorrect: Dataflows Gen2 import and transform data by using Power Query. This loads a copy of the data and does not reference the source tables in place.
Q05 - Question
A KQL database in an Eventhouse stores delivery events. Analysts run the same filtering logic in many queries. They also run frequent summary queries and want the summary results calculated ahead of time so the queries return faster. Which TWO items should you use?
Domain: Prepare data (45–50%) Type: Multi-select
- A. Update policies
- B. Materialized views
- C. Copilot for Real-Time Intelligence
- D. Stored functions
B and D are correct.
Explanation: Materialized views precalculate and store summary results for faster queries. Stored functions save frequently used query logic so you can reuse it across multiple queries. Together they address both requirements.
A is incorrect: Update policies transform incoming data and save it to different tables as it arrives. They do not store reusable query logic or precalculate summaries for faster queries.
C is incorrect: Copilot for Real-Time Intelligence helps you write queries by generating query code. It does not save reusable logic or store precalculated summary results.
Q06 - Question
A logistics team receives a continuous stream of temperature readings from sensors. Each reading has a timestamp. The team needs dashboards that show current values alongside historical trends. It also needs alerts when readings are anomalous, and it wants to use time-series functions in the query language. Which data store should the team use for the incoming data?
Domain: Prepare data (45–50%) Type: Single choice
- A. Warehouse
- B. Lakehouse
- C. SQL database in Fabric
- D. Eventhouse
D is correct.
Explanation: An eventhouse is designed for real-time analytics on streaming and time-series data. Its data is indexed and partitioned by ingestion time. KQL provides built-in time-series functions such as make-series and series_decompose_anomalies. Power BI integration supports low-latency dashboards.
A is incorrect: A warehouse handles batch and micro-batch loading. It is not purpose-built for streaming ingestion where data must be queryable within seconds.
B is incorrect: A lakehouse processes data in batch or micro-batch patterns. It is not the store built for streaming data with subsecond latency.
C is incorrect: SQL database in Fabric is for operational transactional workloads. It is not an analytical data store for streaming time-series analysis.
Q07 - Question
A finance team stores raw files in the Files area of a Fabric lakehouse. The team members work mainly in Power BI and Excel and do not write code. They must clean and reshape the data before they load it into tables. Which tool should they use?
Domain: Prepare data (45–50%) Type: Single choice
- A. A notebook that uses PySpark
- B. A Data Factory pipeline that orchestrates activities
- C. Dataflows Gen2
- D. The SQL analytics endpoint
C is correct.
Explanation: Dataflows Gen2 use the Power Query interface. They suit users who are familiar with Power BI or Excel, and you can use them to transform data and load it into the lakehouse.
A is incorrect: Notebooks suit data engineers who are familiar with programming languages such as PySpark, SQL, and Scala. This team does not write code.
B is incorrect: Pipelines provide a visual interface to orchestrate ETL processes with activities that run in sequence or in parallel. They are for orchestration and are not the Power Query experience that this team already knows.
D is incorrect: The SQL analytics endpoint is read-only. You can create views and apply SQL security, but you cannot modify the underlying data.
Q08 - Question
A sales fact table records the order date, ship date, and delivery date for each order. Analysts need to filter and group sales by each of these dates. You want to keep the model simple. Which design should you use?
Domain: Prepare data (45–50%) Type: Single choice
- A. Create three separate physical date dimension tables, one for each date
- B. Add the order date, ship date, and delivery date to a junk dimension
- C. Keep a date dimension for order date only, and store the other two dates as measures
- D. Use one date dimension as a role-playing dimension, and reference it three times from the fact table
D is correct.
Explanation: A role-playing dimension is a single dimension referenced multiple times in one fact table, each time with a different context. Each reference is a distinct role, but there is only one physical dimension table. This keeps the model simple and supports multiple analytical perspectives.
A is incorrect: Role-playing dimensions use one physical dimension table, not one per role. Separate copies add tables to maintain and are not needed.
B is incorrect: Junk dimensions consolidate small, independent, low-cardinality attributes such as flags and status values. Dates need calendar attributes and hierarchies, so they do not fit this pattern.
C is incorrect: Measures are numeric values that you aggregate. Dates are dimension keys that link to context. Storing them as measures removes the ability to group by year, quarter, or month for ship and delivery dates.
Q09 - Question
Your organization receives application log files in JSON format. Compliance requires that you keep the original files unchanged so that you can reprocess them later. Analysts also need queryable tables with enforced schemas and ACID transactions. You want to meet both requirements in one Microsoft Fabric lakehouse. Which design should you use?
Domain: Prepare data (45–50%) Type: Single choice
- A. Store the JSON files in the Files folder only, and have analysts query them directly by using the SQL analytics endpoint.
- B. Load the JSON data into the Tables folder and do not keep the original files.
- C. Store the JSON files in a separate data lake and keep only the analyst tables in the lakehouse.
- D. Store the original JSON files in the Files folder, and load the transformed results into Delta tables in the Tables folder.
D is correct.
Explanation: The Files folder keeps raw data in its native format for compliance or reprocessing. The Tables folder holds Delta Lake tables that enforce schemas and support ACID transactions. You can keep both in the same lakehouse and load processed results from files into tables.
A is incorrect: Files in the Files folder do not enforce a schema and do not support direct SQL queries. Analysts cannot get schema enforcement or ACID transactions from files.
B is incorrect: Loading the data only into tables leaves no copy of the original files. You then cannot meet the requirement to reprocess the original data.
C is incorrect: A separate data lake adds a second system for raw data. A single lakehouse can hold both the raw files and the structured tables.
Q10 - Question
A data engineer uses the Real-Time hub and wants to subscribe to system-generated events in Fabric, such as job status changes and changes to workspace items. Which category should the engineer use?
Domain: Prepare data (45–50%) Type: Single choice
- A. Data sources
- B. Azure sources
- C. Azure events
- D. Fabric events
D is correct.
Explanation: The Fabric events category in the Real-Time hub lets you subscribe to system-generated events in Fabric. Examples include job status changes, events from actions on files or folders in OneLake, and Fabric workspace item changes.
A is incorrect: Data sources is for browsing and connecting to streaming data sources, such as Microsoft sources, database change data capture feeds, and external sources. It is not the category for Fabric system events.
B is incorrect: Azure sources is for discovering and configuring Azure streaming data sources such as Azure IoT Hub and Azure Service Bus. It does not cover Fabric job or workspace events.
C is incorrect: Azure events is for subscribing to system events from Azure services, such as actions on files or folders in Azure Blob Storage. It does not cover Fabric events.