In some cases, users need to use the OPTION() clause in their dbt models that appears in SQL Server. The problem is that dbt does not provide this capability. A good reproducing example is the recursive code below, fetching data from the database.
test table calendar containing a wide range of dates
WITH cte
AS (SELECT [date]
FROM calendar
WHERE DATE = '2020-01-01'
UNION ALL
SELECT DATEADD(day, 1, [date]) as [date]
FROM cte
WHERE DATEADD(day, 1, [date])
< '2021-01-01'
-- < '2020-02-28'
)
SELECT *
FROM cte
OPTION (MAXRECURSION 0);
In the case of the condition < '2021-01-01', the recursion exceeds 100. Therefore, if you do not use the OPTION clause (MAXRECURSION 0), in this scenario, the recursion will stop at 100, not retrieving all the data in the query. However, in the case of table creation, an error will occur. MAXRECURSIONis just one example; there are many more options.
The use of the option clause in dbt. Below, I'm providing code that demonstrates the use of the option parameter in dbt.
{{
config({
"materialized": 'table',
"option_clause": "MAXRECURSION 0"
})
}}
WITH cte
AS (SELECT [date]
FROM calendar
WHERE DATE = '2020-01-01'
UNION ALL
SELECT DATEADD(day, 1, [date]) as [date]
FROM cte
WHERE DATEADD(day, 1, [date])
< '2021-01-01'
-- < '2020-02-28'
)
SELECT *
FROM cte
In some cases, users need to use the![image](https://github.com/dbt-msft/dbt-sqlserver/assets/114922270/adbdb44c-9fe5-4857-8a07-83c9b40d7cc9)
OPTION()
clause in their dbt models that appears in SQL Server. The problem is that dbt does not provide this capability. A good reproducing example is the recursive code below, fetching data from the database. test tablecalendar
containing a wide range of datesIn the case of the condition
< '2021-01-01'
, the recursion exceeds 100. Therefore, if you do not use theOPTION
clause(MAXRECURSION 0)
, in this scenario, the recursion will stop at 100, not retrieving all the data in the query. However, in the case of table creation, an error will occur.MAXRECURSION
is just one example; there are many more options.The use of the option clause in dbt. Below, I'm providing code that demonstrates the use of the option parameter in dbt.