[Bug] Filter out deleted Accounts

[fivetran-joemarkiewicz]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Currently the stg_quickbooks__account model brings in ALL accounts. However, there are some cases where an account will be deleted in QuickBooks and it will need to be removed from this model to ensure accuracy of downstream reports. Since there is a _fivetran_deleted field available in the source account table synced by the Fivetran QuickBooks connector, we should leverage this field to then filter out deleted accounts.

This update will result in a breaking change as we will be filtering out records which previously were not being removed.

Relevant error log or model output

Deleted accounts are still present in the staging and downstream models.

Expected behavior

Deleted accounts are removed from staging and downstream models to ensure accuracy of reporting.

Possible solution

In order to not cause unexpected changes in the downstream reports, we will apply this as a breaking change to both dbt_quickbooks_source and dbt_quickbooks (just a CHANGELOG, version, and packages.yml update). The changes required in this source package include the following:

• Update the get_account_columns() macro to add a new field for _fivetran_deleted.
• Add the _fivetran_deleted field into the final cte of the stg_quickbooks__account model.
• In the last select statement of the stg_quickbooks__account model add a where condition to filter out deleted records. We should use the same approach as we have leveraged in other models (see here for a reference).
• Update the src and stg ymls to include the new field for the relevant account docs.

dbt Project configurations

N/A

Package versions

Latest

What database are you using dbt with?

postgres, redshift, snowflake, bigquery, databricks

How are you running this dbt package?

Fivetran Quickstart Data Model

dbt Version

1.8.1

Additional Context

No response

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

This is a good first issue! If you are community member and wish to contribute this fix. Please comment in the thread and our team can help guide you on these updates!

• Yes.
• Yes, but I will need assistance.
• No.

[fivetran-joemarkiewicz]

We will need to also take into consideration that deletes are available for the following source tables via the Fivetran QuickBooks connector.

A few of these staging models already take the _fivetran_deleted filter into consideration, but we will want to batch these changes together and make sure we have full coverage.

[fivetran-joemarkiewicz]

Hi @brandonrf94 I just wanted to let you know that I was holding off on moving this forward further due to my confusion around another example of this issue where we saw an active field being listed as _fivetran_deleted and wanted to make sure this was expected.

I confirmed with our PM for the connector that per QuickBooks this is an expected behavior. With that, I'll move forward with having our team review your PR next sprint and look to incorporate the additional filters in the other staging models as well.

[fivetran-joemarkiewicz]

Hi @brandonrf94, I just wanted to reach back out and provide an update that we are planning to address this in the last sprint of October. You can expect to see this addressed in the release around then. Thanks for your patience!