search

fivetran / dbt_fivetran_log

Data models for Fivetran's internal log connector built using dbt.
https://fivetran.github.io/dbt_fivetran_log/
Apache License 2.0
30 stars 24 forks source link

[Bug] Recursive issues #84

Closed dioptre closed 1 year ago

dioptre commented 1 year ago

Is there an existing issue for this?

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?

fivetran-joemarkiewicz commented 1 year ago

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

A few quick questions:

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 1 year ago

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.

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

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 1 year ago

Yes the account field does exist, will do some more exploring.

On Thu, Jul 20, 2023 at 3:08 PM Joe Markiewicz @.***> wrote:

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 https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__account.sql#L1 (account) and the variable name here https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__account.sql#L4 (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 https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__user.sql#L3 (fivetran_user) and the variable name here https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__user.sql#L6 (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 🤔

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_fivetran_log/issues/84#issuecomment-1644678982, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFZTGF7ZFOUEVCWUKVVUJDXRGT5JANCNFSM6AAAAAA2R7ETLQ . You are receiving this because you were mentioned.Message ID: @.***>

dioptre commented 1 year ago

Any reason that you aren't sticking with the base, tmp_ nomenclature in all the other repos?

with base as (

select * from {{ ref('stg_quickbooks__bill_linked_txn_tmp') }}

),

fields as (

select / The below macro is used to generate the correct SQL for package staging models. It takes a list of columns that are expected/needed (staging_columns from dbt_quickbooks_source/models/tmp/) and compares it with columns in the source (source_columns from dbt_quickbooks_source/macros/). For more information refer to our dbt_fivetran_utils documentation ( https://github.com/fivetran/dbt_fivetran_utils.git). /

{{ fivetran_utils.fill_staging_columns( so

On Fri, Jul 21, 2023 at 12:18 PM Andrew Grosser @.***> wrote:

Yes the account field does exist, will do some more exploring.

On Thu, Jul 20, 2023 at 3:08 PM Joe Markiewicz @.***> wrote:

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 https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__account.sql#L1 (account) and the variable name here https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__account.sql#L4 (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 https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__user.sql#L3 (fivetran_user) and the variable name here https://github.com/fivetran/dbt_fivetran_log/blob/0e6d9b1cec1c38b840a63de78d7726dfd502df35/models/staging/stg_fivetran_log__user.sql#L6 (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 🤔

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_fivetran_log/issues/84#issuecomment-1644678982, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFZTGF7ZFOUEVCWUKVVUJDXRGT5JANCNFSM6AAAAAA2R7ETLQ . You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

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 1 year ago

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