fivetran/dbt_fivetran_log

[Bug] Recursive issues

Closed this issue · 6 comments

dioptre commented

Is there an existing issue for this?

  • I have searched the existing issues

Describe the issue

I get recursive errors running the dbt package:

20:51:16  Completed with 8 errors and 0 warnings:
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__account (models/staging/stg_fivetran_log__account.sql)
20:51:16    Binder Error: Circular reference to CTE "account", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__account_membership (models/staging/stg_fivetran_log__account_membership.sql)
20:51:16    Binder Error: Circular reference to CTE "account_membership", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__credits_used (models/staging/stg_fivetran_log__credits_used.sql)
20:51:16    Binder Error: Circular reference to CTE "credits_used", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__destination (models/staging/stg_fivetran_log__destination.sql)
20:51:16    Binder Error: Circular reference to CTE "destination", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__destination_membership (models/staging/stg_fivetran_log__destination_membership.sql)
20:51:16    Binder Error: Circular reference to CTE "destination_membership", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__incremental_mar (models/staging/stg_fivetran_log__incremental_mar.sql)
20:51:16    Binder Error: Circular reference to CTE "incremental_mar", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__transformation (models/staging/stg_fivetran_log__transformation.sql)
20:51:16    Binder Error: Circular reference to CTE "transformation", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Runtime Error in model stg_fivetran_log__trigger_table (models/staging/stg_fivetran_log__trigger_table.sql)
20:51:16    Binder Error: Circular reference to CTE "trigger_table", use WITH RECURSIVE to use recursive CTEs
20:51:16  
20:51:16  Done. PASS=6 WARN=0 ERROR=8 SKIP=7 TOTAL=21
stg_fivetran_log__account: error
stg_fivetran_log__account_membership: error
stg_fivetran_log__connector_tmp: success
stg_fivetran_log__credits_used: error
stg_fivetran_log__destination: error
stg_fivetran_log__destination_membership: error
stg_fivetran_log__incremental_mar: error
stg_fivetran_log__log_tmp: success
stg_fivetran_log__transformation: error
stg_fivetran_log__trigger_table: error
stg_fivetran_log__usage_cost: success
stg_fivetran_log__user: success
stg_fivetran_log__connector: success
stg_fivetran_log__log: success
fivetran_log__mar_table_history: skipped
fivetran_log__connector_status: skipped
fivetran_log__transformation_status: skipped
fivetran_log__usage_mar_destination_history: skipped
fivetran_log__audit_table: skipped
fivetran_log__connector_daily_events: skipped
fivetran_log__schema_changelog: skipped

Relevant error log or model output

No response

Expected behavior

Works

dbt Project configurations

default

Package versions

.7 - 0.8

What database are you using dbt with?

postgres

dbt Version

1.5.1

Additional Context

No response

Are you willing to open a PR to help address this issue?

  • Yes.
  • Yes, but I will need assistance and will schedule time during our office hours for guidance
  • No.
fivetran-joemarkiewicz commented

Hi @dioptre thanks for opening this issue. I have actually never seen error logs or a recursive message like this 🤔.

A few quick questions:

  • Are you able to confirm that you have the tables in your source for the models that the package is failing on (ie. do you have an account table in your source?
  • Did you change the materialization of any of the models?
  • Which dbt-postgres adapter version are you using
  • Any other configs you may have globally set in your project. Even if they don't seem to pertain to the fivetran_log package, they may be causing some unexpected behavior

Let me know if you would be able to provide some additional insight following the above questions. This will help us understand what may be occurring in your implementation of the package.

fivetran-joemarkiewicz commented

The only thing I found that could cause an issue like this is the fact that the cte name in the failed models is the same name as the variable we are calling in the from statement.

  • See the cte name here (account) and the variable name here (account).

This does seem to be consistent with the fact that the models which succeeded have different cte names from the variables being declared.

  • See the cte name here (fivetran_user) and the variable name here (user).

However, it is strange that when I am running the package on our internal Postgres AWS RDS instance this does not provide an error. Are you by chance hosting your Postgres instance on a different provider? Or are you using a different warehouse? I am trying to understand why you are seeing this error but our Postgres instance does not 🤔

dioptre commented
dioptre commented
fivetran-joemarkiewicz commented

The reason they are likely not in line is simply due to the Fivetran Log being an early package which didn't have the same standards when we initial built the package. That being said, this is an opportunity to update the staging models to be in line with our other package standards for staging models.

However, I am still curious why this would cause the failures you are seeing? Were you able to identify if there are any other differences to your environment that may be contributing to the error? I mainly want to understand so we can properly prevent this in the future if this is something we need to account for across our packages.

Would you be able to inspect the the target folder of your dbt project to view the materialized code of one of the failed models? I am wondering if the materialized code will give us any insight into the circular reference error.

fivetran-reneeli commented

Thanks @dioptre for raising this ticket! Our newest release covers the respective updates. Feel free to reach out with anything else!