Data Analyst Associate · Domain 4: Executing queries using Databricks SQL and Databricks SQL Warehouses · 20% of the exam
Task 4.1: Utilize Genie Code (Databricks Assistant) within a Notebook or SQL Editor to facilitate query writing and debugging; Explain the role a SQL Warehouse plays in query execution
Asking Genie Code to write, explain and fix SQL in the editor, and what serverless, pro and classic SQL warehouses provide, how they scale and queue, and who can use them.
Study it
Genie Code in the SQL editor and notebooks
Lesson coming
SQL warehouses: types, sizing, scaling and queuing
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
An analyst, who is not a workspace admin, wants Genie Code (Databricks Assistant) to always write his SQL with common table expressions and snake_case aliases when he asks it for queries in the SQL editor. The workspace admins have set workspace instructions that ask for camelCase aliases. Which statement is correct?
AHe can add his rules to the workspace instructions file, since every user can edit .assistant_workspace_instructions.md
BHis user instructions take precedence over the workspace instructions for every request he makes
CHis user instructions also apply to Quick Fix and Autocomplete suggestions in the editor
DHe adds them to his .assistant_instructions.md; on conflicts, the workspace instructions generally win
Show the answer and why
AHe can add his rules to the workspace instructions file, since every user can edit .assistant_workspace_instructions.md
Incorrect
Only workspace administrators can add or edit workspace instructions; other users can only view the file.
BHis user instructions take precedence over the workspace instructions for every request he makes
Incorrect
Unless otherwise instructed, Genie Code generally prioritizes workspace instructions over user instructions.
CHis user instructions also apply to Quick Fix and Autocomplete suggestions in the editor
Incorrect
Instructions apply to every response, including inline suggestions and Suggest Fix; Quick Fix and Autocomplete are the exceptions.
DHe adds them to his .assistant_instructions.md; on conflicts, the workspace instructions generally win
Correct
User instructions live in .assistant_instructions.md in his user folder and apply only to him; on conflicts, workspace instructions generally win.
Genie Code supports user instructions (.assistant_instructions.md in the user's workspace folder, applied only to that user) and workspace instructions (.assistant_workspace_instructions.md, editable only by workspace admins). Genie Code generally prioritizes workspace instructions over user instructions and applies instructions to every response except Quick Fix and Autocomplete.
A pro SQL warehouse of size Small is set to a minimum and maximum of one cluster. Each morning about 40 dashboard queries arrive at once, and the monitoring page shows Peak Queued Queries well above zero. Separately, the query profile of one large nightly query shows bytes spilled to disk. Which changes address the two problems?
ARaise the maximum number of clusters for both problems, since more clusters also stop the spilling
BRaise the maximum number of clusters for the morning queue, and increase the cluster size for the spilling query
CSwitch the warehouse to the classic type to gain Intelligent Workload Management for the morning peak
DTurn on Predictive IO for the warehouse so that the large query no longer spills to disk
Show the answer and why
ARaise the maximum number of clusters for both problems, since more clusters also stop the spilling
Incorrect
Clusters are added to serve concurrent queries; spilling to disk in one query is addressed by a larger cluster size.
BRaise the maximum number of clusters for the morning queue, and increase the cluster size for the spilling query
Correct
For many concurrent queries, configure enough clusters for the peak (about one per 10 concurrent queries on pro); for spills, increase cluster size.
CSwitch the warehouse to the classic type to gain Intelligent Workload Management for the morning peak
Incorrect
Classic warehouses support Photon only; Intelligent Workload Management is a serverless feature.
DTurn on Predictive IO for the warehouse so that the large query no longer spills to disk
Incorrect
Pro warehouses already include Predictive IO, which speeds up selective scans; spills point to a cluster size that is too small.
Classic and pro warehouses scale manually between a minimum and maximum number of clusters, and Databricks recommends one cluster for every 10 concurrent queries; a Peak Queued Queries value above 0 shows that more capacity is needed. Bytes spilled to disk in the query profile indicate that the cluster size may be too small. Serverless warehouses add Intelligent Workload Management to pro's Photon and Predictive IO.
An analyst asks Genie Code (Databricks Assistant) in the SQL editor to write a query over a table on which she has no SELECT privilege. What governs what Genie Code can access for her?
AThe privileges of the workspace admin who enabled Genie Code
BThe privileges of the notebook's or query's owner
CHer own Unity Catalog permissions on the data she queries
DNone; Genie Code reads every table in the metastore
Show the answer and why
AThe privileges of the workspace admin who enabled Genie Code
Incorrect
Genie Code does not act with an admin's privileges; it uses the user's own permissions.
BThe privileges of the notebook's or query's owner
Incorrect
Genie Code works with the permissions of the user who is using it.
CHer own Unity Catalog permissions on the data she queries
Correct
Genie Code is governed by your Unity Catalog permissions, so it can only access data and perform operations you have permissions for.
DNone; Genie Code reads every table in the metastore
Incorrect
Genie Code works within the user's Unity Catalog permissions.
Genie Code, the AI coding and data assistant in notebooks and the SQL editor, is governed by Unity Catalog permissions: it can only access data and perform operations that the user has permissions for.
An analyst who is not a workspace admin finds that the SQL warehouse she uses every day is stopped. She has permission to connect to it. What can she do?
ANothing; only workspace admins can start a SQL warehouse
BCreate a new SQL warehouse of her own
CAsk Genie Code to rebuild the warehouse
DStart the existing warehouse again herself
Show the answer and why
ANothing; only workspace admins can start a SQL warehouse
Incorrect
Users can restart any SQL warehouse they can connect to.
BCreate a new SQL warehouse of her own
Incorrect
Most users cannot create SQL warehouses; that needs admin or unrestricted cluster creation permissions.
CAsk Genie Code to rebuild the warehouse
Incorrect
Starting the existing warehouse is enough, and she can do it herself.
DStart the existing warehouse again herself
Correct
Most users cannot create SQL warehouses but can restart any SQL warehouse they can connect to.
Creating a SQL warehouse requires being a workspace admin or a user with unrestricted cluster creation permissions, but users can restart any SQL warehouse they can connect to.
An older internal guide tells analysts to run their queries on a "SQL endpoint". What is that resource called in Databricks today?
AA Lakeflow job
BA serverless pipeline
CA SQL warehouse
DA Lakebase instance
Show the answer and why
AA Lakeflow job
Incorrect
A job orchestrates tasks; SQL endpoints were SQL compute.
BA serverless pipeline
Incorrect
Pipelines process data; SQL endpoints were renamed to another resource.
CA SQL warehouse
Correct
In 2023, SQL endpoints were renamed as SQL warehouses, the SQL-optimized compute that powers Databricks SQL.
DA Lakebase instance
Incorrect
Lakebase is a Postgres OLTP database, not the compute for Databricks SQL.
SQL warehouses and SQL endpoints both refer to the SQL-optimized compute resource that powers Databricks SQL; SQL endpoints were renamed as SQL warehouses in 2023.