topic Re: RECURSION_ROW_LIMIT - how to increase more than 1M ? in Data Engineering

RECURSION_ROW_LIMIT - how to increase more than 1M ?

saurabh_aher — Thu, 31 Jul 2025 06:47:50 GMT

I have usecase where we requires rows more than 1M. buts recursion is limited to 1M. how to increase this limit in Recursive CTE ?

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

szymon_dybczak — Thu, 31 Jul 2025 07:02:33 GMT

Hi @saurabh_aher ,

According to documentation, this limit can be overridden if the SELECT statement driving the recursive CTE includes a LIMIT clause, which effectively controls the recursion. Did you try that?

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

saurabh_aher — Thu, 31 Jul 2025 07:05:07 GMT

yes I tried that but its giving syntax error when its done.

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

saurabh_aher — Thu, 31 Jul 2025 07:17:59 GMT

example of that.

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

szymon_dybczak — Thu, 31 Jul 2025 07:25:30 GMT

Hi @saurabh_aher ,

And when you try something like that?

with recursive cte as ( select 1 as n union all select n+1 from cte where n<15 ) select * from cte limit 2000000

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

saurabh_aher — Thu, 31 Jul 2025 09:19:43 GMT

Its not working then as well.

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

szymon_dybczak — Thu, 31 Jul 2025 10:02:39 GMT

On my side below approach is working.

%sql WITH RECURSIVE recursive_cte(n) MAX RECURSION LEVEL 200000 AS ( SELECT 1 UNION ALL SELECT n + 1 FROM recursive_cte WHERE n < 1050000 ) SELECT * FROM recursive_cte LIMIT 1050000;

But still generating so many rows using recursive call is not the best idea (unless your use case is different and you paste your code just for the sake of example).

But if you want to generate huge number of rows you can take approach suggested by Itzik-Ben-Gan:

Number series generator challenge solutions – Part 1 - SQLPerformance.com

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

saurabh_aher — Thu, 31 Jul 2025 10:10:32 GMT

I have a Databricks SQL table with the following columns:

id | name | managerId | rolename

This table contains hierarchical data for all employees in the organization, where each employee has an associated manager (except for the CEO, whose managerId is NULL).

I want to build a new table that flattens the hierarchy, so that for any given employee ID, I can retrieve all of their direct and indirect subordinates, along with each subordinate's level in the hierarchy

the code

UNION ALL

-- Recursive part: find all subordinates
SELECT
h.root_id,
h.root_name,
e.id AS emp_id,
e.name AS emp_name,
e.managerId,
h.level_id + 1 AS level_id
FROM hierarchy h
JOIN employees e
ON e.managerId = h.emp_id
)
SELECT *
FROM hierarchy
ORDER BY root_id, level_id, emp_id;

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

szymon_dybczak — Thu, 31 Jul 2025 11:24:28 GMT

Hi @saurabh_aher ,

Look at documentation example. You can't use order by with limit at the same time, because order by prevents early termination of recursion. Load the content of your cte with limit applied to delta table and then you can do whatever you want (I mean order the data according to your needs).

WITH RECURSIVE hierarchy AS ( -- Anchor: start with each employee as their own root (level 0) SELECT e.id AS root_id, e.name AS root_name, e.id AS emp_id, e.name AS emp_name, e.managerId, 0 AS level_id FROM employees e UNION ALL -- Recursive part: find all subordinates SELECT h.root_id, h.root_name, e.id AS emp_id, e.name AS emp_name, e.managerId, h.level_id + 1 AS level_id FROM hierarchy h JOIN employees e ON e.managerId = h.emp_id ) SELECT * FROM hierarchy LIMIT 150000 /*Put here your number*/

Re: RECURSION_ROW_LIMIT - how to increase more than 1M ?

KapilPatil — Mon, 02 Mar 2026 17:21:01 GMT

Hi saurabh_aher,
I was also facing the same issue. I resolved it by using the LIMIT ALL clause where the recursive CTE is used in the SELECT clause. Additionally, the Databricks Runtime (DBR) version must be 17.2 or above.