Developing Code (Python/SQL) practice questions

Enable delta.enableChangeDataFeed.

Why it fails: Change Data Feed tracks row-level changes for downstream consumption but does not fundamentally resolve metadata lock contention between concurrent write operations. While useful for auditing and incremental processing, it is not the primary mechanism for preventing write conflicts when multiple processes attempt to commit to the table simultaneously.

Set 'delta.autoOptimize.optimizeWrite' to false.

Why it fails: Disabling optimizeWrite actually forces the write operation to produce smaller, more numerous files, which exacerbates metadata overhead and increases the probability of transaction log conflicts. This setting is detrimental to high-concurrency environments, as it increases the time spent managing individual file writes during the commit process.

Use the 'APPEND' mode exclusively for all writers.

Why it fails: While append-only operations are less prone to conflicts than updates or deletes, they still interact with the same transaction log. If multiple writers append simultaneously, they still compete for the metadata lock. Append mode does not inherently resolve concurrency conflicts in high-volume streaming ingest scenarios without proper isolation configuration.

Delta Lake allows concurrent MERGE operations on the same table without restriction.

Why it fails: Delta Lake enforces optimistic concurrency control. If two concurrent MERGE operations attempt to modify the same set of files or the same partition, the transaction will fail. One of the operations must be retried after the first one successfully commits to avoid potential data corruption or inconsistencies.

MERGE operations will automatically fail if the source table has more rows than the target table.

Why it fails: The number of rows in the source and target datasets is irrelevant to the MERGE operation's success. MERGE is designed to handle varying data volumes. Its performance depends on the number of matches and files processed, not the total row count comparison between the two input sources.

Schema evolution during MERGE is only supported for dropping columns.

Why it fails: Schema evolution in MERGE specifically supports adding new columns to the target table. Dropping columns is not a standard operation during a merge process, as Delta Lake primarily focuses on additive schema changes to ensure data backward compatibility and prevent accidental data loss during the synchronization of datasets.

The table does not exist in the metastore.

Why it fails: If the table did not exist, the error would specifically state that the table or view could not be found. The presence of column suggestions implies the table and its schema were successfully located by the parser, but the specific column referenced was not found as expected.

The user lacks permissions to read the specific column.

Why it fails: Permissions issues usually manifest as an Authorization error rather than an AnalysisException regarding column resolution. The system would explicitly state that the user does not have access to the resource, rather than offering suggestions for what the column name might have been intended to be.

The Delta table files are corrupted.

Why it fails: Corrupted Delta files typically result in internal exceptions or errors indicating an inability to read the transaction log or Parquet footer. An AnalysisException is a query-time parsing error that occurs before the system even attempts to access the physical data files residing in the storage layer.

The retention period is set to 0, requiring immediate deletion of all history.

Why it fails: Setting a retention period to zero or a very low value forces the engine to verify and remove nearly all historical versions and unreferenced files, which places a massive load on the file system's metadata service. This is particularly resource-intensive in object storage environments with high latency.

The table is currently being read by another process.

Why it fails: Vacuuming is an independent maintenance task. While it may compete for I/O resources, it does not require exclusive access to the table to run. Reading processes continue to function using the current snapshot, while Vacuum identifies files that are no longer needed for any active version.

Vacuum requires that the cluster has enough memory to load the entire table.

Why it fails: Vacuum does not load table content into memory. It operates primarily as a metadata and file system task. It needs to list files in the directory and compare them against the Delta log, which is a process that is memory-efficient regardless of the size of the underlying data.

Increase the number of shuffle partitions to 1000.

Why it fails: Increasing shuffle partitions only helps if there is excessive data movement across a large cluster. For a single-row update, it will actually add unnecessary overhead, as the engine will spend more time managing the shuffle than performing the actual data update, leading to increased latency for the entire operation.

Drop and recreate the table with a new schema.

Why it fails: Dropping and recreating a table is an extremely destructive and slow operation. It is not a sustainable maintenance strategy for production pipelines, as it causes massive downtime and loses all historical transaction data, making it unsuitable for operational workflows that require frequent or even occasional updates to existing data records.

Perform the update using a Python UDF.

Why it fails: Python UDFs are significantly slower than native Spark SQL operations because they force data to be serialized between the JVM and the Python interpreter. For data-intensive operations like updating large tables, using native SQL expressions is always preferred for performance and stability to avoid serialization overhead and memory issues.

@dlt.expect_or_drop('drop_null', 'customer_id IS NOT NULL')

Why it fails: The @dlt.expect_or_drop decorator is the correct mechanism to enforce data quality by dropping records that violate the specified predicate. By providing a constraint name and the SQL expression as strings, the DLT engine correctly identifies which records to filter out during the transformation stage.

ALTER TABLE table_name ADD CONSTRAINT customer_id IS NOT NULL

Why it fails: Standard SQL DDL commands like ALTER TABLE are generally not used directly within the DLT pipeline definition code. DLT manages table schemas and constraints declaratively through decorators or specific functions, ensuring that the lineage and data quality metrics are captured properly within the DLT event log.

dlt.validate(customer_id, 'NOT NULL')

Why it fails: There is no dlt.validate function in the Delta Live Tables Python API. DLT relies on decorator-based expectations applied to functions that return a Spark DataFrame. Using non-existent library functions will result in an AttributeError when the pipeline attempts to initialize the graph during execution.

Increase the number of partitions using repartition() on the skewed column.

Why it fails: Simply increasing the number of partitions via repartitioning does not solve skew if the data is already heavily concentrated in specific keys. The skewed keys will still end up on the same partitions, leading to the same performance issues despite having a higher overall partition count.

Use a broadcast hint for the larger table in the join.

Why it fails: Broadcast joins are only effective when one of the tables is small enough to fit into the memory of every executor. Forcing a broadcast on a large, skewed table will trigger an OutOfMemory error as the executor attempts to load more data than its heap can handle.

Set spark.sql.shuffle.partitions to a much lower value.

Why it fails: Reducing the number of shuffle partitions usually exacerbates skew issues by grouping more data into each individual task. This leaves fewer tasks to process the large volume of data, resulting in even longer execution times for the skewed partitions and increasing the probability of executor crashes.

Use the dlt.write() method for all DataFrames.

Why it fails: The dlt.write method is restricted to the DLT runtime context. If used in a standard notebook or job cluster, it will throw a NameError or ModuleNotFoundError because the dlt library is not injected into the standard Spark session environment in non-DLT workloads.

Manually insert metadata into a separate SQL table.

Why it fails: Manually tracking lineage via metadata tables is error-prone and decoupled from the actual data movement. This approach does not leverage Databricks' native observability features, meaning the lineage information will not appear in the Data Explorer UI, making it difficult for users to trace data upstream/downstream.

Use the Spark UI 'Stages' tab to view lineage.

Why it fails: The Spark UI 'Stages' tab displays technical execution information like shuffle read/write and task duration. It does not provide data lineage, which describes the flow of data from source to destination. Lineage requires a platform-level understanding of table dependencies, which the Spark UI does not provide.

To manage the checkpoint state and file metadata.

Why it fails: The schema location acts as a persistent store for file discovery metadata. This allows Auto Loader to track which files have been ingested and which are new, ensuring that even if the cluster restarts, the pipeline continues processing from the exact point it left off.

To move the raw files into a Delta table.

Why it fails: Auto Loader is a streaming source that reads files; it does not move the raw files themselves into the Delta table. It reads the raw data into a DataFrame, which can then be written to a Delta table, but the schema location is not involved in moving files.

To specify the destination path for the output table.

Why it fails: The output table path is defined in the .writeStream() or .table() method. The schema location is an internal directory used by the Auto Loader source, distinct from the actual target data location where the Delta table records are ultimately persisted.

DROP TABLE and CREATE TABLE

Why it fails: Dropping a table removes all associated metadata, including grants, history, and table properties. This requires re-applying permissions, which is inefficient and creates a security gap between the drop and the recreate. It also loses the transaction history, making auditing difficult.

Use the vacuum command to delete all data.

Why it fails: VACUUM is intended to remove stale data files that are no longer referenced by the table version history. It is not designed for clearing out all data in a table and cannot be used as a replacement for truncating or overwriting data in a single operation.

Change the table location to a new path.

Why it fails: Changing the table location via ALTER TABLE is a metadata-level change that does not replace the data in the existing storage path. It points the table to a different directory, which is a dangerous operation that can lead to orphaned data files and inconsistencies.

WHEN MATCHED THEN UPDATE

Why it fails: This clause only handles existing records. It does not perform an insert for new records, so using only this clause would result in the loss of all new data that does not already have a corresponding record in the target table.

WHEN MATCHED AND target.id IS NULL THEN INSERT

Why it fails: This logic is syntactically invalid because if a row is matched, the target ID cannot be null (assuming the join condition relies on the ID). Furthermore, 'WHEN MATCHED' is intended for existing records, not for the insertion of new records based on join failures.

INSERT INTO ... WHERE NOT EXISTS

Why it fails: While this SQL pattern works in some relational databases, it is not the standard or most efficient way to perform conditional upserts in Delta Lake. MERGE INTO is the native, optimized construct provided by Delta for handling conditional inserts and updates in a single atomic transaction.

It enables automatic deletion of all files older than the retention period.

Why it fails: VACUUM is not an automatic process; it must be triggered manually or via a scheduled job. Disabling the retention check does not enable automated file deletion. Instead, it merely removes the safety barrier, allowing the user to specify a retention period shorter than the default seven-day threshold during execution.

It prevents the Delta table from being accessed by concurrent readers.

Why it fails: The retention duration configuration has no impact on concurrency control or reader access. Delta Lake maintains ACID transactions through optimistic concurrency control regardless of the VACUUM retention policies. Readers will continue to access the table, though they may face issues if they attempt to query deleted historical snapshots.

It automatically upgrades the table to the latest Delta Lake protocol version.

Why it fails: This configuration change is entirely unrelated to the Delta Lake protocol version or feature capabilities. Protocol version upgrades are handled by the 'ALTER TABLE ... SET TBLPROPERTIES' command, which manages the reader/writer compatibility. This setting is strictly a safety override for storage maintenance operations and does not affect table architecture.

A low-cardinality boolean flag column.

Why it fails: Z-Ordering is ineffective on low-cardinality columns. With only a few distinct values, the data would not be distributed meaningfully across files. Columns with high cardinality are preferred because they provide enough granularity for the storage engine to prune large portions of the dataset during query execution.

A timestamp column used for range-based filtering.

Why it fails: Z-Ordering on high-cardinality columns like timestamps or unique identifiers allows for effective file skipping. When combined with filters, the engine can quickly determine which files contain the relevant range, minimizing I/O. This is a best practice for time-series data or logs frequently queried by time-based ranges.

A column used strictly for joining with other tables.

Why it fails: Z-Ordering is designed to optimize data skipping for filter predicates, not join operations. While data locality can sometimes help with joins, Z-Ordering is not the primary mechanism for join optimization. Broadcast joins or partition-based optimizations are much more effective for improving the performance of join operations.

A column that contains large, unstructured text blobs.

Why it fails: Z-Ordering large, unstructured text columns is counterproductive and inefficient. These columns do not have the cardinality or range properties necessary for effective Z-Ordering. Additionally, sorting by large text fields consumes significant compute resources without providing the data-skipping benefits needed to justify the cost of the operation.

Enable Auto Compaction on the source Delta table by setting delta.autoOptimize.autoCompact to true.

Why it fails: Auto Compaction optimizes file layout after writes but does not prevent FileNotFoundException caused by concurrent updates. The error arises because the source table is being modified while the job reads it, and Auto Compaction does not provide snapshot isolation for the read. Therefore, this setting will not resolve the concurrency issue.

Use the option 'versionAsOf' to read a specific version of the source table.

Why it fails: Reading a specific version via versionAsOf provides a consistent snapshot at that version, but it is a static read and will not include new data written after that version. The job likely needs the latest data, so pinning to an old version is not appropriate. Additionally, it does not address the root cause of concurrent updates causing file deletion during the write.

Set the Spark configuration spark.databricks.delta.snapshotPartitions to a higher value.

Why it fails: The snapshotPartitions configuration controls the number of partitions used when reading Delta Lake metadata, not the isolation level for concurrent writes. Increasing it may improve read performance but does not prevent FileNotFoundException when the underlying files are deleted or changed by another job. Thus, it is not the correct solution for this scenario.

df.repartition(100)

Why it fails: Repartition changes the number of partitions but does not cache the DataFrame. It triggers a shuffle and does not store data in memory or disk for reuse. It is unrelated to caching. This method would not avoid recomputation; it only redistributes data.

df.cache()

Why it fails: The cache method uses the default storage level, which is MEMORY_AND_DISK in Spark. However, in some contexts, the default may be MEMORY_ONLY, and the question specifies caching in memory and on disk. While cache() often defaults to MEMORY_AND_DISK, it is not explicit. The question asks for a method to cache in memory and on disk, and persist with explicit level is more precise.

df.checkpoint()

Why it fails: Checkpointing saves the DataFrame to a reliable storage (e.g., HDFS) and truncates the lineage. It is not a caching mechanism for in-memory storage. It is used for truncating lineage and recovery, not for caching in memory and disk. Therefore, it does not meet the requirement.

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN INSERT * WHEN NOT MATCHED THEN UPDATE SET *

Why it fails: The actions are reversed: WHEN MATCHED should update, and WHEN NOT MATCHED should insert. Using INSERT for matched records would cause duplicates or errors because the record already exists. Using UPDATE for unmatched records would fail because there is no existing record to update. This is semantically incorrect.

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT *

Why it fails: This statement deletes matched records instead of updating them, and inserts new records. This would remove existing records when they match the staging data, which is the opposite of an upsert. The goal is to update existing records, not delete them. This does not achieve the desired outcome.

MERGE INTO target USING staging ON target.id = staging.id WHEN MATCHED THEN UPDATE SET target.* = staging.* WHEN NOT MATCHED THEN INSERT *

Why it fails: The UPDATE clause uses target.* = staging.*, which is not valid syntax in Delta Lake MERGE. The correct syntax is UPDATE SET * or explicitly listing columns. Using target.* = staging.* would cause a parse error. The rest of the statement is correct, but this syntax error makes it invalid.

Use a foreachPartition action that writes results to an external system, and load the lookup table inside the function for each partition.

Why it fails: foreachPartition is an action, not a transformation, so it does not return a DataFrame and is intended for side effects like writing to external systems. While loading the lookup table per partition is efficient, using an action here does not fit the requirement to transform the DataFrame and continue processing. It would also break the lineage and require separate handling of results.

Use a pandas UDF with the function decorated with @pandas_udf, and inside the function load the lookup table from a file each time.

Why it fails: Loading the lookup table inside the pandas UDF for each batch or each call would repeatedly read the 2 GB file, causing severe performance degradation. Pandas UDFs are efficient for vectorized operations, but they do not automatically cache external data across executors. The lookup table should be loaded once per executor, not per row or per batch.

Broadcast the lookup table as a Spark DataFrame and perform a join with the large DataFrame before applying the Python function.

Why it fails: Broadcasting a 2 GB lookup table is generally not feasible because Spark broadcast variables are limited by spark.sql.autoBroadcastJoinThreshold (default 10 MB) and memory constraints. Forcing a broadcast of 2 GB would likely cause out-of-memory errors on executors. A regular join would shuffle data, but the question focuses on reusing the lookup table within a Python function, not on join optimization.

ALTER TABLE users ADD CHECK (email LIKE '%@%.%');

Why it fails: While this syntax is close, it lacks the CONSTRAINT keyword and a name for the constraint. Delta Lake requires a named constraint for management and error reporting. Without a name, the statement may fail or be ambiguous. Therefore, it is not the recommended way to add a constraint.

CREATE CONSTRAINT valid_email ON users (email) CHECK (email LIKE '%@%.%');

Why it fails: The CREATE CONSTRAINT syntax is not valid in Delta Lake. Constraints are added to existing tables using ALTER TABLE ... ADD CONSTRAINT. This statement would result in a syntax error and not enforce the desired validation. Thus, it is not the correct approach.

ALTER TABLE users SET TBLPROPERTIES ('constraints' = 'email_format');

Why it fails: Delta Lake does not use table properties to define constraints. The 'constraints' property is not a valid way to enforce data quality rules. Constraints must be added using the ADD CONSTRAINT syntax with a CHECK expression. This option would not enforce any validation and is therefore incorrect.

Set spark.sql.shuffle.partitions to a higher value to increase parallelism.

Why it fails: Increasing shuffle partitions would create more partitions and likely produce even more small files, worsening the problem. The goal is to reduce the number of output files, so you would typically decrease the number of partitions before writing, not increase them. Higher parallelism can help with large shuffles but does not address small file consolidation.

Enable adaptive query execution (AQE) and set spark.sql.adaptive.coalescePartitions.enabled to true.

Why it fails: AQE with partition coalescing can reduce the number of partitions dynamically during shuffles, but it does not guarantee a reduction in output files for all write scenarios, especially if the final partitioning is determined by the write operation. While AQE is beneficial, coalesce is a more direct and deterministic way to control the number of output files before writing.

Repartition the DataFrame by the partition column of the target table before writing.

Why it fails: Repartitioning by the partition column would create one file per partition value, but if the target table is not partitioned by that column, it could still result in many files. Moreover, repartitioning triggers a full shuffle, which is expensive. The requirement is to reduce files without changing partitioning, so repartition by an arbitrary column is not appropriate.

Install the module as a wheel package using %pip install and then import it.

Why it fails: Installing the module as a wheel package requires packaging and distributing it, which is overkill for a simple project within Repos. While this approach works, it is not the simplest or intended method for importing a local module in a Repos environment. It adds unnecessary complexity and build steps.

Use dbutils.library.install to install the module from the repo.

Why it fails: dbutils.library.install is deprecated and used for installing libraries, not for importing local modules. It does not add the module to the Python path. This approach is outdated and not recommended for importing modules within a Repos project. It would not achieve the desired import.

Use the %run magic command to execute the Python module as a notebook.

Why it fails: The %run magic command is used to execute another notebook, not a Python module file. It cannot import functions from a .py file. While it can run a notebook that contains code, it does not provide module import functionality. Therefore, it is not suitable for importing a Python module.