DP-700 Ingest and Transform Data Practice Test 2 — 15 Questions

Question 1

Refer to the exhibit. The Copy activity fails to infer the schema correctly from the source files. What is the most likely cause?

Accepted Answer

The sink type is set to DeltaSink, which requires a pre-defined schema mapping for autoCreate.. The exhibit shows a standard copy activity without a defined dataset mapping or schema definition. When using 'autoCreate' for a Delta sink, Fabric requires a schema definition or an existing mapping to determine column types correctly. Without it, the sink assumes all data is string-based, which often leads to errors when writing to strictly typed Delta tables or results in poor query performance.

Answer

The recursive flag is set to true, causing the sink to attempt to merge multiple schemas.

Answer

AzureBlobFSReadSettings does not support schema inference from delimited files.

Answer

The Copy activity requires an Execute Pipeline activity to initialize the sink.

Question 2

You need to perform a complex transformation that involves multiple joins across tables of varying sizes in Fabric. Which tool provides the best balance between performance and ease of use?

Accepted Answer

Fabric Dataflow Gen2.. Dataflows Gen2 are specifically designed for low-code ETL, providing a visual interface that translates into efficient Spark jobs. They handle complex joins and transformations natively, making them ideal for standard data engineering tasks. They abstract the complexity of Spark configuration while providing enough power to manage large-scale data manipulation, making them the superior choice for most transformation scenarios compared to manual coding.

Answer

Microsoft Purview Data Catalog.

Answer

A SQL stored procedure on an on-premises server.

Answer

Azure Data Factory pipeline parameters only.

Question 3

You have a large Delta table that is frequently queried. You notice that queries are slow due to small file overhead. Which command should you run to optimize this table?

Accepted Answer

OPTIMIZE table_name. The OPTIMIZE command is the standard Delta Lake maintenance operation that compacts small files into larger ones, drastically improving read performance. When combined with Z-Ordering, it also organizes data physically based on column values, which further accelerates query performance for filters. This is a critical task for maintaining high-performance analytical tables in a Lakehouse environment, preventing the performance degradation caused by file fragmentation.

Answer

VACUUM table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Answer

REORG TABLE table_name APPLY

Question 4

You are configuring a Fabric Pipeline. Which TWO activities can be used to perform conditional logic within your data ingestion workflow?

Accepted Answer

If-Condition activity. Conditional logic is essential for building resilient data pipelines. The If-Condition activity allows for simple branching based on Boolean expressions, while the Switch activity enables multi-way branching based on discrete values. These activities allow engineers to build sophisticated workflows that react to data quality results, source availability, or specific environmental flags, ensuring that the pipeline behaves appropriately under various conditions without manual oversight.

Answer

Lookup activity

Answer

Stored Procedure activity

Answer

Wait activity

Question 5

You need to ingest data from an API into a Fabric Lakehouse. The API requires a token-based authentication refreshed every hour. What is the most efficient way to handle this?

Accepted Answer

Use a Web activity to fetch the token, then pass it to the Copy activity.. Using a Web activity in a Fabric Pipeline to call the authentication endpoint, followed by storing the token in a secure Azure Key Vault or variable, is the standard pattern. This allows the subsequent Copy activity to dynamically retrieve and use the token. This pattern ensures secure credential management while maintaining the automation required for hourly refreshes in a production data environment.

Answer

Hardcode the token in the Copy activity connection string.

Answer

Create a new connection every hour manually.

Answer

Configure the API to never expire its tokens.

Question 6

You are designing a data lakehouse architecture. Which file format is recommended for optimal performance in Fabric?

Accepted Answer

Delta Lake. Delta Lake is the gold standard format for Fabric. It is an open-source storage layer that brings ACID transactions to data lakes. It enables high-performance querying and supports time travel, schema evolution, and efficient metadata management. Because Fabric is built on the foundation of Delta Lake, using this format allows you to take advantage of native optimizations, providing superior performance compared to traditional formats like CSV or Parquet alone.

Answer

CSV

Answer

JSON

Answer

Avro

Question 7

What is the primary function of the 'Validate' activity in a Fabric pipeline?

Accepted Answer

To ensure a dataset exists before a downstream activity runs.. The Validate activity is used to check the existence or completion of a dataset before proceeding to the next step. It is commonly used to ensure that upstream processes have successfully dropped files into a landing zone. This activity prevents downstream failures by ensuring the data is ready and present, providing a reliable orchestration pattern that keeps pipelines running smoothly and predictably in production.

Answer

To verify the data types of columns in a table.

Answer

To perform a checksum verification of file integrity.

Answer

To compile the pipeline and check for syntax errors.

Question 8

When ingesting data from an external source, what is the best practice for handling sensitive data?

Accepted Answer

Store secrets in an Azure Key Vault and reference them in the connection.. Sensitive data should be handled using Azure Key Vault to store secrets and connections. By referencing these keys in your Fabric pipeline, you avoid hardcoding credentials. Furthermore, you should use data masking or encryption at rest within the Lakehouse to ensure that sensitive fields remain protected even from unauthorized users who may have access to the underlying storage, fulfilling compliance and security requirements for enterprise data management.

Answer

Hardcode credentials in the pipeline JSON code.

Answer

Pass credentials as plain text parameters to the pipeline.

Answer

Use the same service principal for all external sources.

Question 9

Refer to the exhibit. You are using Structured Streaming to ingest data into a Delta table. Why is the checkpoint path required?

Accepted Answer

It stores the offset and state information for fault tolerance.. Checkpointing is essential in Structured Streaming for fault tolerance and exactly-once processing guarantees. It stores the state of the stream, including the offsets of the data processed so far. If a job fails, the checkpoint allows the stream to resume exactly where it left off, preventing data loss or duplication. This is a mandatory component for building reliable streaming pipelines in a production Fabric Lakehouse environment.

Answer

It is used to store the schema definition.

Answer

It increases the throughput of the write operation.

Answer

It is required for overwriting existing tables.

Question 10

Which Fabric feature allows you to monitor the status and history of your data pipeline runs?

Accepted Answer

Fabric Monitoring Hub.. The Monitoring Hub in Fabric provides a centralized view of all pipeline runs, including status, duration, and error logs. It is the primary tool for administrators and engineers to troubleshoot failed jobs, monitor performance, and track the history of data movement. Having this visibility is crucial for maintaining a reliable data platform, as it allows for proactive detection and quick resolution of pipeline failures or performance bottlenecks in your environment.

Answer

Power BI Desktop.

Answer

Microsoft Purview.

Answer

Data Warehouse SQL Query Editor.

Question 11

You are processing a large dataset in a Notebook and encounter frequent 'Shuffle' operations that slow down the job. What is the most likely cause?

Accepted Answer

The job is performing wide transformations on large datasets.. Frequent shuffles are almost always caused by operations that redistribute data across the cluster, such as wide transformations like group-by, join, or distinct. When these operations are performed on large datasets, the cost of moving data over the network becomes the primary bottleneck. Reducing the need for these operations or optimizing the join strategy is critical for improving performance in distributed Spark processing environments.

Answer

The cluster has too much memory.

Answer

The data is already partitioned correctly.

Answer

The file format is set to Delta.

Question 12

You are building a data pipeline in Microsoft Fabric using a Copy Activity to ingest millions of small JSON files from an Azure Data Lake Storage Gen2 container into a Lakehouse table. The performance is significantly slower than expected due to high request latencies. Which optimization technique should you implement?

Accepted Answer

Enable staging in the Copy Activity settings to temporarily buffer data before writing it to the destination.. Enabling staging allows the Copy Activity to stage data in an intermediary storage account before writing to the Lakehouse, which efficiently compresses and batches requests. This drastically reduces the number of individual PUT operations against the storage endpoint, directly mitigating the throttling and latency issues commonly associated with processing massive volumes of small files in distributed analytics environments.

Answer

Increase the Data Integration Units (DIUs) to the maximum allowable limit of 256 for the pipeline activity.

Answer

Convert all source JSON files into single-line newline-delimited JSON format prior to executing the copy pipeline.

Answer

Switch the destination from a Delta Lake table in the Lakehouse to a raw staged folder within the Files section.

Question 13

You are configuring a medallion architecture in Microsoft Fabric using Lakehouse Delta tables. You need to implement an incremental data load pattern from a bronze table to a silver table using a PySpark notebook. Which TWO actions must you perform to ensure idempotency and high performance? Choose exactly two.

Accepted Answer

Use the Delta Lake merge operation based on a unique business surrogate key to handle both inserts and updates idempotently.. Implementing merge operations with watermarking and schema evolution enables incremental updates while maintaining table consistency. Idempotency guarantees that running the pipeline multiple times for the same time window yields identical results without data duplication, which is critical for robust enterprise data engineering pipelines in Microsoft Fabric.

Answer

Overwrite the entire silver table partition dynamically on every execution without checking for existing record identifiers.

Answer

Disable Delta Lake transaction logs during the write phase to accelerate the throughput of incremental insert operations.

Answer

Cache the entire bronze table into the Spark driver memory before executing the join and transformation logic.

Question 14

You are designing a data pipeline in Microsoft Fabric using Data Factory. You need to ingest data from an on-premises SQL Server database into a Lakehouse. Which integration component must be deployed to facilitate secure connectivity between the on-premises network and Microsoft Fabric?

Accepted Answer

Self-hosted Integration Runtime. To connect Fabric to on-premises resources, the Self-hosted Integration Runtime (SHIR) acts as the bridge. It handles data transfer across the firewall without opening inbound ports on your local network. This is a critical architectural requirement for hybrid data integration scenarios, ensuring that Fabric can reach behind the perimeter to pull data securely while maintaining strict compliance with corporate security standards for data movement.

Answer

Azure Data Gateway

Answer

Managed Virtual Network

Answer

Azure ExpressRoute

Question 15

You are developing a Notebook in Microsoft Fabric to process high-volume sensor data. You need to optimize performance using Delta Lake features. Which TWO actions should you perform? (Choose two)

Accepted Answer

Enable Z-Ordering on frequently filtered columns. Optimizing Delta Lake tables involves managing file sizes and data distribution. Z-Ordering optimizes multi-column queries by co-locating related data, significantly reducing read amplification. Compaction (using OPTIMIZE) merges small files into larger, more efficient files, which directly improves scan performance. Implementing these features is essential for maintaining query speed as data grows in the Lakehouse, ensuring that the Spark engine spends less time scanning unnecessary file blocks during execution.

Answer

Manually delete old Parquet files from the file system

Answer

Convert the table to CSV format

Answer

Increase the number of partitions to equal total file count