[Bug] Divide by zero with redshift destination

[teddy-ruby]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Thanks so much for this package! When trying to generate salesforce formula fields I get a Divide by zero error. I am using a view materialization in production so I wasn't catching this on the model run but just discovered this when querying the view. I then changed the materialization to table locally and can reproduce the error on model run.

Generally, I believe the issue is that COALESCE is being used with a fallback value of 0 which is then being used in the denominator. I highlighted the problematic line below.

Thanks!

Relevant error log or model output

In particular, I belive the problematic line is: 

(COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_up_for_renewal_c, 0) + COALESCE(mt.arr_change_new_business_c, 0)), 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0))
            END, 0) / COALESCE(mt.arr_up_for_renewal_c, 0)) AS renewal_opp_revenue_retention_c

Replacing the above line with the following I am able to run the query succesfully:
(COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_up_for_renewal_c, 0) + COALESCE(mt.arr_change_new_business_c, 0)), 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0))
            END, 0) /  NULLIF(COALESCE(mt.arr_up_for_renewal_c, 0), 0))) AS renewal_opp_revenue_retention_c

The full code is below

SELECT
    mt.lean_data_reporting_total_marketing_touches_c,
    mt.owner_id,
    mt.contract_id,
    mt.subscription_end_date_c,
    mt.onboarding_kickoff_date_c,
    mt.lead_source,
    mt.other_closed_lost_reason_c,
    mt.csm_c,
    mt.primary_competitor_c,
    mt.sales_accepted_date_c,
    mt.type,
    mt.other_crm_c,
    mt.adr_tier_initial_c,
    mt.renewal_date_c,
    mt.legal_completed_date_c,
    mt.next_step,
    mt.fiscal_year,
    mt.cs_handoff_notes_c,
    mt.arr_change_override_c,
    mt.cabal_connections_c,
    mt.id,
    mt.number_of_rev_ops_requests_c,
    mt.success_risks_c,
    mt.last_referenced_date,
    mt.probability,
    mt.closed_lost_reason_c,
    mt.urgency_c,
    mt.record_type_id,
    mt.previous_stage_c,
    mt.metrics_c,
    mt.number_of_opp_contacts_marketing_c,
    mt.arr_up_for_renewal_c,
    mt.stage_change_date_1_discovery_c,
    mt.summary_c,
    mt.amount_up_for_renewal_c,
    mt.arr_change_cross_sell_c,
    mt.key_risks_c,
    mt.rev_ops_meeting_notes_c,
    mt.stage_change_date_6_routed_signature_c,
    mt.discovery_completed_c,
    mt.number_of_target_accounts_c,
    mt.sales_engagement_tool_c,
    mt.forecast_category,
    mt.potential_blockers_c,
    mt.decision_criteria_c,
    mt.primary_contact_c,
    mt.arr_change_v_2_downsell_reducing_c,
    mt.last_viewed_date,
    mt.stage_change_date_initial_contact_c,
    mt.is_this_a_priority_c,
    mt.why_user_gems_c,
    mt.last_stage_change_date_c,
    mt.adr_c,
    mt.number_of_opp_contacts_sales_c,
    mt.last_amount_changed_history_id,
    mt.has_open_activity,
    mt.lean_data_reporting_opportunity_source_c,
    mt.upgrade_opportunity_c,
    mt.fiscal_quarter,
    mt.trial_offering_c,
    mt.number_of_customers_c,
    mt.lean_data_days_in_stage_c,
    mt.number_of_opp_contacts_ops_c,
    mt.lead_routing_c,
    mt.buyer_committee_names_positions_c,
    mt.push_count,
    mt.account_id,
    mt.adr_meeting_status_c,
    mt.stage_change_date_4_confirm_intent_c,
    mt.marketing_automation_tool_c,
    mt.stage_change_date_2_business_val_c,
    mt.why_now_c,
    mt.demo_referral_notification_sent_c,
    mt.closed_lost_reason_detail_c,
    mt.lean_data_status_info_c,
    mt.data_test_approved_c,
    mt.which_crm_are_they_using_c,
    mt.economic_buyer_c,
    mt.close_date,
    mt.procurement_completed_date_c,
    mt.contract_auto_renews_c,
    mt.last_modified_date,
    mt.pricebook_2_id,
    mt.prod_what_was_missing_c,
    mt.budget_confirmed_c,
    mt.lh_lh_test_2_c,
    mt.first_trial_stage_date_data_test_c,
    mt.user_gem_summary_c,
    mt.last_modified_by_id,
    mt.system_modstamp,
    mt.sub_stage_c,
    mt.stage_name,
    mt.earliest_date_to_retarget_c,
    mt.revenue_reasons_c,
    mt.roi_analysis_completed_c,
    mt.last_stage_change_date,
    mt.pain_c,
    mt.campaign_id,
    mt.is_closed,
    mt.buying_process_and_budget_c,
    mt.referred_date_c,
    mt.first_demo_stage_date_c,
    mt.how_many_pocs_do_we_have_c,
    mt.objectives_and_pain_points_c,
    mt.is_split,
    mt.last_activity_date,
    mt.champion_c,
    mt.synced_quote_id,
    mt.impact_c,
    mt.is_won,
    mt.number_of_opp_contacts_c,
    mt.fiscal,
    mt.number_of_users_c,
    mt.cs_what_could_have_been_done_better_c,
    mt.name,
    mt.lid_linked_in_company_id_c,
    mt.stage_change_date_slip_c,
    mt.created_by_id,
    mt.salesforce_crm_c,
    mt.arr_change_new_business_c,
    mt.lid_is_influenced_c,
    mt.stage_change_date_3_tech_validation_c,
    mt.referral_details_c,
    mt.closed_won_referral_notification_sent_c,
    mt.security_it_completed_date_c,
    mt.referred_by_c,
    mt.lean_data_routing_action_c,
    mt.how_did_you_hear_about_ug_c,
    mt.description,
    mt.has_overdue_task,
    mt.contact_id,
    mt.downsell_reason_c,
    mt.status_c,
    mt.lh_lh_test_1_c,
    mt.manager_notes_c,
    mt.last_close_date_changed_history_id,
    mt.is_deleted,
    mt.expected_revenue,
    mt.arr_change_downsell_removing_c,
    mt.data_test_status_c,
    mt.subscription_start_date_c,
    mt.average_sales_price_c,
    mt.what_s_their_acv_c,
    mt.number_of_opp_contacts_director_c,
    mt.how_did_you_hear_about_us_c,
    mt.what_tools_are_they_using_c,
    mt.competitors_multi_c,
    mt.arr_change_upsell_c,
    mt.amount,
    mt.most_recent_rev_ops_request_date_c,
    mt.decision_process_c,
    mt.first_call_date_c,
    mt.ug_subscription_c,
    mt.decision_criteria_meddic_c,
    mt.additional_information_c,
    mt.gong_main_competitors_c,
    mt.has_opportunity_line_item,
    mt.forecast_category_name,
    mt.funding_approved_c,
    mt.arr_total_c,
    mt.adr_tier_c,
    mt.created_date,
    mt.arr_change_churn_c,
    mt.identify_pain_c,
    jt1.number_of_employees AS account_employees_c,
    CONVERT(VARCHAR, jt1.type) AS account_type_c,
    (COALESCE(
            CASE WHEN (COALESCE(mt.stage_name, '') = COALESCE('Closed Lost', '')) THEN
                0
            ELSE
                (COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_up_for_renewal_c, 0) + COALESCE(mt.arr_change_new_business_c, 0)), 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0))
            END, 0) * COALESCE(mt.probability, 0)) AS opp_arr_total_v_2_prob_weighted_c,
    CASE WHEN (NOT(mt.stage_change_date_6_routed_signature_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_6_routed_signature_c,
    CASE WHEN (NOT(mt.stage_change_date_2_business_val_c IS NULL)) THEN
        DATEDIFF(MINUTE, CAST(mt.first_demo_stage_date_c AS TIMESTAMP), CAST(mt.stage_change_date_2_business_val_c AS TIMESTAMP)) / (24.0 * 60)
    ELSE
        NULL
    END AS days_initial_meeting_to_sales_accepted_c,
    (COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_change_new_business_c, 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) - COALESCE(mt.arr_change_churn_c, 0)) AS arr_change_v_2_c,
    DATEDIFF(MINUTE, CAST(DATE(mt.last_stage_change_date) AS TIMESTAMP), CAST(CURRENT_DATE AS TIMESTAMP)) / (24.0 * 60) AS days_in_current_stage_c,
    NULL AS account_s_rev_ops_owner_c,
    (COALESCE(
            CASE WHEN (COALESCE(mt.stage_name, '') = COALESCE('Closed Lost', '')) THEN
                0
            ELSE
                (COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_up_for_renewal_c, 0) + COALESCE(mt.arr_change_new_business_c, 0)), 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0))
            END, 0) / COALESCE(mt.arr_up_for_renewal_c, 0)) AS renewal_opp_revenue_retention_c,
    mt.primary_contact_c AS primary_contact_id_18_character_c,
    CASE WHEN (NOT(mt.stage_change_date_2_business_val_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_2_business_validation_c,
    NULL AS referred_by_marketing_influencer_c,
    (NOT(LENGTH(COALESCE(CONVERT(VARCHAR, mt.other_crm_c), '')) = 0)) AS crm_filled_in_c,
    CASE WHEN (COALESCE(mt.stage_name, '') = COALESCE('Closed Won', '')) THEN
        TRUE
    ELSE
        FALSE
    END AS is_closed_won_c,
    mt.account_id AS account_id_c,
    NULL AS owner_mismatch_with_account_c,
    CASE WHEN (COALESCE(mt.stage_name, '') = COALESCE('Closed Lost', '')) THEN
        0
    ELSE
        (COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(mt.arr_up_for_renewal_c, 0) + COALESCE(mt.arr_change_new_business_c, 0)), 0) + COALESCE(mt.arr_change_upsell_c, 0)), 0) + COALESCE(mt.arr_change_cross_sell_c, 0)), 0) + COALESCE(mt.arr_change_downsell_removing_c, 0)), 0) + COALESCE(mt.arr_change_v_2_downsell_reducing_c, 0))
    END AS opp_arr_total_v_2_c,
    1 AS gong_gong_count_c,
    NULL AS days_stage_2_to_stage_3_c,
    (NOT(LENGTH(COALESCE(CONVERT(VARCHAR, mt.lead_routing_c), '')) = 0)) AS lead_routing_filled_in_c,
    NULL AS logged_in_user_is_account_owner_c,
    CASE WHEN (NOT(mt.stage_change_date_slip_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_5_slip_c,
    CASE WHEN (NOT(mt.stage_change_date_4_confirm_intent_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_4_confirm_intent_c,
    NULL AS adr_role_c,
    NULL AS days_stage_3_to_stage_4_c,
    (NOT(LENGTH(COALESCE(jt1.whatever_it_takes_account_c, '')) = 0)) AS wit_account_c,
    NULL AS days_stage_4_to_stage_5_c,
    NULL AS calendly_url_adr_c,
    NULL AS days_stage_5_to_stage_6_c,
    NULL AS referred_by_csm_c,
    (COALESCE((COALESCE((COALESCE((COALESCE((COALESCE(
                            CASE WHEN (NOT(LENGTH(COALESCE(mt.metrics_c, '')) = 0)) THEN
                                1
                            ELSE
                                0
                            END, 0) + COALESCE(
                            CASE WHEN (NOT(LENGTH(COALESCE(mt.economic_buyer_c, '')) = 0)) THEN
                                1
                            ELSE
                                0
                            END, 0)), 0) + COALESCE(
                    CASE WHEN (NOT(LENGTH(COALESCE(mt.decision_criteria_meddic_c, '')) = 0)) THEN
                        1
                    ELSE
                        0
                    END, 0)), 0) + COALESCE(
            CASE WHEN (NOT(LENGTH(COALESCE(mt.decision_process_c, '')) = 0)) THEN
                1
            ELSE
                0
            END, 0)), 0) + COALESCE(
    CASE WHEN (NOT(LENGTH(COALESCE(mt.identify_pain_c, '')) = 0)) THEN
        1
    ELSE
        0
    END, 0)), 0) + COALESCE(
            CASE WHEN (NOT(LENGTH(COALESCE(mt.champion_c, '')) = 0)) THEN
                1
            ELSE
                0
            END, 0)) AS meddic_fields_valued_c,
    jt1.last_activity_date AS last_activity_account_c,
    (('https://app.gong.io/go/account?crm-id=' || mt.id) || '&crm-object-type=opportunity') AS gong_deal_url_c,
    NULL AS days_stage_6_to_closed_won_c,
    CASE WHEN (((COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Second Call', ''))
            OR(COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Genuine Interest', ''))
            OR(COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Trial', ''))
            OR(COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Proposal', ''))
            OR(COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Closed Won', '')))
        OR((COALESCE(CONVERT(VARCHAR, mt.stage_name), '') = COALESCE('Closed Lost', ''))
        AND((COALESCE(mt.previous_stage_c, '') = COALESCE('Second Call', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Genuine Interest', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Trial', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Proposal', ''))))
        OR CASE WHEN (NOT(mt.stage_change_date_2_business_val_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END) THEN
        TRUE
    ELSE
        FALSE
    END AS is_sales_qualified_c,
    NULL AS days_created_to_stage_1_c,
    NULL AS current_user_is_adr_owner_opps_c,
    CASE WHEN ((mt.subscription_start_date_c <= CURRENT_DATE)
        AND(mt.subscription_end_date_c >= CURRENT_DATE)) THEN
        TRUE
    ELSE
        FALSE
    END AS active_c,
    CASE WHEN (COALESCE(mt.record_type_id, '') = COALESCE('0123w000001Ydpi', '')) THEN
        'Renewal'
    ELSE
        'Sales'
    END AS opp_record_type_bucket_c,
    ((CONVERT(VARCHAR, mt.stage_name) || ' ') || CONVERT(VARCHAR, mt.sub_stage_c)) AS stage_detail_c,
    CASE WHEN mt.revenue_reasons_c IS NOT NULL THEN
        REGEXP_INSTR(mt.revenue_reasons_c, 'New Business') > 0
    ELSE
        FALSE
    END AS is_new_business_opp_c,
    CASE WHEN (NOT(mt.stage_change_date_1_discovery_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_1_discovery_c,
    CASE WHEN (NOT(mt.stage_change_date_initial_contact_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_0_initial_contact_c,
    NULL AS primary_campaign_source_c,
    (NOT(LENGTH(COALESCE((jt1.abx_c), '')) = 0)) AS abx_account_c,
    ((NOT(LENGTH(COALESCE(mt.adr_c, '')) = 0))
        AND(((COALESCE(mt.record_type_id, '') = COALESCE('0123w000001Ydpd', ''))
        AND((COALESCE(mt.stage_name, '') = COALESCE('Call/Demo', ''))
        OR(COALESCE(mt.stage_name, '') = COALESCE('Second Call', ''))
        OR(COALESCE(mt.stage_name, '') = COALESCE('Genuine Interest', ''))
        OR(COALESCE(mt.stage_name, '') = COALESCE('Trial', ''))
        OR(COALESCE(mt.stage_name, '') = COALESCE('Proposal', ''))
        OR(COALESCE(mt.stage_name, '') = COALESCE('Closed Won', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Call/Demo', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Second Call', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Genuine Interest', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Trial', ''))
        OR(COALESCE(mt.previous_stage_c, '') = COALESCE('Proposal', ''))))
    OR((COALESCE(mt.record_type_id, '') = COALESCE('0123w000001xp5b', ''))
    AND((COALESCE(mt.stage_name, '') = COALESCE('Closed Won', ''))
    OR(
        CASE WHEN (NOT(mt.stage_change_date_1_discovery_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE)
    OR(
        CASE WHEN (NOT(mt.stage_change_date_2_business_val_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE)
    OR(
        CASE WHEN (NOT(mt.stage_change_date_3_tech_validation_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE)
    OR(
        CASE WHEN (NOT(mt.stage_change_date_4_confirm_intent_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE)
    OR(
        CASE WHEN (NOT(mt.stage_change_date_slip_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE)
    OR(
        CASE WHEN (NOT(mt.stage_change_date_6_routed_signature_c IS NULL)) THEN
            TRUE
        ELSE
            FALSE
        END = TRUE))))) AS adr_commissionable_c,
    NULL AS current_user_is_adr_for_account_c,
    NULL AS opp_owner_role_c,
    (COALESCE(mt.adr_tier_c, '') = COALESCE('Tier 1 Opp', '')) AS tier_1_c,
    NULL AS account_s_adr_c,
    (NOT mt.is_closed) AS is_open_c,
    NULL AS calendly_url_ae_c,
    NULL AS created_by_role_c,
    CASE WHEN (NOT(mt.stage_change_date_3_tech_validation_c IS NULL)) THEN
        TRUE
    ELSE
        FALSE
    END AS achieved_stage_3_technical_validation_c,
    NULL AS days_stage_1_to_stage_2_c,
    NULL AS revenue_retention_formula_c,
    NULL AS most_recent_data_test_date_c,
    NULL AS data_test_job_change_rate_c,
    ((((jt1.icp_c = TRUE)
                OR(NOT(LENGTH(COALESCE(jt1.abx_c, '')) = 0)))
            AND(COALESCE(jt1.type, '') = COALESCE('Prospect', '')))
        OR((COALESCE(jt1.type, '') = COALESCE('Prospect', ''))
        AND(COALESCE(jt1.number_of_employees, 0) >= COALESCE(100, 0))
        AND(COALESCE(jt1.number_of_employees, 0) <= COALESCE(10000, 0))
        AND((COALESCE(LOWER(jt1.billing_country), '') = COALESCE('united states', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('usa', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('us', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('united states of america', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('canada', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('united kingdom', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('uk', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('netherlands', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('israel', ''))
        OR(COALESCE(LOWER(jt1.billing_country), '') = COALESCE('australia', ''))))) AS icp_account_c,
    CASE WHEN (mt.sales_accepted_date_c IS NULL) THEN
        FALSE
    ELSE
        TRUE
    END AS sales_accepted_c,
    CASE WHEN (COALESCE(mt.arr_change_override_c, 0) != COALESCE(0, 0)) THEN
        mt.arr_change_override_c
    ELSE
        CASE WHEN ((COALESCE(mt.type, '') = COALESCE('New Business', ''))
            AND(COALESCE(mt.stage_name, '') = COALESCE('Closed Won', ''))) THEN
            mt.arr_total_c
        ELSE
            CASE WHEN ((COALESCE(mt.type, '') = COALESCE('Renewal', ''))
                AND(COALESCE(mt.stage_name, '') = COALESCE('Closed Won', ''))) THEN
                0
            ELSE
                CASE WHEN ((COALESCE(mt.type, '') = COALESCE('Renewal', ''))
                    AND(COALESCE(mt.stage_name, '') = COALESCE('Closed Lost', ''))) THEN
                (- mt.arr_total_c)
            ELSE
                CASE WHEN ((COALESCE(mt.type, '') = COALESCE('Upsell', ''))
                    AND(COALESCE(mt.stage_name, '') = COALESCE('Closed Won', ''))) THEN
                    mt.arr_total_c
                ELSE
                    0
                END
                END
            END
        END
    END AS arr_change_c,
    NULL AS owner_name_c,
    (mt.stage_change_date_4_confirm_intent_c > mt.stage_change_date_slip_c) AS temp_stage_4_c,
    NULL AS referred_by_account_type_c,
    DATEDIFF(MINUTE, CAST(DATE(mt.created_date) AS TIMESTAMP), CAST(mt.close_date AS TIMESTAMP)) / (24.0 * 60) AS days_between_created_and_closed_c,
    CASE WHEN (NOT mt.is_closed) THEN
        DATEDIFF(MINUTE, CAST(DATE(mt.created_date) AS TIMESTAMP), CAST(CURRENT_DATE AS TIMESTAMP)) / (24.0 * 60)
    ELSE
        (DATEDIFF(MINUTE, CAST(DATE(mt.created_date) AS TIMESTAMP), CAST(mt.close_date AS TIMESTAMP)) / (24.0 * 60))
    END AS age_in_days_c,
    mt.id AS opportunity_id_18_character_c,
    NULL AS opp_source_bucket_c,
    (COALESCE(mt.amount, 0) - COALESCE(mt.arr_total_c, 0)) AS one_time_total_c,
    DATEDIFF(MINUTE, CAST(mt.first_demo_stage_date_c AS TIMESTAMP), CAST(CURRENT_DATE AS TIMESTAMP)) / (24.0 * 60) AS days_since_initial_meeting_completed_c,
    NULL AS ug_subscription_nrr_l_365_d_c
FROM
    org_960f5791_salesforce.opportunity AS mt
    LEFT JOIN org_960f5791_salesforce.account AS jt1 ON mt.account_id = jt1.id;

Expected behavior

I would hope for a safe divide by 0 behavior and the value of the formula field to be null. This would allow the model to still be used.

dbt Project configurations

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

profile: 'fivetran_salesforce'

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.1
• latest: 1.3.2 - Update available!

Plugins:

• redshift: 1.3.0 - Up to date!
• postgres: 1.3.1 - 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.

[fivetran-joemarkiewicz]

Hi @teddy-ruby thanks so much for raising this issue!

It looks like the issue you are highlighting is due to a translation issue (with the coalesce divide by zero you mentioned). Unfortunately, the dbt package does not control the translations which occur, but rather materializes the translation synced within the Salesforce connector in the fivetran_formula or fivetran_formula_model tables in your warehouse.

Fortunately, our customer support and engineering teams are able to help address the issue you have highlighted. I would recommend opening a Support Ticket to raise this with our CS and Eng teams so they may work with you on a fix within the connector.

[fivetran-joemarkiewicz]

Marking this as wontfix as this is not something we will be able to address within the dbt package. @teddy-ruby feel free to ask me any other questions. Otherwise, once your support ticket is opened we should be able to close this issue and keep the conversation moving forward in the support ticket.

[fivetran-joemarkiewicz]

Closing this issue as the path forward is to open a Support Ticket (linked in my previous response).

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