[Bug] Macro compilation error

calder-holt commented 2 years ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

Attempting to create a model using the macro for this package yields a compilation error.

{{ salesforce_formula_utils.sfdc_formula_view(source_table='account') }}

Relevant error log or model output

Runtime Error
  Compilation Error in model account (models/mart_revops/account.sql)
    In get_column_values(): relation raw_fivetran.salesforce.fivetran_formula does not exist and no default value was provided.

    > in macro statement (macros/etc/statement.sql)
    > called by macro sfdc_get_formula_column_values (macros/sfdc_get_formula_column_values.sql)
    > called by macro sfdc_formula_view_fields (macros/sfdc_formula_view_fields.sql)
    > called by macro sfdc_formula_view (macros/sfdc_formula_view.sql)
    > called by model account (models/mart_revops/account.sql)
    > called by model account (models/mart_revops/account.sql)

Expected behavior

Compiles to a model with formula fields

dbt Project configurations

salesforce_schema: salesforce salesforce_database: raw_fivetran

account_pass_through_columns: [created_date, created_by_id, implementation_tier_c, new_business_opportunity_c, active_recruiting_subscription_tier_c, churn_reason_c, renewal_at_risk_c, contract_end_date_c, ghsa_arr_company_wide_c, ghsa_arr_seats_quantity_c, ghsa_arr_seats_c, implementation_manager_c, account_executive_c, service_tier_c, gho_arr_c, ghr_arr_c, crm_arr_c, inclusion_arr_c, region_c, number_of_hires_30_c, number_of_hires_60_c, number_of_hires_90_c, number_of_hires_12_months_c, all_active_subscriptions_c, implementation_consultant_email_c] opportunity_pass_through_columns: [contract_length_c, created_by_id, last_stage_change_date, sdr_c, marketing_lead_source_c, arr_for_quota_c, date_reached_negotiate_c, date_reached_close_c, date_reached_validate_c, date_reached_develop_c, date_reached_prove_c, arr_up_for_renewal_gho_c, arr_up_for_renewal_ghr_c, arr_up_for_renewal_services_c, arr_up_for_renewal_total_c, arr_up_for_renewal_ghsa_company_wide_c, arr_up_for_renewal_ghsa_seats_c, gho_arr_c, ghr_arr_c, services_arr_c, ghsa_arr_company_wide_c, ghsa_arr_seats_c, ghsa_seats_quantity_c] user_pass_through_columns: [created_date, quota_current_month_c, quota_current_quarter_c, quota_current_year_c, time_to_first_opp_c, time_to_full_quota_c] contact_pass_through_columns: [created_date, created_by_id, marketing_lead_source_c, onboarding_demo_request_c, sourcing_automation_demo_request_c, converted_lead_id_c, lead_created_date_c, requested_demo_timestamp_c] lead_pass_through_columns: [sdr_c, world_region_c, marketing_lead_source_c, marketing_status_c, onboarding_demo_request_c, sourcing_automation_demo_request_c, converted_timestamp_c, mql_timestamp_c, requested_demo_timestamp_c]

Package versions

0.6.5

What database are you using dbt with?

redshift

dbt Version

1.0.4

Additional Context

No response

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

[X] Yes.
[ ] Yes, but I will need assistance and will schedule time during our office hours for guidance
[ ] No.

calder-holt commented 2 years ago

@fivetran-joemarkiewicz I was wondering if you all heard anything back from dbt labs about the compilation error? Also, if there is any workaround we can do to call the macros directly in our model that would be good to know so we could use the package to get some models out the door for our salesforce team.

fivetran-joemarkiewicz commented 2 years ago

Hi @calder-holt I was able to find the dbt-utils issue I had mentioned during our call. However, it seems we were able to address the issue and merged the fix into the package. Therefore, this issue must be some different variation we haven't accounted for yet 🤔.

I am still looking into this, but having trouble recreating the issue on my end. Would you be able to share the contents of your salesforce.src file and any other configs from your dbt_project.yml. This way I can try and replicate the same environment as you and see if I encounter the same issue.

calder-holt commented 2 years ago

@fivetran-joemarkiewicz absolutely!

dbt_project.yml

  salesforce_source:
    +schema: salesforce_source
    enabled: true
    bind: false
    +tags:
    - "fivetran_salesforce"
  salesforce:
    +schema: mart_revops
    enabled: true
    bind: false
    +tags:
    - "fivetran_salesforce"

  salesforce_schema: salesforce
  salesforce_database: raw_fivetran
  account_pass_through_columns: [created_date, created_by_id, implementation_tier_c, new_business_opportunity_c, active_recruiting_subscription_tier_c, churn_reason_c, renewal_at_risk_c, contract_end_date_c, ghsa_arr_company_wide_c, ghsa_arr_seats_quantity_c, ghsa_arr_seats_c, implementation_manager_c, account_executive_c, service_tier_c, gho_arr_c, ghr_arr_c, crm_arr_c, inclusion_arr_c, region_c, number_of_hires_30_c, number_of_hires_60_c, number_of_hires_90_c, number_of_hires_12_months_c, all_active_subscriptions_c, implementation_consultant_email_c]
  opportunity_pass_through_columns: [contract_length_c, created_by_id, last_stage_change_date, sdr_c, marketing_lead_source_c, arr_for_quota_c, date_reached_negotiate_c, date_reached_close_c, date_reached_validate_c, date_reached_develop_c, date_reached_prove_c, arr_up_for_renewal_gho_c, arr_up_for_renewal_ghr_c, arr_up_for_renewal_services_c, arr_up_for_renewal_total_c, arr_up_for_renewal_ghsa_company_wide_c, arr_up_for_renewal_ghsa_seats_c, gho_arr_c, ghr_arr_c, services_arr_c, ghsa_arr_company_wide_c, ghsa_arr_seats_c, ghsa_seats_quantity_c]
  user_pass_through_columns: [created_date, quota_current_month_c, quota_current_quarter_c, quota_current_year_c, time_to_first_opp_c, time_to_full_quota_c]
  contact_pass_through_columns: [created_date, created_by_id, marketing_lead_source_c, onboarding_demo_request_c, sourcing_automation_demo_request_c, converted_lead_id_c, lead_created_date_c, requested_demo_timestamp_c]
  lead_pass_through_columns: [sdr_c, world_region_c, marketing_lead_source_c, marketing_status_c, onboarding_demo_request_c, sourcing_automation_demo_request_c, converted_timestamp_c, mql_timestamp_c, requested_demo_timestamp_c]

sources.yml

- name: salesforce
    database: raw_fivetran
    schema: salesforce
    quoting:
      database: false
      schema: false
      identifier: false
    tables:
      - name: account
      - name: fivetran_formula

fivetran-joemarkiewicz commented 2 years ago

Thanks @calder-holt I still haven't been able to replicate the error just yet. Can you confirm if your Snowflake instance is case sensitive?

calder-holt commented 2 years ago

Hey @fivetran-joemarkiewicz we are on Redshift

fivetran-joemarkiewicz commented 2 years ago

@calder-holt my apologies that may be the reason I am not able to replicate the issue at hand 🤦. I am not sure I was assuming you were on Redshift when you even called it out in the Issue description.

Let me replicate my instance on Redshift and see if I have any luck!

fivetran-joemarkiewicz commented 2 years ago

@calder-holt I was able to replicate a similar (not the same) error when running with the same src.yml configuration you have above. While, it is not the same error, I think it may be leading us in the right direction.

I noticed when adding the following to my src.yml

    quoting:
      database: false
      schema: false
      identifier: false

I started to get compilation errors. Once I removed them, I started to see successful runs. Would you mind removing those temporarily to see if that fixes the issue at hand?

fivetran-joemarkiewicz commented 2 years ago

Hi @calder-holt 👋

I was just curious if the above suggestion ended up working for you?

calder-holt commented 2 years ago

Hey @fivetran-joemarkiewicz sorry for the delayed response here! Removing those lines from the src.yml file I still get the same errors:

Runtime Error
  Compilation Error in model account (models/mart_revops/account.sql)
    In get_column_values(): relation "raw_fivetran"."salesforce"."fivetran_formula" does not exist and no default value was provided.

    > in macro statement (macros/etc/statement.sql)
    > called by macro sfdc_get_formula_column_values (macros/sfdc_get_formula_column_values.sql)
    > called by macro sfdc_formula_view_fields (macros/sfdc_formula_view_fields.sql)
    > called by macro sfdc_formula_view (macros/sfdc_formula_view.sql)
    > called by model account (models/mart_revops/account.sql)
    > called by model account (models/mart_revops/account.sql)

fivetran-joemarkiewicz commented 2 years ago

No worries at all!

If you copy and paste "raw_fivetran"."salesforce"."fivetran_formula" and try to run a select statement directly in Redshift do you see a similar error?

select *
from "raw_fivetran"."salesforce"."fivetran_formula"
limit 10

Or do the database.schema.table all need to be capitalized? If that is the case, we may be able to apply a fix to address that. Before diving into that solution, curious what the results (does it successfully run in Redshift) of the above query are in your warehouse.

calder-holt commented 1 year ago

@fivetran-joemarkiewicz that query executes fine as is (no changes to capitalization necessary)

fivetran-joemarkiewicz commented 1 year ago

Hi @calder-holt this is very interesting. This seems to be a dbt-core related issue in my opinion. Since the compiled code executes as expected, I am unsure why it does not materialize when running through dbt 🤔.

A few suggestions I have:

Open an issue on dbt-core and link this issue for someone to be able to take a closer look on the dbt side as to why it may be compiling properly (as the copy/paste works as expected), but the materialization of the model fails in dbt.
It may be a long shot, but if you have the fivetran_formula_model table in your warehouse, you could try and use upgrade to the latest version of our package and use Option #1 outlined in our README to generate your formula fields. This option has the from statement created by Fivetran in your warehouse and may be able to circumvent the issue we are currently seeing.

I apologize we have not been able to come to a conclusion as to why this is not working on your end. However, it is suspicious that the package compiles the correct code but is not able to materialize. Hopefully someone from dbt-labs will be able to point us in the right direction.

fivetran-joemarkiewicz commented 1 year ago

Hi @calder-holt it seems this issue has gone a bit stale. As the best path forward here is most likely to open an inquiry with dbt-labs, I will close this issue.

Please feel free to reopen this issue if you feel there is still more to discuss.

fivetran / dbt_salesforce_formula_utils