Executing Queries with Databricks SQL

A data analyst runs a query in Databricks SQL that aggregates sales data by product category. The analyst notices that the query results include a category value of NULL. The analyst wants to exclude rows where the category is NULL from the aggregation. Which SQL clause should be added to the query?

Which TWO of the following are benefits of using Unity Catalog with Databricks SQL?

Refer to the exhibit. An analyst receives this error when attempting to select from a table in Databricks SQL. What is the most likely cause?

An analyst is performing a join between a large fact table and a small dimension table. To ensure optimal performance in Databricks SQL, which join type is preferred when the dimension table fits in memory?

An analyst wants to view the query profile for a specific query that was run recently. Where should they look in the Databricks SQL UI?

An analyst is preparing a dashboard and needs to ensure that the queries powering it are as performant as possible. Which THREE techniques should the analyst use to optimize these SQL queries in Databricks?

A data analyst needs to share a frequently updated sales dashboard with business stakeholders who do not have access to the underlying raw tables containing Personally Identifiable Information (PII). Which Databricks SQL feature should be implemented to securely present this summary data?

When querying a Delta table that is updated frequently, what is the most reliable way to ensure an analyst sees the most up-to-date data?

When migrating a reporting workload to Databricks SQL, an analyst needs to ensure that specific query results are consistent across multiple runs, even if the underlying Delta table is being updated. Which feature should the analyst utilize?

A data analyst has a Databricks SQL query that joins a large fact table to a small dimension table. The query is slow because the dimension table is being shuffled across the cluster. The analyst wants to avoid this shuffle and improve performance. Which SQL hint should they use?

Which Databricks SQL object allows an analyst to save a specific query result set and share it with other users, ensuring they see a snapshot of the data at the time of execution?

Which of the following describes the purpose of 'Data Skipping' in Databricks SQL when querying Delta tables?

A data analyst runs a query in Databricks SQL that joins a large fact table to a small dimension table. The query spills to disk and is slow. The analyst adds a /*+ BROADCAST(dim) */ hint to the query. What is the primary effect of this hint on the query execution?

A data analyst is querying a Delta table that is frequently updated with new transactions. The analyst needs to ensure that a report always reflects the most recent committed data, even if a write operation is in progress. Which feature of Delta Lake should the analyst rely on?

A data analyst needs to query a Delta table named sales and retrieve only the rows where the sale_date is in the year 2023. The table is partitioned by sale_date. Which SQL statement will most efficiently return the required data?

Which DDL statement is used to remove a table's data and metadata permanently from the catalog?

An analyst needs to perform a join between a very large table and a small lookup table. Which optimization strategy should the analyst ensure is utilized to maximize performance?

An analyst wants to visualize trends over time using a Databricks SQL dashboard. Which feature should they use to allow end-users to dynamically filter the dashboard data without modifying the underlying SQL queries?

A data analyst is writing a Databricks SQL query that needs to join a large table with a small table and then aggregate the results. To optimize performance, they want to ensure the small table is broadcasted. Which SQL hint syntax should they use in the query?

A data analyst is using Databricks SQL to query a large Delta table. The query is performing slowly because it must scan the entire table to retrieve data for a specific date range. Which action should the analyst take to optimize query performance?

Which THREE of the following are benefits of using the Delta Lake format in Databricks SQL?

An analyst runs a long-running query that fails with an 'Out of Memory' (OOM) error. Which approach is the most effective way to resolve this without changing the underlying data structure?

An analyst needs to query a table that has many small files resulting from frequent streaming ingestions. Which command is most appropriate to consolidate these files into larger, more efficient files for future analytical queries?

Which of the following describes the purpose of 'Query History' in Databricks SQL?

An analyst is running queries on a shared Databricks SQL Warehouse. Which TWO actions improve query performance by reducing the impact of high concurrency?

Which option is recommended to ensure that a SQL Warehouse automatically terminates when no queries are being executed, thereby minimizing unnecessary costs?

You want to create a temporary view that is only available for the duration of the current SQL session. Which command achieves this?

A data analyst is running a complex aggregate query in Databricks SQL that frequently times out before finishing. The underlying Delta table contains hundreds of gigabytes of historical log data partitioned by date. What is the most effective Databricks SQL query optimization technique to apply directly within the SQL statement?

An analyst is using Databricks SQL and wants to optimize query performance for large-scale joins. Which TWO actions should the analyst perform to improve the performance of join operations involving large Delta tables?

A data analyst is exploring a large Delta table named sales in the Databricks SQL query editor. Before writing a detailed report, the analyst wants to quickly view the first 10 rows to understand the column names and data types. Which SQL statement should the analyst execute?

An analyst wants to see the unique values in a column named 'category'. Which SQL command should they use?

Which Databricks SQL feature should an analyst use to view the execution plan and understand the data flow, including the number of rows processed and the time taken at each stage of a query?

Which THREE of the following are valid methods to optimize the performance of a slow-running SQL query in Databricks?

An analyst needs to combine data from two tables that share a common key. Which join type returns all rows from both tables, even if there is no match in the other table?

Refer to the exhibit. An analyst attempts to query table changes using the table changes function (table_changes()) to track incremental updates for a reporting pipeline, but encounters the error shown in the exhibit. How should the analyst resolve this issue?

A data analyst is troubleshooting a slow Databricks SQL query that joins a large fact table with a small dimension table. The query plan shows a broadcast hash join, but the analyst notices that the small table is not being broadcast as expected. Which configuration should the analyst check to ensure the small table is broadcast?

An analyst is preparing a report and needs to ensure that sensitive PII columns are not exposed. Which TWO techniques can be used to achieve this in Databricks SQL?

A data analyst is building a Databricks SQL dashboard that includes a parameter for the region. The dashboard should allow viewers to select a region from a dropdown list that is populated dynamically from the distinct values in the sales table's region column. Which feature should the analyst use to implement this?

You want to perform a case-insensitive search for a string in a column. Which function is most efficient to use for this purpose?

An analyst is observing that a specific query is slow because it performs a large shuffle. Which of the following is the most likely cause, and how can it be mitigated?

Which clause is used in Databricks SQL to filter results after an aggregation has been performed?

An analyst is optimizing query performance in Databricks SQL. Which TWO of the following actions directly improve query execution speed by reducing the amount of data scanned during query execution?

Refer to the exhibit. The table 'sales' is partitioned by 'region' and 'order_date'. How does the Databricks SQL engine process this query to ensure optimal performance?

A data analyst is running a complex, resource-intensive query inside the Databricks SQL query editor that frequently times out before returning results. Which administrative feature should the analyst or workspace admin leverage to manage compute resources and prevent long-running queries from blocking other users?

An analyst is writing a query in the Databricks SQL editor and needs to reference a temporary view that was created earlier in the same interactive session. How are temporary views scoped within Databricks SQL environments?

Which capability is provided by Databricks SQL 'Alerts' when a threshold is triggered?

Which function is used to calculate the average of a column in a SQL query?

Refer to the exhibit. Why might the analyst choose to create a new table with 'AS SELECT *' instead of just running OPTIMIZE on the original table?