6 SQL Features That Make Databricks Feel Like a Real Database

Multi-statement transactions, stored procedures, collations, spatial SQL, temp tables, and recursive CTEs — Databricks just closed the gap

Your Databricks workspace just became a lot more familiar.

If you've ever worked with PostgreSQL, SQL Server, or Oracle, you know the comfort of features like transactions, stored procedures, and recursive queries. For years, Databricks didn't have them. It was a powerful engine for big data, but it didn't feel like a database.

That changed quietly. Six features have landed that close the gap between Databricks and traditional databases. And for data engineers, this changes how you design pipelines.

Let's walk through each one.

1. Multi-Statement Transactions

What it is: BEGIN, COMMIT, and ROLLBACK, the transaction primitives every database developer expects.

Why it matters: Before this, each SQL statement in Databricks was its own atomic unit. If you needed to update three tables together, all or nothing, you had to get creative with staging tables and manual rollback logic.

Now, with Databricks Runtime 18.0+ and managed Unity Catalog tables, you can wrap multiple operations in a single transaction.

BEGIN;

-- Update the dimension table
UPDATE gold.customers
SET status = 'churned'
WHERE last_order_date < '2025-01-01';

-- Log the change
INSERT INTO gold.customer_status_log (customer_id, old_status, new_status, changed_at)
SELECT customer_id, 'active', 'churned', current_timestamp()
FROM gold.customers
WHERE last_order_date < '2025-01-01';

-- Validate before committing
SELECT CASE
  WHEN COUNT(*) > 10000 THEN RAISE_ERROR('Too many customers affected, aborting')
END
FROM gold.customers WHERE status = 'churned';

COMMIT;

If anything fails, nothing persists. That's the ACID guarantee you already know from Delta Lake Architecture, now extended to multi-statement workflows.

The foundation hasn't changed. Delta Lake gave you ACID at the table level. Multi-statement transactions give you ACID across tables.

This pairs naturally with Data Quality Engineering. You can validate data within the transaction and roll back if something looks wrong, before bad data reaches your Gold layer.

flowchart LR
  A[BEGIN] --> B[UPDATE Table 1]
  B --> C[INSERT Table 2]
  C --> D{Validation Check}
  D -->|Pass| E[COMMIT]
  D -->|Fail| F[ROLLBACK]
  style E fill:#10b981,color:#fff
  style F fill:#ef4444,color:#fff

2. SQL Stored Procedures and SQL Scripting

What it is: Reusable SQL programs with variables, control flow (IF, WHILE, FOR), error handling, and parameterized logic.

Why it matters: Until now, if you wanted reusable logic in Databricks, you wrote Python UDFs or notebooks. Stored procedures let you encapsulate complex SQL logic on the server side.

CREATE OR REPLACE PROCEDURE silver.refresh_daily_metrics(report_date DATE)
LANGUAGE SQL
AS
BEGIN
  -- Clear existing data for the date
  DELETE FROM silver.daily_metrics WHERE metric_date = report_date;

  -- Rebuild from bronze
  INSERT INTO silver.daily_metrics
  SELECT
    report_date AS metric_date,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_revenue
  FROM bronze.orders
  WHERE order_date = report_date
  GROUP BY product_id;

  -- Log completion
  INSERT INTO silver.pipeline_log (step, completed_at)
  VALUES ('daily_metrics_refresh', current_timestamp());
END;

Call it simply:

CALL silver.refresh_daily_metrics('2026-03-21');

If you've studied Custom Function Development, you know when Python UDFs make sense, row-level transformations, complex parsing, ML inference. Stored procedures fill a different gap: orchestrating multi-step SQL workflows without leaving SQL.

They also complement Production Orchestration. A Databricks Jobs & Pipelines task can call a stored procedure as a single task, keeping your pipeline clean and auditable.

3. Collations

What it is: Rules that determine how strings are compared and sorted, case sensitivity, accent sensitivity, and locale-aware ordering.

Why it matters: Have you ever written LOWER(name) = LOWER(input) to do a case-insensitive match? That's a workaround. Collations solve it properly.

-- Create a table with case-insensitive columns
CREATE TABLE silver.customers (
  id BIGINT,
  name STRING COLLATE 'UNICODE_CI',  -- CI = Case Insensitive
  email STRING COLLATE 'UNICODE_CI'
);

-- This now works without LOWER()
SELECT * FROM silver.customers
WHERE name = 'john smith';  -- Matches 'John Smith', 'JOHN SMITH', etc.

This is a data quality feature disguised as a string feature. If you've worked through Data Transformations, you know how much pipeline code goes into normalizing strings. Collations push that logic into the schema itself.

It's also relevant to Data Quality Engineering. Fewer manual transformations means fewer places for bugs to hide.

4. Spatial SQL

What it is: Native geometry types and spatial functions, ST_POINT, ST_DISTANCE, ST_CONTAINS, ST_WITHIN, and more.

Why it matters: Location data is everywhere. Store locations, delivery routes, customer addresses, sensor positions. Previously, you needed external libraries like GeoSpark or H3 to do spatial analysis. Now it's built in.

-- Find all stores within 10km of a customer
SELECT
  s.store_name,
  ST_DISTANCE(
    ST_POINT(s.longitude, s.latitude),
    ST_POINT(-73.9857, 40.7484)  -- Customer location (NYC)
  ) / 1000 AS distance_km
FROM gold.stores s
WHERE ST_DISTANCE(
  ST_POINT(s.longitude, s.latitude),
  ST_POINT(-73.9857, 40.7484)
) < 10000
ORDER BY distance_km;

Spatial joins are still joins. The fundamentals from Joins and Aggregations apply directly, you're just adding a geometric predicate instead of an equality condition.

And the data has to come from somewhere. Enterprise Data Ingestion covers how to connect the sources that contain this location data in the first place, often using Lakeflow Connect for automated ingestion.

5. Temp Tables

What it is: Session-scoped tables that persist data (not just metadata) for the duration of your session.

Why it matters: You already know CREATE OR REPLACE TEMP VIEW from SQL Query Fundamentals. Temp views are aliases, they don't store data, they re-execute the query each time you reference them.

Temp tables are different. They materialize the data once and store it for the session.

-- This materializes the filtered data once
CREATE TEMP TABLE active_customers AS
SELECT * FROM gold.customers
WHERE status = 'active'
  AND last_order_date > '2025-01-01';

-- These queries read from materialized data (fast)
SELECT COUNT(*) FROM active_customers;
SELECT region, COUNT(*) FROM active_customers GROUP BY region;
SELECT AVG(lifetime_value) FROM active_customers;

If you reference the same filtered dataset multiple times in a session, temp tables avoid redundant computation. This connects directly to Performance Optimization, understanding when to materialize intermediate results is a key performance skill.

Temp views are lazy. Temp tables are eager. Know when you need each.

6. Recursive CTEs

What it is: CTEs that reference themselves, enabling hierarchical and graph-like queries.

Why it matters: Org charts. Bill of materials. Category trees. Network paths. Any data with parent-child relationships was painful to query in Databricks. You either wrote iterative Python or used multiple self-joins.

Recursive CTEs solve this elegantly.

-- Find all reports under a manager, recursively
WITH RECURSIVE org_tree AS (
  -- Base case: the manager
  SELECT employee_id, name, manager_id, 1 AS depth
  FROM gold.employees
  WHERE employee_id = 1001

  UNION ALL

  -- Recursive case: their direct reports, and their reports, etc.
  SELECT e.employee_id, e.name, e.manager_id, t.depth + 1
  FROM gold.employees e
  JOIN org_tree t ON e.manager_id = t.employee_id
  WHERE t.depth < 10  -- Safety limit
)
SELECT * FROM org_tree ORDER BY depth, name;

If you've studied Joins and Aggregations, you know self-joins. Recursive CTEs replace the pattern where you'd manually join a table to itself three, four, five times. One query handles arbitrary depth.

This is also powerful for building hierarchical dimensions in your Lakehouse Architecture. Think product categories, geographic hierarchies, or account structures in your Gold layer, often managed via Lakeflow Declarative Pipelines (formerly Delta Live Tables).

Before and After

Here's what changed:

Your Learning Path

Every new feature builds on fundamentals. Here's where to start:

What This Means

Databricks is no longer just a big data engine. It's a database.

And that changes the job description. Data engineers who understand both the distributed computing fundamentals and these database-native patterns will design better pipelines, write cleaner SQL, and build more reliable systems.

The features are new. The fundamentals aren't.

The best time to learn Databricks was yesterday. The second best time is now, because it just got a lot more capable.

Every chapter in BricksNotes teaches the foundation that makes these features powerful. Start with Data Engineering Fundamentals and work through at your own pace. The SQL features will be waiting when you get there.