Hard Difficulty Questions

Use the 'Cluster Logs' to compare the OS kernel versions of the nodes.

Why it fails: Comparing OS kernel versions is rarely the solution for job failures, as Databricks manages the underlying runtime environment. If there is a disparity, it would be due to selecting different Databricks Runtime versions, not the specific OS kernel, which is abstracted away from the end user.

Enable the 'Debug Mode' on the Spark Driver to see raw system calls.

Why it fails: Debug mode for raw system calls is not a standard feature in Databricks and would not provide useful context for comparing environment configurations. This level of debugging is excessively low-level and does not address the high-level configuration differences that typically cause environment-specific pipeline failures.

Query the 'workspace_users' table to see who ran the job last.

Why it fails: Knowing who ran a job last does not help identify configuration differences. While auditing is useful for security, the technical investigation of why a job fails requires comparing settings, libraries, and code, not the identity of the user who triggered the last execution.

Increase the number of Kafka partitions to improve parallelism.

Why it fails: Increasing Kafka partitions can improve throughput but also increases the number of tasks and potentially the cluster size needed, which may raise cost. It does not directly address the cost of the DLT pipeline itself. The latency issue might be due to other factors, and simply adding partitions may not reduce cost; it could even increase it if the cluster scales up to handle more partitions.

Configure the pipeline to use Photon-optimized clusters.

Why it fails: Photon can improve performance but comes at a higher DBU rate. While it might reduce runtime, the cost per DBU is higher, so overall cost may not decrease. For a continuous pipeline, the cluster runs constantly, so the higher rate could actually increase cost. Photon is beneficial for performance-critical workloads, but not necessarily for cost reduction in this scenario.

Enable autoscaling on the DLT cluster and set a minimum number of workers.

Why it fails: Autoscaling can help with performance but may not reduce cost if the cluster often scales to the maximum. Setting a minimum number of workers ensures the cluster is always running at least that many, which could increase cost. Autoscaling is reactive and may not address the continuous mode's constant resource consumption. It is not the most effective cost-saving change for a continuous pipeline.

Total number of users logged into the workspace.

Why it fails: Workspace user counts say nothing about cluster node utilisation or task scheduling. To confirm whether max_workers nodes are being used efficiently, the engineer needs cluster metrics such as worker utilisation, task queue depth, or executor run time. User metrics belong to workspace administration, not job performance tuning.

The total number of files in the Delta table.

Why it fails: While file counts can impact performance, they do not help identify task-level efficiency or skew on the cluster. They are a storage metadata metric and provide no insight into how the compute nodes are sharing the processing load.

The memory overhead of the driver node.

Why it fails: Driver memory overhead only impacts the orchestration of the job. If the worker nodes are reaching their maximum limit and the job is still failing, the issue lies in the executor task execution, not the driver's memory management.

Increase 'spark.driver.memory'.

Why it fails: Increasing driver memory does not fix the issue, as the broadcast happens on the executor side where the join computation takes place. If the broadcast data is too large, it will still lead to executor-level OOM errors, regardless of how much memory is allocated to the driver node.

Increase the number of shuffle partitions.

Why it fails: Increasing shuffle partitions helps with data parallelism but does not address the issue of broadcast join memory consumption. A broadcast join happens independently of shuffle partitions, so changing this setting will not prevent the executor from running out of memory when trying to hold the large broadcasted table.

Enable 'spark.sql.adaptive.enabled'.

Why it fails: Adaptive Query Execution (AQE) is helpful for optimization, but it can actually decide to convert a shuffle join into a broadcast join if it determines the table is small enough. In a shared cluster, this might still lead to OOMs if the threshold is too high, so manual thresholding is safer.

DLT supports significantly higher throughput than Structured Streaming.

Why it fails: DLT is built on top of Spark Structured Streaming; therefore, its raw processing throughput is inherently comparable. While DLT optimizes job execution, it does not fundamentally change the performance limits of the underlying Spark engine. The primary benefits of DLT are operational, not related to raw engine throughput.

DLT is the only way to read from cloud object storage.

Why it fails: Standard Spark Structured Streaming and Auto Loader are fully capable of reading from cloud object storage without needing DLT. DLT is a higher-level framework for orchestration and quality, not a prerequisite for accessing data in S3, ADLS, or GCS. Many architectures use standard streaming without the DLT layer.

DLT supports non-Delta storage formats for all outputs.

Why it fails: Delta Live Tables is explicitly designed for the Delta Lake format, which is the cornerstone of its functionality, including ACID transactions and schema enforcement. It does not natively support writing output to non-Delta formats like CSV or JSON, as that would undermine the core reliability features of the DLT framework.

Increase spark.driver.memory

Why it fails: The driver memory handles metadata and task scheduling, not the actual data processing for shuffles. Increasing driver memory will not solve an OOM error that occurs during the execution of a shuffle on the worker nodes, as those tasks run in the executor memory space.

Decrease spark.executor.memory

Why it fails: Decreasing executor memory will make OOM errors more likely, not less. Each task needs sufficient heap space to perform data processing, and reducing this allocation will force tasks to fail even earlier when handling large datasets during shuffle stages.

Set spark.sql.shuffle.partitions to 1

Why it fails: Setting shuffle partitions to 1 forces all data into a single partition, which would cause an OOM error for almost any dataset of non-trivial size. This is the worst possible configuration for managing memory in a distributed cluster and would lead to massive performance degradation.

Cluster event logs

Why it fails: Cluster event logs record lifecycle events such as node starts, autoscaling and configuration changes, not per-query resource consumption. They suit diagnosing cluster-level failures. Identifying concurrency-heavy queries requires the SQL warehouse query history, which shows duration and resource usage per statement.

Job run history logs

Why it fails: Job run history logs are scoped to job tasks and do not capture individual queries submitted through the SQL warehouse interface by users or BI tools. Therefore, they are insufficient for debugging concurrency issues on SQL warehouses.

Delta table access logs

Why it fails: These logs are primarily used for security auditing to see who has accessed specific data files. They lack the performance and execution plan details necessary to debug compute resource consumption or concurrency bottlenecks in a SQL warehouse.

Convert the table to a Parquet external table and rely on the query engine to infer partitioning

Why it fails: Moving to a plain Parquet external table discards Delta Lake features such as transaction logs, data skipping statistics, OPTIMIZE, and Z-ORDER or liquid clustering. Without those, the engine cannot benefit from Delta's file-level min/max statistics, so bytes scanned would likely increase rather than decrease for the date-filtered and user-aggregated queries.

Enable Change Data Feed on the table so late-arriving corrections are captured automatically

Why it fails: Change Data Feed records row-level changes for downstream consumers to read incrementally; it does not itself improve scan efficiency or join performance for analytical queries. Enabling it adds storage and write overhead, and it does not address file layout or pruning, so it fails to satisfy the goal of reducing bytes scanned for the date-filtered aggregations.

Partition the table by user_id to maximize file skipping for user-level aggregations

Why it fails: Partitioning by a high-cardinality column such as user_id produces an enormous number of small directories and files, which overwhelms the metastore and slows listing and planning. It also does nothing for the common event_date filter, so queries scanning a date range would still touch most partitions, making this choice counterproductive for the stated workload.

Table ACLs that grant SELECT only on specific columns.

Why it fails: Unity Catalog does not support column-level grants in the GRANT statement. While you can grant SELECT on a table, you cannot restrict which columns a user sees via table ACLs. Column-level security is achieved through column masks or by exposing a view with a subset of columns, so this option does not meet the masking requirement.

Workspace-level IP access list to restrict access to the table.

Why it fails: IP access lists control network origins for workspace access, not row or column visibility within a table. They cannot mask a column or filter rows based on a user's region. This feature addresses a different security concern and does not satisfy the fine-grained data access requirements described.

Dynamic view that joins the table with a mapping table of user regions.

Why it fails: A dynamic view can implement row-level filtering and column masking, but it is a separate object that users query instead of the base table. The scenario asks for fine-grained access control on the Delta table itself, and using a view would not apply the policies directly to the table. Unity Catalog's native row filters and column masks are the intended features here.

To increase the read throughput of Delta tables.

Why it fails: Customer-managed keys are a security feature, not a performance optimization tool. While they provide robust encryption, they do not impact the underlying I/O performance of Delta Lake. Performance improvements are achieved through techniques like Z-Ordering, partitioning, and using efficient file formats, not through key management configurations.

To enable transient data encryption for cluster nodes.

Why it fails: Transient data encryption on cluster nodes is handled by the platform's standard disk encryption. CMK is specifically focused on the long-term encryption of data at rest within the underlying object storage layer, providing a mechanism for regulatory compliance and total data access revocation by the customer.

To bypass the need for Unity Catalog permissions.

Why it fails: Encryption and access control are separate security domains. Even with CMK, Unity Catalog permissions are still strictly required to govern who can query the data. CMK protects the data files in storage; Unity Catalog governs the SQL-level access for users and service principals within the workspace.

Increase the number of executors in the cluster to handle the load.

Why it fails: Adding more executors does not solve the underlying skew problem. If data is skewed, one executor will still get the vast majority of the data regardless of how many others are available. The workload will remain bottlenecked by the single slowest task, regardless of total cluster size.

Use the 'repartition' hint on the join keys to force uniform distribution.

Why it fails: Repartitioning by the join key does not solve skew; it actually exacerbates it. If certain keys occur much more frequently than others, repartitioning will simply group all those heavy keys onto the same partition, leading to the exact same skew issues that you were trying to resolve initially.

Convert the join into a broadcast join to prevent shuffling entirely.

Why it fails: Broadcast joins are only effective when one of the tables is small enough to fit in memory. If the skewed table is large, a broadcast join will cause an out-of-memory error. This technique is not applicable to the problem of a large, skewed table, making it an ineffective solution here.

The Databricks cluster needs to be restarted to apply the new IAM policy.

Why it fails: While policy changes can take time to propagate, restarting the cluster is not a requirement for IAM changes to take effect. If the policy were correct, the cluster would eventually assume the new permissions. The error indicates a fundamental lack of permissions in the current policy, not a latency issue.

The S3 bucket policy is blocking the request despite the IAM policy.

Why it fails: While this is a plausible scenario in AWS, the most direct cause based on the provided exhibit is the IAM policy itself. The question asks why the *job* fails, and the provided policy is clearly insufficient for a write operation, regardless of the bucket policy's state.

The 'Resource' ARN is missing the suffix '/*'.

Why it fails: The exhibit explicitly includes '/*' in the Resource field. This allows the permission to apply to objects inside the bucket. The issue is not the resource definition but the lack of the write action. The syntax of the ARN is correct, but the permission scope is too narrow.

A foreign table that points to the partner's storage location.

Why it fails: A foreign table is used to access data that resides outside the provider's Unity Catalog metastore, often in the partner's storage. This is the opposite of the requirement: the data must remain in the provider's storage. A foreign table would not prevent copying and would not share the provider's table with the partner in a controlled way.

Grant SELECT on the table to the partner's service principal and configure cross-account IAM access.

Why it fails: Granting SELECT to a service principal in another Databricks account is not possible directly because Unity Catalog privileges are scoped to the account. Cross-account IAM access would allow access to storage but not provide a governed way to share the table with the partner's Databricks account. This approach would require significant custom configuration and would not provide a clean sharing interface.

A deep clone of the table into a storage location accessible by the partner.

Why it fails: A deep clone copies both data and metadata to a new location. This would create a duplicate of the data, potentially outside the provider's controlled storage if the partner's location is used, and it requires ongoing synchronization. It increases operational overhead and does not meet the requirement that data not be copied outside the provider's storage.

Configure single-node clusters with maximum autoscaling limits to handle unpredictable concurrency peaks without cluster management overhead.

Why it fails: Single-node clusters completely lack distributed processing capabilities, restricting query execution to a single driver and causing immediate out-of-memory failures on large datasets. They cannot scale out workloads across multiple worker nodes to handle concurrency effectively.

Provision pools of pre-warmed idle driver nodes to ensure zero-second startup latency for all analytical queries.

Why it fails: Pre-warmed idle driver nodes do not address concurrency spikes, since workers execute queries, and idle drivers waste cost. It is tempting because eliminating cold starts sounds attractive, and would suit predictable latency-sensitive workloads, not sporadic unpredictable analytics needing elastic worker scaling.

Disable automatic cluster termination and keep all worker nodes running 24/7 to guarantee immediate resource availability.

Why it fails: Disabling auto-termination causes clusters to run continuously, racking up massive idle charges during off-peak hours and weekends. This is an anti-pattern for cost optimization in cloud environments where resources should scale down when not in use.

Use repartition(200) to evenly redistribute the data across 200 partitions.

Why it fails: repartition performs a full shuffle of the data to evenly distribute it across the specified number of partitions. While it can achieve the desired partition count, it introduces significant network and disk I/O overhead. In this scenario, the engineer wants to minimize shuffling, so repartition is less efficient than coalesce. repartition is better when you need to increase partitions or achieve even distribution, but here the goal is to reduce partitions with minimal shuffle.

Use repartitionByRange(200) on a key column to control data distribution.

Why it fails: repartitionByRange performs a range partitioning shuffle based on the values of a column, which is useful for sorting or range-based operations but involves a full shuffle. It does not minimize shuffling; rather, it introduces a shuffle to redistribute data by range. In this scenario, the objective is to reduce partitions with minimal shuffle, so this method is not appropriate. It would also require specifying a column, which is not mentioned as necessary.

Use bucketBy(200, 'id') and saveAsTable to create 200 buckets.

Why it fails: bucketBy is used for bucketing tables on a column, which can optimize joins and aggregations, but it requires a shuffle during the write and is not a method to simply reduce partitions. It also creates a bucketed table, which may not be desired if the target is a Delta table without bucketing. The scenario asks for reducing partitions before writing, not for bucketing. This approach would not achieve the goal efficiently and could introduce unnecessary complexity.

Use a watermark on event_time and filter records where event_time < current_watermark - INTERVAL 2 HOURS.

Why it fails: Watermarks in Structured Streaming are used to handle late data by defining a threshold for stateful operations, but they do not directly expose a 'current_watermark' value for filtering. You cannot reference the watermark directly in a filter expression. This approach misuses the watermark concept and would not compile or execute as intended.

Use the event_time column and compare it to the current watermark using the watermark() function.

Why it fails: There is no watermark() function in PySpark or Spark SQL that returns the current watermark value. Watermarks are internal to the streaming engine and not exposed as a column or function. Attempting to use watermark() would result in an analysis error. This option is based on a misconception about watermark accessibility.

Use the event_time column and compare it to the maximum event_time in the table minus 2 hours.

Why it fails: Comparing to the maximum event_time in the table identifies records that are older than the latest event by 2 hours, which is not the same as being late relative to processing time. A record could be old but still within the allowed window if no newer events have arrived. This method would incorrectly flag many records and does not address late-arriving data.

Use `@dp.expect_or_drop("valid_amount", "amount >= 0")` on the main table, and rely on the event log to retrieve dropped records for quarantine.

Why it fails: The event log stores metrics about dropped records, but not the actual record contents. You cannot retrieve the dropped rows from the event log. To capture the actual invalid records, you need a separate table that filters the source. This approach fails to provide the quarantine data.

Use `@dp.expect_all_or_fail({"valid_amount": "amount >= 0"})` on the main table, and configure a separate quarantine table using `@dp.expect_all({"invalid_amount": "amount < 0"})`.

Why it fails: `expect_all_or_fail` will fail the pipeline if any record has a negative amount, which violates the requirement to not fail. The quarantine table would not be populated because the pipeline stops. This approach is too strict and does not meet the non-failing requirement.

Use `@dp.expect("valid_amount", "amount >= 0")` on the main table, and create a quarantine table that uses `@dp.expect_or_drop("invalid_amount", "amount < 0")`.

Why it fails: `expect` on the main table does not drop invalid records; they would still be written, violating the requirement. The quarantine table would capture invalid records, but the main table would contain them too. This does not meet the need to exclude negative amounts from the main table.

Faster cold-start reads by caching data on the driver.

Why it fails: Delta Lake improves read performance through metadata-based filtering and Z-Ordering, but it does not cache data on the driver. Driver caching would be inefficient for large datasets. Performance gains come from the optimized file access patterns and metadata pruning enabled by the Delta transaction log and indexing capabilities provided.

Allows the use of SQL as the only way to interact with data.

Why it fails: Delta Lake supports a wide range of APIs, including PySpark, Scala, and Java, in addition to SQL. Requiring only SQL would severely limit the flexibility of data pipelines. Delta Lake is designed to be accessible through the Spark DataFrame API, which is a major advantage for complex data processing tasks.

Users can directly mount external locations as DBFS paths.

Why it fails: Unity Catalog's external location model is intended to replace or augment traditional DBFS mounts. While it provides similar functionality, it does not rely on global DBFS mounts. Instead, it provides granular, policy-driven access to data files, which is a much more secure and manageable approach for enterprise data governance.

External locations are automatically created for every S3 bucket in the account.

Why it fails: Unity Catalog does not automatically import or create external locations for all S3 buckets. This would be a security nightmare, as it would expose all storage resources by default. Administrators must explicitly register each location, ensuring that only necessary data assets are governed and made accessible within the platform.

External locations are only supported for Delta-formatted data.

Why it fails: Unity Catalog supports various data formats in external locations, not just Delta. While Delta is the recommended format for performance and governance, Unity Catalog is designed to be flexible, allowing users to register and govern external tables in formats like Parquet, CSV, or JSON for compatibility with legacy systems.

Increase the number of shuffle partitions to 2000.

Why it fails: Increasing shuffle partitions can help with large data volumes but may create many small partitions, increasing overhead. It does not directly address skew; skewed keys will still cause uneven partition sizes. Without AQE or salting, this may not resolve the issue and could worsen performance due to task scheduling overhead. It is not a targeted solution for skew.

Cache the larger table in memory before the join.

Why it fails: Caching a large table may consume significant memory and cause spilling, potentially slowing down the job. It does not address the shuffle or skew issues. Caching is beneficial for iterative algorithms or repeated access, but for a single join, it may not provide a net benefit and could increase cost by requiring a larger cluster. It is not the most effective optimization here.

Repartition the larger table on the join key before the join.

Why it fails: Repartitioning the larger table on the join key can help with skew but also introduces a full shuffle, which is expensive. If the smaller table is broadcast, no shuffle is needed at all. Repartitioning is often used to mitigate skew, but it should be combined with other techniques like salting. In this scenario, broadcasting is more effective and avoids the shuffle entirely.