Executing Queries with Databricks SQL practice questions

SELECT * FROM table_name VERSION AS OF '2023-10-01'

Why it fails: Version-based time travel requires an integer identifier representing the transaction log version, not a date string. Using a date string in this syntax will trigger a parsing error, as the engine expects a long integer. This approach is incorrect for filtering by a specific relative time window.

SELECT * FROM table_name TIMESTAMP AS OF CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Why it fails: The TIMESTAMP AS OF clause instructs the Databricks SQL engine to retrieve the table state as it existed at the specified point in time. This forces the engine to access only the files valid at that timestamp, significantly reducing the amount of data read from cloud storage compared to standard filtering.

SELECT * FROM table_name OPTIMIZE FOR 24_HOURS

Why it fails: The OPTIMIZE keyword is used in DDL statements to compact small files in a Delta table to improve read performance. It is not a valid clause for SELECT statements or a method for filtering query results by time. Using this syntax will result in a syntax error during query parsing.

Run the ANALYZE TABLE command.

Why it fails: ANALYZE TABLE updates the table statistics used by the optimizer to generate efficient query plans. While this is a good practice for performance optimization, it does not address the lack of disk space for intermediate query results. It will not prevent the disk full error during query execution.

Clear the browser cache and try again.

Why it fails: Browser cache only holds client-side UI data. It has zero impact on the server-side operations of the SQL Warehouse. The DISK_FULL error occurs on the cloud infrastructure nodes, which are completely independent of the analyst's local web browser cache or individual client settings.

Restart the Databricks cluster.

Why it fails: Restarting the cluster might clear temporary files, but it does not solve the root cause: the query itself requires more disk space than is currently allocated. The error will likely recur as soon as the query is executed again, regardless of the cluster state. Scaling is the required fix.

Force a broadcast join on both tables.

Why it fails: Broadcasting requires the entire table to fit into the memory of every worker node. For two large tables, this is physically impossible and will cause an out-of-memory error. Broadcasting should only be used when at least one of the tables is small enough to fit in memory.

Partition the tables on the join keys.

Why it fails: Partitioning on join keys can lead to significant performance gains if the query filters include the partition columns. It allows the engine to perform partition pruning, effectively limiting the data read from storage to only the partitions relevant to the join, which reduces I/O and speeds up execution.

Convert all tables to CSV format.

Why it fails: CSV is a text-based format that lacks the schema metadata and advanced indexing capabilities of Delta tables. Converting to CSV would eliminate data skipping and compression benefits, leading to massive performance regressions for any analytical query. Delta tables are highly optimized for SQL and should always be preferred.

Use a single-node warehouse.

Why it fails: A single-node warehouse is intended for very small datasets or development tasks. It lacks the distributed compute resources required to process large-scale joins across multiple workers. For large table joins, a multi-node cluster is essential to provide the parallel processing power needed to finish the query in reasonable time.

Enable Auto-Stop on the SQL Warehouse.

Why it fails: Auto-stop is a cost-management feature designed to terminate compute resources after a period of inactivity. It does not provide mechanisms for handling concurrency or managing write locks on Delta tables. Enabling it will reduce compute costs but will not improve performance during concurrent read and write operations.

Increase the maximum number of clusters.

Why it fails: Increasing the number of clusters helps with query queuing, but it does not resolve locking contention at the storage level if the underlying write processes are blocking reads. Without serverless capabilities, traditional multi-cluster scaling might still face latency issues when Delta transaction logs are heavily contested.

Reduce the warehouse size to X-Small.

Why it fails: Reducing warehouse size decreases compute capacity, which typically increases the duration of complex queries. Smaller warehouses have fewer resources to process data, leading to longer execution times and potentially higher resource exhaustion during concurrent operations, which directly contradicts the goal of improving performance in high-concurrency scenarios.

Use the REFRESH TABLE command before the query.

Why it fails: REFRESH TABLE is used to invalidate cached metadata for a specific table in the Hive metastore, but it does not clear the SQL warehouse result cache for query outputs. It is intended for traditional Spark SQL environments rather than the managed result caching behavior in Databricks SQL.

Set the warehouse to 'ReadOnly' mode.

Why it fails: Setting a warehouse to ReadOnly mode restricts the ability to perform write operations, but it does not change how the result cache functions. It does not force the query engine to ignore previously stored results or ensure that the most recent data is fetched from the underlying storage.

Restart the SQL warehouse instance.

Why it fails: Restarting the warehouse is an extreme and inefficient method for clearing the result cache. It incurs significant downtime and compute provisioning overhead. There are far more lightweight ways to manage cache, and this approach is not recommended for production workflows or standard data analysis tasks in Databricks SQL.

ALTER TABLE table_name SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true')

Why it fails: Change Data Feed tracks row-level changes for downstream synchronization. While useful for auditing and CDC, it does not fundamentally change how the Delta engine handles concurrent read/write locks, nor does it resolve read degradation caused by heavy write contention during standard SQL select operations.

REFRESH TABLE table_name

Why it fails: The REFRESH TABLE command invalidates the local metadata cache for the specified table. It forces the engine to re-fetch metadata from the underlying storage layer. This does not address concurrent write-read contention, as it only updates the metadata cache and does not manage transaction log locks.

ANALYZE TABLE table_name COMPUTE STATISTICS

Why it fails: ANALYZE TABLE collects statistics for the cost-based optimizer to generate efficient query plans. While essential for query performance optimization, it does not affect concurrency or locking mechanisms. It helps the engine choose the best join strategy but does not resolve contention between writers and readers.

Run VACUUM table_name RETAIN 0 HOURS

Why it fails: Running VACUUM with a zero-hour retention period would permanently delete all files not referenced in the current state of the transaction log. If the table is already missing files referenced by the log, this command could lead to permanent data loss or further corruption.

Execute FSCK REPAIR TABLE table_name

Why it fails: The FSCK REPAIR command is specifically designed to scan the Delta table storage, identify missing files that are still referenced in the transaction log, and remove those references. This reconciles the log with the actual files present, resolving the metadata error and restoring query capability.

Drop and recreate the table using a CTAS statement

Why it fails: While this might solve the metadata error, it is destructive. It results in the loss of all history, metadata, and potentially the data itself if managed tables are dropped. Analysts should always attempt to repair the metadata or recover the table state before resorting to destruction.

The SQL warehouse is currently paused and needs manual resumption.

Why it fails: A paused SQL warehouse automatically resumes when a query is submitted, returning execution results once ready rather than throwing a schema-not-found error. Schema errors indicate naming or metadata path problems, not compute availability.

The analyst's user account has exceeded its daily query execution cost quota.

Why it fails: Exceeding cost quotas or budgets results in access denial or throttling errors managed by workspace administrators, not missing schema metadata errors. The error message explicitly points to a missing schema path.

The underlying table data files have been physically deleted from cloud object storage.

Why it fails: Physical deletion of storage files typically leads to file-not-found or corrupted table state exceptions during read operations rather than a schema-not-found error at the metastore level. Metadata resolution happens before file scanning.

Use the MERGE statement to update a target table with the latest records.

Why it fails: MERGE is used for upserting data into a target table, not for querying the latest record per key. While it can update a table, it does not directly return query results and is not a query technique. It would require writing to a table, which is unnecessary for a read-only analysis.

Use a self-join on the primary key and timestamp to find the maximum timestamp.

Why it fails: A self-join can work but is generally less efficient than window functions, especially on large tables. It requires joining the table to itself, which can cause shuffles and increased resource usage. While valid, it is not considered efficient compared to window functions in Databricks SQL.

Use the LATERAL VIEW explode function to flatten the data.

Why it fails: LATERAL VIEW explode is used to flatten arrays or maps into multiple rows, not to deduplicate or select latest records. It does not address the requirement of selecting the latest record per primary key. Applying it here would not yield the desired result.

VACUUM table_name

Why it fails: VACUUM removes old data files that are no longer referenced by the Delta table, which can reduce storage but does not refresh metadata for queries. It is a maintenance operation, not a way to see the latest data. Running it would not ensure the analyst sees recent updates.

CACHE TABLE table_name

Why it fails: CACHE TABLE loads the table into memory for faster access but does not refresh metadata. If the table is updated, the cached data may become stale. It is used for performance, not for ensuring the latest data. Using it would not guarantee seeing the most recent writes.

ANALYZE TABLE table_name COMPUTE STATISTICS

Why it fails: ANALYZE TABLE computes statistics used by the optimizer to improve query plans. It does not refresh data or metadata. It is unrelated to seeing the latest data; it only helps with query optimization. It would not address the need to see recent writes.

Use the OPTIMIZE command with ZORDER on the table to improve data skipping.

Why it fails: OPTIMIZE with ZORDER improves file layout and data skipping for faster queries, but it does not ensure the query sees the latest committed data. It also rewrites files, which can conflict with concurrent writes. It is a performance optimization, not a consistency mechanism.

Set the Spark configuration spark.sql.adaptive.enabled to true to enable adaptive query execution.

Why it fails: Adaptive query execution optimizes the query plan at runtime based on statistics, improving performance, but it does not guarantee snapshot isolation or the latest committed data. It is unrelated to concurrency control. Enabling it may help performance but not correctness in this scenario.

Use the VACUUM command before running the query to remove old files and improve read performance.

Why it fails: VACUUM removes old files that are no longer referenced by the Delta log, but it does not affect the consistency or isolation of concurrent reads. Running VACUUM before a query can be expensive and may interfere with time travel or concurrent readers. It is not necessary for seeing the latest data.

Increase the spark.sql.shuffle.partitions setting.

Why it fails: This setting controls the number of partitions used during shuffles, such as for joins or aggregations. It does not affect the amount of data scanned for a simple filter query. Increasing it might even add overhead. It is unrelated to reducing data scanned through predicate pushdown or partitioning.

Use SELECT * to retrieve all columns for simplicity.

Why it fails: Using SELECT * retrieves all columns, which increases the amount of data read and transferred. For reducing data scanned, the analyst should select only the necessary columns. Column pruning is an important optimization, and SELECT * works against it, making this action counterproductive.

Apply a Z-order clustering on the date column.

Why it fails: Z-order clustering can improve data skipping for multiple columns, but it is not a substitute for partitioning on a date column when the primary filter is date. While Z-order can help, partitioning is more effective for date-range filters. Additionally, Z-order is more complex to maintain and may not be necessary if partitioning is used.

Convert the query into a Databricks SQL Alert that triggers on a schedule.

Why it fails: Alerts evaluate a query on a schedule and notify users when a condition is met; they do not cache result sets for dashboard reuse. Using an Alert would still execute the full 2 TB scan each evaluation and would not accelerate interactive dashboard rendering. Alerts serve monitoring and notification use cases, not query-result persistence for visualizations.

Add a Z-ORDER BY clause on the join key of the Delta table.

Why it fails: Z-ORDER improves data-skipping for selective filters by co-locating related data in files, but it does not eliminate repeated full scans when the query reads most of the table. The analyst's problem is repeated execution of the same query, not poor file pruning. Z-ORDER also requires OPTIMIZE to be run, adding maintenance work without solving the caching need.

Create a Delta Live Tables pipeline that materializes the query as a streaming table.

Why it fails: Delta Live Tables pipelines are designed for declarative ETL with data-quality expectations, not for ad-hoc analyst dashboards. Introducing a pipeline adds orchestration and cost overhead that the scenario does not require. The analyst simply needs cached query results, which Result Cache provides without building a separate pipeline. DLT would not automatically cache dashboard interactions.

Use SELECT * exclusively in all production dashboards.

Why it fails: The SELECT * pattern is an anti-pattern because it forces the engine to read every column from the underlying storage, increasing I/O overhead. Selecting only necessary columns allows the query engine to utilize column pruning, which reduces data volume and improves memory efficiency during the execution phase.

Increase the warehouse size to 4XL regardless of query complexity.

Why it fails: Increasing warehouse size does not always solve concurrency issues and often leads to wasted spend. Larger warehouses provide more resources for individual queries but do not necessarily handle more concurrent users efficiently. Proper warehouse sizing should be based on workload requirements rather than blindly scaling to the maximum size.

Always use ORDER BY in every subquery.

Why it fails: Using ORDER BY in subqueries adds unnecessary sorting overhead, which consumes CPU and memory. Sorting should only be applied at the final stage of a query if the presentation layer requires it. Unnecessary sorting operations inside subqueries significantly degrade performance in high-concurrency environments by increasing execution time per query.

The table schema is corrupted and requires a REPAIR TABLE command.

Why it fails: REPAIR TABLE is used in legacy Hive Metastore environments to sync external partition directories. It is not relevant to the modern three-level namespace resolution in Unity Catalog. This command does not address namespace lookup errors and will not resolve issues where a table cannot be located by the engine.

The SQL Warehouse is stopped and needs to be restarted.

Why it fails: If a SQL Warehouse is stopped, the error message would explicitly state that the warehouse is unavailable or starting. A TABLE_OR_VIEW_NOT_FOUND error specifically refers to the inability to locate the object metadata, which confirms the warehouse itself is functioning and capable of returning errors to the user.

The table has too many partitions, causing a timeout.

Why it fails: A timeout error due to excessive partitions would result in a different error message, typically related to query execution latency or resource exhaustion. The TABLE_OR_VIEW_NOT_FOUND error is strictly a metadata resolution failure, meaning the engine has successfully parsed the query but cannot find the target data object.

Sort-merge join

Why it fails: Sort-merge joins are typically chosen by the optimizer for large-to-large table joins. This operation requires sorting and shuffling both tables, which is computationally expensive and slow for this scenario. It should be avoided when one side is small enough to be broadcast efficiently across the distributed worker cluster.

Cartesian join

Why it fails: Cartesian joins produce a cross-product of all rows, which is mathematically intensive and almost always unintentional. They lead to massive data explosion and will likely crash the query or cause severe performance degradation due to the sheer volume of output rows generated by the cross-join calculation.

Full outer join

Why it fails: A full outer join returns all records when there is a match in either the left or right table. It does not dictate the physical execution strategy for the join itself, such as broadcasting or shuffling. Relying on this join type does not inherently optimize performance for small-to-large table joins.

CREATE VIEW view_name AS SELECT * FROM table

Why it fails: A standard CREATE VIEW command creates a persistent object stored in the catalog. This view remains available to all users and sessions until explicitly dropped. It does not provide the session-level isolation needed for temporary, short-lived analytical transformations or intermediate query steps that should not persist in the database.

CREATE LOCAL VIEW view_name AS SELECT * FROM table

Why it fails: There is no 'LOCAL VIEW' command in Databricks SQL. This syntax will result in an error. The correct syntax for a session-scoped object is CREATE TEMPORARY VIEW, which ensures the metadata is cleaned up properly at the end of the user's connection, preventing unnecessary clutter in the catalog.

CREATE SESSION TABLE view_name AS SELECT * FROM table

Why it fails: This is not valid Databricks SQL syntax. To create a temporary table-like object, you would use CREATE TEMPORARY VIEW. Using incorrect syntax leads to query failure. Always verify the command structure against the official Databricks SQL documentation to ensure the engine correctly interprets your intent for temporary data structures.

Use the GRANT ALL PRIVILEGES command on all tables.

Why it fails: Granting all privileges is a security anti-pattern that violates the principle of least privilege. It gives users full control, including the ability to drop tables or grant permissions to others, which creates significant security risks rather than protecting sensitive data through controlled, restricted access for specific user roles.

Implement Row-Level Security using a filter clause in the view definition.

Why it fails: While row-level security is a valid strategy, the prompt specifically asks about protecting sensitive PII columns, not rows. Row-level security limits which records a user sees, but it does not redact specific column values within the rows that the user is permitted to access, failing the requirement for column-level protection.

Run the query as a superuser to bypass masks.

Why it fails: Bypassing security controls is dangerous and generally not possible for standard analysts. Databricks SQL security is designed to be enforced consistently regardless of the user's session role. Relying on superuser access for reporting is an improper security practice that undermines the governance model designed to protect organizational data.

The Catalog Explorer

Why it fails: Catalog Explorer is designed for browsing tables, schemas, and volumes within Unity Catalog. It provides metadata information, lineage, and sample data but does not contain execution-level details, performance metrics, or query plans for historical SQL statements. It is not the appropriate interface for analyzing query performance or reviewing execution history.

The SQL Editor

Why it fails: The SQL Editor is where queries are written and executed. While it shows the current output and runtime for a session, it does not provide the detailed Query Profile visualization necessary to analyze bottlenecks. For full performance analysis, the dedicated Query History view must be accessed for the specific query.

The SQL Warehouse settings page

Why it fails: The SQL Warehouse settings page is used to configure warehouse-level parameters like scaling policies, auto-stop intervals, and instance types. It is an administrative page for managing infrastructure, not an analytical tool for inspecting individual query performance or viewing execution plans for specific SQL statements that have already been executed.

WHERE

Why it fails: The WHERE clause filters individual rows before any grouping or aggregation occurs. It cannot be used to filter based on aggregated results like SUM or COUNT. Attempting to use WHERE for post-aggregation filtering will cause a syntax error because the aggregated metrics do not exist at that execution stage.

GROUP BY

Why it fails: The GROUP BY clause is used to collect data across multiple records and group the results by one or more columns. It does not perform filtering itself; rather, it organizes the data for aggregation. Filtering the grouped results requires the addition of a HAVING clause to apply logical conditions.

FILTER

Why it fails: While there is a FILTER clause used within aggregate functions (e.g., SUM(sales) FILTER (WHERE region = 'US')), it is not a standalone clause for filtering aggregate groups. Using it incorrectly as a top-level clause will result in a syntax error, as it is not designed to function like the WHERE or HAVING clauses.