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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

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 4

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 5

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

Question 6

Which of the following functions allows you to convert a string representing a date into a DATE type in Spark SQL?

Accepted Answer

to_date(string_col, format). The 'to_date()' function is the standard tool for parsing date strings into Spark's internal DateType. By allowing an optional format pattern, it provides the flexibility to handle various date string representations (e.g., 'yyyy-MM-dd' or 'MM/dd/yyyy'). This is critical for data ingestion pipelines where incoming source data is often stored as raw text and must be converted to typed formats for accurate temporal calculations and filtering.

Answer

cast(string_col AS DATE)

Answer

convert_date(string_col)

Answer

date_format(string_col)

Question 7

Which TWO of the following statements correctly describe the behavior of the 'UNION' and 'UNION ALL' operators in Spark SQL?

Accepted Answer

UNION performs a distinct operation, removing duplicate rows.. UNION and UNION ALL are fundamental set operations. UNION automatically performs a distinct operation on the combined result set to remove duplicates, which is computationally expensive. UNION ALL simply appends the rows without checking for duplicates, making it significantly faster for large datasets where the user knows there is no overlap or where duplicates are explicitly desired for downstream processing or auditing requirements.

Answer

UNION ALL removes duplicate rows from the result set.

Answer

Both operators require that the input datasets have different schemas.

Answer

UNION is the default operator and does not require a keyword.

Question 8

A data analyst is working with a large sales Delta table in Databricks and needs to partition the aggregated results by region and order year. Which TWO of the following approaches correctly create a partitioned table using Spark SQL? Choose 2 answers.

Accepted Answer

CREATE TABLE sales_partitioned USING DELTA PARTITIONED BY (region, order_year) AS SELECT * FROM raw_sales;. Creating partitioned tables in Spark SQL requires utilizing the PARTITIONED BY clause in a CREATE TABLE statement or leveraging DataFrame write options with partitionBy. Partitioning optimizes downstream queries by enabling partition pruning, drastically reducing data scanning costs in cloud storage environments like AWS S3 or Azure ADLS Gen2.

Answer

CREATE TABLE sales_partitioned (region STRING, order_year INT) USING DELTA PARTITIONED BY (region);

Answer

ALTER TABLE sales_partitioned ADD PARTITION (region='North', order_year=2023);

Answer

CREATE TABLE sales_partitioned STORED AS PARQUET PARTITIONED BY (region, order_year) AS SELECT * FROM raw_sales;

Question 9

A data engineer needs to register an existing Parquet directory located at 'dbfs:/data/sales/' as a managed Spark SQL table named 'sales_data'. Which SQL statement correctly accomplishes this operation?

Accepted Answer

CREATE TABLE sales_data AS SELECT * FROM parquet.`dbfs:/data/sales/`;. Registering an external data source as a managed table requires creating the table with the USING clause and pointing to the location. However, managed tables are typically created without a LOCATION clause since Spark manages their underlying storage path automatically. When an explicit LOCATION is provided with a managed table, Spark can throw an error or treat the directory in unexpected ways depending on the runtime, making CREATE TABLE ... USING parquet LOCATION the standard syntax for creating an external table rather than a managed table. Understanding the structural differences between managed and external tables is vital for robust data lake governance and preventing accidental data loss when dropping tables.

Answer

CREATE TABLE sales_data USING parquet LOCATION 'dbfs:/data/sales/';

Answer

CREATE MANAGED TABLE sales_data AS SELECT * FROM parquet.`dbfs:/data/sales/`;

Answer

CREATE TABLE sales_data USING parquet OPTIONS (path 'dbfs:/data/sales/');

Question 10

You are using Spark SQL in Databricks to analyze a Delta table named events that contains a column event_time of type TIMESTAMP. You need to retrieve the average number of events per hour for each day, but only for events that occurred in the last 7 days. You want to use built-in date functions to truncate and extract components. Which SQL expression correctly computes the daily hourly average count?

Accepted Answer

SELECT date_trunc('day', event_time) AS day, AVG(hourly_count) AS avg_hourly_count FROM (SELECT date_trunc('day', event_time) AS day, hour(event_time) AS hour, COUNT(*) AS hourly_count FROM events WHERE event_time >= current_date() - INTERVAL 7 DAYS GROUP BY date_trunc('day', event_time), hour(event_time)) GROUP BY day. The correct approach uses a subquery to calculate hourly counts per day, then averages those counts per day. This ensures the average is computed over the actual hourly buckets, not assuming uniform distribution. The other options either produce incorrect aggregations or invalid SQL syntax.

Answer

SELECT date_trunc('day', event_time) AS day, hour(event_time) AS hour, COUNT(*) AS total_events FROM events WHERE event_time >= current_date() - INTERVAL 7 DAYS GROUP BY date_trunc('day', event_time), hour(event_time) ORDER BY day, hour

Answer

SELECT date_trunc('day', event_time) AS day, hour(event_time) AS hour, COUNT(*) / 24.0 AS avg_hourly_count FROM events WHERE event_time >= current_date() - INTERVAL 7 DAYS GROUP BY date_trunc('day', event_time), hour(event_time)

Answer

SELECT date_trunc('day', event_time) AS day, hour(event_time) AS hour, AVG(count(*)) OVER (PARTITION BY date_trunc('day', event_time), hour(event_time)) FROM events WHERE event_time >= current_date() - INTERVAL 7 DAYS GROUP BY date_trunc('day', event_time), hour(event_time)

Question 11

A developer runs a Spark SQL query that joins a fact table with a dimension table and then filters on a column from the dimension table. The query is slow because the dimension table is small but the fact table is huge. Which Catalyst optimizer rule is most likely responsible for improving performance by handling this pattern?

Accepted Answer

BroadcastHashJoin. The Catalyst optimizer includes a rule called BroadcastHashJoin that automatically broadcasts a small table to all executors when joining with a large table, eliminating the need to shuffle the large table. This is exactly the scenario described: a small dimension table joined to a huge fact table. The other rules address different optimizations like predicate pushdown or constant expression evaluation, which are not the primary reason for improved join performance here.

Answer

PushDownPredicate

Answer

OptimizeIn

Answer

ConstantFolding

Question 12

A developer is using Spark SQL in a Databricks notebook to analyze a DataFrame orders with columns order_id, customer_id, amount, and status. They need to compute, for each customer, the total amount of only the completed orders, and also count how many distinct statuses each customer has across all their orders. Which TWO expressions achieve these two aggregations correctly in a single groupBy? (Choose two.)

Accepted Answer

countDistinct(col('status')).alias('distinct_statuses'). The two required aggregations are a conditional sum for completed-order amounts and a distinct count of statuses. A conditional sum with otherwise(0) yields the correct total per customer, and countDistinct returns the number of unique statuses. The other expressions either use unsupported syntax, count non-distinct values, or compute an average instead of a sum.

Answer

sum(col('amount')).filter(col('status') == 'completed').alias('completed_total')

Answer

avg(when(col('status') == 'completed', col('amount'))).alias('completed_total')

Answer

count(col('status')).alias('distinct_statuses')

Question 13

You are writing a Spark SQL query against a DataFrame registered as a temporary view named sales. The query needs to return only the distinct product categories from the sales view, sorted alphabetically. Which SQL statement should you use?

Accepted Answer

SELECT DISTINCT category FROM sales ORDER BY category ASC. The correct statement uses DISTINCT to eliminate duplicates and ORDER BY to sort. It is the simplest and most direct way to achieve the requirement. The other options either use unsupported syntax or unnecessarily complex constructs.

Answer

SELECT category FROM sales GROUP BY category ORDER BY category ASC

Answer

SELECT DISTINCT(category) FROM sales ORDER BY category ASC

Answer

SELECT UNIQUE category FROM sales ORDER BY category ASC

Question 14

A data engineer is working with a Spark SQL DataFrame that contains a column `event_time` of type string in the format 'yyyy-MM-dd HH:mm:ss'. The engineer needs to extract the year and month as separate integer columns for downstream analysis. Which two functions should be used together to achieve this? (Choose two.)

Accepted Answer

month(). To extract year and month as separate integer columns from a timestamp, the appropriate Spark SQL functions are year() and month(). These functions operate on date/timestamp types and return integers. The string column must first be converted to a timestamp using to_timestamp() or cast, but the extraction itself is done by year() and month(). Other functions like date_format() return strings, and to_date() only converts types.

Answer

date_format()

Answer

trunc()

Answer

to_date()

Question 15

A data analyst is working in a Databricks notebook and needs to run a Spark SQL query that extracts the year from a string column named order_date, which is stored in the format 'yyyy-MM-dd'. Which SQL expression should they use in the SELECT statement?

Accepted Answer

YEAR(TO_DATE(order_date)). To extract a date part from a string in Spark SQL, the string must first be converted to a date or timestamp type. TO_DATE parses the ISO-formatted string into a date, and YEAR then returns the integer year. Other functions like EXTRACT or DATE_PART also require a date/timestamp input, so they cannot be applied directly to the string column without a prior conversion. The correct approach combines TO_DATE with YEAR for a reliable, type-appropriate result.

Answer

YEAR(order_date)

Answer

DATE_PART('year', order_date)

Answer

EXTRACT(YEAR FROM order_date)