[Feature] dbt compile does not work on new schema

[fivetran-joemarkiewicz]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Currently when attempting to run dbt compile without doing a dbt run previously you are hit with a compilation error in the date spine model. This is due to the quickbooks__general_ledger model not being created yet and it is a dependency of the date spine.

It would be preferable if the package can run dbt compile without needing a dbt run. This will help with CI/CD processes and ensure users will be able to validate the compiled code prior to execution.

Describe alternatives you've considered

Running dbt run before dbt compile. This solves the issue, but is not the ideal solution.

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

This may not be able to be fully addressed. However, it should be investigated and at the least documented for users.

[josler]

We're hitting this problem when trying to use this model on dbt cloud - appears we are running before compiling as well and coming across this problem.

We can't use Fivetran to run this quickbooks transformer, due to a separate bug with number precision in generated tables in our Snowflake destination.

Is there an ETA for a fix on this? Thanks!

[fivetran-joemarkiewicz]

Hey @josler thanks for commenting here and sorry to hear you are running into this issue. Before diving into your case, would you be able to add more detail to your comment here

We can't use Fivetran to run this quickbooks transformer, due to a separate bug with number precision in generated tables in our Snowflake destination.

Is this a rounding bug you are seeing in the package or directly with the source data synced by Fivetran? I mainly ask because if it is either I am not entirely sure transforming via dbt Cloud or Fivetran Transformations will resolve this issue. Let me know!

Now to your initial question! We have seen this error occur when you are trying to run the package for the very first time (especially in some dbt Cloud envs) due to dbt compile being run first, then followed by dbt run. The issue stems from the following code.

dbt_quickbooks/models/intermediate/int_quickbooks__general_ledger_date_spine.sql

Lines 5 to 8 in 5fe28a4

	{% if execute %}
	{% set first_date_query %}
	select min( transaction_date ) as min_date from {{ ref('quickbooks__general_ledger') }}
	{% endset %}

The issue is that when running dbt compile, dbt is looking for the quickbooks__general_ledger model, but it does not exist yet due to the package not materializing that model for the first time yet. In order to solve this issue we need to understand how to ensure dbt does not error out when it cannot find this model. My initial thought was the if execute should shield it, but that doesn't seem to do the trick.

In other packages, we have avoided this error by changing the ref to a source call. However, we need this code to reference the denormalized general ledger model to find the earliest transaction date. If we look at one of the denormalized transaction tables (via a source call) we may not be getting the earliest date as this is before we union all the transactions together.

Unfortunately there is no ETA on this fix as I am unsure what the best path forward is at the moment. Do you have any ideas or solutions we could implement to solve this on your end? Once we identify a solution, the ETA should be fairly quick to patch this in an upcoming release.

[josler]

Hey,

On the precision issue it's something that Fivetran support told me they're aware of and the team is looking into. I think it's related to how the source data was synced.

In any case, for this issue I don't have enough expertise here to suggest a useful solution without thinking about it further. We couldn't get this transform running correctly on dbt cloud either so are exploring alternative solutions (not necessarily dbt-powered).

Thanks!