[Bug] Extra underscores added to schema causing failure

teddy-ruby commented 1 year ago

Is there an existing issue for this?

[x] I have searched the existing issues

Describe the issue

In creating the view, the final select statement adds underscores to my schema causing errors. I am just starting to work on supporting formula fields for my salesforce connectors so I am fairly new to dbt and have a setup that is pretty much copied from the examples. My src_salesforce.yaml file looks like : version: 2

sources:
  - name: salesforce #It would be best to keep this named salesforce
    schema: 'org_0cc6aa0e_salesforce' #Modify this to be where your Salesforce data resides
    tables:
      - name: fivetran_formula_model
        description: Used for the recommended Option 1 version of the formula solution.
      - name: fivetran_formula
        description: Used for options 2 and 3 of the original individual formula solution.
      - name: account
        description: SFDC account table
      - name: opportunity
        description: SFDC opportunity table

And my opportunity model looks like:

{{ salesforce_formula_utils.sfdc_formula_view('opportunity') }} In the created queries, the schema in the FROM portion of my select statement turns from org_0cc6aa0e_salesforce -> org_0_cc_6_aa_0_e_salesforce which does not exist and therefore the run errors.

Relevant error log or model output

Some initial queries work correctly:
select
                model as value

            from "saasgrid"."org_0cc6aa0e_salesforce"."fivetran_formula_model"

            where object = 'opportunity'

            group by model
            order by count(*) desc

But ultimately the view materialization fails
SELECT mt.old_c,mt.owner_id,mt.contract_id,mt.lead_source,mt.security_request_required_c,mt.type,mt.se_notes_c,mt.churn_notice_received_date_c,mt.next_step,mt.fiscal_year,mt.marketing_source_c,mt.cabal_connections_c,mt.do_not_trigger_slack_c,mt.accounts_payable_name_c,mt.created_by_role_c,mt.brad_s_pipe_c,mt.id,mt.closed_loss_reason_c,mt.sales_loft_1_most_recent_cadence_name_c,mt.last_referenced_date,mt.trial_calendar_link_c,mt.mrr_c,mt.probability,mt.record_type_id,mt.original_source_drill_down_1_c,mt.managed_service_account_c,mt.closed_not_qualified_reason_c,mt.igm_deal_hub_create_subscription_c,mt.churn_notice_received_c,mt.closed_by_role_c,mt.mql_source_drill_down_2_c,mt.renewal_strategy_c,mt.deal_hub_create_subscription_c,mt.discovery_completed_c,mt.billing_frequency_c,mt.manager_forecast_category_c,mt.forecast_category,mt.next_steps_c,mt.executive_leadership_c,mt.marketing_sub_source_c,mt.last_viewed_date,mt.loss_reason_c,mt.unique_entry_current_endpoint_c,mt.trial_start_date_c,mt.win_reason_c,mt.last_amount_changed_history_id,mt.has_open_activity,mt.demo_date_c,mt.mql_source_c,mt.accounts_payable_phone_c,mt.fiscal_quarter,mt.security_questionnaire_request_date_c,mt.renewal_strategy_notes_c,mt.sdr_c,mt.date_of_prospecting_c,mt.latest_source_drill_down_1_c,mt.original_source_drill_down_2_c,mt.push_count,mt.account_id,mt.date_of_negotiating_c,mt.contract_start_date_c,mt.link_to_completed_sec_req_c,mt.latest_source_c,mt.lead_type_c,mt.custom_tos_c,mt.economic_buyer_c,mt.close_date,mt.finance_approval_c,mt.manager_next_steps_c,mt.last_modified_date,mt.use_cases_c,mt.trial_end_date_c,mt.opt_out_date_c,mt.pricebook_2_id,mt.budget_confirmed_c,mt.marketing_subsource_detail_c,mt.billing_type_c,mt.at_risk_c,mt.last_modified_by_id,mt.system_modstamp,mt.stage_name,mt.roi_analysis_completed_c,mt.loss_reason_notes_c,mt.last_stage_change_date,mt.deal_room_url_c,mt.campaign_id,mt.is_closed,mt.date_of_pending_c,mt.delta_arr_c,mt.date_of_qualifying_c,mt.last_activity_date,mt.champion_c,mt.pre_qualification_notes_c,mt.accounts_payable_email_c,mt.dh_arr_c,mt.latest_source_drill_down_2_c,mt.synced_quote_id,mt.is_won,mt.referring_partner_c,mt.sales_to_success_handoff_c,mt.fiscal,mt.onboarding_fee_c,mt.name,mt.contract_end_date_c,mt.lid_linked_in_company_id_c,mt.created_by_id,mt.billing_start_date_c,mt.is_private,mt.billing_status_c,mt.reason_code_c,mt.total_opportunity_quantity,mt.notified_of_renewal_c,mt.description,mt.has_overdue_task,mt.contact_id,mt.renewal_notification_sent_c,mt.competitor_c,mt.churn_details_c,mt.last_close_date_changed_history_id,mt.pandadoc_tracking_number_c,mt.sales_engineer_c,mt.is_deleted,mt.expected_revenue,mt.demo_outcome_c,mt.opt_out_c,mt.saasoptics_auto_process_c,mt.billing_complete_c,mt.opportunity_renewal_date_c,mt.marketing_ad_group_c,mt.amount,mt.date_of_trialing_c,mt.decision_process_c,mt.original_source_c,mt.term_c,mt.sales_loft_1_most_recent_last_completed_step_c,mt.months_c,mt.cs_renewal_forecast_category_c,mt.risks_red_flags_c,mt.unique_entry_ring_lead_last_processed_date_c,mt.gong_main_competitors_c,mt.se_sentiment_c,mt.has_opportunity_line_item,mt.security_questionnaire_status_c,mt.forecast_category_name,mt.sales_loft_1_primary_contact_c,mt.outbound_override_c,mt.unique_entry_ring_lead_app_field_c,mt.created_date,mt.identify_pain_c,mt.win_reason_notes_c,mt.mql_source_drill_down_1_c,mt.net_terms_c,null AS deal_hub_synced_quote_status_c,CASE WHEN (COALESCE(mt.stage_name,'')=COALESCE('Closed Lost','')) THEN mt.delta_arr_c ELSE CASE WHEN (COALESCE(mt.stage_name,'')=COALESCE('Closed Won','')) THEN (COALESCE(mt.dh_arr_c,0)*COALESCE(mt.probability,0)) ELSE CASE WHEN ((COALESCE(mt.delta_arr_c,0)<COALESCE(0.99,0)) AND (COALESCE(mt.delta_arr_c,0)>COALESCE((-0.99),0))) THEN (COALESCE(mt.dh_arr_c,0)*COALESCE(mt.probability,0)) ELSE (COALESCE(mt.delta_arr_c,0)*COALESCE(mt.probability,0)) END END END AS forecasted_arr_c,1 AS opportunity_count_c,CASE WHEN (mt.contract_start_date_c IS NULL) THEN mt.close_date ELSE mt.contract_start_date_c END AS reporting_start_date_c,jt1.customer_start_date_c AS customer_start_date_c,null AS dh_tcv_c,null AS cross_object_lookup_c,null AS created_by_manager_c,((((((mt.name||' - ')||'Q')||CONVERT( VARCHAR, CASE WHEN (COALESCE(((COALESCE(EXTRACT(MONTH FROM mt.close_date),0)-COALESCE(1,0))),0)/COALESCE(3,0))<0 THEN CEIL((COALESCE(((COALESCE(EXTRACT(MONTH FROM mt.close_date),0)-COALESCE(1,0))),0)/COALESCE(3,0)))-1 ELSE CEIL((COALESCE(((COALESCE(EXTRACT(MONTH FROM mt.close_date),0)-COALESCE(1,0))),0)/COALESCE(3,0))) END ))||' ')||CONVERT( VARCHAR, EXTRACT(YEAR FROM mt.close_date) ))||' PRO') AS naming_convention_c,null AS total_acv_c,CASE WHEN ((COALESCE(mt.type,'')=COALESCE('New Business','')) AND (mt.is_won=True)) THEN 1 ELSE 0 END AS new_business_won_count_c,CASE WHEN ((COALESCE(mt.type,'')=COALESCE('Expansion','')) AND (mt.is_closed=FALSE)) THEN 0 ELSE CASE WHEN (COALESCE(mt.stage_name,'')=COALESCE('Closed Lost','')) THEN ABS((COALESCE(jt1.original_arr_new_c,0)+COALESCE(jt1.expansion_contraction_lifetime_c,0))) ELSE CASE WHEN (mt.is_closed=FALSE) THEN jt1.current_arr_new_c ELSE (COALESCE(mt.dh_arr_c,0)-COALESCE(mt.delta_arr_c,0)) END END END AS opp_starting_arr_c,(COALESCE(((COALESCE(mt.mrr_c,0)*COALESCE(mt.months_c,0))),0)+COALESCE(mt.onboarding_fee_c,0)) AS tcv_c,(COALESCE(jt1.current_mrr_c,0)+COALESCE(mt.mrr_c,0)) AS new_managed_services_mrr_c,CASE mt.stage_name WHEN '1 - Prospecting' THEN 1 WHEN '2 - Qualifying' THEN 2 WHEN '3 - Trialing' THEN 3 WHEN '4 - Negotiating' THEN 4 WHEN '5 - Pending' THEN 5 WHEN 'Closed Won' THEN 6 ELSE 0 END AS stage_value_c,DATEDIFF( DAYS, CAST( mt.date_of_prospecting_c AS TIMESTAMP), CAST( DATE(mt.created_date)  AS TIMESTAMP)) AS days_in_prospecting_c,CAST(DATEADD( SECONDS, CAST ( ( 30 * 24 * 60 * 60 ) * -1 AS INTEGER ), CAST( mt.contract_start_date_c  AS TIMESTAMP) ) AS TIMESTAMPTZ) AS renewal_notification_date_30_days_c,(COALESCE(mt.mrr_c,0)*COALESCE(12,0)) AS arr_c,null AS record_type_name_c,DATEDIFF( DAYS, CAST( mt.date_of_qualifying_c AS TIMESTAMP), CAST(  CURRENT_DATE   AS TIMESTAMP)) AS days_in_qualifying_c,1 AS gong_gong_count_c,((((((CASE WHEN (COALESCE(LENGTH(COALESCE(jt1.billing_street,'')),0)>COALESCE(0,0)) THEN (jt1.billing_street||' ') ELSE '' END||'\n')||CASE WHEN (COALESCE(LENGTH(COALESCE(jt1.billing_city,'')),0)>COALESCE(0,0)) THEN (jt1.billing_city||', ') ELSE '' END)||CASE WHEN (COALESCE(LENGTH(COALESCE(jt1.billing_state,'')),0)>COALESCE(0,0)) THEN (jt1.billing_state||' ') ELSE '' END)||CASE WHEN (COALESCE(LENGTH(COALESCE(jt1.billing_postal_code,'')),0)>COALESCE(0,0)) THEN jt1.billing_postal_code ELSE '' END)||'\n')||CASE WHEN (COALESCE(LENGTH(COALESCE(jt1.billing_country,'')),0)>COALESCE(0,0)) THEN jt1.billing_country ELSE '' END) AS account_billing_address_c,mt.id AS x_18_digit_opp_id_c FROM org_0_cc_6_aa_0_e_salesforce.opportunity AS mt LEFT JOIN org_0_cc_6_aa_0_e_salesforce.account AS jt1 ON mt.account_id=jt1.id 

  ) ;

[0m22:41:30.817942 [debug] [Thread-1  ]: Postgres adapter: Postgres error: schema "org_0_cc_6_aa_0_e_salesforce" does not exist

[0m22:41:30.818725 [debug] [Thread-1  ]: On model.fivetran_dbt.opportunity: ROLLBACK
[0m22:41:30.897051 [debug] [Thread-1  ]: finished collecting timing info
[0m22:41:30.897781 [debug] [Thread-1  ]: On model.fivetran_dbt.opportunity: Close
[0m22:41:30.899781 [debug] [Thread-1  ]: Database Error in model opportunity (models/opportunity.sql)
  schema "org_0_cc_6_aa_0_e_salesforce" does not exist
  compiled Code at target/run/fivetran_dbt/models/opportunity.sql

Expected behavior

For the schema to not be altered in the construction of queries.

dbt Project configurations

name: 'fivetran_dbt' version: '1.0.0' config-version: 2

profile: 'fivetran_dbt'

model-paths: ["models"] analysis-paths: ["analyses"] test-paths: ["tests"] seed-paths: ["seeds"] macro-paths: ["macros"] snapshot-paths: ["snapshots"]

target-path: "target" # directory which will store compiled SQL files clean-targets: # directories to be removed by dbt clean

"target"
"dbt_packages"

Package versions

packages:

package: fivetran/salesforce_formula_utils version: 0.8.0

What database are you using dbt with?

redshift

dbt Version

Core:

installed: 1.3.0
latest: 1.3.2 - Update available!

Your version of dbt-core is out of date! You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

Plugins:

redshift: 1.3.0 - Up to date!
postgres: 1.3.0 - Update available!

Additional Context

No response

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

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

teddy-ruby commented 1 year ago

Upon further investigation, I realized this is SQL query stored in my fivetran_formula_model table so the schema must be altered in that creating of those rows during sync.

fivetran-joemarkiewicz commented 1 year ago

Hey @teddy-ruby you beat me to it! You are correct that the SQL which seems to be throwing the error you are seeing is generated by the connector. As such, there is not much our team is able to do from the dbt package.

However, our engineering team will be able to help address any issues from the SQL generated within the fivetran_formula_model table. You can open a support ticket to have our customer support and engineering teams help you!

fivetran-joemarkiewicz commented 1 year ago

As this is an issue we won't be able to address within the dbt package, I will close the issue. Please be sure to reach out to our support team to help address the issue. Thanks!

teddy-ruby commented 1 year ago

Thanks Joe! Yep, I reached out to support and they are working to address the issue. Thanks for your response! Cheers

fivetran / dbt_salesforce_formula_utils