DP-700 Ingest and Transform Data — Timed 20 Questions

Question 1

You are transforming data in a Fabric Notebook using PySpark. You need to handle schema evolution when loading data into a Delta table. Which TWO actions should you perform?

Accepted Answer

Set the spark.databricks.delta.schema.autoMerge.enabled configuration to true.. To handle schema evolution in a Delta table, you can enable it globally for the Spark session by setting spark.databricks.delta.schema.autoMerge.enabled to true, or you can enable it per-write operation using the DataFrame writer option .option('mergeSchema', 'true'). While either approach works independently, you can select both or either depending on the options provided in typical Microsoft exam formats.

Answer

Drop the Delta table and recreate it whenever the source schema changes.

Answer

Manually alter the table schema using SQL ALTER TABLE commands before every execution.

Answer

Convert the Spark DataFrame to JSON format before writing it to the Delta table.

Question 2

Refer to the exhibit. You are receiving OOM errors in your Fabric Notebook. What is the best way to optimize the memory usage for this task?

Accepted Answer

Enable AQE (Adaptive Query Execution) to dynamically adjust partitions.. The explanation discusses data skew, broadcasting, and salting, but the correct answer (B) is about Adaptive Query Execution (AQE). While AQE can help with skew, the explanation should explicitly mention how AQE dynamically coalesces small partitions and optimizes join strategies to prevent OOM errors, rather than focusing on manual techniques like salting.

Answer

Increase the number of partitions to 10,000.

Answer

Upgrade the Fabric capacity to the highest SKU.

Answer

Use the .collect() method before writing the output.

Question 3

Which THREE factors should you consider when choosing a partition key for a Delta table in Fabric?

Accepted Answer

Cardinality of the column values.. The explanation correctly identifies that low cardinality is preferred to avoid small files, but the provided options list 'Cardinality of the column values' (A) as correct without specifying low or high. Furthermore, the explanation mentions 'data type' (D) is a factor, but the explanation text provided does not actually justify why data type is a factor for partitioning, whereas the number of rows (C) is a factor in determining if partitioning is even necessary.

Answer

The total number of rows in the table.

Answer

The number of concurrent users accessing the table.

Question 4

Refer to the exhibit. You have configured a Microsoft Fabric pipeline copy activity to ingest paginated data from a REST API endpoint. During execution, the activity fails intermittently when fetching large pages due to response timeouts. Which parameter should you add to the RestSource definition in the pipeline JSON configuration to resolve pagination timeout issues?

Answer

Add "requestInterval": "00:00:05" within the RestSource typeProperties section to throttle rapid API calls.

Answer

Add "paginationRules": {"supportRFC5988": "true"} to automatically handle next-link headers provided by the API.

Answer

Add "maxConcurrentConnections": 10 within the sink storeSettings to increase parallel write throughput.

Answer

Add "compressionType": "GZip" to the source dataset definition to reduce payload size over the network.

Question 5

You have a large Delta table in your Fabric Lakehouse. You are observing slow read performance on queries that filter by a specific column. What is the most recommended action to improve query performance?

Accepted Answer

Implement partitioning on the filtered column. While partitioning is a valid technique, it is not the 'most recommended' action for a single column filter in Delta Lake. Partitioning creates physical directory structures and can lead to the 'small file problem' if the column has high cardinality. Z-Ordering (or Liquid Clustering in Fabric) is the industry-standard recommendation for improving query performance on specific columns in Delta tables, as it enables data skipping without the overhead of partitioning.

Answer

Increase the compression ratio of the files

Answer

Rename the column to be shorter

Answer

Disable the Delta transaction log

Question 6

You are designing a transformation strategy in a Fabric Lakehouse using Spark. You need to ensure that the Delta tables support schema evolution as the source data structures change over time. Which THREE actions or configurations are required?

Accepted Answer

Set 'mergeSchema' to 'true' in the Spark write options.. In Microsoft Fabric (Apache Spark / Delta Lake), schema evolution is enabled either by setting the DataFrame write option 'mergeSchema' to 'true' or by setting the Spark session configuration 'spark.databricks.delta.schema.autoMerge.enabled' to 'true' (supported in Fabric for compatibility). Option E ('enforceSchema') actually does the opposite: it enforces an existing schema and rejects data that does not conform to it, preventing schema evolution. Therefore, the three correct actions/configurations should not include E.

Answer

Use the 'Overwrite' mode for every write operation.

Answer

Use the 'df.write.save()' method without specifying a format.

Answer

Define a schema in the 'read' operation and use 'enforceSchema'.

Question 7

You are building a Fabric Pipeline that iterates through 100 different folders to ingest data. You notice that the pipeline is running very slowly because it processes folders one by one. Which property of the 'ForEach' activity should you modify?

Accepted Answer

Set 'isSequential' to False.. In Microsoft Fabric and Azure Data Factory, the property that controls parallel execution in a ForEach activity is named 'isSequential'. However, the value must be set to 'false' to enable parallelism. The explanation is technically correct, but the option text 'Set isSequential to False' is the standard way to describe this configuration in exam contexts.

Answer

Increase the 'Batch Count' to 100.

Answer

Set 'isSequential' to True.

Answer

Change the 'Wait on Completion' setting to False.

Question 8

You are implementing a Medallion Architecture in Microsoft Fabric. Which TWO items are best suited for the 'Gold' layer where data must be highly performant for complex analytical queries and support full T-SQL DDL/DML?

Accepted Answer

Fabric Warehouse.. In a Microsoft Fabric Medallion Architecture, the Gold layer contains highly refined, business-level data optimized for analytics. The Fabric Warehouse is specifically designed to provide robust, full T-SQL DDL/DML support and high performance for complex analytical queries. While the Lakehouse is used in the Medallion architecture (often for Bronze/Silver/Gold files and tables), the item specifically providing full T-SQL DDL/DML for the relational Gold layer is the Fabric Warehouse.

Answer

Fabric Lakehouse.

Answer

KQL Database.

Answer

Dataflow Gen2.

Answer

External Secret.

Question 9

When configuring a Dataflow Gen2 destination, you choose a Fabric Lakehouse. What happens if the schema of the incoming data changes and you have 'Use automatic settings' enabled for the destination?

Accepted Answer

The extra columns in the source will be ignored during ingestion.. When 'Use automatic settings' (Auto schema) is enabled for a Fabric Lakehouse destination in Dataflow Gen2, the system automatically adapts to schema changes by evolving the table schema (adding new columns, widening data types) rather than dropping and recreating the table, which would cause data loss. The behavior of dropping and recreating the table does not align with the standard behavior of 'Auto' update method in Fabric dataflows.

Answer

The dataflow will fail immediately to prevent data loss.

Answer

The destination table will be dropped and recreated with the new schema.

Answer

The dataflow will prompt the user to manually map the new columns.

Question 10

You are designing a Data Factory pipeline in Microsoft Fabric. You need to ingest data from an on-premises SQL Server database. Which component is required to facilitate this connectivity securely?

Accepted Answer

An on-premises data gateway. In Microsoft Fabric, the component used to connect to on-premises data sources is the 'Data Gateway' (specifically the On-premises data gateway). However, the term 'Self-hosted integration runtime' is the equivalent component in Azure Data Factory. Since the question specifically asks about Microsoft Fabric, the correct answer is the On-premises data gateway. The provided options list both A and C, but A is the ADF terminology. The explanation is correct, but the option mapping in the prompt is confusing.

Answer

A self-hosted integration runtime

Answer

A public IP address for the SQL server

Answer

A VPN-only connection

Question 11

Refer to the exhibit. The data ingestion job failed. Based on the error log, what is the most likely cause?

Accepted Answer

Insufficient permissions on the Lakehouse. In Microsoft Fabric, access to a Lakehouse is governed by Fabric workspace roles (Admin, Member, Contributor, Viewer) or specific item-level permissions, rather than Azure RBAC roles like 'Storage Blob Data Contributor' which apply to the underlying ADLS Gen2 storage account. The explanation should focus on Fabric workspace permissions or Lakehouse-specific access control.

Answer

The source SQL Server is offline

Answer

The Parquet format is unsupported

Answer

The pipeline is missing a gateway

Question 12

Which strategy is most effective for handling schema drift in a Data Factory Copy Activity when the source SQL table adds new columns frequently?

Accepted Answer

Update the pipeline mapping manually. The Copy Activity in Azure Data Factory does not support automatic schema drift handling for sink columns in the way described. To handle schema drift in a Copy Activity, one must typically use 'Auto mapping' and ensure the sink dataset is configured to allow flexible schema, or use a Mapping Data Flow which explicitly supports the 'Allow schema drift' feature. The Copy Activity's mapping is generally static unless using specific sink types that support dynamic schema evolution.

Answer

Enable schema drift at the sink

Answer

Use a Stored Procedure for ingestion

Answer

Convert the source to a view

Question 13

You are using a Copy Activity to ingest data. You notice that the ingest process is slow due to the high number of small files in the source folder. How can you improve performance?

Accepted Answer

Enable parallel copy. Ingesting many small files is inefficient because each file requires individual metadata operations and network overhead. Enabling parallel copy allows the Copy Activity to process multiple files concurrently, significantly reducing the overhead and improving the overall throughput of the data movement process in your pipeline.

Answer

Use a single-threaded copy

Answer

Decrease the timeout settings

Answer

Switch to a CSV format

Question 14

Refer to the exhibit. You are reviewing a JSON configuration for a Data Factory Copy activity that ingests logs into a Lakehouse. Users report that when the source schema changes slightly, the pipeline fails. How should you modify the ingestion strategy to handle schema drift automatically?

Accepted Answer

Switch to a Spark Notebook using .option("mergeSchema", "true") during the write.. Schema drift occurs when source metadata changes unexpectedly, such as new columns being added. In Fabric, Spark-based ingestion is generally more resilient to schema drift than the Copy Activity because Spark can be configured to merge schemas. This allows for a more flexible ingestion pattern where the destination table evolves alongside the source data without requiring manual pipeline adjustments.

Answer

Enable 'Allow Schema Drift' in the Copy activity mapping settings.

Answer

Use a Dataflow Gen2 with the 'Dynamic Schema' option enabled.

Answer

Change the tableAction from 'Overwrite' to 'Append' in the JSON.

Question 15

You are designing a Fabric pipeline to copy data from an on-premises SQL Server to a OneLake Lakehouse. Which integration runtime configuration ensures the highest throughput for large datasets?

Accepted Answer

Configure a Self-hosted Integration Runtime and enable the Staged Copy feature using an Azure Blob storage account.. Self-hosted integration runtimes are required for on-premises data sources. To maximize throughput for large volumes, you must enable staged copy, which allows the data to be temporarily cached in an Azure Blob storage account before landing in the Lakehouse. This approach optimizes performance by decoupling the read and write operations, reducing latency and avoiding bottlenecks on the on-premises gateway during heavy transformation or ingestion tasks.

Answer

Use the default Azure Integration Runtime without a staging account.

Answer

Deploy a Self-hosted Integration Runtime on a virtual machine in the same region as the Lakehouse.

Answer

Increase the Data Integration Unit (DIU) setting to the maximum value in the Copy Activity.

Question 16

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 17

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 18

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 19

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 20

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.