Data Preparation practice questions

Use the dropna() method to remove all rows with null values.

Why it fails: Dropping all rows with null values often introduces significant selection bias and reduces the sample size of the dataset. Unless the missing data is completely at random and represents a tiny fraction of the total, this technique usually degrades model performance by discarding potentially valuable information.

Manually iterate through the DataFrame using a Python loop.

Why it fails: Iterating through DataFrames using Python loops forces the execution to move to the driver, bypassing Spark's distributed architecture. This severely limits scalability and causes significant performance bottlenecks. Data preparation must utilize vectorized Spark operations to leverage distributed compute resources effectively for large data volumes.

Convert the entire DataFrame to a Pandas object for imputation.

Why it fails: Converting large Spark DataFrames to Pandas objects requires loading the entire dataset into the driver's memory. This typically leads to Out-of-Memory (OOM) errors for large datasets. Data engineering should remain within the Spark ecosystem to ensure distributed processing and proper resource utilization throughout the transformation process.

ALTER TABLE target_table SET TBLPROPERTIES ('schema.evolution' = 'true')

Why it fails: Table properties are used for storage and performance configurations, not for controlling the behavior of the DataFrameWriter during the write process. While some table properties influence Delta features, schema evolution is specifically controlled through the DataFrameWriter options or Spark session configurations, not static table metadata settings.

Use the 'REPLACE WHERE' clause in the write command.

Why it fails: The 'REPLACE WHERE' clause is used for overwriting data within a specified partition or range, not for updating the schema structure. It is intended for targeted data updates or deletes rather than structural schema modifications. Attempting to use this for schema evolution will not update the table metadata.

Configure the table as a Hive-Metastore managed table.

Why it fails: The legacy Hive Metastore does not support Delta Lake's schema evolution features. Modern Databricks architecture relies on Unity Catalog for comprehensive schema management and governance. Relying on legacy Hive configurations is counterproductive and lacks the sophisticated integration needed for automated schema reconciliation in contemporary data engineering workflows.

Run the OPTIMIZE command manually every hour.

Why it fails: Manual optimization requires scheduled jobs, which can lead to resource contention and potential conflicts if not carefully orchestrated. It is a reactive approach that doesn't address the root cause of small files during the ingestion process, leading to degraded performance between the scheduled optimization intervals.

Increase the shuffle partitions to 2000.

Why it fails: Increasing shuffle partitions is usually done to prevent data skew or to utilize more executors. However, if the data volume per partition is small, this will actually exacerbate the small file problem by creating even more files, potentially worsening the read performance instead of improving it.

Use the REORG TABLE command.

Why it fails: REORG TABLE is primarily used for rewriting data to remove deleted records or for changing the table layout. While it can improve performance, it is not the standard mechanism for addressing continuous small-file ingestion issues in streaming pipelines, where Auto-Compact and Optimized Writes are the preferred architectural choices.

Partition by columns with very high cardinality.

Why it fails: Partitioning by high-cardinality columns creates a massive number of subdirectories, leading to metadata overhead and the small-file problem. This degrades performance significantly because the Spark driver must manage excessive metadata. High-cardinality columns should instead be handled using Z-Ordering or indexing strategies within the Delta table.

Store all data in a single massive file for simplicity.

Why it fails: Storing data in a single massive file destroys the benefits of parallel processing in Spark, as the file cannot be split across multiple executors. Delta Lake thrives on having many optimally sized files that can be processed in parallel, which is essential for performance at scale.

The null record is dropped, and the pipeline continues.

Why it fails: In DLT, if an expectation is configured to 'fail' on violation, the pipeline will stop execution rather than silently dropping rows. Silently dropping rows would mask data quality issues, whereas failing the pipeline forces the engineer to address the root cause, ensuring data integrity is never compromised.

The null record is routed to a 'quarantine' table.

Why it fails: Routing to a quarantine table requires a specific 'ON VIOLATION' configuration, such as 'expect_or_drop' or a custom logic flow. By default, with strict error settings, DLT halts the execution. Simply defining a rule does not imply automatic routing to a separate table without explicit DLT configuration.

The record is updated to a default value of 0.

Why it fails: DLT expectations do not automatically perform data imputation or modification unless explicitly coded within the transformation logic. An expectation is a validation rule, not an imputation rule. Relying on default values without clear documentation can hide systemic data issues, which DLT is designed to uncover.

Increase the shuffle partitions drastically to 10,000.

Why it fails: Arbitrarily increasing shuffle partitions often leads to the creation of thousands of tiny tasks, which adds significant overhead and does not solve the underlying imbalance of the data. The skew needs to be specifically addressed at the key level rather than through a global increase in partitions.

Switch to a broadcast join for all large tables.

Why it fails: Broadcast joins are only suitable for tables small enough to fit in the driver's and executors' memory. Attempting to broadcast large tables will cause Out-of-Memory (OOM) errors and application failure. It is not a solution for skewed joins involving large datasets that exceed memory limits.

Re-partition the data by a random column.

Why it fails: Re-partitioning by a random column destroys the physical ordering of the data and does not solve the join skew issue, as the join key remains skewed after the shuffle. The skew must be addressed in relation to the join key, not by adding artificial entropy to the dataset.

Drop the 'id' column from df2 before joining.

Why it fails: Dropping the 'id' column before the join would remove the key needed for the join itself, unless you use a different column as the join key. This approach is illogical for an inner join and would lead to incomplete data in the resulting joined DataFrame.

Set 'spark.sql.crossJoin.enabled' to true.

Why it fails: Enabling cross joins does not solve column name ambiguity; it only allows Cartesian products, which are extremely inefficient and rarely the intended result. Cross joins create a massive output size that can crash executors, and they do not address the naming collisions occurring in the schema.

Cast the 'id' column to a string type.

Why it fails: Casting the column type does nothing to resolve the ambiguity of the column names. Spark's issue is with the duplicate column names in the schema, not with the underlying data type of the keys. This would not prevent the AnalysisException from occurring during the plan resolution phase.

Increase the learning rate during the training phase.

Why it fails: Increasing the learning rate is a model-hyperparameter tuning step, not a data preparation task. It can lead to model instability and gradient explosion. Data imbalance must be addressed at the source data level through sampling techniques before the training process begins, not by tweaking the training parameters.

Convert the target variable to a continuous value.

Why it fails: Converting a classification task into a regression task is not a solution for class imbalance; it changes the nature of the problem entirely. Imbalance is a structural issue within the dataset that must be addressed via sampling to maintain the classification goals of the project.

Ignore the imbalance as models handle it naturally.

Why it fails: Most machine learning models, especially those based on gradient descent, will bias heavily towards the majority class when imbalance exists. Ignoring this will result in poor precision and recall for the minority class, rendering the model ineffective for its intended purpose. It is a critical preparation step.

Window.partitionBy('date').orderBy('date')

Why it fails: Partitioning by the date itself would isolate each day into its own window, resulting in an average calculated only for that single day. This prevents the window function from looking across multiple days, effectively negating the purpose of calculating a multi-day rolling average.

Window.partitionBy('id').rangeBetween(0, 30)

Why it fails: RangeBetween is based on the column value, not the row count. If 'date' is used, this would look for values between 0 and 30 days from the current date. This is incorrect for row-based windowing and does not implement a rolling average correctly as it is highly dependent on date gaps.

Window.orderBy('date').rowsBetween(0, 30)

Why it fails: Using (0, 30) would look at the current row and the 30 *following* rows (future days), effectively calculating a leading average instead of a rolling (historical) average. This is incorrect for standard time-series analysis, where historical trends are analyzed based on past performance, not future values.

Read the JSON files directly using a Python loop and convert them to tensors sequentially.

Why it fails: Sequential processing ignores the distributed computing capabilities of Databricks clusters. The overhead of opening millions of individual files from cloud storage creates significant latency. This approach fails to leverage multi-threaded data fetching, leading to severe under-utilization of GPU resources during the expensive model training phase.

Convert the raw JSON files into a single massive CSV file on the DBFS root.

Why it fails: CSV files lack the schema enforcement and metadata indexing required for high-performance machine learning workflows. Furthermore, storing a massive file on DBFS prevents distributed reads across multiple worker nodes. This creates a single point of failure and a massive bottleneck during the data loading stage.

Use a Pandas UDF to iterate through all JSON files on the driver node.

Why it fails: The driver node has limited memory and compute capacity compared to the cluster workers. Attempting to process millions of files via a Pandas UDF on the driver will trigger an Out-of-Memory error. Distributing the workload across executor nodes is strictly necessary for large-scale data preparation tasks.

Apply heavy stemming and stop-word removal to all text chunks.

Why it fails: Advanced LLMs often perform better with full-context text chunks. Heavy stemming can strip away nuances that transformer-based embedding models use to understand context. Stop-word removal is frequently counterproductive for modern retrieval systems that rely on deep semantic relationships rather than simple keyword matching algorithms.

Convert all text to lowercase to ensure absolute consistency.

Why it fails: While casing consistency is sometimes useful, many state-of-the-art embedding models are sensitive to casing and punctuation. Forcing lowercase conversion can strip away information that models use to distinguish between proper nouns and common terms. This often results in a decrease in retrieval accuracy for domain-specific tasks.

Replace all special characters with numeric placeholders.

Why it fails: Numeric placeholders destroy the semantic value of the text data. Embedding models require the original structure to understand complex relationships between words. Replacing characters with numbers creates noise that the transformer model cannot interpret effectively, leading to poor retrieval performance and inaccurate model predictions during inference.

The field is ignored, and the rest of the record is inserted successfully.

Why it fails: This behavior would occur under a permissive schema evolution policy, not a strict one. Strict schema enforcement is designed to prevent unexpected fields from entering the data lake. Allowing silent field omission can lead to data loss or confusion in downstream analytical processes and LLM retrieval pipelines.

The record is written to a 'dead-letter' queue for manual review.

Why it fails: While this is a best practice for robust data engineering, the specific 'strict_schema' policy in Databricks typically causes a write failure rather than an automatic redirection to a secondary queue. Explicit logic is required to route invalid records to a separate destination for further inspection or remediation.

The system automatically adds the new field as a column to the table.

Why it fails: Automatic schema evolution is a distinct feature from 'strict_schema'. Enabling it would contradict the strict policy requirement. Adding columns automatically can lead to massive, sparse tables that cause performance issues and confusion for users querying the data, which is why it is explicitly disallowed in strict configurations.

Executing embedding generation as a single-threaded Python process on the driver.

Why it fails: Single-threaded processes on the driver node are insufficient for large-scale embedding tasks. They create a massive performance bottleneck and often fail due to memory constraints. Parallel distribution across the worker cluster is the only way to process large volumes of data within reasonable time limits in Databricks.

Avoiding the use of Spark SQL entirely for data cleaning.

Why it fails: Spark SQL is highly optimized and often provides the fastest way to perform data cleaning tasks like filtering, null handling, and casting. Avoiding it would mean using slower, less efficient native Python loops. Leveraging Spark SQL's query optimizer is essential for achieving high performance in Databricks.

Drop the table and re-create it from scratch every week.

Why it fails: Dropping and recreating tables is a destructive, high-overhead operation that destroys history and lineage. It is unnecessary given the built-in maintenance commands like OPTIMIZE. This approach would also interrupt active inference pipelines, making it an unacceptable strategy for production environments that require high availability and stability.

Change the table format to CSV to reduce file management overhead.

Why it fails: CSV format does not support the ACID transactions or the optimized indexing required for high-performance vector lookup. Changing to CSV would eliminate data skipping and concurrency features, leading to extremely slow read performance and potential data integrity issues. This is a regression in capability for any production system.

Disable the Delta transaction log to speed up future writes.

Why it fails: The transaction log is essential for Delta Lake's ACID properties and consistency. Disabling it would break the core functionality of the platform and prevent time-travel and auditability. There is no supported way to disable the log, and attempting to bypass it would result in data corruption.

Ignore the error and let the job continue running.

Why it fails: Ignoring errors in a data pipeline leads to data loss or ingestion failures. The job will eventually stop or produce incomplete datasets. This lack of error handling is dangerous for production systems, as it masks underlying issues that could lead to corrupted downstream models and unreliable predictions.

Enable 'mergeSchema' in the writeStream configuration.

Why it fails: While 'mergeSchema' handles new columns, it cannot automatically reconcile incompatible type changes like String to Integer. Enabling it will not solve this specific error. This option is a common misconception about the extent of Delta's automatic schema evolution capabilities in streaming production environments.

Delete the entire Delta table and reload from the raw source.

Why it fails: This is an extremely inefficient and disruptive way to handle a simple type mismatch. It forces a complete reload of the dataset, which wastes compute resources and interrupts access to the data. Strategic casting is a much faster and more robust solution for managing schema evolution.

Manually copy all PDF files to the local driver node of a standard interactive cluster and execute custom Python scripts to parse them into local CSVs.

Why it fails: Processing unstructured data locally on the driver node creates severe single-node bottlenecks and bypasses scalable distributed computing architectures. This manual workflow lacks proper lineage tracking and fails to leverage cluster resource scaling for large datasets.

Upload the invoices directly into a legacy Hive metastore table as binary blobs without leveraging Unity Catalog security features.

Why it fails: Relying on legacy Hive metastores restricts fine-grained governance capabilities and limits modern integrations required for secure generative AI workflows. Unity Catalog is the modern standard for managing structured, semi-structured, and unstructured data assets uniformly.

Store the PDF binaries inside an unmanaged cloud bucket, bypassing Databricks entirely until the model inference stage requires document retrieval.

Why it fails: Bypassing managed storage and governance layers leads to fragmented data silos and complicates access control enforcement during retrieval-augmented generation. Centralized management within Databricks ensures reliable tracking, auditing, and processing throughout the machine learning lifecycle.

Use the binaryFile data source to read raw bytes and apply a regular expression to strip everything before the first newline and after the last newline.

Why it fails: The binaryFile data source reads PDFs as binary content, not as text. PDFs are compressed and structured, so newline characters do not directly correspond to line breaks. A regular expression on raw bytes would not reliably remove headers or footers and could corrupt the content.

Convert each PDF page to an image, run OCR with a third-party library, and manually delete header and footer regions using bounding box coordinates.

Why it fails: While OCR can extract text, manually specifying bounding boxes for every page is not scalable and is error-prone. This approach also loses the structural metadata that ai_parse_document provides, making it harder to automate the removal of recurring headers and footers across a large corpus.

Load the PDFs with spark.read.format("pdf") and drop the first and last line of each page based on line index.

Why it fails: Spark does not have a built-in "pdf" format for reading PDFs. While some libraries can parse PDFs, they do not provide line-level indexing that reliably corresponds to headers and footers. This approach would fail to correctly identify and remove the targeted content.

schema_of_json

Why it fails: schema_of_json infers the schema from a sample JSON string, but it does not validate individual records against a predefined schema. It is useful for schema discovery, not for enforcing conformance or quarantining malformed records during data preparation for fine-tuning.

json_tuple

Why it fails: json_tuple extracts specific fields from a JSON string but does not enforce a full schema. It returns null for missing fields but cannot validate the entire structure or types. It is less suitable than from_json for schema validation and quarantine of malformed records.

to_json

Why it fails: to_json converts a struct into a JSON string, which is the opposite of what is needed. The team already has JSON strings that must be validated against a schema. Using to_json would not parse or validate the existing strings and would not help identify malformed records for quarantine.

Use the Databricks Vector Search API to directly index the raw text without chunking, relying on its built-in chunking feature.

Why it fails: Databricks Vector Search does not automatically chunk raw text; it requires pre-chunked text and precomputed embeddings. While it can index embeddings, it does not perform chunking. Thus, this approach would not meet the requirement of splitting text into chunks with overlap.

Export the text to a Parquet file on cloud storage, then use a separate Python script with the Hugging Face transformers library on a single node to compute embeddings.

Why it fails: Exporting to cloud storage and using a single node introduces data movement and bottleneck. It is not efficient for large corpora and does not leverage Databricks' distributed processing. This approach would be slow and costly, and it bypasses native Databricks capabilities.

Use a Scala UDF that calls an external embedding API for each row, then write the results to a Delta table.

Why it fails: Calling an external API for each row via a Scala UDF can be slow and may incur network overhead. It also requires managing API keys and rate limits. While it could work, it is less efficient than using Databricks-native Foundation Model APIs or distributed Spark NLP with local models.

Use the ai_query function with a prompt to ask an LLM to normalize the text.

Why it fails: Using an LLM for simple normalization is overkill and costly. It introduces latency and potential inconsistencies. Deterministic string operations are more appropriate for tasks like removing HTML tags, lowercasing, and expanding contractions. LLMs are better for complex tasks like summarization.

Use Delta Live Tables expectations to enforce that text is normalized.

Why it fails: DLT expectations are for data quality validation, not transformation. They can check if text matches a pattern, but they do not perform normalization. Using them would not transform the data; it would only flag non-conforming records, which is not the goal here.

Use the Databricks Feature Store to apply normalization functions during feature computation.

Why it fails: Feature Store is designed for managing and serving features for ML models, not for text normalization. While you could define a UDF, it is not the primary tool for text preprocessing. It would add unnecessary complexity and not provide built-in text normalization capabilities.