How to Connect Azure Databricks to Power BI: A Step-by-Step Guide (With Examples & Best Practices)

Modern data platforms demand both large-scale data processing and powerful visualization. Azure Databricks excels at big data analytics using Apache Spark, while Power BI is one of the most widely used business intelligence tools for reports and dashboards.

By integrating Azure Databricks with Power BI, organizations can transform massive datasets and visualize them in a secure, interactive, and scalable way.

This guide covers:

• Connecting Azure Databricks to Power BI

• Architecture and authentication models

• Import vs DirectQuery selection

• Performance optimization techniques

• Common issues and best practices

High-Level Architecture

+------------------+     +-----------------------+     +------------------+
| Data Sources     | --> | Azure Databricks      | --> | Power BI         |
| (ADLS, SQL, IoT) |     | (Spark / Delta Lake)  |     | Reports & Dash   |
+------------------+     +-----------------------+     +------------------+
                             |
                             |
                    Databricks SQL Warehouse

Data Flow Explanation

1. Raw data is ingested into Azure Databricks

2. Data is transformed using Spark and stored as Delta tables

3. Power BI connects using Databricks SQL Warehouse or cluster

4. Business users consume insights via dashboards

Why Integrate Azure Databricks with Power BI?

• Efficient handling of large-scale data processing

• Self-service BI on top of big data

• Reduced data duplication

• Support for near real-time reporting

• Combination of advanced analytics with rich visualization

Prerequisites

Azure Databricks

• Active Azure Databricks workspace

• Databricks SQL Warehouse (recommended) or running cluster

• Delta tables or views created

• Proper workspace access permissions

Power BI

• Power BI Desktop (latest version)

• Power BI Pro or Premium license (for sharing & refresh)

• Network access to Azure Databricks

Authentication Options

Option 1: Azure Active Directory (Recommended)

• Enterprise-grade security

• Single Sign-On (SSO)

• No token management required

Option 2: Personal Access Token (PAT)

• Easier setup

• Common in development and testing

• Tokens must be rotated periodically

Step-by-Step: Connecting Azure Databricks to Power BI

Step 1: Prepare Data in Azure Databricks

Create an aggregated Delta table or view:

CREATE TABLE sales_summary
USING DELTA
AS
SELECT
  country,
  year,
  SUM(revenue) AS total_revenue
FROM sales_data
GROUP BY country, year;

Best Practice: Use aggregated BI-friendly tables instead of raw data.

Step 2: Get Connection Details from Databricks

From Databricks SQL Warehouse, collect:

• Server Hostname

• HTTP Path

• Access Token (if using PAT)

Location: Databricks Workspace → SQL Warehouses → Connection Details

Step 3: Open Power BI Desktop

1. Open Power BI Desktop

2. Click Get Data

3. Select Azure Databricks

4. Click Connect

Step 4: Enter Connection Details

Provide the following:

• Server Hostname

• HTTP Path

• Authentication method:

  • Azure Active Directory, or

  • Access Token

Click OK to connect.

Step 5: Select Tables or Use SQL

You can either:

• Select tables/views directly, or

• Write a custom SQL query:

SELECT country, total_revenue
FROM sales_summary
WHERE year = 2025;

Import Mode vs DirectQuery Mode

Recommendation:

• Use Import mode for dashboards

• Use DirectQuery for large or frequently changing datasets

Using Databricks SQL Warehouses (Best Practice)

Why SQL Warehouses Are Better

• Optimized for BI workloads

• Auto-scale and auto-stop

• Lower cost than interactive clusters

• Better concurrency for multiple users

Golden Rule: Always use Databricks SQL Warehouses for Power BI reporting.

Performance Optimization Tips

In Azure Databricks

• Store data in Delta Lake

• Optimize tables:

OPTIMIZE sales_summary
ZORDER BY (country, year);

• Avoid SELECT *

• Use partitions wisely

In Power BI

• Reduce number of visuals per page

• Avoid complex DAX with DirectQuery

• Push transformations to Databricks

Security and Governance

• Use Azure AD authentication

• Enable Unity Catalog

• Apply:

  • Row-Level Security (RLS)

  • Column-Level Security (CLS)

• Restrict cluster and SQL Warehouse access

• Never hardcode access tokens

Scheduling Refresh in Power BI Service

1. Publish report to Power BI Service

2. Open Dataset Settings

3. Configure credentials

4. Set Scheduled Refresh

5. Ensure SQL Warehouse auto-start is enabled

Common Issues and Troubleshooting

Authentication Failed

• Token expired

• Missing SQL Warehouse permissions

Slow Performance

• Using interactive cluster instead of SQL Warehouse

• Too many visuals in DirectQuery

Timeout Errors

• Reduce dataset size

• Increase Power BI timeout

• Pre-aggregate data

Real-World Use Cases

• Enterprise data lake reporting

• Financial and sales dashboards

• Machine learning model outputs

• IoT analytics visualization

Frequently Asked Questions (FAQs)

Q1. Can Power BI connect to Databricks without SQL Warehouse?
Yes, but SQL Warehouses are strongly recommended for BI workloads.

Q2. Is DirectQuery real-time?
It is near real-time and depends on query complexity and cluster size.

Q3. Is Databricks more expensive than Azure SQL?
For large analytics workloads, Databricks is often more cost-efficient.

Q4. Can I use Power BI Service without Power BI Desktop?
Initial dataset creation requires Power BI Desktop.