Databricks Spark SQL Operations Quiz

1. What is the difference between persist() and cache() in Spark?

Cache() is a shorthand for persist(MEMORY_ONLY)

Persist() is faster than cache()

Cache() stores data permanently

They are identical and interchangeable

2. Which method is used to write a DataFrame to a Delta table in Databricks?

Df.write.format('delta').mode('overwrite').save(path)

Df.export('delta', path)

Df.to_delta(path)

Df.write_delta(path)

3. What does the unpivot operation accomplish in Spark SQL?

Converts columns into rows (the inverse of pivot)

Rotates rows into columns

Removes duplicate columns

Combines multiple tables vertically

4. In Databricks, which file format is optimized for columnar storage and analytics?

Parquet

CSV

JSON

XML

5. What is the purpose of the COALESCE function in Spark SQL?

Returns the first non-null value from a list of columns

Combines multiple partitions into fewer partitions

Merges two tables into one

Removes null values from a DataFrame

6. Which operation in Spark requires a shuffle of data across the network?

GroupBy() followed by aggregation

Map() on each row

Select() to choose columns

Filter() to remove rows

7. What is the correct syntax for a subquery in a FROM clause in Spark SQL?

SELECT * FROM (SELECT col FROM table) AS alias

SELECT * FROM SELECT col FROM table

SELECT * FROM [SELECT col FROM table]

SELECT * FROM {SELECT col FROM table}

8. In a Spark SQL window function, what does the ORDER BY clause control?

The order in which rows are processed within each partition

The final sort order of the result set

The number of rows in each window

The direction of data shuffling

9. What does the EXPLAIN command in Spark SQL display?

The logical and physical execution plan of a query

A list of all tables in the database

The storage location of data files

The query execution time statistics

10. Which SQL aggregate function returns the number of non-null values?

COUNT()

SUM()

AVG()

TOTAL()

11. What is the primary advantage of using DataFrame APIs over raw SQL in Spark?

DataFrames automatically optimize query execution through Catalyst optimizer

SQL is not supported in Spark

DataFrames require less memory than SQL queries

SQL cannot handle distributed data

12. In Spark, which method removes duplicate rows from a DataFrame?

Distinct()

Unique()

Deduplicate()

Remove_duplicates()

13. What is the primary purpose of using bucketing in Spark SQL?

To pre-shuffle and organize data for efficient joins and aggregations

To compress data during storage

To eliminate duplicate rows automatically

To encrypt sensitive columns

14. Which of the following best describes a wide transformation in Spark?

Requires data shuffle across partitions (e.g., groupBy, join)

Operates independently on each partition

Combines multiple DataFrames without shuffling

Reduces the number of columns in a DataFrame

15. What does the BROADCAST hint do in a Spark SQL join?

Sends a smaller table to all worker nodes to avoid shuffling

Replicates data across all partitions

Converts the join to a cross product

Filters rows before the join operation

16. In Databricks, what is the benefit of partitioning a table by a column?

Improves query performance by reducing data scanned

Increases storage capacity automatically

Prevents data corruption

Eliminates the need for indexing

17. Which join type returns all rows from both tables, filling missing values with null?

FULL OUTER JOIN

INNER JOIN

LEFT JOIN

CROSS JOIN

18. What is the purpose of the Catalyst optimizer in Spark?

To convert SQL queries into optimized physical execution plans

To compress data before writing to storage

To distribute data across cluster nodes

To monitor query performance metrics

19. In Spark SQL, what does the PARTITION BY clause accomplish in a window function?

Divides rows into groups for computation within each group

Splits data across multiple files on disk

Creates separate tables for each partition

Filters rows before aggregation

20. Which method is used to cache a DataFrame in Spark for repeated access?

Df.persist()

Df.save()

Df.store()

Df.buffer()

Databricks Data Engineer Spark SQL Operations Quiz