Databricks SQL: Query Tables

You've built your Lakehouse, loaded data into tables, and now you're staring at a pile of raw data wondering how to turn it into insights. The pain is real: writing ad-hoc queries in notebooks, dealing with slow clusters, and struggling to share results with your team. Databricks SQL is the answer — a first-class SQL experience built directly on your Lakehouse, designed for analysts and data engineers who think in tables, not Spark code. In this lesson, you'll learn how to use Databricks SQL to query tables efficiently, turning raw data into decisions in minutes, not hours.

The problem this lesson solves

Traditional approaches to querying data often involve a patchwork of tools: a BI tool that can't handle big data, a notebook that only one person understands, or a database that requires constant tuning. Databricks SQL eliminates this friction by providing a unified interface to query tables directly on the Lakehouse, leveraging the power of Delta Lake and serverless compute. The problem is that many users don't know how to use Databricks SQL to its full potential — they stick to old habits and miss out on performance, governance, and collaboration features.

• Performance: Say goodbye to long-running queries that time out. Databricks SQL uses Photon and Delta Lake's optimized file formats to deliver fast results.
• Collaboration: Run queries, build dashboards, and alert your team — all from one platform.
• Governance: Use Unity Catalog to control access to tables, ensuring only the right people see the right data.

Pro tip: If you're still joining CSV files in Excel, this lesson is your escape route.

Core concept / mental model

Think of Databricks SQL as the "Google for your data." You just type a query, and the system figures out where the data lives, how to read it, and returns results in seconds. At its heart, it's a SQL endpoint that connects to your tables in the Databricks Lakehouse. The mental model is simple:

• Tables: Your data is stored as tables (often Delta tables) in the catalog.
• SQL Warehouse: A compute resource that executes your queries. It's like a virtual office where your queries do their work.
• Queries: You write SQL to select, filter, join, and aggregate data.

Key components:

• Catalog / Schema: Organizes tables into namespaces (e.g., sales_db.transactions).
• SQL Warehouse: The compute engine (classic or serverless) that runs your SQL.
• Query Editor: The UI in Databricks where you write and run queries.
• Results: Output displayed as a table or chart, ready for analysis.

How it works step by step

To query tables with Databricks SQL, follow this logical sequence:

1. Set up a SQL Warehouse: This is the compute resource that runs your queries. You can choose a Classic or Serverless warehouse based on your needs.
2. Navigate to SQL Editor: In the Databricks workspace, open the SQL Editor to write your query.
3. Select a catalog and schema: Specify which database you're querying (e.g., samples schema).
4. Write and run your query: Use standard SQL (SELECT, FROM, WHERE, etc.) to retrieve data.
5. Review and export results: View the result set, analyze it, and export for further use.

Cause and effect: Each step builds on the previous one. If your warehouse isn't running, your query will wait. If you don't specify the right schema, you'll get an error. Understanding this chain helps you debug quicker.

Hands-on walkthrough

Let's get your hands dirty. Here's how to query tables in Databricks SQL step by step.

1. Create a SQL Warehouse

If you don't have a warehouse yet, create one:

# Via Databricks UI: Compute > SQL Warehouses > Create SQL Warehouse
# Settings: Name='my_warehouse', Cluster size='Small', Serverless=ON (recommended)
# Wait for the warehouse to be running (green status)

2. Open the SQL Editor and run a basic query

In the Databricks workspace, click on SQL Editor in the sidebar. Then run:

-- Use the 'samples' catalog and the 'nyctaxi' schema
USE CATALOG samples;
USE SCHEMA nyctaxi;

-- Show the table structure
DESCRIBE TABLE trips;

-- Select the first 10 rows
SELECT * FROM trips LIMIT 10;

Expected output: A table with 10 rows and columns like tpep_pickup_datetime, trip_distance, fare_amount, etc.

3. Write an analytical query

Now let's answer a business question: "What's the average trip distance by hour?"

SELECT
  hour(tpep_pickup_datetime) AS pickup_hour,
  AVG(trip_distance) AS avg_distance
FROM trips
GROUP BY hour(tpep_pickup_datetime)
ORDER BY pickup_hour;

Expected output: 24 rows, one per hour, with the average trip distance.

4. Use a saved query for repeatability

Save your query with a name, then you can re-run it later or use it in a dashboard.

# Click 'Save' in the SQL editor, name it 'Avg Trip Distance by Hour'
# To re-run later: SQL Editor > Saved > select your query > Run

Pro tip: Use LIMIT to avoid pulling huge result sets while you're testing.

Compare options / when to choose what

When you need to query tables, you have several options. Here's a comparison:

When to choose Databricks SQL: If your task is a straightforward query or a recurring report, SQL is the winner. If you need to write multi-step ETL logic with Python, stick with notebooks.

Pro tip: You can also use Databricks SQL to create logical views — a virtual table over a query — which you can then reuse in other queries. This keeps your SQL DRY (Don't Repeat Yourself).

Troubleshooting & edge cases

Even with a tool like Databricks SQL, you'll hit issues. Here are the most common ones and how to fix them.

• Error: Table or view not found
• This usually means you're not in the right catalog or schema. Run USE CATALOG <name>; USE SCHEMA <name>; first.
• Example: SELECT * FROM missing_table; → Error. Fix: SELECT * FROM my_schema.existing_table;
• Query runs forever
• Your warehouse may be stopped or scaling. Check the warehouse status. Also, ensure you've applied filters (WHERE) to reduce data scanning.
• Example: SELECT * FROM huge_table; → Slow. Fix: SELECT * FROM huge_table WHERE dt = '2024-01-01';
• Python code that works in a notebook fails in SQL
• Databricks SQL is pure SQL; you cannot use Python functions. Convert any custom logic to SQL expressions or use a view for complex logic.
• Example: df.filter(...) → not available. Use WHERE instead.
• Null handling differs: SQL uses NULL and IS NULL; not None or == None. Use COALESCE for defaults.

What you learned & what's next

You've just unlocked a superpower: the ability to query tables directly with Databricks SQL. You can now:

• Explain the core idea behind using Databricks SQL to query tables — it's a serverless SQL engine built on the Lakehouse.
• Complete practical exercises — you created a warehouse, ran SELECT queries, and used GROUP BY for analytics.

This lesson marked the 8th step in your Databricks journey. Next, you'll dive into building dashboards and creating alerts to share your insights visually and in real-time. The Lakehouse awaits — keep querying!