Databricks-Spark-Assoc Using Spark SQL — Timed 40 Questions

Question 1

You have a Delta table named 'sales_data'. Which TWO SQL commands allow you to effectively manage data storage and query performance for this table?

Accepted Answer

OPTIMIZE sales_data. Managing Delta tables requires proactive maintenance. Z-Ordering is a technique to colocate related information in the same set of files, significantly improving data skipping during query execution. The OPTIMIZE command is essential for consolidating small files into larger ones. Together, these commands form the cornerstone of Delta Lake table maintenance, ensuring that the storage layer remains performant as datasets grow over time, which is essential for professional-grade Spark SQL optimization.

Answer

VACUUM sales_data RETAIN 0 HOURS

Answer

ALTER TABLE sales_data REFRESH

Answer

TRUNCATE TABLE sales_data

Question 2

Which THREE of the following are valid ways to create a Delta table in Spark SQL?

Accepted Answer

CREATE TABLE table_name USING DELTA AS SELECT * FROM source_table. Delta Lake supports multiple ways to initialize tables, providing flexibility for different architectural patterns. Whether migrating existing data, streaming new data, or defining a new schema from scratch, these methods ensure compatibility with ACID transactions. Mastering these creation patterns is essential for Spark developers to build robust ingestion pipelines and maintain high-quality data assets in the Lakehouse, ensuring consistent performance and data integrity across various business use cases.

Answer

CREATE TABLE table_name AS SELECT * FROM path.`/data/path`

Answer

CREATE TABLE table_name FROM '/path/to/files'

Question 3

You are querying a large Delta table and notice that your query is scanning significantly more data than expected. Which technique allows you to restrict the data scanned by the query based on column values?

Accepted Answer

Use the ZORDER BY clause during the table write process.. Data skipping is a primary performance feature of Delta Lake. When you filter by columns that are part of the table's physical layout—either through Z-Ordering or simple partitioning—Spark can skip entire files that do not contain relevant data. This reduces I/O dramatically. Understanding how to leverage predicates in WHERE clauses to facilitate data skipping is vital for writing efficient Spark SQL queries against large-scale, high-volume datasets.

Answer

Implement column-level partitioning on high-cardinality columns.

Answer

Increase the 'spark.sql.broadcastTimeout' configuration.

Answer

Rewrite the query to use a LATERAL VIEW explode.

Question 4

You need to update existing records in a Delta table based on a match from another DataFrame. Which TWO commands or techniques achieve this efficiently?

Accepted Answer

MERGE INTO target_table t USING source_df s ON t.id = s.id WHEN MATCHED THEN UPDATE SET *. The MERGE INTO statement is the standard for performing UPSERT (update/insert) operations in Delta Lake. It is highly optimized, ensuring atomic operations that handle inserts, updates, and deletes in a single pass. Understanding MERGE and the associated logic is a cornerstone of building reliable CDC (Change Data Capture) pipelines. These techniques ensure data consistency and accuracy, which are mandatory requirements in production-grade Spark SQL data engineering workflows.

Answer

UPDATE target_table SET value = x WHERE id IN (SELECT id FROM source_df)

Answer

DELETE FROM target_table WHERE id IN (SELECT id FROM source_df); INSERT INTO target_table SELECT * FROM source_df

Answer

REPLACE INTO target_table SELECT * FROM source_df

Question 5

Which configuration setting is most important to tune when you observe that your Spark SQL job is failing due to 'Out of Memory' errors during a massive join operation?

Accepted Answer

spark.sql.shuffle.partitions. Memory management is central to Spark performance. When joins exceed available executor memory, the shuffle process fails. Adjusting the number of shuffle partitions ensures that each task handles a manageable amount of data. Properly tuning this parameter is essential for preventing job failures, as it balances the degree of parallelism with the memory footprint per task, which is a core responsibility for Spark developers dealing with large-scale data transformations.

Answer

spark.driver.memory

Answer

spark.sql.sources.fileCacheSize

Answer

spark.sql.autoBroadcastJoinThreshold

Question 6

Which SQL function is used to convert a string representation of a date into a DATE type in Spark SQL?

Accepted Answer

CAST(date_string AS DATE). The TO_DATE function is the industry-standard tool for data type conversion in SQL. Dealing with raw data often requires casting strings to proper date objects to enable temporal filtering and grouping. As a Spark developer, mastery of type casting functions is vital to ensure that your data schemas are clean and your analytical queries can leverage time-series logic effectively without runtime conversion errors.

Answer

PARSE_DATE(date_string)

Answer

CONVERT_DATE(date_string)

Answer

STRING_TO_DATE(date_string)

Question 7

You are optimizing a Spark SQL query that performs an equi-join between a 10GB table and a 5MB lookup table. To maximize performance and avoid a shuffle, which action should you take?

Accepted Answer

Use the broadcast hint for the 5MB table in the JOIN clause.. Broadcasting the small table allows Spark to send the entire 5MB dataset to every executor, enabling a map-side join. This strategy eliminates the need to shuffle the 10GB table across the cluster, which is the primary bottleneck in large joins. Efficient join strategies like broadcast joins are essential for Databricks developers to minimize network I/O and reduce overall job execution time in distributed environments.

Answer

Apply a repartition operation to both tables to balance the data distribution.

Answer

Increase the number of partitions to 1000 to improve parallelism during the join.

Answer

Convert both tables to temporary views and join them using standard SQL.

Question 8

Which TWO of the following statements regarding the Spark SQL 'DataFrame.write' operation are accurate?

Accepted Answer

The 'overwrite' mode replaces the entire contents of the destination table or directory.. Understanding write modes and partitioning is critical for managing data lifecycles in Databricks. 'Overwrite' mode replaces existing data, while 'Append' adds to it. Proper partitioning optimizes downstream reads, but excessive partitioning leads to the small file problem. Developers must balance these operations to ensure performant data pipelines that avoid storage overhead and maintain data integrity during write processes to cloud object storage.

Answer

Partitioning by a column with high cardinality is recommended to improve read performance.

Answer

The 'append' mode will fail if the destination directory does not exist.

Answer

Using 'overwrite' mode automatically updates all dependent views.

Question 9

Which of the following functions is best suited for extracting the year from a timestamp column in Spark SQL?

Accepted Answer

YEAR(timestamp_column). Using built-in date-time functions is a standard practice in Spark SQL for data transformation. These functions are highly optimized and pushed down to the storage layer when possible, providing better performance than UDFs. Knowing the correct function name is essential for developers to write concise and efficient code for time-series analysis and reporting within Databricks environments.

Answer

GET_YEAR(timestamp_column)

Answer

EXTRACT_YEAR(timestamp_column)

Answer

DATE_PART(timestamp_column, 'YEAR')

Question 10

Which THREE of the following are valid ways to improve the performance of a Spark SQL query that scans a large partitioned table?

Accepted Answer

Use partition pruning by filtering on the partition column in the WHERE clause.. Performance tuning in Spark SQL often relies on minimizing data scanned from storage. By using partition pruning, columnar formats like Parquet, and predicate pushdown, developers ensure the cluster only processes necessary data. These techniques reduce CPU, I/O, and memory consumption, which are the primary costs of running Databricks workloads, and directly impact job completion times and cost efficiency.

Answer

Increase the number of executors to 1000 for every small query.

Answer

Convert the table to a CSV format to make it easier for Spark to parse.

Question 11

Which function is the most appropriate way to convert a string column containing date information into a proper DateType column in Spark SQL?

Accepted Answer

TO_DATE(col, 'yyyy-MM-dd'). The TO_DATE function is the standard SQL-compliant way to parse date strings into DateType objects. Understanding type casting is fundamental in Spark SQL because improper types lead to inefficient storage and potential runtime errors during aggregation or temporal filtering. Using the correct casting function ensures that the Catalyst optimizer can apply predicate pushdown efficiently, as date-based filters require semantic understanding of temporal data formats to operate correctly.

Answer

CAST(col AS DATE)

Answer

FROM_UNIXTIME(col)

Answer

DATE_FORMAT(col, 'yyyy-MM-dd')

Question 12

You are working with a Delta table. Which TWO of the following commands are valid ways to improve query performance for frequently filtered columns in Spark SQL?

Accepted Answer

OPTIMIZE table_name ZORDER BY (column_name). Optimizing data layout is a core responsibility of a Databricks engineer. Z-Ordering and partitioning are the two primary mechanisms used to skip data during scan operations. By physically organizing data on disk according to frequently queried columns, Spark can prune large portions of the dataset that do not meet filter criteria, drastically reducing I/O and accelerating query response times across large-scale data lakes.

Answer

REORG TABLE table_name APPLY (PURGE)

Answer

VACUUM table_name RETAIN 0 HOURS

Answer

COMPACT table_name USING BIN_PACK

Question 13

When creating a Delta table using the 'CREATE TABLE' statement in Spark SQL, which THREE of the following features are supported natively?

Accepted Answer

PARTITIONED BY clause. Delta Lake provides robust data management capabilities that extend standard Spark SQL. Understanding these native features is essential for building reliable, production-grade pipelines. Features like partitioning, comment support, and table properties are standard, while advanced features like generated columns improve data quality. Knowing what is supported ensures developers can build schema-enforced, high-performance tables without needing complex workarounds or third-party libraries in their SQL code.

Answer

AUTO_INCREMENT for primary keys

Answer

FOREIGN KEY constraints

Question 14

You are performing a join between a large table and a table with high cardinality. Which technique should you use to avoid memory pressure on a single node during the join?

Accepted Answer

Add a salt column to the join keys.. High-cardinality join keys often lead to data skew, where a single task ends up processing a massive amount of data while others remain idle. This creates performance bottlenecks and potential OOM errors. By using techniques like salting or ensuring optimal partitioning, developers can distribute the workload evenly across the cluster, which is a vital skill for scaling Spark applications in production.

Answer

Broadcast the large table to all executors.

Answer

Use the COLLECT_LIST function on the join keys.

Answer

Set the spark.sql.autoBroadcastJoinThreshold to -1.

Question 15

Which TWO of the following are benefits of using the Delta Lake format over Parquet for Spark SQL operations?

Accepted Answer

ACID transaction support. Delta Lake adds a transactional layer on top of Parquet, providing ACID guarantees and time-travel capabilities that standard Parquet files lack. These features are essential for modern data lakehouses. By managing transaction logs, Delta Lake ensures that concurrent read/write operations remain consistent, which is a major pain point when using raw Parquet files without a dedicated concurrency management system.

Answer

Automatically converts all files to CSV format.

Answer

No need for a Hive Metastore.

Answer

Instant query execution without any latency.

Question 16

You need to query a Delta table named `sales_data` using Spark SQL and flatten an array column named `items` into multiple rows while preserving the rest of the record structure. Which function should you use?

Accepted Answer

USE LATERAL VIEW EXPLODE(items) AS item. The LATERAL VIEW EXPLODE construct is designed specifically for flattening array columns in Spark SQL. It generates a virtual table containing a row for each element in the array, joining it back to the original source row. This guarantees that missing or single-valued elements are handled correctly while maintaining structural context, making it an essential pattern in modern Databricks data lakehouse architectures.

Answer

USE EXPLODE() directly in the SELECT clause without a LATERAL VIEW

Answer

USE FLATTEN() function

Answer

USE COLLECT_LIST(items)

Question 17

Which TWO of the following statements regarding the behavior of the 'MERGE INTO' command in Databricks SQL are correct?

Accepted Answer

The join condition in the MERGE statement must be deterministic.. The MERGE INTO command is a powerful feature for maintaining Delta Lake tables by combining insert, update, and delete operations into a single atomic transaction. Understanding the strict requirements, such as the need for a deterministic join condition and the restriction against updating columns used in the join, is vital for ensuring data integrity and preventing runtime errors during complex ETL processes in a Lakehouse architecture.

Answer

You can modify columns that are included in the join condition without restriction.

Answer

MERGE INTO supports joining the target table with an external RDBMS table directly.

Answer

The MERGE operation does not support the deletion of records.

Question 18

You are querying a large partitioned table in Databricks using Spark SQL and need to permanently register the filtered output as a new table in the Hive metastore while avoiding a full table scan. Which statement correctly achieves this persistence?

Accepted Answer

CREATE TABLE new_table AS SELECT * FROM source_table WHERE date = '2023-01-01'. Using CREATE TABLE AS SELECT allows Spark SQL to persist transformation results directly into the metastore as a managed table. This approach leverages predicate pushdown and partition pruning if the source table query filters correctly on partition columns, preventing full table scans and storing the optimized data effectively for subsequent analytical queries.

Answer

INSERT OVERWRITE TABLE new_table SELECT * FROM source_table WHERE date = '2023-01-01'

Answer

CREATE GLOBAL TEMP VIEW new_table AS SELECT * FROM source_table WHERE date = '2023-01-01'

Answer

CACHE TABLE new_table AS SELECT * FROM source_table WHERE date = '2023-01-01'

Question 19

You need to persist a temporary view in Spark SQL so that it is available across different sessions within the same Spark application. Which command should you execute?

Accepted Answer

CREATE GLOBAL TEMPORARY VIEW view_name AS SELECT * FROM table. In Spark SQL, a standard 'CREATE VIEW' creates a temporary view scoped only to the current session. By using 'CREATE GLOBAL TEMPORARY VIEW', the view is stored in a system-preserved temporary database 'global_temp'. This allows all sessions within the same Spark application to access the view, which is essential for collaborative data processing tasks where multiple notebooks or jobs need to reference the same intermediate dataset without persisting it to permanent storage.

Answer

CREATE TEMP VIEW view_name AS SELECT * FROM table

Answer

CREATE VIEW view_name AS SELECT * FROM table

Answer

CREATE PERSISTENT VIEW view_name AS SELECT * FROM table

Question 20

You have a large Delta table and need to optimize query performance for frequently filtered columns. Which TWO actions should you take?

Accepted Answer

Run OPTIMIZE table_name ZORDER BY (column_name). Z-Ordering and partitioning are primary techniques for optimizing data skipping in Delta Lake. Z-Ordering maps related information in the same set of files, drastically reducing the amount of data read for range and equality queries. Partitioning physically organizes data into directories, allowing Spark to prune entire partitions before scanning, which significantly improves performance for queries that frequently filter on specific low-cardinality dimensions like date or region.

Answer

Increase the number of partitions to 10,000

Answer

Enable auto-compaction using Delta features

Answer

Rebuild the table using a smaller block size