DP-600 Prepare Data Practice Test 4 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Enable partitioning by a frequently filtered column.. The lack of partitioning leads to full table scans during queries, as Spark must read every file in the directory to filter data. Enabling partitioning, particularly on date or category columns, allows for partition pruning. This optimization significantly reduces the amount of data read, resulting in faster query execution times and lower costs for data retrieval in the Fabric Lakehouse environment.

Answer

Change the compression codec to GZIP.

Answer

Change the file format to CSV.

Answer

Increase the source storage tier performance.

Question 2

Your organization requires that all data loaded into a Fabric Lakehouse must be converted to Delta Lake format. You are currently using a pipeline that copies CSV files to the Lakehouse. How can you ensure the data is automatically stored as Delta tables?

Accepted Answer

Use a Notebook to read the CSV and write it to the Lakehouse using format('delta').. Fabric Lakehouses are built on Delta Lake. When you load data into a Lakehouse table, the system automatically uses Delta as the storage format. By ensuring the ingestion pipeline targets the Lakehouse table rather than raw file storage, you guarantee compatibility with ACID transactions, time travel, and schema enforcement. This is the standard operational pattern for maintaining reliable, high-performance data lakes within the Fabric environment.

Answer

Run a post-load script to convert files using the CONVERT TO DELTA command.

Answer

Configure the pipeline to save the files in the 'Files' section of the Lakehouse.

Answer

Manually rename the file extensions from .csv to .delta after the copy is complete.

Question 3

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

Accepted Answer

Deduplicate records based on primary key identifiers.. 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.

Answer

Perform initial ingestion of raw files from the source system.

Answer

Apply final business-level aggregations for BI reports.

Question 4

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?

Accepted Answer

OPTIMIZE. Small file syndrome is a common issue in Delta Lake that negatively impacts read performance because the storage engine must open, close, and metadata-check every individual file. The OPTIMIZE command compacts these small files into larger, more efficient files, which dramatically improves read performance. This maintenance task is crucial for long-term health of analytical tables as data grows or is updated incrementally over time.

Answer

VACUUM

Answer

REORG TABLE

Answer

ANALYZE TABLE

Question 5

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?

Accepted Answer

Use df.withColumn('date_col', lit(None)) to add the missing column.. Handling missing columns gracefully is essential for robust data pipelines. By using the 'withColumn' method with 'lit(None)' or by selecting a null constant as the missing column, you ensure the schema remains consistent for subsequent steps. This prevents the pipeline from failing when encountering data sources that might be missing specific fields, allowing for flexible and resilient schema evolution during the data ingestion and transformation phases.

Answer

Alter the source system to include the column.

Answer

Re-run the ingestion pipeline until the column appears.

Answer

Drop all existing columns and recreate the DataFrame.

Question 6

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

Accepted Answer

Fabric Data Pipeline. Fabric Pipelines allow for the orchestration of various activities, including running Notebooks, executing Dataflows, and managing data movement. They provide a visual interface for designing complex, repeatable workflows, including scheduling, dependency management, and error handling. This is the standard, enterprise-grade way to manage production data workloads within Fabric, ensuring that tasks run in the correct order and on a consistent schedule to meet business requirements.

Answer

SQL Server Agent

Answer

Power BI Desktop

Answer

DirectQuery

Question 7

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?

Accepted Answer

Spark Structured Streaming with watermarking and dropDuplicates.. When dealing with streaming data, deduplication is critical to prevent inaccurate analytical results caused by retries or network issues. Using the 'dropDuplicates' method in Spark structured streaming allows for efficient removal of redundant events based on specific identifiers within a given watermark period. This ensures that the downstream Lakehouse contains a clean, accurate representation of the event data, which is essential for reliable real-time analytics and monitoring.

Answer

A SQL stored procedure.

Answer

A Power Query filter in a Dataflow.

Answer

A standard SQL GROUP BY clause.

Question 8

You need to access data from an Azure Data Lake Storage Gen2 account in a Fabric Notebook. What is the most secure way to authenticate?

Accepted Answer

Configure Managed Identity for the Fabric capacity.. Using Managed Identity is the recommended authentication method in Azure and Fabric. It eliminates the need to store credentials in code or configuration files, significantly reducing security risks. By granting the Fabric resource access to the storage account via RBAC roles, you implement the principle of least privilege, ensuring that data is accessed only by authorized services without manual secret management.

Answer

Hardcode the account key in the Notebook code.

Answer

Use a SAS token with infinite expiration.

Answer

Store the connection string in a plain text file in the Lakehouse.

Question 9

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?

Accepted Answer

Implement retry policies for API connections.. Building resilient pipelines requires handling transient errors and ensuring that data is processed consistently. Implementing retry logic manages temporary network flickers, utilizing incremental loading minimizes the data volume transferred, and setting up error alerts ensures that developers are notified immediately if a failure occurs. These features combine to make the data ingestion process stable, efficient, and monitorable, which is critical for enterprise-grade data solutions that must run reliably on a daily basis.

Answer

Hardcode the API credentials in the Dataflow M-code.

Answer

Enable the 'Always-On' flag for the Dataflow runtime.

Question 10

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?

Accepted Answer

The entire existing table content is replaced by the new data.. 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.

Answer

Only new rows are appended to the existing data.

Answer

The job will fail if the table already exists.

Answer

Only rows with matching keys are updated.

Question 11

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?

Accepted Answer

Use a struct definition in the Spark read process.. Defining a schema explicitly in the Lakehouse ensures that incoming data adheres to specific data types and structures, preventing runtime errors during analysis. This practice is crucial for data governance and quality control, as it provides a clear contract that data producers must meet. By enforcing a schema, you ensure that downstream models, reports, and dashboards function reliably without unexpected type conversions or missing data issues.

Answer

Rely on Spark's schema inference.

Answer

Manually update the schema in the SQL endpoint after loading.

Answer

Convert all columns to the 'String' type.

Question 12

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?

Accepted Answer

Use multiple concurrent copy activities in a Fabric Pipeline.. Parallelization is the most effective way to reduce the duration of large data loads. By splitting the source data into smaller, independent chunks, you can process them concurrently across multiple Spark executors. This optimizes the utilization of the cluster's compute resources, significantly decreasing the total ingestion time and enabling the handling of much larger datasets within the required time constraints for daily reporting and analytics.

Answer

Reduce the executor count to save memory.

Answer

Force single-threaded execution to ensure data consistency.

Answer

Increase the timeout settings for the copy activity.

Question 13

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?

Accepted Answer

Load the CSV files into a Fabric Lakehouse table using the Delta format and enable V-Order optimization during write operations.. 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.

Answer

Keep the files in their original CSV format within the Lakehouse files section and query them directly using external tables.

Answer

Compress the CSV files into GZIP archives prior to ingestion and store them in the unstructured storage zone of the Lakehouse.

Answer

Convert the CSV files into uncompressed JSON format to preserve nested hierarchical structures before loading them into a managed table.

Question 14

Refer to the exhibit. You are optimizing a Lakehouse table for frequent analytical queries that filter by specific dates. Based on the configuration in the exhibit, which optimization strategy should you implement to improve query performance?

Accepted Answer

Implement Z-Order indexing on the date columns.. Using Parquet partitioning by Year, Month, and Day enables partition pruning, allowing the query engine to skip irrelevant data files. This significantly reduces I/O overhead for date-filtered queries. In Fabric, this strategy is foundational for high-performance Lakehouse analytics, as it aligns the physical storage layout with common business query patterns, ensuring that the engine only scans the specific files required for the requested date ranges.

Answer

Enable V-Order on the Delta table.

Answer

Convert the file format to CSV to increase compression.

Answer

Disable partitioning to reduce file metadata overhead.

Question 15

You are preparing a large dataset in a Fabric Lakehouse using PySpark. You notice significant data skew on the 'CustomerID' column, causing some executors to run much longer than others. Which TWO actions will help resolve the skew during a join operation?

Accepted Answer

Broadcast the smaller table involved in the join.. Addressing data skew is vital for maintaining performance in distributed Spark jobs. Skew occurs when one or few partitions hold significantly more data than others, leading to executor bottlenecks. By implementing techniques like salting or broadcast joins, you force Spark to distribute the workload more evenly, preventing the 'long tail' effect where one task waits for a single overloaded executor to finish its processing before the entire job proceeds.

Answer

Increase the number of partitions for the entire dataframe.

Answer

Reduce the spark.sql.shuffle.partitions configuration.

Answer

Enable AQE and increase the shuffle partitions.