Databricks SQL Editor: Summary Tables, Charts and Query History (Part 3 of 3)

A query result disappears when you close the tab. A saved table, a validated summary and a chart do not. This post follows Part 3 of the video series on the Databricks SQL Editor in Free Edition: save the sample orders as managed tables with CREATE TABLE AS SELECT (CTAS), build and validate a summary table, chart it, and find your work again with saved queries and query history.

<iframe width="560" height="315" src="https://www.youtube.com/embed/D_a9o4xdJtM" title="Databricks SQL Tutorial: Summary Tables, Charts &amp; Query History (Part 3 of 3)" frameborder="0" allowfullscreen></iframe>

Watch on YouTube

The SQL file and cheat sheet are in the Part 3 folder on GitHub. Attach the Serverless Starter Warehouse, turn off Run all statements, and run one statement at a time.

Save the orders with CTAS

USE CATALOG workspace;
CREATE SCHEMA IF NOT EXISTS bt_sql_6aab57cf80df;

CREATE TABLE workspace.btsql6aab57cf80df.orders AS SELECT * FROM VALUES (101, 'Ana', 'NORTH', 'Books', 2, CAST(10.00 AS DECIMAL(10,2)), DATE '2026-10-01'), ... (105, CAST(NULL AS STRING), 'EAST', 'Books', 1, CAST(12.00 AS DECIMAL(10,2)), DATE '2026-10-03'), ... AS t(orderid, customer, region, category, quantity, unitprice, order_date); ```

CTAS creates a table and loads it in one step. The casts on the nulls give every column a clear type. A CTAS returns no rows. No error means the table exists. A second statement saves the seven customer tiers the same way.

Managed tables and what DROP does

Neither statement has a LOCATION clause, so both are managed tables. Unity Catalog owns their storage, and if you drop a managed table, its data goes with it. In Catalog Explorer, the details tab for orders shows the type as MANAGED.

Check the load before you build on it

SELECT 'orders' AS table_name, COUNT(*) AS n
FROM workspace.bt_sql_6aab57cf80df.orders
UNION ALL
SELECT 'customers', COUNT(*)
FROM workspace.bt_sql_6aab57cf80df.customers;

The result is 8 orders and 7 customers. Recomputing the baseline from the saved table still gives 6 valid orders and 142.50 in revenue. Saving the data did not change it.

Build a summary table

CREATE TABLE workspace.bt_sql_6aab57cf80df.region_category_revenue AS
SELECT
  region,
  category,
  COUNT(*) AS order_count,
  SUM(CAST(quantity * unit_price AS DECIMAL(12,2))) AS revenue
FROM workspace.bt_sql_6aab57cf80df.orders
WHERE quantity IS NOT NULL AND unit_price IS NOT NULL
GROUP BY region, category;

This is a small gold-style table: pre-aggregated and ready for a dashboard or BI tool. Reading it back gives the same five buckets as Part 2. The difference is that this result is stored, so anyone can query it without repeating the logic.

Validate it with a PASS or FAIL rule

SELECT
  COUNT(*) AS bucket_count,
  SUM(order_count) AS total_orders,
  SUM(revenue) AS total_revenue,
  CASE
    WHEN COUNT(*)=5 AND SUM(order_count)=6 AND SUM(revenue)=CAST(142.50 AS DECIMAL(12,2))
      THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM workspace.bt_sql_6aab57cf80df.region_category_revenue;

The result is 5 buckets, 6 orders, 142.50 and PASS. Revenue is compared to a value cast as a decimal, so the comparison is exact.

Create a bar chart

Roll the summary up to revenue by region, which gives the easiest shape to chart: one label and one number.

SELECT region, SUM(revenue) AS revenue
FROM workspace.bt_sql_6aab57cf80df.region_category_revenue
GROUP BY region
ORDER BY region;

The result is EAST 12.00, NORTH 80.00, SOUTH 25.00 and WEST 25.50. Run only this statement, then above the results click the + button and choose Visualization:

1. Pick Bar. If it opens as a horizontal chart, uncheck Horizontal chart.
2. Set the X column to region.
3. Set the Y column to revenue, with Sum.
4. Save it. The chart becomes a tab next to the table and is saved with the query.

NORTH clearly dominates, with 80.00 of the 142.50.

Join the saved tables

SELECT
  c.tier,
  COUNT(DISTINCT o.order_id) AS matched_orders,
  SUM(CAST(o.quantity * o.unit_price AS DECIMAL(12,2))) AS revenue
FROM workspace.bt_sql_6aab57cf80df.orders o
INNER JOIN workspace.bt_sql_6aab57cf80df.customers c ON o.customer=c.customer
WHERE o.quantity IS NOT NULL AND o.unit_price IS NOT NULL
GROUP BY c.tier
ORDER BY c.tier;

Bronze has 1 order and 30.00, gold 3 and 75.50, silver 1 and 25.00. That adds up to 130.50, the inner join number from Part 2. The 12.00 gap is order 105, which has no customer.

Saved queries and query history

Saved queries are files in your workspace, so you can rename, move and share them like any other workspace file.

Query History in the sidebar lists every statement your warehouse ran. You can filter by user, date range, compute, duration and status. Failed statements show a red icon. In the video's session, two failures appear: a CREATE TABLE on a table that already existed, and a typo. Click any query to see its status, duration and rows returned, then open See query profile for the full statement and a graph of the plan showing where the time went.

Practice challenge

Write a validation for just the NORTH region of the summary table: it should have 2 buckets and 80.00 in revenue. Filter with WHERE region = 'NORTH', then check that COUNT(*) = 2 and SUM(revenue) = CAST(80 AS DECIMAL(12,2)). NORTH has a Books bucket at 20.00 and a Toys bucket at 60.00, so the result is PASS.

Bonus: to show revenue by region split into Books and Toys, chart the summary table as a bar chart with region on the X axis, revenue on the Y axis and category as the group by.

Exam traps from Part 3

• A table with no LOCATION is managed, and dropping it deletes the data.
• A plain CREATE TABLE fails if the table already exists. For scripts you rerun, use CREATE OR REPLACE or IF NOT EXISTS.
• UNION ALL keeps duplicates. UNION removes them.
• Validate every summary table against totals you trust.