Skip to content

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?

  1. AHe can add his rules to the workspace instructions file, since every user can edit .assistant_workspace_instructions.md
  2. BHis user instructions take precedence over the workspace instructions for every request he makes
  3. CHis user instructions also apply to Quick Fix and Autocomplete suggestions in the editor
  4. 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.

Question 2 · choose 1

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?

  1. ARaise the maximum number of clusters for both problems, since more clusters also stop the spilling
  2. BRaise the maximum number of clusters for the morning queue, and increase the cluster size for the spilling query
  3. CSwitch the warehouse to the classic type to gain Intelligent Workload Management for the morning peak
  4. 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.

Question 3 · choose 1

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?

  1. AThe privileges of the workspace admin who enabled Genie Code
  2. BThe privileges of the notebook's or query's owner
  3. CHer own Unity Catalog permissions on the data she queries
  4. 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.

Databricks documentation

Question 4 · choose 1

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?

  1. ANothing; only workspace admins can start a SQL warehouse
  2. BCreate a new SQL warehouse of her own
  3. CAsk Genie Code to rebuild the warehouse
  4. 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.

Question 5 · choose 1

An older internal guide tells analysts to run their queries on a "SQL endpoint". What is that resource called in Databricks today?

  1. AA Lakeflow job
  2. BA serverless pipeline
  3. CA SQL warehouse
  4. 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.

Practice domain 4 →Practice all domains →