[Bug] 'dbt run' hangs when running macro on SFDC objects with many columns

[Cameron-Skidmore]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

When doing a 'dbt run' using the macro for the SFDC Account or Opportunity object the build/compile hangs and eventually the process is killed and the following warning message is displayed:

This happens even when specifying only one or two fields in the 'fileds_to_include' parameter of the macro.

The macro works as expected for other objects in the same project (User, Territory_c, Opportunity_field_history). These objects have much fewer total columns and formula columns, which I'm assuming is the reason they are working and the much larger objects are not.

Relevant error log or model output

(see screencap above)

Model does not compile, so there is nothing in the Targets folder of the project.

Expected behavior

A successful build/compile when executing 'dbt run' on models using the macro and referencing the SFDC Account or Opportunity objects, allowing my team to reference the output of the macro in the base models of our current dbt project.

dbt Project configurations

Package versions

What database are you using dbt with?

bigquery

dbt Version

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
• [X] No.

[fivetran-joemarkiewicz]

Hi @Cameron-Skidmore thanks so much for opening this issue!

Based on your write-up in the issue and from our discussion during the office hour call, I would like to try and modify the package to determine the root cause of the issue. I believe this has to do with memory allocation within dbt before compilation even begins. Therefore, I am going to create a branch of this package that limits (or removes) the number of rows from the base table before the formula macro compilation takes place.

I will post back here with an update for a branch that you will be able to try out. As mentioned, I think a realistic timeline for that patched version of the package will be early next week.

[fivetran-joemarkiewicz]

Hi @Cameron-Skidmore 👋

As I mentioned, I was able to get a working branch in place that allows you to specifically include only relevant base fields you want in your materialized model that is created from this macro. This new argument base_fields_to_include allows you to pass through the select fields you want as a list and only those will be materialized. I hope this will help with the performance issue you have been encountering.

To leverage this working branch you may add the following snippet to your packages.yml:

packages:
    - git: https://github.com/fivetran/dbt_salesforce_formula_utils.git
      revision: feature/only-formulas
      warn-unpinned: false

Once you have this in your packages.yml, you may execute dbt deps to fetch the changes in this branch. You can now add the new argument and add the fields you want to bring through. Below is an example:

{{ salesforce_formula_utils.sfdc_formula_view('test_payment_issue_c'
    ,fields_to_include=['is_closed_flag']
    ,base_fields_to_include=['id','name']
) }}

Let me know if you have any questions when testing this out, and I hope this helps with performance! 🤞

[Cameron-Skidmore]

Awesome. Thanks, Joe! I'll test this out today and let you know how it works.

[Cameron-Skidmore]

Ok, I tested the updated macro with several different field combinations on both the Account and Opportunity object, and unfortunately the problem persists. (two examples below)

[fivetran-joemarkiewicz]

Thanks for trying this branch out, but I am sorry to see the issue has not been resolved.

Just to check, did you by chance run a dbt clean and then a dbt deps after updating the packages.yml?

[Cameron-Skidmore]

I had not run dbt clean when testing last time. I thought maybe that was the issue.

But I ran a clean and deps this time, but still no luck. The problem persists :/

[fivetran-joemarkiewicz]

That's very strange indeed 🤔

Could you share the contents of the formula_fields_account.sql file? I am curious what fields you are calling out in the macro.

[fivetran-sheringuyen]

Hey @Cameron-Skidmore! I spent some time looking into this and it looks like it's likely a Python/DBT issue which would be outside of our wheelhouse. There seems to have been a similar issue for quite a few customers a while back here and there were a couple of recommendations that you may want to try in the dbt slack thread. I would also recommend filing an issue with dbt-core, as I suspect they may be better equipped to help out with this particular issue.

[Cameron-Skidmore]

@fivetran-joemarkiewicz I was OOO for a while. Getting caught up. Sounds like this issue might not be able to be fixed here based on @fivetran-sheringuyen comments.

For what it is worth, here is the contents of the formula_fields_account.sql file. It is just the macro.

[fivetran-joemarkiewicz]

Hey @Cameron-Skidmore!

Correct, this is something I believe would be better suited for the folks at dbt-labs are more equipped to answer. The error message you provided from using the macro seems to stem from a spill over of memory within dbt. Since the error is uniquely dbt related, I feel they would be better to help us understand why you are seeing the semaphore failure.

A similar issue (not semaphore, but dbt related) was found using this macro before and the way we were able to resolve it was by the user opening an issue on dbt-core. Since that was succesful in the past, I feel that would be best course of action.

[fivetran-joemarkiewicz]

Closing this issue as stated above it is probably best suited to be addressed within dbt-core. Please feel free to re-open the issue if needed.