[Bug] type mismatches using bigquery

[mdw123]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When attempting to dbt run my project which is connected to a bigquery back-end, I get incompatible type issues with UNION ALLs in quickbooks__expenses_sales_enhances.sql and quickbooks__ap_ar_enhanced.sql

Relevant error log or model output

log-level-system
Summary
Details

Download Logs
21:27:46  Registered adapter: bigquery=1.5.3
21:27:46  Found 120 models, 85 tests, 0 snapshots, 2 analyses, 597 macros, 0 operations, 0 seed files, 40 sources, 0 exposures, 0 metrics, 0 groups
21:27:46  
21:27:47  Concurrency: 4 threads (target='default')
21:27:47  
21:28:36  
21:28:36  Finished running 41 view models, 61 table models in 0 hours 0 minutes and 49.27 seconds (49.27s).
21:28:36  
21:28:36  Completed with 6 errors and 0 warnings:
21:28:36  
21:28:36  Database Error in model quickbooks__expenses_sales_enhanced (models/quickbooks__expenses_sales_enhanced.sql)
21:28:36    Column 6 in UNION ALL has incompatible types: DATE, TIMESTAMP, DATE, TIMESTAMP at [718:5]
21:28:36    compiled Code at target/run/quickbooks/models/quickbooks__expenses_sales_enhanced.sql
21:28:36  
21:28:36  Database Error in model quickbooks__ap_ar_enhanced (models/quickbooks__ap_ar_enhanced.sql)
21:28:36    Column 20 in UNION ALL has incompatible types: DATE, TIMESTAMP at [367:5]
21:28:36    compiled Code at target/run/quickbooks/models/quickbooks__ap_ar_enhanced.sql
21:28:36  
21:28:36  Compilation Error in model.quickbooks.quickbooks__general_ledger_by_period, caused by compilation error in referenced ephemeral model model.quickbooks.int_quickbooks__general_ledger_date_spine
21:28:36  
21:28:36  Compilation Error in model.quickbooks.quickbooks__balance_sheet, caused by compilation error in referenced ephemeral model model.quickbooks.int_quickbooks__general_ledger_date_spine
21:28:36  
21:28:36  Compilation Error in model.quickbooks.quickbooks__profit_and_loss, caused by compilation error in referenced ephemeral model model.quickbooks.int_quickbooks__general_ledger_date_spine
21:28:36  
21:28:36  Compilation Error in model.quickbooks.quickbooks__cash_flow_statement, caused by compilation error in referenced ephemeral model model.quickbooks.int_quickbooks__general_ledger_date_spine
21:28:36  
21:28:36  Done. PASS=96 WARN=0 ERROR=2 SKIP=4 TOTAL=102
21:27:46  Registered adapter: bigquery=1.5.3

Expected behavior

expect dbt run to succeed

dbt Project configurations

  quickbooks_database: abc
  quickbooks_schema: def
  using_address: true # disable if you don't have addresses in QuickBooks
  using_bill: true # disable if you don't have bills or bill payments in QuickBooks
  using_credit_memo: true # disable if you don't have credit memos in QuickBooks
  using_department: true # disable if you don't have departments in QuickBooks
  using_deposit: true # disable if you don't have deposits in QuickBooks
  using_estimate: true # disable if you don't have estimates in QuickBooks
  using_invoice: true # disable if you don't have invoices in QuickBooks
  using_invoice_bundle: true # disable if you don't have invoice bundles in QuickBooks
  using_journal_entry: true # disable if you don't have journal entries in QuickBooks
  using_payment: true # disable if you don't have payments in QuickBooks
  using_refund_receipt: true # disable if you don't have refund receipts in QuickBooks
  using_transfer: true # disable if you don't have transfers in QuickBooks
  using_vendor_credit: true # disable if you don't have vendor credits in QuickBooks
  using_sales_receipt: true # disable if you don't have sales receipts in QuickBooks
  using_credit_card_payment_txn: true # enable if you want to include credit card payment transactions in your staging models
  using_purchase_order: true #enable if you want to include purchase orders in your staging models

Package versions

packages:
  - package: fivetran/quickbooks
    version: [">=0.9.0", "<0.10.0"] # we recommend using ranges to capture non-breaking changes automatically

What database are you using dbt with?

bigquery

dbt Version

I'm using clouddbt and it does not report a version; it throws an exception.

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
• No.

[fivetran-joemarkiewicz]

Hi @mdw123 thanks for opening this issue and my appologies for the delayed response. We were looking into this issue on our end but have hit a bit of a wall in investigating the issue you are seeing.

I believe the best way forward would be for you to schedule some time during our office hours to investigate the error you are seeing and identify possibly next steps to solve the issue.

[fivetran-reneeli]

Hey @mdw123 thanks for raising this to our attention. We tackled this issue in our recent release, v0.12.0. Let us know if there's anything else!