CCNA Using Spark SQL Questions

GREATEST(discount, 0.0) turns negative values into 0.0 but returns null if discount is null. Wrapping it with COALESCE(..., 0.0) replaces that null with 0.0. This combination satisfies both conditions: nulls become 0.0, negatives become 0.0, and positive values are preserved. It is the correct nested expression.

The BROADCAST hint explicitly instructs the Spark optimizer to broadcast the smaller table to all worker nodes. This eliminates the need for a shuffle, which is the most expensive part of a join, and is a standard way to ensure high-performance execution in Spark SQL for asymmetric join operations.

DESCRIBE HISTORY is the standard command for viewing the transaction log of a Delta table. It displays information such as the operation, user, timestamp, and version, which are critical for debugging and data lineage analysis. This command allows users to monitor table evolution and identify specific modifications.

Spark SQL structured streaming supports windowed aggregations using the window function. Specifying a window duration of 10 minutes and slide duration of 5 minutes creates overlapping windows. Grouping by the window and key, then counting, maintains state across micro-batches to produce counts per window. This is the correct approach for sliding window aggregations in structured streaming.

Z-Ordering rearranges data within files to ensure that related values are physically grouped together. This maximizes the probability that a query filter will result in skipping entire files, as the file-level statistics (min/max) become much more discriminative, directly reducing the total amount of data read from storage.

The BROADCAST hint explicitly instructs the Spark catalyst optimizer to perform a broadcast hash join. By duplicating the smaller table to all executors, Spark eliminates the need for expensive wide transformations and data reshuffling, allowing the join to occur locally within each task, which maximizes performance for this specific scenario.

Spark SQL leverages partition pruning to eliminate entire partitions based on the region filter. Additionally, Delta Lake collects statistics on non-partitioned columns like amount, enabling data skipping at the file level. When statistics indicate that a file's min/max range for amount does not overlap with the filter, that file is skipped. This combination drastically reduces I/O.

Salting distributes the skewed data across multiple tasks by splitting the hot key into multiple sub-keys. This allows Spark to process the aggregation in parallel across different executors. A second aggregation stage is then required to sum the sub-totals, effectively removing the salt and providing the correct final count.

Delta Lake uses a metadata-driven approach where it only processes the files containing the records to be deleted. By writing new files and updating the transaction log, it ensures ACID compliance and enables time travel, representing the efficient, performant way Delta Lake handles record-level deletions.

ANALYZE TABLE computes statistics like count, min, max, and null counts for columns. The cost-based optimizer uses this data to decide the best join strategy. This is a best practice in Databricks for any table that is frequently used in complex SQL queries to ensure optimal execution paths.

This query correctly groups rows by department, computes the total salary per group, and then applies HAVING to filter groups whose aggregate exceeds 500,000. In Spark SQL, HAVING is the proper clause for aggregate-based filtering, and it is evaluated after GROUP BY, so it works as required.

This command creates a temporary view that exists only within the current SparkSession. It is the ideal tool for ad-hoc analysis or intermediate transformations where the data does not need to be saved to long-term storage or shared across different clusters or users within the organization's wider data environment.

Delta tables are optimized for performance with features like Z-Ordering, data skipping, and statistics. By leveraging these features, Delta tables provide the best balance of write performance and low-latency read performance for SQL queries in Databricks, making them the preferred choice for analytical data storage in the lakehouse.

Defining a table with `USING parquet` and a `LOCATION` clause registers the existing directory in the metastore so Spark SQL can resolve the name `sales`. Because the data stays in place, no copying occurs, and subsequent queries read the Parquet files directly. This matches the requirement to query existing files without relocation.

The array_contains function checks if a given value exists in an array. It returns a boolean, which can be used in a filter condition. This directly addresses the requirement to filter rows where the array contains 'spark'. It is the standard function for this purpose in Spark SQL.

The broadcast hint tells Spark to broadcast the smaller 'customers' table to all executors, avoiding a shuffle of the large 'orders' table. This is the correct technique when one side of the join is small enough to fit in memory. Spark SQL supports the BROADCAST hint, and it is the standard way to eliminate shuffle for such joins. The hint must be placed immediately after SELECT.

In Spark SQL, aggregate functions that target a specific column, such as COUNT(col), ignore NULL values. This behavior is standard ANSI SQL and is vital to understand when calculating metrics like non-null record counts, as failing to account for this can lead to significant errors in business reporting.

A left outer join keeps all rows from the left DataFrame, customers, and matches rows from orders where possible. Customers with no orders appear with null values in the order columns, which is exactly the desired behavior. This satisfies the requirement to include every customer while optionally attaching their orders.

spark.sql executes a SQL string against the current catalog and returns a DataFrame, so the result can be chained with DataFrame transformations such as filter or withColumn. Because orders and returns are registered as temporary views, they resolve in the session catalog. This is the standard way to mix SQL and the DataFrame API in one pipeline.

The HAVING clause is designed specifically to filter data after the GROUP BY and aggregation operations have been performed. This is the only way to apply predicates to the results of aggregate functions, which is a required capability for generating summarized insights from large datasets in Spark SQL.

Temporary views live in the session-scoped catalog of the SparkSession that created them. A second notebook attached to the same cluster receives a separate SparkSession, so the name high_value_customers cannot be resolved and Spark raises TABLE_OR_VIEW_NOT_FOUND. Persisting the result as a managed or external table would be required for cross-notebook visibility.

A `LEFT OUTER JOIN` preserves every row from the left `transactions` table and fills unmatched customer columns with nulls. This satisfies the requirement to retain transactions lacking a matching customer. Inner joins would drop those rows, so the outer join is essential to the scenario's stated goal of not losing left-side records.

In SQL, LIMIT is applied after the final result set has been determined, including any sorting required by the ORDER BY clause. This ensures that if you request the top 10 rows, you receive the 10 rows with the highest or lowest values as determined by the specified column ordering.

Preserving all rows from the primary left table and augmenting them with matching values from the right table defines the left outer join. Unmatched columns are automatically padded with null values, exactly meeting the business requirement.

AQE can dynamically optimize joins by converting sort-merge joins to broadcast joins if one side is small after initial stages, and by coalescing partitions. Enabling AQE helps minimize shuffling and improves performance. It is a best practice for large joins in Spark SQL, especially when statistics are outdated or data skew exists.

Temporary views are bound to the specific Spark session that created them, ensuring isolation. Global temporary views are bound to the Spark application and are accessible by any session on the same cluster, allowing shared access to temporary datasets across different notebooks or users running on the same cluster.

Enabling these properties allows Databricks to automatically coalesce small writes into larger files during the write operation itself. This significantly reduces the number of small files created by concurrent or frequent streaming writes, ensuring that data is laid out optimally for future analytical queries without manual intervention.

If the partition column is stored as a string and the literal is cast or formatted differently, Catalyst may fail to derive a static partition predicate and fall back to scanning all partitions. Type or format mismatches between the filter literal and the partition column defeat pruning. Aligning the literal's type and format with the column restores efficient partition elimination.

Broadcast Hash Join optimizes performance by broadcasting the smaller table to all executors. Because the small table is available locally on every node, the join operation avoids large data shuffles, making it significantly faster for scenarios where one table can comfortably fit within the driver-defined broadcast memory threshold.

The primary goal of caching is to keep a computed DataFrame in memory so that subsequent actions triggered on that data do not need to re-execute the entire lineage of transformations. This is crucial for performance when the same data is used multiple times within a job.

This statement creates a managed Delta table in the current database, stores its data in the warehouse directory, and registers metadata in the metastore. Because it is a managed table, it persists beyond the session and is queryable from other notebooks on the same cluster, satisfying both the persistence and metastore-management requirements described in the scenario.

COUNT(DISTINCT customer_id) is a native aggregate that counts unique non-null customer_id values within each region group. Spark SQL supports this directly inside a GROUP BY region query, so it returns the distinct customer count per region without a subquery. It handles nulls by ignoring them, which matches typical distinct-count semantics in SQL.

The to_date function parses the string using the supplied pattern and returns a date value containing only the date portion. Using the format string ensures correct parsing of the timestamp text. This produces the desired event_date column while leaving the original event_time column unchanged, which is exactly what the scenario requires.

A generated column defined with `GENERATED ALWAYS AS` is computed automatically from the expression whenever rows are written, and adding it to a Delta table backfills values for existing rows. This persists the derived value in the table so all future queries see `discounted_price` populated. It is the declarative way to express the computed column in Spark SQL on Delta.

This configuration sets the limit on the total size of serialized results of all actions that can be returned to the driver. By increasing this value, you allow larger result sets, though it is often safer to rewrite the query to write the results to storage rather than collecting them locally.

This query sorts all employees by salary in descending order and then limits the result to the first five rows. In Spark SQL, LIMIT after ORDER BY returns the top N rows according to the sort, which exactly matches the requirement to get the five highest-paid employees.

The 'concat_ws' function accepts a separator as its first argument and handles NULL values by ignoring them. This behavior is crucial for data cleaning and string formatting tasks where missing values should not invalidate the entire concatenated output, ensuring a consistent and readable result string for downstream consumers.

The Analysis phase is explicitly responsible for verifying that all referenced objects, such as tables and columns, exist in the underlying metadata catalog. It resolves these names into concrete references, which is a prerequisite for all further optimization and execution steps in the Spark SQL pipeline.

Delta data skipping relies on per-file statistics such as min/max and null counts stored in the transaction log. If statistics were not collected, or if there are too few files for skipping to matter, the optimizer cannot prune files for country = 'US'. Low cardinality alone does not guarantee skipping; the statistics must exist and the file layout must be granular enough to separate values.

The EXPLAIN command is essential for viewing the logical and physical plans generated by the Catalyst optimizer. It reveals how Spark intends to execute the query, showing joins, filtering, and scans, which helps developers identify if the plan is optimized or if performance is hindered by inefficient operations.

The 'spark.table()' method is a direct and efficient way to create a DataFrame by looking up the table name in the catalog. It is highly readable and is the standard way to retrieve a persistent table as a DataFrame for further transformation using the Spark API.

By broadcasting the smaller table, Spark eliminates the need to move the large dataset across the network. The large table's partitions are processed in parallel on each executor against a full local copy of the small table. This is the most efficient join type for small-to-large table operations.

COALESCE iterates through its arguments in order and returns the first one that is not null. If all arguments are null, it returns null. This is the idiomatic way in Spark SQL to perform null-replacement or provide fallback values for columns containing missing or null data in source systems.

The OPTIMIZE command is the standard Delta Lake operation used to coalesce small files into larger, more performant files. It is an essential maintenance task for Databricks environments to ensure that storage layouts remain optimized for analytical queries, which significantly reduces the time spent on I/O operations.

This expression uses to_timestamp with the correct format pattern to convert the string column into a timestamp, then compares it against current_timestamp() minus a 7-day interval. Spark SQL supports INTERVAL 7 DAYS syntax, and this correctly filters rows within the last week. It is the only option that both parses the string format correctly and uses a valid interval expression.

The definition of a Left Outer Join is that it preserves the entire left side of the join. For rows that do not have a corresponding key in the right table, Spark fills the right-side columns with NULLs, allowing the developer to see the full list from the left dataset.

The forall higher-order function returns true only if the lambda predicate evaluates to true for every element in the array. In this scenario, forall(scores, s -> s >= 70) yields a single boolean column that is true exactly when all scores are at least 70, which matches the requirement. It short-circuits on the first false, making it efficient.

This window specification partitions by customer_id, orders by transaction_date, and defines the frame from the start of the partition up to the current row. SUM(sales_amount) over this window yields a running total per customer. The ROWS frame is explicit and ensures each row includes all preceding rows, which is exactly the requirement. It is the standard approach for cumulative sums.

The 'EXPLAIN' command is the standard way to inspect Spark's query execution plans. It helps developers understand how the Catalyst optimizer parses and plans the query, allowing them to identify bottlenecks, such as unnecessary shuffles or full table scans, before executing the actual data-intensive operations on the large dataset.

ACID transactions ensure data integrity by allowing multiple concurrent readers and writers to interact with the data without corruption. This is a critical feature for data pipelines where consistency is paramount, and standard Parquet files do not provide this level of transactional guarantee by default.

ARRAY_CONTAINS is the correct function to test whether an array column includes a specific value. It returns a boolean and works directly on array-typed columns in Spark SQL. In a WHERE clause, it filters rows where the tags array contains 'urgent'. This is the idiomatic and efficient way to perform membership tests on arrays without exploding them first, preserving row granularity.

A window function with `PARTITION BY dept ORDER BY salary DESC` assigns a rank within each department without collapsing rows, and filtering on the rank column keeps only the top three per department. This is the idiomatic Spark SQL pattern for top-N-per-group and is fully supported in Databricks SQL warehouses and clusters.