Skip to main content

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 USE permission on a SQL warehouse. If no SQL warehouses appear in the SQL editor, contact your workspace admin.
  • The USE CATALOG privilege on the catalog, the USE SCHEMA privilege on the schema, and the SELECT privilege 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 Icon 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:

SQL
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 Plus Sign Icon 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 Sparkle genie code icon. Genie Code to ask questions about your data, generate a query, or explain a query. To optimize a query, use the /optimize command. 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 USE permission 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:

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 transactions to franchises on franchise_id.
  • Converts or truncates transaction_timestamp to 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:

SQL
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​