Recursive CTE in Model not supported

Bug reports
1

I have a SQL running on Redshift to traverse a client hierarchy (id, parent_id, type) to find the children of a set of clients with a specific type. This runs fine as a raw SQL, but I am not able to convert this to a model, because Metabase converts my SQL into a

"""
SELECT
x, y, z
FROM
(my recursive CTE)
"""

Which throws this error from Redshift:

"ERROR: Recursive CTE in subquery are not supported".

Is there a way to stop Metabase from converting my SQL in this way? Or does anyone have thoughts on how to persist a recursive query from Metabase?

Many thanks,
Patrik

2

unfortunately there's not (yet). I suggest you convert your SQL to a view in redshift, so Metabase treats it like a normal table

3

Thanks!