Databricks SQL Endpoints for BI

You've built a beautiful Lakehouse with Delta Lake, and your Spark jobs run flawlessly. But when your BI team asks for dashboards, the answer isn't "connect to the cluster" — it's a messy world of JDBC URLs, resource contention, and performance mysteries. The pain is real: analytics queries slow to a crawl, costs balloon, and your data engineers become the bottleneck for every dashboard refresh. This lesson solves that problem by teaching you how to use Databricks SQL endpoints (now called SQL warehouses) as the dedicated, high-performance bridge between your Lakehouse and your BI tools. You'll learn to create, configure, and connect these endpoints, and you'll walk away with a practical recipe for giving your analysts a fast, reliable, and governed path to your data.

The problem this lesson solves

Your BI team needs to query data in Databricks. You have two obvious options: let them run queries on an interactive cluster, or export data to a separate warehouse. Both are painful.

• Interactive clusters are built for engineering, not for BI. They're ephemeral by design — they auto-terminate when idle, causing connection failures and re-start delays. They also share resources with your ETL workloads, so a heavy Spark job can starve a simple SELECT query, and a user running a massive aggregation can slow down production pipelines.
• Exporting data means duplicating data, creating stale copies, and managing another system. You lose the freshness and governance of the Lakehouse, and you double your infrastructure costs.

This is the exact pain point that Databricks SQL endpoints (formerly known as SQL warehouses) were designed to solve. They are a compute resource specifically tuned for SQL and BI workloads, giving you predictable performance, elastic scalability, and a clean separation from your ETL jobs.

This lesson is step 22 in your Databricks learning path. By the end, you'll know how to create, configure, and connect a SQL endpoint to a BI tool, and you'll understand the mental model that makes it work.

Core concept / mental model

Think of a SQL endpoint as a dedicated bridge between your BI tool and your Delta Lake. It's not a database itself — it's a compute service that runs SQL queries against data stored in your Lakehouse. The BI tool sends a query over JDBC/ODBC, the endpoint parses and executes it using a distributed SQL engine (Photon), and returns the results.

The key idea is separation of concerns: you have one copy of data in Delta Lake, and you can have multiple SQL endpoints serving different BI workloads. This is analogous to a restaurant: the kitchen (your data) is the single source of truth, and the waiters (SQL endpoints) are responsible for taking orders (queries) and delivering food (results). If you have a busy tables section, you add more waiters (scale out), but the kitchen doesn't change.

Key characteristics:

• Elastic and serverless — endpoints scale up and down automatically based on the load (number of concurrent queries), so you don't have to manage cluster sizes.
• Performance-optimized — they use Photon, a native C++ engine, for fast SQL execution on Delta Lake.
• Secure and governed — you can control who can access which endpoints via access controls, and you can use query history to monitor usage.
• BI-friendly — they are designed to handle many concurrent queries and are compatible with the Simba ODBC/JDBC drivers.

Key terminology

How it works step by step

To use a SQL endpoint for BI, you follow this logical sequence:

1. Create a SQL endpoint in the Databricks workspace. You define the cluster size (T-shirt size), auto-scaling limits, and idle time.
2. Configure access — specify which users or service principals can use the endpoint. You can also set query governance rules, like data access policies.
3. Connect your BI tool — using the JDBC/ODBC URL and credentials provided by Databricks. You typically grab the connection details from the endpoint's UI.
4. Run queries and build dashboards — your BI tool sends queries to the endpoint, which executes them against Delta tables.
5. Monitor and tune — use the Query History and Metrics tabs to identify slow queries, and adjust the endpoint sizing or add auto-scaling as needed.

The endpoint is not a data warehouse in the traditional sense; it doesn't store data. It's compute that reads from your Lakehouse. This means you get live, fresh data without any ETL duplication.

Under the hood

When a query hits the endpoint, the Databricks SQL engine uses Photon to optimize and execute the query. It leverages the columnar format of Delta Lake for efficient scans. It also uses a cost-based optimizer to choose the best execution plan. The endpoint can scale out to handle many concurrent queries by adding more clusters (compute nodes) as needed.

Hands-on walkthrough

In this walkthrough, you'll create a SQL endpoint, run a few queries, and then connect to it from a Python script using the Databricks SQL Connector for Python. This mirrors what a BI tool would do.

Prerequisites

• A Databricks workspace with Unity Catalog or the old Hive metastore (any mode works).
• A Delta table to query. We'll assume a table named sales in schema default.
• The Databricks SQL Connector Python package installed: pip install databricks-sql-connector.

Step 1: Create a SQL endpoint

1. In your Databricks workspace, click SQL in the sidebar, then SQL Warehouses.
2. Click + Create SQL Warehouse.
3. Give it a name, e.g., BI_Endpoint.
4. Under Cluster size, pick Small (2X-Small) for this demo.
5. Set Auto stop to 10 minutes (so it shuts down when idle).
6. Leave Scaling range at default (1-1 for Small, so no auto-scaling in this demo).
7. Click Create.

The endpoint will start provisioning. It takes a minute or two.

Step 2: Get connection details

In the SQL Warehouses list, click on your warehouse name, and then click Connection details. You'll need: - Server hostname (e.g., xxx.cloud.databricks.com) - HTTP path (e.g., /sql/1.0/warehouses/abc123) - JDBC URL (sometimes not shown; you can construct it)

You'll also need a personal access token (PAT) from your workspace. Create one under User Settings → Developer → Access Tokens.

Step 3: Run queries using Python

Here's a complete Python script that connects to the endpoint and runs a simple aggregation.

from databricks import sql
import os

# Set these via environment variables for security
HOST = os.getenv("DATABRICKS_HOST")  # e.g., "adb-123456.7.databricks.azure.com"
HTTP_PATH = os.getenv("DATABRICKS_HTTP_PATH")  # e.g., "/sql/1.0/warehouses/abc123"
TOKEN = os.getenv("DATABRICKS_TOKEN")

# Connect to the SQL endpoint
connection = sql.connect(
    server_hostname=HOST,
    http_path=HTTP_PATH,
    access_token=TOKEN
)

# Execute a query
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT region, SUM(amount) AS total_sales FROM default.sales GROUP BY region")
        rows = cursor.fetchall()
        for row in rows:
            print(f"{row.region}: ${row.total_sales:,.2f}")
finally:
    connection.close()

Expected output:

West: $1,203,456.78
East: $987,654.32
...

Step 4: Connect a BI tool (example: Power BI / Tableau)

Most BI tools support ODBC/JDBC. Here's the connection pattern (adapted for any tool):

1. Download the Simba Spark ODBC or Databricks JDBC driver from the Databricks website.
2. In your BI tool, create a new data source.
3. Enter the Server hostname (e.g., adb-123.4.databricks.azure.com).
4. Enter the HTTP Path (e.g., /sql/1.0/warehouses/abc123).
5. For authentication, choose Personal Access Token and paste your token.
6. Connect and start building dashboards.

Below is an example of a JDBC URL you might use (though usually the tool's UI builds it for you):

jdbc:databricks://adb-123.4.databricks.azure.com:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/abc123;AuthMech=3;UID=token;PWD=<token>

Step 5: Verify performance

You can check the Query History in the SQL endpoint UI to see the execution time and scanned data. For the demo, a simple query should run in under a second.

Compare options / when to choose what

You have a few options for running BI queries on Databricks:

When to choose what: - Use SQL endpoints for any production BI workload that needs consistent performance and concurrency. - Use interactive clusters for exploratory data science where you need Python/Scala notebooks. - Use serverless SQL if you want zero-config and have unpredictable load. - Avoid exporting data if you can — the Lakehouse gives you live data and cost efficiency.

Variations

• SQL Warehouse vs. SQL Endpoint: They're the same thing. The UI now says "SQL Warehouses" but the API and older documentation still call them "SQL endpoints." Don't get confused.
• Using the Databricks SQL Connector is great for Python; for other languages, use the Simba ODBC/JDBC drivers.
• For dashboards inside Databricks, you can use Databricks SQL Dashboards directly, which don't need an external BI tool.

Troubleshooting & edge cases

Common pitfalls and fixes:

• Connection fails: Most often, you're using the wrong HTTP path or hostname. Double-check the Connection details page. Also, ensure your personal access token is valid and has the right permissions.
• Endpoint is stopped: SQL endpoints auto-stop after the configured idle time. This takes a minute to restart; your BI tool's connection may time out. Set a longer auto-stop for production, and consider enabling serverless for instant restart.
• Query is slow: Check the Query History to see if it's a full table scan. Add predicate pushdown by filtering on partition columns, or use Delta Lake liquid clustering to optimize data layout.
• Concurrency issues: If many users are hitting the endpoint, you might hit the maximum concurrent queries. Increase the max scaling range or choose a larger cluster size.
• Permission errors: Ensure the user/principal has Can Use permission on the SQL endpoint, and also has access to the underlying data.
• ODBC/JDBC driver issues: Make sure you're using the Databricks-provided drivers, as generic Apache Spark drivers may not work with all features.
• Incorrect data types: BI tools and Databricks SQL may have type mapping quirks. Use CAST to ensure a consistent return type.
• Timeout errors in BI tool: Increase the query timeout in your BI tool settings; Databricks may kill long-running queries if they exceed the query execution timeout (default 24 hours, but your BI tool often has a shorter default).

What you learned & what's next

You've now mastered the core concept of using Databricks SQL endpoints for BI. You can:

• Explain what a SQL endpoint is and why it's the right compute for BI workloads.
• Create a SQL endpoint with proper sizing and auto-scaling.
• Connect your BI tool or Python script using JDBC/ODBC and the SQL connector.
• Troubleshoot common connection and performance issues.

This lesson addressed both learning objectives: you understand the mental model and you completed a hands-on exercise.

The next lesson in the track will be Databricks SQL Analytics: Building Dashboards, where you'll use these endpoints to create live dashboards directly in Databricks and learn about query optimization techniques for better performance. By building on your endpoint knowledge, you'll turn raw data into actionable insights.

Keep experimenting: create a second endpoint for a different team to see how separation of concerns works in practice. Happy querying!