Data Analyst Associate · Domain 5: Analyzing Queries · 15% of the exam
Task 5.1: Understand the Features, Benefits, and Supported Workloads of Photon; Identify poorly performing queries in the Databricks Intelligence platform, such as Query Insights, Query Profiler log, etc
What Photon accelerates and where it runs, and finding slow queries with query history, the query profile and query performance insights.
Study it
Photon, query history, the query profile and performance insights
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
A team moves its ad hoc SQL and scheduled reports to a serverless SQL warehouse and asks what it must do so that Photon accelerates these queries. What should the analyst tell them?
ASelect the Use Photon Acceleration checkbox when editing the warehouse
BNothing; Photon is the default engine for SQL warehouses and needs no code changes
CRewrite the reports with the DataFrame API, which Photon accelerates instead of SQL
DConvert the stateful streaming jobs that feed the reports so Photon can run them
Show the answer and why
ASelect the Use Photon Acceleration checkbox when editing the warehouse
Incorrect
That checkbox belongs to classic all-purpose and jobs compute; Photon is already the engine of every SQL warehouse.
BNothing; Photon is the default engine for SQL warehouses and needs no code changes
Correct
Photon is enabled on SQL warehouses and serverless compute and supports SQL with no code changes.
CRewrite the reports with the DataFrame API, which Photon accelerates instead of SQL
Incorrect
Photon accelerates SQL workloads as well as DataFrame API calls, without code changes.
DConvert the stateful streaming jobs that feed the reports so Photon can run them
Incorrect
The question is about the warehouse's SQL queries; besides, Photon supports stateless streaming only.
Photon is the Databricks-native query engine. It is the default engine for all SQL warehouses and is enabled on serverless compute and serverless Lakeflow pipelines; it supports SQL and DataFrame APIs with no code changes. On classic compute it is controlled with the Use Photon Acceleration checkbox.
A dashboard query on a SQL warehouse filters a large table only by customer_id. The table is liquid clustered by event_date, and the query profile shows the insight COVERAGE_FILTER_KEYS_CLUSTERING. The dashboard always shows the last 30 days. Which change by the analyst follows the insight's recommendation?
AIncrease the cluster size of the warehouse
BSelect Optimize so that Genie Code changes the table's clustering keys and applies them
CAdd a filter on event_date for the last 30 days to the dashboard's query
DRun CACHE SELECT on the table each morning before the dashboard is used
Show the answer and why
AIncrease the cluster size of the warehouse
Incorrect
A larger size adds compute but does not reduce the bytes read, which is what the insight is about.
BSelect Optimize so that Genie Code changes the table's clustering keys and applies them
Incorrect
For insights that involve table or compute changes, Genie Code summarizes the recommended actions as text; it rewrites only queries.
CAdd a filter on event_date for the last 30 days to the dashboard's query
Correct
The insight flags clustering keys that are not used in filters, and its recommendation is to add filters on the clustering keys to reduce bytes read.
DRun CACHE SELECT on the table each morning before the dashboard is used
Incorrect
CACHE SELECT is ignored on SQL warehouses, and caching does not act on the missing clustering-key filter.
Query performance insights appear in query history and in the query profile's Performance insights tab. COVERAGE_FILTER_KEYS_CLUSTERING means the table is clustered by keys that the scan does not filter on; the recommendation is to add filters on the clustering keys. Optimize opens Genie Code, which rewrites queries and only describes table or compute changes.
In a workspace enabled for serverless compute, a notebook on serverless compute ran slowly last night, and an analyst wants to see which of its SQL statements took longest, with their durations. Where can she find them first?
AOn the Query history page, filtered by compute and date range
BOnly in the system.query.history table, because the Query history page lists SQL warehouse queries only
COn the SQL tab of the Spark UI for the notebook's compute
DIn DESCRIBE HISTORY of the tables the notebook read
Show the answer and why
AOn the Query history page, filtered by compute and date range
Correct
Query history also contains SQL and Python queries run on serverless compute for notebooks and jobs, and it filters by compute, date, duration and status.
BOnly in the system.query.history table, because the Query history page lists SQL warehouse queries only
Incorrect
In serverless-enabled workspaces the Query history page includes serverless notebook and job queries as well.
COn the SQL tab of the Spark UI for the notebook's compute
Incorrect
The Spark UI is not available on serverless compute; Databricks points to the query profile instead.
DIn DESCRIBE HISTORY of the tables the notebook read
Incorrect
Table history records provenance for each write to a table, not the queries that read it.
The Query history page lists queries run on SQL warehouses and, when the workspace is enabled for serverless compute, SQL and Python queries run on serverless compute for notebooks and jobs. Filters cover user, date range, compute, duration, status, statement type and query tags.
How does Photon process data compared with the traditional Spark SQL engine?
AIt runs each query on one large node so that no data is ever shuffled
BAs a native C++ vectorized engine that processes columnar batches
CIt replaces the Catalyst optimizer with its own query planner
DIt caches every table in memory before running queries
Show the answer and why
AIt runs each query on one large node so that no data is ever shuffled
Incorrect
Photon replaces the execution layer; it does not limit queries to one node.
BAs a native C++ vectorized engine that processes columnar batches
Correct
Photon replaces the JVM-based execution engine with a native C++ runtime that processes data in columnar batches.
CIt replaces the Catalyst optimizer with its own query planner
Incorrect
Catalyst still plans the query; Photon takes over at execution.
DIt caches every table in memory before running queries
Incorrect
Photon's gains come from vectorized columnar execution, not from caching whole tables.
Photon is the Databricks-native vectorized query engine. For supported operations it replaces the JVM-based Spark SQL execution engine with a native C++ runtime, processing columnar batches; the Catalyst optimizer still plans the query.
A query on Photon-enabled compute uses an operation that Photon does not support. What happens?
AIt falls back to the Spark runtime for that operation
BThe query fails with an unsupported-operation error
CThe whole workspace switches Photon off
DThe query waits until a Photon-compatible version is released
Show the answer and why
AIt falls back to the Spark runtime for that operation
Correct
When Photon meets an unsupported operation, it transparently falls back to the Spark runtime for the remainder of that operation.
BThe query fails with an unsupported-operation error
Incorrect
Unsupported operations do not fail the query; execution falls back.
CThe whole workspace switches Photon off
Incorrect
The fallback applies to that portion of the execution only.
DThe query waits until a Photon-compatible version is released
Incorrect
The query runs now, with the Spark runtime for the unsupported part.
When a query uses an operation that Photon does not support, Photon transparently falls back to the Spark runtime for that portion of the execution, so the query still runs.
An analyst sees in query history that one of her queries has been running for 40 minutes and is no longer needed. What can she do there?
ANothing; running queries can be stopped only by restarting the warehouse
BCancel it from the query details, so its status changes to Canceled
CMark it as Finished so the warehouse stops counting it
DMove it to the queue so other queries run first
Show the answer and why
ANothing; running queries can be stopped only by restarting the warehouse
Incorrect
A running query can be cancelled from query history.
BCancel it from the query details, so its status changes to Canceled
Correct
In query history, Cancel next to Status terminates the query, and its status changes to Canceled.
CMark it as Finished so the warehouse stops counting it
Incorrect
Status is set by the system; the action available is Cancel.
DMove it to the queue so other queries run first
Incorrect
Query history offers Cancel for a running query, not re-queuing.
Query history shows each query's status (Queued, Running, Finished, Failed or Cancelled). For a running query, click Cancel next to Status; the query is terminated and its status changes to Canceled.
A query shows the insight REDUNDANT_JOIN: an outer join did not change the outer side's row count, and no columns of the joined table are used. Which change follows the recommendation?
AIncrease the warehouse's cluster size
BRun OPTIMIZE on the joined table to compact its small files
CTurn the outer join into a cross join
DRemove the join or add primary key or unique constraints
Show the answer and why
AIncrease the warehouse's cluster size
Incorrect
The insight points to unneeded work in the query, not to warehouse size.
BRun OPTIMIZE on the joined table to compact its small files
Incorrect
Compacting files does not remove a join the query does not need.
CTurn the outer join into a cross join
Incorrect
A cross join pairs every row with every row and adds work.
DRemove the join or add primary key or unique constraints
Correct
REDUNDANT_JOIN recommends removing the join or applying primary key or unique constraints.
Query performance insights flag optimization opportunities. REDUNDANT_JOIN means an outer join did not change the outer side's row count and none of the joined table's columns are used; the recommendation is to remove the join or apply primary key or unique constraints.