Author: Jared Champion

Geospatial analytics has become increasingly critical for modern data-driven organizations, from location intelligence and urban planning to supply chain optimization and environmental monitoring. With the release of Databricks Runtime (DBR) 17.1, Databricks has significantly enhanced its geospatial SQL capabilities, introducing native geospatial data types and optimized functions that make spatial analysis more powerful and accessible than ever before.

This post will explore the new geospatial features in DBR 17.1 that can significantly improve spatial analytics in Databricks.

Let’s get ready to unlock the full potential of geospatial analytics on the Databricks Platform!

What’s new in DBR 17.1 — Geospatial Data Types and Functions

DBR 17.1 introduces native support for geospatial data types and additional functions to work with these types.

GEOMETRY type

The GEOMETRY type represents a geometry object (point, linestring, or polygon, etc.) using a Euclidean coordinate reference system (X, Y) and an optional Z coordinate, as though the Earth were flat. The underlying coordinate reference is described by the SRID value of the GEOMETRY type. There are over 11K SRIDs supported for this type.

Example:

SELECT st_geomfromtext(‘POINT(1 2)’, 4326);

GEOGRAPHY type

The GEOGRAPHY type represents a geographic object (point, linestring, polygon, etc.) using a geographic coordinate system on the Earth's surface. The coordinates are expected to be valid longitude and latitude values. Note: Currently, only 4326 is supported for this type.

Example:

SELECT st_geogfromtext(‘POINT(1 2)’);

80+ SPATIAL Functions

A wide range of spatial functions have also been added — too many to dive into for this post! We will review a select few, but please check out the full list at the link above. These functions can assist in importing/exporting spatial data, editing and updating spatial types, understanding spatial relationships, and transforming spatial data.

Working with Geospatial types in DBSQL

In the following sections, we will dive into the Geospatial types in Databricks! To follow along, ensure you are using a cluster with DBR 17.1 or higher. A sample notebook with the code used in this section is available here.

Let’s start by creating some sample geospatial data! For our example, let's take a retail use case. We will create tables to represent:

• Retail Stores (points)
• Delivery Zones (polygons)
• Routes (paths)
• Customer locations (points)

Retail Stores

-- Create a table of retail store locations using the new POINT geometry type

CREATE OR REPLACE TABLE IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores') AS
SELECT
 store_id,
 store_name,
 city,
 state,
 ST_Point(longitude, latitude, 4326) as location,
 annual_revenue,
 employee_count,
 store_type
FROM VALUES
 (1, 'Downtown Seattle', 'Seattle', 'WA', -122.3328, 47.6061, 2500000, 25, 'flagship'),
 (2, 'Pike Place Market', 'Seattle', 'WA', -122.3417, 47.6089, 1800000, 18, 'standard'),
 (3, 'SFO Union Square', 'San Francisco', 'CA', -122.4074, 37.7873, 3200000, 32, 'flagship'),
 (4, 'Mission District', 'San Francisco', 'CA', -122.4194, 37.7599, 1600000, 16, 'standard'),
 (5, 'Beverly Hills', 'Los Angeles', 'CA', -118.4000, 34.0736, 2800000, 28, 'flagship'),
 (6, 'Santa Monica', 'Los Angeles', 'CA', -118.4912, 34.0195, 2100000, 21, 'standard'),
 (7, 'Times Square', 'New York', 'NY', -73.9857, 40.7580, 4200000, 42, 'flagship'),
 (8, 'Brooklyn Heights', 'New York', 'NY', -73.9958, 40.6955, 1900000, 19, 'standard'),
 (9, 'Michigan Avenue', 'Chicago', 'IL', -87.6244, 41.8781, 3100000, 31, 'flagship'),
 (10, 'Lincoln Park', 'Chicago', 'IL', -87.6376, 41.9217, 1700000, 17, 'standard')
AS t(store_id, store_name, city, state, longitude, latitude, annual_revenue, employee_count, store_type);

We can see that this table has saved our location in the new geometry type!

-- Verify the store data and examine the geometry type
SELECT
 store_name,
 city,
 location,
 ST_AsText(location) as location_wkt,
 ST_X(location) as longitude,
 ST_Y(location) as latitude
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores')
LIMIT 5;

Delivery Zones

-- Create delivery zone polygons using ST_GeomFromText
CREATE OR REPLACE TABLE IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones') AS
SELECT
 zone_id,
 zone_name,
 city,
 ST_GeomFromText(zone_polygon, 4326) as zone_boundary,
 max_delivery_time_minutes,
 delivery_fee
FROM VALUES
 (1, 'Seattle Central', 'Seattle', 'POLYGON((-122.3500 47.5900, -122.3200 47.5900, -122.3200 47.6200, -122.3500 47.6200, -122.3500 47.5900))', 30, 5.99),
 (2, 'SF Downtown', 'San Francisco', 'POLYGON((-122.4200 37.7700, -122.3900 37.7700, -122.3900 37.8000, -122.4200 37.8000, -122.4200 37.7700))', 25, 7.99),
 (3, 'LA West Side', 'Los Angeles', 'POLYGON((-118.5000 34.0000, -118.3500 34.0000, -118.3500 34.1000, -118.5000 34.1000, -118.5000 34.0000))', 45, 6.99),
 (4, 'Manhattan Midtown', 'New York', 'POLYGON((-74.0000 40.7400, -73.9700 40.7400, -73.9700 40.7800, -74.0000 40.7800, -74.0000 40.7400))', 20, 8.99),
 (5, 'Chicago Loop', 'Chicago', 'POLYGON((-87.6400 41.8600, -87.6100 41.8600, -87.6100 41.8900, -87.6400 41.8900, -87.6400 41.8600))', 35, 6.99)
AS t(zone_id, zone_name, city, zone_polygon, max_delivery_time_minutes, delivery_fee);

-- Examine the delivery zones
SELECT
 zone_name,
 city,
 ST_AsText(zone_boundary) as polygon_wkt,
 -- Use ST_Transform to change SRID to one that is measured in meters for easier calculation
 ST_Area(ST_Transform(zone_boundary, 5070)) as area_sq_meters
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones');

Customer Data with H3 Integration

-- Create customer locations with H3 indexing (combining new geometry types with H3)
CREATE OR REPLACE TABLE IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') AS
SELECT
 customer_id,
 customer_name,
 ST_Point(longitude, latitude, 4326) as location,
 h3_pointash3(ST_AsText(location), 7) as h3_cell_level7,
 city,
 state,
 last_order_date,
 total_lifetime_value
FROM VALUES
 (1001, 'Alice Johnson', -122.3350, 47.6062, 'Seattle', 'WA', '2025-01-15', 1250.00),
 (1002, 'Bob Smith', -122.3400, 47.6080, 'Seattle', 'WA', '2025-01-20', 890.50),
 (1003, 'Carol Davis', -122.4080, 37.7875, 'San Francisco', 'CA', '2025-01-18', 2100.75),
 (1004, 'David Wilson', -122.4200, 37.7600, 'San Francisco', 'CA', '2025-01-22', 1650.25),
 (1005, 'Eva Brown', -118.4010, 34.0740, 'Los Angeles', 'CA', '2025-01-17', 1890.00),
 (1006, 'Frank Miller', -118.4920, 34.0200, 'Los Angeles', 'CA', '2025-01-25', 980.30),
 (1007, 'Grace Lee', -73.9860, 40.7585, 'New York', 'NY', '2025-01-19', 2850.90),
 (1008, 'Henry Garcia', -73.9960, 40.6960, 'New York', 'NY', '2025-01-21', 1320.60),
 (1009, 'Iris Martinez', -87.6250, 41.8785, 'Chicago', 'IL', '2025-01-16', 1450.80),
 (1010, 'Jack Rodriguez', -87.6380, 41.9220, 'Chicago', 'IL', '2025-01-23', 1750.40)
AS t(customer_id, customer_name, longitude, latitude, city, state, last_order_date, total_lifetime_value);

-- Verify customer data with H3 cells
SELECT
 customer_name,
 city,
 ST_AsText(location) as point_wkt,
 h3_cell_level7
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers')
LIMIT 5;

Examples of Geospatial Analytics on Retail Samples

Spatial Relationships and filtering

In this section, we are finding all customers within the delivery zones. We can use ST_DistanceSpheroid on SRID 4326 points to determine the distance in meters between two points on the Earth. As a performance trade-off, ST_DistanceSphere can be used instead at the cost of some accuracy.

-- Find all customers within delivery zones using ST_Within
SELECT
 c.customer_name,
 c.city,
 dz.zone_name,
 dz.delivery_fee,
 -- ST_DistanceSperoid will give us meters to the zone centre
 ST_DistanceSpheroid(c.location, ST_Centroid(dz.zone_boundary)) as distance_to_zone_center
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') c
JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones') dz ON dz.city = c.city
WHERE ST_Within(c.location, dz.zone_boundary)
ORDER BY c.city, distance_to_zone_center;

Distance and Area calculations

Here, we are using a different method to obtain our measurement in meters. By leveraging the ST_Transform function to project our SRID 4326 coordinates to SRID 507,0, which is measured in meters.

Because we are using SRID 5070 here, we can leverage ST_Distance to directly calculate the distance in the native units (meters) instead of calling ST_DistanceSphere or ST_DistanceSpheroid. For more information on the differences between the distance functions, refer to the official launch post for Spatial SQL.

-- Calculate area of delivery zones and find nearest store
SELECT
 dz.zone_name,
 dz.city,
 ROUND(ST_Area(ST_Transform(dz.zone_boundary, 5070)), 2) as area_sq_meters,
 rs.store_name as nearest_store,
 -- We can also project to the SRID that is measured in meters
 ROUND(ST_Distance(ST_Centroid(ST_Transform(dz.zone_boundary, 5070)), ST_Transform(rs.location, 5070)), 2) as distance_to_store_meters
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones') dz
CROSS JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores') rs
WHERE rs.city = dz.city
QUALIFY ROW_NUMBER() OVER (PARTITION BY dz.zone_id ORDER BY ST_Distance(ST_Centroid(dz.zone_boundary), rs.location)) = 1
ORDER BY dz.city;

Buffer analysis for market expansion

-- Create 5km buffer zones around flagship stores to identify market opportunities
WITH flagship_buffers AS (
 SELECT
   store_id,
   store_name,
   city,
   location,
   ST_Buffer(location, 0.045) as buffer_5km -- Approximately 5km at these latitudes
 FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores')
 WHERE store_type = 'flagship'
),
potential_customers AS (
 SELECT
   fb.store_name,
   fb.city,
   COUNT(c.customer_id) as customers_in_buffer,
   ROUND(SUM(c.total_lifetime_value), 2) as total_market_value,
   ROUND(AVG(c.total_lifetime_value), 2) as avg_customer_value
 FROM flagship_buffers fb
 LEFT JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') c ON ST_Within(c.location, fb.buffer_5km)
 GROUP BY fb.store_id, fb.store_name, fb.city
)
SELECT * FROM potential_customers
ORDER BY total_market_value DESC;

Density Analysis w/ H3 Aggregations

Let's use our sample dataset to visualise the density of our delivery zones. By finding all customers within a given hex boundary, we can see where our customers are clustered and where to focus our services.

-- Analyze customer density using H3 hexagonal grid
SELECT
 h3_cell_level7,
 h3_boundaryaswkt(h3_cell_level7) as hex_boundary,
 COUNT(customer_id) as customer_count,
 ROUND(SUM(total_lifetime_value), 2) as total_value,
 ROUND(AVG(total_lifetime_value), 2) as avg_value_per_customer
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers')
GROUP BY h3_cell_level7
HAVING customer_count >= 1 
ORDER BY total_value DESC;

Performance with Spatial Data

Especially in these early days of the features, passing hints into the queries can significantly speed up our geospatial processing. If, for example, we know that one side of our join is small enough to fit in memory, then leveraging the existing broadcast hints can provide big boosts in performance!

-- Optimized: Broadcast the smaller delivery_zones table
SELECT /*+ BROADCAST(dz) */
 c.customer_name,
 dz.zone_name,
 ROUND(ST_DistanceSpheroid(c.location, ST_Centroid(dz.zone_boundary)), 2) as distance_meters
FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') c
JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones') dz ON ST_Within(c.location, dz.zone_boundary);

Delta Lake optimisations

Let's optimize the current tables and then run a complex query on the data.

-- Optimize tables for better query performance
ALTER TABLE customers_indexed CLUSTER BY (h3_level8);

-- Add table properties for better performance
ALTER TABLE customers_indexed SET TBLPROPERTIES (
 'delta.autoOptimize.optimizeWrite' = 'true',
 'delta.autoOptimize.autoCompact' = 'true'
);

Store Delivery Optimisation in Retail

-- Find optimal delivery dispatch
WITH customer_store_distances AS (
 SELECT
   c.customer_id,
   c.customer_name,
   c.location as customer_location,
   rs.store_id,
   rs.store_name,
   rs.location as store_location,
   ST_DistanceSpheroid(c.location, rs.location) as distance,
   ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY ST_DistanceSpheroid(c.location, rs.location)) as store_rank
 FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') c
 CROSS JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores') rs
 WHERE rs.city = c.city  -- Only consider stores in the same city
),
optimal_routes AS (
 SELECT
   customer_id,
   customer_name,
   store_id,
   store_name,
   distance as distance_meters
 FROM customer_store_distances
 WHERE store_rank = 1  -- Closest store
)
SELECT
 store_name,
 COUNT(customer_id) as assigned_customers,
 ROUND(AVG(distance_meters), 2) as avg_distance_meters,
 ROUND(SUM(distance_meters), 2) as total_route_distance
FROM optimal_routes
GROUP BY store_id, store_name
ORDER BY assigned_customers DESC;

Best Practices Summary

• Leverage native geometry and geography types for type safety and performance
• Broadcast small tables on large spatial joins (for now)
• Combine ST functions and H3 approximations to efficiently operate on spatial data at scale
• Leverage Databricks' in-built optimisations, such as liquid clustering

Finally, let's finish up with a comprehensive analysis of the Retail Supply chain coverage in our sample data:

-- Combine all components to determine where to most effectively invest to improve our service
WITH delivery_metrics AS (
 SELECT
   rs.store_id,
   rs.store_name,
   rs.city,
   COUNT(DISTINCT c.customer_id) as total_customers,
   ROUND(AVG(ST_DistanceSpheroid(rs.location, c.location)), 2) as avg_delivery_distance,
   ROUND(SUM(c.total_lifetime_value), 2) as total_market_value,
   -- Calculate service area using envelope around customers
   ST_Envelope(ST_Union_Agg(c.location)) as service_area
 FROM IDENTIFIER(:catalog || '.' || :schema || '.' || 'retail_stores') rs
 LEFT JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'customers') c ON c.city = rs.city
 GROUP BY rs.store_id, rs.store_name, rs.city
),
zone_coverage AS (
 SELECT
   dm.*,
   dz.zone_name,
   dz.max_delivery_time_minutes,
   ROUND(
     COALESCE(
       ST_Area(ST_Intersection(dm.service_area, dz.zone_boundary)) /
       NULLIF(ST_Area(dz.zone_boundary), 0),
       0
     ), 3
   ) as zone_coverage_ratio
 FROM delivery_metrics dm
 LEFT JOIN IDENTIFIER(:catalog || '.' || :schema || '.' || 'delivery_zones') dz ON dz.city = dm.city
),
efficiency_metrics AS (
 SELECT
   *,
   ROUND(total_market_value / NULLIF(avg_delivery_distance, 0), 2) as value_per_km,
   CASE
     WHEN zone_coverage_ratio > 0.8 THEN 'excellent'
     WHEN zone_coverage_ratio > 0.6 THEN 'good'
     WHEN zone_coverage_ratio > 0.3 THEN 'fair'
     WHEN zone_coverage_ratio > 0.15 THEN 'partial'
     ELSE 'needs_improvement'
   END as coverage_rating
 FROM zone_coverage
)
SELECT
 store_name,
 city,
 total_customers,
 avg_delivery_distance as avg_delivery_distance_m,
 total_market_value,
 value_per_km as efficiency_ratio,
 ROUND(zone_coverage_ratio * 100, 1) as coverage_percentage,
 coverage_rating
FROM efficiency_metrics
ORDER BY efficiency_ratio DESC;

Conclusion

Hopefully, this has got you excited to start leveraging Spatial SQL for your Geospatial workloads on Databricks!

For further reading, take a look at the launch post for Spatial SQL. You can find all the code used in this post in the accompanying notebook here. Additional sample code and notebooks are also available in the Databricks Documentation.

Unlocking Geospatial Analysis with Databricks SQL was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author: Abhishek Dey

Introduction

Recursive Common Table Expressions (CTEs) are a powerful yet underutilised feature of SQL that allows queries to repeatedly reference their own results. In Databricks SQL (DBSQL), Recursive CTEs provide scalable and elegant solutions to problems involving hierarchies, paths, and dependencies, eliminating the need for procedural code or custom loops.

How Recursive CTEs Work

Recursive CTEs are a form of self-referencing SQL that builds output over successive evaluations. A recursive CTE has two components:

1. Anchor query — seeds the recursion with base results
2. Recursive query — joins back on its own output to expand the result set
3. Termination — stops when no new rows are returned

In Databricks SQL:

• The WITH RECURSIVE keyword is mandatory
• Recursive references must be at the top level of the statement
• Recursion depth can be implicitly controlled using filters or guards
• It is highly performant
• Recursive level is configurable using MAX RECURSION LEVEL
• Up to 1M rows in the resultset, which can be overruled with a LIMIT in the driving query

For more information, please refer to our documentation.

What did users do before Recursive CTEs?

Before Recursive CTEs, teams often had to:

• Write PySpark or Python code to simulate hierarchy or graph traversals
• Manage recursion manually using loops, state tracking, and temp views
• Break recursion into multiple flat joins or procedural SQL blocks
• Use third-party graph engines or orchestration tools outside SQL
• Accept delays or stale results because real-time graph logic wasn’t SQL-native

These approaches were:

• Heavy for data engineers
• Too complex for most SQL analysts
• Slower to build, debug, and maintain

What do Recursive CTEs unlock?

With this feature, users can now:

• Express deeply structured problems declaratively — in pure SQL
• Eliminate Python boilerplate and flattening logic
• Traverse trees, DAGs, and dependency chains directly in the query engine
• Model stateful or multi-hop systems (e.g., org charts, workflows, routes)
• Keep business logic in one place — fast, explainable, governed

Now, let’s look at some real-life use cases and how Recursive CTEs in Databricks SQL drive business insights.

Use Case 1: Hierarchies and Business Rollups

Scenario:
In large enterprises, departments are often organized hierarchically (e.g., Corporate → Engineering → ML → AI Lab). Business users often need to calculate total salary spend, headcount, or budget roll-ups across this hierarchy.

What Recursive CTE Solves:
Recursive CTEs help you traverse the organizational tree, identify all descendant departments under a given root, and aggregate values like salary or cost across those levels.

-- Drop & create department hierarchy
DROP TABLE IF EXISTS dbx_departments;


CREATE TABLE IF NOT EXISTS dbx_departments (
 dept_id STRING, parent_dept_id STRING, dept_name STRING
);


INSERT INTO dbx_departments
 VALUES
   ('D1', NULL, 'Corporate'),
   ('D2', 'D1', 'Finance'),
   ('D3', 'D1', 'Engineering'),
   ('D4', 'D3', 'Platform'),
   ('D5', 'D3', 'ML'),
   ('D6', 'D5', 'AI Lab');


-- Drop & create employees table
DROP TABLE IF EXISTS dbx_employees;


CREATE TABLE IF NOT EXISTS dbx_employees (
 emp_id STRING, emp_name STRING, dept_id STRING, salary INT
);


INSERT INTO dbx_employees
 VALUES
   ('E1', 'Alice', 'D2', 120000),
   ('E2', 'Bob', 'D3', 150000),
   ('E3', 'Charlie', 'D4', 140000),
   ('E4', 'David', 'D5', 135000),
   ('E5', 'Eva', 'D6', 180000),
   ('E6', 'Frank', 'D6', 170000);


-- Recursive CTE to expand department hierarchy up to 5 levels
WITH RECURSIVE dept_tree (
  root_dept,
  dept_id,
  path,
  level
)
MAX RECURSION LEVEL 5 AS (


  -- Anchor: start with each department as a root
  SELECT
    dept_id AS root_dept,
    dept_id,
    dept_name AS path,
    1 AS level
  FROM dbx_departments


  UNION ALL


  -- Recursive: find child departments and build the path
  SELECT
    dt.root_dept,
    d.dept_id,
    CONCAT(dt.path, ' > ', d.dept_name),
    dt.level + 1
  FROM dbx_departments d
  JOIN dept_tree dt ON d.parent_dept_id = dt.dept_id
)


-- Aggregate total salary for each root department and show the department path
SELECT
  root_dept,
  MAX(path) AS dept_path,
  SUM(e.salary) AS total_salary
FROM dept_tree dt
LEFT JOIN dbx_employees e ON dt.dept_id = e.dept_id
GROUP BY root_dept
ORDER BY root_dept, total_salary DESC;

Output:

How It Works Technically

• Start with Every Department as a Root
  The anchor query initializes each department as its own root with level 1 and a starting path.
• Recursively Traverse the Hierarchy
  The recursive part joins child departments to their parents, building a full path and incrementing the level.
• Limit Depth to 5 Levels
  The MAX RECURSION LEVEL 5 directive ensures the hierarchy expansion stops after 5 levels, preventing runaway recursion.
• Join Employees and Roll Up Salaries
  For each department reached in the tree, the salaries of employees are joined and grouped by the original root department.
• Aggregate and Present Paths and Totals
  The final output shows the root department, the full department path, and the total salary cost under each subtree.

Business Outcomes:

• Enables LOB-level financial planning
• Simplifies compliance rollups and cost centre analytics
• Powers org-wide KPI dashboards without needing ETL restructuring

Use Case 2: Valid Flight Itineraries (Multi-Hop Path Planning)

Scenario:
In global travel and logistics networks, customers or systems need to find all valid flight itineraries between two locations, considering multiple stops, avoiding loops, and optionally factoring in timing or layover constraints. For example, discovering paths like JFK → CDG → SIN → SYD without revisiting the same city.

What Recursive CTE Solves:
Recursive CTEs simulate multi-hop traversal across a flight graph, where each node is an airport and edges are valid flights. They allow SQL to:

• Build paths of arbitrary length from a given origin
• Accumulate total travel time or distance
• Track visited airports to avoid cycles
• Support constraints such as layover time windows, flight directionality, or airline preferences

How it works technically:
The anchor query selects direct flights from the origin. The recursive part then joins flights from the current destination, expanding the path and preventing cycles using ARRAY logic.

-- DROP and create base flights table
DROP TABLE IF EXISTS flights;


CREATE TABLE flights (flight_id STRING, origin STRING, destination STRING, duration INT);


-- Sample data with multiple hops
INSERT INTO flights
 VALUES
   ('F1', 'JFK', 'CDG', 7),
   ('F2', 'CDG', 'SIN', 13),
   ('F3', 'SIN', 'SYD', 8),
   ('F4', 'JFK', 'LHR', 6),
   ('F5', 'LHR', 'SYD', 22),
   ('F6', 'CDG', 'SYD', 18),
   ('F7', 'JFK', 'DXB', 12),
   ('F8', 'DXB', 'SIN', 9),
   ('F9', 'SIN', 'JFK', 20);


-- cycle potential
-- Recursive CTE to find all valid itineraries from JFK to SYD
WITH RECURSIVE flight_paths (
 origin,
 destination,
 path,
 total_time,
 hops,
 cycle
) AS (
 -- Anchor: start from JFK
 SELECT
   origin,
   destination,
   ARRAY(origin, destination),
   duration,
   1,
   false
 FROM
   flights
 WHERE
   origin = 'JFK'
 UNION ALL
   -- Recursive: expand next legs
 SELECT
   fp.origin,
   f.destination,
   array_union(fp.path, ARRAY(f.destination)),
   fp.total_time + f.duration,
   fp.hops + 1,
   array_contains(fp.path, f.destination)
 FROM
   flight_paths fp
   JOIN flights f ON fp.destination = f.origin
 WHERE
   NOT fp.cycle
   AND fp.hops < 5
) -- Final result: valid acyclic paths to SYD
SELECT
 path,
 total_time,
 hops
FROM
 flight_paths
WHERE
 destination = 'SYD'
 AND NOT cycle
ORDER BY
 total_time ASC;

Output:

Business Outcomes:

• Empowers travel engines and OTAs to provide flexible, rule-based itineraries
• Supports route optimization for cargo and logistics routing systems
• Enables what-if analysis in network design
• Builds the foundation for pricing engines, availability queries, or travel planning UX tools

This use case showcases the effectiveness of SQL recursion as a graph traversal tool, enabling the solution of use cases that were previously addressed in code or graph databases.

Use Case 3: Satellite Missions Orbital Planner

Scenario:

In space logistics, management of concurrent satellite missions, each with different objectives — communications, reconnaissance, and scientific observation is needed. These missions must navigate a congested orbital environment with increasing risk from space debris and limited propulsion capacity.

Each spacecraft:

• Has a specific payload type, which limits allowed orbital transitions.
• Operates under a strict delta-v (Δv) budget, constraining how much it can maneuver.
• Must avoid blocked orbits, especially those recently impacted by debris-generating events.
• Can only initiate transfers during valid orbital windows.
• Needs to find a safe, legal, and fuel-efficient path to its target orbit (e.g., from Low Earth Orbit(LEO) to Geostationary Earth Orbit(GEO) ).

What Recursive CTE Solves

They enable SQL-based exploration of all valid orbital paths from LEO to GEO, respecting mission constraints like fuel (delta-v), debris avoidance, and payload routing. Recursive CTEs solve the challenge of pathfinding across a dynamic graph while enforcing complex physical and operational rules — all inside SQL.

How it works technically:

A Recursive CTE starts with a base case (starting from LEO) and expands paths one hop at a time by joining with allowed transitions. It accumulates metrics such as total delta-v, tracks visited orbits to prevent cycles, and filters out paths that violate constraints, including blocked orbits or fuel limits. This recursion continues until no new valid paths can be found.

-- Step 1: Define transition graph
DROP TABLE IF EXISTS orbit_transfers;
CREATE TABLE orbit_transfers (
 from_band STRING,
 to_band STRING,
 delta_v_kms DOUBLE
);


INSERT INTO orbit_transfers VALUES
 ('LEO', 'MEO', 2.5),
 ('MEO', 'GTO', 3.0),
 ('GTO', 'GEO', 1.5),
 ('LEO', 'GTO', 5.0),
 ('LEO', 'SSO', 2.2),
 ('SSO', 'GTO', 4.5);


-- Step 2: Flag debris-heavy zones
DROP TABLE IF EXISTS debris_zones;
CREATE TABLE debris_zones (
 band STRING,
 is_blocked BOOLEAN
);


INSERT INTO debris_zones VALUES
 ('LEO', false),
 ('MEO', true),   -- High collision risk
 ('GTO', false),
 ('GEO', false),
 ('SSO', false);


-- Step 3: Recursive CTE simulating valid orbital paths
WITH RECURSIVE orbit_path (
 current_band,
 path,
 total_delta_v,
 visited,
 cycle,
 blocked
) AS (
 -- Anchor: start from LEO
 SELECT
   from_band,
   ARRAY(from_band, to_band),
   delta_v_kms,
   ARRAY(from_band, to_band),
   false,
   dz.is_blocked
 FROM orbit_transfers ot
 JOIN debris_zones dz ON ot.to_band = dz.band
 WHERE from_band = 'LEO' AND dz.is_blocked = false


 UNION ALL


 -- Recursively expand paths
 SELECT
   ot.to_band,
   array_union(op.path, ARRAY(ot.to_band)),
   op.total_delta_v + ot.delta_v_kms,
   array_union(op.visited, ARRAY(ot.to_band)),
   array_contains(op.visited, ot.to_band),
   dz.is_blocked
 FROM orbit_path op
 JOIN orbit_transfers ot ON ot.from_band = op.current_band
 JOIN debris_zones dz ON ot.to_band = dz.band
 WHERE NOT op.cycle
   AND dz.is_blocked = false
   AND op.total_delta_v + ot.delta_v_kms <= 9.5
)


-- Step 4: Return optimal paths to GEO
SELECT
 path,
 total_delta_v
FROM orbit_path
WHERE current_band = 'GEO' AND NOT cycle
ORDER BY total_delta_v ASC;

Output :

Business Outcomes:

• Autonomous Routing: Recursive CTEs in DBSQL simulate all valid orbital transfer paths in SQL, eliminating the need for external graph algorithms.
• Real-Time Adaptability: They dynamically avoid debris zones and respect mission timing, enabling safer and more adaptive planning.
• Faster Planning Cycles: Mission plans that once took hours via scripting are now computed in seconds using SQL alone.
• Cost Optimization: By minimizing fuel (delta-v) and avoiding delays, Recursive CTEs reduce operational costs per mission.
• Auditability and Compliance: All logic is versioned, governed, and integrated with enterprise controls, ensuring traceable, compliant planning.

Recursive CTEs empower aerospace and satellite teams to build safer, faster, and more cost-effective missions — entirely in Databricks SQL.

Best Practices When Using Recursive CTEs in DBSQL

- Define an Exit Condition
 — Ensure recursion converges either by reaching a LIMIT, checking for visited states, usage of MAX RECURSION LEVEL <INTEGER_VALUE> or tracking a depth column
 — Add a WHERE NOT cycle or WHERE depth <= X to prevent infinite loops.

- Use Arrays or Sets to Track Paths
 — Track visited nodes with ARRAY or MAP types to detect cycles
 — array_contains(visited, next_node) is a common pattern to prevent infinite recursion

- Recursion Depth
 — Use a max recursion level or depth column and cap it (depth <= 10, etc.) to avoid runaway computation

- Filter Early
 — Push constraints into the anchor and recursive parts to reduce the result set
 — Avoid full-table joins within the recursion

- Use Liquid Clustering when possible
 — If tables are large (e.g., hierarchies or graph edge lists), optimize with liquid clustering on join keys like parent_id, from_node, or dept_id

Troubleshooting Tips

- No Output
 — If your recursive CTE returns no results, verify that your anchor query returns rows

- Infinite Recursion or Data Explosion
 — Add a depth limit and cycle detection (via visited arrays or map_contains) to prevent runaway recursion

- Unexpected or Incomplete Paths
 — Debug step-by-step using depth, path, and cardinality; validate that joins are expanding correctly.

- Cycle Detection Not Working
 — Ensure correct use of array_contains() or map_contains() — especially when tracking STRUCTs or multiple keys.

- Recursion Stops Too Soon
 — Watch for filters or logical errors (e.g., blocked paths, early cycle flags) that halt recursion prematurely.

• Column Reference Errors
   — Always declare column names explicitly in recursive CTE headers

Recursive CTEs bring the power of graph traversal, hierarchy modeling, and complex state simulation directly into your SQL environment. Whether you’re planning satellite missions, resolving org structures, or simulating dependencies, this feature empowers you to solve real-world challenges without leaving your data platform.

For data and platform product teams, Recursive CTEs using DBSQL are a game-changer:

• Ship Faster: Eliminate external code (Python, orchestration logic) and build complex data logic natively in SQL. No round trips, no pipeline delays.
• Lower Platform Complexity: Fewer microservices, no need for custom DAGs or graph engines — everything lives in SQL, fully governed.
• Enable Real-Time Features: Power use cases like dependency resolution, lineage tracing, or route planning directly from live datasets.
• Scale Confidently: Built on Databricks SQL engine, Recursive CTEs scale to millions of rows without sacrificing performance.
• Keep Logic Close to Data: Business rules are expressed where the data lives, making it easier to maintain, test, and collaborate across data, product, and engineering teams.
• Migrate to Databricks SQL: Recursive CTEs have always been complex objects to migrate to Databricks With the support of recursive CTEs, it gets much easier to
• Unify logic and compute on the lakehouse
• Get fully managed recursion with explicit controls
• Build real-time graph logic, dependency trees, hierarchies, and workflows — all declarative in Databricks SQL

If you’re using Databricks SQL, now is the time to try out Recursive CTEs:

• Model your hierarchies and dependencies natively
• Replace Python or graph engine workarounds with declarative SQL
• Explore your data recursively — securely and at scale

Give it a try in your workspace, and see how much complexity you can eliminate by thinking recursively!

Related Blogs

• https://www.databricks.com/product/databricks-sql
• https://www.databricks.com/blog/introducing-sql-scripting-support-databricks-part-1
• https://www.databricks.com/blog/introducing-recursive-common-table-expressions-databricks

Driving Business Insights with Recursive CTEs in DBSQL was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author: Cody Austin Davis, Field Engineering Manager at Databricks

Introduction

In this article, we are going to cover a ton of ground. So much has happened in the Databricks SQL world this year, so we are going to walk through a single use case to demonstrate where analytics is heading on DBSQL in the era where AI is embedded in all future analytics projects. This use case will focus on creating and analyzing STIX Cyber Threat Intelligence data in a graph to analyze patterns and campaigns. This use case requires a lot, and we will leverage this example to showcase the following capabilities in Databricks SQL that are now available:

• Recursive CTEs — Databricks SQL now supports recursive CTEs, which unlocks a TON of cool analytics use cases — the main one in which we will focus on today is recursive graph traversal on tables in Databricks.
• Stored Procedures — We can now create and use Stored Procedures in Databricks sql to run any arbitrary compound SQL workflows. This is really important for packaging up advanced SQL scripts (like our graph traversal logic) into reusable components that end users and agents can use without any heavy lifting.
• Agentic Analytics — Now that we can create functions and stored procedures in Unity Catalog, the next step is to use that function registry as a tool catalog for any agents that we build with them, this is critical for context and logical governance for agentic systems.

Since this is a dense article, lets outline first what we will accomplish, and you can feel free to skip to any section if you want to zoom in on a particular areas:

• Step 1 — STIX data graph generation — generate some synthetic STIX data to analyze via a Graph data model.
• Step 2 — Write some logic to analyze the graph with recursive CTEs to do common analysis patterns like search and page rank. Create Functions and Stored Procedures to analyze the graph with composable and reusable tools.
• Step 3— Tie these functions into an agentic system that can help end users analyze this graph without the need to code!

Lets get started! The Git repo for this whole project from data generation, function definition, to building an agent prototype can be found here in the Git repo.

Disclaimer: This is a cyber focused use case to demonstrate new DBSQL features, but me (the author) is NOT a cyber security expert — so apologies if I get some domain expertise concepts wrong :).

Step 1 — Create a STIX dataset to analyze

We start with a python script to generate some node and edge data for a graph of STIX data of various malware, tools, infra, threat-actors, etc. The script can be re-run from anywhere, you can find the whole script here in the Git repo.

import uuid
import random
import json
from datetime import datetime
from faker import Faker
from stix2 import (
    ThreatActor, Malware, Indicator, Relationship, ObservedData,
    Identity, Tool, Infrastructure, Report, CourseOfAction
)
from pyspark.sql import Row
from pyspark.sql.types import StructType, StructField, StringType, MapType

from pyspark.sql.functions import from_json, col, parse_json

# === Configuration ===
NUM_THREAT_ACTORS = 100
NUM_MALWARE = 100
NUM_CAMPAIGNS = 10
NUM_OBSERVED = 1000
NUM_TOOLS = 10
NUM_INFRASTRUCTURE = 10
NUM_REPORTS = 10
NUM_COURSE_OF_ACTION = 5
NUM_IDENTITIES = 10000

fake = Faker()

# === Reusable Core Entities ===
threat_actors = [
    ThreatActor(
        id=f"threat-actor--{uuid.uuid4()}",
        name=fake.company(),
        description=fake.text(),
        roles=["intrusion-set"],
        labels=["apt"]
    ) for _ in range(NUM_THREAT_ACTORS)
]

malware_pool = [
    Malware(
        id=f"malware--{uuid.uuid4()}",
        name=fake.word(),
        is_family=True,
        labels=["trojan", "spyware"]
    ) for _ in range(NUM_MALWARE)
]

tools = [
    Tool(
        id=f"tool--{uuid.uuid4()}",
        name=fake.word(),
        labels=["remote-access", "loader"]
    ) for _ in range(NUM_TOOLS)
]

infrastructure_list = [
    Infrastructure(
        id=f"infrastructure--{uuid.uuid4()}",
        name=fake.domain_name(),
        infrastructure_types=["command-and-control"]
    ) for _ in range(NUM_INFRASTRUCTURE)
]

identities = [
    Identity(
        id=f"identity--{uuid.uuid4()}",
        name=fake.company(),
        identity_class="organization",
        sectors=["technology", "financial-services"],
        contact_information=fake.email()
    ) for _ in range(NUM_IDENTITIES)
]

# === Generate STIX Objects ===
stix_objects = []

# Campaigns
for _ in range(NUM_CAMPAIGNS):
    ta = random.choice(threat_actors)
    malware_used = random.sample(malware_pool, k=random.randint(1, 3))
    indicators = [
        Indicator(
            id=f"indicator--{uuid.uuid4()}",
            name="Malicious IP",
            pattern_type="stix",
            pattern=f"[ipv4-addr:value = '{fake.ipv4()}']"
        ) for _ in range(random.randint(1, 2))
    ]
    relationships = []
    for mal in malware_used:
        relationships.append(Relationship(
            id=f"relationship--{uuid.uuid4()}",
            relationship_type="uses",
            source_ref=ta.id,
            target_ref=mal.id,
        ))
        for ind in indicators:
            relationships.append(Relationship(
                id=f"relationship--{uuid.uuid4()}",
                relationship_type="indicates",
                source_ref=ind.id,
                target_ref=mal.id,
            ))

    stix_objects.append(ta)
    stix_objects.extend(malware_used)
    stix_objects.extend(indicators)
    stix_objects.extend(relationships)

# Observed Data (e.g. logs, detections)
for _ in range(NUM_OBSERVED):
    first = fake.date_time_this_year()
    last = fake.date_time_between_dates(datetime_start=first)

    observed = ObservedData(
        id=f"observed-data--{uuid.uuid4()}",
        first_observed=first,
        last_observed=last,
        number_observed=random.randint(1, 10),
        objects={
            "0": {"type": "ipv4-addr", "value": fake.ipv4()},
            "1": {"type": "domain-name", "value": fake.domain_name()}
        }
    )
    stix_objects.append(observed)

# Contextual entities
stix_objects.extend(tools)
stix_objects.extend(infrastructure_list)
stix_objects.extend(identities)

# Reports
for _ in range(NUM_REPORTS):
    published = fake.date_time_this_year()
    obj_refs = random.sample([o.id for o in stix_objects if hasattr(o, "id")], k=3)
    report = Report(
        id=f"report--{uuid.uuid4()}",
        name=fake.sentence(),
        published=fake.date_time_this_year(),
        object_refs=obj_refs
    )
    stix_objects.append(report)

# Courses of Action
for _ in range(NUM_COURSE_OF_ACTION):
    coa = CourseOfAction(
        id=f"course-of-action--{uuid.uuid4()}",
        name="Apply security patch",
        description=fake.sentence()
    )
    stix_objects.append(coa)

# === Convert to Spark Rows ===
def to_row(obj):
    parsed = json.loads(obj.serialize())
    return Row(
        stix_id=parsed.get("id"),
        stix_type=parsed.get("type"),
        name=parsed.get("name", None),
        description=parsed.get("description", None),
        raw=json.dumps(parsed)  # No extra whitespace, no escape issues
    )

rows = [to_row(o) for o in stix_objects]

# === Define schema with raw as string ===
schema = StructType([
    StructField("stix_id", StringType(), True),
    StructField("stix_type", StringType(), True),
    StructField("name", StringType(), True),
    StructField("description", StringType(), True),
    StructField("raw", StringType(), True)
])

# === Create Spark DataFrame and Parse JSON ===
df = spark.createDataFrame(rows, schema=schema)
df = df.withColumn("raw", parse_json(col("raw")))

(df.write.format("delta")
 .mode("overwrite")
 .option("overwriteSchema", "true")
 .saveAsTable(f"{TARGET_CATALOG}.{TARGET_SCHEMA}.full_synthetic_stix_graph")
)

Our output should look like this in Unity Catalog:

where can can review some sample data:

This example node is a synthetic malware node that also contains edges that connects to other objects like threat-actors (such as threat actors → uses → malware). We now have a graph of connected actors, malware, and tools that we can analyze. But what if we have a LOT of these? We need to build more robust graph pattern search and traversal functions to get enough insight out of our threat graph. This is where all the new features of DBSQL start to shine!

Step 2 — Graph Analytics Functions on Unity Catalog

To analyze any basic graph, we need to be able to do a few things, such as

• Get information about a node or edge
• Find ‘neighbors’, or connections to nodes we care about
• Perform pattern search to do things like identifying nodes with a lot of influence, finding common relationships between 2 nodes, and cluster patterns of nodes/activity together.

Now there are a ton of graph traversal algos out there, but lets focus on some basic ones to identify high influential nodes and find all the nearest connections to a node we care about. This is a common need in security and many other industries like adtech, healthcare, finance fraud detection, etc.

Graph algos are not as straightforward as common SQL analytics problems. They require more advanced algorithms that track state and perform recursion. Now with Stored Procedures and Recursive CTEs, we can do these kinds of things natively in SQL!

Lets write a few functions out to start to provide some reusable tools that analysts or AI agents can use:

• Get Note / Edge Types — we need to be able to easily discover the types of nodes/edges we have in our graph first. We create out first functions to do this here:

CREATE OR REPLACE FUNCTION main.threat_graph_analytics.get_node_types()
RETURNS ARRAY<STRING>
COMMENT 'This gets all the available node types in the graph'
RETURN
  (SELECT collect_set(type)
  FROM main.threat_graph_analytics.stix_graph_nodes
  LIMIT 1)
;

CREATE OR REPLACE FUNCTION main.threat_graph_analytics.get_relationship_types()
RETURNS ARRAY<STRING>
COMMENT 'This gets all the available edge connection types in the graph'
RETURN
  SELECT collect_set(relationship_type)
  FROM main.threat_graph_analytics.stix_graph_edges
  LIMIT 1;

• Get Node Metadata — we cant do much if we cant look at info about a node! So let’s define a function to do that for a given node we care about:

CREATE OR REPLACE FUNCTION main.threat_graph_analytics.get_node_metadata(node_id STRING)
RETURNS STRUCT<id: STRING, type: STRING, raw: STRING>
COMMENT 'Get all metadata / information about a particular node id'
RETURN
  (SELECT
    STRUCT(id, type, CAST(raw AS STRING) AS raw)
  FROM main.threat_graph_analytics.stix_graph_nodes
  WHERE id = node_id
  LIMIT 1);

• Top Connected Nodes — to just get a sense for the top nodes with the most connections, we can write a simple function to find the most connected nodes:

CREATE OR REPLACE FUNCTION main.threat_graph_analytics.get_top_connected_nodes(limit_num INT DEFAULT 2, connected_node_types ARRAY<STRING> DEFAULT NULL)
RETURNS ARRAY<STRUCT<node_id: STRING, type: STRING, degree: INT>>
COMMENT 'Get the top connected nodes to a give node id to begin deeper analysis on impactful nodes. You can optionally supply a filter array<string> list of node types you want connections for.'
RETURN (
  SELECT collect_list(
    STRUCT(
      node_id,
      type,
      degree
    )
  )
  FROM (
    SELECT
      n.id AS node_id,
      n.type,
      (COALESCE(e_in.degree_in, 0) + COALESCE(e_out.degree_out, 0)) AS degree,
      ROW_NUMBER() OVER (ORDER BY (COALESCE(e_in.degree_in, 0) + COALESCE(e_out.degree_out, 0)) DESC) AS rn
    FROM main.threat_graph_analytics.stix_graph_nodes n
    LEFT JOIN (
      SELECT dst AS id, COUNT(*) AS degree_in
      FROM main.threat_graph_analytics.stix_graph_edges
      GROUP BY dst
    ) e_in ON n.id = e_in.id
    LEFT JOIN (
      SELECT src AS id, COUNT(*) AS degree_out
      FROM main.threat_graph_analytics.stix_graph_edges
      GROUP BY src
    ) e_out ON n.id = e_out.id
  )
  WHERE rn <= limit_num
  AND connected_node_types IS NULL OR array_contains(connected_node_types, type)
);

• Breadth First Search — now we get into analyzing patterns/relationships in the graph. This function helps us find all other nodes closely connected to a node we are analyzing within N degrees of separation away from the node.

-- Use Stored procedures for more complex compound statements with multiple steps and params
CREATE OR REPLACE PROCEDURE main.threat_graph_analytics.bfs_search(
  input_node_id STRING, 
  max_level INT, 
  connected_node_types ARRAY<STRING> DEFAULT NULL
)
LANGUAGE SQL
SQL SECURITY INVOKER
COMMENT 'Breadth first search from a given input node id with max_level being the number of degrees out from the node you want to search. You can also supply an array<list> of node types you want to track connections for'
AS
BEGIN
    WITH RECURSIVE neighborhood(level, node_id, prev_node_id, relationship_type, path, visited) AS (
      -- Base case: start from the given node
      SELECT
        0 AS level,
        id AS node_id,
        CAST(NULL AS STRING) AS prev_node_id,
        CAST(NULL AS STRING) AS relationship_type,
        ARRAY(id) AS path,
        ARRAY(id) AS visited
      FROM main.threat_graph_analytics.stix_graph_nodes AS n
      WHERE n.id = input_node_id

      UNION ALL

      -- Breadth-first search: traverse level by level
      SELECT
        n.level + 1,
        CASE WHEN e.src = n.node_id THEN e.dst ELSE e.src END AS node_id,
        n.node_id AS prev_node_id,
        e.relationship_type,
        array_union(n.path, ARRAY(CASE WHEN e.src = n.node_id THEN e.dst ELSE e.src END)) AS path,
        array_union(n.visited, ARRAY(CASE WHEN e.src = n.node_id THEN e.dst ELSE e.src END)) AS visited
      FROM neighborhood n
      JOIN main.threat_graph_analytics.stix_graph_edges e
        ON n.node_id = e.src OR n.node_id = e.dst
      WHERE n.level < max_level
        AND NOT array_contains(n.visited, CASE WHEN e.src = n.node_id THEN e.dst ELSE e.src END)
    )

    SELECT
      STRUCT(
        n.level AS level,
        n.node_id AS node_id,
        nodes.type AS type,
        n.prev_node_id AS prev_node_id,
        n.relationship_type AS relationship_type,
        array_join(n.path, ', ') AS path
      )
    FROM neighborhood n
    JOIN main.threat_graph_analytics.stix_graph_nodes nodes
      ON n.node_id = nodes.id
    WHERE n.level > 0
      AND (connected_node_types IS NULL OR array_contains(connected_node_types, nodes.type))
;

END;

This is where some of our new DBSQL functionality starts to come into play! In the above example, we created a Stored Procedure — this wraps any arbitrary SQL logic into a single callable object, making managing more complex pipelines or analytical functions much easier to manage and reuse! Additionally, we leverage recursive CTEs to run a graph traversal algorithm to find all connected nodes to an input node within N degrees of separation with breadth-first search. This is really powerful!

• Page Rank — This is a classic algorithm to determine the amount of influence a node has relative to others. Basically a popularity contest based on the number of inbound connections to it weighted by the influence of the nodes it is connected to. We can now implement this in a stored procedure.

CREATE OR REPLACE PROCEDURE main.threat_graph_analytics.pagerank_top_n(
  top_n INT,
  filter_node_types ARRAY<STRING> DEFAULT NULL
)
LANGUAGE SQL
SQL SECURITY INVOKER
COMMENT 'Run page rank on the STIX graph to get a stack rank of the most influential nodes, optionally filtered by node types'
AS
BEGIN
  WITH RECURSIVE
  params AS (
    SELECT CAST(0.85 AS DOUBLE) AS d, CAST(30 AS INT) AS max_iter
  ),
  nodes AS (
    SELECT id, type
    FROM main.threat_graph_analytics.stix_graph_nodes
    WHERE filter_node_types IS NULL OR array_contains(filter_node_types, type)
  ),
  total_nodes AS (
    SELECT COUNT(*) AS n FROM nodes
  ),
  edges AS (
    SELECT e.src, e.dst
    FROM main.threat_graph_analytics.stix_graph_edges e
    JOIN nodes ns ON ns.id = e.src
    JOIN nodes nd ON nd.id = e.dst
  ),
  outdeg AS (
    SELECT src, COUNT(*) AS outdeg FROM edges GROUP BY src
  )

  /* Paths recursion: carry per-path weight; NO aggregation inside recursion */
  paths (node_id, weight, iteration) AS (
    -- iteration 0: start with 1/N at each node
    SELECT
      n.id         AS node_id,
      CAST(1.0 / t.n AS DOUBLE) AS weight,
      0            AS iteration
    FROM nodes n
    CROSS JOIN total_nodes t

    UNION ALL

    -- push weight along outgoing edges, scaling by d/outdeg
    SELECT
      e.dst        AS node_id,
      CAST(p.weight * par.d / NULLIF(od.outdeg, 0) AS DOUBLE) AS weight,
      p.iteration + 1 AS iteration
    FROM paths p
    JOIN params par         ON TRUE
    JOIN edges e            ON e.src = p.node_id
    LEFT JOIN outdeg od     ON od.src = e.src
    WHERE p.iteration < par.max_iter
  ),

  last_iter AS (
    SELECT MAX(iteration) AS k FROM paths
  ),

  /* Sum contributions at the final iteration and add teleport mass */
  final_rank AS (
    SELECT
      n.id AS node_id,
      n.type AS node_type,
      -- teleport series: ((1-d)/N) * (1 - d^k) / (1 - d)
      CAST( ((1 - par.d) / t.n) * (1 - POWER(par.d, li.k)) / (1 - par.d) AS DOUBLE )
        + COALESCE(pr_k.inbound_sum, 0.0) AS pagerank
    FROM nodes n
    CROSS JOIN params par
    CROSS JOIN total_nodes t
    CROSS JOIN last_iter li
    LEFT JOIN (
      SELECT node_id, SUM(weight) AS inbound_sum
      FROM paths
      WHERE iteration = (SELECT k FROM last_iter)
      GROUP BY node_id
    ) pr_k
    ON pr_k.node_id = n.id
  )

  SELECT node_id, node_type, pagerank
  FROM final_rank
  ORDER BY pagerank DESC
  LIMIT top_n;
END;

Now we have some really strong tools that we can reuse for Graph analysis, and all we need to do is run these functions! For functions, we call them by using them in any SQL expression like below:

SELECT main.threat_graph_analytics.get_node_types();
SELECT main.threat_graph_analytics.get_top_connected_nodes(5);
SELECT main.threat_graph_analytics.get_node_metadata('malware--c3072957-766c-4e5a-8f0c-6d95f1f00537')

For Stored Procedures, since these are more formal compound scripts that are run, we invoke them with a CALL statement like the following:

-- Call your stored procedures to do advanced analytics
CALL main.threat_graph_analytics.pagerank_top_n(10, split('threat-actor,malware', ','));
CALL main.threat_graph_analytics.bfs_search('threat-actor--c2a4aee6-e118-466d-93ef-72d42718455c',2)

We are starting to collect a strong repository of tools we can use for advanced analytics — and now in AI agentic systems! Notice that we add comments that describe what this function does — this is much more important to do when using these functions in AI systems, so the model can understand what it does and when it may need to be used.

Step 3 — Use functions as tools in Agents on Databricks

Now that we have some strong reusable functions to analyze our graph, we can empower analysts to use them to perform searches for patterns in our graph — OR we could build an agentic system to help us to that better! To do that, we can leverage the Mosaic Agent Framework on Databricks to construct an agent that not only can summarize information, but can actually do analysis on our graph data for us!

First things first, we can start by using the functions we defined above to pass clean information into an AI model directly in Databricks SQL like so:

-- You can query your custom function to do analysis / get node / campaign info, and then pass to AI models or even custom agents you host in model serving!
WITH results AS (
  SELECT main.threat_graph_analytics.get_node_metadata('malware--8ba54ee3-823b-4c8f-af94-4cfab3a6ee22') AS node
)
SELECT ai_query(
  'databricks-meta-llama-3-3-70b-instruct',
  CONCAT('Summarize the following metadata: ', CAST(node AS STRING))
) AS summary
FROM results;

In this example, we run our function to get data about a node and pass it into the ai_query function and specify any model hosted on Databricks. This is a powerful generic AI function to be able to access any native model or model serving endpoint hosted on Databricks straight from SQL.

But we can do a lot more by building an Agent hosted on Databricks so that it not only summarizes information you give it, but can actually run the analysis for us (and decide how to run the analysis given the tools it has access to) to automatically build analytical reports without the end user needing to know anything about Graph analytics.

If you have yet to built an agent in Databricks, here are some links to get started building an agent and testing it in the Databricks playground via code or with AgentBricks:

• Create to Agents on Databricks — Show the different ways to build out of the box or custom agents with Databricks
• Create Out of the Box Agents with AgentBricks — no code immediate agents to do common task like document parsing, information extraction, customLLM, and an agent supervisor for evaluation.
• Create an Agent with Code and Serve on Databricks — Learn how to build a custom agent for production use cases end to end from agent development, evaluation, serving, monitoring, and rapid prototyping.

We are going to build an Agent in code with the AI Agent Framework in Databricks to use our Graph tools. We wont go through the entire agent development process in this article, but you can easily plug in your model and tools to the existing template notebooks available here (live notebook) and here (docs). You do not need to code all this from scratch, so use these templates to get started quickly.

These template notebooks walk through the process of defining the agent, integrating it with ML Flow 3.0 for tracing and agent evaluation (very important for production use cases), and deploying your agent to a Databricks Model Serving endpoint to be used anywhere.

Since the above template notebooks walk you through the whole process, we will focus here on how to add our tools to the agent from this notebook starting point.

First we set mlflow to autolog, define the model we want to use, and define a system prompt for our agent base model.

from typing import Any, Generator, Optional, Sequence, Union,Iterable, List, Dict, Any
import mlflow
from databricks_langchain import (
    ChatDatabricks,
    VectorSearchRetrieverTool,
    DatabricksFunctionClient,
    UCFunctionToolkit,
    set_uc_function_client,
)
from unitycatalog.ai.langchain.toolkit import UnityCatalogTool
from langchain_core.language_models import LanguageModelLike
from langchain_core.runnables import RunnableConfig, RunnableLambda
from langchain_core.tools import BaseTool, Tool, StructuredTool
from langgraph.graph import END, StateGraph
from langgraph.graph.graph import CompiledGraph
from langgraph.graph.state import CompiledStateGraph
from langgraph.prebuilt.tool_node import ToolNode
from mlflow.langchain.chat_agent_langgraph import ChatAgentState, ChatAgentToolNode
from mlflow.pyfunc import ChatAgent
from mlflow.types.agent import (
    ChatAgentChunk,
    ChatAgentMessage,
    ChatAgentResponse,
    ChatContext,
)
import os
from langchain_community.utilities.sql_database import SQLDatabase
from pydantic import BaseModel, Field
from databricks import sql

mlflow.langchain.autolog()

client = DatabricksFunctionClient()
set_uc_function_client(client)

############################################
# Define your LLM endpoint and system prompt
############################################
LLM_ENDPOINT_NAME = "databricks-claude-3-7-sonnet"
WAREHOUSE_ID = "<WAREHOUSE_ID>"

llm = ChatDatabricks(endpoint=LLM_ENDPOINT_NAME)

system_prompt = """This is a Graph Analytics Agent for cyber threat detection data with STIX dataset. Your goal is to analyze that graph and product threat reports. 
"""

Next, we need to define tools and attach them to our agent. To do this, we use the UCFunctionToolkit and bind our UC functions as tools to our LangGraph agent. Lets do that:

### Add tools out of the box from Unity Catalog Functions

tools = []

# You can use UDFs in Unity Catalog as agent tools
uc_tool_names = ["main.threat_graph_analytics.get_node_types",
"main.threat_graph_analytics.get_relationship_types", 
"main.threat_graph_analytics.get_node_metadata",
"main.threat_graph_analytics.get_top_connected_nodes"]

uc_toolkit = UCFunctionToolkit(function_names=uc_tool_names)

tools.extend(uc_toolkit.tools)

This is very simple for UC Functions! We can also add any custom tools we want here as well. In our example, we will add a custom query handler to push to the DBSQL Warehouse we want as well as a custom page_rank tool to call our Stored Procedure:

#### Tool 1 - Custom Query Tool

# Define the schema for the tool input
class QueryInput(BaseModel):
    query: str = Field(..., description="A SELECT-only SQL query to run on Databricks.")

# Your execution function
def run_databricks_query(query: str, params: Optional[tuple] = None) -> str:
    return run_query(query, params)

# Create the tool object manually
databricks_sql_tool = Tool(
    name="databricks_sql_query",
    description="Run a read-only SQL query against the configured Databricks catalog.schema.",
    func=run_databricks_query,
    args_schema=QueryInput
)


# Append it to your existing list of tools
tools.append(databricks_sql_tool)


#### Tool 2 - Breadth first search stored procedure
class NeighborhoodInput(BaseModel):
    node_id: str = Field(..., description="Start node ID (e.g., a STIX ID).")
    max_level: int = Field(2, ge=1, le=10, description="Max hop distance from the start node.")


def run_recursive_neighborhood_query(node_id: str, max_level: int = 2) -> list[dict]:
    query = """CALL main.threat_graph_analytics.bfs_search(?,?)"""
    # Uses your existing DB-API runner with '?' binding
    return run_query(query, params=(node_id, int(max_level)), as_dict=True)


recursive_neighborhood_tool = StructuredTool.from_function(
    func=run_recursive_neighborhood_query,
    name="recursive_neighborhood_query",
    description=(
        "Breadth-first neighborhood from a starting node up to `max_level` hops. "
        "Returns rows with level, node_id, node_type, prev_node_id, relationship_type, and path."
    ),
    args_schema=NeighborhoodInput
)

tools.append(recursive_neighborhood_tool)


#### Tool 3 - Custom Page Rank Tool
class PageRankModelInput(BaseModel):
    top_n: int = Field(10, ge=1, le=10, description="Top N nodes in page rank popularity.")
    node_types: Optional[List[str]] = Field(
        default=None,
        description="Optional list of node types to filter on (e.g., ['threat-actor', 'malware'])."
    )

def run_page_rank_query(top_n: int = 10, node_types: Optional[List[str]] = None) -> list[dict]:
    if node_types:
        # Convert list to comma-separated string for SQL
        node_types_str = ",".join(node_types)
        query = """CALL main.threat_graph_analytics.pagerank_top_n(?, split(?, ','))"""
        params = (top_n, node_types_str)
    else:
        query = """CALL main.threat_graph_analytics.pagerank_top_n(?)"""
        params = (top_n,)
    return run_query(query, params=params, as_dict=True)

page_rank_tool = StructuredTool.from_function(
    func=run_page_rank_query,
    name="page_rank_query",
    description=(
        "Page rank algorithm to get the top N most popular nodes, optionally filtered by a list of node types, by page rank influence."
    ),
    args_schema=PageRankModelInput
)

tools.append(page_rank_tool)

All a tool needs is an input Schema (via a Pydantic data class), a function, a description, and a name! With this, we can add our more custom stored procedures right alongside our initial UCfunction toolkit. Now we have a list of tool objects, and all thats left is to bind those tools to our agent and compile it!

def create_tool_calling_agent(
    model: LanguageModelLike,
    tools: Union[Sequence[BaseTool], ToolNode],
    system_prompt: Optional[str] = None,
) -> CompiledGraph:
    ## This is where be connect our tools we created to the agent
    model = model.bind_tools(tools)

.... more code.....

The rest of the code in the notebook is our starting template for our agent, feel free to follow that notebook and play with it to build your own!

Now we follow the notebook to register the agent as a model in MLFlow in Unity Catalog, write a py file, evaluate the agent, and deploy it to a model serving endpoint!

Once we run agent.deploy(), we can then open the Databricks playground and test our agent by selecting the model we just deployed:

We have a quick and easy playground to try out our agent, and evaluate its live performance. From here, now we iterate on the agent to refine the tools, prompts, agent logical flow, as well as the evaluation cases we need to run our ML Flow Agent Evaluation against to make sure it passes all our tests.

This is just a starting point to showcase how all the latest and greatest DBSQL features can drastically up-level your analytics, and integrate seamlessly with the latest AI development frameworks available — all on Databricks.

Graph Analytics with DBSQL Stored Procedures and Agents was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Authors: Cody Austin Davis, Field Engineering Manager @ Databricks; Sadhana Bala, Product Manager @ Databricks

Introduction

Maximizing the value of your Databricks account starts with clear visibility into cost and usage. To support this, last year, Databricks launched v1 of the Usage Dashboard powered by system tables. Since then, thousands of customers have used the dashboard to gain essential cost observability across their Databricks accounts.

We’ve received great feedback from teams who rely on the dashboard for high level day-to-day cost monitoring. At the same time, we’ve heard a clear demand for deeper, more actionable insights to better manage usage across the Databricks platform.

This year, we’re excited to preview version 2.0 of the Usage Dashboard (link) that takes observability a step further and helps users not only understand spend at a high level, but also:

🔍Deep dive into team and use case specific spend

🔍 Monitor untagged, or mistagged usage

🔍 Identify specific objects and owners driving spend anomalies

In this blog, we will showcase the new sections in the dashboard and highlight some of the updated use cases that customers are using this dashboard for!

Set up

This dashboard is now available in Databricks Labs (link) to be downloaded and imported by any user with access to system tables. To get started, review the README, import the dashboard, and start managing your usage!

Sections of the Dashboard

The dashboard now leverages the latest and greatest of AI/BI Dashboards (such as global filters, dynamic measures, and multi-tabbed sections!) and is comprised of 4 key sections:

1. Usage Overview: Summarize executive-friendly spend analysis and trends
2. Usage Overview — Top N: Quickly identify top N drivers of spend and find owners of that object
3. Usage Analysis — Tag Matching: Understand usage in the way that makes sense for your organization through tags; find usage that contains specific sets of tags, and find untagged/mistagged usage to match your organization’s cost management model
4. Usage Analysis — Top Objects: Dive deeper by finding top objects driving spend as well as identifying ownership and location of objects to immediately take action at a granular level

Section I: Usage Overview

This section provides an executive summary of spend across a Databricks account to answer broad questions on trends, forecast usage using an AI-powered function, and display high level spend by product, workspace over different periods.

How it works:

• Choose the time grain you want to look at (Daily/Weekly/Monthly/etc.), and quickly see the top spending products, usage run rate, as well as optionally forecasted usage
• Leverage the global filters to enable different preview functions
• By default the dashboard pulls in system.billing.list_prices, but use the “Preview — Price Table” filter to switch over to system.billing.account_prices (only works if you are enrolled in the PrPr for AWS and GCP),
• If not enabled on account_prices, input your “Discount Override” by specifying SKU-level or billing_origin_product level discounts
• Enable forecasted projections of spend with the “Preview — Forecast” filter, leveraging the AI_FORECAST function (in PuPr)

Many customers use these tables to export from the Dashboard and do further analysis and budgeting outside the tool!

Section II: Usage Overview — Top N

In this section, users can quickly identify top N objects, ranked by spend. For example, in our dashboard above, we see that SQL is our top spend driver. In this top N section, we can quickly look at the top spending warehouses.

How it works

• Select the object type that you want to rank by — here we are looking at warehouse_ids
• Specify the number (“top N”) of objects you want to break out — here we look at top 10

This now identifies the top 10 warehouses over time as well as shows us how these top objects compare to the “long tail” of remaining warehouses (enable or disable this comparison with the “Include Remaining Billable Objects” filter). This is often an important comparison because it helps users easily see how skewed usage is to the top objects (i.e. “is 80% of my SQL spend coming from these top warehouses?”). I

If we scroll down, we can identify in which workspaces theses warehouses live, as well as get a period over period analysis:

In our example above, we can see the top objects and see that our top 10 warehouses make up about 90% of our total SQL spend ($55k spend in REMAINING OBJECTS vs $541k total SQL spend). We can also click on these hyperlinked object_ids to navigate directly to those warehouses, and see everything about that warehouse like owner, sizing, usage patterns, etc.

Section III: Usage Analysis — Tag Matching

This section allows you to manage and view your usage within the semantic terms that your business defines through tags. It allows you to ask questions such as: “What is the usage for my ads platform?”, “What are my development costs for my supply chain use case?”, and “What products make up these use cases’ spend?“.

For example, let’s say we tag our objects in production with a team tag. We can search for that tag and see how much of our total usage is properly tagged with team like so:

How it works

• Search for multiple tag key, value pairs (or just keys) in the Tag search filter
• Filter for just usage matching this tag, or report on usage not matching this tag, or not tagged at all using the Usage Match Filter
• Normalize Tags, so you can track tags regardless of casing or extra spacing in the terms

You can now report on spend by product and workspace, for the team tag, to support chargebacks and showbacks. If the tag match rate is low, we can Group by product and identify if tagging is low across all the product lines. The next section will also help deep dive into which objects are improperly tagged or untagged usage!

If you do not yet know which tags to search on, you can now leverage the “Tag Popularity” section at the bottom of this tab to find the most common tags:

Section IV: Usage Analysis — Top Objects

Let’s say you identify a team that has some out-of-budget spend. What do you do to actually start to find the cause of that and take action? You can then leverage the Usage Analysis — Top Objects section to drill deeper into the specific objects (jobs, warehouses, endpoints, clusters, etc.) that are driving usage for that team.

How it works

• Use the tag search filter to search for the tag(s) of interest — in this example we search for team=databricks_shared, as they are our top spending team
• Optionally filter for what type of objects you want to filter for by leveraging the “Billing origin product” Global filter (e.g., SQL only)
• Filter out old objects that are not used recently with the “Days Since Last Usage” slider

As we filter for a specific team / tag set, the above visual gives us a quick heatmap of where spend is high by workspace and product. We can even click on the “SQL” part of the heatmap to quickly filter for the drivers of that high spend. We can very quickly see that this team is a heavy SQL user. But what warehouses are driving that, and who owns them? We can dig in further in the below card that shows identifiable details of each warehouse (id, owner, name), and when it was last used:

If you click on the Billable Object Id, this will take you directly to the warehouse to monitor it and take action!

Conclusion

Now, in a single dashboard, you can zoom in from high level executive spend management all the way down to specific drivers of spend that you can immediately take action on. Customers have begun to use this tool as a starting point to build a rich cost observability and management system on Databricks!

This dashboard is still in preview so please feel free to reach out to sadhana.bala@databricks.com (PM, Cost Management) to provide feedback!

Introducing version 2.0 of the Databricks Usage Dashboard was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author: Chris Koester, Specialist Solutions Architect @ Databricks

Introduction

The new http_request function enables performing HTTP requests with DBSQL, and with connections governed by Unity Catalog. http_request unlocks compelling new opportunities for SQL, and should be considered when there’s a preference to use SQL over Python, use governed HTTP connections, or use DBSQL warehouses for compute.

To demonstrate the new capabilities, this post will guide you through building a simple earthquake monitor using the http_request function, AI/BI, and workflows.

Key Components

1. USGS earthquakes data feed: Data feed provided by the USGS. This is a great data source because it doesn’t require authentication, and is updated every minute.
2. HTTP connection in Unity Catalog: Governed connection to the USGS earthquakes data feeds.
3. http_request: SQL function that allows us to make HTTP requests using the HTTP connection defined above, and is available in DBSQL and DBR 16.2+.

Step-by-Step Guide

1. Establish an HTTP Connection in Unity Catalog

First, we need to create an HTTP connection for the USGS feeds. This governed connection in Unity Catalog allows us to manage permissions and credentials in one place. The connection will also automatically propagate to your serverless network policy.

Developers can use any feed available from the base path of the connection.

The USGS feeds don’t require authentication, so a placeholder of “na” is used for the bearer_token argument.

create connection if not exists usgs_conn
type HTTP
options (
  host 'https://earthquake.usgs.gov',
  port '443',
  base_path '/earthquakes/feed/v1.0/',
  bearer_token 'na'
);

2. Make the HTTP Request

Next, we’ll make a GET request to the data feed using http_request. Notice that we use the HTTP connection that we created above. parse_json is used to return the data as the variant type. Variant is used for storing semi-structured data, and provides an easier option than using a struct and an explicit schema.

select
  parse_json(
    http_request(
      conn => 'usgs_conn',
      method => 'GET',
      path => 'summary/all_day.geojson'
    ).text
  ) as resp_variant

This is what the results look like as variant. The data we’re looking for is in the features array.

3. Explode the Features Array

We need to explode the features array and return the array elements as rows. Explode requires an array or map, so we’ll cast features from variant to array<variant>.

with resp as (
select
  parse_json(
    http_request(
      conn => 'usgs_conn',
      method => 'GET',
      path => 'summary/all_day.geojson'
    ).text
  ) as resp_variant
)
select
  explode(variant_get(resp_variant, '$.features')::array<variant>) as col
from resp

We now have a row for each element in the array.

4. Extracting Earthquake Details

The data are in a single variant column, and we’ll extract the desired fields using the intuitive dot-notation syntax. Type casting and processing will be performed here as well.

-- Get response and return as variant
with resp as (
select
  parse_json(
    http_request(
      conn => 'usgs_conn',
      method => 'GET',
      path => 'summary/all_day.geojson'
    ).text
  ) as resp_variant
),
-- Convert array of features to rows
response_rows as (
  select
    explode(variant_get(resp_variant, '$.features')::array<variant>) as col
  from resp
)
-- Select desired columns
select
  from_unixtime(col:properties.time::bigint/1000) as utc_time,
  col:properties.mag::double,
  col:properties.place::string,
  col:geometry.coordinates[0]::double as longitude,
  col:geometry.coordinates[1]::double as latitude,
  col:geometry.coordinates[2]::double as depth
from response_rows

The results look like the example below, and can now be written to Delta by wrapping the statement above with create or replace table usgs as. Since the feed provides data for a rolling time period (Past day), create or replace table provides an easy way of maintaining the same rolling period in Delta.

5. Schedule as a Job

With the SQL statement complete, we can create a job for easy scheduling and orchestration. The screenshot below shows a couple dozen runs with a 2X-Small warehouse, and the longest duration is 41 seconds.

6. Visualize with AI/BI

With data being updated frequently using a job, we can share fresh insights using an AI/BI dashboard.

Handle Unsuccessful Responses with SQL Scripting

We can improve the robustness of the solution by checking the response status code and taking different actions based on the status. If our HTTP request to the feed is successful, we’ll receive a response with a status code of 200 along with the requested data.

If we receive a status code other than 200, we want to log the response and exit. We don’t want to proceed without data, and the log will allow us to monitor unsuccessful requests. For example, here’s a response where the path url was incorrect.

SQL scripting (Available in DBSQL 2025.15 and DBR 16.3) offers a way of checking the HTTP response and taking a different action based on the status code. Let’s review the steps. It is assumed that the HTTP connection shown earlier has already been created.

1. Create Logging Table

We’ll use a simple table for logging unsuccessful requests. The job id and job run id are also logged.

create table if not exists earthquakes_error_log (
  timestamp timestamp,
  job_id string,
  job_run_id string,
  status_code int,
  error_message string
);

2. Make the HTTP Request

Retrieve the complete response (status and text) and store it in a variable.

declare or replace resp struct<status_code int, text string>;

set var resp = (
  select
    http_request(
      conn => 'usgs_conn',
      method => 'GET',
      path => 'summary/all_day.geojson'
    )
);

3. Load Data or Log Non-200 Responses

An IF THEN ELSE statement is used to check the response status code and determine which action is taken. This prevents us from trying to parse and load unsuccessful requests. You could also choose to raise an exception and fail the job for unsuccessful requests.

-- Log non-200 responses to Delta table
-- Script must be in separate notebook cell
begin
  if resp.status_code == 200 then
    create or replace table earthquakes as
    with raw_data as (
      select explode(variant_get(parse_json(resp.text), '$.features')::array<variant>) as col
    )
    select
      from_unixtime(col:properties.time::bigint/1000) as utc_time,
      col:properties.mag::double,
      col:properties.place::string,
      col:geometry.coordinates[0]::double as longitude,
      col:geometry.coordinates[1]::double as latitude,
      col:geometry.coordinates[2]::double as depth
    from raw_data;
  else
    insert into IDENTIFIER(:catalog || '.' || :schema || '.' || 'earthquakes_log')
      values (current_timestamp(), :job_id, :job_run_id, resp.status_code, resp.text);
  end if;
end;

Other Patterns and Opportunities

The example described in this post just scratches the surface of what is newly available at the intersection of HTTP and SQL. http_request also supports HTTP methods ‘POST’, ‘PUT’, ‘DELETE’, and ‘PATCH’.

Databricks documentation shows how to connect AI agent tools to external services, and the companion repo for this post also contains some additional examples:

• nasa_asteroids shows how to generate a collection of metadata for multiple HTTP requests, then iterate through them using a workflows for each task.
• databricks_api shows how to work with the Databricks API using simple SQL functions.

Conclusion

In this post, we demonstrated how to construct an end-to-end solution using a serverless DBSQL warehouse to retrieve and process data from a web-based API. By leveraging the http_request function, we can fetch data from external APIs and persist it to Delta Lake using SQL. Orchestration and sharing of insights can be accomplished with workflows and AI/BI.

Furthermore, we showed how the solution’s robustness can be improved with SQL scripting.

The solution described in this post can be deployed as a Databricks Asset Bundle (DAB), which can be found in the DBSQL HTTP repo.

Building an Earthquake Monitor with DBSQL’s HTTP_REQUEST was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Authors

Andrey Mirskiy, Lead Specialist SA @ Databricks
Davide Veneziano, Sr. Solutions Architect @ Databricks

Introduction

The blog introduces the cheatsheet, a compilation of best practices for integrating Databricks with Power BI, to optimize performance, scalability, cost, and report efficiency.

In today’s data-driven landscape, integrating powerful tools like Databricks and Power BI is essential for businesses aiming to maximize their data’s potential. Databricks provides a robust platform for data engineering and analytics, while Power BI excels in data visualization. To fully leverage these tools, careful planning and adherence to best practices are crucial.

This blog post aims to introduce the best practices when using Power BI on Databricks. Here, we introduce a one-pager cheatsheet which provides a concise and structured overview, outlining the key best practices to ensure meeting high performance, scalability, and security requirements and manage costs efficiently.

Unlocking Business Insights with Databricks and Power BI

Databricks offers robust data warehousing capabilities through its Databricks SQL engine, which is designed for high performance and scalability. By leveraging a Lakehouse architecture, Databricks enables users to analyze virtually unlimited data volumes. For scalability, Databricks offers Serverless SQL warehouses that automatically scale in and out to meet workload demands, offering high performance and cost efficiency. This is critical for BI applications where fluctuating and spiky workloads are common.

Integrating Power BI with Databricks offers several advantages that enhance business intelligence capabilities. It enables organizations to analyze large datasets efficiently, leveraging Databricks’ distributed computing platform, coupled with Power BI’s semantic modeling capabilities and dynamic visualization, to ensure optimal performance even with massive datasets, all under a unified governance framework.

Why Do Best Practices Matter?

Is your report running slow? In most cases this is due to sub-optimal configuration or design.

Not following best practices using Power BI on Databricks can lead to multiple challenges that impact performance and overall user experience. Poorly optimized tables, inefficient semantic models, and sub-optimal configuration settings can lead to slow report loading times, as well as unnecessary costs.

Adoption of best practices is the key to ensure performance, scalability, and cost efficiency: your reports will load quicker and more efficiently, providing users with timely insights at lower costs.

Introducing the Power BI on Databricks Best Practices Cheatsheet

In our daily interactions with customers, we observed that while there are numerous resources available, there’s a significant gap: a concise summary of the most important best practices for using Power BI on Databricks is sorely needed.

To address this need, we introduce Power BI on Databricks Best Practices Cheat Sheet.This resource provides concise and structured guidance specifically designed for Power BI and Databricks practitioners.

Best practices are organized into four typical phases, which may correspond to different roles within an organization. In some cases, these roles might be handled by the same individual leading to overlapping.

1. Data Preparation
   This phase is focused on preparing the dataset for reporting. The objective is to implement an efficient data model and optimize the storage layer for better query performance.
2. SQL Serving
   This phase involves setting up and configuring the Databricks SQL Warehouse that Power BI connects to. The goal is to meet performance SLAs and scalability requirements while managing costs efficiently.
3. Power BI Integration
   This phase focuses on configuring the integration between Power BI and Databricks. The objective is to optimize Power BI semantic models for performance and leverage unified governance.
4. Power BI Report Design
   In this phase, teams design semantic models and reports in Power BI. The goal is to create fast and efficient reports and dashboards that inherit optimizations from previous phases.

For each phase, an extensive list of best practices and recommendations is provided.

Every best practice includes a concise description, the explanation of the impact, and the link to the relevant source, such as documentation and blogs.

In the example above we recommend specific storage mode configurations in Power BI semantic models depending on the type of tables. For fact tables, we suggest using DirectQuery storage mode where Power BI submits queries directly to the data source, namely Databricks SQL. For dimension tables, we recommend using Dual storage mode which allows Power BI to decide whether to use cached data or query the data source based on the context. These recommendations aim to leverage both Databricks’ scalability and Power BI’s caching capabilities effectively. A link to the documentation is provided for further details. The rationale behind this best practice is also explained: implementing these configurations enables Power BI to generate more efficient SQL queries, improve scalability, optimize available capacity, and effectively utilize Databricks’ SQL engine. This approach of providing both recommendations and their justifications is common for most best practices discussed.

Additionally, each section includes troubleshooting steps to help identify the root cause of slow-running reports.

Finally, the document concludes with a list of links to in-depth articles for further reading.

Conclusion

Adopting best practices for integrating Databricks with Power BI is not just a recommendation; it’s a necessity for organizations seeking to maximize their data analytics capabilities. By optimizing data models, managing costs efficiently, and ensuring performance and scalability, businesses can unlock the full potential of these powerful tools.

We believe that the structure of the cheat sheet, which presents a simple and actionable checklist grouped into common phases, can accelerate the adoption of best practices. By following these structured guidelines, practitioners can efficiently optimize the use of Power BI on Databricks and ensure that their setup is efficient, performant, and scalable from the very beginning.

Whether you’re looking to improve report performance, manage costs, or enhance data governance, following Power BI on Databricks best practices will be instrumental in achieving your goals.

Introducing the Power BI on Databricks Best Practices Cheat Sheet was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author

Numan Ali, Solutions Architect @ Databricks

Introduction

Databricks has launched Genie, a user-friendly tool that allows any user to interact with their data using everyday language. Genie uses generative AI that understands your company’s specific terms and data, and it keeps improving by learning from user feedback. Genie makes it easier for everyone to access and understand data, all while governing the data sources, contexts, and improving the trusted results over time collaboratively.

One of the most common datasets in the organizations are the audit and usage logs generated by the data platforms. In Databricks, system tables are a rich analytical store of the users’ operations data. System tables include audit logs and lineage tables, which contain operational data for all Databricks workspaces in a customer’s account and the billing system table, which contains data for all workspaces. Customers have been using the system tables to create dashboards and reports that help them understand how the platform is being used. However, sometimes, those dashboards and reports are not enough and the platform owners and sponsors want to know more about the usage in different dimensions. They typically send their questions to the platform admins who then have to write custom queries to get the answers out. Combining Genie and System tables can help those users to get the information they need simply using the natural language.

In this article, we will cover 4 example use cases for analyzing system table cost and usage data:

1. Basic usage spike investigation — Identify anomalies in usage over time, and dig into drivers of trends
2. Contract management & burndown — Add your contract discounts, terms, and other context to help Genie manage your spend relative to your business contracts.
3. Tag-based usage analysis — Analyze drivers of usage based on your own contextual grouping of usage with tag-based analysis.
4. Spend forecasting — Forecast spend in natural language with SQL functions

Setting up Genie Space

Setting up a Genie space is very easy. We simply create a space and define the universe of tables from Unity Catalog that Genie can access for its space. For this particular Genie space, weneed the following tables;

• System.compute.clusters — Records compute configurations in your account.
• System.billing.usage — Provides the information about billable usage
• System.billing.list_prices — Gives you access to a historical log of SKU pricing
• system.workflow.jobs — Tracks creation, deletion, and basic information of jobs.
• System.workflow.job_run_timeline — Records the start, end, and resulting state of job runs.
• System.workflow.job_task_run_timeline — Records the start, end, and resulting state of job tasks.
• System.workflow.job_tasks — Tracks creation, deletion, and basic information of job tasks.
• System.compute.node_timeline — Includes minute-by-minute records of your compute’s utilization metrics.

Investigating a usage spike

Let’s imagine we area Platform Manager or in a position where weneed to look at the cost of running Databricks frequently. We would start with the usage trend of the current month. If we have to write it in SQL it would look something like this:

SELECT
 usage_date,
 SUM(usage_quantity) AS total_dbus
FROM
 system.billing.usage
WHERE
 usage_date BETWEEN DATE_TRUNC('month', CURRENT_DATE()) AND CURRENT_DATE()
GROUP BY 1
ORDER BY 1

There are many challenges with manually writing queries to get answers from the platform:

1. Users might not have time or skills to write a SQL code to get the answer out of the system or even if they do, they need to learn the table schemas and relations to write the SQL query, which can take up unnecessary time and effort.
2. Without a standardized way to improve intelligence on the data, users will likely ask the same questions over and over again, and often get the answers inconsistently, wasting time and decreasing confidence in the results!

In Genie, users can simply write this in natural language and let the Genie do this for them. Genie understands the relations and context of the tables that have been set up in the space. Users can ask questions, experiment, refine, and get to a cohesive answer without needing to write code from scratch. Most importantly, they can save the queries and context that Genie generates so users never have to ask the same question twice by adding to Genie’s trusted answers and SQL Queries / Functions context.

Here is a snapshot of how to get this information out using Genie:

Genie also explains the logic behind the SQL query it generates as you can see in the above example.

You can also click on the “Show generate code” button and see the code that Genie creates.

Not only can users see and improve the code Genie writes, but users can also add positive/negative feedback about its correctness, as well as add code as formal re-usable functions that Genie can access later.

That’s a great start but what if we would like to know what happened on a particular day e-g 7th of August as it seems to be the highest usage day? We can dig further in natural language.

It came out that the most usage happened at 4am in the morning. So, lets follow up with this question:

This is a bit too detailed for what we really need. Since Databricks has different prices for different SKUs and regions, it lists each one as a separate SKU in the system tables to track usage more accurately. But for my analysis, we want to look at usage by product. For example, weconsider both “ENTERPRISE_SERVERLESS_REAL_TIME_INFERENCE_US_WEST_OREGON” and “ENTERPRISE_SERVERLESS_REAL_TIME_INFERENCE_AP_TOKYO” are simply “Inference” or “Real Time Inference”

Therefore, we would follow up with this question to clean up our understanding of product groupings:

Billing.Usage tables keeps track of the origin product in “billing_origin_product” so we can add an instruction to use that column while answering the “Product” related questions like so:

You have probably noticed the response to above questions about usage was more in terms of DBUs (Databricks Units) and not the actual “dollar spend”. It is because the systems.billing.usage keeps the usage only in DBUs. But if you need the dollar value (or what we call $DBUs) you will then have to ask the question in a slightly different way.

When we look at the generated code now, wecan see the JOIN between system.billing.usage and system.billing.list_prices table to get the actual “list price”.

Note that this is the “list price” and not the price you might get with the discounts you have either with Databricks or your Cloud provider. To make it more “obvious” we can also add instructions to get the “spend” from the system.billing.list_prices tables.

Since we have spent the effort to refine Genie’s understanding of navigating our pricing system tables, we can now save all these queries as trusted queries by adding SQL Queries to our Genie context as well as sample questions to our settings to automatically suggest the questions to users in the future! We only have to solve problems once.

Adding Contract Info and Discount values to Genie space for Exec Management

In many cases, Databricks users might have a direct contract with Databricks that provides them discounts to specific product SKUs. Those users, oftentimes, would like to know how much are they spending against their contract, how much of the contract they have left etc. The Contract term and value is unique to each customer and Databricks does not yet have this data in the System Tables. But in Genie, we can easily add contract terms and contract values as an instruction and guide Genie to use those values to answer contract related questions for executive to easily manage spend in natural language.

Now, let’s ask the question about how we are doing against the example contract we gave Genie above.

You can see that Genie used contract and discount values that we added to calculate the spend and compare against the contract amount.

Monitoring Usage with Tags

It is possible to add custom tags in Databricks to attribute workloads to specific workspaces, teams, projects, and users. As a platform manager, we might want to keep a track of the spend against a specific project, use case, or a team to manage my resources better.

The information about tags is stored in system.billing.usage table under customer_tags attribute which is of a STRUCT data type. There are multiple ways to retrieve the information from a STRUCT type attribute but we would like Genie to always use backticks `` because it can handle the case sensitivity. We can add that as an instruction as below:

Now lets take an example. We would like to monitor the usage for tags “demo” and “project” that we have added against specific resources.

Let’s review the query that was generated. We asked Genie to show the “discounted” spend for each “Product.” To do this, Genie followed three steps: (a) it used “billing_origin_product” to identify the product, (b) it applied discounts based on each product, and it used backticks `` to extract the “demo” and “project” tags.

SELECT
 `usage`.`billing_origin_product`,
 `usage`.`custom_tags`.`demo` AS `demo_tag`,
 `usage`.`custom_tags`.`project` AS `project_tag`,
 SUM(
   `usage`.`usage_quantity` * `list_prices`.`pricing`.`effective_list`.`default`
 ) AS `total_spend`
FROM
 `system`.`billing`.`usage`
   JOIN `system`.`billing`.`list_prices`
     ON `usage`.`sku_name` = `list_prices`.`sku_name`
WHERE
 `usage`.`billing_origin_product` IS NOT NULL
 AND `usage`.`custom_tags`.`demo` IS NOT NULL
 AND `usage`.`custom_tags`.`project` IS NOT NULL
GROUP BY
 `usage`.`billing_origin_product`,
 `usage`.`custom_tags`.`demo`,
 `usage`.`custom_tags`.`project`

Can Genie forecast the spend?

Yes! Databricks introduced AI Functions to apply AI, like text translation or sentiment analysis, on your data that is stored on Databricks. One of them is ai_forecast() which is designed to extrapolate time series data into the future. This time, we just need to add a SQL query as an instruction to answer such questions.

Now we would like to see my forecasted spend of the year after applying the discount.

What’s Next?

Now that the Genie space is set up, you can interact with it asking different questions such as:

1. What is the total spend of the platform last week, month or year.
2. What is the product wise (i-e Interactive, DLT, SQL) spent last week, month or year.
3. What is the spend by a specific workspace.
4. What is the “job” wise spend?
5. What are the clusters that have spent more than 1K?
6. What is the cluster-owner wise spend.

Full instruction set for the above Genie Room:

*Use system.billing.usage.billing_origin_product to answer Product related questions
*Use list_prices.pricing.effective_list.default to calculate price or spend;
*Use system.billing.usage/usage_metadata.job_id for job_id and exclude 'null' values;
*Use system.billing.usage.usage_metadata.cluster_id for cluster related usage, spend or pricing questions;
*Use system.billing.usage.usage_metadata.notebook_id for notebook related usage questions.


*Total_Contract_Value = 10000000
*Contract_Start_Date = '2025-01-01'
*Contract_End_Date = '2025-12-31'
*Use "Total_Contract_Value" , "Contract_Start_Date" and "Contract_End_Date". to measure the spend against the contract
*When pulling usage by tag, tag key/values can be inconsistent, so always as the user they want to normalized the case and spacing on all tags to find usage matching by usage.custom_tags


*SQL discount = 0.15
*ALL_PURPOSE discount = 0.10
*DLT discount = 0.20
*JOBS discount 0.15


*multiply total spend of an billing_origin_product with their respective discounts to get the actual spend.


*Use ai_forecast() function on total spend to forecast the spend for future


*Use system.billing.usage.custom_tags to answer Tags related questions

Chat Your Way to Cost Visibility with Databricks Genie was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author

Justin Kolpak, Sr. Solutions Architect @ Databricks

Introduction

To reduce the burden of writing and maintaining repetitive, boilerplate code, it’s often a best practice to build parameterized pipelines for the more common, repetitive tasks (for example, ingesting data from the source), and pass in the relevant inputs to those pipelines. The result is a solution that requires less development time, a smaller, more maintainable code base, and ultimately allows for more time to focus on the more interesting and value-add responsibilities.

With recent enhancements to the Databricks Workflows product, you can build out this entire pattern using Databricks native functionality! This blog presents 3 orchestration patterns to build robust configuration-driven, parameterized, SQL-based ETL pipelines with Databricks SQL and Workflows using the For Each task type and the new dynamic value references for SQL outputs.

Feature Overview

Logically, this configuration-driven pattern relies on three key pieces of functionality:

1. Write code with parameters: Of course, we’ll need the ability to write parameterized code, and luckily, this has long been supported in Databricks. In the examples to follow, we’ll show how this can be used with Databricks SQL Query Parameters using the :parameter syntax.
2. Retrieve and pass in parameters to each iteration of the loop: Once we have our parameterized code, we need to figure out which inputs to pass into the query. There are many ways to store these configurations and retrieve the values, but in this blog, we’ll focus on doing this in a SQL-first approach by retrieving the inputs with a Databricks SQL query and passing in the parameters via dynamic value references for SQL outputs.
3. Loop/iterate over a set of inputs: Finally, you need a looping construct to iterate through the parameters. For this, we’ll use the Databricks Workflows For Each Task Type.

Examples

Let’s dig into the details to show exactly how this pattern works. All of the code to follow is available in this GitHub Repository. The Workflows are represented as Databricks Asset Bundles, making it easy to recreate in your own workspace and follow along.

For context, we have a dataset stored as text-delimited files in cloud object storage that we’d like to ingest and process into bronze and silver layers. We’ll start with a simple base case and add on complexity from there.

Example 1: Simple For Each

Let’s start with the basics. In this example, we have a basic SQL query that creates a Delta Lake table using the COPY INTO function, which reads data from a file path and uses a few other options to properly parse a delimited file. I know this is a pattern that I’ll want to use more than once, so I write the query with Query Parameters.

-- Declare Variables
DECLARE OR REPLACE VARIABLE _create_table_sql STRING;
DECLARE OR REPLACE VARIABLE _copy_into_sql STRING;


-- Create Table
SET VAR _create_table_sql = 'CREATE TABLE IF NOT EXISTS ' || :tgt_catalog || '.' || :tgt_schema || '.' || :tgt_table || ' (' || :src_column_list || ' , _last_modified_ts timestamp' || ')
TBLPROPERTIES (delta.enableChangeDataFeed = true)';


SELECT _create_table_sql;
EXECUTE IMMEDIATE _create_table_sql;


-- COPY INTO Table
SET VAR _copy_into_sql = "COPY INTO " || :tgt_catalog || '.' || :tgt_schema || '.' || :tgt_table || "
BY POSITION
FROM (
 SELECT
     *
     , now() AS _last_modified_ts
 FROM '" || :src_path || "'
 )
FILEFORMAT = "|| :src_format || "
FORMAT_OPTIONS ('mergeSchema' = 'true', 'sep' = '"|| :src_sep || "')
COPY_OPTIONS('mergeSchema' = 'true')";


SELECT _copy_into_sql;
EXECUTE IMMEDIATE _copy_into_sql;

How can we go about applying this code on every file location and bronze table I’d like to produce? Historically, I might have reached for a Python for loop + the Databricks %run command or used an external orchestrator, each which introduces their own drawbacks.

Enter: The For Each task in Databricks Workflows. Here’s how it works:

Start out by creating a new task, and setting the Type to “For Each” and providing an array of inputs in the “Inputs” field. In this case, the Inputs align to the parameters in the query above:

[{"bronze_catalog":"justin_kolpak","
bronze_schema":"tpch_blog","bronze_table":
"bronze_customer",
"primary_key":"c_custkey",
"silver_catalog":"justin_kolpak",
"silver_schema":"tpch",
"silver_table":"silver_customer",
"src_column_list":"c_custkey int, c_name string, c_address string, c_nationkey int, c_phone string, c_acctbal decimal(15,2), c_mktsegment string, c_comment string, c_dummy string",
"src_format":"csv",
"src_header":"false",
"src_path":"/Volumes/justin_kolpak/tpch/raw/customer",
"src_sep":"|"},

{"bronze_catalog":"justin_kolpak","bronze_schema":"tpch_blog","bronze_table":"bronze_orders","primary_key":"o_orderkey","silver_catalog":"justin_kolpak","silver_schema":"tpch","silver_table":"silver_orders","src_column_list":"o_orderkey int, o_custkey int, o_orderstatus string, o_totalprice double, o_order_date date, o_orderpriority string, o_clerk string, o_shippriority int, o_comment string, o_dummy string","src_format":"csv","src_header":"false","src_path":"/Volumes/justin_kolpak/tpch/raw/orders","src_sep":"|"},{"bronze_catalog":"justin_kolpak","bronze_schema":"tpch_blog","bronze_table":"bronze_lineitem","primary_key":"l_orderkey","silver_catalog":"justin_kolpak","silver_schema":"tpch","silver_table":"silver_lineitem","src_column_list":"l_orderkey int, l_partkey int, l_suppkey int, l_linenumber int, l_quantity decimal(15,2), l_extendedprice decimal(15,2), l_discount decimal(15,2), l_tax decimal(15,2), l_returnflag string, l_linestatus string, l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct string, l_shipmode string, l_comment string, l_dummy string","src_format":"csv","src_header":"false","src_path":"/Volumes/justin_kolpak/tpch/raw/lineitem","src_sep":"|"}]

Then from there, add in the “inner” task that the For Each operator will iterate through.

And that’s it!

When this job runs, the tasks all run concurrently and the UI shows execution details at the iteration-level, making it simple to monitor, observe, and debug issues with this pipeline. In the absence of a capability like this, we would likely only see 1 Success (if everything succeeded) or 1 Failure (if at least one of the components failed) at the job- or task-level, and any troubleshooting efforts would begin with digging through the code/logs trying to find out what specifically caused the failure. By having iteration-level details, we can immediately see what succeeded, what failed, how long it took, what parameters it used, what the code looked like, etc.

Example 2: For Each + Dynamic Value Reference for SQL Outputs

What if the list of inputs is constantly changing over time? In the real world, things are always in flux, being added, removed, or modified, and it’s not practical to go into the Workflow and update the “Inputs” field every time there’s a change. Instead, let’s store these inputs in a configuration file (using a JSON file here, but a table or a YAML file would work great too!) and see how we can fit them into this Workflow.

In this Workflow, we’ve added an initial task that queries the configuration table/file and returns a result set with all of the parameters to pass into the subsequent task. Then using task values, we can pass in these parameters to downstream tasks.

Task values have been around for a long time with Python, which would require explicitly setting the task values at the end of the get_ingest_metadata task. Now, with dynamic value references for SQL outputs, we 1) can use SQL to do this and 2) don’t need to explicitly set task values — we just need to write a SQL query that returns a meaningful result set and use it through the rest of the workflow.

Here’s the get_ingest_metadata task — it’s a SQL query that returns a result set with all of the parameters needed downstream.

Instead of hardcoding the values in the “Inputs” field, as in Example 1 above, this Workflow can be more dynamic by setting the “Inputs” field to {{tasks.get_ingest_metadata.output.rows}}. With this configuration, the Workflow will use the result set of the previous SQL task (get_ingest_metadata) and pass them in as an input to the For Each operator (load_bronze, in this case).

Example 3: For Each + Dynamic Value References + Run Job

In the examples above, the For Each task is iterating over just a single task. What if the task we want to iterate over is more complex than just 1 task? Enter: the Run Job task type, which supports the ability to run another job from the current job. This provides the ability to build highly modular, DRY (Don’t Repeat Yourself) workflows, and when combined with For Each and dynamic value references, creates a very robust, maintainable, and observable pipeline — all with SQL!

Instead of the inner task using a SQL File or Query, we can set it to “Run Job”. If there are needs for more advanced logic than can reasonably be expressed with a single task (as in Examples 1 and 2), being able to build a separate job with orchestration logic provides more flexibility in your workflow definitions.

In this example, it’s a relatively straightforward job consisting of two tasks to be run in sequence, but this pattern can be extended to much more complex tasks. For example, you may want to build conditional control flow logic into your workflow, or you may find yourself with code written in different languages (e.g. a SQL query and a Python script) that you’d like to orchestrate as part of the same process.

By using this pattern, you can handle highly complex orchestration patterns, while still taking advantage of a configuration-driven approach facilitated by the dynamic value references and For Each operator.

How To Get Started

All of the resources in this blog are available on this GitHub Repository as a Databricks Asset Bundle. Follow the instructions in the Getting Started section.

Conclusion

These examples are just scratching the surface on what can be accomplished with these tools. Workflows supports a variety of advanced orchestration operators that we didn’t have a chance to discuss (like conditional execution, run dbt projects or DLT pipelines, and much more). By putting all of these tools together, inspired by the patterns outlined in this blog, you can build highly sophisticated data pipelines using just Databricks Workflows + DBSQL.

If you have any questions, feedback, or ideas on how this can be extended to other use cases, feel free to reach out to me at justin.kolpak@databricks.com.

Note: All code is provided as-is and is not subject to support SLAs. The examples in this code base are specifically designed to work with the TPC-H dataset, and any production application of this will require some level of customization to work with your datasets and requirements. The intention of publishing these examples is to highlight the high-level design patterns that you can build using Databricks SQL and Workflows.

Databricks SQL Orchestration Patterns with For Each and Dynamic Value References was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Authors

Patrick Sciortino, Sr. Solutions Engineer at Databricks
Heeren Sharma, Sr. Solutions Architect at Databricks

Introduction

Having a unified data catalog that enables comprehensive data discovery and governance is essential for organizations striving to effectively manage their data estate and maximize the value they can derive from data and AI. However, integrating data from different sources is an ongoing endeavor as new operational systems emerge and organizational structures evolve. This article examines two key strategies for integrating and managing diverse data sources through Unity Catalog in the Databricks Data Intelligence Platform context: data ingestion and data federation, enabled by lakehouse federation. We’ll explore each approach’s advantages, drawbacks, and ideal use cases, helping you make informed decisions for your data management needs.

Data Ingestion

There are various ways to ingest data into the Databricks Data Intelligence Platform; a deep dive is out of scope for this article and is extensively documented here. Having said that, the following end result is quite common regardless of the specific ingestion method:

• A data ingestion pipeline copies the data from the source system to the organization’s Data Lake (generally to an area called landing zone, staging area or Bronze layer, depending on naming preferences). These pipelines are typically built to perform incremental data ingestion, in other words, to only load new changes from the source system once historical data has been copied.
• A Unity Catalog table is registered that points to that data in the Data Lake (where data is ingested directly into an optimized data format) or to a corresponding format-optimized version of it (in cases where data is ingested in its raw format first, before being converted). In itself, the Unity Catalog table (as shown in the following figure) is simply a metadata artefact that enables easy consumption and governance of that data, and storing the underlying data in an open-source and optimized format like Delta is a way to ensure optimal compaction and query performance as well as to guarantee ACID properties and other benefits as mentioned here while embracing the benefits of an open Data Lakehouse approach.

Several tools can help streamline the setup of such data ingestion pipelines:

• Lakeflow Connect is the Databricks-native data ingestion product that leverages Change Data Capture for optimal incremental data ingestion out of the box. It also stores the data in the optimized and open-source Delta format and registers the corresponding Unity Catalog table without additional overhead. External technology partners like Fivetran, Qlik, etc. also deliver the same managed experience.
• For real-time or near real-time requirements, Databricks can natively ingest data from event buses like Apache Kafka, AWS Kinesis, Confluent Cloud, Amazon MSK, or Azure Event Hubs using Delta Live Tables or Spark Structured Streaming and load it directly into a Unity Catalog table.

Another typical pattern involves copying the data to the Data Lake in its original format using a standard data integration tool like AWS Glue, Azure Data Factory, or Google’s Cloud Data Fusion, and then loading data from the corresponding file location into a Delta table via COPY INTO or Auto Loader.

Regardless of the chosen approach, the result is that source data is being centralized in the Delta Lake, which offers optimal query speed and scalability, leveraging Databricks’ performance optimizations. A prerequisite is that data engineering or data platform teams are required to develop and maintain data ingestion pipelines, which, depending on the case and the chosen approach, may involve more or less complex implementation effort before any business value can be delivered from the data.

Data Federation with Lakehouse Federation

Now, what if the organization’s data could be managed and queried via Unity Catalog without copying that data from where it lives? That would mean any query would point to the freshest operational data while saving storage costs and avoiding discrepancies between the analytical and operational worlds. Isn’t that like having your cake and eating it too? To nuance this, one needs to consider the exact implications of that approach.

Lakehouse Federation is Databricks’ native query federation solution. It enables Unity Catalog data consumers (users and systems) to leverage read-only managed JDBC connections to popular database solutions and access external catalogs without needing to migrate the underlying data physically. Another benefit is that federated tables and respective connections can be governed alongside all other data and AI assets in Unity Catalog — allowing, for example, Unity Catalog admins to set granular permissions on these federated tables, or to visualize their downstream dependencies as part of Unity Catalog’s out-of-the-box lineage graph.

Enabling access and centralized governance on dozens or even hundreds of tables from external systems in one go and without data copy is undoubtedly attractive in specific scenarios (more on that later). However, an important implication of this setup is that all queries on federated tables are effectively executed directly on the source system. This pushback of query workloads to the source can be just fine in some cases (e.g. in case of small data volumes and infrequent queries) but comes with the risks of causing throttling on the source system, generating unnecessarily high egress traffic, or lowering query performances, depending on the specific source and on the query patterns (frequency, complexity, and returned data volume).

Lakehouse Federation + Materialized Views for Data Materialization

Because of the above mentioned risks, avoiding hitting the source system at every query is often desirable. In that case, a common (and usually recommended) approach is to complement Lakehouse Federation with Materialized Views. Materialized Views in Databricks are a special type of database object that stores the result of an SQL query as a physical table and keeps it up-to-date over time by refreshing (or recomputing the result) on an arbitrary schedule. Unlike regular views, which resolve the underlying data at query time, materialized views’ precomputation provides a powerful way to enhance the performance of downstream data processing and analysis workloads, making it easy to deliver fresh data quickly and efficiently.

However, Materialized Views — when pointing to Federated Tables –have a drawback. They can’t update incrementally, so the entire dataset must be reloaded to reflect fresh data. This can be expensive and cause high data transfer if a large dataset needs frequent updates. Besides, it is critical to ensure that these full refresh workloads, being pushed to the source, don’t create throttling and thereby slow down other data processing workloads or worse, operational processes.

That being said, the Lakehouse Federation + Materialized Views pattern offers a way to quickly enable access and governance on external sources without compromising downstream query performances — making it a fantastic tool to accelerate value delivery in the first phase of a migration project, enable quick Proof of Concepts or even as a long-term solution when it has been validated that the full refreshes of Materialized Views are viable for the case at hand. This article covers the approach in more detail and offers helpful code snippets to get started with such an implementation.

Comparison Matrix

The following table summarizes the primary considerations for the data integration options we discussed and typical use cases for each approach.

The case for a Hybrid Approach

Many organizations find that combining data integration approaches creates an optimal balance between performance, cost, and operational efficiency. To illustrate this, let’s consider BrightPath, a hypothetical global education services leader offering online learning platforms and tools to schools, universities, and corporate clients. The company uses Salesforce as its central CRM to manage customer relationships, track sales opportunities, and provide personalized marketing campaigns. BrightPath started its journey with Databricks last year to unify data and AI workflows at scale.

BrightPath realized that they could ingest data from Salesforce efficiently using Lakeflow Connect. It required little overhead to set up with a few clicks in the UI. Powered by serverless compute, a managed, robust pipeline experience efficiently ingests any new or changed data from the desired Salesforce objects into Delta tables every 6 hours. All while governed through Unity Catalog.

Brightpath also wanted to provide an improved and automated content recommendation on their portal using advanced machine learning models. Furthermore, to enhance user engagement, it would like to leverage generative AI to generate relevant exercises for a student given their learning history, previous scores and learning goals. To achieve this, Databricks-hosted AI models like state of the art Llama-4 models, needed to be fed by real-time data on platform user activity, which is processed from the application to the Lakehouse in near real time by having the user session data sent to BrightPath’s event bus like a Kafka topic and then read by Delta Live Table pipelines.

More recently, BrightPath acquired HighPotential, a mid-market enterprise focusing on AI-driven simplification of highly technical content for educational purposes. While the longer-term plan following the acquisition is to consolidate the technical landscape of the new organization, business leaders needed quick access to the analytics-ready data processed through HighPotential’s data warehouse, BigQuery. This could be easily achieved by setting up Lakehouse federation to run federated queries on BigQuery data. Because this data is often queried and joined with BrightPath’s Salesforce data, adding a layer of materialized views allowed the conglomerate to augment existing analytical reports with the new information without altering the refresh performances of those user-facing reports.

By combining this hybrid approach, BrightPath can execute on its migration and unification of various data assets to the Data Intelligence Platform while instantly unlocking valuable insights to technical and business users.

Conclusion

Using the right tool for the right job:

• We discussed data federation and data ingestion as two alternative approaches to integrating data into the Lakehouse. Each approach has pros and cons, but data ingestion is often more scalable and, when properly implemented, offers an effective and reliable solution. Lakeflow Connect or ingestion partner tools such as Fivetran can speed up the setup of end-to-end incremental data ingestion pipelines by significantly lowering the implementation barrier.
• On the other hand, data federation, facilitated by Lakehouse Federation, can speed up PoCs and migrations (covered more in detail in this blog) as it allows data teams to deliver business value before data ingestion pipelines are fully developed. It can also be a valid approach for long-term solutions where the scope of the Materialized Views query or their freshness requirements are such that the benefits of avoiding data duplication balance out the lower efficiency compared to incremental ingestion methods. This blog post is a recommended read for more details on the approach.
• As is often the case, there is no one-size-fits-all approach for data integration, and we observe that large organizations frequently adopt hybrid strategies. Hopefully, this article will give you the keys to designing your organization’s optimal data integration pathway!

Related articles :

• Lakehouse Federation — Single Pane of Glass for all Your Data
• Navigating Your Migration to Databricks: Architectures and Strategic Approaches

Data Federation vs Data Ingestion: updated guidance on an optimal strategy for the integration of… was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author: Cody Austin Davis

Introduction

In a previous article, we showed how to set up performance SLA alerting and monitoring with DBSQL Alerts, Slack, and PagerDuty. This allows warehouse admins to monitor their warehouse SLAs with a suite of tools such as the Warehouse Advisor Dashboard and the Alerting System defined in our previous article to achieve real-time intel as well as deep analysis of your warehouses on Databricks.

Now, in addition to deep analysis with dashboards and SLA alerting with Alerts, we can now go even further and react faster to issues that arise with one of the coolest new feature in Unity Catalog — HTTP API federation.

This feature allows users to register REST API endpoints as governed connections as well as use that API directly in SQL — all without needing to expose and hardcode credentials in the code itself. This opens up a ton of use cases, but for observability, this allows us to poll the live query history api to monitor queries as they come in real-time. This offers the advantage of much more real-time observability compared to dashboards and SQL alerts based on system tables, which can be delayed. With this example, we can poll the query history real-time to quickly identify and react to information like long running queries, queued queries for a long time, high data scanning queries, errors, and anything that we can find in the query history/profile results! Lets create a simple example that can be expanded to any need for query analysis.

Steps:

1. Create a UC Connection to Query History
2. Write SQL query to identify long running queries stuck in progress
3. Create an alert to trigger and show the long running queries
4. Schedule that alert and monitor the job

Step 1 — Create UC Connection to Query History

The first thing we do it create a UC connection to register our query history api to our workspace as a governed connection. To do this, go to Catalog → External Data → Connections in the workspace.

Create a new HTTP connection — call it Query History API. Pick your auth type and hit “next”

Put in your workspace url, token / auth info, and then make your base path the query history api from here. Hit Save!

Now you can use you connection in a SQL query with the http_request function.

Step 2 — Write a SQL Query to get long running queries from the API in real time

Now we can write a SQL query to ping the query history API in real time to poll for long running queries. Here is an example:

WITH raw_results AS (
  SELECT http_request(
    conn => 'QueryHistoryAPI',
    method => 'GET',
    path => '/',
    json => to_json(named_struct(
      'warehouse_ids', "['<warehouse_ids>']",
      'statuses', "['QUEUED', 'RUNNING']"
    )
    )) AS raw_resp,
    current_timestamp() AS snapshot_time
),

-- Put the raw STRING result into a VARIANT to parse
clean_results AS (
SELECT 
explode(PARSE_JSON(raw_resp.text):res::array<variant>) AS clean_resp,
snapshot_time
FROM raw_results
WHERE raw_resp.status_code::integer == 200
),

-- Calculate the Query runtime and pull any other metrics you want to track from the variant
query_runtime_cals AS (
SELECT 
clean_resp AS queries,
queries:query_start_time_ms::long AS query_start_time_ms,
snapshot_time,
timestamp_millis(query_start_time_ms) AS query_start_time,
(unix_timestamp(snapshot_time) - query_start_time_ms / 1000.0) AS runtime_from_snapshot_seconds
FROM clean_results
)

-- Add a filter for queries that are still running longer than 60 seconds!
SELECT 
*
FROM query_runtime_cals
WHERE runtime_from_snapshot_seconds >= 60 -- SLA / Alert trigger in seconds

This SQL statement pulls from the Query History API, parses the raw response into a VARIANT column, and then gets queries that are still running or queued for longer the 60 seconds from when it started to when we polled for its status. This will get us a list of all queries that are running too long for our preference like this:

The query is also very fast! Running each poll under a second or two now allows us to trigger alerts or submit this query from any application (try building a data app with it!) and get these long running queries in real time! For our example, lets just start with an alert that runs every minute. First we save this query to our queries registry by clicking “Save” at the top right corner. This query is called “RealTime_SLA_Alert”.

Step 3&4 — Create a DBSQL Alert to show long running queries and schedule it

Now we can take this sql and use it in an alert. To do this, we navigate to the left-hand side of the workspace under the “SQL” tab and click “Alerts”.

We simply select our query we just created, make the trigger condition be when the count of queries that are long running is > 0, and add any formatting we want to our notification email. Once we do this, we can click the “Add Schedule” button and make our alert trigger every 1 minute.

Once the schedule is saved, this creates a Databricks Workflow that runs the schedule refresh process on the DBSQL warehouse we choose, and we have full observability of that schedule by clicking into the workflow from the Alert.

Clicking “View workflow details” takes us to our job that is running our alerts every minute!

Conclusion

Having the ability to use API endpoints directly in SQL all within a safely governed catalog creates endless use cases, but especially for real time observability! Try it out, and see what you can create with http_request and alerts!

Real-Time Query Monitoring on DBSQL with Alerts was originally published in DBSQL SME Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.