Query and explore your data
After your pipeline writes its tables, query them to confirm that the transformations produce the output that you expect, and to explore the data. On Databricks, you write and run queries in the SQL editor, which runs them on a SQL warehouse. The SQL editor includes a schema browser for finding tables, autocomplete, visualizations of query results, and Genie Code for help writing and optimizing queries.
Databricks creates a small SQL warehouse called Starter Warehouse automatically. You can edit or delete this SQL warehouse. Databricks recommends serverless SQL warehouses when they're available. See Connect to a SQL warehouse.
An idle SQL warehouse keeps accruing charges until it stops, so keep Auto Stop turned on. For serverless SQL warehouses, the default of 10 minutes suits typical use. Start with a small warehouse for exploration, and size up only when queries are slow. See Create a SQL warehouse.
The following video introduces Databricks SQL:
Requirements
You need the following before you query the tables:
- The
CAN USEpermission on a SQL warehouse. If no SQL warehouses appear in the SQL editor, contact your workspace admin. - The
USE CATALOGprivilege on the catalog, theUSE SCHEMAprivilege on the schema, and theSELECTprivilege on the tables that you query. - The silver tables from Build a pipeline with Genie Code, in
starterjourney_catalog.bakehouse_silver.
Run your first query
Pick a warehouse and a schema, and then write and run a query in the SQL editor.
Open the SQL editor
In your workspace, click SQL Editor in the sidebar.
Select a SQL warehouse
Use the compute drop-down menu near the top of the editor to select a SQL warehouse, such as Starter Warehouse. If the warehouse is stopped, it starts automatically when you run a query.
Select the catalog and schema
Select starterjourney_catalog and bakehouse_silver from the drop-down menus in the editor. Queries then resolve table names against that catalog and schema, so you can write transactions instead of starterjourney_catalog.bakehouse_silver.transactions. A fully qualified table name in a query overrides the selected catalog and schema.
To browse the tables in a schema, use the schema browser in the left pane. Click a table name to view its columns.
Write and run a query
Enter a query in the editor, and press Ctrl+Enter (Cmd+Enter on macOS) or click Run (1000). This query reads the silver transactions table:
SELECT
transaction_timestamp,
franchise_id,
product,
total_price
FROM
transactions
LIMIT 10
The results appear in the results pane. By default, the editor returns at most 1,000 rows. If the query fails, see Troubleshooting.
Explore the results
After the query runs, chart the results, get help from Genie Code, or save and share the query:
- Visualize the results: In the results pane, click
to open the visualization editor, and then create a chart from the results. See View query results in the new SQL editor.
- Get help from Genie Code: Click
Genie Code to ask questions about your data, generate a query, or explain a query. To optimize a query, use the
/optimizecommand. See Write queries in the new SQL editor. - Save and share the query: Click Save to save the query to your home folder, and then share it with your team. In the SQL editor, a shared query runs with each viewer's credentials, so teammates need the
CAN USEpermission on the SQL warehouse and the privileges that the query uses. See Run and share queries in the new SQL editor.
Answer a business question
Use the silver tables to answer a revenue question: how much revenue did each franchise make each day?
Ask Genie Code for the join
In the SQL editor, open Genie Code and paste this prompt:
Tell Genie Code to do this for you:
Using the transactions and franchises tables in the current bakehouse_silver schema, write a query that joins them on franchise_id. Return the transaction date, franchise name, and total daily revenue. Calculate daily revenue as the sum of total_price, group by transaction date and franchise name, and sort by transaction date and franchise name.
Check the generated SQL
Review the generated SQL. The query is correct if it does the following:
- Joins
transactionstofranchisesonfranchise_id. - Converts or truncates
transaction_timestampto a calendar day. - Sums
total_price. - Groups by the transaction day and the franchise name.
Use the fallback SQL
Generated SQL can look different and still be correct. If it misses one of the checks, replace it with:
SELECT
DATE(t.transaction_timestamp) AS transaction_date,
f.franchise_name,
SUM(t.total_price) AS daily_revenue
FROM transactions AS t
INNER JOIN franchises AS f
ON t.franchise_id = f.franchise_id
GROUP BY
DATE(t.transaction_timestamp),
f.franchise_name
ORDER BY
transaction_date,
franchise_name;
Run the query
Click Run (1000), or press Ctrl+Enter (Cmd+Enter on macOS).
Check the results
The grid must contain transaction_date, franchise_name, and daily_revenue. Franchise names must not be null, and the result must include more than one date and franchise combination.
Save the query
Click Save, and name the query Daily franchise revenue. You check the metric view totals against this query later. See Define sales metrics as a metric view.
Troubleshooting
A query can fail because Databricks can't find the table, or because no SQL warehouse is available to run it.
A query fails with TABLE_OR_VIEW_NOT_FOUND
The table doesn't exist in the selected catalog and schema. Confirm the catalog and schema in the editor drop-down menus, or use the fully qualified table name, such as starterjourney_catalog.bakehouse_silver.transactions.
The editor can't find the silver tables
Selecting bakehouse_silver isn't enough if the editor is using a different catalog. Select starterjourney_catalog, and then select bakehouse_silver again.
The SQL runs but answers a different question
Check the join on franchise_id, the day-level conversion of transaction_timestamp, and SUM(total_price). A correct answer needs all three.
No SQL warehouses appear in the compute drop-down menu
You don't have the CAN USE permission on any SQL warehouse. Ask your workspace admin to grant you access to a SQL warehouse.
Next steps
-
- Orchestrate workloads with Lakeflow Jobs
- Schedule your pipeline and notebooks with a job in Lakeflow Jobs
-
- Databricks SQL editor
- Learn about all of the features of the SQL editor