Courseiva

CCNA Prepare Data Questions

26 of 101 questions · Page 2/2 · Prepare Data · Answers revealed

76
MCQmedium

You are designing a medallion architecture in Microsoft Fabric. Which THREE tasks are essential for the 'Silver' layer processing?

A.Perform initial ingestion of raw files from the source system.
B.Deduplicate records based on primary key identifiers.
C.Validate and cast data types to adhere to the schema.
D.Apply final business-level aggregations for BI reports.
E.Standardize date formats and unit conversions across sources.
AnswerB, C, E

Deduplication is a core function of the Silver layer. By removing redundant entries, the system ensures that analytical queries return accurate results. This process is vital for maintaining data quality and preventing inflated metrics, which are common issues when raw source data contains overlapping or multiple records for the same entity.

Why this answer

The Silver layer serves as the refined, validated, and conformed data store. Essential tasks include deduplicating records to ensure uniqueness, enforcing data types to ensure schema consistency, and applying business logic to standardize formats across different source systems. These steps bridge the gap between raw data and analytical readiness, ensuring that downstream consumers can trust the data without performing repetitive, complex cleaning operations on every single query or report.

Exam trap

Candidates often confuse Bronze layer ingestion tasks with Silver layer processing, incorrectly including raw data preservation or appending uncleaned logs as primary Silver layer responsibilities.

77
MCQmedium

You are using a Fabric pipeline to copy data from an on-premises Oracle database to a Lakehouse. The pipeline uses a self-hosted integration runtime. You need to ensure that the copy activity only transfers rows modified in the last 24 hours to reduce load. Which pipeline component should you configure?

A.In the pipeline, add a 'Filter' activity after the copy activity to remove old rows.
B.In the source dataset, set the 'Partition option' to 'Dynamic range'.
C.In the source dataset, set a query with a WHERE clause filtering on a timestamp column.
D.In the copy activity, set the 'Enable staging' option to true.
AnswerC

The source dataset in a copy activity can be configured to use a query instead of a table. By specifying a SQL query with a WHERE clause that filters on a timestamp column (e.g., WHERE LastModified >= DATEADD(day, -1, GETDATE())), you ensure that only recent rows are retrieved. This is the most direct way to implement incremental loading without additional pipeline logic. It reduces the data transferred and processed.

Why this answer

To transfer only rows modified in the last 24 hours, the most efficient method is to filter at the source by specifying a query in the source dataset. This reduces the amount of data read from Oracle and transferred through the self-hosted integration runtime. Options like staging, partitioning, or post-copy filtering do not achieve the selective extraction and may increase load.

Thus, using a source query with a WHERE clause is correct.

Exam trap

The trap here is thinking that post-copy filtering or staging reduces source load, when actually the filtering must happen at the source to be effective.

78
MCQhard

You are creating a Fabric pipeline that uses a Copy activity to load data from an on-premises Oracle database into a Lakehouse. The Oracle source has a column of type TIMESTAMP WITH TIME ZONE. You need to preserve the time zone information in the Lakehouse. Which data type should you map the source column to in the Lakehouse table?

A.datetime2
B.string
C.datetimeoffset
D.datetime
AnswerC

datetimeoffset stores date and time along with the time zone offset, preserving the original time zone information from Oracle's TIMESTAMP WITH TIME ZONE. This ensures that the data remains accurate when analyzed across different time zones, which is essential for global reporting.

Why this answer

To preserve time zone information from Oracle's TIMESTAMP WITH TIME ZONE, map it to datetimeoffset in the Lakehouse. This data type stores the offset and ensures temporal accuracy across regions, which is vital for global data analysis.

Exam trap

The trap here is assuming that datetime2 or datetime can store time zone offsets; they cannot, and only datetimeoffset preserves the offset.

79
MCQmedium

Refer to the exhibit. You are reviewing a JSON configuration for a Fabric item. What is the primary outcome of deploying this configuration in a Microsoft Fabric workspace?

A.A new Data Factory pipeline is created to move data
B.A virtualization link is created to ADLS Gen2 data
C.The LKH_Finance Lakehouse is deleted and recreated
D.Data is physically copied to the ExternalSales folder
AnswerB

The JSON configuration defines a shortcut that points to a specific ADLS Gen2 path and maps it into the 'Files' section of a Lakehouse. This allows users to interact with the external 'sales' data as if it were local to the Fabric Lakehouse, fulfilling the definition of a virtualization link.

Why this answer

The configuration creates a shortcut (virtualization link) in the Fabric workspace that points to existing ADLS Gen2 data without copying it. This is the OneLake shortcut feature, which lets Fabric items reference external storage in place. Because no data movement occurs, the outcome is a virtualized reference rather than a pipeline or physical copy.

Exam trap

The trap is confusing 'shortcut' with 'copy' — candidates assume any Fabric configuration that references external data must move it, but OneLake shortcuts are explicitly zero-copy virtualization links.

How to eliminate wrong answers

Option A is wrong because no Data Factory pipeline is defined — a shortcut is a metadata pointer, not an orchestration activity. Option C is wrong because the configuration does not delete or recreate the LKH_Finance Lakehouse; it only adds a shortcut reference. Option D is wrong because OneLake shortcuts do not physically copy data — the ExternalSales folder remains in its original ADLS Gen2 location and is accessed via the shortcut.

80
MCQhard

Refer to the exhibit. You are processing data in a Spark Notebook. The schema of the incoming data does not contain the 'date_col' column, but your downstream code expects it. What is the most efficient way to handle this?

A.Alter the source system to include the column.
B.Use df.withColumn('date_col', lit(None)) to add the missing column.
C.Re-run the ingestion pipeline until the column appears.
D.Drop all existing columns and recreate the DataFrame.
AnswerB

Adding a null-filled column dynamically ensures that the DataFrame schema aligns with the downstream expectations. Using 'lit(None)' allows the pipeline to continue execution without errors, providing a placeholder that can be filled later if needed, or simply maintaining the schema contract required by downstream transformation or load processes.

Why this answer

In Spark, when a source DataFrame lacks a column that downstream code expects, the most efficient fix is to add the column in-memory using withColumn with lit(None), which creates a nullable column without touching the source or re-reading data. This preserves the existing schema and satisfies downstream expectations with minimal overhead.

Exam trap

DP-600 often tests the confusion between fixing data at the source versus handling schema mismatches in the notebook, where the efficient in-memory fix is preferred over pipeline re-runs or source changes.

How to eliminate wrong answers

Option A is wrong because altering the source system is an external change outside the Spark job's control and does not solve the immediate processing problem. Option C is wrong because re-running the ingestion pipeline will not make a missing column appear if the source schema genuinely lacks it, and it wastes compute. Option D is wrong because dropping and recreating the DataFrame discards all existing data and is destructive and unnecessary when a simple column addition suffices.

81
MCQmedium

Refer to the exhibit. You are using a notebook to read data from a source SQL database. To ensure the read operation is parallelized, what technique should you apply?

A.Set the fetch size to 1.
B.Use the partitionColumn, lowerBound, upperBound, and numPartitions options.
C.Run the query inside a single threaded loop.
D.Increase the executor memory to infinite.
AnswerB

These options are specifically designed to enable parallel reading from JDBC sources. By defining a column to split the data on and providing range bounds, the Spark SQL connector can issue multiple concurrent queries to the source database, each retrieving a distinct subset of the data based on the partition column.

Why this answer

Reading data in parallel is essential for performance when dealing with large SQL tables. By specifying partition columns and boundaries in the Spark read configuration, you instruct the engine to split the data into multiple chunks that can be fetched concurrently by different executors. This significantly reduces the total read time compared to a single-threaded read, which would otherwise become a major bottleneck in the ETL pipeline.

Exam trap

Many candidates incorrectly suggest increasing the number of executors or worker nodes, overlooking that parallel reading from a single source requires specific partition parameters to split the query workload.

82
MCQmedium

You are building a Fabric Dataflow Gen2 to ingest a large CSV file from Azure Blob Storage into a Lakehouse. The source file contains customer records, and you must ensure that the Email column is populated and follows a valid format before loading. You need to implement a data quality check that prevents invalid rows from reaching the destination. What should you configure in the Dataflow Gen2?

A.Add a conditional column that replaces invalid emails with a default value.
B.Configure a data destination that maps only the Email column and ignores other columns.
C.Use the Split column transformation to separate the Email column into multiple columns based on a delimiter.
D.Apply a filter on the Email column to keep only rows that match a valid email pattern.
AnswerD

Filtering the Email column to retain only rows that match a valid email pattern ensures that rows with missing or malformed emails are excluded before the data is written to the Lakehouse. This satisfies the requirement to prevent invalid rows from reaching the destination and is a straightforward data quality enforcement step within Dataflow Gen2.

Why this answer

To prevent invalid rows from reaching the Lakehouse, you must filter the data so that only rows meeting the email validity condition are passed downstream. Filtering on the Email column with a pattern match removes records that are missing or malformed, ensuring only valid data is loaded. Other transformations either alter values or restructure columns without excluding invalid rows.

Exam trap

The trap here is assuming that transforming or replacing invalid values is equivalent to filtering them out, when the requirement explicitly demands that invalid rows must not reach the destination.

83
MCQeasy

You have a CSV file with headers in the first row. Using a Spark notebook in Fabric, which code snippet correctly reads this file into a DataFrame?

A.spark.read.csv(path)
B.spark.read.option('header', 'true').csv(path)
C.spark.read.option('header', 'true').option('inferSchema', 'true').csv(path)
D.spark.load.csv(path, header=True)
AnswerC

This approach correctly interprets the header and automatically assigns the appropriate data types to the DataFrame. This is the optimal starting point for data preparation in Fabric, as it produces a structured, clean dataset that the Spark engine can process efficiently without further manual intervention.

Why this answer

The provided options list A, B, and D as 'wrong' and C as 'CORRECT', but the options themselves are valid Spark code snippets. Option B is technically correct for reading a CSV with headers, and Option C is also correct (and more robust). Labeling valid code as 'wrong' in the options list is misleading for a certification question.

Exam trap

Candidates often forget that 'inferSchema' is not required to read a file, but it is best practice for data exploration.

84
MCQmedium

Refer to the exhibit. You are running a Spark job to load sales data. The 'overwrite' mode is set. What is the impact of this operation on the target table?

A.Only new rows are appended to the existing data.
B.The entire existing table content is replaced by the new data.
C.The job will fail if the table already exists.
D.Only rows with matching keys are updated.
AnswerB

In Spark, 'overwrite' mode drops the existing table or partition and replaces it with the new incoming dataset. This is a destructive operation that should only be used when the entire dataset is being refreshed from the source, as it removes any pre-existing records that were not included in the new load.

Why this answer

Using 'overwrite' mode replaces the entire content of the table with the new dataset. While effective for full refreshes, it should be used cautiously in production as it destroys historical data stored within that table. It is essential to ensure that this is the intended behavior and that any necessary historical data has been archived or that the table is intended to be a complete snapshot, not a cumulative store.

Exam trap

Candidates mistakenly assume 'overwrite' mode appends new rows or only updates changed records, forgetting that it completely drops and replaces the entire target table content.

85
MCQmedium

You are using Fabric Dataflows Gen2 to ingest data. You notice that your Dataflow is slow. You want to see if the Dataflow is executing 'Mashup' queries on the source or the Fabric engine. Which tool should you use?

A.The Spark Job Monitor
B.The Query Plan visualizer
C.The SQL Profiler
D.The Pipeline Run history
AnswerB

The Query Plan in Dataflow Gen2 allows you to see the execution details of your transformations. It explicitly highlights where query folding is happening and where it is breaking. This is the primary tool for tuning Dataflow performance, allowing you to optimize your data preparation for maximum speed and efficiency.

Why this answer

In Dataflow Gen2, the 'View Query Plan' feature is the standard way to inspect how the Mashup engine is processing transformations. It shows whether query folding is occurring, which is vital for performance. By checking the query plan, you can identify steps that are not folding and adjust your logic, such as ensuring data types match or avoiding non-foldable functions, to ensure the source system does the heavy lifting.

Exam trap

Candidates often look at job run logs or resource monitors instead of the specialized tool designed specifically to inspect Mashup engine transformations and query folding behavior.

86
MCQhard

You are using a Fabric notebook to read a large Delta table from a Lakehouse. You need to minimize the amount of data scanned and improve query performance when filtering on a high-cardinality column named TransactionID. Which technique should you use?

A.Use the VACUUM command with a retention period of 7 days.
B.Apply Z-ORDER BY on the TransactionID column during an OPTIMIZE operation.
C.Partition the Delta table by the TransactionID column.
D.Convert the Delta table to a Parquet table and enable predicate pushdown.
AnswerB

Z-ORDER BY sorts data within files based on the specified column, clustering related values together. For high-cardinality columns like TransactionID, this improves data skipping because the query engine can use min/max statistics to skip files that do not contain the filtered values. This directly reduces the amount of data scanned. Therefore, it is the correct technique.

Why this answer

Applying Z-ORDER BY on the TransactionID column during OPTIMIZE clusters data to improve data skipping. For high-cardinality columns, this is more effective than partitioning, which would create too many small files. Z-ORDER BY enables the query engine to skip irrelevant files using min/max statistics, reducing data scanned.

Other options like VACUUM or converting to Parquet do not provide this benefit.

Exam trap

The trap here is partitioning by a high-cardinality column; this creates many small partitions and hurts performance instead of helping.

87
MCQmedium

You have a Fabric Lakehouse with a Delta table named Sales. The table receives frequent updates, and you notice that query performance has degraded over time. You need to improve read performance by optimizing the physical layout of the data files without changing the table schema. Which action should you perform?

A.Convert the Delta table to a Parquet table using the OPTIMIZE command.
B.Run VACUUM on the Delta table with a retention period of 0 hours.
C.Repartition the Delta table by using the REORG TABLE command.
D.Run OPTIMIZE on the Delta table with Z-ORDER BY on a frequently filtered column.
AnswerD

OPTIMIZE compacts small files and Z-ORDER BY co-locates related data, improving data skipping and read performance. This directly addresses the physical layout without altering the schema. The scenario specifies frequent updates, which create many small files; OPTIMIZE consolidates them. Z-ORDER BY on a commonly filtered column further enhances pruning. This is the recommended maintenance operation in Microsoft Fabric for Delta tables.

Why this answer

The correct action is to run OPTIMIZE with Z-ORDER BY on a frequently filtered column. Frequent updates create many small files, which degrade read performance. OPTIMIZE compacts these files into larger ones, and Z-ORDER BY clusters data to enable efficient data skipping.

This improves query speed without schema changes, aligning with the requirement to optimize physical layout in a Fabric Lakehouse.

Exam trap

The trap here is confusing VACUUM with performance optimization; VACUUM only removes old files and does not reorganize data for faster reads.

88
MCQmedium

You are creating a shortcut in a Microsoft Fabric Lakehouse to access data stored in an Azure Data Lake Storage (ADLS) Gen2 account. What is the primary benefit of using a shortcut instead of copying the data?

A.Shortcuts increase the query speed by caching data.
B.Shortcuts automatically convert files to Delta format.
C.Shortcuts avoid data duplication and synchronization overhead.
D.Shortcuts provide read/write access to all file types.
AnswerC

By linking to the original data in ADLS Gen2, shortcuts remove the need to physically copy or move files into the Lakehouse. This prevents data fragmentation, reduces storage consumption, and eliminates the need for orchestration to keep the mirrored data updated, making it a highly efficient architectural pattern for integration.

Why this answer

Shortcuts in a Microsoft Fabric Lakehouse are zero-copy references that point to data in external storage such as ADLS Gen2, S3, or another OneLake location. Because the data stays in its original location, there is no duplication of storage and no need to maintain ETL pipelines to keep a second copy synchronized. This makes shortcuts ideal for cross-workspace and cross-cloud data virtualization scenarios.

Exam trap

The trap here is confusing shortcuts with materialized copies or caching layers — candidates often assume shortcuts improve performance or transform data, when in fact their defining benefit is avoiding duplication and synchronization.

How to eliminate wrong answers

Option A is wrong because shortcuts do not cache data — they are metadata pointers, and query performance depends on the underlying storage and engine, not on any caching introduced by the shortcut itself. Option B is wrong because shortcuts do not transform or convert file formats; they surface files in their native format (Parquet, CSV, Delta, etc.), and format conversion requires an explicit notebook or data pipeline. Option D is wrong because shortcut access is read-only by default — you cannot write through a shortcut to the underlying external storage, and not all file types are equally supported for tabular consumption.

89
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 runtime component should you configure to ensure secure connectivity?

A.Azure Integration Runtime
B.Self-hosted Integration Runtime
C.Managed Virtual Network Integration Runtime
D.Power BI Gateway
AnswerB

The self-hosted integration runtime provides the necessary gateway to perform data integration activities within a private network. By installing it on a local machine, you enable the Fabric pipeline to securely access on-premises data sources without requiring inbound firewall changes, fulfilling the technical requirements for local environment connectivity.

Why this answer

To connect to on-premises data sources, you must install and configure a self-hosted integration runtime. This acts as a bridge between the local network and the Microsoft Fabric environment, allowing the pipeline to reach behind the firewall. This is critical for data security because it avoids opening inbound ports in your corporate firewall, relying instead on outbound communication over port 443 to the cloud service.

Exam trap

Candidates often confuse the 'Azure Integration Runtime' (for cloud-to-cloud) with the 'Self-hosted Integration Runtime' (for on-premises). Secure on-prem connectivity requires the latter to traverse firewalls.

90
Multi-Selectmedium

You are troubleshooting a slow-running Spark job in a Fabric notebook. Which TWO metrics should you analyze to identify potential bottlenecks in your data processing job?

Select 2 answers
A.Task duration distribution.
B.Shuffle read/write bytes.
C.Total number of files in the Lakehouse.
D.Workspace storage capacity.
E.The number of users logged into the workspace.
AnswersA, B

Task duration reveals skew; if one task takes significantly longer than others, it suggests that the data is unevenly distributed across partitions. This identifies the 'long tail' problem, where the entire job is held up by a single overloaded executor, indicating a need for better partitioning or data distribution strategies.

Why this answer

The Spark UI provides critical diagnostic information for understanding job performance. Analyzing task duration and data shuffle metrics allows you to pinpoint where the bottleneck lies. Skewed tasks and excessive data movement across the network are the two most common causes of performance degradation in distributed systems.

By identifying these issues, engineers can apply targeted optimizations like salting or broadcast joins to bring the job execution time within acceptable performance thresholds.

Exam trap

Candidates mistakenly focus on cluster-level CPU utilization instead of examining task-level execution metrics and shuffle bytes, which hide data skew and network bottlenecks.

91
Multi-Selectmedium

You are designing a data ingestion strategy from an on-premises SQL Server to a Microsoft Fabric Lakehouse. You want to utilize the 'Fast Copy' feature in Dataflows Gen2 to maximize throughput. Which TWO conditions must be met to enable this capability? (Select TWO)

Select 2 answers
A.The 'Enable staging' option must be selected for the dataflow.
B.The dataflow must use a Fabric Notebook as a destination.
C.The transformation steps must only include supported connectors and basic operations.
D.A Premium Capacity (P1 or higher) must be assigned to the workspace.
E.The source SQL Server must have the 'V-Order' property enabled.
AnswersA, C

Fast Copy relies on the Fabric Data Factory orchestration engine to move data directly. This process requires staging to be enabled, allowing the system to land data in a temporary storage area before final delivery, which facilitates the high-speed transfer bypass of the standard Mashup engine.

Why this answer

Fast Copy in Dataflows Gen2 allows for high-performance data movement by bypassing the standard Power Query engine for the actual data transfer. To trigger this, the data must not have complex transformations that require the Power Query engine, and staging must be enabled so the Data Factory orchestration engine can handle the movement efficiently.

Exam trap

Candidates frequently assume Fast Copy works automatically with any complex custom Power Query transformation step without requiring staging.

92
MCQhard

You are preparing data in a Microsoft Fabric Lakehouse. A Delta table named CustomerTransactions contains millions of rows and is frequently updated with new transactions and corrections to existing ones. You need to apply incremental updates from a staging table StagingTransactions that includes both new records and updates to existing records based on a TransactionID. You must ensure that the final table reflects the latest data without duplicates. Which approach should you use?

A.Use a Dataflow Gen2 to combine CustomerTransactions and StagingTransactions, remove duplicates based on TransactionID, and load the result to a new table.
B.Use a Spark notebook to perform a Delta MERGE operation, matching on TransactionID, updating existing rows and inserting new ones.
C.Use a Spark notebook to read StagingTransactions and write to CustomerTransactions with mode('append').
D.Use a Fabric pipeline with a Copy activity to copy StagingTransactions to CustomerTransactions, setting the sink to overwrite mode.
AnswerB

Delta MERGE is designed for upsert scenarios. It matches on a key, updates existing records, and inserts new ones, ensuring the target table reflects the latest state without duplicates. This is the correct and efficient way to handle incremental updates in a Fabric Lakehouse.

Why this answer

The Delta MERGE operation is the optimal solution for applying incremental updates, as it handles both inserts and updates based on a key. It ensures data consistency and avoids duplicates. Other methods like append, overwrite, or full reprocessing are either incorrect or inefficient for large, frequently updated tables.

Exam trap

The trap here is thinking that a Dataflow Gen2 or Copy activity can efficiently handle incremental updates, but they lack the merge capability and would require full table rewrites.

93
MCQmedium

You are designing a data pipeline that must handle late-arriving data. Which strategy ensures that your reports are consistent even when data arrives after the scheduled batch window?

A.Delete and recreate the entire Lakehouse daily.
B.Use event-time partitioning and idempotent writes.
C.Increase the timeout threshold of the pipeline.
D.Reject all records that arrive after the window.
AnswerB

Partitioning by event time allows late-arriving data to be inserted into the correct historical partition without affecting other time periods. Idempotent writes ensure that if the pipeline is re-run to process the late data, the existing data is not duplicated, maintaining the integrity and accuracy of the downstream analytical reports.

Why this answer

To handle late-arriving data, you should implement idempotent pipelines that support re-running or 'backfilling' data based on the business logic's event time rather than processing time. By using watermark-based processing in structured streaming or partitioning your lakehouse tables by the event date, you can ensure that late records are correctly appended to the historical partitions. This maintains reporting consistency because the data is attributed to the correct time period, regardless of when it arrived.

Exam trap

Candidates often suggest using 'Triggered' pipelines or simple batch retries, which do not address the fundamental issue of data arriving out of temporal order for reporting consistency.

94
MCQmedium

You are loading a large CSV file into a Fabric Lakehouse using a Dataflow Gen2. The CSV contains date columns stored as text in MM/dd/yyyy format. You need to ensure these columns are loaded into a Delta table as proper date types. Which transformation should you apply in the Dataflow Gen2 Power Query editor?

A.Use the 'Add Custom Column' transformation with Date.FromText([ColumnName]).
B.Use the 'Change Type' transformation and select 'Date' for the column.
C.Use the 'Replace Values' transformation to remove slashes and then convert to number.
D.Use the 'Split Column' transformation to separate day, month, and year into three columns.
AnswerB

The 'Change Type' transformation in Power Query explicitly converts the column to the Date data type, parsing the text based on the locale settings. For MM/dd/yyyy format, you must ensure the locale is set to English (United States) to avoid misinterpretation. This is the correct approach for loading dates into a Delta table with proper date types.

Why this answer

The Change Type transformation in Power Query is designed to convert column data types. For text dates in MM/dd/yyyy format, setting the data type to Date and ensuring the correct locale (e.g., English (United States)) will correctly parse the values. This results in a proper Date type in the Delta table, enabling date-based operations and correct storage.

Exam trap

The trap here is assuming that any date-like text will automatically convert to a Date type without considering locale or format, leading to errors or misinterpretation.

95
MCQmedium

Your team is using Microsoft Fabric to build a data platform. You need to provide a way for data scientists to explore raw data files in a Lakehouse using Python, while also allowing SQL developers to query the same data as tables. What is the most efficient way to achieve this?

A.Store the data in a Warehouse and use the Spark SQL connector.
B.Use a Lakehouse and save data in the Delta format in the 'Tables' section.
C.Create two copies of the data: one in OneLake for Python and one in Azure SQL for SQL.
D.Use Dataflows Gen2 to export data to a CSV file for each group.
AnswerB

Saving data as Delta tables in a Lakehouse provides the best of both worlds. Data scientists can use PySpark to read the Delta files directly, and the SQL analytics endpoint automatically exposes these Delta tables for SQL developers to query using standard T-SQL, all from a single copy of data.

Why this answer

In Microsoft Fabric, the Lakehouse item is designed for multi-engine access. Data scientists can use Spark and Python to interact with files, while the automatic discovery of Delta tables makes the same data available through the SQL analytics endpoint. This dual-access model promotes collaboration without data duplication.

Exam trap

Candidates often recommend duplicating data into separate storage accounts for Spark and SQL engines, missing the unified capability of Delta tables.

96
MCQeasy

You are using a Fabric Notebook to explore data. You want to view the distribution of a numeric column in your dataframe. Which tool provides the fastest way to visualize this without writing additional code?

A.The 'Chart' view in the DataFrame output.
B.The display() function with manual Matplotlib code.
C.Power BI Desktop integration.
D.Exporting to Excel for analysis.
AnswerA

The built-in Chart view in Fabric Notebooks provides an interactive, no-code interface for visualizing dataframe results. It supports various plot types, including histograms and box plots, making it the most efficient way to inspect the distribution of numeric columns during the data preparation and profiling phase.

Why this answer

The 'Chart' view in the DataFrame output in Fabric Notebooks provides a quick, no-code way to visualize data distributions. It automatically generates charts based on the data type, allowing you to explore numeric columns without writing additional code. This is the fastest method for initial data exploration.

Exam trap

DP-600 often tests the fastest no-code visualization method in notebooks, and candidates may overlook the built-in Chart view, opting for code-based solutions.

How to eliminate wrong answers

Option B is wrong because using display() with manual Matplotlib code requires writing code, which is not the fastest no-code option. Option C is wrong because Power BI Desktop integration involves exporting or connecting to Power BI, which is an external tool and not immediate within the notebook. Option D is wrong because exporting to Excel is a manual, time-consuming process and not integrated into the notebook environment.

97
Multi-Selectmedium

You are preparing data in a Fabric Lakehouse using Spark. You have a Delta table 'Sales' with columns 'ProductID', 'SaleDate', and 'Amount'. You need to optimize the table for queries that filter on 'SaleDate' and 'ProductID'. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Convert the Delta table to Parquet format.
B.Use Z-Ordering on the 'ProductID' column.
C.Partition the Delta table by the 'SaleDate' column.
D.Set the table property 'delta.autoOptimize.optimizeWrite' to true.
E.Enable change data feed on the Delta table.
AnswersB, C

Z-Ordering (or multi-dimensional clustering) on ProductID will co-locate related data in the same set of files, improving data skipping for queries that filter on ProductID. It is especially useful for high-cardinality columns like ProductID. Combined with partitioning by SaleDate, this provides efficient pruning for both columns. Z-Ordering is performed using the OPTIMIZE command with ZORDER BY.

Why this answer

To optimize a Delta table for queries filtering on SaleDate and ProductID, you should partition by SaleDate and apply Z-Ordering on ProductID. Partitioning enables partition pruning for date filters, while Z-Ordering co-locates data by ProductID to improve data skipping. These two actions together provide efficient filtering on both columns.

Enabling change data feed, converting to Parquet, or setting optimizeWrite do not directly address read performance for these filters.

Exam trap

The trap here is confusing write optimizations like optimizeWrite with read optimizations like partitioning and Z-Ordering.

98
MCQhard

You are cleaning data using a Dataflow Gen2 in Microsoft Fabric. You need to optimize the performance of the transformation process. Which TWO actions should you perform? Each correct answer represents a complete solution.

A.Disable query folding for all steps
B.Enable staging on intermediate queries
C.Ensure query folding is active
D.Increase the timeout settings for the data source
E.Convert all data types to Text before processing
AnswerB, C

Enabling staging allows Dataflow Gen2 to cache intermediate results. This is critical for complex pipelines because it prevents the engine from recalculating previous steps multiple times. By caching data at logical points, the overall pipeline performance improves, as downstream transformations operate on pre-processed, high-performance data rather than raw sources.

Why this answer

Optimizing Dataflow Gen2 performance involves minimizing the amount of data moved and shifting computation to the source or the engine level. By enabling query folding, the engine pushes transformations back to the source system, reducing memory usage in Fabric. Additionally, staging reduces the number of times data is read, allowing the engine to cache intermediate results efficiently for subsequent transformations, which significantly improves throughput during large-scale data cleansing operations.

Exam trap

Candidates often select caching or indexing options instead of enabling staging and query folding, confusing traditional relational database tuning with Fabric Dataflow Gen2 optimization mechanisms.

99
MCQmedium

You are building a Fabric Dataflow Gen2 that ingests a CSV file from a Lakehouse Files area. The file contains a column named 'TransactionDate' stored as text in the format 'MM/dd/yyyy'. You need to convert this column to a proper Date type to enable time intelligence in a downstream semantic model. Which Power Query M transformation should you apply?

A.Add a custom column with the formula Date.FromText([TransactionDate]) and then remove the original column.
B.Change the column data type to Date using the 'Using Locale' option with English (United States).
C.Change the column data type to Date directly from the Transform tab without specifying a locale.
D.Use the 'Replace Values' transformation to swap '/' with '-' and then change the type to Date.
AnswerB

The 'Using Locale' option ensures the text is parsed as MM/dd/yyyy, because the default locale might interpret it as dd/MM/yyyy. This is critical when the source format is ambiguous and the Fabric workspace locale differs. It converts the column to a true Date type, enabling correct time intelligence in the semantic model.

Why this answer

The correct transformation is to change the column type to Date while explicitly specifying the source locale (English (United States)). This ensures that the text 'MM/dd/yyyy' is parsed correctly regardless of the workspace or user locale. Other options either do not address locale ambiguity or rely on default settings that may misinterpret the date format, leading to incorrect data.

Exam trap

The trap here is assuming that changing the data type alone will correctly parse dates without considering the locale, which can silently swap month and day.

100
MCQeasy

You are building a Dataflow Gen2 that combines data from two Lakehouse tables: 'Sales' and 'Products'. You need to join them on 'ProductID' and keep all rows from 'Sales' even if there is no matching product. Which join kind should you select?

A.Inner join
B.Right outer join
C.Full outer join
D.Left outer join
AnswerD

A left outer join returns all rows from the left table (Sales) and matching rows from the right table (Products). If there is no match, the result contains nulls for the right table's columns. This exactly meets the requirement to keep all sales rows, even those without a matching product. It is the correct join kind for preserving the left table's data while enriching with product information when available.

Why this answer

To keep all rows from the Sales table while bringing in matching product details, a left outer join is the correct join kind. It ensures that no sales records are lost, and product information is added where available. Inner join would drop unmatched sales, right outer join would focus on products, and full outer join would include all products, which is not required.

Thus, left outer join is the suitable option.

Exam trap

The trap here is mixing up left and right outer joins, or thinking that full outer join is needed to keep all rows from one table.

101
MCQeasy

You need to access data stored in an Azure Data Lake Storage (ADLS) Gen2 account from a Microsoft Fabric Lakehouse without physically moving or copying the data. The goal is to minimize storage costs and maintain a single source of truth. Which feature should you implement?

A.Data Factory Pipelines
B.Mirroring
C.OneLake Shortcuts
D.V-Order Optimization
AnswerC

OneLake shortcuts enable live connections to external storage locations like ADLS Gen2 or Amazon S3. By using shortcuts, the data remains in its original location while appearing as a native table or folder within the Lakehouse. This eliminates the need for ingestion pipelines and prevents the accumulation of duplicate storage costs.

Why this answer

Shortcuts in Microsoft Fabric allow you to virtualize data from external sources like ADLS Gen2 directly into your Lakehouse. This approach avoids data duplication, reduces storage costs, and ensures that any updates in the source are immediately reflected in Fabric. It is a core feature for building a unified data mesh architecture without the overhead of ETL.

Exam trap

Candidates often suggest 'Mirroring' or 'Data Factory Copy Activity'. These methods physically move or replicate the data, failing the requirement to avoid copying and minimize storage costs.

← PreviousPage 2 of 2 · 101 questions total

Ready to test yourself?

Try a timed practice session using only Prepare Data questions.