Databricks-Spark-Assoc Using Spark SQL Practice Test 4 — 15 Questions

Question 1

Which of the following describes the behavior of a Delta table when a 'DELETE' operation is performed?

Accepted Answer

It identifies affected files, rewrites them excluding the target rows, and updates the transaction log.. Delta Lake implements deletes by rewriting only the files containing the data to be removed, while maintaining a record of the change in the transaction log. This is much more efficient than traditional systems that might perform full table scans or require complex locking. Understanding this 'copy-on-write' or 'merge-on-read' approach is vital for Databricks developers to predict performance and costs when executing large-scale DML operations.

Answer

It deletes the entire table and recreates it without the target rows.

Answer

It physically removes the data from all files in a synchronous manner.

Answer

It only marks the rows as deleted without affecting the underlying files.

Question 2

Which TWO of the following are benefits of using Delta Lake over standard Parquet files in Spark SQL?

Accepted Answer

Support for ACID transactions.. Delta Lake extends the capabilities of standard Parquet by adding a transaction log and metadata layer. This enables ACID transactions, Time Travel, and schema enforcement, which are critical for robust data engineering. For a Databricks certified developer, knowing why Delta is the preferred format for the Lakehouse architecture is essential for building scalable, reliable, and maintainable data systems that surpass the limitations of raw file-based storage.

Answer

Faster raw I/O performance for single-column reads.

Answer

Automatic conversion of CSV files to optimized Parquet.

Answer

Compatibility with legacy Hive Metastore versions without modification.

Question 3

When working with Delta Lake tables in Databricks, which command should you use to optimize the physical layout of files to improve query performance?

Accepted Answer

OPTIMIZE table_name. Delta Lake provides the `OPTIMIZE` command to compact small files into larger ones, which is vital for maintaining performance as data grows. Frequent small writes can lead to file proliferation, degrading read speeds. Running `OPTIMIZE` regularly helps maintain efficient file sizes, enabling faster query execution by reducing metadata overhead and maximizing the benefits of data skipping through better file-level statistics.

Answer

COMPACT TABLE table_name

Answer

REORGANIZE TABLE table_name

Answer

VACUUM table_name

Question 4

Which TWO of the following are true concerning Spark SQL's handling of NULL values?

Accepted Answer

The COUNT(column_name) function excludes NULL values from the count.. Correctly managing NULLs is crucial for data accuracy. Spark SQL treats NULLs as unknown values. Aggregate functions like SUM and COUNT(col) skip NULLs, while count(*) includes them. Understanding these nuances is essential for developers to write robust SQL queries that correctly interpret missing data, avoiding common pitfalls in reporting and data quality validation processes within Databricks production environments.

Answer

NULL values are treated as zero in all mathematical operations.

Answer

The 'IS NULL' condition is not supported in Spark SQL; use '== NULL' instead.

Answer

Joining on columns that contain NULL values will always result in an inner join match.

Question 5

What is the primary purpose of the 'Cache' command in Spark SQL?

Accepted Answer

To keep the data in memory to avoid redundant re-computation.. Caching allows developers to persist frequently accessed data in memory across multiple actions. By avoiding repeated reads from storage and re-computation of transformations, caching significantly speeds up iterative workloads, such as machine learning training or complex dashboard refreshes. However, it must be used judiciously, as memory is a finite resource, and unnecessary caching can lead to OOM errors and overall performance degradation.

Answer

To permanently store the data on the disk for future use.

Answer

To force the garbage collector to free up memory immediately.

Answer

To automatically partition the data across the cluster for faster access.

Question 6

Which of the following is the most efficient way to convert a Spark DataFrame into a format suitable for low-latency SQL queries in Databricks?

Accepted Answer

Write the data into a Delta table.. Delta Lake is the gold standard for Spark workloads because it brings ACID transactions, schema enforcement, and high performance to data lakes. Converting DataFrames to Delta tables enables indexing, data skipping, and file-level statistics, which are essential for low-latency SQL access. This approach replaces older formats like Parquet, offering superior capabilities and seamless integration with the Databricks engine, making it the standard practice for modern data lake architecture.

Answer

Write the data as a collection of CSV files in the cloud storage.

Answer

Keep the data as a temporary view in the driver's memory.

Answer

Write the data as JSON files to exploit document-based query engines.

Question 7

Which of the following describes the behavior of a 'Left Outer Join' in Spark SQL?

Accepted Answer

It returns all rows from the left table and matched rows from the right table.. A Left Outer Join is fundamental in SQL for merging datasets where you want to keep all records from the left table, regardless of whether a match exists in the right table. Understanding this ensures that data integration processes correctly handle non-matching records, preventing data loss during joins. It is a critical concept for analysts and developers building reports that require comprehensive views of primary entities with optional supplemental info.

Answer

It returns only rows that have matches in both the left and right tables.

Answer

It returns all rows from the right table and matched rows from the left table.

Answer

It excludes all rows from both tables that do not have a matching key.

Question 8

When executing a Spark SQL query, what does the Catalyst optimizer perform during the 'Analysis' phase?

Accepted Answer

It checks the existence of tables and columns in the catalog.. The Analysis phase is the first step in the query optimization process where Spark resolves identifiers and validates the schema. It checks if tables and columns exist in the catalog and resolves data types. Without this step, Spark would not be able to build a logical plan for the query, as it would not know which data to fetch or how to process the specified columns and tables correctly.

Answer

It translates the logical plan into a series of physical execution steps.

Answer

It pushes down predicates to the data source to minimize I/O.

Answer

It chooses the most efficient join strategy based on table size.

Question 9

Which THREE of the following are valid ways to monitor or debug Spark SQL query performance in Databricks?

Accepted Answer

Use the 'EXPLAIN' statement to inspect the physical plan of the query.. Monitoring tools like the Spark UI, Query Profile, and Explain plans are critical for developers to understand how their SQL code executes. They provide visibility into shuffle sizes, stage durations, and physical plan generation. Effectively using these tools is the difference between writing performant code and creating bottlenecks. They allow developers to identify skewed data, suboptimal joins, and unnecessary data scanning, which are common issues in large-scale Spark SQL workloads.

Answer

Manually restart the cluster every time a query takes more than 10 seconds.

Answer

Increase the driver memory to the maximum available for all jobs.

Question 10

When performing a 'Z-ORDER' operation on a Delta table, how does it improve query performance?

Accepted Answer

It reorders data to maximize the effectiveness of data skipping.. Z-Ordering is a technique that maps multi-dimensional data to one dimension while preserving locality. By co-locating related information in the same files, Z-Ordering enables the Delta Lake reader to skip more data when filtering. This is highly effective for high-cardinality columns, as it dramatically increases the effectiveness of data skipping, leading to significantly faster query response times in large, partitioned Delta tables.

Answer

It compresses data more tightly than standard Parquet compression.

Answer

It automatically creates a secondary index for every column in the table.

Answer

It removes all null values from the table to reduce storage size.

Question 11

You are optimizing a Spark SQL query that performs a large join between a 10GB table and a 5MB lookup table. To ensure performance efficiency, which command should you use to hint to the optimizer?

Accepted Answer

SELECT /*+ BROADCAST(t2) */ * FROM t1 JOIN t2. Spark SQL uses cost-based optimization, but small table broadcasts significantly reduce network shuffle overhead. By using the BROADCAST hint, you force the engine to send the small table to every executor, avoiding a full shuffle of the 10GB dataset. This is a critical optimization technique in Databricks environments where minimizing cross-node data movement is essential for reducing total job latency and improving cluster resource utilization.

Answer

SELECT /*+ MERGE(t1, t2) */ * FROM t1 JOIN t2

Answer

SELECT /*+ SHUFFLE(t1, t2) */ * FROM t1 JOIN t2

Answer

SELECT /*+ SKEW(t1) */ * FROM t1 JOIN t2

Question 12

Which of the following Spark SQL configuration settings should be adjusted to prevent the 'Driver OOM' error when collecting massive amounts of query results to the driver node?

Accepted Answer

spark.driver.maxResultSize. Collecting data to the driver node is a dangerous operation in distributed computing because the driver has limited heap memory. Spark provides configuration limits to prevent users from accidentally crashing the driver by pulling too much data from the executors. Adjusting these settings—or better, avoiding collecting altogether—is a key skill for Databricks developers to maintain cluster stability in production environments.

Answer

spark.executor.memory

Answer

spark.sql.shuffle.partitions

Answer

spark.memory.fraction

Question 13

In Spark SQL, what is the primary difference between a temporary view and a global temporary view?

Accepted Answer

Temporary views are accessible only within the current session, while global temporary views are accessible across all sessions.. Understanding scope is critical for managing data access in multi-user Databricks environments. Temporary views are session-specific, ensuring data isolation between users. Global temporary views are visible to all sessions on the cluster and are stored in the 'global_temp' database. This distinction is vital for maintaining security and avoiding namespace collisions when multiple data engineers are working simultaneously on the same shared cluster infrastructure.

Answer

Temporary views persist after the cluster is terminated, while global temporary views do not.

Answer

Temporary views are written to the Hive Metastore, while global temporary views are only in memory.

Answer

Global temporary views are faster to query because they are cached by default.

Question 14

Which clause is used in a Spark SQL query to limit the number of rows returned by a query, and in which logical order is it executed?

Accepted Answer

LIMIT, executed after ORDER BY.. The LIMIT clause is a common SQL operation used to restrict result sets. In Spark SQL, it is essential to understand that it is applied after sorting if an ORDER BY is present, or simply on the result stream if not. Using LIMIT is a critical performance practice to avoid overwhelming the driver when previewing large datasets in a notebook environment.

Answer

TAKE, executed before ORDER BY.

Answer

FETCH, executed before WHERE.

Answer

TOP, executed after GROUP BY.

Question 15

What is the purpose of the 'ANALYZE TABLE' command in Databricks?

Accepted Answer

To generate statistics used by the optimizer for query planning.. The ANALYZE TABLE command collects table statistics, such as row counts and column distributions. This information is crucial for the Spark Catalyst optimizer to make informed decisions about join strategies, such as when to broadcast a table or how to order joins. Without accurate statistics, the optimizer might choose inefficient execution plans, leading to degraded performance in complex multi-join queries.

Answer

To remove unused files and clean up storage.

Answer

To update the table schema after adding a column.

Answer

To verify the integrity of the Delta log.