Q01 - Question
In a Dataflow Gen2, you must split one source query into separate, smaller tables to create a star schema. You also have one query that is needed only for a one-time import and must not load to the destination. Which two actions should you take?
Domain: Ingest and transform data (30–35%) Type: Multiple choice
- A. Use the Data Preview pane to split the data into separate tables.
- B. Duplicate or reference the query for each table you need.
- C. Turn off Diagram view to prevent the one-time query from loading.
- D. Disable the load of the query that is needed only for the one-time import.
B and D are correct.
Explanation: In the Queries pane, you can duplicate or reference a query when you need multiple copies of the same data, such as splitting data into smaller tables for a star schema. You can also disable the load of a query when you need only a one-time import.
A is incorrect: The Data Preview pane shows only a subset of data so you can see how transformations affect it. It does not split data into tables.
C is incorrect: Diagram view shows how queries are connected and which transformations are applied. You can turn it on or off, but this does not control whether a query loads.
Q02 - Question
You are designing a data architecture using Dataflows Gen2 in Microsoft Fabric. You need to identify the limitations of Dataflows Gen2 to ensure they meet your security and architectural requirements. Which feature is unsupported in Dataflows Gen2?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Row-level security
- B. Scheduled data refreshes
- C. Integration with data pipelines
- D. Extracting data once for reuse
A is correct.
Explanation: Row-level security isn't supported in Dataflows Gen2. If analysts must see only specific rows, you need a different mechanism and cannot rely on the dataflow for this control.
B is incorrect: Dataflows can be run manually, on a refresh schedule, or as part of a data pipeline orchestration.
C is incorrect: Dataflows can be run as part of a data pipeline orchestration.
D is incorrect: Optimizing performance with dataflows enables extracting data once for reuse, reducing data refresh time for slower sources.
Q03 - Question
You must first extract and load raw data into a lakehouse. After the data is loaded, you must cleanse and transform it, and then offer the result as a curated semantic model for analysts who build reports. Which approach follows an ELT process?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Use a pipeline to extract and load the data into the lakehouse. Then create a Dataflow Gen2 that connects to the lakehouse data to cleanse and transform it.
- B. Create a Dataflow Gen2 to extract and transform the data, and then use a pipeline to load it into the lakehouse.
- C. Use a Dataflow Gen2 to extract and transform the data before it reaches any destination.
- D. Use a Dataflow Gen2 as a replacement for a data warehouse and skip the lakehouse.
A is correct.
Explanation: In an ELT process, a pipeline extracts and loads the data into a destination such as a lakehouse. A Dataflow Gen2 then connects to the lakehouse data to cleanse and transform it, and you can offer the dataflow as a curated semantic model.
B is incorrect: Extracting and transforming before loading is an ETL process, not an ELT process.
C is incorrect: Transforming before loading is an ETL order. The requirement is to load first and transform afterward.
D is incorrect: Dataflows aren't a replacement for a data warehouse, and the requirement is to load the data into a lakehouse first.
Q04 - Question
A retail company has stores in several countries. Each store provides data from a different source. The business requests a semantic model that consolidates these sources. You must make the data consistent, land the cleansed data in a destination, and avoid manually extracting and transforming data from every source. Which approach should you use?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Use a data pipeline with a Copy data activity to land the raw data directly into a semantic model.
- B. Create a Dataflow Gen2 that ingests and transforms the data, and then lands the cleansed data in a destination.
- C. Create a separate Dataflow Gen2 for each store and load them into individual semantic models.
- D. Connect reports directly to each store source and skip the transformation step.
B is correct.
Explanation: Use a Dataflow Gen2 to ingest and transform data from multiple sources and land the cleansed data in another destination. This gives you consistent data and removes the need to process each source by hand.
A is incorrect: A Copy data activity copies raw data but does not transform it to ensure consistency, and semantic models are not direct destinations for raw copy activities.
C is incorrect: Creating separate dataflows and individual semantic models does not consolidate the disparate data sources into a single semantic model as requested.
D is incorrect: Skipping transformation does not make the data consistent across stores, and it does not land cleansed data in a destination.
Q05 - Question
In a Fabric Spark notebook, you use a Spark SQL query to aggregate product counts by category. You need a bar chart with a custom figure size, custom grid lines, and rotated axis labels. The built-in chart view does not give you enough control over formatting. What should you do?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Convert the result to a Pandas dataframe by using toPandas, and then create the chart with Matplotlib.
- B. Change the results view to a chart and adjust the chart properties.
- C. Pass the Spark dataframe directly to Matplotlib without converting it.
- D. Save the result as a Parquet file, and then reload it into a Spark dataframe.
A is correct.
Explanation: When you need more control over formatting than the built-in charts provide, use a graphics package in code. Matplotlib requires a Pandas dataframe, so use the toPandas method to convert the query result before you create the chart.
B is incorrect: The built-in chart view is useful for quickly summarizing data visually. Use a graphics package when you need more control over the formatting.
C is incorrect: Matplotlib requires data in a Pandas dataframe rather than a Spark dataframe, so you must convert the result first.
D is incorrect: Saving and reloading data in Parquet format does not create a chart or give you control over chart formatting.
Q06 - Question
A Dataflow Gen2 ingests and transforms sales data and lands it in a Fabric data store. A stored procedure must run after the dataflow completes, and the data must refresh without anyone running the dataflow manually. What should you do?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Configure a refresh schedule on the Dataflow Gen2 and add a stored procedure step within the Power Query Online interface.
- B. Schedule a refresh for the dataflow and run the stored procedure separately using a Spark notebook.
- C. Create a pipeline that uses a Copy data activity to run the dataflow and the stored procedure.
- D. Add the dataflow to a pipeline, add an activity to run the stored procedure after it, and schedule the pipeline or use a trigger.
D is correct.
Explanation: Incorporate the dataflow into a pipeline to orchestrate extra activities, such as executing a stored procedure after the dataflow completes. A pipeline can be scheduled or activated by a trigger to run the dataflow, so the data refreshes without a manual run.
A is incorrect: Power Query Online in Dataflows Gen2 is used for data transformation, not for orchestrating post-load stored procedures.
B is incorrect: Scheduling the dataflow and running the stored procedure separately does not orchestrate the stored procedure to run automatically after the dataflow completes.
C is incorrect: A Copy data activity copies data; it does not execute a Dataflow Gen2 or a stored procedure.
Q07 - Question
A data engineer loads product data into a Spark dataframe named df in a Fabric notebook. Analysts need the data to persist after the Spark session ends, to be stored in the Tables storage location in Delta format, and to be queried by using SQL code in a notebook cell. Which two actions should you take?
Domain: Ingest and transform data (30–35%) Type: Multiple choice
- A. Run df.createOrReplaceTempView("products_view") to make the data available to analysts.
- B. Run df.write.format("delta").saveAsTable("products") to save the dataframe as a managed table.
- C. Query the products table by using the %%sql magic in a notebook cell.
- D. Run spark.catalog.createExternalTable over a folder in the Files storage area.
B and C are correct.
Explanation: The saveAsTable method saves the dataframe as a table in the catalog. In Microsoft Fabric, data for managed tables is stored in the Tables storage location, and Delta is the preferred format. The %%sql magic runs SQL code in a notebook cell that queries objects in the catalog, and the result set is displayed as a table.
A is incorrect: A temporary view is deleted automatically at the end of the current session, so it does not meet the persistence requirement.
D is incorrect: An external table defines metadata in the catalog but gets its data from an external storage location, typically a folder in the Files area. It does not store the data in the Tables location.
Q08 - Question
A data engineer wrote a Python script that ingests and transforms files for a lakehouse. The script uses functions defined in a separate Python code file. The process must run on a schedule without interactive use. You need to configure how Spark runs the script in Microsoft Fabric. What should you do?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Create a notebook and use markdown cells to document the script.
- B. Create a temporary view of the transformed data in the Spark catalog.
- C. Create a Spark job definition that specifies the script, the reference file, and the lakehouse.
- D. Create a custom Spark pool that has autoscale enabled.
C is correct.
Explanation: A Spark job definition runs a script on demand or on a schedule as part of an automated process. You can specify a reference file that contains functions used by the script and a reference to the lakehouse that contains the data to process.
A is incorrect: Notebooks are intended for interactive exploration and analysis. Markdown cells document code but do not define a scheduled script run.
B is incorrect: A temporary view makes dataframe data queryable in the current session and is deleted at the end of the session. It does not run a script on a schedule.
D is incorrect: A custom Spark pool defines the compute nodes. It does not define the script, the reference file, or the schedule.
Q09 - Question
A source system delivers a CSV file named product-data.csv to the Files/data folder of a lakehouse. The file has no header row. The columns are ProductID, ProductName, Category, and ListPrice. You use PySpark in a notebook to load the file into a dataframe. You need the dataframe to have these column names and data types. What should you do?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Load the file with header=True and use the first data row as the column names.
- B. Load the file, and then write the dataframe with the partitionBy method.
- C. Load the file, and then use the select method to choose the four columns.
- D. Define a StructType schema for the four columns and load the file with that schema and header=False.
D is correct.
Explanation: When a data file does not contain column names, you can specify an explicit schema. Define the columns and data types with StructType, and then pass the schema when you load the file with header=False. An explicit schema can also improve performance.
A is incorrect: Setting header=True is for files where the first row contains the column names. Your file has no header row, so a data row would be treated as the header.
B is incorrect: The partitionBy method controls how a dataframe is saved to folders. It does not define column names or types when you load a file.
C is incorrect: The select method returns a new dataframe that contains columns that already exist. It does not assign names or types to a file that has no header row.
Q10 - Question
Your team uses one Microsoft Fabric workspace for several Spark workloads. One workload needs a specific Spark runtime version and a public library from the Python Package Index (PyPI). The other workloads must keep the default configuration. You need to define the runtime and library requirements for only the first workload. What should you do?
Domain: Ingest and transform data (30–35%) Type: Single choice
- A. Change the node family of the starter pool to memory optimized nodes.
- B. Create a custom environment that specifies the Spark runtime and installs the PyPI library.
- C. Enable high concurrency mode in the workspace settings.
- D. Enable the native execution engine by setting spark.native.enabled to true.
B is correct.
Explanation: A custom environment lets you specify the Spark runtime, install public libraries from PyPI, and set the Spark pool and configuration properties. Data engineers can then select that environment for the task that needs it, and other workloads keep the default configuration.
A is incorrect: The node family sets the type of virtual machines used for the nodes. It does not set the runtime version or install libraries.
C is incorrect: High concurrency mode shares Spark sessions across multiple users or processes. It does not define a runtime or install libraries.
D is incorrect: The native execution engine can improve query performance on Parquet and Delta data. It does not select a runtime or install libraries.