SQL Warehouses, Genie Code and Federated Queries

What it is

A SQL warehouse is the compute resource that runs SQL in Databricks SQL. Every query from the SQL editor, an AI/BI dashboard, a Genie Agent, an alert or an external BI tool runs on one.

Genie Code (formerly Databricks Assistant) is the AI helper inside notebooks and the SQL editor that writes, explains and fixes code. Lakehouse Federation lets the same warehouse query external databases through Unity Catalog, so one query can join a Delta table with a table that still lives in, for example, PostgreSQL.

How it works

What the warehouse does in query execution

When you run a query, the warehouse parses and plans it, Unity Catalog checks that you hold the needed privileges, and the warehouse's clusters read the data and compute the result with the Photon engine. Results go back to the editor, dashboard or tool.

You pay for the warehouse while it runs. Its size (from 2X-Small upward) sets the power for each query, and its scaling range (minimum and maximum clusters) adds clusters when many users query at once. Auto stop shuts an idle warehouse down so it stops costing money.

Warehouse types

Serverless suits BI and ad hoc work because it starts fast and scales quickly. Pro fits where serverless is unavailable or custom networking is needed; classic gives entry level performance.

Genie Code in the SQL editor and notebooks

Genie Code also gives AI autocomplete as you type. It uses Unity Catalog metadata such as table names, columns and comments, which is why well documented tables give better suggestions.

Federated queries

Lakehouse Federation exposes an external database as a foreign catalog. An admin creates a connection (host, credentials), then a foreign catalog on it; after that, its tables use normal three level names.

CREATE CONNECTION pg_crm TYPE postgresql
  OPTIONS (host 'crm.example.com', port '5432',
           user secret('crm', 'user'), password secret('crm', 'pwd'));
CREATE FOREIGN CATALOG crm USING CONNECTION pg_crm OPTIONS (database 'crm');

A cross system query is then an ordinary join:

SELECT o.order_id, o.amount, c.segment
FROM main.sales.orders AS o
JOIN crm.public.customers AS c ON o.customer_id = c.id
WHERE c.country = 'US';

Federation suits ad hoc reporting and proofs of concept without copying data.

How the exam asks about this

• "A dashboard query must join lakehouse sales with customers in a PostgreSQL database, without copying data." Use Lakehouse Federation: a connection, a foreign catalog, then a join.
• "A query fails with a column not found error in the SQL editor." Use Genie Code, /fix or Diagnose error, to propose a corrected query.
• "Many analysts hit the dashboard at 9 a.m. and queries queue." Raise the warehouse's maximum clusters (scaling), not only its size.