Courseiva

CCNA Prepare Data Questions

75 of 101 questions · Page 1/2 · Prepare Data · Answers revealed

1
Multi-Selectmedium

You are developing a data pipeline in Microsoft Fabric. You need to choose between using a Shortcut and the 'Copy activity' to bring data from an existing Azure Data Lake Storage (ADLS) Gen2 account into your Fabric Lakehouse. What are THREE primary advantages of using Shortcuts over the Copy activity? (Select THREE)

Select 3 answers
A.Data remains in its original source location without being duplicated.
B.You can apply V-Order optimization to the source files through the Shortcut.
C.There is no need to manage or schedule data refresh pipelines for the Shortcut.
D.Shortcuts reduce the cost of storage in the Fabric capacity.
E.Shortcuts automatically convert CSV files into the Delta Lake format.
AnswersA, C, D

Shortcuts create a pointer to the external data source rather than physically moving the bits into Fabric's managed storage. This avoids data duplication, which simplifies data governance and ensures that there is a single source of truth across different platforms and cloud environments.

Why this answer

Shortcuts in Microsoft Fabric provide a way to virtualize data without moving or duplicating it. This approach reduces storage costs, ensures data is always up-to-date by pointing directly to the source, and eliminates the need to manage complex ETL pipelines for simple data access, facilitating a more agile data architecture.

Exam trap

Candidates often include 'increased security' or 'faster data processing' as advantages. Shortcuts are about virtualization and cost-saving, not necessarily about increasing raw processing speed over a copy.

2
MCQmedium

You are using a Fabric Data Factory pipeline to copy data from an on-premises Oracle database to a Lakehouse. The pipeline must run every night and only copy rows that have changed since the last run. Which configuration should you use to achieve incremental data loading?

A.Use a tumbling window trigger and set the concurrency to 1.
B.Enable the 'Enable incremental copy' option in the Copy activity settings.
C.Configure the source dataset to use the 'Change Data Capture' feature of the Oracle connector.
D.Use a watermark column in the source and configure the Copy activity to filter rows based on the last watermark value stored in a control table.
AnswerD

Incremental loading with a watermark column involves tracking the last loaded value (e.g., a timestamp or ID) and using it to filter new or changed rows. Storing the watermark in a control table allows the pipeline to retrieve the last value, copy only newer rows, and then update the watermark. This is a standard pattern in Microsoft Fabric Data Factory and meets the requirement to copy only changed rows.

Why this answer

Using a watermark column is the standard method for incremental loading in Fabric Data Factory. By storing the last watermark value in a control table, the pipeline can filter source rows to only those changed since the last run. This minimizes data transfer and processing.

Other options like a built-in incremental copy setting or CDC are not available or require complex setup, and tumbling window triggers alone do not filter data.

Exam trap

The trap here is assuming the Copy activity has a built-in incremental copy option; it does not, so you must implement watermark logic manually.

3
Multi-Selecthard

You are optimizing a Spark Notebook that processes large Parquet files in a Fabric Lakehouse. You want to ensure that the data is stored in a way that maximizes read performance for the SQL analytics endpoint. Which THREE features or techniques should you utilize? (Select THREE)

Select 3 answers
A.V-Order
B.Partitioning
C.Z-Order
D.Convert to CSV format
E.Disable Delta Logs
AnswersA, B, C

V-Order is a proprietary optimization technique in Microsoft Fabric that rearranges the data within Parquet files. This allows the Fabric SQL engine and Spark to read the data much faster by optimizing compression and data distribution, making it a critical step for high-performance analytical workloads in the Lakehouse.

Why this answer

V-Order is a Fabric-specific optimization for Parquet files that improves read speed. Partitioning organizes data into folders based on column values to prune data during reads. Z-Order is a technique used within Delta tables to colocate related information in the same files, further enhancing the efficiency of filter operations in the SQL analytics endpoint.

Exam trap

Candidates often select 'Indexing' or 'Clustering' as valid options. While these exist in other systems, Fabric specifically relies on V-Order, Partitioning, and Z-Order for Lakehouse optimization.

4
Multi-Selecthard

You are optimizing a large Delta table in a Microsoft Fabric Lakehouse. Users report slow query performance due to a high volume of small files and unsorted data across frequently filtered columns. Which TWO actions should you perform? Each correct answer presents part of the solution.

Select 2 answers
A.Execute the OPTIMIZE table_name ZORDER BY (column_name) command in a Spark notebook.
B.Run the VACUUM table_name RETAIN 0 HOURS command to instantly purge all historical file versions.
C.Configure partitioning on every column that has high cardinality to maximize pruning efficiency.
D.Enable predictive optimization on the workspace to automate file compaction and maintenance tasks.
E.Alter the table properties to increase the default checkpoint interval to every 500 commits.
AnswersA, D

OPTIMIZE with ZORDER BY compacts the many small files into larger ones and co-locates rows sharing similar values in the specified columns. This data skipping lets queries on frequently filtered columns bypass irrelevant files, cutting I/O and improving scan performance.

Why this answer

Option A is correct because running OPTIMIZE table_name ZORDER BY (column_name) in a Spark notebook compacts the many small files into larger ones and co-locates related data by the frequently filtered columns, which directly addresses both the small-file and unsorted-data problems and improves data skipping. Option D is correct because enabling predictive optimization on the workspace lets Microsoft Fabric automatically run maintenance such as file compaction (OPTIMIZE) and other housekeeping tasks, reducing manual effort while keeping the Delta table healthy. Option B is wrong because VACUUM with RETAIN 0 HOURS removes all historical file versions, breaking time travel and risking readers that still reference older files, and it does not fix small files or sorting.

Option C is wrong because partitioning on every high-cardinality column creates an excessive number of small partitions and directories, which worsens the small-file problem rather than solving it. Option E is wrong because increasing the checkpoint interval to every 500 commits does not compact small files or sort data and can actually slow down transaction log reads and recovery.

Exam trap

The trap is thinking that partitioning on high-cardinality columns improves performance, when it actually creates too many small partitions; also, VACUUM with RETAIN 0 is a dangerous shortcut that candidates might choose for immediate space reclamation.

5
MCQhard

You are optimizing a Spark job in Fabric that processes large Parquet files. You notice high memory consumption and frequent spills to disk. Which TWO actions should you take to improve performance?

A.Decrease the number of Spark partitions
B.Increase the number of Spark partitions
C.Enable predicate pushdown
D.Increase the executor memory size
E.Use the default Spark configuration without changes
AnswerB, C

Increasing the number of partitions breaks down the data into smaller, more manageable units. This ensures that each executor is not overwhelmed by the volume of data it needs to process at once, effectively preventing memory overflow and the subsequent expensive spilling to disk during large transformations.

Why this answer

Increasing the number of Spark partitions (B) reduces the amount of data each task processes, lowering per-task memory pressure and reducing spills. Enabling predicate pushdown (C) allows Spark to push filter conditions down to the Parquet reader so fewer rows/row-groups are read into memory, cutting both I/O and memory footprint. Together these address the root cause of high memory consumption and disk spills without simply throwing hardware at the problem.

Exam trap

The trap is assuming 'more memory always fixes spills' — candidates pick D because it sounds intuitive, but the exam wants you to recognize that partition sizing and predicate pushdown address the root cause, while adding memory only masks it.

How to eliminate wrong answers

Option A is wrong because decreasing partitions makes each partition larger, increasing per-task memory pressure and worsening spills — the opposite of what is needed. Option D is wrong because increasing executor memory may mask the symptom but does not address the underlying skew/partition-size problem, and it is not one of the two best-practice actions for spill reduction. Option E is wrong because leaving the default Spark configuration unchanged will not resolve the issue — defaults are rarely tuned for large Parquet workloads and often produce too few or too many partitions depending on file layout.

6
MCQmedium

You are cleaning a dataset in a Fabric Notebook. You notice that your dataframe contains significant null values in the 'customer_id' column and you must remove these rows. Which PySpark command effectively achieves this filtering while maintaining dataframe immutability?

A.df.remove_null('customer_id')
B.df.filter(df['customer_id'] != None)
C.df.dropna(subset=['customer_id'])
D.df.clear('customer_id')
AnswerC

The dropna() method is the standard and most efficient way to remove rows containing nulls in a Spark dataframe. By specifying the subset argument, you ensure that only the rows missing a value in the 'customer_id' column are discarded, preserving the rest of your valuable dataset.

Why this answer

df.dropna(subset=['customer_id']) removes rows where the specified column is null and returns a new dataframe, preserving immutability. PySpark dataframes are immutable by design, so any filtering operation returns a new dataframe rather than modifying the original in place. This is the idiomatic PySpark method for dropping null rows in a specific column.

Exam trap

The trap is that df.filter(df['customer_id'] != None) looks like valid Python, but Spark's null semantics make it a no-op — candidates who don't know SQL three-valued logic pick B.

How to eliminate wrong answers

Option A is wrong because df.remove_null() is not a valid PySpark DataFrame method — no such API exists. Option B is wrong because comparing a column to Python's None using != does not correctly filter nulls in Spark; null semantics mean null != None evaluates to null, not true, so rows are not removed as expected. Option D is wrong because df.clear() is not a PySpark DataFrame method and would not filter rows.

7
MCQhard

You are optimizing a Spark job in Fabric that processes large volumes of telemetry data. The job is failing due to memory overhead during a join operation on high-cardinality columns. Which TWO actions should you take to improve performance?

A.Increase the number of partitions for the join operation using repartition().
B.Use broadcast join hints for the smaller dimension table.
C.Implement salting on the join key to resolve data skew.
D.Convert the Spark DataFrames to Pandas objects for faster local processing.
E.Increase the driver node memory allocation in the Spark session configuration.
AnswerB, C

Broadcast joins eliminate the need for a shuffle phase by sending a copy of the smaller table to every executor. This significantly improves performance for joins involving large fact tables and smaller dimension tables, as it prevents the overhead of moving large data volumes across the network during execution.

Why this answer

Optimizing join operations in Spark requires addressing memory pressure and data distribution. Broadcast joins are ideal for small tables to avoid shuffling, while salting keys helps distribute data evenly when skewed partitions exist. These techniques reduce the risk of OOM errors by preventing individual executors from becoming overwhelmed by large partitions or excessive data movement across the network during shuffle stages.

Exam trap

Candidates often select cache mechanisms or scaling up cluster sizes instead of addressing partition skews and join strategies, which directly cause out-of-memory errors during shuffles.

8
MCQeasy

When preparing data in a Lakehouse, you need to enforce a schema on incoming JSON data before loading it into a Delta table. Which Fabric tool allows you to define and apply a structural schema programmatically?

A.Shortcut
B.Spark Notebook
C.SQL Analytics Endpoint
D.Mirroring
AnswerB

Spark notebooks allow for granular schema definition using PySpark APIs. By using 'spark.read.schema(schema_definition).json(path)', you can explicitly enforce data types, preventing schema evolution issues. This is a critical step in data preparation to ensure downstream analytical models receive clean, reliable, and predictable data structures.

Why this answer

Spark notebooks in Fabric are the ideal tool for enforcing schemas. By defining a StructType schema in PySpark, you can explicitly map JSON fields to data types, handling missing or malformed data during the read process. This proactive approach ensures that Delta tables maintain high data quality and consistency, preventing type errors during query execution or report creation later in the lifecycle.

Exam trap

Candidates frequently choose graphical Dataflows or Data Pipelines for complex programmatic schema definitions, overlooking the advanced StructType programming capabilities available in Spark notebooks.

9
MCQeasy

You are creating a Fabric notebook to load data from a CSV file stored in the Lakehouse Files section into a Delta table. You want to use Spark to read the CSV and write it as a Delta table, ensuring that the schema is inferred correctly. Which code snippet should you use?

A.df = spark.read.format('csv').load('Files/data.csv'); df.write.format('delta').mode('append').save('/Tables/mytable')
B.df = spark.read.csv('Files/data.csv', header=True, schema='auto'); df.write.mode('overwrite').parquet('/Tables/mytable')
C.df = spark.sql('SELECT * FROM csv.`Files/data.csv`'); df.write.format('delta').insertInto('mytable')
D.df = spark.read.format('csv').option('header', 'true').option('inferSchema', 'true').load('Files/data.csv'); df.write.format('delta').saveAsTable('mytable')
AnswerD

This code reads the CSV with header and schema inference, then writes it as a Delta table using saveAsTable. The header option ensures the first row is used as column names, and inferSchema automatically detects data types. This is the correct approach for loading CSV into a Delta table in a Fabric notebook.

Why this answer

To correctly load a CSV with headers and inferred schema into a Delta table, you must set the header and inferSchema options when reading, and use saveAsTable to create a managed Delta table. The other options either lack proper schema inference, write in the wrong format, or assume an existing table. This approach ensures the table is created with correct column names and data types.

Exam trap

The trap here is overlooking the need for both header and inferSchema options, or confusing writing to a path with creating a managed table.

10
MCQmedium

You are designing a data ingestion pipeline in Microsoft Fabric. You need to land raw CSV files from an external source into the Lakehouse. Which storage location should you use to ensure the data is optimized for delta table conversion and subsequent Lakehouse processing?

A.The Tables folder
B.An Azure SQL Database
C.The Files folder
D.The Semantic Model workspace
AnswerC

The Files folder in a Fabric Lakehouse is specifically architected to store unstructured and semi-structured data. It provides the necessary interface for Spark jobs to read raw CSVs and write them out as Delta tables, which are required for high-performance querying within the Lakehouse SQL analytics endpoint.

Why this answer

The Files section of a Microsoft Fabric Lakehouse is the designated landing zone for raw, unstructured, or semi-structured data like CSVs. By landing files here, you can leverage Spark notebooks or Dataflows to perform ELT operations, enabling the transformation of raw files into high-performance Delta tables. This workflow is central to the Medallion architecture, ensuring data quality and lineage throughout the Fabric data lifecycle.

Exam trap

Candidates often select the Tables folder instead of Files, assuming structured tables should house raw CSVs, ignoring that raw un-ingested files must land in the Files landing zone first.

11
MCQhard

Refer to the exhibit. You are reviewing the M-code for a Dataflow Gen2. To ensure that the 'FilteredRows' step is executed on the source SQL Server rather than in the Fabric Mashup engine, what concept must be maintained?

A.Data Source Privacy Levels
B.Schema Drift
C.Query Folding
D.Fast Copy
AnswerC

Query folding is the process where the Power Query engine translates transformation steps into a single query sent to the data source. In this exhibit, filtering by date and removing columns are standard operations that SQL Server can perform, so the engine 'folds' these steps to optimize performance.

Why this answer

Query folding is the ability of Power Query to translate M-code into the native language of the source system, such as T-SQL. When folding occurs, the source system handles the filtering and column selection, which is much more efficient than transferring all data to Fabric for processing. Maintaining folding is critical for performance.

Exam trap

Candidates often confuse 'Query Folding' with 'Dataflow performance' generally, failing to realize that adding certain non-foldable transformation steps (like custom M functions) silently breaks the folding process, forcing local processing.

12
MCQhard

Refer to the exhibit. You are reviewing a configuration for a maintenance task on a Delta table within a Microsoft Fabric Lakehouse. What is the primary benefit of ensuring the 'v_order' property is set to true during this operation?

A.It enables ACID transactions for concurrent users.
B.It implements Liquid Clustering for dynamic data layout.
C.It optimizes Parquet files for faster read performance by Fabric engines.
D.It automatically evolves the schema when new columns are detected.
AnswerC

V-Order applies a specialized sorting and encoding algorithm to Parquet files during the write operation. This makes the files highly compatible with the Fabric compute engines, particularly the SQL engine and Power BI Direct Lake mode, significantly reducing the time required to scan and filter data during queries.

Why this answer

Setting `v_order = true` on a Delta table write triggers the V-Order optimization, which physically reorders and compresses Parquet data so that Microsoft Fabric engines (Power BI, SQL, Spark) can read it faster. It is a write-time layout optimization specific to Fabric's Delta/Parquet format, improving read performance for downstream consumers.

Exam trap

DP-600 often tests confusion between write-time layout optimizations (V-Order) and table-level features (Liquid Clustering, ACID, schema evolution), causing candidates to pick a feature that sounds related but operates at a different layer.

How to eliminate wrong answers

Option A is wrong because ACID transaction support is inherent to the Delta Lake format itself (via the transaction log), not something enabled by the v_order property. Option B is wrong because Liquid Clustering is a separate Delta feature configured via CLUSTER BY, unrelated to the v_order write option. Option D is wrong because schema evolution is controlled by mergeSchema/autoMerge, not by v_order, which only affects physical file layout.

13
Multi-Selecthard

You are using a Fabric Dataflow Gen2 to ingest data from a REST API that returns JSON. The JSON contains nested arrays and objects. You need to flatten the nested structures into a tabular format for loading into a Lakehouse. Which two actions should you perform in the Dataflow Gen2 query? (Choose two.)

Select 2 answers
A.Use the 'Transpose' transformation to swap rows and columns.
B.Use the 'Parse JSON' transformation to convert the JSON string into a record.
C.Use the 'Group By' transformation to aggregate nested fields.
D.Use the 'Expand' option on the column containing the nested records to create new columns for each field.
E.Use the 'Expand' option on the column containing the nested list to create new rows for each element.
AnswersD, E

Expanding a column that contains records or lists in Dataflow Gen2 allows you to extract the nested fields into separate columns. This is the primary method to flatten nested JSON structures into a tabular format, making the data suitable for loading into a Lakehouse table.

Why this answer

To flatten nested JSON in Dataflow Gen2, you need to expand both records and lists. Expanding a record column extracts its fields into new columns, while expanding a list column creates new rows for each element. These two actions transform the nested structure into a relational table.

Other transformations like Parse JSON, Transpose, or Group By do not address the need to unnest the data.

Exam trap

The trap here is confusing 'Parse JSON' with expansion; Parse JSON is only for converting a JSON string to a record, not for flattening already-parsed nested structures.

14
MCQhard

Refer to the exhibit. You are implementing fine-grained access control in a Fabric Lakehouse using OneLake security roles. The exhibit shows a policy applied to a service account. What is the result when 'ServiceAccount_A' attempts to query the 'Sales' table?

A.The query succeeds if the user is a Workspace Admin.
B.The query fails with an Access Denied error.
C.The query succeeds if the account has write access.
D.The query succeeds if the table is cached in memory.
AnswerB

The policy explicitly specifies a DENY action for the READ operation on the Sales table resource. In the Fabric security evaluation engine, a DENY rule overrides any existing ALLOW rules, resulting in an immediate Access Denied error when the ServiceAccount_A attempts to execute a read query.

Why this answer

OneLake security roles in Microsoft Fabric enforce row-level and column-level access at the data plane, independent of workspace roles. If the policy applied to ServiceAccount_A does not grant access to the Sales table (or explicitly denies it), the query fails with an Access Denied error regardless of workspace role. Workspace Admin privileges do not bypass OneLake security role enforcement for data queries.

Exam trap

DP-600 often tests whether candidates assume Workspace Admin or item-level permissions override OneLake security roles — they do not, because OneLake enforces data-plane authorization independently of control-plane roles.

How to eliminate wrong answers

Option A is wrong because Workspace Admin is a control-plane role for managing items, not a data-plane bypass — OneLake security roles are evaluated separately and still deny access. Option C is wrong because write access to the item does not imply read access to the table's rows under OneLake security; the role policy governs read visibility. Option D is wrong because caching has no bearing on authorization — access checks occur before any data is returned, so a cached table does not grant access.

15
MCQmedium

You are using a Microsoft Fabric Notebook to process a large dataset stored in a Lakehouse. You need to ensure that the Spark job uses the most efficient method to handle many small files that have accumulated in the Delta table over time. Which operation should you perform?

A.Run the VACUUM command on the table.
B.Execute the OPTIMIZE command on the table.
C.Change the table's partition scheme to a more granular column.
D.Set the 'spark.sql.shuffle.partitions' to a higher value.
AnswerB

OPTIMIZE is a Delta Lake command that performs file compaction. It takes numerous small files and rewrites them into a smaller number of larger files, typically targeting a size of 1GB. This reduces the overhead for the Spark engine during file discovery and improves overall query execution speed.

Why this answer

Small files can significantly degrade Spark performance due to excessive metadata overhead and inefficient I/O. The OPTIMIZE command in Delta Lake compacts these small files into larger, more efficient Parquet files. This process improves query performance and is a standard maintenance task for managing data health within a Fabric Lakehouse.

Exam trap

Candidates often suggest 'repartitioning' or 'vacuuming'. While helpful, the specific command to address the overhead of many small files in Delta tables is the 'OPTIMIZE' command.

16
MCQmedium

You are building a medallion architecture in a Microsoft Fabric Lakehouse. Raw CSV files land daily in the Files section of the lakehouse. You need to ingest these files into a Delta table in the Bronze layer while ensuring schema evolution is automatically handled if new columns appear in the source files. Which command should you use in a PySpark notebook?

A.df.write.format('parquet').mode('overwrite').saveAsTable('bronze_sales')
B.df.write.format('delta').mode('append').option('mergeSchema', 'true').saveAsTable('bronze_sales')
C.df.write.format('delta').mode('errorIfExists').saveAsTable('bronze_sales')
D.df.write.format('csv').mode('append').saveAsTable('bronze_sales')
AnswerB

Delta Lake provides native support for schema evolution through the mergeSchema option during write operations. Appending incoming data while enabling this option automatically updates the target Delta table metadata to include new columns discovered in the source files without failing the pipeline execution.

Why this answer

Writing with format('delta'), mode('append'), and option('mergeSchema','true') is the correct combination: Delta Lake is the required format for a Fabric Lakehouse table, append mode preserves existing data across daily loads, and mergeSchema=true enables automatic schema evolution when new columns appear in the source. This is the standard Bronze-layer ingestion pattern in a medallion architecture.

Exam trap

The trap is confusing Delta with Parquet — candidates pick A because Parquet is a columnar format and sounds efficient, but Fabric Lakehouse managed tables require Delta, and only Delta supports mergeSchema for automatic schema evolution.

How to eliminate wrong answers

Option A is wrong because it writes Parquet format, not Delta — Fabric Lakehouse tables must be Delta to support ACID transactions, time travel, and schema evolution; Parquet tables in the lakehouse are not managed Delta tables. Option C is wrong because mode('errorIfExists') will fail the job if the table already exists, which breaks daily incremental ingestion, and it does not enable schema evolution. Option D is wrong because writing CSV format to a lakehouse table is not supported for managed Delta tables and provides no schema evolution, no ACID guarantees, and poor query performance.

17
MCQmedium

You are building a Fabric Dataflow Gen2 to ingest data from an on-premises SQL Server. The source table contains a column named 'OrderDate' stored as datetime2. In the Power Query editor, you need to convert this column to a date type and ensure that the resulting Delta table in the Lakehouse stores it as a date. Which transformation should you apply?

A.Change the data type of the column to Date using the Transform tab.
B.Use the 'Date' column from the Add Column tab, selecting only the date part.
C.Use the 'Transform' tab and select 'Date' under the 'Time' section.
D.Apply a custom column with the formula Date.From([OrderDate]).
AnswerA

Changing the data type to Date in Power Query converts the datetime2 values to date, removing the time portion. When the dataflow writes to a Lakehouse Delta table, the column will be stored as a date type, which is the desired outcome for this scenario.

Why this answer

To convert a datetime column to a date type in Power Query, you change the data type to Date. This operation removes the time component and ensures that when the data is written to a Lakehouse Delta table, the column is stored as a date. Other options either create a new column without altering the original or perform incorrect transformations.

Exam trap

The trap here is confusing extracting the date part with changing the data type, which leaves the original column unchanged and still a datetime.

18
MCQmedium

You have a Fabric Lakehouse with a Delta table named Sales. The table contains a column 'OrderDate' of type timestamp. You need to create a new table that contains only sales from the last 30 days, and you want to minimize storage and compute. What should you do?

A.Use the Copy activity in a pipeline to copy the entire Sales table to a new table, then delete older rows.
B.Create a view in the Lakehouse that filters the Sales table for the last 30 days.
C.Use a Dataflow Gen2 to load the Sales table, filter for the last 30 days, and output to a new Lakehouse table.
D.Use a Spark notebook to read the Sales table, filter for the last 30 days, and write the result to a new Delta table using overwrite mode.
AnswerD

Filtering the data and writing to a new Delta table with overwrite mode ensures only the required data is stored, minimizing storage. This approach uses Spark compute but is efficient because it processes only the filtered subset. It creates a separate table, which is appropriate for a snapshot of recent sales without affecting the original table.

Why this answer

Using a Spark notebook to filter and write a new Delta table with overwrite mode is the most efficient way to create a persistent table with only recent data. It processes only the necessary rows, reducing compute, and stores only the filtered data, minimizing storage. This is a common pattern in Fabric Lakehouses.

Exam trap

The trap here is assuming that a view can satisfy the requirement for a new table and minimize compute, but views do not store data and recompute on each access.

19
MCQeasy

You are creating a Dataflow Gen2 to ingest data from an Azure SQL Database into a Fabric Lakehouse. You need to ensure that only rows where the Status column equals 'Active' are loaded. Which transformation should you apply?

A.Use the 'Remove Rows' transformation and specify to remove rows where Status is not 'Active'.
B.Use the 'Conditional Column' transformation to add a flag for active rows and then filter on that flag.
C.Use the 'Group By' transformation on the Status column and then select the 'Active' group.
D.Use the 'Filter Rows' transformation with a condition Status equals 'Active'.
AnswerD

The 'Filter Rows' transformation is designed to include only rows that meet a specified condition. By setting the condition to Status equals 'Active', you ensure that only active records are loaded. This is the most direct and efficient way to filter data in Dataflow Gen2. It reduces the data volume early in the pipeline, improving performance.

Why this answer

The 'Filter Rows' transformation is the correct choice because it allows you to specify a condition to keep only rows that match. In this scenario, filtering on Status equals 'Active' ensures that only active records are loaded into the Lakehouse. This transformation is simple, efficient, and preserves the original row structure.

It is a fundamental data preparation step in Dataflow Gen2.

Exam trap

The trap here is overcomplicating the solution by using multiple transformations or aggregations when a simple filter is sufficient.

20
MCQmedium

You are using a Fabric Dataflow Gen2 to transform data from an Azure SQL Database. The source table has a column 'OrderDate' of type datetime2. You need to extract the year and month from this column and create a new column 'YearMonth' in the format 'yyyy-MM'. Which transformation should you use?

A.Add a custom column with the formula Text.Start([OrderDate], 7)
B.Use the 'Date' transformation and select 'Year' and 'Month' separately, then concatenate.
C.Use the 'Extract' transformation to extract the first 7 characters.
D.Add a custom column with the formula Date.ToText([OrderDate], "yyyy-MM")
AnswerD

The Date.ToText function in Power Query M converts a date/datetime value to a text string using the specified format. Using "yyyy-MM" produces the desired 'YearMonth' string. This is the correct approach to extract and format year and month from a datetime column in Dataflow Gen2.

Why this answer

Date.ToText with a format string is the most direct and reliable way to create a 'yyyy-MM' formatted column from a datetime column in Dataflow Gen2. Other options either fail due to type mismatches or require unnecessary extra steps.

Exam trap

The trap here is using text functions on a datetime column without converting it first, which leads to errors or incorrect results.

21
MCQmedium

You are using Fabric to ingest data. You notice that your data ingestion job is taking too long to complete. You want to parallelize the data load. Which strategy is most effective?

A.Reduce the executor count to save memory.
B.Use multiple concurrent copy activities in a Fabric Pipeline.
C.Force single-threaded execution to ensure data consistency.
D.Increase the timeout settings for the copy activity.
AnswerB

Running multiple copy activities in parallel allows for full utilization of the available compute and network bandwidth. This strategy is highly effective for large-scale migrations where the source system can handle multiple connections, drastically reducing the overall time required to move data from the source to the Fabric Lakehouse storage layer.

Why this answer

To parallelize data ingestion effectively in Microsoft Fabric when dealing with pipeline-based copy activities, you can use multiple concurrent copy activities or partition the copy source. Option B directly addresses parallelizing data loads within Fabric Pipelines.

Exam trap

Distractors may suggest reducing resources or timeouts, which either slow down the job or do nothing to address the root cause of sequential processing bottlenecks.

22
MCQeasy

You are creating a Fabric pipeline that copies data from an on-premises Oracle database to a Lakehouse. The pipeline must run daily and only copy rows that have been modified since the last run. You need to configure the source to support incremental copying. What should you do?

A.Use a watermark column in the source table and configure the Copy activity to use a query with a WHERE clause based on the last run timestamp.
B.Set the Copy activity's 'Enable staging' property to true and use a staging area to compare data.
C.Enable the 'Change data capture' option in the Oracle source settings of the Copy activity.
D.Configure the Oracle source to use the 'PolyBase' option for incremental loads.
AnswerA

Using a watermark column (such as LastModifiedDate) is the standard approach for incremental copying. You can configure the Copy activity to run a query that filters rows where the watermark column is greater than the last successful run time. This requires storing the last run time, often in a control table or pipeline variable. This method is supported in Fabric pipelines and works with Oracle sources.

Why this answer

Incremental copying in Fabric pipelines is typically implemented using a watermark column. You store the last successful run timestamp and use it in the source query's WHERE clause to fetch only new or modified rows. This method is efficient and supported for various sources, including Oracle.

Other options are either not available or do not provide incremental functionality.

Exam trap

The trap here is assuming that the Copy activity has a built-in CDC feature for all sources, which it does not for Oracle in Fabric.

23
MCQhard

You are loading a large CSV file from OneLake into a Delta table in a Fabric Lakehouse using a Spark notebook. The CSV contains a column 'TransactionDate' with values in the format 'MM/dd/yyyy'. After loading, you notice that the dates are stored as strings. You need to convert this column to a proper date type and then write the table. Which PySpark code snippet should you use?

A.df = df.withColumn('TransactionDate', to_date(col('TransactionDate'), 'MM/dd/yyyy'))
B.df = df.withColumn('TransactionDate', date_format(col('TransactionDate'), 'MM/dd/yyyy'))
C.df = df.withColumn('TransactionDate', to_timestamp(col('TransactionDate'), 'MM/dd/yyyy'))
D.df = df.withColumn('TransactionDate', cast(col('TransactionDate'), 'date'))
AnswerA

The to_date function with a format string 'MM/dd/yyyy' correctly parses the string into a date type. This is the standard way to convert string dates in Spark. After this transformation, the column will be of DateType, and you can write it to a Delta table. This approach handles the specific format and ensures proper data typing.

Why this answer

The correct function to convert a string in a specific format to a date type is to_date with the format string. Casting without a format will fail for non-standard formats, and date_format and to_timestamp serve different purposes. Using to_date ensures the column is properly typed as DateType.

Exam trap

The trap here is assuming that cast to date will automatically interpret the string format, when it only works for the default ISO format.

24
MCQhard

You are creating a Dataflow Gen2 to ingest data from an on-premises Oracle database into a Fabric Lakehouse. The data volume is large, and you need to ensure that the dataflow uses the most efficient query folding. You notice that a custom column step is preventing folding. Which action should you take to restore query folding?

A.Replace the custom column with a native transformation that can be folded, such as using the 'Add column' from existing columns with supported operations.
B.Use the 'Table.Buffer' function in the custom column to cache the data and allow folding.
C.Add an index column before the custom column step to improve performance.
D.Disable query folding by setting the dataflow to 'Import' mode instead of 'DirectQuery'.
AnswerA

Query folding relies on translating transformations into the source's native query language. Custom columns often use expressions that cannot be translated, breaking folding. By replacing them with native transformations that use supported operations (e.g., simple arithmetic, concatenation), the Mashup Engine can fold the step back to the Oracle source. This reduces data transfer and improves performance. It is the recommended approach to maintain folding when possible.

Why this answer

Query folding is critical for efficient data ingestion in Dataflow Gen2. Custom columns often use M expressions that cannot be translated to the source's SQL, breaking the fold. Replacing them with native transformations that use foldable operations restores folding, allowing the source to perform the heavy lifting.

This minimizes data movement and leverages the database's processing power. Other options either disable folding or introduce non-foldable steps.

Exam trap

The trap here is assuming that any transformation can be folded or that buffering helps folding, when it actually breaks it.

25
MCQeasy

You are using a Fabric notebook to load a Parquet file from the Lakehouse Files section into a Spark DataFrame. You need to ensure that the schema is inferred correctly and that the data is read efficiently. Which PySpark code should you use?

A.spark.read.format('parquet').load('Files/data/file.parquet')
B.spark.read.csv('Files/data/file.parquet', header=True, inferSchema=True)
C.spark.read.parquet('Files/data/file.parquet').schema()
D.spark.sql('SELECT * FROM parquet.`Files/data/file.parquet`')
AnswerA

This code uses the Spark DataFrameReader to read a Parquet file from the Lakehouse Files path. Parquet is a columnar format, and Spark automatically infers the schema. This is the standard and efficient way to load Parquet data in a Fabric notebook.

Why this answer

The correct way to read a Parquet file in a Fabric notebook is using spark.read.format('parquet').load(path). This leverages Spark's native Parquet support, infers the schema automatically, and reads the data efficiently.

Exam trap

The trap here is using the wrong reader for the file format, such as spark.read.csv for Parquet, or misusing the API by calling .schema() on the reader instead of the DataFrame.

26
MCQhard

You are monitoring a pipeline execution in Fabric. You notice that the 'Copy' activity is taking longer than expected. Which metric should you investigate to identify if the bottleneck is related to data movement throughput?

A.CPU utilization on the driver node.
B.Data read and Data written metrics.
C.Memory usage of the Lakehouse.
D.Number of active users in the workspace.
AnswerB

These metrics are the primary indicators of movement throughput. By comparing the volume of data moved against the time taken, you can determine if the pipeline is achieving the expected transfer rates. If these metrics show low throughput, you can adjust settings like degree of copy parallelism to improve performance.

Why this answer

The Data read and Data written metrics in the Copy activity's monitoring view directly reflect the volume and throughput of data movement. If the Copy activity is slow, these metrics reveal whether the bottleneck is in reading from the source or writing to the destination, making them the right diagnostic.

Exam trap

DP-600 often tests whether candidates can distinguish pipeline-level data movement metrics (Data read/written) from compute or workspace-level metrics (CPU, memory, users) when diagnosing Copy activity performance.

How to eliminate wrong answers

Option A is wrong because driver node CPU utilization relates to Spark compute, not the Copy activity's data movement throughput. Option C is wrong because Lakehouse memory usage is a storage/compute resource metric, not a measure of Copy activity throughput. Option D is wrong because active workspace users have no bearing on a single Copy activity's performance.

27
Multi-Selecthard

You are optimizing a Lakehouse for high-concurrency read operations from Power BI. You want to ensure that the Delta tables are in the best possible state for Direct Lake mode. Which THREE actions should you take during the 'Prepare Data' phase? (Select THREE)

Select 3 answers
A.Enable V-Order on all Delta tables during the write process.
B.Convert all Delta tables into the CSV format for better compatibility.
C.Run the OPTIMIZE command to compact small files.
D.Ensure that the tables are partitioned on high-cardinality columns.
E.Regularly update table statistics using the ANALYZE command.
AnswersA, C, E

V-Order is a specialized optimization that sorts and encodes data within Parquet files specifically for the Fabric and Power BI engines. Enabling it ensures that Direct Lake mode can fetch data with maximum efficiency, significantly reducing the latency of report visuals when interacting with large datasets.

Why this answer

Direct Lake mode relies on Delta Parquet files. V-Order (A), compaction via OPTIMIZE (C), and statistics via ANALYZE (E) are the standard performance tuning steps. Partitioning on high-cardinality columns (D) is generally discouraged in Delta Lake as it leads to too many small files, which degrades performance; partitioning should be done on low-cardinality columns.

Exam trap

Candidates often confuse traditional SQL database indexing/partitioning strategies with Delta Lake optimization strategies.

28
MCQmedium

You are creating a Fabric Dataflow Gen2 to ingest data from an on-premises Oracle database into a Lakehouse. The source table contains a column named 'LastModified' of data type TIMESTAMP WITH TIME ZONE. You need to ensure that the dataflow stores this column as a DateTime with time zone offset in the Lakehouse. What should you do?

A.In the Dataflow Gen2 query, change the column data type to DateTime.
B.In the Dataflow Gen2 query, change the column data type to DateTimeZone.
C.In the Dataflow Gen2 query, leave the column as Text and let the Lakehouse infer the type.
D.In the Dataflow Gen2 query, change the column data type to Date.
AnswerB

Dataflow Gen2 uses the M language, where DateTimeZone is the native type for timestamps with time zone offset. Changing the column type to DateTimeZone preserves the offset and ensures correct storage in the Lakehouse Delta table as a timestamp with time zone. This is the correct approach for maintaining time zone fidelity.

Why this answer

The Dataflow Gen2 M language includes DateTimeZone, which is specifically designed to handle timestamps with time zone offsets. By converting the source column to DateTimeZone, the offset is preserved and the data is correctly written to the Lakehouse as a timestamp with time zone. Other types like DateTime or Date would lose the offset or time components, and leaving it as Text would not provide the correct semantic type.

Exam trap

The trap here is assuming that DateTime in Dataflow Gen2 automatically includes time zone information, when it actually represents a local datetime without offset.

29
MCQmedium

You are preparing a large dataset in Fabric and need to remove duplicate rows based on multiple columns. Which approach is most efficient for a Delta table?

A.Use a T-SQL DELETE statement with a subquery
B.Use the dropDuplicates method in PySpark
C.Export to Excel, clean, and re-import
D.Use a Dataflow Gen1
AnswerB

The 'dropDuplicates' method is highly efficient, leveraging the distributed nature of the Spark engine to scan data and remove redundancies. It allows developers to specify a subset of columns, providing a declarative way to handle data quality without writing complex SQL scripts or manually managing record iteration.

Why this answer

The most efficient way to remove duplicates in a Delta table within Fabric is by using the 'dropDuplicates' method in PySpark or executing a MERGE statement. By specifying the subset of columns, the Spark engine intelligently identifies duplicate entries and keeps only the distinct records. This operation is highly optimized for Delta formats, ensuring that the deduplication process remains performant even as the volume of the data scales significantly.

Exam trap

Candidates might suggest using SQL 'DISTINCT' or manual filtering. While these work, 'dropDuplicates' is the idiomatic and most efficient Spark-native method for handling large Delta tables.

30
MCQmedium

You are preparing a dataset in a Fabric Lakehouse. You want to ensure that users querying the data through the SQL analytics endpoint see the most up-to-date schema changes without manual refresh. How does Fabric handle this synchronization?

A.Users must run the 'MSCK REPAIR TABLE' command
B.Fabric automatically synchronizes Delta metadata
C.The SQL endpoint only updates during a capacity restart
D.Schemas must be manually exported to a JSON file
AnswerB

The Lakehouse in Fabric has a built-in mechanism that automatically discovers Delta tables and synchronizes their schema with the SQL analytics endpoint. This seamless integration ensures that any table created via Spark is immediately available for T-SQL querying with the correct columns and data types without manual intervention.

Why this answer

Microsoft Fabric provides an automatic metadata synchronization service that monitors the Lakehouse for changes to Delta tables. When a Spark job or Notebook creates or updates a Delta table, the metadata is automatically propagated to the SQL analytics endpoint, ensuring that the SQL schema remains in sync with the underlying Delta Lake storage.

Exam trap

Candidates frequently assume that manual SQL endpoint refreshes or explicit DDL statements are required to update schema metadata after running Spark notebooks.

31
MCQhard

Refer to the exhibit. A data engineer runs this code in a Fabric Notebook to move data from the Bronze layer (Files) to the Silver layer (Tables). What is a potential performance risk of running this code repeatedly without modification?

A.The code will automatically create duplicate rows
B.The SQL endpoint will be unable to read the Delta table
C.Full data processing leads to increased compute costs
D.V-Order will be disabled for this specific table
AnswerC

The code lacks any filtering or incremental loading logic (like checking for new files or using a watermark). As the 'SalesData' folder grows over time, the Spark job will read every single file and attempt to rewrite the entire table, leading to exponentially increasing compute time and costs.

Why this answer

The code snippet (implied by the context of 'moving data' without incremental logic) performs a full overwrite or full read/write of the dataset. Repeatedly processing the entire Bronze dataset instead of using incremental loads (e.g., filtering by timestamp or using merge) leads to unnecessary compute consumption and increased costs as the data volume grows. The explanation's mention of 'errors or unintended overwrites' is secondary to the performance risk requested in the stem.

Exam trap

Candidates often confuse 'save mode' errors with 'performance' issues. The question specifically asks for a performance risk.

32
MCQeasy

You are creating a Dataflow Gen2 to ingest data from an OData source into a Lakehouse. The source contains a column 'OrderDate' stored as text in the format 'MM/dd/yyyy'. You need to convert this column to a Date type and load it into a Delta table. Which transformation should you use?

A.Use the 'Split column' transformation by delimiter and keep the first part.
B.Use the 'Parse' transformation and select Date from the list of formats.
C.Change the data type of the OrderDate column to Date using the Transform tab.
D.Use the 'Add custom column' transformation with a Date.FromText function.
AnswerC

The Transform tab in Dataflow Gen2 provides a 'Data Type' option that allows you to change the column type. Selecting Date will convert the text values to date values, interpreting them according to the locale settings. This is the standard and correct way to convert a text column to a date type. It ensures that the Delta table receives the correct data type for OrderDate, enabling date-based operations downstream.

Why this answer

To convert a text column to a Date type in Dataflow Gen2, the most straightforward method is to change the column's data type using the Data Type option on the Transform tab. This directly modifies the column, ensuring that the Delta table receives the correct type. Other transformations like Parse, Split, or Add custom column do not directly change the existing column's type or are meant for different purposes.

Thus, changing the data type is the correct approach.

Exam trap

The trap here is overcomplicating the conversion by using functions or custom columns when a simple data type change suffices.

33
MCQmedium

You are developing a data pipeline in Microsoft Fabric that ingests data from an on-premises SQL Server. You need to ensure that sensitive PII data is masked before it lands in the Lakehouse. Which transformation approach should you implement to maintain compliance while preserving data utility?

A.Apply Dynamic Data Masking at the SQL Server source before extraction.
B.Use T-SQL views in the Lakehouse to mask columns upon query execution.
C.Use Dataflows Gen2 to apply Power Query transformations to mask PII columns during ingestion.
D.Implement row-level security on the Lakehouse table to hide sensitive rows.
AnswerC

Dataflows Gen2 is the recommended tool for performing extract, transform, load (ETL) operations within Fabric. By applying transformations during the load process, you ensure that PII is masked before being staged in the Lakehouse, adhering to security best practices by minimizing the storage of unmasked sensitive data in the lake.

Why this answer

Dataflows Gen2 is the correct tool for performing transformations during the ingestion process in Microsoft Fabric. By applying Power Query transformations within the Dataflow, you can mask PII data before it is written to the Lakehouse, ensuring that sensitive data never lands in the storage layer in an unmasked state. This aligns with the requirement to mask data 'before it lands'.

Exam trap

Candidates often confuse 'Dynamic Data Masking' (which is a security feature applied at the database engine level) with 'Data Masking' (which is a data transformation process).

34
MCQmedium

You are designing a data ingestion pipeline in Microsoft Fabric that loads millions of transactional rows daily from Azure Blob Storage into a Lakehouse table. The upstream data arrives as uncompressed CSV files containing frequently changing records. To ensure the downstream analytics queries maintain high performance and avoid performance degradation caused by small file proliferation, which configuration and format strategy should you implement?

A.Keep the files in their original CSV format within the Lakehouse files section and query them directly using external tables.
B.Load the CSV files into a Fabric Lakehouse table using the Delta format and enable V-Order optimization during write operations.
C.Compress the CSV files into GZIP archives prior to ingestion and store them in the unstructured storage zone of the Lakehouse.
D.Convert the CSV files into uncompressed JSON format to preserve nested hierarchical structures before loading them into a managed table.
AnswerB

Delta Lake format combined with V-Order optimization organizes the underlying Parquet files into a sorted, compressed layout optimized for the Power BI Direct Lake engine, maximizing scan efficiency and significantly reducing memory consumption during reporting.

Why this answer

Converting incoming CSV files into Delta Lake format inside the Fabric Lakehouse automatically applies ACID transactions and compacts data into Parquet files. This approach supports efficient columnar storage, schema enforcement, and V-Order optimization, which dramatically improves analytical query performance while preventing the small file problem inherent in raw CSV storage.

Exam trap

Candidates often keep data in raw CSV format inside the Lakehouse or use basic Parquet without V-Order, causing severe small file proliferation and slow query performance.

35
MCQmedium

You are designing a Fabric Lakehouse to store sales data. The data will be queried by a Power BI semantic model in Direct Lake mode. You need to ensure that the semantic model can efficiently handle queries that aggregate sales amounts by product category and time. Which Delta table optimization should you implement?

A.Create a materialized view that pre-aggregates sales by ProductCategory and Date.
B.Use V-Order optimization on the Delta table.
C.Partition the table by ProductCategory.
D.Enable change data feed on the table.
AnswerB

V-Order is a write optimization that sorts and organizes data to improve compression and query performance in Fabric. It is particularly beneficial for Direct Lake mode because it enhances data skipping and reduces I/O. By applying V-Order, the semantic model can more efficiently aggregate sales amounts by product category and time, as the data is physically clustered to support such queries.

Why this answer

V-Order optimization is designed to improve query performance in Fabric, especially for Direct Lake mode. It sorts data during writes, which enhances compression and data skipping, making aggregations by product category and time more efficient. Partitioning by a low-cardinality column may not help, materialized views are not native, and change data feed is for incremental loads, not performance.

Therefore, V-Order is the correct choice.

Exam trap

The trap here is assuming that partitioning always improves performance, but partitioning by a low-cardinality column like ProductCategory can cause small file issues and may not benefit aggregations.

36
MCQmedium

You are developing a Fabric Notebook to perform an ETL job. You need to ensure that the data processing remains atomic; if a failure occurs, the table should not contain partial data. What approach should you use?

A.Use the Spark SQL 'TRUNCATE' command before loading.
B.Write data using the 'overwrite' mode.
C.Manually delete the existing Parquet files.
D.Disable the Delta Lake checkpoint feature.
AnswerB

Writing in 'overwrite' mode in Delta Lake is an atomic operation. The transaction log is updated to point to the new files only after they are successfully written, ensuring that the table remains in a consistent state even if the process fails midway through the write operation.

Why this answer

Delta Lake's transaction log ensures atomicity for all operations. By using the 'overwrite' mode or working within a single transactional transaction, you ensure that the state of your data remains consistent. This is critical for data integrity, as it prevents downstream processes from consuming incomplete or corrupted data resulting from failed pipeline executions, which is a core requirement for reliable enterprise data engineering.

Exam trap

Candidates often suggest manual error handling or 'try-catch' blocks. In Delta Lake, atomicity is handled automatically by the write mode and the transaction log.

37
MCQmedium

You have a Fabric pipeline that ingests CSV files into a Lakehouse. The files sometimes contain rows with missing values in the 'OrderDate' column. You need to load only rows where 'OrderDate' is not null, and you want to minimize data movement. Which Dataflow Gen2 transformation should you use?

A.Use the 'Replace values' transformation to replace null with a default date, then filter.
B.Use the 'Filter rows' transformation with the condition 'OrderDate is not null'.
C.Use the 'Remove rows' transformation with the condition 'OrderDate is null'.
D.Use the 'Sort' transformation to sort by OrderDate, then remove rows with null.
AnswerB

The 'Filter rows' transformation allows you to keep only rows that satisfy a given condition. By setting the condition to 'OrderDate is not null', you retain only rows with valid OrderDate values, which is exactly the requirement. This transformation is efficient because it operates within the dataflow's query folding capabilities, minimizing data movement by pushing the filter down to the source when possible. It is the standard way to exclude nulls in Dataflow Gen2.

Why this answer

The 'Filter rows' transformation is the correct tool to keep only rows that meet a specific condition. Using 'OrderDate is not null' ensures that rows with missing dates are excluded, which aligns with the requirement to load only valid records. This method is efficient because Dataflow Gen2 can often fold the filter back to the source, reducing data movement.

Other transformations either remove the wrong rows, alter data, or add unnecessary steps.

Exam trap

The trap here is confusing 'Filter rows' with 'Remove rows' or thinking that sorting can eliminate nulls.

38
MCQmedium

You are building a data transformation pipeline using Dataflow Gen2 in Microsoft Fabric. You need to combine two queries containing customer information and sales transactions based on a shared customer ID column, ensuring that all records from the customer table are retained even if they have no matching sales. Which join kind should you select?

A.Inner join
B.Left Outer join
C.Right Anti join
D.Full Outer join
AnswerB

A Left Outer join returns every row from the customer (left) table plus matching sales rows, emitting nulls where no sale exists. This satisfies the requirement to retain all customer records even without matching transactions, which an inner join would discard.

Why this answer

A Left Outer join retains all records from the left table (customers) and matches corresponding records from the right table (sales). Unmatched customer rows return null values for sales columns, satisfying the requirement to keep all customers regardless of transaction history.

Exam trap

Candidates often select 'Full Outer Join' out of confusion, failing to realize that this would include unmatched sales records, violating the requirement to only keep all customers.

39
MCQmedium

Refer to the exhibit. You are writing PySpark code in a Fabric notebook to combine sales and product information. Given the configuration, what is the primary behavior of this merge operation?

A.It returns all sales records, even if no product exists
B.It performs a Cartesian product
C.It returns only records where the product_id exists in both
D.It creates a union of both tables
AnswerC

The configuration specifies an 'inner' join, which is the intersection of two datasets. Only rows sharing a common product_id are included in the final output. This ensures that the resulting dataset contains complete, valid information, which is a common requirement when preparing data for downstream analytical modeling.

Why this answer

The exhibit defines an inner join between the sales and products DataFrames using the product_id column as the key. An inner join returns only the rows where there is a match in both DataFrames. This is a standard operation in data preparation to ensure that every sales record has a corresponding valid product entity, effectively filtering out orphans that lack a referenced product record.

Exam trap

Candidates often mistake an Inner join for a Left join, assuming it will keep all sales records even if no matching product exists, which leads to unexpected data loss.

40
MCQeasy

You are creating a Fabric Dataflow Gen2 to transform data from a CSV file. Which feature should you use if you want to preview the impact of your transformations on a sample set of data before applying them to the entire dataset?

A.Power Query Editor
B.DirectQuery mode
C.Notebook Markdown cell
D.Pipeline Debug mode
AnswerA

The Power Query Editor is the primary interface for applying transformations in Dataflow Gen2. It automatically displays a preview of the data based on your current step, allowing you to see the immediate effect of any operation you perform on the sample set before finalizing the data flow design.

Why this answer

While the Power Query Editor is the environment where transformations occur, the specific feature that allows users to preview the impact of transformations on a sample set of data is 'Data preview' or 'Data profiling' within the Power Query Editor. However, given the options provided, 'Power Query Editor' is the only logical choice, but the explanation should be more precise regarding the 'Data preview' functionality.

Exam trap

Candidates might confuse the environment (Power Query Editor) with the specific feature (Data preview/sampling).

41
MCQmedium

Refer to the exhibit. This SQL code is executed in a Microsoft Fabric Notebook. What is the primary purpose of this operation in the context of data preparation?

A.To create a historical snapshot of the inventory for auditing.
B.To perform an UPSERT operation to synchronize the Silver table with new data.
C.To delete records from the Silver table that no longer exist in the Bronze table.
D.To validate that the data types in the Bronze table match the Silver table.
AnswerB

This MERGE command implements UPSERT logic by checking for matching item IDs. If a match is found, it updates the record; if not, it inserts a new one. This is a standard pattern for synchronizing a target table with incremental changes from a source, ensuring data consistency.

Why this answer

The MERGE statement is a critical tool for maintaining a Silver layer in a medallion architecture. It allows for UPSERT logic, where existing records are updated with new information and new records are inserted. This ensures that the Silver layer provides a clean, deduplicated, and current state of the business entities.

Exam trap

Candidates often confuse the MERGE statement with a simple INSERT or UPDATE, overlooking that its primary value in the Medallion architecture is the atomic 'UPSERT' capability for synchronization.

42
MCQmedium

An organization is migrating a 2 TB dataset from an on-premises SQL Server to a Microsoft Fabric Lakehouse. The data contains complex transformations that require high performance and the ability to handle data skewed across multiple partitions. Which tool should you use to ensure the most scalable and performant ingestion and transformation process?

A.Dataflows Gen2
B.Fabric Notebooks
C.Data Factory Copy Activity
D.T-SQL Stored Procedures
AnswerB

Fabric Notebooks allow engineers to use PySpark or Scala to manage massive datasets through distributed computing. Spark provides granular control over data shuffling, partitioning strategies, and caching, which are essential for optimizing the ingestion of a 2 TB dataset and resolving performance issues related to data skew in a scalable manner.

Why this answer

Fabric Notebooks utilize Apache Spark, which is designed for large-scale distributed data processing. For a 2 TB dataset with complex logic and data skew, Spark's ability to customize partitioning and leverage cluster resources provides the necessary scalability that visual tools might lack. This ensures that the data preparation process remains efficient as the dataset grows and becomes more complex over time.

Exam trap

Candidates often choose Data Factory Copy Activity because it is the standard tool for data movement, but it lacks the compute power for complex transformations on large, skewed datasets.

43
MCQmedium

You need to optimize the storage of a large dataset in a Fabric Lakehouse using Delta Lake format. Which TWO actions should you perform to improve query performance? (Choose two.)

A.Run the OPTIMIZE command with ZORDER BY.
B.Increase the number of partitions to 10,000.
C.Execute the VACUUM command regularly.
D.Run the OPTIMIZE command to compact files.
E.Convert all tables to Parquet format.
AnswerA, D

Z-Ordering co-locates related data within the same set of files. This significantly improves data skipping performance during queries with filters on the Z-Ordered columns. It is an essential performance tuning technique for large-scale analytical tables where selective filtering is a common access pattern for users.

Why this answer

Running OPTIMIZE with ZORDER BY co-locates related data in the same files, enabling data skipping and dramatically reducing I/O for filtered queries. Running OPTIMIZE to compact small files addresses the small-file problem that degrades Delta Lake read performance. Together these are the two standard Delta Lake tuning actions for query performance in a Lakehouse.

Exam trap

DP-600 often tests whether candidates confuse VACUUM (storage cleanup) with OPTIMIZE (performance compaction), or mistakenly believe more partitions and Parquet conversion improve query speed when they actually add overhead or remove Delta benefits.

How to eliminate wrong answers

Option B is wrong because increasing partitions to 10,000 creates excessive small files and metadata overhead, which hurts performance rather than improving it — partitioning should be based on cardinality and query patterns, typically keeping partition counts manageable. Option C is wrong because VACUUM removes old, unreferenced files to reclaim storage; it does not improve query performance and can break time travel if retention is set too low. Option E is wrong because Delta Lake tables are already stored as Parquet files under the hood — converting to Parquet would strip Delta's transaction log, ACID guarantees, and time travel, and is not a performance optimization.

44
MCQmedium

You are building a Fabric Dataflow Gen2 to ingest data from a REST API that returns JSON. The API response includes a nested array of order line items under an 'orders' property, and each line item contains a 'productId' and 'quantity'. You want to flatten this structure into a single table with one row per line item, preserving the parent order ID. Which Dataflow Gen2 transformation should you use?

A.Use the 'Pivot' transformation on the 'orders' column to turn the array into columns.
B.Use the 'Expand' option on the 'orders' column and then select the nested fields to extract.
C.Use the 'Split Column' transformation on the 'orders' column by a delimiter.
D.Use the 'Unpivot' transformation on the 'orders' column to convert the array into rows.
AnswerB

The 'Expand' transformation in Dataflow Gen2 is designed to flatten nested structures such as lists or records. Expanding the 'orders' column will produce one row per element in the array, and you can select 'productId' and 'quantity' to extract. The parent order ID will be automatically repeated for each expanded row, achieving the desired result.

Why this answer

Expanding a nested array column is the correct way to flatten JSON data in Dataflow Gen2. The 'Expand' transformation creates one row per element in the array and allows selection of nested fields, preserving parent values. Pivot, unpivot, and split are meant for different reshaping tasks and cannot handle nested structures.

Exam trap

The trap here is confusing flattening nested arrays with reshaping operations like pivot or unpivot, which do not handle nested structures.

45
MCQmedium

Which TWO factors are most important when choosing an partitioning strategy for a large Delta table in Microsoft Fabric?

A.High cardinality columns like Unique ID.
B.Low to medium cardinality columns like Date.
C.The number of total files in the storage account.
D.Common query filters used by end users.
E.The color and naming convention of the folders.
AnswerB, D

Partitioning on low to medium cardinality columns is the best practice. It creates a manageable number of directories, allowing the engine to effectively skip irrelevant data during queries. Using columns like 'Date' aligns well with common analytical query patterns, making it highly effective for pruning the data scan range.

Why this answer

Partitioning is essential for query performance in large tables. The two most important factors are cardinality and query patterns. Columns with low to medium cardinality, like 'Date' or 'Region', are ideal for partitioning to reduce the amount of data scanned.

Furthermore, you must align the partitions with the most frequent query filters to ensure the query optimizer can prune unnecessary files, significantly improving performance and reducing the cost of query execution across the data lake.

Exam trap

Candidates often suggest high-cardinality columns like 'TransactionID' for partitioning. This creates too many small partitions, which destroys Spark performance and metadata management efficiency.

46
MCQhard

You are using Fabric Dataflows Gen2 to ingest data from an API. You need to ensure the process is resilient. Which THREE features should you implement?

A.Implement retry policies for API connections.
B.Use incremental loading to fetch only new records.
C.Configure alerts to notify the team on failure.
D.Hardcode the API credentials in the Dataflow M-code.
E.Enable the 'Always-On' flag for the Dataflow runtime.
AnswerA, B, C

APIs frequently experience transient issues like rate limiting or temporary network timeouts. Configuring a retry policy allows the dataflow to automatically attempt the request again after a delay, preventing the entire pipeline from failing due to minor, temporary fluctuations in service availability or network stability.

Why this answer

Resilience in Dataflows Gen2 means the pipeline can survive transient API failures, avoid re-pulling data unnecessarily, and alert humans when something breaks. Retry policies handle throttling and transient HTTP 429/5xx errors, incremental loading reduces the blast radius of a failure by fetching only new/changed rows, and failure alerts ensure the team is notified rather than discovering stale data days later. Together these three form the standard reliability triad for API ingestion.

Exam trap

DP-600 often tests the confusion between 'resilience' features and 'security' or 'performance' features, tempting candidates to pick credential handling or non-existent runtime flags.

How to eliminate wrong answers

Option D is wrong because hardcoding credentials in M-code exposes secrets in the dataflow definition and breaks when credentials rotate — you should use a secure connection or Key Vault. Option E is wrong because there is no 'Always-On' flag for Dataflow runtime; Dataflows Gen2 run on demand or on a schedule, and Fabric capacity already keeps the engine available.

47
MCQeasy

You are using Fabric Dataflow Gen2 and need to merge data from two different sources. Which transformation would you use to combine the datasets based on a common key, similar to a SQL JOIN operation?

A.Append
B.Merge
C.Filter
D.Group By
AnswerB

The Merge transformation is the functional equivalent of a join. It allows you to select columns from two tables and combine them based on a match between common keys. This is the correct tool for relating datasets that share identifiers, which is a fundamental data engineering task in Fabric.

Why this answer

In Fabric Dataflow Gen2, the Merge transformation is used to combine two datasets based on a common key, similar to a SQL JOIN. It allows you to join tables by selecting matching columns and choosing the join kind (e.g., inner, left outer). This is the correct transformation for merging data from different sources.

Exam trap

DP-600 often tests the difference between Merge (join) and Append (union), and candidates may confuse the two when asked about combining datasets based on a key.

How to eliminate wrong answers

Option A (Append) is wrong because Append combines rows from two tables vertically (union), not based on a key. Option C (Filter) is wrong because Filter is used to remove rows based on conditions, not to combine datasets. Option D (Group By) is wrong because Group By aggregates data, not joins it.

48
MCQmedium

You are migrating data from an on-premises data warehouse to Fabric. You need to ensure the schema of the migrated data remains intact. What should you use to define the schema?

A.Rely on Spark's schema inference.
B.Use a struct definition in the Spark read process.
C.Manually update the schema in the SQL endpoint after loading.
D.Convert all columns to the 'String' type.
AnswerB

Defining an explicit StructType schema when reading data ensures that the data is loaded with the intended data types, regardless of how the source data is structured. This prevents common errors like incorrect type inference and provides a solid foundation for further transformations, ensuring the integrity of the data throughout the pipeline.

Why this answer

Using a struct definition in the Spark read process explicitly defines the schema, ensuring the migrated data retains its original column names, data types, and structure. This prevents Spark from inferring an incorrect schema and preserves schema integrity during migration to Fabric.

Exam trap

DP-600 often tests the misconception that schema inference is reliable for migrations, when in fact explicit schema definition is required to guarantee schema integrity.

How to eliminate wrong answers

Option A is wrong because relying on Spark's schema inference can misinterpret data types (e.g., inferring strings as integers or vice versa) and may not preserve the exact schema from the source warehouse. Option C is wrong because manually updating the schema in the SQL endpoint after loading is reactive and error-prone; it does not ensure the migrated data itself has the correct schema. Option D is wrong because converting all columns to 'String' type destroys the original data types and violates the requirement to keep the schema intact.

49
MCQhard

You are preparing data in a Fabric Lakehouse using a Spark notebook. You need to merge a DataFrame containing updates into an existing Delta table to perform an upsert. The Delta table is named 'silver.customers' and the merge condition is on 'CustomerID'. Which PySpark code should you use?

A.deltaTable.alias('t').merge(updatesDF.alias('s'), 't.CustomerID = s.CustomerID').whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()
B.deltaTable.merge(updatesDF, 'CustomerID').whenMatchedUpdateAll().whenNotMatchedInsertAll().save()
C.deltaTable.alias('t').merge(updatesDF.alias('s'), 't.CustomerID = s.CustomerID').whenMatchedUpdateAll().whenNotMatchedInsertAll().saveAsTable('silver.customers')
D.deltaTable.alias('t').merge(updatesDF.alias('s'), 't.CustomerID = s.CustomerID').whenMatchedUpdateAll().whenNotMatchedInsertAll().write().mode('overwrite').save()
AnswerA

This code uses the Delta Lake merge operation correctly. It aliases the target and source, specifies the merge condition, and uses whenMatchedUpdateAll and whenNotMatchedInsertAll to update existing rows and insert new ones. Finally, execute() runs the merge. This is the standard upsert pattern.

Why this answer

The correct PySpark code for an upsert into a Delta table uses the merge operation with proper aliases, a merge condition, whenMatchedUpdateAll and whenNotMatchedInsertAll, and finally execute(). This ensures existing rows are updated and new rows are inserted.

Exam trap

The trap here is using save() or saveAsTable() instead of execute() to finalize the merge, or providing an invalid merge condition like just the column name.

50
MCQmedium

You are using a Fabric Data Pipeline to copy data from a REST API endpoint that returns JSON. The API requires an OAuth2 token for authentication. You need to configure the pipeline to securely authenticate and retrieve the data. What should you do?

A.Use a Copy activity with the REST connector and embed the OAuth2 token as a dynamic expression in the source dataset.
B.Use a Web activity to call the API, passing the client secret directly in the request header as a Base64-encoded string.
C.Configure the pipeline to use a self-hosted integration runtime and store the credentials in a configuration file on the runtime machine.
D.Create a linked service for the REST API that uses the 'OAuth2' authentication type, and store the client ID and client secret in Azure Key Vault. Reference the Key Vault in the linked service.
AnswerD

Using a linked service with OAuth2 authentication and storing credentials in Azure Key Vault is the secure and recommended method. Fabric supports Key Vault integration for secrets, ensuring sensitive information is not exposed in the pipeline. This allows the pipeline to obtain tokens and access the API securely.

Why this answer

For secure authentication to a REST API with OAuth2 in Fabric pipelines, you should create a linked service that uses OAuth2 and references Azure Key Vault for secrets. This centralizes credential management and avoids exposing secrets in pipeline definitions. The Copy activity can then use this linked service to authenticate and retrieve data.

Exam trap

The trap here is thinking that passing secrets in headers or embedding tokens in datasets is acceptable, when secure secret management via Key Vault is required.

51
MCQhard

A Fabric Lakehouse table named Sales is stored in Delta format and contains a column OrderDate of type string holding values such as '2024-03-15'. Analysts complain that range filters on this column are slow and that date functions fail. You must convert the column to a proper date type in place while preserving the existing table and its history. You are using a Fabric notebook with Spark. Which approach should you use?

A.Create an external table over the same Parquet files and declare OrderDate as date in the external table definition.
B.Use the DeltaTable API to overwrite the table with a DataFrame where OrderDate is cast to date, using overwriteSchema set to true.
C.Drop the Sales table and recreate it with OrderDate typed as date, reloading data from the original source system.
D.Run ALTER TABLE Sales ALTER COLUMN OrderDate TYPE DATE in Spark SQL to change the column type directly.
AnswerB

Overwriting with overwriteSchema enabled replaces the table's schema definition while writing the cast column as a true date type. Because the write targets the same Delta table, the table identity is preserved and new commits extend the transaction log, so prior versions remain queryable via time travel until vacuum removes the underlying files. This is the standard in-place type correction pattern.

Why this answer

Casting the column and writing back with overwriteSchema set to true updates the Delta table's schema in place. The table keeps its name and location, new commits append to the same transaction log, and earlier versions remain accessible through time travel until file retention removes them. This satisfies the in-place conversion and history preservation requirements without dropping or duplicating the table.

Exam trap

The trap here is assuming Delta supports arbitrary ALTER COLUMN TYPE conversions, when it only permits limited type widening.

52
MCQmedium

You are building a Fabric data pipeline that ingests CSV files from an Azure Data Lake Storage Gen2 account into a Lakehouse. The files arrive daily and contain a header row, but some rows have missing values in the 'TransactionAmount' column. You need to load only rows where 'TransactionAmount' is not null into a Delta table, while preserving all other columns. Which Dataflow Gen2 transformation should you use?

A.Use the 'Replace values' transformation to replace nulls in TransactionAmount with zero.
B.Use the 'Group by' transformation on TransactionAmount and count rows.
C.Use the 'Fill down' transformation on the TransactionAmount column.
D.Use the 'Remove rows' transformation and specify a condition to remove rows where TransactionAmount is null.
AnswerD

The Remove rows transformation in Dataflow Gen2 allows filtering out rows based on a condition. Setting the condition to remove rows where TransactionAmount is null will exclude those rows while keeping all other columns intact. This is the correct approach because it directly addresses the requirement to load only non-null TransactionAmount rows, and it is a standard transformation in Power Query Online used by Dataflow Gen2.

Why this answer

To load only rows where TransactionAmount is not null, you must filter out rows with null values. The Remove rows transformation in Dataflow Gen2 provides a condition-based filter that can exclude rows where the specified column is null. This preserves the original row structure and all other columns, ensuring that only complete records are written to the Delta table.

Other transformations either modify nulls or aggregate data, which do not meet the requirement.

Exam trap

The trap here is confusing filtering with data imputation; replacing nulls or filling down keeps the rows, while the requirement is to exclude them.

53
MCQmedium

You are using a Fabric pipeline to copy data from an on-premises Oracle database to a Lakehouse. The pipeline must run incrementally, loading only new or changed records since the last run. Which configuration should you use in the Copy activity?

A.Use a watermark column and configure the source query to filter records greater than the last watermark value.
B.Configure the 'Delete files after completion' option to true.
C.Set the 'Enable staging' option to true and specify a staging linked service.
D.Enable the 'Enable partition discovery' option.
AnswerA

Using a watermark column is a standard pattern for incremental loading. You track the maximum value of a timestamp or increasing column from the last run and use it to filter new records in the source query. This ensures only new or changed records are loaded. It is efficient and reliable for Oracle sources.

Why this answer

To achieve incremental loading from an Oracle database, you should use a watermark column. This involves storing the last loaded value (e.g., maximum modification timestamp) and using it in the source query to select only records that have changed since then. The Copy activity in Fabric pipelines supports this pattern through parameterization and dynamic queries.

This method is efficient and ensures that only new or updated records are transferred, reducing load and processing time.

Exam trap

The trap here is confusing performance optimization options like staging or partition discovery with incremental loading mechanisms.

54
Multi-Selecthard

You are managing a Lakehouse in Microsoft Fabric and need to maintain the health and performance of your Delta tables. Which TWO maintenance tasks should you perform regularly? (Select TWO)

Select 2 answers
A.Run the VACUUM command
B.Run the OPTIMIZE command
C.Delete the _delta_log folder
D.Reformat the Lakehouse to NTFS
E.Convert all Delta tables to JSON
AnswersA, B

The VACUUM command is essential for cleaning up old file versions that are no longer needed for time travel or the current table state. In a high-churn environment, failing to vacuum can lead to significant storage bloat in OneLake and increased costs, as every historical version of data is retained indefinitely.

Why this answer

Delta tables require regular maintenance to prevent performance degradation. The VACUUM command removes old data files that are no longer referenced by the current state of the table, saving storage costs. The OPTIMIZE command compacts small files into larger ones, which improves read performance by reducing the number of file handles the engine must manage.

Exam trap

Candidates often include 'Compaction' or 'Indexing' as separate tasks. While related, the specific commands required for table maintenance in Fabric/Delta are VACUUM and OPTIMIZE.

55
MCQmedium

You need to automate the execution of a Notebook daily. Which Fabric tool should you use to orchestrate this workflow?

A.SQL Server Agent
B.Fabric Data Pipeline
C.Power BI Desktop
D.DirectQuery
AnswerB

Fabric Pipelines are the built-in orchestration tool designed to automate, schedule, and monitor data movement and transformation activities. They support complex logical dependencies, retry policies, and scheduling, making them the ideal choice for running Notebooks on a daily basis to ensure data is updated according to organizational business requirements.

Why this answer

Fabric Data Pipelines are the native orchestration tool in Microsoft Fabric, designed to schedule and automate workflows such as running notebooks, dataflows, stored procedures, and other activities. They provide a visual designer and support triggers (schedule, event-based) to run notebooks daily. Unlike other options, Data Pipelines are built into Fabric and can directly invoke a Notebook activity, making them the correct choice for orchestrating notebook execution.

Exam trap

DP-600 often tests the distinction between orchestration tools and data connectivity modes, causing candidates to confuse DirectQuery (a query mode) with a scheduling mechanism, or to overlook that SQL Server Agent is not integrated with Fabric.

How to eliminate wrong answers

Option A is wrong because SQL Server Agent is a component of SQL Server (on-premises or Azure SQL Managed Instance) and cannot natively orchestrate Fabric items like notebooks; it lacks integration with Fabric's internal APIs. Option C is wrong because Power BI Desktop is a client-side authoring tool for reports and semantic models, not an orchestration service; it cannot schedule or run notebooks. Option D is wrong because DirectQuery is a data connectivity mode for Power BI that queries data directly from a source without importing, not an orchestration tool; it does not execute notebooks.

56
MCQmedium

You are preparing a dataset in a Microsoft Fabric Notebook using PySpark. You have a DataFrame 'df' with a column 'Price' that contains some null values. You need to replace these null values with the average price of the entire dataset. Which approach is most appropriate?

A.Use df.na.drop(subset=['Price']) to remove the rows.
B.Calculate the mean of 'Price' and use df.fillna(mean_val, subset=['Price']).
C.Apply a filter where 'Price' is not null using df.filter(df['Price'].isNotNull()).
D.Use the 'V-Order' function to automatically fill missing values.
AnswerB

This is the correct technical approach. First, you calculate the average of the 'Price' column using an aggregation function. Then, you use the 'fillna' method to target the 'Price' column and replace all null entries with that pre-calculated mean value, satisfying the business requirement.

Why this answer

In Spark, handling missing data is a common data preparation task. Calculating the mean and using the 'fillna' or 'fill' method allows for imputation, which maintains the dataset's size while minimizing the statistical impact of missing values. This is a standard practice for preparing data for machine learning or reporting.

Exam trap

Candidates sometimes try to compute the mean using a manual loop or a complex UDF, ignoring the built-in PySpark 'fillna' method which is significantly more efficient for DataFrames.

57
MCQeasy

A data engineer is preparing a Fabric Lakehouse table that will be queried by a Direct Lake semantic model. The table is created by a notebook that writes Parquet files into a folder in the Lakehouse, and the engineer notices the table does not appear in the SQL analytics endpoint. The engineer wants the folder to be recognized as a managed Delta table. What should the engineer do?

A.Move the folder from the Tables area to the Files area so the SQL analytics endpoint can enumerate its contents.
B.Register the folder as an external table by running CREATE EXTERNAL TABLE against the SQL analytics endpoint.
C.Convert the Parquet files to CSV so the Lakehouse can infer the schema and expose the folder as a table.
D.Write the files using the Delta format and include the _delta_log folder so the table can be discovered by the Lakehouse.
AnswerD

For a folder in the Lakehouse Tables area to be recognized as a table, it must contain Delta-formatted data plus the transaction log directory named _delta_log. Without that log, the folder is just Parquet files and the SQL analytics endpoint and Direct Lake cannot treat it as a table. Writing through a Delta writer produces the log automatically.

Why this answer

A Lakehouse recognizes a table when the folder contains Delta data accompanied by the _delta_log transaction log directory. Writing with a Delta writer in a notebook creates that log and the associated JSON commits, after which the table appears in the SQL analytics endpoint and can back a Direct Lake model. Raw Parquet without the log is treated as unmanaged files.

Exam trap

The trap here is assuming a folder of Parquet files is automatically a table, when the Delta transaction log is what makes it discoverable.

58
MCQeasy

You have a Lakehouse in Microsoft Fabric that contains several Delta tables. You need to provide a SQL-based analyst with the ability to query these tables using their preferred desktop tool, such as SQL Server Management Studio (SSMS). What should you provide to the analyst?

A.The Workspace ID and the Lakehouse GUID.
B.The ABFS path to the Delta table folders.
C.The SQL connection string of the SQL analytics endpoint.
D.A Personal Access Token (PAT) for the Fabric REST API.
AnswerC

The SQL analytics endpoint provides a T-SQL interface for the Lakehouse. This endpoint generates a unique connection string that acts like a standard SQL Server instance address. Analysts use this string in SSMS to connect, browse schemas, and execute queries against the Delta tables using familiar SQL syntax.

Why this answer

The SQL analytics endpoint of a Fabric Lakehouse exposes a TDS-compatible SQL endpoint that SSMS, Azure Data Studio, and other SQL clients can connect to using a standard SQL connection string. This lets the analyst query Delta tables with T-SQL without needing Spark or the ABFS path.

Exam trap

DP-600 often tests whether candidates know that the SQL analytics endpoint — not the ABFS path or REST API — is the correct access method for T-SQL clients like SSMS.

How to eliminate wrong answers

Option A is wrong because a Workspace ID and Lakehouse GUID are Fabric REST identifiers, not connection endpoints for SQL clients. Option B is wrong because an ABFS path is a storage URI used by Spark/OneLake, not something SSMS can query directly. Option D is wrong because a PAT is used for authenticating to the Fabric REST API, not for T-SQL connections from SSMS.

59
MCQhard

You are processing streaming data in Fabric. You need to ensure that duplicate events are handled correctly before writing to the Lakehouse. What should you use?

A.A SQL stored procedure.
B.Spark Structured Streaming with watermarking and dropDuplicates.
C.A Power Query filter in a Dataflow.
D.A standard SQL GROUP BY clause.
AnswerB

Spark Structured Streaming provides native support for deduplication. By setting a watermark, you define the time threshold for how long the system keeps state to check for duplicates, which is the most efficient way to handle stream processing at scale while maintaining accuracy and preventing unbounded memory growth.

Why this answer

The explanation is correct, but the question requires explicit trap information to help students avoid common pitfalls.

Exam trap

Candidates might mistakenly choose a SQL-based approach (like GROUP BY or stored procedures) assuming standard batch processing logic applies to streaming data, which does not handle stateful deduplication over time windows.

60
MCQeasy

You are creating a Fabric Lakehouse and need to store raw CSV files that will be processed later by notebooks. You want to organize these files in a folder structure that separates data by year and month. Where should you store these files in the Lakehouse?

A.In the Files section, creating folders for each year and month.
B.In the Tables section, using a Delta table with partitioning by year and month.
C.In a separate Azure SQL Database, then link it to the Lakehouse.
D.In the Tables section, using a folder named after the year and month.
AnswerA

The Files section of a Lakehouse is designed for storing raw, unmanaged files in their native format, such as CSV, Parquet, or JSON. You can create a folder hierarchy to organize files by year and month, which is ideal for raw data that will be processed by notebooks. This allows you to maintain the original files without converting them to Delta format.

Why this answer

The Files section of a Fabric Lakehouse is specifically for storing raw, unmanaged files. You can create a folder structure to organize files by year and month, which is a common practice for raw data. The Tables section is for managed Delta tables, and other options like Azure SQL Database are not file storage solutions.

Storing raw CSVs in the Files section allows notebooks to read and process them as needed.

Exam trap

The trap here is assuming that the Tables section can store raw files, when it is actually reserved for managed Delta tables.

61
MCQhard

You are using a Fabric notebook to read a Delta table from a Lakehouse. You need to optimize read performance for a query that filters on a high-cardinality column 'CustomerID' and also on a low-cardinality column 'Region'. The table is large and partitioned by 'Region'. Which optimization technique should you implement?

A.Enable Z-Ordering on the 'CustomerID' column using OPTIMIZE ... ZORDER BY (CustomerID).
B.Create a Bloom filter index on the 'CustomerID' column.
C.Repartition the table by 'CustomerID' using a new partitioning scheme.
D.Use OPTIMIZE with no ZORDER to compact small files.
AnswerA

Z-Ordering clusters data by the specified column, improving data skipping for high-cardinality filters like CustomerID. Since the table is already partitioned by Region, Z-Ordering on CustomerID complements the partitioning by co-locating related values within each partition. This reduces the amount of data scanned when filtering on CustomerID, significantly improving query performance.

Why this answer

Z-Ordering on CustomerID is the appropriate optimization because it clusters data within each Region partition by CustomerID, enabling efficient data skipping for filters on that high-cardinality column. Repartitioning by CustomerID would cause performance degradation, simple compaction does not provide data skipping, and Bloom filters are not supported in Delta Lake. Thus, Z-Ordering is the correct choice.

Exam trap

The trap here is confusing partitioning with clustering: partitioning by a high-cardinality column like CustomerID leads to many small files, while Z-Ordering is designed for such columns.

62
MCQhard

You have a dataset with a highly skewed distribution in a join key. When performing a join in a Spark notebook, you observe performance degradation. Which technique should you use to handle this skew?

A.Broadcast join
B.Salting
C.Increasing the number of executor cores
D.Reducing the number of partitions
AnswerB

Salting effectively distributes skewed keys across multiple partitions by adding random values to the key. This forces Spark to process the previously bottlenecked key across multiple executors, balancing the compute load and preventing individual tasks from running much longer than others, thereby improving overall job performance and reliability.

Why this answer

Data skew occurs when one key is over-represented, causing a single executor to handle significantly more work than others, leading to bottlenecks. To resolve this, you should perform 'salting'. By appending a random integer to the skewed key in both datasets before joining, you distribute the data more evenly across the cluster partitions.

After the join, you remove the salted integer to restore the original key values and complete the operation.

Exam trap

Many candidates incorrectly suggest increasing cluster size or memory as the primary fix for skew, ignoring that skew is a distribution problem that hardware scaling cannot fundamentally resolve.

63
MCQhard

You are designing a Medallion architecture in Microsoft Fabric. You have a Silver layer table that is frequently updated with new records and changes to existing records. You need to ensure that the process maintains historical versions of the data for auditing while optimizing query performance for the latest state. Which implementation strategy is best?

A.Overwrite the Silver table daily
B.Use SCD Type 1 in a Warehouse
C.Implement SCD Type 2 in a Delta table
D.Create a new table for every refresh
AnswerC

SCD Type 2 tracks historical changes by creating new rows for each change with effective start and end dates. Storing this in a Delta table within a Lakehouse allows you to use Spark for complex MERGE operations and leverage Delta's time travel features for auditing, providing a robust solution for historical data management.

Why this answer

SCD Type 2 in a Delta table maintains full historical versions of records by creating new rows with effective dates and current flags when changes occur. Delta Lake supports ACID transactions, time travel, and efficient MERGE operations, making it ideal for preserving history while optimizing queries for the latest state via partitioning or Z-ordering on the current flag. This satisfies both the auditing requirement and query performance for the latest state.

Exam trap

DP-600 often tests whether candidates confuse SCD Type 1 (no history) with SCD Type 2 (full history), so the trap is picking Type 1 when the requirement explicitly mentions historical versions.

How to eliminate wrong answers

Option A is wrong because overwriting the Silver table daily destroys historical data, violating the auditing requirement. Option B is wrong because SCD Type 1 overwrites existing records and does not preserve history. Option D is wrong because creating a new table for every refresh leads to table sprawl, complex querying, and no unified latest-state view.

64
Multi-Selecthard

You are using a Fabric notebook to ingest data from multiple CSV files in a Lakehouse Files area into a Delta table. The files are updated daily, and you need to perform an incremental load that only processes new or changed files. You also need to ensure that the load is idempotent to avoid duplicates if the notebook is rerun. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Use the COPY INTO command with a file pattern to load all files each time.
B.Implement a watermark table that stores the last processed file timestamp.
C.Use the MERGE statement to upsert new records based on a business key.
D.Enable change data feed on the target Delta table.
E.Partition the Delta table by the ingestion date.
AnswersB, C

A watermark table tracks the last processed file or timestamp, so on subsequent runs, you can filter to only new or changed files. This reduces processing time and avoids reprocessing all files. It complements the MERGE by providing the incremental file list. Without it, you would have to scan all files each run, which is inefficient for daily updates.

Why this answer

To achieve incremental and idempotent loading, you need a mechanism to identify new or changed files, such as a watermark table that records the last processed timestamp. Additionally, you need an upsert operation like MERGE to handle inserts and updates without creating duplicates. Together, these ensure that each file is processed once and that reruns do not duplicate data.

The other options do not provide both incremental file detection and idempotency.

Exam trap

The trap here is assuming that partitioning or change data feed alone can provide idempotency; they do not track source file changes or prevent duplicate loads.

65
MCQmedium

You are using a Fabric notebook to read a large Parquet file from the Lakehouse Files section and write it to a Delta table. You need to optimize the write operation to handle the large file efficiently. Which Spark setting should you configure?

A.Set 'spark.sql.files.maxPartitionBytes' to a higher value to reduce the number of partitions.
B.Use the 'partitionBy' option in the DataFrame writer to partition the Delta table by a low-cardinality column.
C.Use the 'optimizeWrite' option in the Delta writer to enable optimized write.
D.Set 'spark.sql.shuffle.partitions' to a higher value to increase parallelism during the write.
AnswerC

The 'optimizeWrite' option in Delta Lake (available in Fabric) enables optimized write, which reduces the number of small files by coalescing partitions and balancing file sizes. This is specifically designed to improve write performance for large datasets written to Delta tables. It automatically adjusts the number of files based on the data size, leading to more efficient writes and better downstream read performance. This directly addresses the need to handle a large file efficiently.

Why this answer

For large writes to Delta tables, enabling 'optimizeWrite' is the recommended approach. It dynamically optimizes file sizes and reduces the number of small files, which is crucial for performance. Other settings like 'maxPartitionBytes' affect reads, 'shuffle.partitions' can cause small files, and partitioning is for query optimization, not write.

Thus, 'optimizeWrite' is the correct choice to handle the large file efficiently.

Exam trap

The trap here is confusing read optimization settings with write optimization, or thinking that partitioning improves write speed.

66
MCQmedium

You have a large Delta table in a Fabric Lakehouse that is experiencing performance degradation. You observe that the table contains thousands of small files. What command should you use to optimize the table?

A.VACUUM
B.OPTIMIZE
C.REORG TABLE
D.ANALYZE TABLE
AnswerB

The OPTIMIZE command is specifically designed to compact small files into larger, more efficient Parquet files. This process significantly improves read performance for analytical queries by reducing the I/O overhead associated with managing a massive number of small file objects within the underlying storage layer of the Delta table.

Why this answer

The OPTIMIZE command in Delta Lake compacts small files into larger ones, improving read performance for large Delta tables with thousands of small files. It is the standard command to address the small-file problem in a Fabric Lakehouse.

Exam trap

DP-600 often tests the confusion between OPTIMIZE (compaction for performance) and VACUUM (cleanup for storage), since both are Delta Lake maintenance commands but serve different purposes.

How to eliminate wrong answers

Option A is wrong because VACUUM removes old, unreferenced files to reclaim storage, but it does not compact small files or improve read performance. Option C is wrong because REORG TABLE is not a Delta Lake command; it is used in other databases (e.g., DB2) for reorganization. Option D is wrong because ANALYZE TABLE collects statistics for the query optimizer, which can help with planning but does not compact small files.

67
MCQmedium

Refer to the exhibit. You are configuring a pipeline to load data from RawStorage to a Bronze Lakehouse. The current configuration results in slow query performance when reading from the Lakehouse. What change should you make to the configuration?

A.Change the compression codec to GZIP.
B.Change the file format to CSV.
C.Enable partitioning by a frequently filtered column.
D.Increase the source storage tier performance.
AnswerC

Partitioning organizes data into directory structures based on specific column values. By enabling this, Spark can skip entire directories that do not match the query filters, significantly reducing I/O. This is a standard best practice for optimizing performance in data lakes, particularly when working with large volumes of time-series data.

Why this answer

Enabling partitioning by a frequently filtered column (C) is the correct optimization because partition pruning allows the query engine to skip entire partitions that do not match the filter, dramatically reducing the amount of data scanned. This directly addresses slow read performance on a Lakehouse table when queries consistently filter on a low-cardinality column like date or region.

Exam trap

The trap is assuming that 'faster storage' or 'better compression' fixes slow queries — candidates pick D or A because they sound like performance improvements, but the exam wants you to recognize that partitioning (data layout) is the highest-impact fix for filtered reads.

How to eliminate wrong answers

Option A is wrong because changing compression to GZIP reduces file size but does not improve query performance — in fact, GZIP decompression is CPU-intensive and can slow reads compared to Snappy, which is the Delta default. Option B is wrong because switching to CSV is a regression: CSV is row-based, uncompressed-friendly, and lacks columnar pruning, making queries far slower than Parquet/Delta. Option D is wrong because increasing source storage tier performance addresses raw I/O throughput but does not fix the fundamental issue of scanning unpartitioned data — it is a costly workaround, not a design fix.

68
MCQmedium

You are building a Fabric data pipeline that reads JSON files from OneLake. The files contain nested arrays and you need to flatten them into a tabular structure before loading into a Lakehouse table. You want to minimize custom code and use built-in capabilities. What should you use?

A.Write a PySpark script in a Fabric notebook that uses explode() on nested arrays.
B.Use a Dataflow Gen2 with Power Query transformations to expand nested arrays into rows.
C.Use the Copy activity with a JSON source and map the nested arrays to multiple destination columns.
D.Use the Copy activity with a JSON source and enable 'Flatten hierarchy' in the source settings.
AnswerB

Dataflow Gen2 provides Power Query transformations that can expand nested arrays and records into flat tables without writing code. The 'Expand' operation on a column containing lists or records can be applied repeatedly to fully flatten the structure, and the output can be written directly to a Lakehouse table, meeting the requirement for minimal custom code.

Why this answer

Dataflow Gen2 with Power Query is the built-in, low-code tool in Fabric for transforming data, including flattening nested JSON. The expand operation on list or record columns can be applied iteratively to produce a flat table, and the result can be loaded into a Lakehouse table. This satisfies the need for a tabular structure with minimal custom code.

Exam trap

The trap here is assuming that the Copy activity's flatten hierarchy option fully flattens nested arrays into rows, when it only removes top-level hierarchy.

69
MCQeasy

Which component in Fabric is responsible for orchestrating the overall data pipeline, including moving data and running transformations?

A.Lakehouse
B.Data Pipeline
C.SQL Analytics Endpoint
D.Semantic Model
AnswerB

Fabric Data Pipelines provide the framework for orchestrating complex workflows. They integrate activities like Copy Data, Spark Notebooks, and Dataflows into a single, managed process. This is the correct tool for building automated data movement and transformation pipelines that are scalable and easy to monitor in a production environment.

Why this answer

Fabric Data Pipelines serve as the central orchestration engine. They allow you to schedule, monitor, and chain together various activities such as copying data from sources, running Spark notebooks, or executing Dataflows. By managing the workflow dependencies, Pipelines ensure that data is prepared in the correct sequence, providing a unified and reliable way to automate end-to-end data integration tasks within the Microsoft Fabric ecosystem.

Exam trap

Candidates often confuse individual transformation tools like Dataflows or Spark with the master orchestration component responsible for managing workflow dependencies across the entire lifecycle.

70
MCQhard

You are implementing a Medallion architecture in Microsoft Fabric. You have a requirement to ensure that only cleaned, validated data reaches the Silver layer. Which tool is best suited for this gatekeeping process?

A.Copy Activity
B.Dataflow Gen2
C.Shortcut creation
D.Warehouse SQL endpoint
AnswerB

Dataflow Gen2 provides a robust, no-code/low-code interface to perform data transformation, cleansing, and validation. You can build logic that checks for data quality issues and filters out invalid records, ensuring that only data meeting your standards is written into the Silver layer of the Lakehouse.

Why this answer

Dataflow Gen2 is the best suited tool among the options because it provides a low-code/no-code interface for data transformation, cleansing, and validation (Power Query) before loading data into the Silver layer. While Spark Notebooks are also valid, they are not listed as an option. The explanation should clarify that Dataflow Gen2's built-in transformation capabilities make it the primary choice for this gatekeeping process in Fabric.

Exam trap

Candidates might confuse the Silver layer's purpose with simple data movement (Copy Activity) or storage (Shortcuts), ignoring the requirement for 'cleaned, validated data'.

71
MCQhard

Refer to the exhibit. You are implementing a partitioning strategy for a table with 5 years of daily transaction data. The 'TransactionDate' column is a timestamp. What is the most likely performance issue with this code as written?

A.The 'append' mode will fail if the table exists
B.Over-partitioning will create too many small files
C.Delta format does not support the 'partitionBy' method
D.The SQL endpoint cannot read partitioned Delta tables
AnswerB

Partitioning by a daily date over five years creates approximately 1,825 partitions. If each daily load is small, this results in thousands of tiny files. Spark and the SQL analytics endpoint struggle with this because the metadata management and file I/O overhead become the primary bottleneck for every query.

Why this answer

Partitioning a table with 5 years of daily transaction data by a timestamp column (e.g., TransactionDate) without additional granularity control will create thousands of partitions (one per day), leading to over-partitioning. This results in a large number of small files, which degrades read performance due to metadata overhead and inefficient file scanning.

Exam trap

DP-600 often tests the misconception that partitioning on any column improves performance, when high-cardinality columns like timestamps cause over-partitioning and small-file problems.

How to eliminate wrong answers

Option A is wrong because 'append' mode is the correct mode for adding data to an existing Delta table and will not fail if the table exists; it is designed for that purpose. Option C is wrong because Delta Lake fully supports the partitionBy method in Spark. Option D is wrong because the SQL endpoint in Fabric can read partitioned Delta tables without issue.

72
MCQmedium

Your organization is implementing a medallion architecture in Microsoft Fabric. You need to ingest raw CSV data from an Azure Data Lake Storage (ADLS) Gen2 account into a Lakehouse. The data requires complex transformations, including pivoting and multi-level joins, before it is ready for the Silver layer. Which tool should you use to provide a low-code environment for these transformations?

A.Data Factory Pipelines with a Copy activity
B.Dataflows Gen2
C.T-SQL Stored Procedures in a Warehouse
D.Spark Job Definitions
AnswerB

Dataflows Gen2 utilize the Power Query Online engine to offer extensive transformation capabilities through a graphical interface. This tool supports sophisticated operations like pivoting columns and joining multiple tables, which are essential for cleaning and restructuring raw data into a usable format for the Silver layer of a Lakehouse.

Why this answer

Dataflows Gen2 in Microsoft Fabric provides a low-code, Power Query-based environment for ingesting and transforming data, including complex operations like pivoting and multi-level joins, before loading it into a Lakehouse. It is purpose-built for citizen integrators and analysts who need visual transformation capabilities without writing Spark or T-SQL code. This aligns with the requirement for a low-code environment to prepare raw CSV data for the Silver layer.

Exam trap

The trap is assuming that Data Factory Pipelines with Copy activity can handle complex transformations — candidates often conflate data movement with data transformation, but Copy activity is strictly for movement, and complex pivots/joins require a transformation engine like Dataflows Gen2 or Spark.

How to eliminate wrong answers

Option A is wrong because Data Factory Pipelines with a Copy activity only move data from source to destination without performing complex transformations like pivoting or multi-level joins — Copy activity supports only basic mapping and type conversions. Option C is wrong because T-SQL Stored Procedures in a Warehouse require writing code and are not a low-code environment; they also operate on warehouse tables, not directly on raw files in a Lakehouse. Option D is wrong because Spark Job Definitions require writing PySpark or Scala code, which is code-first, not low-code, and is better suited for advanced engineering scenarios rather than the visual transformation requirement described.

73
MCQeasy

A data engineer needs to quickly transform a small Excel file stored in OneLake and load it into a Lakehouse table. The engineer prefers a visual, no-code interface. Which Fabric component is most appropriate?

A.Spark Job Definition
B.Dataflows Gen2
C.Fabric Warehouse
D.KQL Database
AnswerB

Dataflows Gen2 leverage the familiar Power Query interface, allowing users to perform complex data preparation tasks using a ribbon-based UI. This is the primary 'no-code' tool in Fabric for ingesting and transforming data, making it perfectly suited for an engineer looking for a visual way to handle Excel data.

Why this answer

Dataflows Gen2 provide a Power Query-based visual interface that is perfect for no-code transformations. It allows users to connect to sources like OneLake, apply steps to clean and reshape the data, and then define a destination like a Lakehouse table, making it the ideal choice for engineers who prefer visual tools.

Exam trap

Candidates often select 'Data Pipelines' or 'Notebooks'. While these can move data, they are not the preferred 'no-code' visual tool for transforming small Excel files like Dataflows Gen2.

74
MCQmedium

Refer to the exhibit. You are configuring a copy activity to ingest JSON data into a Delta table. You notice that the target table schema is not reflecting the nested structure of the JSON correctly. What should you do to fix this?

A.Change the encoding to ASCII.
B.Define an explicit mapping or schema in the copy activity.
C.Use a Data Flow instead of a Copy Activity.
D.Convert the JSON to CSV before ingestion.
AnswerB

By explicitly defining the schema, you override the default inference behavior, allowing you to correctly define how nested JSON fields are mapped to columns, Structs, or Arrays in the destination Delta table. This ensures the data structure is preserved accurately and is readily queryable by analytical engines.

Why this answer

When a Copy Activity ingests JSON with nested structures into a Delta table, the default schema inference may flatten or mis-map nested objects, causing the target schema to be incorrect. Defining an explicit mapping (or providing an explicit schema) in the Copy Activity's mapping tab forces the correct column-to-field correspondence, including nested paths, so the Delta table reflects the JSON hierarchy properly.

Exam trap

The trap is to blame the activity type or encoding when the real issue is schema mapping — nested JSON almost always requires explicit mapping, and candidates often overlook the mapping tab in the Copy Activity.

How to eliminate wrong answers

Option A is wrong because changing the encoding to ASCII affects character representation, not schema structure — it will not fix nested JSON mapping. Option C is wrong because switching to a Data Flow is a heavier redesign; while Data Flows can handle complex transformations, the immediate fix for a mapping issue is to define explicit mapping in the Copy Activity, not to replace the activity type. Option D is wrong because converting JSON to CSV would flatten the nested structure entirely, losing the hierarchy the target schema needs.

75
MCQmedium

You are designing a data ingestion pipeline in Microsoft Fabric. You need to ingest data from an on-premises SQL Server database into a Lakehouse. Which integration tool should you use to ensure secure, scalable connectivity while minimizing administrative overhead?

A.DirectQuery in Power BI Desktop
B.Fabric Data Factory Copy Activity
C.Notebook using Spark read.jdbc
D.Azure Data Box
AnswerB

The Copy Activity within a Fabric Data Factory pipeline is specifically designed for hybrid data movement. By pairing this with an On-premises Data Gateway, you can securely move data from SQL Server to a OneLake-based Lakehouse, providing the necessary scalability and management features for enterprise-grade ingestion pipelines.

Why this answer

Fabric Data Factory's Copy Activity is the native ingestion mechanism for moving data from on-premises sources into a Lakehouse, using an on-premises data gateway for secure connectivity without exposing the SQL Server to the internet. It provides a managed, scalable, low-code pipeline experience that minimizes administrative overhead compared to custom code or hardware shipping. This is the recommended pattern for batch ingestion into OneLake.

Exam trap

DP-600 often tests whether candidates confuse Power BI query modes (DirectQuery, Import) with data ingestion tools, or over-engineer with notebooks when a managed Copy Activity is the intended low-overhead answer.

How to eliminate wrong answers

Option A is wrong because DirectQuery is a Power BI query mode for live semantic model connectivity, not a data ingestion tool — it does not copy data into a Lakehouse. Option C is wrong because while a Spark notebook using read.jdbc can technically pull data, it requires custom code, credential management, and cluster lifecycle handling, increasing administrative overhead and reducing scalability compared to a managed Copy Activity. Option D is wrong because Azure Data Box is a physical appliance for one-time bulk offline transfers of very large datasets (typically TB/PB scale), not a solution for ongoing pipeline ingestion from an operational SQL Server.

Page 1 of 2 · 101 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Prepare Data questions.