05.1 Databricks SQL

Phase 5 — SQL in Databricks

This chapter moves from distributed PySpark programming into the SQL-first experience used by analysts, analytics engineers, BI developers, data engineers, and enterprise data architects.

---

1. What is Databricks SQL?

Databricks SQL (DBSQL) is the SQL/data-warehousing experience of the Databricks platform. It provides SQL-based querying and analytics directly over lakehouse data instead of requiring the organization to copy data into a separate warehouse.

A useful mental model is:

                    Databricks Lakehouse
                           |
          +----------------+----------------+
          |                |                |
      Delta tables     Unity Catalog    Cloud storage
          |                |                |
          +----------------+----------------+
                           |
                    SQL Warehouse
                           |
             +-------------+-------------+
             |             |             |
        SQL Editor     Dashboards      Jobs
             |
          Analysts / Engineers / BI

Databricks describes SQL as a cloud data warehouse built on lakehouse architecture. It runs directly on the data lake and supports ANSI SQL plus Databricks/Delta extensions.

Official reference: Databricks SQL overview

---

2. Databricks SQL is more than a query editor

A beginner may think:

"Databricks SQL is just a place where I write SELECT statements."

That is too narrow.

The SQL platform includes:

• SQL Editor
• SQL warehouses
• Saved queries
• Query history
• Query profiles
• Visualizations
• AI/BI dashboards
• Alerts
• Jobs
• Metric views
• Streaming tables
• Materialized views
• REST/API integrations
• Unity Catalog governed data access

The architectural view is:

                         Databricks SQL
                               |
       +-----------+-----------+-----------+-----------+
       |           |           |           |           |
     Query      Compute     BI/AI       ETL        Automation
     Editor     Warehouses  Dashboards   Assets       Jobs/API
       |           |           |           |           |
       +-----------+-----------+-----------+-----------+
                               |
                         Unity Catalog
                               |
                         Lakehouse data

This is why DBSQL belongs in an enterprise data architecture discussion, not only in an analyst tutorial.

---

3. Databricks SQL vs Spark SQL

These names are related but are not identical.

Spark SQL

Spark SQL is the SQL interface of Apache Spark.

You can execute:

df = spark.sql("""
    SELECT customer_id, SUM(amount) AS revenue
    FROM sales
    GROUP BY customer_id
""")

The query executes through Spark compute associated with the application.

Databricks SQL

Databricks SQL is the Databricks SQL/data warehousing experience, normally backed by a SQL warehouse.

Spark SQL
   |
Notebook / Spark application
   |
Databricks Runtime / Spark compute

Databricks SQL
   |
SQL Editor / Dashboard / SQL Job
   |
SQL Warehouse

Architect rule

Do not ask:

"Which one is better?"

Ask:

"What workload, user persona, execution environment, governance model and operational requirements am I serving?"

---

4. SQL-first architecture

A typical enterprise analytics flow looks like:

Operational Systems
       |
       v
   Ingestion
       |
       v
    BRONZE
       |
       v
    SILVER
       |
       v
     GOLD
       |
       +--------------------+
       |                    |
       v                    v
 Databricks SQL        PySpark / ML
       |
       v
 BI / Analytics / APIs

DBSQL does not require a separate physical copy of every Gold table.

The SQL warehouse can query governed lakehouse tables directly.

This is one of the fundamental differences between a traditional architecture and a lakehouse architecture.

---

5. The SQL warehouse

A SQL warehouse is the compute resource used to execute SQL workloads in Databricks SQL.

Think of it as:

SQL Query
   |
   v
SQL Warehouse
   |
   +--> Parse
   +--> Analyze
   +--> Optimize
   +--> Execute
   |
   v
Delta / Lakehouse data

Current Databricks documentation describes three SQL warehouse types:

• Serverless
• Pro
• Classic

Databricks recommends serverless SQL warehouses where available.

Reference: Connect to a SQL warehouse

---

6. Why SQL warehouses are separate from normal compute

This distinction is important for architecture.

General-purpose / job compute

Often used for:

• PySpark
• Python
• Spark applications
• complex data engineering
• custom libraries
• multi-language notebooks

SQL warehouse

Designed for:

• SQL analytics
• BI
• dashboards
• interactive SQL
• SQL jobs
• reporting
• SQL-based ETL

Conceptually:

                    Databricks Compute
                           |
             +-------------+-------------+
             |                           |
       General Compute              SQL Warehouse
             |                           |
      PySpark / Python              SQL workloads
      Spark / notebooks             BI / dashboards
      engineering jobs              SQL editor

Do not automatically put every workload on the largest cluster.

Choose compute based on workload characteristics.

---

7. SQL warehouse types

Serverless

Best default for many SQL workloads when supported.

Typical characteristics:

• Fast startup
• Managed infrastructure
• Automatic scaling
• Reduced operational management
• Strong fit for BI and exploratory SQL

Pro

Useful where serverless is unavailable or where additional networking/customization requirements matter.

Classic

Legacy/entry-level option with more infrastructure management.

Architect decision

Requirement	Typical choice
BI and analytics	Serverless
Interactive SQL	Serverless
Elastic workload	Serverless
Custom networking requirement	Pro
Serverless unavailable	Pro / Classic
Legacy constraints	Classic

Always verify current feature availability in your target cloud and region.

Reference: Choose compute

---

8. SQL Editor

The SQL Editor is the primary browser-based SQL authoring interface.

It provides capabilities such as:

• Query authoring
• SQL autocomplete
• Catalog/schema browsing
• Query execution
• Result exploration
• Visualization
• Query sharing
• Collaboration
• Parameters
• Query history/versioning

The current editor can run single or multi-statement SQL.

Example:

SELECT
    customer_id,
    SUM(net_amount) AS revenue
FROM
    main.sales.orders
WHERE
    order_status = 'COMPLETED'
GROUP BY
    customer_id
ORDER BY
    revenue DESC;

Reference: Databricks SQL Editor

---

9. SQL Editor → Warehouse → Data

The execution path should become second nature.

User
 |
 | SQL statement
 v
SQL Editor
 |
 | submit
 v
SQL Warehouse
 |
 +--> Catalog resolution
 |
 +--> Authorization
 |
 +--> SQL parsing
 |
 +--> Logical analysis
 |
 +--> Optimization
 |
 +--> Physical execution
 |
 v
Unity Catalog governed data
 |
 v
Delta / lakehouse storage

This is the SQL equivalent of the Spark execution mental model learned earlier.

---

10. Unity Catalog is part of the SQL architecture

In a Unity Catalog-enabled workspace, SQL warehouses use Unity Catalog to manage access to data sources.

That means the SQL layer should be designed around governed object names such as:

catalog.schema.table

Example:

SELECT *
FROM enterprise_sales.gold.customer_revenue;

Instead of designing an enterprise platform around unmanaged storage paths, prefer governed catalog objects wherever appropriate.

Reference: Query data in Databricks

---

11. Three-level namespace

The standard governed namespace is:

catalog
   |
   +-- schema
          |
          +-- table
          +-- view
          +-- volume

Example:

SELECT *
FROM prod_sales.gold.daily_revenue;

Where:

• prod_sales = catalog
• gold = schema
• daily_revenue = table

This naming model becomes especially important in multi-environment enterprise architectures.

---

12. Query lifecycle

When you execute SQL, think in layers.

Step 1 — Parse

SQL text is converted into an internal representation.

Step 2 — Analyze

Databricks resolves:

• tables
• columns
• functions
• data types
• permissions
• object references

Step 3 — Optimize

The engine determines an efficient execution strategy.

Step 4 — Execute

The warehouse executes the physical plan.

Step 5 — Return results

Results are presented to the SQL Editor, dashboard, application or downstream process.

SQL text
  |
  v
Parser
  |
  v
Analyzer
  |
  v
Optimizer
  |
  v
Physical plan
  |
  v
Execution
  |
  v
Result

This lifecycle becomes critical when we study SQL optimization later.

---

13. Query vs table vs view

These are different architectural objects.

Table

Stores durable data.

SELECT *
FROM prod_sales.gold.orders;

View

Stores a logical query definition rather than an independent copy of the underlying result.

CREATE VIEW prod_sales.gold.completed_orders AS
SELECT *
FROM prod_sales.silver.orders
WHERE status = 'COMPLETED';

Query

A reusable SQL asset representing a SQL statement.

A query can be executed interactively or incorporated into workflows depending on the Databricks feature being used.

Architectural distinction

Table  -> durable data
View   -> reusable logical abstraction
Query  -> executable SQL asset

Do not use a view as a substitute for a physical serving table when the workload requires precomputation.

That decision comes later when we study views and materialized views.

---

14. SQL and Delta Lake

Databricks SQL works naturally with Delta Lake.

Example:

SELECT
    order_date,
    SUM(net_amount) AS revenue
FROM prod_sales.gold.orders
GROUP BY order_date
ORDER BY order_date;

The underlying table may be a Delta table stored in cloud object storage.

Therefore:

SQL
 |
 v
SQL Warehouse
 |
 v
Delta table
 |
 v
Cloud object storage

This is one of the key lakehouse principles:

SQL is separated from the physical storage location while remaining governed by the platform's metadata and access model.

---

15. SQL does not mean "move data into a warehouse"

Traditional architecture often looks like:

S3 / Data Lake
      |
      v
ETL / Copy
      |
      v
Enterprise Data Warehouse
      |
      v
BI

A lakehouse architecture can look like:

Cloud Storage
      |
      v
Delta Lake
      |
      v
Databricks SQL
      |
      v
BI

The physical architecture can therefore be simpler.

The important point is not "warehouse versus lake."

The important point is:

Where is the authoritative data, who governs it, how is it served, and what compute is optimized for the workload?

---

16. Querying with SQL

Start with simple exploration:

SELECT *
FROM main.sales.orders
LIMIT 100;

Projection:

SELECT
    order_id,
    customer_id,
    order_date,
    net_amount
FROM main.sales.orders;

Filtering:

SELECT *
FROM main.sales.orders
WHERE order_status = 'COMPLETED'
  AND order_date >= DATE '2026-01-01';

Aggregation:

SELECT
    customer_id,
    SUM(net_amount) AS revenue,
    COUNT(*) AS order_count
FROM main.sales.orders
GROUP BY customer_id;

The syntax is familiar to SQL developers.

The execution architecture is what you must learn.

---

17. SQL joins

Example:

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    o.net_amount
FROM main.sales.orders o
LEFT JOIN main.sales.customers c
    ON o.customer_id = c.customer_id;

The same distributed execution concerns from PySpark still apply:

• Join cardinality
• Broadcast opportunities
• Shuffle
• Data skew
• Filtering before joins
• Projection pruning
• Statistics
• Physical join strategy

So:

SQL does not remove distributed-computing problems. It gives you a declarative way to express the workload.

This is why the Spark fundamentals learned earlier remain relevant.

---

18. SQL window functions

Example:

SELECT
    customer_id,
    order_date,
    net_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS order_rank
FROM main.sales.orders;

Window functions can be expensive because the engine may need to redistribute and order data.

Later in this course we will study:

• Window execution
• Sorts
• Partitioning
• Spill
• Query plans
• Performance tuning

---

19. SQL parameters

Reusable queries often need runtime parameters.

For example:

SELECT *
FROM main.sales.orders
WHERE order_date >= :start_date
  AND order_date < :end_date;

Parameters are useful for:

• reusable analytics
• dashboards
• scheduled jobs
• operational reporting
• environment-specific execution

Avoid constructing SQL by concatenating untrusted strings when a parameterized approach is available.

---

20. Query history

Query History is an operational tool, not merely a convenience.

It can help you investigate:

• Query duration
• Resource usage
• Execution behavior
• Recent failures
• Repeated slow queries
• Workload patterns

A production workflow should look like:

User reports slow query
        |
        v
Query History
        |
        v
Identify query
        |
        v
Query Profile
        |
        v
Find expensive operator
        |
        v
Tune SQL / data layout / compute

Reference: Databricks SQL concepts

---

21. Query Profile

Query Profile provides a detailed execution view.

Use it to answer:

• Which operator consumes most time?
• Where is data being shuffled?
• Is a join expensive?
• Is an aggregation expensive?
• Is there an unexpectedly large intermediate result?
• Is the query scanning more data than expected?

This is the SQL equivalent of the Spark UI mindset:

Do not optimize from intuition alone. Inspect the execution evidence.

---

22. SQL performance mental model

Use this sequence:

READ LESS
   ↓
FILTER EARLY
   ↓
SELECT FEWER COLUMNS
   ↓
REDUCE ROWS
   ↓
REDUCE SHUFFLE
   ↓
OPTIMIZE JOINS
   ↓
OPTIMIZE DATA LAYOUT
   ↓
CHOOSE APPROPRIATE COMPUTE
   ↓
MEASURE AGAIN

This should feel familiar from PySpark performance engineering.

The difference is that SQL lets the optimizer determine much of the physical execution strategy.

---

23. Example: inefficient SQL

Suppose:

SELECT *
FROM prod_sales.silver.orders o
JOIN prod_sales.silver.customers c
  ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01';

Potential improvements include:

Project only required columns

SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.net_amount,
    c.customer_segment
FROM ...

Filter as early as possible

FROM (
    SELECT *
    FROM prod_sales.silver.orders
    WHERE order_date >= DATE '2026-01-01'
) o
JOIN ...

The optimizer may already perform predicate and projection pushdown, but you should still understand the logical workload and verify the physical plan.

---

24. SQL and BI

A common enterprise pattern is:

                   Gold Delta Tables
                          |
                    SQL Warehouse
                          |
          +---------------+---------------+
          |               |               |
       Tableau         Power BI        AI/BI
          |               |               |
          +---------------+---------------+
                          |
                      Business Users

Databricks SQL supports third-party BI connectivity through standard connectivity options and integrations.

For an architect, the important question is:

Should BI tools query the lakehouse directly, or should we introduce a serving/semantic layer?

The answer depends on:

• concurrency
• latency
• semantic consistency
• workload isolation
• governance
• cost
• data freshness

---

25. SQL vs PySpark decision matrix

Requirement	Prefer
Analyst exploration	SQL
BI/reporting	SQL
Standard relational transformations	SQL
Complex Python libraries	PySpark/Python
Custom distributed transformations	PySpark
Data engineering with reusable Python modules	PySpark
SQL-heavy ETL	SQL
Mixed Python + SQL workflow	Notebook
ML feature/model workflows	Python/PySpark
Interactive dashboard	SQL warehouse

This is not a hard rule.

A mature Databricks platform normally uses both SQL and Python/PySpark.

---

26. SAP/BW architect translation

If you come from SAP BW/4HANA, map the concepts carefully.

SAP analytics concept	Databricks mental model
BW query	SQL query / BI semantic asset
ADSO	Delta table
CompositeProvider	SQL view / modeled serving layer
Transformation	SQL / PySpark transformation
Process chain	Job / workflow / pipeline
HANA calculation logic	SQL / Spark / optimized data model
Authorization	Unity Catalog privileges/policies
InfoProvider	Governed table/model
AFO reporting	BI tool / SQL-driven analytics
HANA SQL	Databricks SQL

This is a conceptual translation, not a one-to-one product mapping.

The biggest shift is:

Databricks separates storage, compute, governance and transformation more explicitly than the classic BW-centric architecture.

---

27. Hands-on lab — first Databricks SQL query

Step 1 — Open SQL Editor

Open the Databricks SQL Editor.

Step 2 — Select a SQL warehouse

Choose an available SQL warehouse.

You generally need permission to use the warehouse before executing queries.

Step 3 — Identify a governed table

Browse Catalog Explorer or the SQL Editor data browser.

Step 4 — Run:

SELECT *
FROM <catalog>.<schema>.<table>
LIMIT 100;

Step 5 — Inspect the result

Identify:

• Row count
• Columns
• Data types
• Nulls
• Business keys
• Date fields
• Measures

Step 6 — Run an aggregation

SELECT
    COUNT(*) AS rows,
    COUNT(DISTINCT <business_key>) AS distinct_keys
FROM <catalog>.<schema>.<table>;

Step 7 — Inspect query history

Find the query and inspect its execution information.

Acceptance criteria

You should be able to explain:

1. Which SQL warehouse executed the query?
2. Which catalog/schema/table was accessed?
3. How was access governed?
4. What was the query's execution path?
5. Where would you investigate if the query were slow?

---

28. Hands-on lab — build a Gold revenue query

Assume:

prod_sales.silver.orders
prod_sales.silver.customers

Build:

SELECT
    o.order_date,
    c.customer_segment,
    SUM(o.net_amount) AS revenue,
    COUNT(*) AS order_count
FROM prod_sales.silver.orders o
JOIN prod_sales.silver.customers c
    ON o.customer_id = c.customer_id
WHERE o.order_status = 'COMPLETED'
GROUP BY
    o.order_date,
    c.customer_segment
ORDER BY
    o.order_date,
    c.customer_segment;

Then answer:

• What is the output grain?
• What is the join cardinality?
• Where can duplicates appear?
• Which columns are required?
• What happens if the customer dimension has duplicate keys?
• Which operation is likely to require data movement?
• How would you investigate performance?

---

29. Production SQL checklist

Before promoting a SQL workload:

Correctness

• Output grain documented
• Join cardinality understood
• Null behavior understood
• Duplicate behavior tested
• Time-zone behavior validated
• Business definitions validated

Governance

• Unity Catalog objects used
• Least-privilege access
• Sensitive columns protected
• Ownership defined
• Lineage available

Performance

• Only required columns selected
• Filters applied appropriately
• Join strategy understood
• Data layout appropriate
• Query Profile reviewed
• Concurrency tested

Operations

• Warehouse sizing reviewed
• Query timeout considered
• Failure behavior defined
• Monitoring available
• Cost reviewed

---

30. Common mistakes

Mistake 1 — Treating SQL as single-machine processing

Databricks SQL is still distributed execution.

Mistake 2 — Using SELECT *

It can increase data scanning and downstream processing.

Mistake 3 — Ignoring join cardinality

A many-to-many join can multiply rows dramatically.

Mistake 4 — Assuming bigger warehouse = better SQL

A larger warehouse can hide an inefficient query and increase cost.

Mistake 5 — Ignoring data layout

SQL performance depends on how efficiently the underlying data can be accessed.

Mistake 6 — Ignoring governance

Direct storage access patterns can bypass the architectural benefits of Unity Catalog.

Mistake 7 — Treating Query History as optional

Production performance engineering requires evidence.

---

31. Architect mental model

Think of Databricks SQL as four layers:

┌─────────────────────────────────────────────┐
│                 CONSUMPTION                 │
│ BI • Dashboards • Analysts • Applications  │
├─────────────────────────────────────────────┤
│                    SQL                      │
│ Queries • Views • Metrics • ETL • Alerts   │
├─────────────────────────────────────────────┤
│                  COMPUTE                    │
│        Serverless / Pro / Classic          │
├─────────────────────────────────────────────┤
│                LAKEHOUSE                    │
│ Unity Catalog • Delta • Cloud Storage      │
└─────────────────────────────────────────────┘

The architect chooses:

• where data lives
• how it is governed
• how it is modeled
• which compute executes it
• who consumes it
• how performance is measured
• how cost is controlled

---

32. Interview questions

Q1. What is Databricks SQL?

A SQL/data-warehousing experience on the Databricks lakehouse that provides SQL querying, analytics, BI, and SQL-based data workflows over governed lakehouse data.

Q2. What is a SQL warehouse?

A compute resource optimized for running SQL workloads in Databricks SQL.

Q3. Databricks SQL vs Spark SQL?

Spark SQL is the SQL interface of Apache Spark. Databricks SQL is the broader Databricks SQL/data warehousing experience, typically using SQL warehouses and integrated analytics capabilities.

Q4. Why use a SQL warehouse instead of all-purpose compute?

It provides SQL-optimized compute and operational capabilities suited to analytics, BI, and SQL workloads.

Q5. What is the role of Unity Catalog?

It provides centralized governance, access control, auditing, lineage and data discovery across governed Databricks data.

Q6. How do you troubleshoot a slow SQL query?

Start with Query History, open the query profile, identify the expensive operators, inspect joins/shuffles/data volume/layout and then apply targeted optimization.

Q7. Should every Gold table be exposed directly to BI?

Not necessarily. The serving model should consider semantics, concurrency, latency, governance and workload isolation.

Q8. Is Databricks SQL a traditional data warehouse?

It provides data-warehousing capabilities, but operates directly on the lakehouse architecture rather than requiring a separate copied warehouse storage layer.

---

33. Production architecture example

SAP / CRM / ERP / APIs
          |
          v
      Ingestion
          |
          v
    Bronze Delta
          |
          v
    Silver Delta
          |
          v
     Gold Delta
          |
          +--------------------+
          |                    |
          v                    v
   SQL Warehouse          PySpark / ML
          |
          +----------------+
          |                |
          v                v
     BI Platforms      Applications
          |
          v
      Business

Govern the entire platform through Unity Catalog.

Separate workloads through appropriate compute.

Measure SQL through Query History and Query Profile.

This is the beginning of a production lakehouse serving architecture.

---

34. Key takeaways

Remember these ten points:

1. Databricks SQL is the SQL/data-warehousing layer of the lakehouse.
2. SQL warehouses are the primary compute resource for DBSQL.
3. Serverless is the recommended default where available.
4. SQL execution remains distributed.
5. Unity Catalog governs enterprise data access.
6. Delta Lake provides the underlying transactional data layer.
7. SQL and PySpark solve complementary problems.
8. Query History and Query Profile are essential production tools.
9. Query performance depends on data volume, joins, layout, compute and concurrency.
10. Architecture decisions should optimize business performance, governance and cost together.

---

Official references

• Databricks SQL overview
• Databricks SQL concepts
• Get started with Databricks SQL
• Query data
• SQL Editor
• SQL warehouses
• Choose compute

Next

05.2 SQL Warehouses — deep dive into warehouse types, serverless architecture, sizing, scaling, concurrency, workload isolation, cost, configuration and production operating patterns.