Ingest and Transform Data practice questions

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

Why wrong: Dropping and recreating tables results in significant data loss and metadata overhead. This approach is highly inefficient and disrupts downstream consumption layers like Power BI or SQL endpoints that rely on the table persistence, making it a poor practice for maintaining robust production-level data engineering pipelines.

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

Why wrong: Manual schema modification is error-prone and prevents automation. Data pipelines should be self-healing where possible. Relying on manual intervention creates bottlenecks and increases the risk of human error, which is detrimental to the scalability and reliability goals of a modern Fabric-based data platform.

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

Why wrong: Writing as JSON forces a loss of Delta Lake's native optimization features like ACID compliance and schema enforcement. This bypasses the very engine features that make Delta Lake ideal for data transformations, leading to inefficient storage and degraded performance for any subsequent analytical queries performed on the data.

Increase the number of partitions to 10,000.

Why wrong: Setting an arbitrarily high number of partitions often causes extreme overhead due to task scheduling and metadata management. This can lead to slow performance and can actually increase memory pressure on the driver node, which has to manage the scheduling of thousands of tasks concurrently.

Upgrade the Fabric capacity to the highest SKU.

Why wrong: Throwing more resources at a bad execution plan is an expensive and inefficient way to solve problems. While it might mask the issue, the underlying skew or inefficient transformation logic remains. Always optimize the code first before resorting to scaling up resources, as this ensures long-term cost-efficiency.

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

Why wrong: The .collect() action pulls all data from the distributed Spark executors into the memory of the driver node. This is the most common cause of OOM errors in Spark. It should be avoided at all costs when processing large datasets, as the driver node cannot handle massive amounts of data.

The total number of rows in the table.

Why wrong: While table size is relevant for overall storage, the number of rows itself is not a direct factor in choosing a partition key. The structure of the data and how users query it are much more important. Partitioning a small table can actually result in worse performance due to overhead.

The number of concurrent users accessing the table.

Why wrong: While concurrency is important for overall system throughput, it is not a direct factor in choosing a partition key. Partitioning strategies are primarily about optimizing data retrieval patterns and storage layout. High concurrency is managed by scaling the compute resources, not by the physical partitioning of the data files.

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

Why wrong: Throttling requests with an interval helps avoid rate-limiting errors from APIs, but it does not address individual HTTP request timeouts caused by slow-responding servers rendering large data payloads during active downloads.

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

Why wrong: Configuring pagination rules such as RFC5988 support enables the copy activity to correctly parse continuation headers and iterate through multi-page API responses without exceeding single-request payload limits or timing out on large datasets.

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

Why wrong: Increasing concurrent connections affects how data is written to the destination Lakehouse, but it has no impact on the upstream HTTP source timeout behavior when retrieving slow pages from an external web service.

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

Why wrong: Most REST APIs return uncompressed JSON unless explicitly negotiated via HTTP headers. Forcing a compression type at the source connector level without API support results in malformed requests rather than fixing timeouts.

Increase the compression ratio of the files

Why wrong: While higher compression saves storage space, it often increases the CPU overhead required to decompress the files during reads. This can actually degrade read performance for queries that need to scan large amounts of data, which is the opposite of the desired performance optimization.

Rename the column to be shorter

Why wrong: Renaming a column has no impact on query execution speed. The storage engine processes data based on the physical layout and indexes, not on the length of the column metadata names. This is an irrelevant optimization that provides no benefit to query performance.

Disable the Delta transaction log

Why wrong: Disabling the transaction log would effectively break the Delta Lake table functionality and remove the benefits of ACID transactions and time travel. It would not improve performance and would likely lead to data corruption or inconsistencies in the analytical query results.

Use the 'Overwrite' mode for every write operation.

Why wrong: While Overwrite mode replaces the entire table and can effectively change the schema, it is not a schema evolution strategy; it is a data replacement strategy. Schema evolution is typically associated with appending data while simultaneously updating the schema to include new information without losing historical data.

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

Why wrong: Failing to specify the 'delta' format may cause Spark to use a default format like Parquet, which does not support the same schema evolution capabilities as Delta Lake. In Fabric, it is best practice to always explicitly use the Delta format for tables to ensure full feature compatibility.

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

Why wrong: When reading data, using enforceSchema or defining a schema ensures that the incoming data adheres to expected types. Combined with mergeSchema on the write side, it allows the engineer to control how new data is validated and then integrated into the existing Lakehouse table structure.

Increase the 'Batch Count' to 100.

Why wrong: Batch Count is used in some orchestration tools to define the number of items in a group, but in Fabric ForEach, the relevant setting for parallelism is 'isSequential'. There is a 'BatchCount' property for parallel execution limits, but it only applies when 'isSequential' is False.

Set 'isSequential' to True.

Why wrong: Setting 'isSequential' to True explicitly forces the pipeline to process every iteration in a serial fashion, one after the other. This is the opposite of what is needed to speed up the process and would maintain the current slow performance behavior described in the scenario.

Change the 'Wait on Completion' setting to False.

Why wrong: There is no 'Wait on Completion' setting for the ForEach activity itself. While some activities have asynchronous options, the flow control of a ForEach loop is managed by the sequential property, which determines how the iterations are dispatched to the execution engine.

Fabric Lakehouse.

Why wrong: A Fabric Lakehouse stores data in Delta format and provides a SQL Analytics Endpoint for high-performance, read-only SQL queries. While it has limited DML compared to the Warehouse, it is frequently used in the Gold layer for reporting due to its efficiency and OneLake integration.

KQL Database.

Why wrong: KQL Databases are optimized for real-time analytics and telemetry data using the Kusto Query Language. While performant, they do not support standard T-SQL DDL/DML in the same way a Warehouse does and are generally used for specific observability or streaming use cases.

Dataflow Gen2.

Why wrong: Dataflows Gen2 are ingestion and transformation tools, not storage or query layers. They are used to move and transform data into the Gold layer (Warehouse or Lakehouse) but do not themselves serve as the final destination for high-performance analytical querying by end users.

External Secret.

Why wrong: External Secrets are security components used to store credentials and sensitive information. They have no role in the data storage or query performance aspects of a Medallion Architecture's Gold layer and are strictly used for secure connectivity during the ingestion process.

The dataflow will fail immediately to prevent data loss.

Why wrong: The 'automatic' setting is intended to reduce manual intervention. Rather than failing, the system tries to handle the change automatically. Failing would be the behavior if strict schema validation were enabled without any automatic reconciliation or evolution features being active in the configuration.

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

Why wrong: When 'Use automatic settings' is selected for a Dataflow Gen2 destination, the engine manages the table schema. If a mismatch is detected, it will drop the existing table and create a new one that matches the incoming data's structure to ensure the ingestion completes.

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

Why wrong: Dataflows Gen2 run as background processes once published and scheduled. They cannot prompt a user for input during execution. The 'automatic' setting is specifically designed to avoid the need for manual mapping by making the necessary changes to the destination automatically.

A self-hosted integration runtime

Why wrong: While commonly used in Azure Data Factory, Fabric pipelines use the managed gateway infrastructure. Relying on legacy terminology can lead to configuration errors, as Fabric integrates the gateway management directly into the workspace settings rather than requiring a dedicated infrastructure VM management overhead associated with traditional self-hosted runtimes.

A public IP address for the SQL server

Why wrong: Opening public ports for internal database servers creates significant security vulnerabilities. Microsoft best practices strictly advise against exposing private databases to the public internet. The data gateway approach ensures that all traffic is outbound and encrypted, maintaining a zero-trust security posture for your sensitive on-premises data assets.

A VPN-only connection

Why wrong: A VPN establishes a secure tunnel but does not provide the application-level connectivity layer required for Fabric data ingestion. The gateway software is specifically architected to handle the metadata and data movement protocols necessary for Fabric pipelines to translate local SQL queries into actionable data loads in the lakehouse.

The source SQL Server is offline

Why wrong: An offline SQL Server would result in a connection timeout or network connectivity error, not an 'Access Denied' error. Access errors indicate that the connection was successful, but the security layer rejected the requested operation, pointing squarely at permissions rather than network or infrastructure availability issues.

The Parquet format is unsupported

Why wrong: Parquet is a native and highly recommended format for Fabric Lakehouses. It is fully supported for all ingestion activities. An unsupported format error would trigger a specific 'Format Not Supported' exception, not a generic 'Access Denied' error, which is strictly related to authentication and authorization policies.

The pipeline is missing a gateway

Why wrong: Missing gateway configurations usually produce connection errors related to infrastructure accessibility. Since the error specifically states 'Access Denied', the issue is related to identity and credentials rather than the bridge component. The system is able to reach the destination but is barred from writing data due to authorization settings.

Enable schema drift at the sink

Why wrong: Enabling schema drift allows the sink to automatically detect and incorporate new columns from the source. This is the recommended practice for resilient data pipelines. It removes the need for manual mapping adjustments and ensures that your data lake always reflects the current structure of the source.

Use a Stored Procedure for ingestion

Why wrong: Stored procedures require fixed schemas. If the source changes, the procedure will likely break unless it is also updated. This approach provides no benefit over standard copy activities for handling drift; instead, it adds complexity by requiring code-level changes whenever the source schema undergoes any modifications.

Convert the source to a view

Why wrong: A view does not automatically inherit new columns from the base table unless it is defined using 'SELECT *'. Even so, this does not address the ingestion pipeline's requirements to handle changes. It is a workaround that masks the issue rather than solving the fundamental need for schema flexibility.

Use a single-threaded copy

Why wrong: Single-threaded copies are the slowest possible configuration. They do not leverage the distributed nature of the ingestion engine, leading to extremely long processing times. This is the opposite of the performance-oriented approach needed for large, file-heavy datasets, as it ignores the scalability of the Fabric cloud infrastructure.

Decrease the timeout settings

Why wrong: Decreasing the timeout will cause the pipeline to fail prematurely rather than addressing the performance issue. It does not speed up the ingestion process; it only makes the pipeline less resilient to legitimate delays. Performance problems require optimization of the transfer logic, not changes to arbitrary timeout limits.

Switch to a CSV format

Why wrong: Changing file formats does not help with the small file problem. The overhead is caused by the *number* of files, not their contents. Whether the files are CSV or Parquet, the infrastructure still has to open, process, and close each one, so the bottleneck remains unchanged.

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

Why wrong: The standard Copy Activity in Data Factory requires explicit mapping or a fixed schema definition. While some tools have drift toggles, the most robust way to handle evolving schemas in a Fabric Lakehouse environment is to leverage Spark's mergeSchema option, which provides programmatic control over how new columns are integrated into Delta tables.

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

Why wrong: Dataflow Gen2 is excellent for transformations but is less flexible than Spark when it comes to automated, complex schema evolution across many tables. While it handles data types well, it often requires manual updates to the mapping if the source schema changes significantly, making Spark the more robust choice for drift.

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

Why wrong: Changing the table action to 'Append' does not solve schema drift; it merely changes how data is added to the table. If the schema of the new data does not match the existing table, the append operation will still fail unless schema evolution is specifically enabled via the engine's configuration or options.

Use the default Azure Integration Runtime without a staging account.

Why wrong: The Azure Integration Runtime cannot directly access on-premises resources behind a firewall. Attempting this will result in a connection failure, as the cloud-based runtime lacks the necessary network visibility into your local network architecture, making it unsuitable for direct on-premises connectivity without a self-hosted agent.

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

Why wrong: While necessary for connectivity, simply deploying the runtime is insufficient for performance. Without configuring staging, the data must pass through the gateway in a single stream, which significantly limits throughput and increases the risk of timeout errors when processing massive datasets that exceed standard bandwidth limitations.

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

Why wrong: DIU settings control the compute power for cloud-to-cloud copies but do not resolve the fundamental bottleneck of an on-premises network connection. Increasing DIUs without a staged copy implementation will only increase resource consumption on the gateway without effectively improving the actual data transfer rate from the SQL server.

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

Why wrong: Recursive flag simply tells the activity to look into subdirectories. It does not interfere with the initial schema inference logic itself. While recursive ingestion might lead to schema drift if files differ, it is not the primary cause of an immediate failure to infer the base schema.

AzureBlobFSReadSettings does not support schema inference from delimited files.

Why wrong: AzureBlobFSReadSettings is fully capable of reading delimited files and performing initial column discovery. The issue is not the read capability but rather how the sink interprets the output metadata during the table creation process when moving data between different storage formats in the Fabric environment.

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

Why wrong: A Copy activity is sufficient on its own to move data and create tables. There is no architectural requirement for an Execute Pipeline activity to initialize a sink. Adding unnecessary layers of orchestration does not resolve underlying configuration issues related to type mapping or schema definition.

Microsoft Purview Data Catalog.

Why wrong: Purview is a governance and metadata management tool, not an execution engine for data transformation. It provides insights into data lineage and classification but lacks the computational capabilities required to perform joins or transformations on datasets. It cannot execute ETL logic or manipulate table data.

A SQL stored procedure on an on-premises server.

Why wrong: Executing transformations on-premises limits scalability and creates high latency when working with cloud-based data. Fabric is designed to perform transformations within its own storage layer to maximize throughput and minimize egress costs. Moving data back and forth to an on-premises server is counter-productive and inefficient.

Azure Data Factory pipeline parameters only.

Why wrong: Parameters are used to control the configuration of activities, not for performing actual data transformations. While you can pass parameters into activities, they do not provide the logic engine needed to perform relational joins or data cleansing operations. They are strictly configuration elements, not data processing tools.

VACUUM table_name

Why wrong: VACUUM removes old files that are no longer referenced by the transaction log, helping manage storage costs. It does not compact existing files or improve query performance through data organization. While important for maintenance, it is not the correct command for addressing small file overhead issues.

ANALYZE TABLE table_name COMPUTE STATISTICS

Why wrong: ANALYZE updates metadata statistics, which helps the query optimizer make better decisions about join strategies and scan plans. It does not physically rearrange or merge the underlying data files, meaning it will not solve the issue of excessive small files causing slow read performance.

REORG TABLE table_name APPLY

Why wrong: There is no REORG command in standard Delta Lake implementations. This command is typically associated with legacy database systems like DB2 or SQL Server. Using invalid syntax will result in execution errors, as it is not part of the Spark-based Delta Lake command set used in Fabric.

Lookup activity

Why wrong: The Lookup activity is used to retrieve data from a dataset or query result. While it can be used to feed values into conditional logic, it is an ingestion/retrieval activity, not a control-flow activity. It does not perform branching or logic evaluation on its own.

Stored Procedure activity

Why wrong: A Stored Procedure activity is used to invoke SQL code. While that code may contain conditional logic, the activity itself does not manage pipeline-level branching or orchestration. It is a discrete task execution unit and should not be used for control flow orchestration in the Fabric pipeline.

Wait activity

Why wrong: The Wait activity pauses the pipeline execution for a specified duration. It does not support logical branching, conditional evaluation, or any form of workflow control beyond timing. It is intended for synchronization or delaying downstream tasks, not for implementing logic-based decision-making in your ingestion architecture.

Hardcode the token in the Copy activity connection string.

Why wrong: Hardcoding tokens is a major security risk and violates standard security practices. Furthermore, since the token expires every hour, a static value will lead to authorization failures after the first hour, causing the entire ingestion process to fail until a new token is manually updated.

Create a new connection every hour manually.

Why wrong: Manual processes are not scalable or reliable for production data engineering. Requiring hourly human intervention creates a high risk of downtime and operational overhead. Data pipelines must be designed to handle authentication renewals automatically to ensure continuous data flow without human dependency in the pipeline.

Configure the API to never expire its tokens.

Why wrong: Authentication tokens are designed to expire for security. Requesting an infinite token is rarely permitted by enterprise APIs due to the immense security risks. Even if possible, it would be an insecure configuration that could lead to unauthorized data access if the credentials were ever compromised.