[Error] <issue with passthrough metrics>

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

Adding passthrough metrics is not working as expected.

dbt_snapchat_ads_source models have metrics that are missing from the dbt_snapchat_ads models.

This is causing the following error:

Column name XXXX is ambiguous at [229:9]

Relevant error log or model output

11:10:41  Began running node model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report
11:10:41  8 of 12 START sql table model dbt_dev_snapchat_ads_source.stg_snapchat_ads__ad_hourly_report  [RUN]
11:10:41  Acquiring new bigquery connection 'model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report'
11:10:41  Began compiling node model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report
11:10:41  Opening a new connection, currently in state closed
11:10:42  Writing injected SQL for node "model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report"
11:10:42  Timing info for model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report (compile): 2023-03-15 11:10:41.790652 => 2023-03-15 11:10:42.058106
11:10:42  Began executing node model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report
11:10:42  Writing runtime sql for node "model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report"
11:10:42  On model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report: /* {"app": "dbt", "dbt_version": "1.4.5", "profile_name": "user", "target_name": "default", "node_id": "model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report"} */

    create or replace table `dp-world-reporting`.`dbt_dev_snapchat_ads_source`.`stg_snapchat_ads__ad_hourly_report`

    OPTIONS()
    as (

with base as (

    select * 
    from `dp-world-reporting`.`dbt_dev_snapchat_ads_source`.`stg_snapchat_ads__ad_hourly_report_tmp`
),

fields as (

    select

    ad_id

 as 

    ad_id

, 

    attachment_quartile_1

 as 

    attachment_quartile_1

, 

    attachment_quartile_2

 as 

    attachment_quartile_2

, 

    attachment_quartile_3

 as 

    attachment_quartile_3

, 

    attachment_total_view_time_millis

 as 

    attachment_total_view_time_millis

, 

    attachment_view_completion

 as 

    attachment_view_completion

, 

    date

 as 

    date

, 

    impressions

 as 

    impressions

, 

    quartile_1

 as 

    quartile_1

, 

    quartile_2

 as 

    quartile_2

, 

    quartile_3

 as 

    quartile_3

, 

    saves

 as 

    saves

, 

    screen_time_millis

 as 

    screen_time_millis

, 

    shares

 as 

    shares

, 

    spend

 as 

    spend

, 

    swipes

 as 

    swipes

, 

    video_views

 as 

    video_views

, 

    view_completion

 as 

    view_completion

, 

    view_time_millis

 as 

    view_time_millis

, 

    video_views

 as 

    video_views

, 
    cast(null as STRING) as 

    video_completion

    from base
),

final as (

    select 
        ad_id,
        cast (date as TIMESTAMP) as date_hour,
        attachment_quartile_1,
        attachment_quartile_2,
        attachment_quartile_3,
        (attachment_total_view_time_millis / 1000000.0) as attachment_total_view_time,
        attachment_view_completion,
        quartile_1,
        quartile_2,
        quartile_3,
        saves,
        shares,
        (screen_time_millis / 1000000.0) as screen_time,
        video_views,
        view_completion,
        (view_time_millis / 1000000.0) as view_time,
        impressions,
        (spend / 1000000.0) as spend,
        swipes

                , video_views

                , video_completion

    from fields
)

select * 
from final
    );

11:10:42  BigQuery adapter: Retry attempt 1 of 1 after error: BadRequest('Column name video_views is ambiguous at [229:9]')
11:10:44  BigQuery adapter: https://console.cloud.google.com/bigquery?project=dp-world-reporting&j=bq:europe-west2:22bf9a03-de4b-439e-b028-4d70bdec354b&page=queryresults
11:10:44  Timing info for model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report (execute): 2023-03-15 11:10:42.058564 => 2023-03-15 11:10:44.210166
11:10:44  Database Error in model stg_snapchat_ads__ad_hourly_report (models/stg_snapchat_ads__ad_hourly_report.sql)
  Column name video_views is ambiguous at [229:9]
  compiled Code at target/run/snapchat_ads_source/models/stg_snapchat_ads__ad_hourly_report.sql
11:10:44  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '71126931-651d-4824-81b0-3ea7e7a2c8ee', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f9d4c33cc70>]}
11:10:44  8 of 12 ERROR creating sql table model dbt_dev_snapchat_ads_source.stg_snapchat_ads__ad_hourly_report  [[31mERROR[0m in 2.42s]
11:10:44  Finished running node model.snapchat_ads_source.stg_snapchat_ads__ad_hourly_report

Expected behavior

Adding passthrough metrics other than (swipes, impressions, spend) should work for both snapchat packages:

dbt_snapchat_ads_source dbt_snapchat_ads

dbt Project configurations

snapchat_ads__ad_hourly_passthrough_metrics: 
      - name: "video_views"
      - name: "video_completion"
    snapchat_ads__ad_squad_hourly_passthrough_metrics:
      - name: "video_views"
      - name: "video_completion"
    snapchat_ads__campaign_hourly_report_passthrough_metrics:
      - name: "video_views"
      - name: "video_completion"

Package versions

package: fivetran/snapchat_ads version: 0.5.1

What database are you using dbt with?

bigquery

dbt Version

1.4

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

fivetran / dbt_snapchat_ads_source

[Error] <issue with passthrough metrics> #16