Data Transformation and Modeling practice questions

@dlt.view

Why it fails: This decorator creates a virtual table that is recalculated every time it is queried, meaning it does not persist data to storage and cannot be used to append streaming records to an underlying Delta table for historical analysis.

@dlt.materialized_view

Why it fails: This decorator computes and stores the results of a query but is generally used for batch or complete-refresh workloads rather than specialized append-only streaming flows that require appending stream data to target tables.

@dlt.append_flow

Why it fails: This decorator explicitly defines an append-only flow from a streaming source dataset into a target streaming table, ensuring that incoming streaming records are solely appended to the target Delta table without processing updates or deletes.

rank()

Why it fails: The rank() function assigns sequential ranks to rows within a partition, but it leaves gaps in the ranking sequence when duplicate ordering values occur, unlike functions that maintain contiguous numbering.

dense_rank()

Why it fails: This function computes a continuous ranking sequence for rows within a specified partition without leaving gaps in the numbering sequence, even when duplicate values share the exact same ordering criteria.

ntile()

Why it fails: The ntile() function distributes rows of an ordered partition into a specified number of approximately equal groups or buckets, which is used for quantile analysis rather than sequential ranking without gaps.

The Delta table has not been vacuumed, causing schema drift.

Why it fails: Vacuuming removes old files but does not influence the current schema of a streaming DataFrame. Schema drift is managed through Delta Lake options or Spark schema enforcement, not by cleaning up old data files. This error is strictly about a missing column in the current DataFrame's logical plan.

The watermark definition is missing in the streaming write options.

Why it fails: Watermarking defines how long the system waits for late data, but it is not the primary cause of a 'Cannot resolve' column error. A missing column error occurs before the watermark logic is even validated by the Spark optimizer. The engine needs the column present first to apply the watermark.

The cluster is running an outdated Spark version that does not support windowing.

Why it fails: Spark Structured Streaming has supported windowing functions for many years. It is highly unlikely that a Databricks-supported cluster version would lack this core functionality. The error specifically points to a missing column name, which is a structural issue with the input data rather than a platform limitation.

UPDATE target SET * FROM source WHERE target.id = source.id

Why it fails: The standard SQL UPDATE statement does not support simultaneous inserts or complex join logic for upserts in Delta Lake. Attempting to perform an upsert using only an UPDATE command will fail or result in data loss, as it cannot handle the 'when not matched' logic required for new records.

INSERT INTO target SELECT * FROM source ON DUPLICATE KEY UPDATE

Why it fails: The 'ON DUPLICATE KEY UPDATE' syntax is specific to MySQL and is not supported in Spark SQL or Delta Lake. Attempting to use this syntax will cause a syntax error. Delta Lake uses the specific 'MERGE INTO' syntax for performing upserts efficiently within the Databricks environment.

APPEND INTO target FROM source WHERE NOT EXISTS (SELECT 1 FROM target WHERE target.id = source.id)

Why it fails: There is no 'APPEND INTO' command in Spark SQL. While you could technically perform this via a complex query, it would be highly inefficient and non-atomic. The MERGE INTO command is specifically optimized for this use case and handles the concurrency and atomicity of the operation automatically.

Manually delete Parquet files from the underlying cloud storage (S3/ADLS).

Why it fails: Deleting files manually from cloud storage corrupts the Delta Lake metadata. The transaction log will still point to the deleted files, causing future reads to fail with 'FileNotFound' errors. Always use the VACUUM command, which is designed to handle metadata updates and safe file deletion in tandem.

Run an ALTER TABLE DROP PARTITION command to remove old data files instantly.

Why it fails: ALTER TABLE DROP PARTITION is not a native command for Delta Lake. While you can overwrite partitions or use DELETE, the DROP PARTITION syntax is specific to Hive and does not directly apply to Delta tables in the same way, leading to potential inconsistencies in the Delta transaction log.

Recreate the table by overwriting it with a subset of the original data.

Why it fails: While 'INSERT OVERWRITE' is valid, it is a very heavy-handed and risky approach compared to using VACUUM. It requires reading the entire table and rewriting it, which is inefficient and unnecessary. It also risks data loss if the overwrite logic is flawed, making it a poor choice for routine maintenance.

Use standard Spark SQL 'CREATE TABLE' statements.

Why it fails: Standard Spark SQL statements do not provide automatic lineage or managed orchestration. If a job fails, the engineer must manually track dependencies and restart processes, which is not suitable for complex Silver layer pipelines that require automated data quality and robust failure recovery mechanisms in a production-grade environment.

Manually create a pipeline using 'dbutils.notebook.run' for each stage.

Why it fails: Orchestrating pipelines with 'dbutils' is an anti-pattern that leads to fragile, hard-to-maintain code. It lacks automatic lineage and state management, meaning the engineer must write extensive custom logic to handle retries and dependencies. It does not provide the reliability and automated features offered by the native DLT platform.

Execute the transformation using a Databricks Workflow job.

Why it fails: While Databricks Workflows can trigger pipelines, it is an orchestration layer, not a data transformation framework. Workflows manage the scheduling of jobs but do not provide the built-in data quality, lineage, and incremental management logic that is intrinsic to DLT's table definitions. DLT is the specific feature required here.

Relying on 'spark.read.inferSchema=true' for all production tables.

Why it fails: Inferring schema is a convenient feature for exploration but is highly dangerous for production. It requires an extra pass over the data, which degrades performance, and it is prone to errors if the data sample is incomplete. In production, schemas must always be explicitly defined to ensure stability.

Using the 'ALTER TABLE' command to define the schema before ingestion.

Why it fails: The 'ALTER TABLE' command is used to modify existing table structures, not to define the schema during a read operation. Using it for initial schema definition is syntactically impossible for a read job and demonstrates a misunderstanding of the separation between storage DDL and processing logic.

Use the 'permissive' mode and allow the pipeline to continue with nulls.

Why it fails: Permissive mode is risky as it obscures data quality issues. It forces the column values to null when parsing fails, which leads to silent data corruption in downstream analytics. In professional engineering, we require explicit error handling and auditing, which permissive mode does not provide for legacy data pipelines.

Convert the entire CSV to Parquet before reading, assuming it fixes formatting.

Why it fails: Conversion does not magically fix format inconsistencies or malformed records. If the CSV is malformed, the conversion process will either fail or propagate the errors into the Parquet file. This adds an unnecessary, expensive intermediate step that provides no real value for data quality improvement in the ingestion pipeline.

Use a regex-based parser in a UDF to clean the data before schema application.

Why it fails: UDFs in Spark are notoriously slow because they force the data to be moved from the JVM to the Python interpreter. This drastically lowers performance and scalability. Native Spark operations are always preferred over custom UDFs for data cleaning and transformation, ensuring the pipeline remains performant as volume grows.

EXPECT (user_id IS NOT NULL)

Why it fails: This constraint is a standard EXPECT clause, which drops rows that fail the validation criteria. While it maintains data quality, it does not fulfill the requirement to quarantine the invalid records; instead, it simply discards them from the final table, resulting in permanent data loss for the records missing a user_id.

CONSTRAINT user_id_check EXPECT (user_id IS NOT NULL) ON VIOLATION FAIL UPDATE

Why it fails: This configuration instructs the DLT pipeline to halt execution entirely upon encountering a row that violates the constraint. While this ensures strict data integrity, it contradicts the requirement to process the valid data while merely isolating the invalid records, leading to unnecessary downtime for the entire data processing pipeline.

EXPECT (user_id IS NOT NULL) ON VIOLATION FAIL UPDATE

Why it fails: Actually, the question specifically asks for quarantine. Since DLT currently handles quarantining via event logs or separate tables, the most appropriate way to handle invalid data without stopping the pipeline is utilizing constraints that track violations. In professional DLT workflows, this involves capturing violations to a separate audit table.

Include all columns from the Bronze table to ensure maximum flexibility.

Why it fails: Including all raw columns in a Gold table creates a 'wide' table that is inefficient for BI tools to query. BI tools perform better on narrow tables that contain only the necessary business attributes, improving both storage efficiency and query performance, which is essential for responsive dashboard experiences.

Use high-cardinality columns for partitioning to improve filter performance.

Why it fails: Partitioning on high-cardinality columns causes the small file problem, which degrades performance across the board. Partitioning should only be used for low-cardinality columns where query filtering is common, such as 'region' or 'year', to ensure efficient metadata management and prevent excessive file counts in the underlying storage.

It automatically picks the first record and ignores the others.

Why it fails: Delta Lake's MERGE command is designed to be deterministic. It does not arbitrarily select a record; instead, it enforces a strict rule that the source must not contain duplicates on the join key. Allowing it to pick a random record would introduce inconsistency and violate ACID guarantees for the database operation.

It performs an 'upsert' on all duplicate records in the target.

Why it fails: Performing an upsert on all duplicates would lead to unexpected data states where the target is overwritten multiple times in a single transaction. Since MERGE is meant to be a precise operation, allowing multiple updates to the same target row via source duplicates would negate the purpose of the key-based logic.

It updates the target once and inserts new rows for the remaining duplicates.

Why it fails: This behavior would break the idempotency expected from a MERGE operation. The engine does not distinguish between duplicates to split them into updates and inserts; it treats the entire batch as an invalid operation because the join condition is ambiguous, which is a core safety feature of Delta Lake.

Enable 'mergeSchema' in the write option

Why it fails: The 'mergeSchema' option does the opposite of schema enforcement; it allows the target table schema to evolve by adding new columns from the source data. While useful for schema evolution, it does not provide the strict validation required to reject non-conforming data, which is the primary goal of schema enforcement.

Set the table property 'delta.columnMapping.mode' to 'none'

Why it fails: Column mapping is used to support column renaming and dropping without rewriting data, which is independent of schema enforcement. It does not provide validation of incoming data types or structures against the table's schema, so it is not the correct solution for rejecting records that fail validation requirements.

Use a UDF (User Defined Function) to validate data types

Why it fails: While a UDF could technically check types, it is inefficient, slow, and hard to maintain compared to native Delta schema enforcement. Using built-in Delta capabilities is always preferred for performance and reliability, as native features are optimized to run within the Spark engine without the overhead of row-level UDF execution.

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.schemaEvolutionMode", "failOnNewColumns").load(path)

Why it fails: Configuring schema evolution mode as failOnNewColumns will explicitly abort the streaming query when encountering any unexpected column structure. This behavior directly contradicts the requirement to prevent pipeline failures and actively interferes with maintaining continuous data ingestion from unstable upstream JSON sources.

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.schemaEvolutionMode", "none").load(path)

Why it fails: Setting the schema evolution mode to none forces Auto Loader to strictly adhere to the initially inferred schema. Any newly introduced fields in incoming JSON files will be completely ignored or dropped during parsing, preventing downstream analysis teams from ever capturing or inspecting the newly added data attributes.

spark.readStream.format("cloudFiles").option("cloudFiles.format", "json").option("cloudFiles.inferSchema", "false").load(path)

Why it fails: Disabling schema inference altogether forces Spark to treat all incoming JSON payload fields as string types or fail completely depending on reader options. This configuration does not address schema evolution or provide a safe mechanism for capturing newly appearing columns inside the target table structure.

@dlt.expect("valid_id", "id IS NOT NULL")

Why it fails: The expect decorator only logs data quality violations into the event log and metrics while allowing invalid records to pass through into the target table. This behavior fails to meet the requirement of actively dropping records that fail null checks on critical identifier fields.

@dlt.drop_where("id IS NOT NULL")

Why it fails: The drop_where syntax does not exist as a valid decorator function within the Delta Live Tables Python API framework. Data quality constraints must be declared using valid expectation functions combined with appropriate violation handling clauses like drop or fail.

@dlt.fail_update("valid_id", "id IS NOT NULL")

Why it fails: fail_update aborts the pipeline when the condition is violated, so it cannot drop failing rows; dropping null-ID records requires @dlt.expect_or_drop instead. fail_update suits halting on invalid business logic, which is the second requirement, not the null-check filtering described here.

Delete the directory specified in the checkpointLocation option and restart the stream.

Why it fails: Deleting the checkpoint directory resets the streaming state metadata, causing the stream to lose its progress tracking offsets and re-process historical files from scratch. Furthermore, it does not prevent the AnalysisException from recurring once the engine encounters the new column again.

Set .option("mergeSchema", "true") on the writeStream portion of the Spark query.

Why it fails: While mergeSchema is valid for batch Delta writes or specific streaming scenarios, Auto Loader's schema inference and evolution are primarily governed during the read phase using cloudFiles-specific options. Setting mergeSchema alone on the writer does not resolve Auto Loader's internal reader schema validation checks.

Change the cloudFiles.format option from csv to json to automatically bypass schema checks.

Why it fails: Changing the underlying file format from CSV to JSON is logically incorrect because the source files are CSV format. Changing formats will cause parsing failures due to mismatched syntax and does not solve schema evolution challenges inherent to streaming file ingestion.

Join the two streaming tables directly using standard DataFrame join syntax without any time constraints.

Why it fails: Executing a stateful streaming join without watermarks causes the Spark engine to retain all historical state indefinitely, eventually leading to driver and executor memory exhaustion errors during long-running pipeline executions.

Convert both streaming tables to static tables using `.read` before executing the join operation in the pipeline definition.

Why it fails: Converting streaming tables to static tables defeats the purpose of incremental continuous processing in Delta Live Tables, forcing the pipeline to reprocess entire underlying datasets rather than handling incremental updates.

Use a broadcast join hint on the larger streaming table to bypass state management requirements during the join.

Why it fails: Broadcast hints cannot eliminate the fundamental requirement for state management in streaming-to-streaming joins, as Spark still needs to buffer incoming rows from both sides to match records arriving at different times.

spark.sql("MERGE INTO sales AS t USING new_data AS s ON t.sale_id = s.sale_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *")

Why it fails: This MERGE statement updates matching records and inserts new ones based on sale_id. It does not delete records in the target partition that are absent from new_data. If the goal is to fully replace the partition, any old records not present in new_data would remain, which does not meet the overwrite requirement.

spark.sql("DELETE FROM sales WHERE sale_date = '2024-01-01'"); spark.sql("INSERT INTO sales SELECT * FROM new_data")

Why it fails: This approach deletes the target partition and then appends new data. While it achieves the desired end state, it is not atomic; if the INSERT fails, the partition is left empty. The question asks for a single command to overwrite the partition, and this is a two-step process that lacks atomicity, making it less desirable than a direct overwrite.

spark.sql("INSERT OVERWRITE TABLE sales SELECT * FROM new_data")

Why it fails: This is a dynamic partition overwrite. Without specifying a partition, it overwrites all existing partitions with the data from new_data. Since new_data contains only records for '2024-01-01', all other partitions would be deleted, causing data loss. The engineer needs to preserve other partitions, so this command is incorrect.

Set the `startingOffsets` option to `earliest` and enable `ignoreDeletes`.

Why it fails: `startingOffsets` set to `earliest` will cause the pipeline to process all historical data from the beginning of the Kafka topic on the first run, which violates the requirement to process only new data. `ignoreDeletes` is unrelated to Kafka ingestion. This configuration would lead to unnecessary reprocessing of old data.

Set the `startingOffsets` option to `latest` and configure a watermark of 7 days using `withWatermark`.

Why it fails: `startingOffsets` set to `latest` is correct, but `withWatermark` is used for handling late data in stateful operations like streaming aggregations, not for controlling which data is read from Kafka. The requirement is to process only new data and handle late-arriving data up to 7 days old; a watermark does not affect the ingestion offsets. Thus, this configuration does not meet the ingestion requirement.

Use `spark.readStream.format("kafka")` with `startingOffsets` set to `latest` and set `failOnDataLoss` to `false`.

Why it fails: While `startingOffsets` set to `latest` is correct, setting `failOnDataLoss` to `false` can cause silent data loss if offsets are out of range, which is risky. Moreover, the question specifies a DLT pipeline; using raw `spark.readStream` bypasses DLT's managed checkpointing and expectations. DLT provides its own mechanisms to handle streaming sources, so this approach is not appropriate.

Collect statistics on the `id` column using `ANALYZE TABLE ... COMPUTE STATISTICS`.

Why it fails: Delta Lake automatically collects statistics on the first 32 columns of each file, including min/max values, which are used for data skipping. Manually computing statistics with ANALYZE TABLE is not necessary for merge performance and does not provide additional benefits for the `id` column. It is more relevant for query optimization in some cases but not for merge.

Increase the `spark.sql.shuffle.partitions` setting.

Why it fails: Increasing shuffle partitions can help with large shuffles, but it does not reduce the amount of data scanned during a merge. The merge performance bottleneck is often the scan of the target table, which can be mitigated by data skipping via Z-Ordering. Adjusting shuffle partitions is a general tuning parameter but not specific to reducing scanned data for merge.

Partition the table by the `id` column.

Why it fails: Partitioning by a high-cardinality column like `id` can lead to a large number of small partitions, which degrades performance due to excessive metadata and file listing overhead. While partitioning can help with data skipping, it is not recommended for high-cardinality columns. Z-Ordering is a better choice for such columns.

SELECT customer_id, order_id, order_date, amount FROM sales.orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) <= 10

Why it fails: Databricks SQL does not support the QUALIFY clause for filtering window function results. The query would fail with a parse error. The correct pattern in Databricks SQL is to compute the window function in a subquery or common table expression and then filter the resulting rank column in an outer query, as shown in the option using a derived table.

SELECT customer_id, order_id, order_date, amount FROM sales.orders GROUP BY customer_id ORDER BY order_date DESC LIMIT 10

Why it fails: This statement is invalid in Databricks SQL because order_id, order_date, and amount appear in the SELECT list but are not part of the GROUP BY clause or wrapped in an aggregate function. Even if it ran, grouping would collapse rows per customer rather than ranking the individual orders, so it cannot return the 10 most recent orders for each customer.

SELECT customer_id, order_id, order_date, amount FROM sales.orders ORDER BY order_date DESC LIMIT 10

Why it fails: A global ORDER BY with LIMIT 10 returns only the 10 most recent orders across the whole table, not 10 per customer. Customers with many recent orders would consume all the rows while other customers would be omitted entirely. This does not satisfy the per-customer requirement and can also force a full sort of the table before the limit is applied.