How can you leverage LLMs and prompt engineering to generate or explain Spark SQL code in Databricks?

In Databricks the built-in way is Genie Code, the workspace's AI assistant: it writes Spark SQL from a description, explains or fixes a query, and sees your Unity Catalog tables and columns, so its SQL uses real names. Prompt engineering is what makes its answers right: name the tables, say what the result should look like, and check what comes back.

1. Generate a query

A weak prompt: "average sales per region last quarter". The model has to guess the table, the date column and what "last quarter" means.

A better prompt:

Using main.sales.orders (columns region, order_date, amount), write Spark SQL for the average order amount per region for the previous calendar quarter, relative to today. Sort by average, highest first.

SELECT region, AVG(amount) AS avg_amount
FROM main.sales.orders
WHERE order_date >= add_months(date_trunc('QUARTER', current_date()), -3)
  AND order_date <  date_trunc('QUARTER', current_date())
GROUP BY region
ORDER BY avg_amount DESC;

Asking for dates relative to today, not fixed ones, keeps the query correct next quarter.

2. Explain a query

Select a query and ask Genie Code to explain it, or ask for something specific: "Explain step by step what this query returns, and point out anything that could produce duplicates or drop rows." Asking for risks, not just a summary, gets a more useful answer.

3. Fix and improve

When a query fails, Genie Code can read the error and propose a fix. It can also be asked to rewrite a query: "rewrite with window functions instead of the self-join", or "why is this slow? Here is the query profile".

4. Generate SQL from inside SQL

ai_query calls a model from a query, for example to turn a column of business questions into draft SQL for review. Use it for batch work, not for running generated SQL automatically.

Prompting habits that work

• Name the tables and columns, or let the assistant read them from Unity Catalog; add table and column comments so it understands the meaning.
• State the definitions: what counts as an active customer, which date column defines the quarter.
• Ask for one thing at a time and build up complex queries in steps.
• Give an example of the expected output shape.

Always check the result: run it on a small date range, compare a total with a figure you trust, and read the joins and filters. A query can run without errors and still answer a different question. For business users who do not write SQL, a Genie space, set up by an analyst with example queries and definitions, gives answers within limits the analyst controls.