[Bug] Ambigous columns

[nszoni]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

properties and status columns called twice in the CTE resulting in ambiguity

• models/stg_shopify__order_line.sql
• models/stg_shopify__product.sql

Relevant error log or model output

13:14:18  Database Error in model stg_shopify__order_line (models/stg_shopify__order_line.sql)
13:14:18    Column name properties is ambiguous at [484:9]
13:14:18    compiled Code at target/run/shopify_source/models/stg_shopify__order_line.sql
13:14:18  
13:14:18  Database Error in model stg_shopify__product (models/stg_shopify__product.sql)
13:14:18    Column name status is ambiguous at [158:9]
13:14:18    compiled Code at target/run/shopify_source/models/stg_shopify__product.sql

Expected behavior

Columns are called once eliminating the chance of ambiguity.

dbt Project configurations

require-dbt-version: [">=1.4.0", "<2.0.0"]

Package versions

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<1.1.0"]
  - package: dbt-labs/codegen
    version: [">=0.9.0", "<1.0.0"]
  - package: fivetran/shopify
    version: [">=0.8.0", "<0.9.0"]
  - package: fivetran/google_ads
    version: [">=0.9.0", "<0.10.0"]
  - package: fivetran/google_ads_source
    version: [">=0.9.0", "<0.10.0"]
  - package: fivetran/microsoft_ads
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/facebook_ads
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/ad_reporting
    version: [">=1.2.0", "<1.3.0"]
  - package: calogica/dbt_expectations
    version: [">=0.8.0", "<0.9.0"]
# - package: fivetran/klaviyo
#   version: [">=0.5.0", "<0.6.0"]
  - package: dbt-labs/segment
    version: [">=0.9.0", "<1.0.0"]
  - package: fivetran/instagram_business
    version: [">=0.2.0", "<0.3.0"]
  - package: fivetran/fivetran_utils
    version: [">=0.4.0", "<0.5.0"]
  - package: dbt-labs/metrics
    version: [">=1.4.0", "<1.5.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:
  - installed: 1.4.0
  - latest:    1.4.1 - 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:
  - bigquery: 1.4.0 - Up to date!

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.

[fivetran-joemarkiewicz]

Hi @nszoni thanks for opening this issue!

I do have one quick question, are you be chance leveraging the product_pass_through_columns and order_line_pass_through_columns variables? If so, I wonder if you are passing the status and properties fields into those variables respectively?

If so, you do not need to include those fields as they are already included in the base select statement for the stg_shopify__product and stg_shopify__order_line models. The error is likely occurring due to the variable trying to add the field end of the statements and is thus causing the duplicate fields and the ambiguous column naming error.

If you did want to include these fields still, you could take advantage of the alias argument in the variable config to rename the field. Nevertheless I wanted to highlight that these fields are included in the staging models by default in the package version you are using.

Let me know if this addresses your issue!

[nszoni]

Hi Joe!

Good callout! I'll check and get back to you with my findings!:)

[nszoni]

Hi @fivetran-joemarkiewicz! Indeed, the issue was including those in the variable! Closing this, thanks:)