Prepare Data practice questions

Data Factory Pipelines with a Copy activity

Why wrong: Copy activities are highly efficient for moving large volumes of data between sources and destinations with minimal transformation. However, they do not support complex logical transformations such as pivoting or multi-level joins within the activity itself, necessitating additional compute stages like Notebooks or Dataflows to refine the data.

T-SQL Stored Procedures in a Warehouse

Why wrong: While T-SQL is powerful for data manipulation, it requires the data to already reside within a Fabric Warehouse or be accessible via external tables. Using stored procedures for initial ingestion and complex pivoting from ADLS Gen2 is less efficient than using purpose-built ingestion tools like Dataflows for low-code requirements.

Spark Job Definitions

Why wrong: Spark Job Definitions allow for the execution of compiled JAR or Python files for high-scale data processing. While extremely powerful and flexible, they are not considered low-code environments and require significant development effort compared to the visual interface of Dataflows Gen2 for standard transformation tasks.

It enables ACID transactions for concurrent users.

Why wrong: ACID transactions are a fundamental feature of the Delta Lake protocol itself, rather than a specific result of V-Order. Delta Lake manages transactions through a JSON-based transaction log, ensuring data integrity regardless of whether V-Order optimization is applied to the underlying Parquet files during the write process.

It implements Liquid Clustering for dynamic data layout.

Why wrong: Liquid Clustering is a separate Delta Lake feature that replaces traditional partitioning and Z-Ordering to simplify table maintenance. While both aim to improve performance, V-Order is a specific file-level optimization for read speed in Fabric, and it operates independently of the high-level clustering strategy used for the table.

It automatically evolves the schema when new columns are detected.

Why wrong: Schema evolution is a capability of Delta Lake that allows the table's schema to change over time without requiring a full rewrite. This process is controlled by specific Spark configurations or merge options, and is not a function of the V-Order file optimization, which focuses on physical storage layout.

The Workspace ID and the Lakehouse GUID.

Why wrong: While Workspace IDs and GUIDs are used for API calls and internal identification, they do not constitute a valid connection string for SQL tools. Analysts require a specific endpoint URL that follows the SQL Server protocol to establish a connection through management tools like SSMS or ADO.NET.

The ABFS path to the Delta table folders.

Why wrong: The Azure Blob File System (ABFS) path is used by Spark and other storage-aware engines to access raw data files. Standard SQL tools like SSMS cannot directly interpret ABFS paths; they require a SQL gateway or endpoint that translates T-SQL queries into storage operations on the underlying data.

A Personal Access Token (PAT) for the Fabric REST API.

Why wrong: Personal Access Tokens are generally used for authentication with REST APIs or Git integration. They do not provide the necessary connection infrastructure for T-SQL tools to query data. Connecting to a SQL analytics endpoint typically uses Microsoft Entra ID (formerly Azure AD) authentication rather than PATs.

Run the VACUUM command on the table.

Why wrong: The VACUUM command is used to remove data files that are no longer referenced by a Delta table and are older than a specific retention threshold. While it helps save storage costs and maintain data privacy, it does not consolidate small files into larger ones to improve read performance.

Change the table's partition scheme to a more granular column.

Why wrong: Increasing the granularity of a partition scheme often exacerbates the small file problem by creating more subdirectories and smaller files per partition. Effective data preparation involves choosing a partition column with low cardinality to avoid the 'small file' syndrome and maintain optimal read performance.

Set the 'spark.sql.shuffle.partitions' to a higher value.

Why wrong: Adjusting shuffle partitions controls the parallelism during data shuffling operations in Spark. Increasing this value creates more tasks and smaller output files during transformations, which could actually worsen the small file issue rather than resolving it. Consolidating existing files requires a dedicated compaction operation like OPTIMIZE.

You can apply V-Order optimization to the source files through the Shortcut.

Why wrong: V-Order is a write-time optimization that Fabric applies when it manages the storage. Because Shortcuts point to external data that Fabric does not own or write to directly, you cannot apply V-Order to the original source files without first ingesting them into Fabric managed storage.

Shortcuts automatically convert CSV files into the Delta Lake format.

Why wrong: Shortcuts provide access to data in its native format. If the source files are CSVs, they appear as CSVs in the Lakehouse files section. They are not automatically converted to Delta; such a conversion would require a transformation process like a Spark job or Dataflow Gen2.

To create a historical snapshot of the inventory for auditing.

Why wrong: A snapshot operation typically involves inserting all incoming records with a timestamp to preserve history. The MERGE statement shown here updates existing records in place, which overwrites the previous state and does not maintain a complete version history unless combined with a Slowly Changing Dimension (SCD) strategy.

To delete records from the Silver table that no longer exist in the Bronze table.

Why wrong: The provided MERGE statement lacks a 'WHEN NOT MATCHED BY SOURCE THEN DELETE' clause. Consequently, it only handles updates and inserts for records present in the source. It does not identify or remove records in the target table that are missing from the source dataset.

To validate that the data types in the Bronze table match the Silver table.

Why wrong: MERGE is a Data Manipulation Language (DML) operation used for modifying data, not a Data Definition Language (DDL) or validation tool. While it requires compatible schemas to execute, its primary goal is data synchronization rather than schema validation or structural checking between different architectural layers.

Liquid Clustering requires manual re-partitioning every time data is appended.

Why wrong: Liquid Clustering is designed to eliminate the need for manual re-partitioning. It dynamically adjusts the data layout based on the clustering keys provided, simplifying table maintenance compared to traditional partitioning schemes that require rigid folder structures and manual management as the data volume increases over time.

Z-Ordering is more efficient for tables where the clustering keys change frequently.

Why wrong: Z-Ordering is actually less efficient when keys change because it is a one-time operation that must be repeated across the entire dataset to remain effective. Liquid Clustering is superior in this scenario as it supports incremental clustering, allowing the table to adapt to new data without full rewrites.

Liquid Clustering can only be applied to tables with fewer than three columns.

Why wrong: Liquid Clustering is not restricted to small numbers of columns. It is highly scalable and can be used on large tables with multiple clustering keys. Its design is intended to handle high-cardinality columns and complex query patterns that traditional partitioning and Z-Ordering struggle to optimize effectively.

Incremental refresh

Why wrong: Incremental refresh is used to optimize the loading process by only updating new or changed data. While it improves performance and efficiency for large datasets, its primary purpose is not error handling or stopping the execution upon a transformation failure in a Dataflow Gen2 context.

Gateway clusters

Why wrong: On-premises data gateways are used to connect Fabric to data sources located behind a firewall or on-premises. While they are essential for data access in those specific scenarios, they do not manage the internal logic of transformation error handling or destination write-back integrity within Fabric.

Query folding

Why wrong: Query folding is an optimization technique where Power Query transformations are pushed back to the source system (like a SQL database) to be executed as a single query. This improves performance but is not a mechanism for managing destination load failures or overall execution flow control.

Data Source Privacy Levels

Why wrong: Privacy levels (like Public, Organizational, or Private) determine how data can be combined across different sources to prevent accidental data leakage. While they can sometimes impact the ability of the engine to fold queries across multiple sources, they are not the primary mechanism for remote execution of logic.

Schema Drift

Why wrong: Schema drift refers to changes in the source metadata, such as new or renamed columns, over time. Handling schema drift ensures that pipelines do not break when the source changes, but it does not relate to whether a transformation step is pushed back to the source database engine.

Fast Copy

Why wrong: Fast Copy is a Fabric-specific feature for Dataflows Gen2 that uses the Data Factory engine for high-speed ingestion. While it relates to performance, it is distinct from query folding; Fast Copy focuses on the throughput of the data movement rather than the translation of M-code into source-specific SQL.

Use df.na.drop(subset=['Price']) to remove the rows.

Why wrong: Dropping rows with null values is a valid strategy if the percentage of missing data is very small. However, the requirement specifically asks to replace nulls with the average price, and dropping rows would lead to data loss and potentially biased results if the nulls are not randomly distributed.

Apply a filter where 'Price' is not null using df.filter(df['Price'].isNotNull()).

Why wrong: Filtering out null values is equivalent to dropping them from the result set. While it ensures that the remaining data is clean, it does not meet the specific goal of imputing the missing values with the average price, which is necessary for maintaining the full count of records.

Use the 'V-Order' function to automatically fill missing values.

Why wrong: V-Order is a storage optimization for Parquet files in Fabric and has no capability for data imputation or logical transformations like filling nulls. It focuses entirely on physical data layout and compression to improve read performance rather than modifying the actual data values within a DataFrame.

Convert all Delta tables into the CSV format for better compatibility.

Why wrong: Direct Lake mode specifically requires the Delta Lake format. CSV files are not supported for Direct Lake and would require the Power BI service to fall back to Import mode or use a slower connection method. Delta Parquet is the native and required format for high-performance Fabric integration.

Ensure that the tables are partitioned on high-cardinality columns.

Why wrong: Partitioning on high-cardinality columns (like OrderID) creates a 'small file' problem, where thousands of tiny files are generated. This drastically slows down performance. Effective partitioning should use low-cardinality columns (like Year or Month) to keep file sizes optimal and improve partition pruning efficiency.

Store the data in a Warehouse and use the Spark SQL connector.

Why wrong: While Spark can connect to a Warehouse, it is not the native environment for file-based exploration. Warehouses are structured-first and do not provide a 'Files' section for raw data exploration, making them less ideal for data scientists who need to work with unstructured or semi-structured data initially.

Create two copies of the data: one in OneLake for Python and one in Azure SQL for SQL.

Why wrong: Creating multiple copies of data across different services introduces data redundancy, increases storage costs, and creates synchronization challenges. Fabric's 'OneCopy' philosophy aims to eliminate this by allowing different engines to work against the same data stored in the Delta Parquet format in OneLake.

Use Dataflows Gen2 to export data to a CSV file for each group.

Why wrong: Exporting to CSV is an inefficient way to share data between teams. CSVs lack the performance benefits of Parquet, the ACID transactions of Delta Lake, and the automatic schema management provided by Fabric. It would create a fragmented environment that is difficult to maintain and slow to query.

Run the VACUUM table_name RETAIN 0 HOURS command to instantly purge all historical file versions.

Why wrong: Retaining zero hours with VACUUM destroys historical snapshots instantly, breaking active time travel queries and potentially corrupting concurrent operations. While it cleans up storage, it does not solve file sizing or data layout fragmentation issues.

Configure partitioning on every column that has high cardinality to maximize pruning efficiency.

Why wrong: Partitioning high-cardinality columns creates an excessive number of tiny directories and files, known as the small file problem. This severely degrades metadata performance and negates the optimization benefits provided by modern Parquet and Delta engines.

Alter the table properties to increase the default checkpoint interval to every 500 commits.

Why wrong: Increasing the checkpoint interval delays metadata summarization and forces Spark to read excessively long transaction logs during startup. This degrades read performance rather than resolving physical data layout and file count inefficiencies.

Inner join

Why wrong: An inner join filters out any customer records that do not have at least one matching transaction in the sales table. This violates the business requirement to retain all customer records in the output dataset.

Right Anti join

Why wrong: A right anti join returns only the rows from the sales table that have no matching entry in the customer table. This operation isolates orphaned transactions rather than combining customer and sales datasets.

Full Outer join

Why wrong: A full outer join retains all records from both tables, introducing unmatched rows from both sides. If sales records exist without valid customer IDs, they would be included, which could corrupt customer-centric aggregations.

Azure Integration Runtime

Why wrong: Azure Integration Runtime is designed for cloud-based data stores like Azure SQL Database or Blob Storage. It lacks the network connectivity features required to reach into a private corporate network where on-premises SQL Server instances reside behind firewalls, making it an unsuitable choice for this specific infrastructure requirement.

Managed Virtual Network Integration Runtime

Why wrong: Managed Virtual Networks provide isolation for data processing but do not provide the specific tunneling capabilities required to bridge an on-premises network to the cloud. This runtime is primarily used to secure communication between Azure-based resources rather than extending the reach to legacy, local data center environments.

Power BI Gateway

Why wrong: While a Power BI Gateway facilitates connections for Power BI service, it is not the correct architectural component for Fabric pipelines. Fabric data pipelines rely specifically on integration runtimes to perform ETL tasks. Using a Power BI Gateway would lead to pipeline failures as it lacks the required orchestration capabilities.

Decrease the number of Spark partitions

Why wrong: Reducing partitions forces the remaining tasks to handle larger data chunks, which increases the likelihood of memory exhaustion and disk spilling. Spark works optimally when data is distributed across as many partitions as possible to balance the load across the cluster nodes, keeping memory usage per task manageable.

Increase the executor memory size

Why wrong: While increasing memory may temporarily hide the symptom of disk spilling, it does not address the underlying issue of inefficient data distribution. Relying solely on larger instances increases costs without improving code efficiency, making it an inferior architectural solution compared to properly managing partitions and filtering data early.

Use the default Spark configuration without changes

Why wrong: Default configurations are rarely optimized for specific workloads, especially when processing large datasets. Failing to tune partition counts or optimize read patterns leaves significant performance on the table, leading to suboptimal resource utilization and longer processing windows that could be avoided with proper Spark job configuration tuning.

The column customer_id is deleted from the table.

Why wrong: The operation targets rows containing null values in the column, rather than dropping the column itself. Deleting the entire column would result in a significant loss of information, as all customer identifiers would be removed from the dataset, rendering the remaining data useless for any customer-specific analysis or reporting.

The null values in customer_id are replaced with 0.

Why wrong: The JSON explicitly defines the action as 'drop'. Replacing values with zero would require an 'impute' or 'fill' command. Since the instruction is to remove records rather than modify them, this option incorrectly describes the behavior and would result in incorrect data representation if applied to the dataset.

The pipeline execution stops immediately.

Why wrong: This is a standard data transformation operation supported by Data Wrangler and would not cause the pipeline to fail. Instead, the transformation executes as part of the data cleaning pipeline, modifying the dataset schema or content as defined, and then continues with the subsequent steps of the data ingestion flow.

Store all files directly in the root directory.

Why wrong: Storing files in the root leads to clutter and makes it difficult to implement fine-grained security or lifecycle management policies. As the number of datasets grows, navigating the root directory becomes unmanageable, increasing the risk of accidental deletion or exposure of sensitive data by unauthorized users or automated processes.

Create a unique folder for every individual user.

Why wrong: Creating user-specific folders leads to silos and makes data discoverability impossible for the rest of the organization. Data should be governed and organized by business process or source system, not by individual users, to ensure consistency and collaborative access across the entire analytical team within the Fabric workspace.

Rename all files to include the ingestion date.

Why wrong: While including dates in filenames is a helpful practice, it is not a structural strategy for the Lakehouse. Relying solely on filenames instead of a folder hierarchy makes it difficult to apply automated policies, such as retention or tiered storage, to specific layers of the data processing pipeline architecture.

Shortcuts increase the query speed by caching data.

Why wrong: Shortcuts do not inherently cache data or improve query performance through local replication. Instead, they provide a pointer to the source location. Query performance depends on the underlying storage and the execution engine, not the shortcut mechanism itself, so this option does not accurately describe the primary functional purpose.

Shortcuts automatically convert files to Delta format.

Why wrong: Shortcuts do not perform data conversion or format transformation. The data remains in its original format at the source. If the data needs to be in Delta format for specific Fabric features, you must perform a separate transformation or write operation to convert the data; the shortcut only provides access.

Shortcuts provide read/write access to all file types.

Why wrong: While shortcuts support various file types, they are primarily for read-optimized access in analytical scenarios. They do not grant universal read/write capabilities for all formats in the same way a native table would. Furthermore, the primary value proposition is data virtualization, not the expansion of write permissions to source systems.

Broadcast join

Why wrong: Broadcast joins are effective for joining a large table with a small one, but they do not solve the issue of skew within a large-to-large join. Attempting to broadcast a skewed dataset would likely result in an OutOfMemory error, as the skewed key would be sent to every node.

Increasing the number of executor cores

Why wrong: Increasing cores does not address the fundamental issue of data distribution. A single skewed key will still be processed by a single task regardless of the number of cores available, because tasks cannot be split mid-operation. The bottleneck remains at the partition level, rendering this approach ineffective for skew.

Reducing the number of partitions

Why wrong: Reducing partitions exacerbates the data skew problem. When you have fewer partitions, each partition ends up containing even more data, which increases the likelihood of a single task being overwhelmed by the skewed key. This approach would likely lead to slower execution times and more frequent memory-related job failures.