Using Spark SQL

A developer is writing a Spark SQL query that must handle null values in a column named discount. The requirement is to replace null discounts with 0.0, but also to replace any negative discount values with 0.0, leaving positive values unchanged. Which expression should be used?

When performing a join between a very small table and a massive table, which Spark SQL optimization technique should be applied to prevent a full shuffle?

Which SQL command is used to view the history of operations performed on a Delta table, including timestamps and operation types?

A developer is using Spark SQL to process a streaming DataFrame from a Kafka source. They need to perform a stateful operation that maintains state across micro-batches to count occurrences of each key over a sliding window of 10 minutes, sliding every 5 minutes. Which Spark SQL operation should they use?

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

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?

You are using Spark SQL to analyze a Delta table named transactions that is partitioned by a column region. You need to run a query that filters on region and also on a non-partitioned column amount. The table has statistics collected on region and amount. Which of the following best describes how Spark SQL will optimize the query?

You are querying a large Delta table partitioned by 'event_date'. You need to calculate the daily count of unique users, but the query is running slowly due to data skew on high-traffic dates. Which SQL approach effectively mitigates this skew during the aggregation?

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

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

A data engineer must produce a report that shows each department's total salary, but only for departments where the total salary exceeds 500,000. The source DataFrame is created from a Delta table with columns department and salary. Which Spark SQL query correctly returns the desired result?

Which SQL function is used to create a temporary view that persists only for the duration of the current Spark session?

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?

A developer runs `spark.sql("SELECT * FROM sales")` and receives an error stating the table or view cannot be found, even though a Parquet directory exists at `dbfs:/mnt/raw/sales/`. The developer wants to query that Parquet data using Spark SQL without moving or copying the files. Which action should the developer take?

A developer is working with a Spark SQL DataFrame that contains a column 'tags' which is an array of strings. The developer needs to filter rows where the array contains the string 'spark' and also transform the array to uppercase. Which TWO Spark SQL functions should be used to achieve these requirements? (Choose two.)

A developer is using Spark SQL to join two DataFrames: a large fact table 'orders' and a smaller dimension table 'customers'. The join key is customer_id. The developer notices that the join is causing a shuffle and wants to avoid it. Which Spark SQL technique should be used to eliminate the shuffle for this join?

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

A data engineer has two Spark SQL DataFrames: customers (customer_id, name) and orders (order_id, customer_id, amount). They want to retrieve every customer along with their orders, but they also want to include customers who have placed no orders, showing null for the order columns. Which operation should they use?

A developer is writing a PySpark script that must run a SQL statement against DataFrames already registered as temporary views named orders and returns. The developer wants the query to use Spark SQL syntax while returning a DataFrame that can be further transformed with the DataFrame API. Which call accomplishes this?

Which clause is used in a SELECT statement to filter the results based on aggregated values?

A data engineer runs the following statement in a Databricks notebook: CREATE OR REPLACE TEMP VIEW high_value_customers AS SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id HAVING SUM(amount) > 10000. Later, the same engineer opens a new notebook attached to the same cluster and tries to run SELECT * FROM high_value_customers. What will happen?

A data engineer is writing a Spark SQL query that joins a `transactions` table to a `customers` table on `customer_id`. The engineer wants to ensure that rows from `transactions` with no matching customer are still returned, with nulls for customer columns, and also wants to exclude duplicate rows that arise from the join. Which TWO clauses should the engineer include? (Choose two.)

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?

You need to combine two datasets in Spark SQL, retaining all records from the left table and matching records from the right table, while filling unmatched right columns with null values. Which join type should you use?

You are using Spark SQL to join two large Delta tables, orders and customers, on a common column customer_id. The orders table is partitioned by order_date, and the customers table is not partitioned. You need to ensure the join is efficient and minimizes shuffling. Which TWO actions should you take? (Choose two.)

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

You are processing a large dataset in Spark SQL and need to ensure that small files are avoided when writing data to Delta Lake. Which approach effectively minimizes small file generation during write operations?

An analyst notices that a Spark SQL query filtering a Delta table with `WHERE order_date = '2024-03-15'` scans far more data than expected, even though the table is partitioned by `order_date`. The partition column was loaded as a string in `yyyy-MM-dd` format. Which explanation best accounts for the excessive scan?

When joining two large tables in Spark SQL, which join type helps avoid expensive shuffles by loading a small table into memory on all executor nodes?

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

A developer is building a Spark SQL pipeline in a Databricks notebook. They need to persist an intermediate DataFrame, built from a transformation of a Delta table, as a physical table in the current database so other notebooks in the same cluster can query it. They also want the table metadata to be managed by the metastore and the data to reside in the default warehouse directory. Which Spark SQL statement should they use?

A developer writes a Spark SQL query that groups orders by region and computes the total revenue per region, but also needs to return the number of distinct customers per region in the same result set. Which TWO expressions correctly compute the distinct customer count per region in a single GROUP BY region query? (Choose two.)

An analyst has a Spark SQL DataFrame named events with a string column event_time in the format 'yyyy-MM-dd HH:mm:ss'. They want to add a new column event_date containing only the date portion, keeping the original column intact. Which expression should they use in a select statement?

A developer needs to add a computed column `discounted_price` equal to `price * 0.9` to an existing Delta table `products` and persist the change so all future queries see the new column. The table already contains data. Which statement should the developer run?

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?

A data analyst needs to run a Spark SQL query that returns the top 5 highest-paid employees from a table named employees, ordered by salary descending. Which query correctly returns exactly five rows?

Which SQL function is used to concatenate strings while allowing you to specify a custom separator, handling null values by ignoring them?

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

A developer has a Delta table events with a high-cardinality column user_id and a low-cardinality column country. A query filters on country = 'US' and also on user_id IN (...). The developer runs EXPLAIN and sees a full scan of all files. Which statement about data skipping and the Delta table's statistics correctly explains why the filter on country is not skipping files?

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

Which THREE of the following are valid ways to create a DataFrame from an existing table in Spark SQL?

Which of the following describes the behavior of a 'Broadcast Hash Join' in Spark SQL?

What is the result of applying the COALESCE function in Spark SQL when multiple arguments are provided?

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

A data engineer is working with a Spark SQL DataFrame in Databricks that has a column named event_time stored as a string in the format 'yyyy-MM-dd HH:mm:ss'. They need to filter rows where event_time falls within the last 7 days relative to the current timestamp. Which Spark SQL expression correctly achieves this?

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

A developer has a Spark SQL DataFrame `df` with an array column named `scores` containing integers. They need to create a new column `passing` that is true only when every element in `scores` is greater than or equal to 70. Which Spark SQL higher-order function should they use?

A data scientist is using Spark SQL to compute a running total of sales amounts for each customer, ordered by transaction date. The query must return, for each row, the sum of all previous sales for that customer up to and including the current row. Which window specification should be used?

Which command is used to display the logical and physical execution plans for a given Spark SQL query?

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

A developer is using Spark SQL to analyze a DataFrame that contains a column named tags, which holds an array of strings for each row. The developer needs to filter rows where the array contains the string 'urgent' and also produce a new column with the number of elements in the array. Which TWO Spark SQL expressions should be used in the query? (Choose two.)

A data engineer is building a Spark SQL pipeline that must return the top 3 highest-paid employees within each department from a Delta table named `employees` with columns `dept`, `name`, and `salary`. The engineer wants a single query that produces one row per qualifying employee, ranked by salary descending within each department, without collapsing rows. Which approach should be used?