Compiled dbt preview not working with jinja is_incremental() macro function on dbt-oracle

[tmirko]

Expected behavior

Expected to see result of compiled dbt preview

Actual behavior

I get an error:

Exception: Runtime Error
  Runtime Error
    Runtime Error
      not connected

Detailed error information:
Error: Traceback (most recent call last):
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\connections.py", line 222, in exception_handler
    yield
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\connections.py", line 274, in add_query
    cursor = connection.handle.cursor()
cx_Oracle.InterfaceError: not connected

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Users\<user>\.vscode\extensions\innoverio.vscode-dbt-power-user-0.10.1\dist\dbt_integration.py", line 441, in compile_node
    compiled_node = self.sql_compiler.compile(self.dbt)
  File "C:\Users\<project location>\lib\site-packages\dbt\task\sql.py", line 39, in compile
    return compiler.compile_node(self.node, manifest, {}, write=False)
  File "C:\Users\<project location>\lib\site-packages\dbt\compilation.py", line 535, in compile_node
    node = self._compile_node(node, manifest, extra_context)
  File "C:\Users\<project location>\lib\site-packages\dbt\compilation.py", line 394, in _compile_node
    compiled_node.compiled_code = jinja.get_rendered(
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 587, in get_rendered
    return render_template(template, ctx, node)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 542, in render_template
    return template.render(ctx)
  File "C:\Users\<project location>\lib\site-packages\jinja2\environment.py", line 1301, in render
    self.environment.handle_exception()
  File "C:\Users\<project location>\lib\site-packages\jinja2\environment.py", line 936, in handle_exception
    raise rewrite_traceback_stack(source=source)
  File "<template>", line 21, in top-level template code
  File "C:\Users\<project location>\lib\site-packages\jinja2\sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 326, in __call__
    return self.call_macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 253, in call_macro
    return macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 6, in template
  File "C:\Users\<project location>\lib\site-packages\jinja2\sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\impl.py", line 189, in get_relation
    return super().get_relation(database, schema, identifier)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\base\impl.py", line 776, in get_relation
    relations_list = self.list_relations(database, schema)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\base\impl.py", line 726, in list_relations
    relations = self.list_relations_without_caching(schema_relation)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\impl.py", line 223, in list_relations_without_caching
    results = self.execute_macro(
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\base\impl.py", line 1026, in execute_macro
    result = macro_function(**kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 326, in __call__
    return self.call_macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 253, in call_macro
    return macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 2, in template
  File "C:\Users\<project location>\lib\site-packages\jinja2\sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 326, in __call__
    return self.call_macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 253, in call_macro
    return macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 2, in template
  File "C:\Users\<project location>\lib\site-packages\jinja2\sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 326, in __call__
    return self.call_macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\clients\jinja.py", line 253, in call_macro
    return macro(*args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
  File "<template>", line 11, in template
  File "C:\Users\<project location>\lib\site-packages\jinja2\sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\base\impl.py", line 278, in execute
    return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\sql\connections.py", line 123, in execute
    _, cursor = self.add_query(sql, auto_begin)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\connections.py", line 278, in add_query
    return connection, cursor
  File "C:\Users\<project location>\lib\contextlib.py", line 135, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\<project location>\lib\site-packages\dbt\adapters\oracle\connections.py", line 245, in exception_handler
    raise dbt.exceptions.RuntimeException(e) from e
dbt.exceptions.RuntimeException: Runtime Error
  not connected

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\Users\<user>\.vscode\extensions\innoverio.vscode-dbt-power-user-0.10.1\dist\dbt_integration.py", line 430, in compile_sql
    node = self.compile_node(self.get_server_node(raw_sql, temp_node_id))
  File "c:\Users\<user>\.vscode\extensions\innoverio.vscode-dbt-power-user-0.10.1\dist\dbt_integration.py", line 448, in compile_node
    raise RuntimeException(str(e))
dbt.exceptions.RuntimeException: Runtime Error
  Runtime Error
    not connected

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\Users\<user>\.vscode\extensions\innoverio.vscode-dbt-power-user-0.10.1\dist\node_python_bridge.py", line 104, in <module>
    value = eval(_compile(data['code'], '<input>', 'eval'), _locals)
  File "<input>", line 1, in <module>
  File "c:\Users\<user>\.vscode\extensions\innoverio.vscode-dbt-power-user-0.10.1\dist\dbt_integration.py", line 434, in compile_sql
    raise RuntimeException(str(e))
dbt.exceptions.RuntimeException: Runtime Error
  Runtime Error
    Runtime Error
      not connected

Steps To Reproduce

Run a Compiled dbt preview on a the dbt model:

{{
  config(
    materialized = 'incremental',
    unique_key = 'payment_id'
    )
}}

with payments as (
select
    id as payment_id,
    orderid as order_id,
    paymentmethod as payment_method,
    status,

    -- amount is stored in cents, convert it to dollars
    amount / 100 as amount,
    created as created_at

from {{ source('stripe', 'payment_src') }}
{% if is_incremental() %}
  where created > (select max(created_at) from {{this}})
{% endif %}
) 

select * from payments

Log output/Screenshots

No response

Operating System

Windows-10-10.0.19041-SP0

dbt version

1.3.0

dbt Adapter

dbt-oracle

dbt Power User version

v0.10.1

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[mdesmet]

Did you setup https://github.com/innoverio/vscode-dbt-power-user#dbtquerytemplate-for-oracle

[tmirko]

Yes I did, when I comment out the is_incremental() function block Compiled dbt preview works as expected.

[mdesmet]

The issue what you see means that an exception has happened and the transaction is rolled back.

Could you figure out what queries are sent to the database and understand what caused the exception?

[tmirko]

Data model runs successfully. In logs for this data model run I can see a few things being done, here are some notable ones that I would expect to see in the compiled dbt preview:

create global temporary table o$pt_stg_payments125228
  on commit preserve rows
  as

with payments as (
select
    id as payment_id,
    orderid as order_id,
    paymentmethod as payment_method,
    status,

    -- amount is stored in cents, convert it to dollars
    amount / 100 as amount,
    created as created_at

from <source>.payment_src

  where created > (select max(created_at) from <user>.stg_payments)

) 

select * from payments;

merge into <user>.stg_payments target
          using o$pt_stg_payments125228 temp
          on (
                temp.PAYMENT_ID = target.PAYMENT_ID
        )
        when matched then
          update set
          target.ORDER_ID = temp.ORDER_ID, 
          target.PAYMENT_METHOD = temp.PAYMENT_METHOD, 
          target.STATUS = temp.STATUS, 
          target.AMOUNT = temp.AMOUNT, 
          target.CREATED_AT = temp.CREATED_AT
          when not matched then
          insert(PAYMENT_ID, ORDER_ID, PAYMENT_METHOD, STATUS, AMOUNT, CREATED_AT)
          values(
            temp.PAYMENT_ID, 
            temp.ORDER_ID, 
            temp.PAYMENT_METHOD, 
            temp.STATUS, 
            temp.AMOUNT, 
            temp.CREATED_AT
            )

I don't know how to check which queries are sent to the database when compiled dbt preview is executed.

[quickcoffee]

I can confirm that the same behaviour is present in our environment with trino as the query engine. Should is_incremental() be recognised by dbt-power-user?

[kairichard]

Can confirm same problem for us using snowflake - removing the {% if is_incremental() %} block allows it to run just fine.

[hodakermani]

I can confirm the same issue. Does anyone have a solution for this already? :)