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?
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?
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?
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?
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?
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?
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?
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?
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?
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.
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?
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?
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?
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.)
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?
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.)
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?
A data engineer is building a Spark SQL job that reads a JSON file where each line is a separate record. The schema is variable, and many fields are missing in some records. The engineer wants to ensure that the query does not fail due to malformed records and that missing fields are treated as null. Which approach should be used when creating the DataFrame?
You are working with a Spark SQL DataFrame that contains a column named metadata of type STRING, which stores JSON strings like '{"key": "value", "count": 10}'. You need to extract the value of the "count" field as an integer for each row. Which Spark SQL function should you use?
A developer needs to join a 40 GB fact table named orders with a 2 MB lookup table named currency_rates on the currency_code column. The lookup table is read from a Delta table and is broadcast correctly, but the developer notices the broadcast exchange still shuffles the small side in one of two consecutive join stages because the same small DataFrame is reused in both joins. Which Spark SQL technique avoids re-broadcasting the small relation for the second join?
A developer is writing a Spark SQL query in Databricks that filters a large Delta table by a partition column and then applies a high-cardinality distinct count on a non-partition column. They notice the query reads far more data than expected and the distinct count causes a large shuffle. They want to reduce data scanned and shuffle size without changing the result semantics. Which approach is most appropriate?
A developer has a Spark SQL DataFrame `df` with columns `id` and `value`. They need to create a new DataFrame that contains only rows where `value` is greater than 100. Which code snippet correctly achieves this using the DataFrame API?
A developer needs to run a Spark SQL query that calculates the total sales per region from a table named 'sales'. The table has columns 'region' and 'amount'. Which SQL statement correctly computes the total sales per region?
A data engineer is working with a Spark SQL DataFrame that contains a column 'tags' as an array of strings. They need to filter rows where the array contains the string 'urgent'. Which TWO Spark SQL functions can be used to achieve this? (Choose two.)
A data engineer is building a Spark SQL query that joins a large fact table with a small dimension table. The dimension table is cached in memory and is known to have a few hundred rows. The engineer wants to ensure the join is executed as a broadcast hash join without relying on the automatic broadcast threshold. Which hint should be added to the SELECT statement?
A data engineer is working with a Spark SQL DataFrame that has a column 'timestamp' of type string in the format 'yyyy-MM-dd HH:mm:ss'. The engineer needs to filter rows where the timestamp is on or after '2023-01-01 00:00:00'. Which approach correctly performs this filtering?
A data analyst is working with a Spark SQL DataFrame that has a column 'price' with some null values. They want to replace all nulls in the 'price' column with 0. Which Spark SQL expression should they use?
A data engineer is using Spark SQL to join two large tables, 'orders' and 'customers', on customer_id. The 'orders' table has a column 'order_date' and the 'customers' table has a column 'signup_date'. The engineer wants to include only orders placed after the customer's signup date. Which join condition correctly implements this requirement?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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 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?
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?
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 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 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 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?
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.)
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?
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 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?
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.)
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?
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.)
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?
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?
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?
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 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?
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?
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?