Until recently, implementing complex multi-step logic in the Apache Spark ecosystem required stepping outside declarative SQL. Sequencing calls, computing intermediate values, and branching on conditions all demanded external languages such as Python (PySpark) or Scala, along with additional tooling.
Spark SQL Scripting, available starting with Spark 4, fundamentally changes this by introducing a procedural extension to classic Spark SQL. Developers can now write full multi-step scripts directly within SQL artifacts, embedding control-flow logic alongside their data operations.
In this post, the Alphyn.AI Team walks through Spark SQL Scripting capabilities with hands-on examples.
Let's start by identifying the typical use cases where Spark SQL Scripting is a strong fit:
Batch DDL/DML sequences
Sequential data preparation statements and transformations — creating or recreating tables, populating them with computed results, and updating objects in the target storage layer (SCD1 and SCD2 scenarios).Data mart preparation and population
Implementing the classic steps of loading from a staging layer into target tables with mandatory intermediate-state checks.Data quality and sanity checks
Computing quality metrics inline during script execution, comparing them against thresholds, and deciding whether to continue or halt with an error.Runbook operations
Running scheduled or on-demand infrastructure maintenance scripts — for example, cleaning up temporary objects or collecting system statistics.
All of these cases typically involve dynamic SQL, which we treat as a common underlying capability rather than a separate scenario. Generating executable SQL from variables or metadata is often the primary reason for reaching for any procedural extension in the first place.
Spark SQL Scripting is controlled by the configuration flag:
spark.sql.scripting.enabled
Spark SQL Script Structure
Every script in the Spark SQL procedural extension is a compound statement — a logical block strictly bounded by the BEGIN ... END keywords.
Inside this block, two logical sections are distinguished:
Declaration section
Where the developer declares local variables, conditions, and error handlers.Script body
The actual sequence of statements to execute.
The script body supports a broad set of constructs:
Conditional branching with IF and CASE;
Looping constructs: LOOP, WHILE, REPEAT, and the specialized FOR loop for iterating over a SQL query result set;
Loop control operators: LEAVE (equivalent to break) and ITERATE (equivalent to continue);
Standard DDL and DML commands (CREATE, DROP, INSERT, etc.);
Plain SELECT statements that return result sets to the caller;
Variable assignment via SET and SET VAR;
Dynamic execution via EXECUTE IMMEDIATE;
Nested BEGIN ... END blocks for scope isolation.
A critical architectural point: compound statements execute in NOT ATOMIC mode. In practice, this means that if one statement inside the block fails, previously completed steps are not automatically rolled back.
This also means pipeline logic must be designed with an absolute focus on idempotency and safety. If a script fails halfway through, you will need to either compensate for changes manually or design each step so that re-running it does not cause data duplication or corruption.
Another important current limitation (as of April 2026): there is no CREATE STORED PROCEDURE command for subsequent invocation via a CALL statement.
Working with Variables
Procedural SQL is inseparable from mechanisms for preserving intermediate state. Spark SQL Scripting offers two levels of variable management.
Two types of variables are available:
Local variables
Declared strictly inside a BEGIN ... END block using the DECLARE keyword; they exist only for the duration of that block's execution.
Nested BEGIN ... END blocks create their own local scopes. It's important to be aware of shadowing: if an inner block declares a variable with the same name as one in an outer block, the inner declaration takes precedence for the duration of the sub-block.Session variables
When you need to pass values between physically separate SQL statements or scripts within the same Spark session, session variables are the answer:
Declaration:
DECLARE VARIABLE ...Updating a value:
SET VAR ...
Important syntax note: The SET command without the VAR keyword applies to Spark configuration settings (SQLConf) and cannot work with SQL variables.
Practical Examples
To reproduce the examples below, create the test table with the following script:
DROP TABLE IF EXISTS demo_orders;
CREATE TABLE demo_orders (
order_id BIGINT,
customer_id BIGINT,
region STRING,
amount DECIMAL(12,2),
order_ts TIMESTAMP,
status STRING, -- NEW / OK / CANCELLED
ingestion_dt DATE
) USING parquet;
INSERT INTO demo_orders VALUES
(1001, 501, 'EU', 10.00, TIMESTAMP '2026-03-01 10:00:00', 'NEW', DATE '2026-03-01'),
(1002, 501, 'EU', 35.50, TIMESTAMP '2026-03-01 11:00:00', 'NEW', DATE '2026-03-01'),
(1003, 502, 'EU', 120.00, TIMESTAMP '2026-03-01 12:00:00', 'OK', DATE '2026-03-01'),
(1004, 503, 'US', 5.00, TIMESTAMP '2026-03-01 10:30:00', 'NEW', DATE '2026-03-01'),
(1005, 503, 'US', 60.00, TIMESTAMP '2026-03-01 13:00:00', 'OK', DATE '2026-03-01'),
(1006, 504, 'US', 15.00, TIMESTAMP '2026-03-02 09:00:00', 'OK', DATE '2026-03-02'),
(1007, 505, 'APAC',200.00, TIMESTAMP '2026-03-02 10:00:00', 'OK', DATE '2026-03-02'),
(1008, 506, 'APAC', 12.00, TIMESTAMP '2026-03-02 11:00:00', 'NEW', DATE '2026-03-02'),
(1009, 507, 'APAC', 90.00, TIMESTAMP '2026-03-03 08:00:00', 'OK', DATE '2026-03-03'),
(1010, 508, 'EU', 1.00, TIMESTAMP '2026-03-03 09:00:00', 'NEW', DATE '2026-03-03');Scenario: run the same script with different parameters (date / region / threshold) without modifying the script text.
Session parameters (declare once per session; after that, only SET VAR is needed):
DECLARE VARIABLE p_dt DATE DEFAULT DATE '2026-03-01';
DECLARE VARIABLE p_region STRING DEFAULT 'EU';
DECLARE VARIABLE p_min_amount DECIMAL(12,2) DEFAULT 50.00;
SET VAR p_dt = DATE '2026-03-02';
SET VAR p_region = 'APAC';
SET VAR p_min_amount = 10.00;Script:
BEGIN
DECLARE v_dt DATE DEFAULT session.p_dt;
DECLARE v_region STRING DEFAULT session.p_region;
DECLARE v_min DECIMAL(12,2) DEFAULT session.p_min_amount;
SELECT
v_dt AS ingestion_dt,
v_region AS region,
v_min AS min_amount,
COUNT(*) AS cnt,
CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2)) AS sum_amount
FROM demo_orders
WHERE ingestion_dt = v_dt
AND region = v_region
AND amount >= v_min;
END;
Scenario: compute batch metrics for a given date.
BEGIN
DECLARE v_dt DATE DEFAULT session.p_dt;
DECLARE v_cnt BIGINT;
DECLARE v_sum DECIMAL(12,2);
SET v_cnt = (SELECT COUNT(*) FROM demo_orders WHERE ingestion_dt = v_dt);
SET v_sum = (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE ingestion_dt = v_dt
);
SELECT v_dt AS ingestion_dt, v_cnt AS rows_cnt, v_sum AS amount_sum;
END;In complex queries, name conflicts can arise between variable names and column names in tables. By default, Spark attempts to resolve a name as a column or alias first, and only falls back to treating it as a variable if no such column exists.
To avoid accidental logic errors and naming collisions, it is recommended to qualify variables explicitly with one of these prefixes:
session.<variable_name>
system.session.<variable_name>
Limitation: session variables cannot be used in persistent (metastore-persisted) objects — such as saved views, column default expressions, or generated columns. Their lifecycle is bound to the current active session only.
Dynamic SQL
EXECUTE IMMEDIATE executes a SQL statement constructed as a string.
It supports:
USING — parameter binding (positional or named);
INTO — writing the result of a single-row query into variables:
0 rows → NULL;
> 1 row → error;
INTO is allowed only for queries (not for DDL/DML).
Key limitations to keep in mind:
nested EXECUTE IMMEDIATE is not allowed;
the string passed to EXECUTE IMMEDIATE must not itself contain a SQL Script — BEGIN … END blocks are not permitted inside it.
Dynamic query construction is a powerful but risky technique. Spark SQL Scripting provides built-in mechanisms to minimize SQL injection risks.
If you need to substitute a dynamic table name, schema, or function name into a query, never do so via plain string concatenation. Use the built-in function:
IDENTIFIER(strExpr) — a safe identifier templating mechanism that reduces the risk of syntax errors and malicious injection.
Let's see these in action.
Scenario: dynamic query with value binding and scalar capture into variables. EXECUTE IMMEDIATE + USING + INTO
BEGIN
DECLARE v_region STRING DEFAULT 'EU';
DECLARE v_min DECIMAL(12,2) DEFAULT 20.00;
DECLARE v_cnt BIGINT;
DECLARE v_sum DECIMAL(12,2);
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM demo_orders WHERE region = ? AND amount >= ?'
INTO v_cnt
USING v_region, v_min;
EXECUTE IMMEDIATE
'SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE region = :r AND amount >= :m'
INTO v_sum
USING (v_region AS r, v_min AS m);
SELECT v_region AS region, v_min AS min_amount, v_cnt AS cnt, v_sum AS sum_amount;
END;Example using IDENTIFIER(strExpr). Scenario: table name provided as a string (safe identifier substitution).
BEGIN
DECLARE v_table STRING DEFAULT 'demo_orders';
DECLARE v_cnt BIGINT;
SET v_cnt = (SELECT COUNT(*) FROM IDENTIFIER(v_table));
SELECT v_table AS table_name, v_cnt AS row_count;
END;Control Flow: Conditionals and Loops
Control flow constructs are among the most valuable features of any procedural extension — they allow scripts to react to data on the fly. Together with dynamic SQL, they are the most heavily used capabilities.
The following conditional branching operators are available inside blocks:
IF ... THEN ... ELSEIF ... ELSE ... END IF
CASE ... END CASE
These are ideal for acting on previously computed metrics. For example, after counting invalid rows, you can route the script either to a data-commit branch or to an alert-generation branch.
Examples
Scenario: data quality gate for a batch — if negative amounts exist, stop; otherwise, proceed.
BEGIN
DECLARE v_dt DATE DEFAULT session.p_dt;
DECLARE v_bad BIGINT;
DECLARE v_decision STRING;
SET v_bad = (SELECT COUNT(*) FROM demo_orders WHERE ingestion_dt = v_dt AND amount < 0);
IF v_bad = 0 THEN
SET v_decision = 'PROCEED';
ELSEIF v_bad < 10 THEN
SET v_decision = 'PROCEED_WITH_WARNING';
ELSE
SET v_decision = 'STOP';
END IF;
SELECT v_dt AS ingestion_dt, v_bad AS bad_rows, v_decision AS decision;
END;Scenario:
A CASE statement selects the commission rate by region (parameter session.p_region);
A CASE expression labels rows by order size.
BEGIN
DECLARE v_region STRING DEFAULT session.p_region;
DECLARE v_fee_rate DECIMAL(6,4);
CASE v_region
WHEN 'EU' THEN SET v_fee_rate = CAST(0.0200 AS DECIMAL(6,4));
WHEN 'US' THEN SET v_fee_rate = CAST(0.0300 AS DECIMAL(6,4));
WHEN 'APAC' THEN SET v_fee_rate = CAST(0.0500 AS DECIMAL(6,4));
ELSE SET v_fee_rate = CAST(0.0100 AS DECIMAL(6,4));
END CASE;
SELECT
order_id,
region,
amount,
CASE
WHEN amount >= 100 THEN 'HIGH'
WHEN amount >= 50 THEN 'MEDIUM'
ELSE 'LOW'
END AS amount_band,
CAST(amount * v_fee_rate AS DECIMAL(12,2)) AS fee
FROM demo_orders
WHERE region = v_region
ORDER BY order_id;
END;The following looping constructs are available:
LOOP ... END LOOP — an infinite loop requiring an explicit exit;
WHILE <condition> DO ... END WHILE — pre-condition loop;
REPEAT ... UNTIL <condition> END REPEAT — post-condition loop;
FOR [var AS] <query> DO ... END FOR — a powerful construct for iterating over a query result set row by row.
For flexible iteration control, two operators are available:
LEAVE <label> — immediately exits the labeled block or loop;
ITERATE <label> — jumps to the next iteration of the labeled loop.
Labels are required when dealing with nested loops so the interpreter knows exactly which loop to exit or which iteration to skip.
Practical Examples
Scenario: scan orders by order_id, skip small ones below 10 (ITERATE), exit once the accumulated total reaches the threshold (LEAVE).
BEGIN
DECLARE v_threshold DECIMAL(12,2) DEFAULT CAST(250 AS DECIMAL(12,2));
DECLARE v_sum DECIMAL(12,2) DEFAULT CAST(0 AS DECIMAL(12,2));
DECLARE v_last BIGINT DEFAULT 0;
DECLARE v_next BIGINT;
DECLARE v_amt DECIMAL(12,2);
scan: LOOP
SET v_next = (SELECT MIN(order_id) FROM demo_orders WHERE order_id > v_last);
IF v_next IS NULL THEN LEAVE scan; END IF;
SET v_amt = (SELECT amount FROM demo_orders WHERE order_id = v_next);
SET v_last = v_next;
IF v_amt < 10 THEN
ITERATE scan; -- continue
END IF;
SET v_sum = v_sum + v_amt;
IF v_sum >= v_threshold THEN
LEAVE scan; -- break
END IF;
END LOOP scan;
SELECT v_last AS last_order_id, v_sum AS accumulated_sum;
END;Scenario: iterate over a date range (from session parameters) and accumulate the total amount.
Range parameters:
DECLARE VARIABLE p_from_dt DATE DEFAULT DATE '2026-03-01';
DECLARE VARIABLE p_to_dt DATE DEFAULT DATE '2026-03-03';
SET VAR p_from_dt = DATE '2026-03-01';
SET VAR p_to_dt = DATE '2026-03-03';Script:
BEGIN
DECLARE v_dt DATE DEFAULT session.p_from_dt;
DECLARE v_end DATE DEFAULT session.p_to_dt;
DECLARE v_days INT DEFAULT 0;
DECLARE v_total DECIMAL(12,2) DEFAULT CAST(0 AS DECIMAL(12,2));
DECLARE v_day_sum DECIMAL(12,2);
WHILE v_dt <= v_end DO
SET v_day_sum = (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE ingestion_dt = v_dt
);
SET v_total = v_total + v_day_sum;
SET v_days = v_days + 1;
SET v_dt = date_add(v_dt, 1);
END WHILE;
SELECT v_days AS days_processed, v_total AS total_amount;
END;Scenario: polling with a retry limit — wait for NEW >= target, or exit after max attempts.
Script:
BEGIN
DECLARE v_target BIGINT DEFAULT 10; -- intentionally larger than the data contains
DECLARE v_attempt INT DEFAULT 0;
DECLARE v_max_attempts INT DEFAULT 4;
DECLARE v_cnt BIGINT DEFAULT 0;
DECLARE v_reason STRING;
REPEAT
SET v_attempt = v_attempt + 1;
SET v_cnt = (SELECT COUNT(*) FROM demo_orders WHERE status = 'NEW');
UNTIL v_cnt >= v_target OR v_attempt >= v_max_attempts
END REPEAT;
IF v_cnt >= v_target THEN
SET v_reason = 'FOUND';
ELSE
SET v_reason = 'MAX_ATTEMPTS';
END IF;
SELECT v_cnt AS new_cnt, v_attempt AS attempts, v_reason AS stop_reason;
END;Scenario: iterate over distinct regions and sum orders with amount >= min (result-set iteration).
Script:
BEGIN
DECLARE v_min DECIMAL(12,2) DEFAULT CAST(10 AS DECIMAL(12,2));
DECLARE v_regions INT DEFAULT 0;
DECLARE v_total DECIMAL(12,2) DEFAULT CAST(0 AS DECIMAL(12,2));
FOR r AS
SELECT DISTINCT region
FROM demo_orders
ORDER BY region
DO
SET v_total = v_total + (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE region = r.region AND amount >= v_min
);
SET v_regions = v_regions + 1;
END FOR;
SELECT v_regions AS regions_cnt, v_min AS min_amount, v_total AS total_amount;
END;Exception Handling and System Handlers
To prevent scripts from failing silently or leaving behind locked resources and dirty data, you can define error handlers.
The DECLARE EXIT HANDLER FOR construct lets you react to failures. The handler catches the error, executes the specified logic, and fully stops execution of the current compound statement.
Handlers can be configured for:
Specific named error classes;
Standard SQLSTATE codes;
The general SQLEXCEPTION condition;
The NOT FOUND condition (useful when reading data where the absence of rows is a valid business scenario).
Scenario: catch a divide-by-zero error and return a meaningful result instead of crashing.
-- To ensure division by zero is an error (not NULL), enable ANSI mode.
SET spark.sql.ansi.enabled = true;
BEGIN
DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
h: BEGIN
SELECT 'FAILED' AS status, 'DIVIDE_BY_ZERO' AS error;
END h;
-- trigger the error
SELECT 1 / 0;
SELECT 'OK' AS status; -- will not execute
END;Scenario: any error produces a clear final result.
BEGIN
DECLARE v_step STRING DEFAULT 'start';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
h: BEGIN
SELECT 'FAILED' AS status, v_step AS failed_step;
END h;
SET v_step = 'compute';
SELECT 1 / 0; -- error for demonstration
SET v_step = 'never';
SELECT 'OK' AS status;
END;Scenario: an error on one loop iteration does not fail the entire run — count warnings and continue.
BEGIN
DECLARE v_warn INT DEFAULT 0;
DECLARE v_total DECIMAL(12,2) DEFAULT CAST(0 AS DECIMAL(12,2));
FOR r AS
SELECT DISTINCT region FROM demo_orders ORDER BY region
DO
maybe_fail: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
h: BEGIN
SET v_warn = v_warn + 1;
END h;
IF r.region = 'APAC' THEN
SELECT 1 / 0; -- deliberately break one step
END IF;
END maybe_fail;
SET v_total = v_total + (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE region = r.region
);
END FOR;
SELECT v_total AS sum_amount, v_warn AS warnings;
END;Scenario: a row is inserted, then an error is raised — the insert remains visible (on first run after Spark 4.0 there will be 1 row). Successful steps are not automatically rolled back!
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
h: BEGIN
SELECT COUNT(*) AS test_rows
FROM demo_orders
WHERE status = 'TEST_FAIL';
END h;
INSERT INTO demo_orders VALUES
(9999, 999, 'EU', 0.01, current_timestamp(), 'TEST_FAIL', current_date());
SELECT 1 / 0; -- error
END;Working with Nested Blocks
Nested BEGIN ... END blocks in Spark SQL Scripting are not merely a cosmetic way to structure code — they are a mechanism for controlling error isolation, variable visibility, and structural clarity.
Nested blocks let you visually and logically separate a script into stages: an initialization block, a transformation block, and a cleanup block.
Blocks can be given labels (e.g., step_1: BEGIN ... END step_1), which makes debugging easier and helps pinpoint exactly which stage failed or stalled.
Example
Scenario: demonstrate that a variable in a nested BEGIN ... END can shadow the outer one without breaking the outer context.
BEGIN
DECLARE v_region STRING DEFAULT 'EU';
DECLARE v_outer_sum DECIMAL(12,2);
DECLARE v_inner_sum DECIMAL(12,2);
SET v_outer_sum = (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE region = v_region AND status IN ('NEW','OK','CANCELLED')
);
inner_block: BEGIN
DECLARE v_region STRING DEFAULT 'APAC'; -- shadows outer v_region
SET v_inner_sum = (
SELECT CAST(COALESCE(SUM(amount), 0) AS DECIMAL(12,2))
FROM demo_orders
WHERE region = v_region AND status IN ('NEW','OK','CANCELLED')
);
END inner_block;
SELECT
v_outer_sum AS eu_sum,
v_inner_sum AS apac_sum;
END;Conclusion
Let's close with a set of practical recommendations and architectural patterns.
Based on the architectural characteristics of Spark SQL Scripting, we have distilled the following development guidelines:
In Alphyn Lakehouse, use Spark Scripting only when you are working within a Spark environment. If your primary data processing or service layer is built on MPP engines such as StarRocks or Impala, consider using the platform's LPSQL procedural extension instead — its capabilities are actually broader than Spark SQL Scripting;
Remember that as of the current version (April 2026), there is no way to save a procedure for later invocation via the CALL command. Plan for script storage architecturally;
Design with NOT ATOMIC in mind — forget classical transactions. Your operations must be idempotent: use CREATE IF NOT EXISTS, INSERT OVERWRITE instead of plain inserts, and the atomic MERGE statement;
Isolate variable scopes. In large scripts with nested blocks, avoid duplicating variable names to prevent confusion from context shadowing;
Strictly separate identifier templating from value binding. Never concatenate dynamic SQL strings manually. Use IDENTIFIER() for object names and USING for parameter values — no exceptions;
Control cardinality in EXECUTE IMMEDIATE INTO. When capturing a scalar into a variable via INTO, guarantee at the SQL level that no more than one row will be returned — for example, by using aggregation or LIMIT 1;
Respect the nature of distributed systems. Loops are excellent for orchestration tasks (iterating over a list of dates or a finite set of values), but they are entirely unsuitable for row-by-row processing of large datasets — this is not an OLTP database. For heavy computations, always use set-based relational operations (JOIN, GROUP BY), reserving loops for control structures only.
Spark SQL Scripting is not mere syntactic sugar — it is an evolutionary step toward bridging classical analytical RDBMS capabilities (such as Oracle PL/SQL or MS SQL Server T-SQL) with the power of distributed Apache Spark computation. Scripting enables data engineers to build processing pipelines in pure SQL, without reaching for external languages or components, reducing the codebase and lowering the barrier to entry for data analysts.
See it on your own data
If you're weighing how this would handle your workloads, we'd be glad to walk you through Alphyn Lakehouse on a real scenario. Book a sovereign-lakehouse walkthrough →
About Alphyn.AI
We build the Alphyn Lakehouse, a Kubernetes-native, high-performance, multi-engine lakehouse for any enterprise data and analytical workload — from agentic AI and BI to structured and unstructured data. Built entirely on open standards and an open architecture, Alphyn Lakehouse is a sovereign, on-premises solution for regulated enterprises across the GCC and the wider MENA region.