Ephemeral materialization not working as expected
nadialloppi opened this issue · 3 comments
Is there an existing issue for this?
- I have searched the existing issues
Current Behavior
Running sql query with ephemeral materialization returns nested WITH clauses.
WITH dbt__cte__name__ as (
WITH
source AS (
SELECT * FROM foo_schema.foo_table
),
Expected Behavior
The expected behavior is a linear, not nested, list of CTE clauses.
Steps To Reproduce
No response
Relevant log output using --debug flag enabled
No response
Environment
- OS:
- Python: 3.10
- dbt: 1.3.2rc1What Oracle database version are you using dbt with?
No response
Additional Context
No response
dbt's ephemeral materialization is meant to be nested
With Oracle, typically, nested WITH statements raise ORA-32034: unsupported use of WITH clause You can read more about the error here
To make ephemeral materialization work, one option is to remove the WITH keyword in the model definition. Please refer an example epehermal model which is also used in a second model both included in this repository
This is the reason why ephemeral models is not yet a supported feature of dbt-oracle
Did you get a chance to try the work around as shown in the example ?
Let me know if you have any questions
Thanks
@nadialloppi closing this issue, please reopen if required