[Question] after updatting to 0.12.0 package orders, order_lines and transactions failed

TomaszE commented 4 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

After we updated dbt package from 0.11.0 to 0.12.0 3 tables started to failing ( an hour earlier, on older version dbt run completed successfully )

Relevant error log or model output

10:57:30
10:57:30    Database Error in model shopify__transactions (models/shopify__transactions.sql)
  000904 (42000): SQL compilation error: error line 3 at position 16
  invalid identifier 'TRANSACTIONS_UNIQUE_ID'
  compiled Code at target/run/shopify/models/shopify__transactions.sql
10:57:30  
10:57:30
10:57:30    Database Error in model shopify__order_lines (models/shopify__order_lines.sql)
  000904 (42000): SQL compilation error: error line 3 at position 16
  invalid identifier 'ORDER_LINES_UNIQUE_KEY'
  compiled Code at target/run/shopify/models/shopify__order_lines.sql
10:57:30  
10:57:30
10:57:30    Database Error in model shopify__orders (models/shopify__orders.sql)
  000904 (42000): SQL compilation error: error line 3 at position 16
  invalid identifier 'ORDERS_UNIQUE_KEY'
  compiled Code at target/run/shopify/models/shopify__orders.sql
10:57:30  
10:57:30
10:57:30  Done. PASS=352 WARN=0 ERROR=3 SKIP=7 TOTAL=362

Expected behavior

to run without issues

dbt Project configurations

vars:
 shopify_database: raw
  shopify_union_schemas: ['shopify_uk','shopify_ca','shopify_fr']
  shopify_using_fulfillment_event: true # false by default
  shopify_source:
    order_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
    order_line_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
    customer_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
    order_line_refund_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
    daily_shop_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
    customer_cohorts_pass_through_columns:
      - name: "region"
        alias: "region"
        transform_sql: "case when source_relation = 'shopify_uk' then 'UK' when source_relation = 'shopify_fr' then 'FR' else 'CA' end"
#    shopify_timezone: "case when source_relation = 'shopify_uk' then 'Europe/London' when source_relation = 'shopify_fr' then 'Europe/Paris' else 'America/Toronto' end"

models: 
 shopify:
    +schema: shopify # leave blank for just the target_schema
    intermediate:
      +schema: staging # leave blank for just the target_schema
  shopify_source:
    +schema: staging # leave blank for just the target_schema

Package versions

package: fivetran/shopify version: [">=0.12.0", "<0.13.0"]

What database are you using dbt with?

snowflake

dbt Version

latest

Additional Context

No response

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

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

fivetran-catfritz commented 4 months ago

Hi @TomaszE you'll need to run a dbt run --full-refresh after upgrading since those columns were added as part of the update and dbt most likely thinks you're on an incremental run if you didn't do the full refresh. I included more details in the Breaking Changes section of the CHANGELOG here.

Let me know if that doesn't resolve your issue, however!

fivetran-catfritz commented 4 months ago

Hi @TomaszE following up to my prior message, were you able to resolve this issue?

fivetran-catfritz commented 3 months ago

Hi @TomaszE we are confident that the full refresh will solve the issue you have mentioned, so I am closing out this issue. If you have any additional questions, however, please reach out to us again in this thread!

TomaszE commented 3 months ago

HI Guys, you are right - full refresh have solved the issue with Shopify tables ( and caused 1000 new with other incremental tables we had , that have been dropped ) hence i was a bit busy last couple of days with trying to fix and recreate what was lost ( by my mistake ) and did not have much time to reply here

Thank you for your help! This can be closed now :)

fivetran / dbt_shopify